Excel滿足特定條件的單元格進(jìn)行求和或匯總

1、Excel滿足特定條件的單元格進(jìn)行求和或匯總?cè)绻?jì)算單元格區(qū)域中某個(gè)文本串或數(shù)字出現(xiàn)的次數(shù)，則可使用COUNTIF工作表函數(shù)。如果要根據(jù)單元格區(qū)域中的某一文本串或數(shù)字求和，則可使用SUMIF工作表函數(shù)。關(guān)于SUMIF函數(shù)在數(shù)學(xué)與三角函數(shù)中以做了較為詳細(xì)的介紹。這里重點(diǎn)介紹COUNTIF的應(yīng)用。COUNTIF可以用來(lái)計(jì)算給定區(qū)域內(nèi)滿足特定條件的單元格的數(shù)目。比如在成績(jī)表中計(jì)算每位學(xué)生取得優(yōu)秀成績(jī)的課程數(shù)。在工資表中求出所有基本工資在2000元以上的員工數(shù)。語(yǔ)法形式為COUNTIF(range,criteria)。其中Range為需要計(jì)算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria確定

2、哪些單元格將被計(jì)算在內(nèi)的條件，其形式可以為數(shù)字、表達(dá)式或文本。例如，條件可以表示為32、"32"、">32"、"apples"。1、成績(jī)表這里仍以上述成績(jī)表的例子說(shuō)明一些應(yīng)用方法。我們需要計(jì)算的是：每位學(xué)生取得優(yōu)秀成績(jī)的課程數(shù)。規(guī)則為成績(jī)大于90分記做優(yōu)秀。如圖8所示根據(jù)這一規(guī)則，我們?cè)趦?yōu)秀門數(shù)中寫公式(以單元格B13為例)：=COUNTIF(B4:B10,">90")語(yǔ)法解釋為，計(jì)算B4到B10這個(gè)范圍，即jarry的各科成績(jī)中有多少個(gè)數(shù)值大于90的單元格。在優(yōu)秀門數(shù)欄中可以看到j(luò)arry的優(yōu)秀門數(shù)為

3、兩門。其他人也可以依次看到。2、銷售業(yè)績(jī)表銷售業(yè)績(jī)表可能是綜合運(yùn)用IF、SUMIF、COUNTIF非常典型的示例。比如，可能希望計(jì)算銷售人員的訂單數(shù)，然后匯總每個(gè)銷售人員的銷售額，并且根據(jù)總發(fā)貨量決定每次銷售應(yīng)獲得的獎(jiǎng)金。原始數(shù)據(jù)表如圖9所示(原始數(shù)據(jù)是以流水單形式列出的，即按訂單號(hào)排列)AB_J.C1訂單號(hào)訂單金額銷售人員2200104015,ODO.00JARRY320010-t024,500.00HELEN420010403N凡OOO,00MICHAEL5200104044,200.00ARR¥61200104054,500,00ANNIE7200104062,500.00AN

4、NIE8:20010-tO7IS,00O.00JARRY920010408BEOOO,00MICHAEL20010409工？OOO.00HELEN11)20010-11050,口口Q.00HELEN12200104112,580.00JARR200,00ANNIE14銷售總額212480,00圖9原始數(shù)據(jù)表按銷售人員匯總表如圖10所示如圖10所示的表完全是利用函數(shù)計(jì)算的方法自動(dòng)匯總的數(shù)據(jù)。首先建立一個(gè)按照銷售人員匯總的表單樣式，如圖所示。然后分別計(jì)算訂單數(shù)、訂單總額、銷售獎(jiǎng)金。(1)訂單數(shù)-用COUNTIF計(jì)算銷售人員的訂單數(shù)。以銷售人員ANNIE的訂單數(shù)公式為例。公

5、式:=COUNTIF($C$2:$C$13,A17)語(yǔ)法解釋為計(jì)算單元格A17(即銷售人員ANNIE)在“銷售人員"清單$C$2:$C$13的范圍內(nèi)(即圖9所示的原始數(shù)據(jù)表)出現(xiàn)的次數(shù)。這個(gè)出現(xiàn)的次數(shù)即可認(rèn)為是該銷售人員ANNIE的訂單數(shù)。(2)訂單總額-用SUMIF匯總每個(gè)銷售人員的銷售額。以銷售人員ANNIE的訂單總額公式為例。公式：=SUMIF($C$2:$C$13,A17,$B$2:$B$13)此公式在“銷售人員"清單$C$2:$C$13中檢查單元格A17中的文本(即銷售人員ANNIE),然后計(jì)算"訂單金額"列($B$2:$B$13)中相應(yīng)量的和

