Excel日常20:函數篇(超好用的條件求和函數SUMIF)
喜歡、有用就點點關注
▲
一、函數語法解析
1、函數定義:對滿足條件的單元格求和。
2、函數語法格式
range:根據條件進行計算的單元格的區域。每個區域中的單元格必須是數字或名稱、數組或包含數字的引用。空值和文本值將被忽略。所選區域可以包含標準 Excel 格式的日期。
criteria:用於確定對哪些單元格求和的條件,其形式可以為數字、表達式、單元格引用、文本或函數。
sum_range:實際求和區域,需要求和的單元格、區域或引用。
▲
二、函數應用實例
▲
1、經典用法
輸入公式:
=SUMIF(C3:C8,">=8000",C3:C8)
或者
=SUMIF(C3:C8,">=8000")
當條件區域和求和區域相同時可以省略第三參數。第三參數省略時,默認條件區域就是求和區域。
▲
2、求性別為“女”的銷售額之和
輸入公式:
=SUMIF(B12:B17,"女",C12:C17)
▲
3、通配符的使用
公式:
F21=SUMIF(A21:A26,"*"&6&"*",C21:C26)
F24=SUMIF(A21:A26,"???",C21:C26)
星號*匹配任意一串字符,問號?匹配任意單個字符。
▲
4、超過15個字符出錯
當我們在單元格F30輸入公式:=SUMIF(B$30:B$35,E30,C$30:C$35),很明顯得到的結果是錯誤的,那該怎麼辦呢?
正確公式是:
=SUMIF(B$30:B$35,E30&"*",C$30:C$35),向下填充。
▲
5、查找功能
通常我們查找時都會用LOOKUP、VLOOKUP等函數查找,下面這題我們將用SUMIF來代替VLOOKUP查找。
公式:G39=SUMIF($A$39:$A$44,$F39,B$39:B$44),向右向下填充。
▲
使用查詢注意事項:
①條件區域的數據必須是唯一的;
②查詢的結果必須是數字。
▲
6、求銷售額在[5000,8000]的和
初看這題時,如果用SUMIF函數來解,是不是覺得只能是這樣解呢:
F48=SUMIF(C48:C53,">=5000")-SUMIF(C48:C53,">8000")
其實也可以用我們前面學到的函數SUM:
F49=SUM(SUMIF(C48:C53,{">=5000",">8000"})*{1,-1})
或者
F50=SUM(SUMIF(C48:C53,ROW(5000:8000))),三鍵結束。
▲
7、隔行求和
公式:D57=SUMIF(A56:A63,"*",A57:A63)
▲
8、隔列求和
公式:I67=SUMIF(A67:F72,"L-L-X",B67)
▲
9、排錯求和
公式:D77=SUMIF(A75:A82,"<9E307"),9E307是excel能承受的最大值。
▲
10、求最後一次銷售日期
下表為2016年10月10日-18日的銷售量:
公式:K86 =SUMIF(A86:J86,"<>",B$85:J$85)-SUMIF(B86:J86,"<>",B$85:J$85)-1,向下填充。
▲
三、函數總結
①、使用SUMIF函數匹配超過255個字符的字符串或字符串#VALUE! 時,將返回不正確的結果。
②、第二參數支持使用通配符,包括問號(?)和星號(*)。問號匹配任意單個字符;星號匹配任意一串字符。如果要查找實際的問號或星號,請在該字符前鍵入波形符(~)。
③、第二參數中指定的條件必須用雙引號括起來,如 "<60"、"女" 等。當指定條件為數字或引用單元格時無需用雙引號括起來。
④、當第三參數省略時,則條件區域就是實際求和區域。
⑤、sum_range 參數與range參數的大小和形狀可以不同。求和的實際單元格通過以下方法確定:使用sum_range參數中左上角的單元格作為起始單元格,然後包括與range參數大小和形狀相對應的單元格。
⑥、SUMIF函數本身不是易失性函數,但是,當SUMIF函數中的range和sum_range參數不包含相同的單元格個數時,它將具備易失性,工作表重新計算需要的時間可能比預期的長。
我們一直秉承簡潔、優雅、高效的為讀者分享工作中遇到的每一個Excel問題,不論是Excel技巧、函數、圖表、VBA,甚至是有關於Excel的開發,只要你能提出來問題,我們總能給你一個滿意的答案!
每天準時來一篇Excel在職場中的案例。