查询更高效 Excel动态求和更简单

使用函数进行统计

在Excel中我们一般使用SUM函数求和，比如现在需要统计图1中产品6的4～7月份的销售数据，使用公式“=SUM（E7：H7）”即可。不过，这里的求和数据区域E7：H7根据查询需求条件的数值而动态变化。这类动态求和的关键是，首先要确定求和的品名，接着根据品名选择求和的数据区域，最后使用SUM函数进行求和。对于求和区域的选择，可以借助OFFSET函数来进行动态引用，OFFSET函数需要设置起始位置、行偏移数、列偏移数、求和数据区域的高度和宽度等五个参数。

1设置品名和月份选择

为了方便对数据进行查询，这里我们先使用数据验证来设置品名和月份选择。定位到B19单元格，依次点击“数据→数据验证→设置→序列”，然后选择数据来源区域为A2：A9，这样在B19单元格中通过下拉列表就可以方便地选择品名了（图2）。

操作同上，在N1：N12区域中依次填充数字1～12，在B20、D20单元格中也使用数据验证，“来源”选择N1：N12区域，这样在需要查询品名及其月份的数据时，可以通过下拉列表进行快速选择。

2确定起始位置参数

在数据求和区域A1：M9中，A1就是起始位置，因此第一个参数即为A1。

3确定行偏移数

定位到E20单元格并输入公式“=MATCH（B19，A2：A9，0）”，公式的意思是使用MATCH函数定位B19单元格输入的品名在A2：A9区域中的实际行号。比如B19是输入产品6，通过函数可以确定这个品名在A2：A9区域中是在第6行，因此函数的引用结果为数字6，将其作为OFFSET函数的第二个参数值（图3）。

4确定列偏移数

同理在F20单元格中输入公式“=MATCH（B20&"月"，B1：M1，0）”，表示以B20单元格中输入的数字为基准，将其和“月”字连接，然后在B1：M1月份区域中查找其偏移行数。比如B20输入的是“4”（即4月），其在月份区域为向右偏移4列。

5确定数据区域的高度和宽度

根據求和条件可以知道，这里求和数据的高度是“1”（即只对指定品名一行数据求和），求和宽度则是“=D20-B20+1”，即“终止月份-初始月份+1”，比如输入的是4～7月份，就是求“7-4+1=4”，即4、5、6、7这四个月份的数据。

6制作求和公式

通过上面的方法，我们确定了OFFSET函数的所有参数。继续定位到G20单元格并输入公式“=SUM（OFFSET（A1，E20，F20，1，D20-B20+1））”，即使用SUM函数对OFFSET函数引用的区域进行求和（图4）。

这样我们需要进行动态求和时，只要在B19单元格中下拉列表选择品名，在B20、D20单元格中选择起始月份，在G20单元格中就可以快速进行动态求和了。了解了上述的参数设置后，为了方便使用，也可以在E20单元格中直接输入嵌套公式“=SUM（OFFSET（A1，MATCH（B19，A2：A9，0），MATCH（B20&"月"，B1：M1，0），1，D20-B20+1））”，这样即可快速求和（图5）。

使用透视表汇总求和

虽然通过函数的方法对动态区域进行求和很方便，但OFFSET函数只能对连续的动态区域进行引用。如果求和需要引用的是不连续的区域，比如要统计产品2、4及其4、6、9月份的销售数据，求和函数的设置就非常复杂，对于这类求和，可以使用数据透视表来实现。

首先在原始数据表中选择A1：M9区域，然后依次点击“插入→数据透视表”，在弹出的窗口中选择A12单元格放置透视表数据（图6）。

点击“确定”后，在A12单元格中就可以看到透视表了。在右侧的透视表窗格中，按提示将“品名”字段拖拽到下方的筛选区域，将Σ数值拖拽到列区域，勾选任意月份数据，求和项会自动出现在Σ值区域中（图7）。

这样，当我们需要统计产品2、4及其4、6、9月份的销售数据时，只要在B12单元格的品名筛选列表中勾选产品2、4，在求和项里勾选4、6、9月份的数据，即可完成求和了（图8）。