王佩豐Excel基礎教程24講:第10講-SUMIF函數


3樓貓 發佈時間:2024-12-21 05:58:42 作者:SystemBot-X Language

視頻鏈接

1.Sumif函數語法

語法

=SUMIF(range, criteria, )

  • range:用於判斷條件的範圍。

  • criteria:條件,用於篩選 range 中滿足條件的單元格。

  • sum_range:實際求和的範圍。如果省略,則對 range 本身求和。

功能

  • 按單一條件對數據求和。

  • 條件可以是具體值、表達式(如 >50)或通配符(如 *A*)。

示例

示例數據

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第0張

示例 1:按條件求和

問題:統計一班的總分數。

公式:

=SUMIF(C2:C6, "一班", B2:B6)

結果:

  • 條件:C2:C6 為 "一班"。

  • 求和範圍:B2:B6。

  • 返回值為 260(90 + 75 + 95)。

示例 2:直接對條件範圍求和

問題:統計分數大於 80 的總和。

公式:

=SUMIF(B2:B6, ">80")

結果:

  • 條件:B2:B6 中的值大於 80。

  • 求和範圍:B2:B6(因為未指定 sum_range,默認對 B2:B6 求和)。

  • 返回值為 270(90 + 85 + 95)。

示例 3:通配符求和

問題:統計名字中包含“張”的學生分數總和。

公式:

=SUMIF(A2:A6, "*張*", B2:B6)

結果:

  • 條件:A2:A6 中包含 "張"。

  • 求和範圍:B2:B6。

  • 返回值為 90。

示例 4:結合條件和空白值

問題:統計未填寫班級的學生分數總和。

公式:

=SUMIF(C2:C6, "", B2:B6)

結果:

  • 條件:C2:C6 中為空白。

  • 求和範圍:B2:B6。

  • 返回值為 0(此示例數據中沒有空白的班級)。

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第1張

備註:在特定情境下,SUMIF函數可巧妙替代VLOOKUP函數,實現高效數據檢索與彙總。

2.Sumif函數計算數值區間

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第2張

=SUMIF(E:E,H8,F:F)

3.Sumif函數超過15位字符時的錯誤

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第3張

=SUMIF(A:A,F3&"*",B:B)

注意:excel只能識別字符串前15位,多餘位數需要用&連接*

4.關於第三參數簡寫時的注意事項

關於第三參數的簡化使用注意事項:在使用Excel中的SUMIF函數時,必須確保後一個區域與前一個區域在計算過程中具有相同的尺寸,並且首行必須相同。即便在實際操作中選擇的後一個區域的單元格數量較少,Excel也會自動進行補充,以保證計算結果的準確性。

關注公式中第三個參數(求和區域)選取區域

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第3張

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第3張

5.在多列中使用Sumif函數

比如,“科目劃分”和“發生額”被分為了好多段,此時只要保證第三參數的開始位置和第一參數的保持一致即可

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第3張

A1:J11與A1:I11不影響結果

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第3張

6.使用輔助列處理多條件的Sumif

存在多個區域用&連接

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第3張

=SUMIF(A:A,J5&K5,G:G)

7.Sumifs函數(處理多條件)

語法

=SUMIFS(sum_range, criteria_range1, criteria1, , ...)

  • sum_range:需要求和的實際數據範圍。

  • criteria_range1:第一個條件的判斷範圍。

  • criteria1:應用於 criteria_range1 的條件。

  • criteria_range2, criteria2:後續條件的判斷範圍及條件(可選,最多支持 127 個條件)。

功能

  • 按多個條件篩選後對數據求和。

  • 每個條件範圍與 sum_range 的大小必須相同。

示例

示例數據

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第3張

示例 1:多條件求和

問題:統計一班的男生總分。

公式:

=SUMIFS(B2:B6, C2:C6, "一班", D2:D6, "男")

結果:

  • 條件:

  • C2:C6 為 "一班"。

  • D2:D6 為 "男"。

  • 求和範圍:B2:B6。

  • 返回值為 260(90 + 75 + 95)。

示例 2:按範圍篩選求和

問題:統計分數在 80 到 95(含)之間的總和。

公式:

=SUMIFS(B2:B6, B2:B6, ">=80", B2:B6, "<=95")

結果:

  • 條件:

  • B2:B6 大於等於 80。

  • B2:B6 小於等於 95。

  • 求和範圍:B2:B6。

  • 返回值為 350(90 + 85 + 95 + 80)。

示例 3:結合通配符求和

問題:統計名字中包含“張”的學生分數。

公式:

=SUMIFS(B2:B6, A2:A6, "*張*")

結果:

  • 條件:A2:A6 中包含 "張"。

  • 求和範圍:B2:B6。

  • 返回值為 90

示例 4:統計滿足多個條件但允許範圍不等的數據

問題:統計二班且分數大於 80 的學生分數。

公式:

=SUMIFS(B2:B6, C2:C6, "二班", B2:B6, ">80")

結果:

  • 條件:

  • C2:C6 為 "二班"。

  • B2:B6 大於 80。

  • 求和範圍:B2:B6。

  • 返回值為 85

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第3張

=SUMIFS(G:G,E:E,J5,F:F,K5)

8.複習數據有效性

(1)給產品做一個下拉框

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第3張

  • 任意選中一個區域,保證從F3開始並向下即可

  • 然後點擊“數據-數據驗證”,選擇“序列”,來源就是要下拉的內容;如果有下拉內容,可以自行輸入,記得中間用英文逗號隔開

  • 選中所需單元格這樣產品就可以選擇輸入了,避免了手動輸入造成的錯誤,如果有重複值記得先去重,輔助列=UNIQUE(A:A),來源選擇輔助列

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第3張

(2)當同類產品的數量加在一起大於了產品的總庫存時,就報警“超出總限制”

以“彩盒”為例,把所有出庫單類別都為“彩盒”時對應的數量全部相加(只要產品類別一樣,就把他們所有的金額加起來,包括自己),若小於等於庫存表中的彩盒數量,則滿足條件,否則就會報警。

任意選中一個區域,保證從G3開始並向下即可

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第3張

然後點擊“數據-數據驗證”,選擇“自定義”,並輸入公式

左邊公式:根據出庫單中相同產品類別計算總和數量

右邊公式:根據庫存表中的產品類別去對應庫存表中找出庫存數量

sumif(F:F,F3,G:G)<sumif(A:A,F3,B:B)

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第3張

寫完之後試一下對不對

先選中產品=彩盒,輸入數量=40000,然後再輸入產品=彩盒,數量=4855,發現不會報錯;如果第二次輸入數量是4856,發現會出現預警;因為總的彩盒庫存是44855

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第3張

王佩豐Excel基礎教程24講:第10講-SUMIF函數-第3張


© 2022 3樓貓 下載APP 站點地圖 廣告合作:asmrly666@gmail.com