Excel中生成列X所有可能组合并计算列Y对应求和的实现方法

嘿，我来帮你搞定这个需求！你需要把X列的所有非空元素组合都列出来，再算出每个组合对应的Y值总和对吧？下面给你两种实用的方法，分别适合小数据集和大数据集的情况：

方法一：手动公式（适合元素少的场景，比如你的3个元素） #

因为只有3个元素，我们可以直接枚举所有非空组合，用SUMIF或者直接相加来计算总和：

• 单个元素组合：
  • a → =SUMIF(X:X,"a",Y:Y) → 结果1
  • b → =SUMIF(X:X,"b",Y:Y) → 结果2
  • c → =SUMIF(X:X,"c",Y:Y) → 结果3
• 两个元素组合：
  • a+b → =SUMIF(X:X,"a",Y:Y)+SUMIF(X:X,"b",Y:Y) → 结果3
  • a+c → =SUMIF(X:X,"a",Y:Y)+SUMIF(X:X,"c",Y:Y) → 结果4
  • b+c → =SUMIF(X:X,"b",Y:Y)+SUMIF(X:X,"c",Y:Y) → 结果5
• 三个元素组合：
  • a+b+c → =SUM(Y:Y) → 结果6

这种方法简单直接，但如果X列元素变多，手动写公式就太麻烦了，这时候就用下面的Power Query方法。

方法二：Power Query自动生成（适合元素多的场景，扩展性强） #

Power Query可以帮你自动生成所有组合并计算总和，步骤如下：

1. 加载数据到Power Query：
   选中你的数据区域 → 点击「数据」选项卡 → 选择「从表格/区域」（记得勾选「我的表格有标题」）

2. 添加索引列：
   在Power Query编辑器里，点击「添加列」选项卡 → 「索引列」→ 「从0开始」，这样每个元素都有一个唯一的索引编号。

3. 生成组合掩码：

   • 添加自定义列，公式：=List.Numbers(1, Number.Power(2, Table.RowCount(#"添加的索引列")) -1)
     这个公式会生成从1到2^n -1的数字（n是元素总数，这里n=3，所以生成1-7），每个数字对应一个非空组合。
   • 再添加一个自定义列，把数字转成固定长度的二进制字符串：=Text.PadStart(Number.ToText([自定义], "B"), Table.RowCount(#"添加的索引列"), "0")
     比如数字3会转成"011"，每一位对应一个元素是否被选中（1表示选中，0表示不选）。

4. 筛选选中的元素并计算：

   • 添加自定义列，把二进制字符串拆成列表：=Text.ToList([自定义.1])
   • 再添加自定义列，把原数据的X、Y列和二进制列表打包：=List.Zip({Table.Column(#"添加的索引列", "X"), Table.Column(#"添加的索引列", "Y"), [自定义.2]})
   • 筛选出二进制位为"1"的元素：=List.Select([自定义.3], each _{2} = "1")
   • 提取组合名称：=Text.Combine(List.Transform([自定义.4], each _{0}), "+")
   • 计算组合的Y值总和：=List.Sum(List.Transform([自定义.4], each _{1}))

5. 整理结果并加载回Excel：
   移除所有中间列，只保留「组合名称」和「Y值总和」列，然后点击「关闭并上载」，结果就会出现在新的工作表里啦！

这样不管X列有多少元素，都能自动生成所有非空组合和对应的总和，非常高效。

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