2012年3月3日 星期六

Excel-分別計算男女生的平均身高和體重(SUMPRODUCT+陣列公式)

有網友問到:在 Excel 中有個姓名、性別、身高、體重的基本資料表(參考下圖左),其中性別1代表男生,性別2代表女生,男生和女生以不規則方式排列,如果想要根據資料表分別求取男生和女生的平均身高和平均體重,該如何處理?

【準備工作】

為了公式方便說明,選取儲存格B1:D24,按一下 Ctrl+Shift+F3 鍵,定義名稱:性別、身高、體重。

【使用SUMPRODUCT函數】

(1) 儲存格G2:=SUMPRODUCT((性別=1)*(身高))/SUMPRODUCT((性別=1)*1)

(性別=1)*(身高):取得性別為1(女生)的身高陣列,由 SUMPRODUCT 函數求得身高的總和。

(性別=1)*1:取得性別為1(女生)的陣列,乘以1後,可將 True/False 陣列轉換為 1/0 陣列,即可取得性別為1的個數。

上述二式相除後,即可取得身高的平均值。

(2) 儲存格G3:=SUMPRODUCT((性別=2)*(身高))/SUMPRODUCT((性別=2)*1)

(3) 儲存格H2:=SUMPRODUCT((性別=1)*(體重))/SUMPRODUCT((性別=1)*1)

(3) 儲存格H3:=SUMPRODUCT((性別=2)*(體重))/SUMPRODUCT((性別=2)*1)

 

【使用陣列公式】

如果你懂得陣列公式的應用,公式可以更簡捷。輸入完陣列公式,要按 Ctrl+Shift+Enter 鍵。

(1) 儲存格G2:{=AVERAGE(IF(性別=1,身高,FALSE))}

IF(性別=1,身高,FALSE):取得性別為1(女生)的身高陣列,再透過 AVERAGE 函數求取平均數。

(2) 儲存格G3:{=AVERAGE(IF(性別=2,身高,FALSE))}

(3) 儲存格H2:{=AVERAGE(IF(性別=1,體重,FALSE))}

(4) 儲存格H23{=AVERAGE(IF(性別=2,體重,FALSE))}

沒有留言:

張貼留言

檢視其他文章

好康東東