Excel 动态范围引用：OFFSET 函数
本篇文章，小鱼将通过制作下拉菜单的任务，为大家介绍一个 Excel 中重要的函数 OFFSET - 动态引用函数。
下图是某公司的各类产品按月份统计的全年销售额情况：
我们将在 O2 和 P2 单元格制作两个下拉菜单，以便在下方的单元格动态统计选定的【起始月份】~【终止月份】的销售额总计。
其中，【终止月份】的选择范围应大于等于【起始月份】。比如，其实月份选择 “五月” ，终止月份的范围为：五月~十二月。
一、制作下拉菜单
点击 O2 单元格，选择【数据】选项卡下的【数据验证】：
弹出【数据验证】对话框，验证条件选择【序列】：
点击【来源】右侧的箭头标志，将序列来源选中 C2~N2 的区域：
再次点击输入框右侧的箭头标志，返回原始的对话框。这里，序列来源的区域已经自动帮我们设置成了绝对引用的形式。
点击【确定】之后，【起始月份】的下拉列表就制作好了~
二、确定终止月份
2.1 OFFSET 函数
由于【终止月份】的下拉菜单引用的来源会随着【起始月份】变化而变化，因此需要使用【OFFSET】函数实现动态范围引用。我们先来认识一下 OFFSET：
=OFFSET(参考单元格， 行数， 列数， 高度， 宽度)
OFFSET 函数用来动态引用某个单元格或者区域，其参数释义如下：
- 参考单元格 参照依据，是指定的起点单元格；
- 行数 从参考单元格向上（负数）或向下（正数）偏移的行数；
- 列数 从参考单元格向左（负数）或向右（正数）偏移的行数；
- 高度 引用的数据区域有多少行；
- 宽度 引用的数据区域有多少列。
可以用如下的坐标系来概括 OFFSET 函数：
以【参考单元格】作为原点 a ，坐标系向下和向右的方向为正方向，向左和向上为负方向，通过函数 m 和列数 n 定位到单元格 b 。以单元格 b 作为起点，引用 i 行 j 列的区域，作为 OFFSET 函数的返回值。
下面，我们先来感受一下 OFFSET 函数：
当前，【起始月份】选择的是八月，则【终止月份】应为八月~十二月，也就是单元格 J2:N2 的区域。OFFSET 函数的参考单元格为【C2】单元格：
=OFFSET(C2,
接下来，定位到起始月份【八月】，需要从 C2 单元格上下移动 0 行，向右移动 7 列，定位到 J2 单元格：
=OFFSET(C2,0,7,
最后，我们需要 OFFSET 返回的引用区域为 J2:N2 ，即从J2 单元格开始，返回 1 行 5 列的区域即可：
=OFFSET(C2,0,7,1,5)
如此就得到我们期望返回的区域啦：
如果选择起始月份是九月，则相应的公式为：
=OFFSET(C2,0,8,1,4)
因此，我们得出结论：当起始月份发生变化时，计算终止月份的 OFFSET 函数只需要更新 2 个参数即可。这两个参数分别为向右偏移的列数以及引用区域的宽度（列数）。
并且偏移的【列数】以及返回的引用【宽度】都和【起始月份】在标题行中的位置有关：
- 偏移的【列数】即 “所选月份的位置 - 1”；
- 引用的【宽度】即 “12 - 所选月份的位置 + 1;
比如，所选月份为九月时，九月以 C2 单元格为参照，则在 C1:N2 中的索引为 9 ，偏移的【列数】就等于 9-1=8 ；引用的【宽度】就为 12-9+1=4 。
接下来，关键的一步就是获取【起始月份】在标题行中的索引，这就需要用到 MATCH 函数。
2.2 MATCH 函数
MATCH 函数的表达式为：
=MATCH(查找值, 查找区域, 查找模式)
用于返回【查找值】在【查找区域（一行或一列数据）】中的第几行（或第几列）。通常配合 OFFSET 函数使用，以确定偏移的行数 / 列数、引用的高度 / 宽度。
接下来，找一个单元格录入如下计算公式：
第一个参数【查找值】为 $O$2 ，即起始月份值所在单元格的绝对引用；第二个参数【查找区域】为 $C$2:$N$2 ，即月份标题行所在区域的绝对引用。最后一个参数为 0 ，即精确匹配。
回车之后，就得到了当前选择的起始月份在月份标题行中的索引，即第 9 列：
现在，只需要把 MATCH 函数返回的位置，嵌套到 OFFSET 函数中，将公式中向右偏移的列数 8 替换为：
MATCH($O$2,$C$2:$N$2,0)-1
向右引用的宽度（列数）4 替换为：
12-MATCH($O$2,$C$2:$N$2,0)+1
至此，就可以获取到随起始月份而变化的动态引用范围啦~
不过，这样直接嵌套会使得 OFFSET 函数变得非常复杂，并不利于我们后期的维护和重复使用。
为此，我们可以将返回位置的公式 MATCH($O$2,$C$2:$N$2,0) 定义成一个名称，这样在主要引用该公式的地方，我们就可以直接录入定义的名称啦~
2.3 将公式定义为名称
首先，复制一份计算起始月份所在位置的公式 =MATCH($O$2,$C$2:$N$2,0) ，打开【公式】选项卡，点击【定义名称】。
弹出【新建名称】对话框，录入自已定义名称，将复制的公式粘贴到【引用位置】，点击确定。
下面，我们来引用一下刚刚定义好的名称【起始月份】：
如我们所料，在 E28 单元格 =起始月份 正确返回了和调用 =MATCH($O$2,$C$2:$N$2,0) 一致的结果。
定义名称：给单元格区域或者公式取一个名字，方便调用。
下面，我们将自定义的名称应用到 OFFSET 函数：
现在，当小鱼选择不同的【起始月份】时，终止月份的范围将自动发生更新：
下面，我们就可以在 P2 单元格中制作动态更新的下拉菜单啦~
三、制作动态下拉菜单
在制作动态下拉菜单之前，我们使用【定义名称】的方法，将 OFFSET 函数也定义成一个名称。
首先，我们来检查公式的引用状态：
参考单元格 C2 在这里是固定不变的，所以我们需要按一次【F4 / Fn+F4】转换为绝对引用。
Ctrl + C 复制上述公式：
=OFFSET($C$2,0,起始月份-1,1,12-起始月份+1)
将其定义为名称，以方便引用。我们上述定义的所有名称，都可以在【公式 - 名称管理器】中进行管理：
接下来，点击 P2 单元格，选择【数据】选项卡，点击【数据验证】功能选项：
弹出【数据验证】对话框，将条件选择为【序列】，在来源中通过自定义名称【终止月份范围】来引用公式：
至此，通过 OFFSET 和 MATCH 函数动态范围引用制作的下拉菜单就完成啦~
四、总结
通过【数据 - 数据验证】可以制作下拉菜单，如果下拉菜单的范围是动态变化的，也就是制作动态下拉菜单，可以先通过 MATCH 函数获取动态变化的位置，再通过 OFFSET 函数返回动态引用的范围。最后将公式定义为名称，引用在数据验证的数据源中即可。
以上就是本文章的全部内容啦，欢迎关注小鱼，在下一篇文章中我们一起解锁 Excel 动态范围统计：根据起始月份和终止月份完成动态业绩求和。
