別說你會SUMIF,不服來辯!
各位表親好啊,今天和大家分享兩個SUMIF函數的高能應用,一起來看看吧:
問題一
如下圖中所示,是一份類比的考評記錄表,每個人的成績次數不一樣,但至少會出現一次。要求用公式計算出所有人最後一次考試成績的平均分。
這裡的結果是85、93、80、98、84的平均數。
參考公式:
=SUMIF(B3:F10,"",B2:F9)/5
接下來,咱們就結合這個問題,聊聊SUMIF函數。
SUMIF函數是咱們日常工作中使用頻率很高的函數,通常用於對區域中符合指定的單個條件的值求和。它與COUNTIF函數具有相似的函數結構和用法,
SUMIF函數的語法很簡單,只有3個參數:
=SUMIF(條件區域,指定的條件,需要求和的區域)
初步認識了SUMIF函數的使用規則,再回到本例中的題目:
先來看=SUMIF(B3:F10,"",B2:F9)的第一參數B3:F10,就是條件區域。
第二參數指定的條件是"",也就是空值。
選擇空值作為指定的條件是本題的關鍵。
因為我們的題目要求是對每個人的最後一個考試成績計算平均值,要計算平均值,首先就要計算出B3:F10區域中每一列的最後一個值。
這最後一個值有什麼共同的特點呢?
就是這個值向下一個儲存格必須是空白的,要是向下一個儲存格有值的話,就不是最後一個值了,對吧?
第3參數是B2:F9,注意這裡的引用區域和第一參數的條件區域形成了一個錯行的效果。
整個公式的意思就是:
如果B3:F10儲存格區域中滿足等於空值的條件,就去計算與空值對應的上一行的和,這樣就變相的得到了B3:F10區域中每一列當中最後一個值的和。
最後用SUMIF函數的計算結果除以5,就完成了咱們的題目要求。結果為88。
如果公式需要再簡化的話,還可以使用:
=SUMIF(B3:F10,"",B2)/5
這裡的第3參數使用了簡寫方式,SUMIF函數會根據第1參數的範圍進行智慧的匹配。需要注意的是,由於求和區域不明確,容易引發公式的重新計算,產生與易失性函數相似的情況。因此當資料量較大時,需謹慎使用第3參數的簡寫方式。
問題二
接下來,我們再看一下這個題目,下圖是一份模擬的員工考核表,如A1:E7儲存格區域所示,員工的每個考核項目使用不同的等級來表示。
現在需要根據A10:B15儲存格的分值對照表,在F列計算出每個員工的總分值。
計算這個問題,同樣可以使用SUMIF函數完成,
{=SUM(SUMIF(A$11:A$15,B2:E2,B$11:B$15))}
本例中的SUMIF函數第二參數使用了多儲存格的區域引用,計算在條件區域A$11:A$15中分別符合條件B2:E2的、對應的B$11:B$15的值。
結果為:{6.7,6.7,5.3,4.6}
最後使用SUM函數求和,得出計算結果為23.3。
老祝說,簡單函數也有大作用,高手的境界就是手中無刀心中有刀。
好了,今天的內容就是這些,小夥伴們明天不見不散!
圖文製作:祝洪忠