excel中的offset函数的设置

在excel中，有一些数据是需要进行动态展示和复杂计算。

动态展示最常见的就是动态图表和动态列表框，以及切片器等，尤其在数据整理中，列表框是大家应用比较多的基础功能。

今天我们就通过某外贸公司的2021年销售数据来分享一下，关于设置动态条件，并根据设置的多个条件，进行销量的汇总，以作为会议报告的销售分析指标。

在下图中，作者设置了三个条件：产品、开始时间和结束时间。

这次计算的目的，是为了得到产品在一段时间内销售情况。

那么，我们一步步来，先设置动态条件。

正如上文所说，我们通过创建一个动态列表框来设置条件。

首先我们点击A2单元格，即要设置列表的单元格地址，然后在数据工具栏中找到”数据验证“功能。

点击数据验证的下拉图标中的”数据验证“，进入设置界面。

并在界面设置中在“允许”下方的列表框中选择“序列”，如图中箭头所示，并在下方“来源”框内，输入我们要显示的产品名称区域，即A4:A18单元格区域，也就是各产品大类名称。

设置完后，点击确定。

我们在下图可以看到，A2单元格设置了一个列表框，可以点击列表中的任意内容，单元格便会以这个内容来显示。

随后再选择开始月份和结束月份下方的单元格，再次进行列表框的设置，方法步骤与上面相同，只不过在输入“来源”时，将单元格地址更换为B3:L3的月份标题行区域。

设置好动态列表框后，可以任意选择需要查询的条件。

接下来，就要根据设定的条件，开始销售数据的计算。

在下图所示，作者设置的条件是，产品为“Householdproducts”，时间段是6月至7月的销量。所以我们的思路就是先选中符合条件的单元格区域，再进行求和计算。

而在excel中，最为强大的引用函数，必然有offset函数的一席之地。

如上图中红框内的公式：=OFFSET(A3,MATCH(A2,A4:A18,0),MATCH(B2,B3:L3,0),1,MATCH(C2,B3:L3,0)-MATCH(B2,B3:L3,0)+1)

这个公式比较长，所以在图中，根据各参数进行了换行显示。

offset函数有五个参数，其表达式为：=offset（参照单元格，向下移动几行，向右移动几列，引用区域的行数，引用区域的列数）

第一参数——参照单元格：也就是说以这个单元格为参照；

第二参数——向下移动几行：从参照单元格开始，向下偏移的行数，如果为1，则向下移动1行；

第三参数——向右移动几行：从参照单元格开始，向右偏移的列数，如果为2，则向右移动2行；

第四参数——引用区域的行数：当从参照单元格开始偏移了指定的行和列数以后，再向下选定指定的行数来作为引用的区域，如果为3，则代表从参照单元格偏移了行和列之后的位置，再向下拉取3行，这3行内容便作为offset函数将要引用的单元格区域；

第五参数——引用区域的列数：与第四参数相似，只不过它是选定列数作为引用区域，如果是2，就表示再向右选择两列。

我们回到公式中，通过图片中标注的第一到第五参数，可以比较快地理清各个参数的含义。

而其中又使用了一个查找引用函数——match函数。

match函数的作用是返回指定值在一列中的位置，如第二参数的MATCH(A2,A4:A18,0)，即表示查找返回A2单元格的值在A4:A18单元格区域的位置，返回的结果以数值显示，结果是10，这样便得到offset函数第二参数，即向下移动10行。

而向下移动10行，正好是“Householdproducts”在品类名称列的位置。

第三参数的MATCH(B2,B3:L3,0)，同理，是返回B2“6月销量”在月份行中的位置，结果等于6.

第四参数的值是1，表示只选定1行区域，最后的第五参数MATCH(C2,B3:L3,0)-MATCH(B2,B3:L3,0)+1)，我们弄懂了match函数的含义，就可以清楚这个表达式的结果是C2返回的位置减去B2返回的位置，再加上1.

简单来讲，就是结束时间的月份位置减去开始时间的月份位置，也就是7月到6月，那么7减去6等于1，我们再加上1，结果为2，也就说最终选定的区域是1行2列，正是我们指定条件下要求和的销量数据。

但最后我们要注意，offset函数引用的是一个区域数据，而区域数据就是一个数组，即数据组合，数组公式要三键结束，CTRL+SHIFT+ENTER，否则公式结果会出错！在公式中，就是以大括号的形式显示。

我们可以按下F9来运行公式，看看它的计算结果，是两个值，而非单独一个值。

得到了需要计算的单元格区域，接下来进行求和，就简单多了。

数组的求和，百分之九十九都是嵌套SUM函数。

而且使用方法很简单，直接在前方嵌套一个sum函数，输入完公式后，同样以三键结束。它会对offset函数公式求得的两个值进行求和，便得到了我们需要的总销量。

我们也可以再试试其他产品和其他时间段的条件下，销量的求和，如下图所示：

综上所述，通过嵌套的数组函数，可以比较方便地计算出指定多条件下的销量总和。而动态列表框的设置，则可以更灵活地展示出不同条件下的销量统计。

学会这样的求和技巧，可以在数据统计分析中，交出一份不错的答卷。

当然，如果还会跨表多条件销量求和，或者通过动态图表来显示不同条件下的销量信息，会更为加分。

这些技巧，在之后的课程中，也会逐步与童鞋们一起分享学习。