6、。這個(gè)相應(yīng)量的和就是銷售人員ANNIE的訂單總額。(3)銷售獎(jiǎng)金-用IF根據(jù)訂單總額決定每次銷售應(yīng)獲得的獎(jiǎng)金。假定公司的銷售獎(jiǎng)金規(guī)則為當(dāng)訂單總額超過(guò)5萬(wàn)元時(shí)，獎(jiǎng)勵(lì)幅度為百分之十五，否則為百分之十。根據(jù)這一規(guī)則仍以銷售人員ANNIE為例說(shuō)明。公式為：=IF(C17<50000,10%,15%)*C17如果訂單總額小于50000則獎(jiǎng)金為10%;如果訂單總額大于等于50000,則獎(jiǎng)金為15%。B17色=AB1CD銷售人員訂單數(shù)訂單總額銷售獎(jiǎng)金ANNIEZ!廠10,200.001,020.00-JARRY426,780.002,67&00HELEN365,600.004325.00MIC

7、HAEL2120,000.0018,000.00訂單總數(shù)12212.480,0030.023.00=SUMIF缸Al=ir(CIT<50000,10%,15%>C17圖10銷售人員匯總表B13*I=COUNTIF<B4：B1O,ABCDEFGHjarryMikeHcrryAnnieJackyAndySmile1數(shù)學(xué)955675651006870卜吾土75603575959S35英語(yǔ)555490100959080物理100557040907065化學(xué)SS40785cl8S8979蕨*73598070839080J歷史905536759585平均分8454S1922679*綜合評(píng)

8、.定不合格合格合格優(yōu)秀合格合格優(yōu)秀門數(shù)21001320圖8SUMIF用途：根據(jù)指定條件對(duì)若干單元格、區(qū)域或引用求和。語(yǔ)法：SUMIF(range,criteria,sum_range)參數(shù)：Range為用于條件判斷的單元格區(qū)域，Criteria是由數(shù)字、邏輯表達(dá)式等組成的判定條件，Sum_range為需要求和的單元格、區(qū)域或引用。實(shí)例：某單位統(tǒng)計(jì)工資報(bào)表中職稱為中級(jí)”的員工工資總額。假設(shè)工資總額存放在工作表的F歹U,員工職稱存放在工作表B歹U。則公式為“=SUMIF（B1:B1000,"中級(jí)"，F(xiàn)1:F1000）”，其中“B1:B1000”為提供邏輯判斷依據(jù)白單元格區(qū)域，&

9、quot;中級(jí)"為判斷條件，就是僅僅統(tǒng)計(jì)B1:B1000區(qū)域中職稱為中級(jí)”的單元格，F(xiàn)1:F1000為實(shí)際求和的單元格區(qū)域。例如：sumif（A1:A20,"a",B1:B20）意思就是：在A1到A20這個(gè)區(qū)域中，凡是"a"的就把它的數(shù)量求和。如果是不同一個(gè)工作表，只要在區(qū)域的前面加上工作表的名稱就行了，如sumif（sheet1!A1:A20,"a",sheet1!B1:B20）但要注意的一點(diǎn)就是在判斷條件時(shí)，條件一定要與字段名相同，就算差一個(gè)點(diǎn)或一個(gè)空格，公式都無(wú)法判斷，所以條件與字段名一定要一致COUNTIF函數(shù)（計(jì)數(shù)

10、求和）COUNT函數(shù)，顧名思義是用來(lái)計(jì)數(shù)的，統(tǒng)計(jì)所選擇區(qū)域的數(shù)值型單元格個(gè)數(shù)。COUNTIF是COUNT函數(shù)的引伸與拓展，在計(jì)數(shù)時(shí)加上先前條件，只有符合計(jì)數(shù)的條件才進(jìn)行統(tǒng)計(jì)計(jì)算。比如，從員工信息表中，計(jì)算出有多少人的年齡大于35歲。下面我們來(lái)看一個(gè)典型的分類計(jì)數(shù)匯總的例子。這里有一張銷售流水記錄表，每名銷售人員累計(jì)做了多少銷售訂單個(gè)數(shù)”呢？COUNTIF正常工作需要兩個(gè)參數(shù)一一條件區(qū)域（本例為左側(cè)表中銷售人員”一列）和計(jì)數(shù)條件（本例為右側(cè)表中的人員姓名）。要計(jì)算第一位銷售人員的訂單數(shù)”，考試，大提示很簡(jiǎn)單，輸入函數(shù)公式“=COUNTIF（$C$2:$C$16,E2）即可。二：SUMIF函數(shù)（條

11、件求和）SUM函數(shù)的作用是對(duì)數(shù)據(jù)求和，而SUMIF對(duì)它進(jìn)行了引伸和拓展，比如計(jì)算金額”在1元以上的數(shù)據(jù)總和、按照人員或產(chǎn)品分類計(jì)算數(shù)據(jù)總和等等。它有3個(gè)參數(shù)，分別是條件區(qū)域、判斷條件、實(shí)際的求和區(qū)域（如果它與條件區(qū)域”是一個(gè)區(qū)域，就可省略）。在上例中，計(jì)算每位銷售人員”的訂單總金額，就要使用SUMIF函數(shù)來(lái)協(xié)助了。如果要計(jì)算每個(gè)人的銷售訂單總金額，把左側(cè)表的銷售人員”一列當(dāng)作條件區(qū)域”，把右側(cè)表的每個(gè)名單當(dāng)作求和條件”，把左側(cè)表的每筆訂單金額”當(dāng)作實(shí)際求和區(qū)域”，在G2單元格中輸入數(shù)據(jù)計(jì)算公式“=SUMIF（$C$2:$C$16,E2,$B$2:$B$16）”，第1名銷售人員的訂單總額”就瞬間

12、產(chǎn)生了。小提示：在本仞的COUNTIF函數(shù)和SUMIF函數(shù)中，由于銷售人員”區(qū)域與訂單總額”區(qū)域都是固定的，所以在函數(shù)中引用這兩列地址時(shí)，要使用絕對(duì)地址”，也就是在地址前添加“$”符號(hào)。三：IF函數(shù)邏輯高手IF函數(shù)是一個(gè)條件函數(shù)，它可以通過(guò)設(shè)置的條件進(jìn)行邏輯判斷。如果在剛才的數(shù)據(jù)匯總表中再添加一列銷售獎(jiǎng)金”數(shù)據(jù)，銷售獎(jiǎng)金”發(fā)放的方法是：如果某個(gè)人的訂單總額大于¥1500,000,那么獎(jiǎng)金”數(shù)用總額X5%,否則獎(jiǎng)金”就用總額>%。所以，第1位銷售人員銷售獎(jiǎng)金”的計(jì)算公式應(yīng)為“=IF（G2>1500000,G2*0.05,G2*0.03）結(jié)合剛才的知識(shí)，推測(cè)一下這個(gè)公式中3個(gè)

13、參數(shù)的涵義吧。"G2>1500000是IF函數(shù)的判斷條件，“G2*0.05是條件成立的操作，"G2*0.03是條件不成立的操作。點(diǎn)睛：*數(shù)據(jù)的分類匯總是Excel最常見(jiàn)的應(yīng)用。分類匯總的方法很多，包括使用函數(shù)和公式。設(shè)置的條件可看作是分類的依據(jù)，用“COUNTIF函數(shù)”進(jìn)行分類計(jì)數(shù)匯總，用“SUMIF函數(shù)”進(jìn)行分類求和匯總，非常方便。*IF函數(shù)的作用是根據(jù)判斷條件的真假，自動(dòng)進(jìn)行分支操作。比如根據(jù)身份證號(hào)碼的奇偶來(lái)判斷性別，根據(jù)績(jī)效數(shù)據(jù)來(lái)填寫成績(jī)等等。在實(shí)際應(yīng)用時(shí)，一個(gè)非常實(shí)用的應(yīng)用是：把IF函數(shù)的真”或假”參數(shù)嵌套成另一個(gè)IF函數(shù)，實(shí)現(xiàn)多種分支操作。如2個(gè)IF函數(shù)的嵌套可寫成“=IF（條件，真，IF（條件，真，假）"，這樣一來(lái)就可以實(shí)現(xiàn)3個(gè)分支判斷了（在Excel中，最多是7層函數(shù)嵌套）。*重復(fù)數(shù)據(jù)的篩選問(wèn)題常常讓我們感到非常棘手。COUNTIF函數(shù)除了可以實(shí)現(xiàn)分類計(jì)數(shù)匯總外，它和IF函數(shù)配合，還可以實(shí)現(xiàn)對(duì)重復(fù)數(shù)據(jù)的標(biāo)識(shí)與篩選，從而將1列中的重復(fù)數(shù)據(jù)刪除。使用了公式“=