工作表中有 10 個學生四個科目的成績,現在需要計算兩科不及格的學生人數。
 


第 6 章\344.xlsx



1. 開啟範例檔案中的資料檔案,在 G2 儲存格輸入以下陣列公式:
=SUM(--(COUNTIF(OFFSET($B$1,ROW(2:11)-1,,,4),"<60")>=2))

按下(Ctrl)+(Shift)+(Enter)複合鍵後,公式將算出至少兩科不及格的學生人數,結果如圖 6.37 所示:
2.jpg
圖 6.37 計算至少兩科不及格的學生人數


2. 公式說明
本例公式利用陣列作為 OFFSET 的參數,且第五參數使用 4,表示產生 10  個  1  列  4  欄的一維陣列,也就是每個學生的四科成績的參照。然後用 COUNTIF 函數分別統計每個學生的成績小於 60 的個數是否大於等於 2,最後用 SUM 函數加總。


3. 使用注意
① 本例也可以使用如下公式完成計算。
=SUMPRODUCT(N(COUNTIF(OFFSET($B$1,ROW(2:11)-1,,,4),"<60")>=2))
=COUNT(0/(COUNTIF(OFFSET($B$1,ROW(2:11)-1,,,4),"<60")>=2))
② OFFSET 可以產生二維陣列,但 OFFSET 只能對儲存格、範圍進行操作,其物件不能是陣列或者常數陣列。
③ 本例也屬於三維參照的範例。


4. 範例延伸
思考:計算四科都不及格的人數。
提示:可以判斷每個人不及格的成績次數是否等於 4,也可以透過利用 SUBTOTAL 函數計算其最大值是否小於 60 來判斷。因為最大值小於 60 就意味著四科全不及格。
 

 

 

 

 

本文同步刊載於>>

Excel職場函數468招:超完整!新人工作就要用到的計算函數+公式範例集
 

內容涵蓋財務、人事、業務、銷售、倉儲、教育等行業的實例為主,以函數語法剖析為輔,示範 468 個函數的實用範例!
內容由淺入深,每篇範例都是獨立的應用,可依需求查詢及學習!
 


歡迎加入 PCuSER 密技爆料粉絲團

 

arrow
arrow

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