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

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


【公式設計與解析】

選取儲存格B1:C151,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:性別、年資。

儲存格F1:=SUMPRODUCT((性別=F$1)*(年資>=ROW(1:1)-1)*(年資<ROW(1:1)))

複製儲存格F1,貼至儲存格F1:G15。

條件一:(性別=F$1)

判斷性別陣列中是否有儲存格F1的內容相同,傳回 TRUE/FALSE 陣列。

條件二:(年資>=ROW(1:1)-1)*(年資<ROW(1:1))

ROW(1:1)向下複製公式時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

『*』運算相當於邏輯 AND 的運算,儲存格F1中的(年資>=ROW(1:1)-1)*(年資<ROW(1:1))等於(年資>=0)*(年資<1),即年資大於 0 而小於 1 者,用來計算 0~1 之間的個數。

最後透過 SUMPRODUCT 函數計算傳回陣列的乘積和,運算過程中 TRUE/FALSE 會轉換為 1/0。在條件一和條件二皆成立時才會傳回 1,否則傳回 0。

文章標籤
創作者介紹

學不完.教不停.用不盡

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