2015年10月30日 星期五

Excel-報名資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)

當在 Excel 中取得一個運動會的報名表(如下圖),除了各班的所有學生基本資料之後,還有各個比賽項目,其中的值為 TRUE 者代表有報名,FALSE 代表沒有報名。
如何能快速計算各個比賽項目的各班男生/女生的參數人數?
Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)

1. 使用樞紐分析表和交叉分析篩選器
以跳高項目為例,當你插入一個樞紐分析表,設定如下:
將欄設為:『性別』欄位;列設為:『班級編碼』欄位;值設定:『跳高』欄位。
你看到的只是各班男生人數和女生人數,並無法呈現 TRUE(報名) 的數量。
Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)
所以你要選取[分析/篩選]功能區中的「插入交叉分析篩選器」。
接著,勾選「跳高」:
Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)
當你按一下「TRUE」,此時顯示的數值即為各班男生/女生的報名人數。
Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)

2. 設計公式來計算
以比賽項目:跳高為例,先將『班級編碼』、『性別』、『跳高』三個欄位的所有資料,定義名稱:班級編碼、性別、跳高。
Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)
輸入公式:
儲存格O2:=SUMPRODUCT((班級編碼=$N2)*(性別=O$1)*(跳高=TRUE))
(班級編碼=$N2)*(性別=O$1)*(跳高=TRUE):「*」運算可以將三個條件執行邏輯AND 運算,即三個條件皆符合者,才會取出來計算乘積和。而在「*」運算時,會將傳回值 TRUE/FALSE 結果轉換為 1/0
提醒:『跳高=TRUE』不可寫出『跳高="TRUE"』,TRUE 在此是一個布林代數值,而非文字 TRUE。
然後,複製儲存格O2,貼至儲存格O2:P48。

【進階處理】
如果想要一次就取得所有比賽項目的各班男生/女生的參賽人數,該何處理?(參考下圖)
Excel-報名表資料處理(SUMPRODCUT,INDIRECT,樞紐分析表和交叉分析篩選器)
設計公式前要注意:運算人數的資料表欄位稱必須要和原始資料的欄位名稱完全相同。
接,除了已經定義的名稱:班級編碼、性別之外,再將本例中的『一百、跳高、跳遠、一仟五、聖火隊、助理裁判』每個欄位的所有資料,定義名稱:一百、跳高、跳遠、一仟五、聖火隊、助理裁判。
設計公式,儲存格P2:
=SUMPRODUCT((班級編碼=$N2)*(性別=$O2)*(INDIRECT(P$1)=TRUE))
INDIRECT(P$1)=TRUE:其中 INDIRECT(P$1),巧妙的將儲存格P1中的欄位名稱轉換為實際儲存格範圍(其中每個欄位中的儲存格範圍都已先定義好名稱了)。
然後,複製儲存格P2,貼至儲存格P2:U4。

【後記】
你有發現嗎?只要一個公式即能求出各班的男生/女生參加各個比賽的人數,其實是有一些巧妙的安排的,例如:
(1) O欄中『男』和『女』是分二列安排。
(2) 運算人數的資料表欄位稱和原始資料的欄位名稱完全相同。

沒有留言:

張貼留言

檢視其他文章

好康東東