【原】如何获得一列数据的所有组合：Excel函数式编程

---

今天介绍一个Excel中实际问题的解决：

如何获得一列数据的所有组合？

问题

---

假设有下面一列数据：

如何获得这里数据的所有组合（子集）形式？

比如只选其中一个元素的话，有下面7种，

a，b，c，d，e，f，g

选取其中两个元素的话，有

ab，ac，ad，ae，af，ag，bc，......

类似的形式。

分析

---

这个问题很容易理解，但是要做到完全枚举出来并不容易，因为这种选择非常多。如果总共有n个元素的话，最终的不同组合数量有2的n次方（减一）之多。

仔细分析我们需要的结果，

仔细分析这个问题，我们发现，如果将所有元素横向列出，并且将我们需要的元素对应的位置标记为1，不需要的标记为0，那么我们就可以用一组0，1组成的串表示一个组合。

而这样的串我们可以看作是一个二进制数，比如：1000101，其位数就是原来元素的个数。

如果能够将所有这样的二进制数列出，就可以根据每个二进制数的0和1的分布，从原来的数组中取出相应的元素，得到一个对应的组合。

在Excel中，这个工作可以描述为下面的步骤：

• 首先，列出所有组合对应的二进制数

• 然后根据二进制数从原数组中获取相应的结果

那么怎么列出所有二进制数呢。

由于我们需要的总共有2的n次方（减一）个，所以，只要先列出从1到2^n - 1的数字，然后将其转换为对应的二进制数即可。

实现：辅助列

---

我们先以辅助列的形式完成这个工作：

首先，在E列使用下面的公式生成一个序列，

=SEQUENCE(POWER(2,COUNTA(B3:B9))-1)

然后在F列使用下面的公式将对应的数值转换为二进制数：

=BASE(E4#,2)

注意，在Excel中，还有另外一个函数：DEC2BIN，可以将十进制数转为二进制数，不过它只能完成不超过512的数字的转换。这里使用的BASE更加灵活和方便。

然后在G列中使用下面的公式将所有的二进制串补充成相同长度：

=RIGHT(REPT("0",COUNTA(B3:B9))&F4#,COUNTA(B3:B9))

思路是为每一个二进制串的左边接上一个足够长的全部为0的串，然后从右边截取7位。

下面就需要复杂一些的动作了。

首先将这个二进制串拆分，可以使用下面的公式，

MID(G4,SEQUENCE(1,7),1)

这样就得到了一个数组，

0, 0, 0, 1, 0, 1,0

我们需要知道数字1所在的具体位置（比如，第1个，第7个等）。

这可以将上面的数组乘以下面的一个数组：

1, 2, 3, 4, 5, 6, 7

两步合成，就是下面的公式：

SEQUENCE(1,7) *MID(G4,SEQUENCE(1,7),1)

这样就得到了，

0, 0, 0, 4, 0, 6, 0

去掉0，只保留大于0的位置，即：

4, 6

这可以通过filter函数得到，

FILTER(SEQUENCE(1,7) *MID(G4,SEQUENCE(1,7),1),SEQUENCE(1,7) *MID(G4,SEQUENCE(1,7),1)>0)

现在，就可以去原数组中取出对应的元素了：

d, f

可以用INDEX函数实现，

INDEX($B$3:$B$9,FILTER(SEQUENCE(1,7) *MID(G4,SEQUENCE(1,7),1),SEQUENCE(1,7) *MID(G4,SEQUENCE(1,7),1)>0))

这样得到的是一个数组，将它们合成一个字符串就可以了。

这一步的完整公式是：

=TEXTJOIN(",",,INDEX($B$3:$B$9,FILTER(SEQUENCE(1,7) *MID(G4,SEQUENCE(1,7),1),SEQUENCE(1,7) *MID(G4,SEQUENCE(1,7),1)>0)))

用一个公式解决问题

---

上面我们使用两个多个辅助列完成这个过程。主要是便于分析问题。同时，对于使用过去版本Excel的朋友可以使用这个方法完成这样的问题。其中的SEQUENCE和FILTER，TEXTJOIN等函数，都可以使用其他方案代替，总体思路不变。

在Office 365中，我们可以使用一个公式解决这个问题：

=LET(    data, B3:B9,    len, COUNTA(data),    num, SEQUENCE(POWER(2, len) - 1),    bin, BASE(num, 2),    binstr, RIGHT(REPT("0", len) & bin, len),    MAKEARRAY(        POWER(2, len),        1,        LAMBDA(r, c,            LET(                cur_binstr, INDEX(binstr, r),                split_binstr, SEQUENCE(1, len) * MID(cur_binstr, SEQUENCE(1, len), 1),                filter_split_binstr, FILTER(split_binstr, split_binstr > 0),                TEXTJOIN(",", , INDEX(data, filter_split_binstr))            )        )    ))

其实就是将前面的各个步骤作为中间变量使用，具体过程并没有变化。只不过最后一步使用MAKEARRAY函数返回最终结果。

当然，你也可以将这个逻辑实现为一个自定义函数，只不过是一步之遥的距离。