2017年2月3日 星期五

Excel-分性別分年資計算人數(SUMPRODUCT)

網友問到計算年資的問題:如何使用 Excel 來分別計算下圖中男生和女生各個年資的人數?如下圖,每個人的資料有性別和年資二個數據,要分男女生來計算不同年資的人數。
Excel-分性別分年資計算人數(SUMPRODUCT)

【公式設計與解析】
選取儲存格B1:C151,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:性別、年資。
儲存格F1:=SUMPRODUCT((性別=F$1)*(年資>=ROW(1:1)-1)*(年資
複製儲存格F1,貼至儲存格F1:G15。
條件一:(性別=F$1)
判斷性別陣列中是否有儲存格F1的內容相同,傳回 TRUE/FALSE 陣列。
條件二:(年資>=ROW(1:1)-1)*(年資
ROW(1:1)向下複製公式時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。
『*』運算相當於邏輯 AND 的運算,儲存格F1中的(年資>=ROW(1:1)-1)*(年資=0)*(年資<1 0="" 1="" p="">
最後透過 SUMPRODUCT 函數計算傳回陣列的乘積和,運算過程中 TRUE/FALSE 會轉換為 1/0。在條件一和條件二皆成立時才會傳回 1,否則傳回 0。

沒有留言:

張貼留言

好康東東