在 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 組範圍/準則。 |
留言列表