Excel 2024/365新函数系列讲座(35):LET函数(4)—— 综合应用案例,一个综合公式搞定汇总计算

下面是一个综合应用案例，要求设计一个综合公式，直接以原始数据区域（A列至C列），制作每个部门、每个项目的发生额汇总表，结果如图右侧的报表所示。

如果以常规的方法和常规函数来解决这样的问题，首先要设计两个辅助列“部门”和“项目”，然后再使用数据透视表进行汇总（这种方法，我将在明晚的视频号直播中进行详细介绍，具体视频号链接请看把今天发布的第2篇文章）。

如果能有使用最新函数，那么就可以设计一个综合公式制作汇总表，省略了中间的辅助列设计过程，大大提升数据处理效率，参考公式如下：

=LET(

科目代码区域, A3:A74,

科目名称区域, B3:B74,

金额区域, C3:C74,

填充代码, SCAN(,科目代码区域,LAMBDA(acc,cell,IF(cell<>"",cell,acc))),

项目数组, XLOOKUP(填充代码,填充代码,科目名称区域),

部门数组, IF(科目代码区域="",MID(科目名称区域,5,100),""),

PIVOTBY(部门数组,项目数组,金额区域,SUM,0,1,,1,,部门数组<>"")

)

这个公式的基本逻辑，仍然是设计辅助数组，分别提取生成部门数组和项目数组（也就是在工作表上设计辅助列，只不过这里将辅助列以数组形式设计到了公式里），最后再使用PIVOTBY函数进行汇总。

由于是数组的一系列处理计算，因此使用LET函数增强公式阅读性。

此外，公式使用了SCAN函数和LAMBDA函数来处理A列科目代码填充问题，因为我们需要依据科目代码来处理生成项目数组。

关于SCAN函数和LAMBDA函数，后面的陆续文章，会做一些介绍。