Excel 二维表的列动态求和，这两个经典的组合公式不教怎么行

前一篇推文我教了大家如何根据列出的条件对二维表的特定列求和，用到了两个非常经典的函数，所以公式异常短小精悍。

有同学提问是不是还有其他方法？当然有，二维表查找交叉位置，我们怎可能不提两个经典的组合公式？

案例：

下图 1 是部门销售人员的的各项业绩表，请根据右侧数据表中列出的求和项，求对应列的总和。

效果如下图 2 所示。

解决方案：

1. 在 H2 单元格中输入以下公式：

=SUM(INDEX(B2:E10,,MATCH(H1,B1:E1,0)))

公式释义：

  • index+match 是比较经典的查找行列交叉位置的组合公式，相关案例可参阅
  • index 的参数含义为 index(需要查找的区域,定位的行值,定位的列值)；
  • 因为参数中的行值为必需，而本例是对整列求和，不需要定位行值，因此第二个参数虽然为空，但位置要留出来；
  • MATCH(H1,B1:E1,0)：
    • 放在 index 的第三个参数处，用于定位列值；
    • 表示在 B1:E1 区域中精确查找 H1 并返回其在区域中的相对位置，结果为一个数值；
  • 最后用 sum 对整个查找区域求和，即可达成目的

2. 在 I2 单元格中输入以下公式：

=SUM(OFFSET(A2:A10,,MATCH(I1,B1:E1,0)))

公式释义：

  • offset 是个偏移函数，用于返回对单元格或单元格区域中指定行数和列数的区域的引用；
  • offset 的参数含义为 OFFSET(基准位置, 要偏移的行数, 要偏移的列数, [引用区域的高度], [引用区域的宽度])；
  • OFFSET(A2:A10,,MATCH(I1,B1:E1,0))：以 A2:A10 为起始位置，行数不变，向右偏移 MATCH(I1,B1:E1,0) 列，相当于定位到“复购数”列的每一个数值；
    • MATCH(I1,B1:E1,0)：计算 B1:E1 区域中与 I1 完全匹配的单元格的相对位置，得到一个数值；
  • SUM()：最后用 sum 函数对上述列求和，就是最终结果

有关 offset 函数的案例，请参阅

很多同学会觉得 Excel 单个案例讲解有些碎片化，初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学，从最基础的概念开始，一步步由简入繁、从入门到精通，系统化地讲解 Excel 的各个知识点。

现在终于有了，以下专栏，从最基础的操作和概念讲起，用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本，你也能成为 Excel 高手。

赞 (0)

相关推荐