|
今天介紹一個(gè)Excel中實(shí)際問題的解決： 如何獲得一列數(shù)據(jù)的所有組合？ 假設(shè)有下面一列數(shù)據(jù)：
如何獲得這里數(shù)據(jù)的所有組合（子集）形式？ 比如只選其中一個(gè)元素的話，有下面7種， a，b，c，d，e，f，g 選取其中兩個(gè)元素的話，有 ab，ac，ad，ae，af，ag，bc，...... 類似的形式。 這個(gè)問題很容易理解，但是要做到完全枚舉出來并不容易，因?yàn)檫@種選擇非常多。如果總共有n個(gè)元素的話，最終的不同組合數(shù)量有2的n次方（減一）之多。 仔細(xì)分析我們需要的結(jié)果，
仔細(xì)分析這個(gè)問題，我們發(fā)現(xiàn)，如果將所有元素橫向列出，并且將我們需要的元素對(duì)應(yīng)的位置標(biāo)記為1，不需要的標(biāo)記為0，那么我們就可以用一組0，1組成的串表示一個(gè)組合。 而這樣的串我們可以看作是一個(gè)二進(jìn)制數(shù)，比如：1000101，其位數(shù)就是原來元素的個(gè)數(shù)。 如果能夠?qū)⑺羞@樣的二進(jìn)制數(shù)列出，就可以根據(jù)每個(gè)二進(jìn)制數(shù)的0和1的分布，從原來的數(shù)組中取出相應(yīng)的元素，得到一個(gè)對(duì)應(yīng)的組合。 在Excel中，這個(gè)工作可以描述為下面的步驟：
那么怎么列出所有二進(jìn)制數(shù)呢。 由于我們需要的總共有2的n次方（減一）個(gè)，所以，只要先列出從1到2^n - 1的數(shù)字，然后將其轉(zhuǎn)換為對(duì)應(yīng)的二進(jìn)制數(shù)即可。 我們先以輔助列的形式完成這個(gè)工作： 首先，在E列使用下面的公式生成一個(gè)序列， =SEQUENCE(POWER(2,COUNTA(B3:B9))-1)
然后在F列使用下面的公式將對(duì)應(yīng)的數(shù)值轉(zhuǎn)換為二進(jìn)制數(shù)： =BASE(E4#,2)
注意，在Excel中，還有另外一個(gè)函數(shù)：DEC2BIN，可以將十進(jìn)制數(shù)轉(zhuǎn)為二進(jìn)制數(shù)，不過它只能完成不超過512的數(shù)字的轉(zhuǎn)換。這里使用的BASE更加靈活和方便。 然后在G列中使用下面的公式將所有的二進(jìn)制串補(bǔ)充成相同長度： =RIGHT(REPT("0",COUNTA(B3:B9))&F4#,COUNTA(B3:B9))
思路是為每一個(gè)二進(jìn)制串的左邊接上一個(gè)足夠長的全部為0的串，然后從右邊截取7位。 下面就需要復(fù)雜一些的動(dòng)作了。 首先將這個(gè)二進(jìn)制串拆分，可以使用下面的公式， MID(G4,SEQUENCE(1,7),1) 這樣就得到了一個(gè)數(shù)組， 0, 0, 0, 1, 0, 1,0 我們需要知道數(shù)字1所在的具體位置（比如，第1個(gè)，第7個(gè)等）。 這可以將上面的數(shù)組乘以下面的一個(gè)數(shù)組： 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函數(shù)得到， FILTER(SEQUENCE(1,7) *MID(G4,SEQUENCE(1,7),1),SEQUENCE(1,7) *MID(G4,SEQUENCE(1,7),1)>0) 現(xiàn)在，就可以去原數(shù)組中取出對(duì)應(yīng)的元素了： d, f 可以用INDEX函數(shù)實(shí)現(xiàn)， 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)) 這樣得到的是一個(gè)數(shù)組，將它們合成一個(gè)字符串就可以了。 這一步的完整公式是： =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)))
上面我們使用兩個(gè)多個(gè)輔助列完成這個(gè)過程。主要是便于分析問題。同時(shí)，對(duì)于使用過去版本Excel的朋友可以使用這個(gè)方法完成這樣的問題。其中的SEQUENCE和FILTER，TEXTJOIN等函數(shù)，都可以使用其他方案代替，總體思路不變。 在Office 365中，我們可以使用一個(gè)公式解決這個(gè)問題： =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)) ) ) ))其實(shí)就是將前面的各個(gè)步驟作為中間變量使用，具體過程并沒有變化。只不過最后一步使用MAKEARRAY函數(shù)返回最終結(jié)果。 當(dāng)然，你也可以將這個(gè)邏輯實(shí)現(xiàn)為一個(gè)自定義函數(shù)，只不過是一步之遙的距離。 |
|
|
