专栏名称: 完美Excel

Excel与VBA技术学习与实践

动态数组实现SUBTOTAL函数进行完整数据处理

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

正文

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

excelperfect

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

在SUBTOTAL函数与SEQUENCE函数组合应用中，使用Excel动态数组一次性汇总所有数据的各种结果；在用动态数组来增强SUBTOTAL函数的汇总处理中，实现了一次性利用SUBTOTAL函数的11个函数，列出函数名及其对应的计算结果；在自定义函数实现SUBTOTAL函数的全部功能中，使用LAMBDA函数来自定义一个函数，以简化前面文章中示例的长而复杂的公式，实现数据的快速汇总。本文将在前面的基础上，进一步修改完善，添加忽略隐藏行的计算及标题。

首先，在原结果上增加一列，即忽略隐藏行后的汇总结果，在单元格O3中输入公式：

=LAMBDA(rng,LET(f,"AVERAGE,COUNT,COUNTA,MAX,MIN,PRODUCT,STDEV,STDEVP,SUM,VAR,VARP",fs,TEXTSPLIT(f,,","),sub_1,SUBTOTAL(SEQUENCE(11),rng),sub_2,SUBTOTAL(SEQUENCE(11)+100,rng),HSTACK(fs,sub_1,sub_2)))(F3:F18)

结果如下图1所示。

图1

在原公式的基础上，添加了一个变量来代表忽略隐藏行的结果，修改HSTACK函数包括新的变量名。

接下来，添加标题。

VSTACK函数允许在单元格区域中添加行，示例中在每列顶部添加标题。在单元格O2中输入公式：

=LAMBDA(rng,

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

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

sub_1,SUBTOTAL(SEQUENCE(11),rng),

sub_2,SUBTOTAL(SEQUENCE(11)+100,rng),

HSTACK(VSTACK("函数",fs),VSTACK("所有行",sub_1),VSTACK("忽略隐藏行",sub_2))))(F3:F18)

结果如下图2所示。

图2

上面的公式可以稍作修改：

=LAMBDA(rng,

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

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

sub_1,SUBTOTAL(SEQUENCE(11),rng),

sub_2,SUBTOTAL(SEQUENCE(11)+100,rng),

VSTACK({"函数","所有行","忽略隐藏行"},HSTACK(fs,sub_1,sub_2))))(F3:F18)

其中，{"函数","所有行","忽略隐藏行"}创建行标题，放置在HSTACK创建的数据表之上。

现在，公式已经测试完毕，我们可以将其自定义函数以简化使用。使用定义名称功能来自定义函数，如下图3所示。

图3

使用自定义函数的结果如下图4所示。

图4

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

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

推荐文章

完美Excel · 自动生成操作工作表的通用代码

3 天前

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

4 天前

Excel之家ExcelHome · Excel中双击的妙用

5 天前

Excel之家ExcelHome · Excel版简易日历，其实很简单

1 周前

Excel之家ExcelHome · 计算排名和年龄，典型公式请收好

6 天前

下厨房 · 进口 | 欢乐颂同款麦片、卡乐比、榴莲饼……全球最火零食大囤货

8 年前

她刊 · 刘涛一路“攻”到巴黎时装周，38岁照样美成20+穿衣偶像！

8 年前

营销兵法 · 《速度与激情8》中国票房远超北美老家，咪咕同名手游功不可没？

7 年前

新闻夜航 · 剖开肚子，流出的血都是粉色的！婆婆差点用这道菜“害死”儿媳和孙子！

7 年前

不正常人类研究中心 · 为什么蟹老板的女儿会是抹香鲸？

7 年前