Excel高效绝技之三——数据提取与整合

问题背景:(接上集上回说到,小三老板看小三处理数据灵活运用各种工具,速度奇快,效率奇高,也想跟小三学点技术,以备不时之需。这不,一大早的小三还没到办公室,老板就想好了难题要再考考小三的水平,顺便学个一招半式。

老板拿出了在网页上复制的数据(表1、表2)来要小三提取出来做成统计表格(表3)。

表1

表2

表3

小三本来还紧张,生怕完成不了,当着老板的面也不好百度。一看这个要求,心里窃喜,太好办了。用Excel的OFFSET函数和ROW函数就解决了。

Offset函数:Offset是Excel中的函数,在Excel中,OFFSET函数的功能为以指定的引用为参照系,通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区域。并可以指定返回的行数或列数。

语法:OFFSET(reference, rows, cols, [height], [width])

OFFSET 函数语法具有下列参数:

Reference:必需。要作为偏移基准的参照。 引用必须引用单元格或相邻单元格区域。否则, OFFSET 返回 #VALUE! 。

Rows:必需。需要左上角单元格引用的向上或向下行数。 使用 5 作为 rows 参数,可指定引用中的左上角单元格为引用下方的 5 行。 Rows 可为正数(这意味着在起始引用的下方)或负数(这意味着在起始引用的上方)。

Cols:必需。需要结果的左上角单元格引用的从左到右的列数。 使用 5 作为      cols 参数,可指定引用中的左上角单元格为引用右方的 5 列。 Cols 可为正数(这意味着在起始引用的右侧)或负数(这意味着在起始引用的左侧)。

高度:可选。需要返回的引用的行高。 Height 必须为正数。

宽度:可选。需要返回的引用的列宽。 Width 必须为正数。

定义不太好理解,直接上实例。

步骤一:检查数据源表的格式是否统一。把数据源的表做成格式统一的表格,每一个车型的数据区域都是8格,每8行一个循环,格式统一才能找到规律,机器是需要按规律才能正确找到对应数据。

步骤二:编写公式(引用)提取数据。

代码释义:

1、序号列第一格E2=OFFSET(B$1,(ROW(B1)-1)*8,)

提取的第一列是序号,序号的第一个值在B1单元格,所以从B$1开始,之所以绝对定位,是因为所有的序号都要参照B1的位置计算。(ROW(B1)-1)是提取行号,序号第一个理应提取B1,而B1是第一行,因此偏移0行,为(ROW(B1)-1)=1-1,*8是因为左侧源数据以8行一循环,偏移8行就是下一个序号的单元格位置。E2单元格的值就是第一个序号,是B1的值,为1,公式意思是“引用(B$1,(B1的行号-1)*8,)”等于offset(B$1,(1-1)*8,),也等于offset(B$1,0,),就是B1。

因为不需要列偏移,所以*8之后逗号为空。

2、序号列第二格公式自动填充为E3=OFFSET(B$1,(ROW(B2)-1)*8,)

还是从B$1开始计算,绝对应用B1。(ROW(B2)-1)*8提取第二个序号的行号,为(B2的行号-1)*8=(2-1)*8=8,参照B$1往下偏移8行,正好是第9行,就是第二个序号,取值为2。所以,公式的逻辑就是为了构造公差为8的等差数列,而这个数列的值就是应提取序号的行号。而之所用ROW(B1)、ROW(B2)取单元格行号进行计算,而不用常数,是因为用常数,公式填充无法递增。而ROW(B1)、ROW(B2)……填充公式刚好递增1,便于构造出8倍递增的序号行号,用ROW(A1)、ROW(A2)……也是可以的。

3、车型列第一格公式则为F2= =OFFSET(B$2,(ROW(B1)-1)*8,)

因为源数据第一个车型的单元格是B2,那么就从B$2开始为参照,取到的行号2、10、18刚好就是车型单元格的行号。ROW函数取行号还是一样,构造公差为1的等差数列,然后乘以8,以8的倍数偏移,因此,不管是继续用(ROW(B1)-1)*8开始,还是和B$2 同步,用(ROW(B2)-2)*8开始,意义一样。

4、后面的列也同样的道理,只是在B列的基准参照起点依次往后推一格即可。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20200706A0VYRS00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券