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列的基准参照起点依次往后推一格即可。