1职场实例

小伙伴们大家好，今天我们来讲解一个Excel职场中经典的实例应用场景：如何根据列标题，对数据源任意整列求和？这个问题的难点在于如何根据不同的列标题动态锁定其整列的区域，进而对该列数据进行快速的求和。该问题包含了Excel中几个常见的函数公式，并配合了使用率极高的数组思维，把复杂的问题简单化了。

如下图所示：

A1:D4为数据源区域，为每位销售员在不同季度的销量数据，A列为姓名行标题，首行为各列的季度列标题，中间为销量值数据，数据源为二维表格式。我们想要通过B8:B9区域的季度数据，在C8:C9列获取数据源中该列的整列销量和。

有些小伙伴用固性思维第一时间想到的是SUM函数。在C8单元格输入函数：

=SUM(B2:B4)

但是发现返回结果，只有C8单元格的求和结果是正确的，下拉填充公式至C9单元格时就不对了，原因是求和的引用区域由B2:B4变成了B3:B5，而我们想要的是变成3季度列D2:D4，所以它不能根据列标题动态锁定指定列。

2解题思路

今天的这个问题，我们需要使用函数SUMPRODUCT函数、INDEX函数、MATCH函数。三个函数配合嵌套使用，运用巧妙的数组思维原理，快速解决这个难题，而最值得我们关注的是INDEX函数的第1参数是区域的情形，对此类问题的帮助。

SUMPRODUCT 函数先计算多个数组的元素之间的乘积再求和。

INDEX 函数通常用来查找数据，根据指定的行和列找到某单元格的内容。

MATCH 函数是一种查找函数，用来某值在某单元格区域的相对位置。比如，某数字在某单元格范围的第几行。

下面我们就来看一下具体操作方法。

首先我们在C8单元格输入函数公式：

=MATCH(B8,$B$1:$D$1,0)

MATCH函数是一种查找函数，用来某值在某单元格区域的相对位置。比如，某数字在某单元格范围的第几行。

MATCH函数用通俗易懂的方式可以表示为：

=MATCH(要查找的数据, 查找区域, 查找方式)

第一参数：你想查找的值

第二参数：可能包含有所要查找数值的连续的单元格区域，仅支持单行或单列，不支持多行/多列。

第三参数：排列顺序（选填），1 为默认类型，此时假设范围已按升序排序，返回小于等于搜索键值的最大值。0 表示完全匹配，在范围未排序的情况下需要使用此方式。-1 是指假设范围是按降序排序时，返回大于等于搜索键值的最小值。

本例中我们利用MATCH函数查找B8单元格中的“1季度”在查找区域B1:D1中的相对位置为1，采用的精确匹配，所以第3参数用0表示。

我们用上一步的MATCH函数作为INDEX函数的第3参数，并且省略第2参数：

=INDEX($B$2:$D$4,,MATCH(B8,$B$1:$D$1,0))

INDEX用于返回表格或区域中的值或值的引用。

基本语法结构是：

=INDEX(数组或区域,行号,列号)

第一参数：必需。单元格区域或数组常量。

第二参数：可选。指定区域中的相对行号。

第三参数：可选。指定区域中的相对列号。

第一参数可以是单行或单列，也可以是多行多列。如果第一参数是单行，则可以省略第二参数，如果第一参数是单列，则可以省略第三参数。如果第一参数是多行多列，省略第二参数（行），则返回整列，结果也是数组；省略第三参数（列），则返回整行，结果也是数组。

本例中，我们用INDEX函数，返回区域B2:D4区域中，上一步MATCH(B8,$B$1:$D$1,0)返回的第1列即B2:B4列的整列数据{40;30;45}，原因就是INDEX函数省略了第2参数，所以返回了B2:D4区域中的整列，结果以数组显示。

最后我们只需要用SUMPRODUCT函数，对上一步中返回的数组求和即可：

=SUMPRODUCT(INDEX($B$2:$D$4,,MATCH(B8,$B$1:$D$1,0)))

即=SUMPRODUCT({40;30;45})=40+30+45=115