利用OFFSET函数将一列数据转为多列数据的原理与应用

周永新，陈晓平

（长沙通信职业技术学院，湖南长沙 410015）

1 认识OFFSET函数

用途：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格或单元格区域，并可以指定返回的行数或列数。

语 法 ： OFFSET（reference， rows， cols，height，width）。

参数：Reference是作为偏移量参照系的引用区域，它必须是单元格或相连单元格区域的引用；Rows是相对于偏移量参照系的左上角单元格，上（下）偏移的行数。如果使用5作为参数Rows，则说明目标引用区域的左上角单元格比reference低5行。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）；Cols是相对于偏移量参照系的左上角单元格，左（右）偏移的列数。如果使用5作为参数Cols，则说明目标引用区域的左上角的单元格比reference靠右5列。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）；Height是要返回的引用区域的行数，Height必须为正数；Width是要返回的引用区域的列数，Width必须为正数。

实例1：如果A1=68、A2=76、A3=85、A4=90，则公式“=SUM(OFFSET(A1:A2，2，0，2，1))”返回177。

2 OFFSET函数的执行过程

从图1中，可以清晰地看到“实例1”中的各参数数据说明，如图1所示。

第一步：首先定位“作为参照系的引用区域”，即图示中的红色区域，实例中该区域为A1：A2，如图①所示。

第二步：找到“相对于参照系的偏移行数”，实例中的该参数为“2”，即在“参照系的引用区域”的基础上偏移2行，如图②所示。

第三步：找到“相对于参照系的偏移列数”，实例中的该参数为“0”，即在“参照系的引用区域”的基础上没有列偏移，光标停留的位置如图②所示；假如本例中该参数为“1”，则会在“参照系的引用区域”的基础上偏移1列，效果如图③所示。

第四步：经过第二步和第三步，系统已经成功的将“引用区域”的地址定位到了A3单元格，至于这个“引用区域”将有多大，是一个单元格，还是由连续的多个单元格组成的一个区域，则取决于OFFSET函数中“height”和“width”两个参数。实例中height参数为“2”、width参数为“1”，即返回的引用区域的是一个两行一列的连续单元格区域，效果如图④所示。假如实例中的“height”和“width”参数分别取值为“2”和“2”，则返回的引用区域的是一个两行两列的连续单元格区域，效果如图⑤所示。

第五步：对引用区域的数据进行运算。实例中即为：对引用区域为A3:A4中的数据进行求和运算，所以返回的结果为：177。

几点说明：

1）实例中“作为参照系的引用区域”为A1：A2，根据Excel的特点，“相连单元格区域”的地址为“左上角单元格”的地址，所以在这里，“A1：A2”区域等价于“A1”单元格。即实例中的函数=SUM(OFFSET(A1:A2，2，0，2，1))，也可以改写为：=SUM(OFFSET(A1，2，0，2，1))。

2）OFFSET函数的第二个参数rows，即“相对于参照系的偏移行数”参数必须为整数，该整数可正可负，正整数表示向下偏移的行数，负整数表示向上偏移的行数。

3） OFFSET函数的第三个参数cols，即“相对于参照系的偏移列数”参数，同上，该参数必须为整数，可正可负，正整数表示向右偏移的列数，负整数表示向左偏移的列数。

4）Height和Width参数必须为正整数。

3 利用OFFSET函数将一列数据转换成多列数据

通过分析OFFSET函数的执行过程可以知道：在以指定的引用为参照系的基础上，通过修改偏移量参数的值，可以得到一个新的引用。这样就为解决“将一列数据转换成多列数据”的问题提供了可能。下面以图3中的数据为例，看看如何利用OFFSET函数“将一列数据转换成多列数据”。

第一步：定位“作为参照系的引用区域”，设定reference参数值为：A1，如图3所示。

第二步：找到“相对于参照系的偏移行数”，设定rows参数。数值“1”的单元格地址为A1，故相对于参照系的偏移行数为“0”，数值“2”的单元格地址为A2，故相对于参照系的偏移行数为“1”，依次类推，数值3、4、……9、10的行偏移数分别为2、3、……8、9，故各数值的“相对于参照系的偏移行数”参数rows的取值分别为0、1、……8、9。

