2016年10月22日 星期六

Excel-不重覆的排列組合(公式,樞紐分析表)

在 Excel 中,在下圖中有類別和項目的清單,要如何才能產生不重覆的排列組合結果(參考下圖右)?
在下圖左中,有類別:甲、乙、丙、丁,項目:忠、孝、仁、愛,要產生其不重覆的排列組合結果,該如何處理?本篇將利用二種方法來處理。
Excel-不重覆的排列組合(公式,樞紐分析表

1. 使用公式
(1) 類別欄位
儲存格D2:=OFFSET($A$2,INT((ROW(1:1)-1)/4),0)
INT((ROW(1:1):當公式向下複製時產生「0,0,0,0,1,1,1,1,2,2,2,2,3,3,3,3」。
(2) 項目欄位
儲存格E2:=OFFSET($B$2,MOD(ROW(1:1)-1,4),0)
MOD(ROW(1:1)-1,4):當公式向下複製時產生「0,1,2,3,0,1,2,3,0,1,2,3,0,1,2,3」
複製儲存格D2:E2,貼至儲存格D2:E18。

2. 使用樞紐分析表工具
如果你不喜歡使用公式來處理,也可以透過「樞紐分析表」工具來自動產生。
你可以將類別清單和項目清單,放在相同或不同的工作表中。
Excel-不重覆的排列組合(公式,樞紐分析表 image
並選取[檔案/選項]功能,在[進階]標籤下找到「編輯自訂清單」按鈕,按一下這個按鈕以新增自訂清單。
Excel-不重覆的排列組合(公式,樞紐分析表
在自訂清單中,新增:忠、孝、仁、愛。
Excel-不重覆的排列組合(公式,樞紐分析表
回到類別清單中,建立樞紐分析表,勾選:新增此資料至資料模型。
Excel-不重覆的排列組合(公式,樞紐分析表
接著,對項目清單執行上述的動作。
Excel-不重覆的排列組合(公式,樞紐分析表
在樞紐分析表的「欄位清單」方塊中切換到「所有」標籤下,將兩個範圍都勾選,並且將「類別」插入「列」中,再將「項目」插入「列」中。(注意:項目要在類別之下)
Excel-不重覆的排列組合(公式,樞紐分析表
接著,選取列標籤中的一個儲存格(使其成為作用中欄位),按一下功能表中的「欄位設定」。然後在[欄位設定]對話框中的「版面配置與列」標籤下,勾選:以列表方式顯示項目標籤,並勾選:重複項目標籤。
Excel-不重覆的排列組合(公式,樞紐分析表
再切到「小計與篩選」標籤下,在[小計]區中勾選「無」。
Excel-不重覆的排列組合(公式,樞紐分析表
目前結果如下圖:(類別和項目目前尚未排序)。接著要執行正確排序的動作。
先在「列標籤」下拉式清單中選取「更多排序選項」:
Excel-不重覆的排列組合(公式,樞紐分析表
在[排序(類別)]對話框中,選取排序選項:遞增(類別),然後按一下「更多選項」按鈕。
Excel-不重覆的排列組合(公式,樞紐分析表
選取自訂排序順序:甲,乙,丙,列, ...。
Excel-不重覆的排列組合(公式,樞紐分析表
接著選取[項目]欄位中的一個儲存格,再選取[資料/排序],並在[排序(項目)]對話框中選取「遞增:項目」,並按一下「更多選項」按鈕。
Excel-不重覆的排列組合(公式,樞紐分析表
選取自訂排序順序:忠,孝,仁,愛。
Excel-不重覆的排列組合(公式,樞紐分析表
結果如下,即為所求。可以複製到其他位置使用了。
Excel-不重覆的排列組合(公式,樞紐分析表

【延伸練習】
你能運用上述的公式或是樞紐分析表工具,產生三個變項的所有排列組合?(如下圖)
Excel-不重覆的排列組合(公式,樞紐分析表

沒有留言:

張貼留言

好康東東