2010年6月17日 星期四

Excel-SUMIFS+COUNTIFS應用

在 Excel 中,如果要根據檢定考試分數表,統計參加人數、及格人數、平均分數等,可以使用SUMIFS、COUNTIFS、SUMPRODUCT等函數來完成。

(1)計算報名人數

儲存格G3:=SUMPRODUCT(($B$2:$B$24=G$2)*1,(($C$2:$C$24)=$F3)*1)

複製儲存格G3到儲存格G3:J4。

此方式利用 SUMPRODUCT 函數,將[檢定]和[級別]合於條件者X1(將True、False轉成1、0)後相乘而得到結果。

 

(2)計算及格人數-1

儲存格G8:=SUMPRODUCT(($B$2:$B$24=G$2)*1,($C$2:$C$24=$F8)*1,($D$2:$D$24>=60)*1)

複製儲存格G8到儲存格G8:J8。

此方式利用 SUMPRODUCT 函數,將[檢定]和[級別]和[分數>=60]合於條件者X1(將True、False轉成1、0)後相乘而得到結果。

 

(3)計算及格人數-2

儲存格G13:=COUNTIFS($B$2:$B$24,G$12,$C$2:$C$24,$F13,$D$2:$D$24,">=60")

複製儲存格G13到儲存格G13:J14。

此方式利用 COUNTIFS 函數,將[檢定]和[級別]和[分數>=60]合於條件者,計算數量後得到結果。

 

(4)計算平均人數-1

儲存格G18:=IF(COUNTIFS($B$2:$B$24,G$17,$C$2:$C$24,$F18)=0,"-",SUMPRODUCT(($B$2:$B$24=G$2)*1,($C$2:$C$24=$F18)*1,$D$2:$D$24)/COUNTIFS($B$2:$B$24,G$17,$C$2:$C$24,$F18))

複製儲存格G18到儲存格G3:J19。

此方式利用 COUNTIFS 計算合於條件的人數,利用 SUMIFS 計算合於條件者的分數和,再計算 SUMIFS/COUNTIFS 可得平均。因為可以COUNTIFS的結果為0,會造成因除數為0的錯誤結果,所以將COUNTIFS的結果為0者,以「-」呈現。

 

(5)計算平均人數-2

儲存格G23:=IF(COUNTIFS($B$2:$B$24,G$17,$C$2:$C$24,$F23)=0,"-",SUMIFS($D$2:$D$24,$B$2:$B$24,G$17,$C$2:$C$24,$F23)/COUNTIFS($B$2:$B$24,G$17,$C$2:$C$24,$F23))

複製儲存格G23到儲存格G23:J24。

觀念同(4),這次是以 SUMIFS 來計算合於條件者的分數和。

 

COUNTIFS:套用準則到跨多個範圍的儲存格,並計算符合所有準則的次數。

語法:COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

criteria_range1:必要參數。是要在其中估算關聯準則的第一個範圍。

criteria1:必要參數。用以定義要計算之儲存格的準則。

criteria_range2, criteria2, ...:選用參數。其他範圍及其相關準則。最多允許 127 組範圍/準則。

 

SUMIFS:將範圍內符合多個準則的儲存格相加。

語法:SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

sum_range:必要參數。要計算總和的一個或多個儲存格。如果是保留空白或文字值,則會予以忽略。

criteria_range1:必要參數。要估算關聯準則的第一個範圍。

criteria1:必要參數。定義 criteria_range1 參數中要相加之儲存格的準則,最多允許 127 組範圍/準則。

沒有留言:

張貼留言

好康東東