excel利用排列組合公式列出所有排列組合情況

2019-12-06 05:10:58

本例主要介紹如何在Excel中把兩組資料的組合全部列出來。

首先，看一下原始資料。A1和A2是兩個主等級，B1:B4是四個附屬等級，每個主等級都有四個附屬等級。需要在C列顯示出所有的組合情況。

我們考慮用INDEX函數來實現這個功能。首先來看一下INDEX第二引數如何做。

雙擊C1，輸入公式=INT((ROW(A1)-1)/4)+1，下拉可以看到1出現了4次，2也出現了4次，這是因為B列資料4次以後就需要在A列偏移一個單元格。

外面巢狀INDEX函數，輸入：

=INDEX($A$1:$A$2,INT((ROW(A1)-1)/4)+1)

下拉後就可以看到「一等」和「二等」各出現了4次。

第二列資料和第一列資料不一樣，我們需要迴圈參照B列的資料。也就是讓1,2,3,4迴圈出現。所以我們考慮用MOD函數。雙擊D1，輸入公式：

=MOD(ROW(A1)-1,4)+1

下拉後我們可以看到如下圖的效果：

同樣，外層巢狀INDEX函數，可以迴圈獲得B列的資料，4行一遍：

=INDEX($B$1:$B$4,MOD(ROW(A1)-1,4)+1)

最後，我們把公式合併在一個單元格中，就生成了最終結果組合。

=INDEX($A$1:$A$2,INT((ROW(A1)-1)/4)+1)&INDEX($B$1:$B$4,MOD(ROW(A1)-1,4)+1)