专栏名称: 完美Excel

Excel与VBA技术学习与实践

用动态数组来增强SUBTOTAL函数的汇总处理

完美Excel · 公众号 · Excel · 2024-12-06 06:09

正文

学习Excel技术，关注微信公众号：

excelperfect

标签：Excel函数，SUBTOTAL函数，LET函数

在SUBTOTAL函数与SEQUENCE函数组合应用中，我们利用Excel动态数组功能实现了一次性利用SUBTOTAL函数的11个函数及其计算结果。然而，其不便之处在于不知道公式中的值具体意味着什么，还需要单独列出函数名称，如下图1所示。

图1

其实，我们可以使用LET函数组合函数名及其结果并将它们放置在两列中输出。

首先使用TEXTJOIN函数来合并代表这11个函数名称的字符串，如下图2中的单元格C21中的公式：

=TEXTJOIN(",",,C3:C13)

将公式结果复制到单元格C23中并只粘贴值。

图2

然后，复制单元格C23中的字符串并准备在下面的公式中使用。

在单元格L3中输入公式：

=LET(f,"AVERAGE,COUNT,COUNTA,MAX,MIN,PRODUCT,STDEV,STDEVP,SUM,VAR,VARP",fs,TEXTSPLIT(f,,","), fs)

注意到，其第2个参数就是我们上面复制的代表函数名称的字符串，以逗号为分隔符。公式的结果如下图3所示，结果fs是这11个函数名的垂直列表。TEXTSPLIT函数转换以逗号作为分隔符的字符串为垂直列表。

图3

下面是单元格L3中最后的完整的公式：

=LET(f,"AVERAGE,COUNT,COUNTA,MAX,MIN,PRODUCT,STDEV,STDEVP,SUM,VAR,VARP",

fs,TEXTSPLIT(f,,","),

sub,SUBTOTAL(SEQUENCE(11),F3:F18),

HSTACK(fs,sub))

变量fs是代表11个函数名称的垂直列表；变量sub包含了这11个函数的计算结果。函数HSTACK将这两个垂直列表合并成两列的表，其第1列是函数名，第2列是对应的计算结果，如下图4所示。

图4

欢迎在下面留言，完善本文内容，让更多的人学到更完美的知识。

欢迎到知识星球：完美Excel社群，进行技术交流和提问，获取更多电子资料，并通过社群加入专门的微信讨论群，更方便交流。

推荐文章

Excel之家ExcelHome · 这些函数都学好，领导把你当成宝

3 天前

Excel之家ExcelHome · 这些公式全学会，天天加班无所谓

5 天前

Excel之家ExcelHome · 高级筛选用得好，年终奖金少不了

6 天前

Excel之家ExcelHome · 小白妙用AI和Python搞定办公自动化

1 周前

Excel之家ExcelHome · 这些公式用不好，天天加班做报表

1 周前

健康养身 · 它是“药草皇后”，泡水喝护肝肾强免疫，春天吃最好！

7 年前

车买买 · 10-20万能买到自主SUV中的昂科威锐界汉兰达，你不考虑下？

7 年前

精读 · 如何活出不设限的人生，开启无限可能？

7 年前

立德融金集团 · 吴钩：宋代一起“刺死辱母者”事件

7 年前

HUGO · 世界正在惩罚晚睡的人

7 年前