今天介绍一个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函数返回最终结果。
当然,你也可以将这个逻辑实现为一个自定义函数,只不过是一步之遥的距离。
联系客服