三级联动下拉菜单的数据怎么汇总（Offset函数的基本概念）

在Excel表格输入公式时，单元格区域的引用是必不可少的；直接框选单元格区域可以实现基本的操作需要，如果要实现动态的单元格区域引用就无能为力了。这篇文章为朋友们分享一下最强大的单元格区域引用函数→offset函数的基本概念和应用实例。Offset函数自身只是一个单元格区域引用函数、并没有什么强大的功能。但是配合SUM、MATCH、COUNTIF等函数就可以完成很多很牛的操作。

一.Offset函数基本概念：

1.语法：Offset(reference, rows, cols, [height], [width])；

各参数中文含义：offset（起始区域，向下偏移行数，向右偏移列数，返回的行数，返回的列数）

2.各参数解释：

（1）Reference：作为参照的单元格引用（不仅可以是单元格，也可以表示从一个区域开始进行偏移。）

（2）Rows：向上或向下偏移的行数（向下为正数，向上为负数；如果这个参数省略表示不向上下偏移，即省略时默认为0）

（3）Cols：向左或向右偏移的列数（向右为正数，向左为负数；如果这个参数省略表示不向左右偏移，即省略时默认为0）

（4）Height：高度，需要返回的引用的行高， Height 必须为正数。（如果这个参数省略表示引用的高度与第一个参数Reference的高度相同。）

（5）Width：需要返回的引用的列宽，Width 必须为正数。（如果这个参数省略表示引用的宽度与第一个参数Reference的宽度相同。）

3.实例演示：

（1）操作：选择E1:F4单元格输入公式=OFFSET(A1,3,1,4,2)同时按住键盘上的Ctrl Shift Enter确定公式。

（2）解析：以A1单元格为参考，向下移动三行、向右移动一列得到的一个4行2列的单元格区域的引用。

二.应用实例：

1.与SUM、MATCH函数配合使用实现动态求和。

（1）选择E2:F2单元格通过数据验证制作一个“月份”下拉列表；选择G2单元格通过数据验证制作一个“销量”下拉列表。

（2）在H2单元格输入公式：

=SUM(OFFSET(A1,MATCH(E2,A2:A10,0),MATCH(G2,B1:C1,0),MATCH(F2,A2:A10,0)-MATCH(E2,A2:A10,0) 1))

（3）公式解析：

①A1单元格作为offset函数的参照单元格。

②MATCH(E2,A2:A10,0)查找E2单元格内容在A2:A10单元格区域的位置；例如，E2内容为2月时，返回的结果为2。

③MATCH(G2,B1:C1,0)查找G2单元格内容在B1:C1单元格区域的位置：例如，G2内容为实际销量时，返回的结果为2。

④MATCH(F2,A2:A10,0)-MATCH(E2,A2:A10,0) 1)中的MATCH(F2,A2:A10,0)查找F2单元格内容在A2:A10单元格区域的位置；例如，F2内容为5月时，返回的结果为5。计算结果5-2 1=4返回的是2月到5月的单元格区域的行数。

⑤offset函数的第五个参数省略，默认值与第一个参数A1单元格区域的列数相同、即第五个参数为1。

⑥最后使用SUM函数对OFFSET函数返回的单元格区域求和。

（4）注意事项：

因为OFFSET函数返回的是一个单元格区域、即返回结果为一个数组，所以一定要同时按住Ctrl Shift Enter三键确定公式。

（5）动态演示：

2.与COUNTIF、MATCH函数配合使用制作多级联动下拉列表。

（1）制作一级下拉菜单：

选择省份下的单元格区域→切换到数据选项卡→数据验证→切换到设置选项界面→允许下选择“序列”→来源下方的对话框输入“辽宁省,吉林省,黑龙江省”→确定。

（2）制作二级联动下拉菜单：

选择市下的单元格区域→切换到数据选项卡→数据验证→切换到设置选项界面→允许下选择“序列”→来源下方的对话框输入下方公式→确定。

=OFFSET($A$1,MATCH($D2,$A$2:$A$16,0),1,COUNTIF($A$2:$A$16,$D2))

（3）公式解析：

①A1单元格作为offset函数的参照单元格,注意这里行和列的绝对引用。

②MATCH($D2,$A$2:$A$16,0)查找D2单元格的省份在A2:A16单元格第一次出现的位置。例如D2单元格内容为“黑龙江省”时，返回的结果为12。这里注意D2单元的只绝对引用列，不绝对引用行。

③因为市在省份的后一列，所以要向右偏移一列、即第三个参数为1。

④COUNTIF($A$2:$A$16,$D2)统计的是D2单元格的省份在A2:A16单元格区域出现的次数，也就是每个省份对应有几个市、即offset函数第四个参数引用单元格区域的行数。

⑤offset函数的第五个参数省略，默认值与第一个参数A1单元格区域的列数相同、即第五个参数为1。

（4）注意事项：

①制作一级下拉菜单时，不同的的选项之间要用英文输入法下的逗号隔开。

②制作二级下拉菜单时，注意公式中对单元格的引用方式、不要混淆绝对引用和相对引用。

（5）动态演示：

总结，通过对OFFSET函数的基本概念的讲解和应用实例介绍相信你一定已经掌握这个函数的用法。如果有什么不懂的地方欢迎在评论区留言讨论。

,