资料文库

动态数组的魅力，用一个 Excel 函数同时实现多种计算

office

如果你有 365 版本的 Excel，是否物尽其用了呢？比如，对一个区域进行多种公式计算，其实只写一个公式就能同时完成。

案例：

对下图 1 中每个月的业绩进行计数、计算平均值和求和。

效果如下图 2 所示。

图片

图片

解决方案：

  1. 在 C17 单元格中输入以下公式 –> 回车：

=SUBTOTAL({2;1;9},OFFSET(C2:C16,,{0,1,2}))

图片

图片

公式释义：

  • subtotal 函数能够依据特定的功能代码，对数据列表或数据区域进行分类汇总；

  • 语法为 SUBTOTAL(功能代码,区域1,[区域2],…)；

  • 功能代码对照表如下：

图片

  • {2;1;9}：

  • 根据上述对照表可以看出，2、1、9 分别对应的函数功能是 count、average 和 sum；

  • 用 {} 使之成为数组；

  • 数字间用“;”符号隔开，表示数组中的每个元素作用于一行；也就是一行进行一种计算；

  • OFFSET(C2:C16,,{0,1,2})：

  • offset 的作用是返回对单元格或单元格区域中指定行数和列数的区域的引用；

  • 语法为 OFFSET(要基于其偏移量的引用, 要偏移的行数, 要偏移的列数, [要返回的引用的行高], [要返回的引用的列宽])；

  • 公式表示引用 C2:C16 区域，用第三个参数分别向右偏移 0 列、1 列、2 列，也就是依次对每一列进行计算；

  1. 调整公式区域的数值格式。

图片

图片