第三步：找到“相对于参照系的偏移列数”，设定cols参数。如图3所示，数值1、2、……9、10均在A列上，故相对于参照系A1的偏移列数均取值为“0”。

第四步：确定“引用区域”的大小，设定“height”和“width”两个参数。对于每一个数值单元格，转换后的“引用区域”仍然是一个单元格，故“height”和“width”两个参数均取值为“1”。

以图3中的数据为例，各数值的OFFSET函数如表1所示。

表1 各数值的OFFSET函数

需要说明的是，转换后的地址是可以根据用户的需要任意指定的。例如在同样的OFFSET函数参数设置下，转换后的数据可以有如图4中所示的多种排列方式。

到此为止，用户可以根据自己的需要，利用OFFSET函数对原数据进行随心所欲的排列。但是有个新的问题却不容忽视：在只有十个数据量的情况下，用户需要对函数进行至少十次修改，那么当数据量扩展到几百个、几千个呢？是否有更好的自动化方法呢？

4 利用自动填充柄，批量执行OFFSET函数

自动填充柄是进行批量数据处理的利器。当它结合公式函数一起使用时，可分为两个步骤：第一步，在选定单元格中定义一个可扩充的函数；第二步，以定义好函数的单元格为基础，拖动自动填充柄即可。如何合理运用单元格地址的“相对引用”和“绝对引用”，定义一个可扩充的函数，是整个问题的关键所在。

根据表3中的OFFSET函数，用户希望得到的各单元格函数为图5中的左图，而如果设定好C2单元格的函数为：OFFSET(A1,0,0,1,1)，通过拖动自动填充柄，会得到图5中的右图，下面通过分析二者之间的差距来改进该OFFSET函数。

经过观察不难发现，自动填充柄拖放OFFSET函数与手工输入OFFSET函数的区别主要表现在函数的第一个参数reference和第二个参数rows。

第一个参数reference：观察手工输入OFFSET函数发现，参数reference中是一个绝对引用单元格。这个问题不难处理，将该单元格地址设为绝对引用皆可。设置后的效果，如图6所示。

第二个参数rows：rows的设置相对于reference要复杂一些。经过观察，可以发现rows的取值与转换后数据的排列方式和所在行列存在一定的关系，下面以图3的数据为例，试着找一下这种关系，如表2所示。

表2 rows取值与转换后矩阵数据的关系

以上关系可以抽象为一个表达式：

rows取值=（数据所在矩阵行-1）×数据矩阵的列数N+数据所在矩阵列-1

通过表3，可以验证这个表达式的正误（N=3）。

表3 验证rows表达式

经过以上验证，可以确定：这个rows表达式可以准确地表示rows的取值，至于如何描述“数据所在矩阵行”和“数据所在矩阵列”，需要借助ROW函数和COLUMN函数。ROW(i:i)可以获得数据所在矩阵第i行的引用；COLUMN(A:A)可以获得数据所在矩阵第A列的引用（返回值是1）。如此一来，便可以将rows表达式描述为具体的应用表达式，以C2单元格为例，它的应用表达式为：

rows取值=(ROW(1:1)-1) ×3+COLUMN(A:A)-1

设置好reference和rows参数以后，其他的参数不需要进行修改，便可以使用自动填充柄批量应用函数了。效果如图7所示。

至此，便可以完整地做出图3的效果了。

5 总结

本文中通过描述OFFSET函数的原理、执行过程、简单应用和结合自动填充柄的应用等四个部分，详细介绍了利用OFFSET函数将一列数据转换为多列数据的原理与应用。在实际的应用中，可能会有各种各样的需求，例如“将一列数据转换为指定行数的数据”、“将两列数据转换为指定列数的数据”等，这些都可以通过修改OFFSET函数中相应的参数来实现。

[1]王兴玲.计算机应用基础[M].北京:清华大学出版社,2011.

[2]希尔伯沙茨,杨冬青.数据库系统概念[M].北京:机械工业出版社,2008.

[3]Excel Home.Excel应用大全[M].北京:人民邮电出版社，2008.

[4]微软公司.MSDN用户手册[Z].2011.

[5]Excel Home.Excel 2010应用大全[M].北京：人民邮电出版社,2011.