基于数组的Excel二维分类汇总单单元格公式解决方案

需求概述 #

本方案是一维数组分类汇总方案的扩展，需通过单单元格纯Excel公式实现以下目标：

• 无需VBA或硬编码单独引用
• 基于指定的Label2参数数组（示例：{"A","B","C"}）和Label4参数数组（示例：{"X","Y","Z"}，可由FILTER()输出）
• 聚合表格中对应Label3的数值，生成二维分类汇总动态数组，预期输出为：{{1,8,0},{0,3,0},{0,5,23}}

公式实现 #

假设源数据存储在结构化表格Table1中，包含列Label2、Label3、Label4，可使用以下公式：

=MAKEARRAY(ROWS({"A","B","C"}), COLUMNS({"X","Y","Z"}), 
 LAMBDA(r,c, 
  SUMPRODUCT(
   --(Table1[Label2] = INDEX({"A","B","C"}, r)),
   --(Table1[Label4] = INDEX({"X","Y","Z"}, c)),
   Table1[Label3]
  )
 )
)

公式说明 #

1. MAKEARRAY：创建与参数数组匹配行列数的动态数组，行数由Label2参数数组的行数决定，列数由Label4参数数组的列数决定
2. LAMBDA(r,c)：遍历数组的每个单元格位置，r为当前行索引，c为当前列索引
3. SUMPRODUCT：实现双条件求和：
   • --(条件)将布尔判断结果转换为0/1数值数组
   • 三个数组（Label2匹配数组、Label4匹配数组、Label3数值数组）相乘后求和，得到对应Label2和Label4组合的Label3总和
4. 参数数组替换：如果Label2/Label4的参数数组由FILTER()生成，直接将公式中的{"A","B","C"}和{"X","Y","Z"}替换为对应的FILTER()公式即可

验证与适配 #

该公式完全满足前置条件：

• 纯公式实现，无任何VBA代码
• 所有逻辑封装在单个单元格中，无需额外的辅助单元格或硬编码引用
• 动态适配参数数组的变化，当FILTER()输出的参数数组更新时，汇总结果会自动同步

内容的提问来源于stack exchange，提问作者mintti