[EXCEL] 顯示組合清單

　
　　相關函數：INDEX() / ROW() / MOD() / INT()

　　如下圖，我們有四種水果，三種包裝尺寸，兩種包裝方法，想要列出一個清單能包含各種組合狀況，如何設公式？

##CONTINUE##

　　根據排列組合原理，共有 4*3*2=24 種組合狀況，使用下面的通用公式，我們可以利用 Excel 快速的把這二十四種組合狀況的清單列出來。

　　=INDEX(顯示資料範圍,1,
　　　MOD(INT((ROW(A1)-1)/(低階資料個數乘積)),顯示資料個數)
　　　+1)

　　以水果清單 A6 的公式為例

　　=INDEX($B$1:$E$1,1,MOD(INT((ROW(A1)-1)/(3*2)),4)+1)

• 顯示資料範圍：即為水果種類所在範圍 $B$1:$E$1，因為稍後要直接往下複製，所以必須為絕對位置。
• 低階資料個數乘積：所謂低階資料，就是表格右側的資料，也就是尺寸種類 3，和包裝種類 2 的乘積。
• 顯示資料個數：即為水果種類 4。

　　同理，尺寸清單 B6 的公式如下

　　=INDEX($B$2:$D$2,1,MOD(INT((ROW(A1)-1)/(2)),3)+1)

　　同理，包裝清單 C6 的公式如下，沒有低階資料時，乘積直接填 1

　　=INDEX($B$3:$C$3,1,MOD(INT((ROW(A1)-1)/(1)),2)+1)

　　然後，再把 A6:C6 直接往下複製即可。

　　好了，現在你有了二十四種組合狀況的清單，可以幹活了....幫每種組合狀況定個價錢如何？

2008/5/30 補充說明：

　　針對公式，做較深入的說明。其實，所謂排列組合的清單，說穿了，只是做兩件事：

　　第一，找出這是第幾輪的資料，也就是從資料清單中要挑第幾個資料出來顯示。以範例來說，第一層的第一輪要出現蘋果，第二輪要出現香蕉。

　　第二，找出低一層的資料個數乘積，也就是同一輪的資料要顯示幾次。以範例來說，第一層的第一輪要出現蘋果的次數，是第二層尺寸３＊第三層包裝２＝６次

　　再回頭看公式，MOD()+1，就是做第一件事。INT(ROW(A1)-1/乘積)，就是做第二件事。再進一步看公式：

　　ROW(A1)-1：是要把序號 1-24，變成 0-23，這樣待會兒在做 INT(序號/低階資料個數乘積) 時，才會得到正確的結果。例如，位置 A10 和 A11 是第五和第六個組合，序號 5，6，應該都顯示第一輪蘋果，但是如果做 INT(5/6)=0，INT(6/6)=1，會顯示不同的資料。為了把序號 5 和 6 落在同一個正確的位置，所以要先減一才行。

　　而 MOD(...)+1，則是因為 ROW(A1)-1 後，得到的資料會從 0 算起，0，1，2，3，而 INDEX() 要求的是 1，2，3，4，所以再 +1 補回來。