贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

在 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-不重覆的排列組合(公式,樞紐分析表

創作者介紹

學不完.教不停.用不盡

vincent 發表在 痞客邦 PIXNET 留言(1) 人氣()


留言列表 (1)

發表留言
  • 悄悄話
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