當在 Excel 中取得一個運動會的報名表(如下圖)，除了各班的所有學生基本資料之後，還有各個比賽項目，其中的值為 TRUE 者代表有報名，FALSE 代表沒有報名。
如何能快速計算各個比賽項目的各班男生／女生的參數人數？
1. 使用樞紐分析表和交叉分析篩選器
以跳高項目為例，當你插入一個樞紐分析表，設定如下：
將欄設為：『性別』欄位；列設為：『班級編碼』欄位；值設定：『跳高』欄位。
你看到的只是各班男生人數和女生人數，並無法呈現 TRUE(報名) 的數量。
所以你要選取[分析／篩選]功能區中的「插入交叉分析篩選器」。
接著，勾選「跳高」：
當你按一下「TRUE」，此時顯示的數值即為各班男生／女生的報名人數。
2. 設計公式來計算
以比賽項目：跳高為例，先將『班級編碼』、『性別』、『跳高』三個欄位的所有資料，定義名稱：班級編碼、性別、跳高。
輸入公式：
儲存格O2：=SUMPRODUCT((班級編碼=$N2)*(性別=O$1)*(跳高=TRUE))
(班級編碼=$N2)*(性別=O$1)*(跳高=TRUE)：「*」運算可以將三個條件執行邏輯 AND 運算，即三個條件皆符合者，才會取出來計算乘積和。而在「*」運算時，會將傳回值 TRUE／FALSE 結果轉換為 1／0。
提醒：『跳高=TRUE』不可寫出『跳高="TRUE"』，TRUE 在此是一個布林代數值，而非文字 TRUE。
然後，複製儲存格O2，貼至儲存格O2:P48。
【進階處理】
如果想要一次就取得所有比賽項目的各班男生／女生的參賽人數，該何處理？(參考下圖)
設計公式前要注意：運算人數的資料表欄位稱必須要和原始資料的欄位名稱完全相同。
接，除了已經定義的名稱：班級編碼、性別之外，再將本例中的『一百、跳高、跳遠、一仟五、聖火隊、助理裁判』每個欄位的所有資料，定義名稱：一百、跳高、跳遠、一仟五、聖火隊、助理裁判。
設計公式，儲存格P2：
=SUMPRODUCT((班級編碼=$N2)*(性別=$O2)*(INDIRECT(P$1)=TRUE))
INDIRECT(P$1)=TRUE：其中 INDIRECT(P$1)，巧妙的將儲存格P1中的欄位名稱轉換為實際儲存格範圍(其中每個欄位中的儲存格範圍都已先定義好名稱了)。
然後，複製儲存格P2，貼至儲存格P2:U4。
【後記】
你有發現嗎？只要一個公式即能求出各班的男生／女生參加各個比賽的人數，其實是有一些巧妙的安排的，例如：
(1) O欄中『男』和『女』是分二列安排。
(2) 運算人數的資料表欄位稱和原始資料的欄位名稱完全相同。
留言列表