EXCEL成績(jī)統(tǒng)計(jì)分析常用函數(shù)教案資料

1、Good is good, but better carries it.精益求精，善益求善。EXCEL成績(jī)統(tǒng)計(jì)分析常用函數(shù)-EXCEL成績(jī)統(tǒng)計(jì)分析常用函數(shù)1、總分=SUM（）2、平均分=AVERAGE（）3、+-*/加減乘除括號(hào)（）4、分?jǐn)?shù)段統(tǒng)計(jì)函數(shù)：FREQUENCY()以一列垂直數(shù)組返回某個(gè)區(qū)域中數(shù)據(jù)的頻率分布。例如，使用函數(shù)FREQUENCY可以計(jì)算在給定的分?jǐn)?shù)范圍內(nèi)測(cè)驗(yàn)分?jǐn)?shù)的個(gè)數(shù)。由于函數(shù)FREQUENCY返回一個(gè)數(shù)組，所以必須以數(shù)組公式的形式輸入。語(yǔ)法FREQUENCY(data_array,bins_array)Data_array為一數(shù)組或?qū)σ唤M數(shù)值的引用，用來(lái)計(jì)算頻率。如果da

2、ta_array中不包含任何數(shù)值，函數(shù)FREQUENCY返回零數(shù)組。Bins_array為間隔的數(shù)組或?qū)﹂g隔的引用，該間隔用于對(duì)data_array中的數(shù)值進(jìn)行分組。如果bins_array中不包含任何數(shù)值，函數(shù)FREQUENCY返回data_array中元素的個(gè)數(shù)。說(shuō)明在選定相鄰單元格區(qū)域（該區(qū)域用于顯示返回的分布結(jié)果）后，函數(shù)FREQUENCY應(yīng)以數(shù)組公式的形式輸入。返回的數(shù)組中的元素個(gè)數(shù)比bins_array（數(shù)組）中的元素個(gè)數(shù)多1。返回的數(shù)組中所多出來(lái)的元素表示超出最高間隔的數(shù)值個(gè)數(shù)。例如，如果要計(jì)算輸入到三個(gè)單元格中的三個(gè)數(shù)值區(qū)間（間隔），請(qǐng)一定在四個(gè)單元格中輸入FREQUENCY函

3、數(shù)計(jì)算的結(jié)果。多出來(lái)的單元格將返回data_array中大于第三個(gè)間隔值的數(shù)值個(gè)數(shù)。函數(shù)FREQUENCY將忽略空白單元格和文本。對(duì)于返回結(jié)果為數(shù)組的公式，必須以數(shù)組公式的形式輸入。5、不及格紅色顯示“格式”菜單中的“條件格式”命令6、排名顯示=RANK(number,ref,order)其中number為需要找到排位的數(shù)字。Ref為包含一組數(shù)字的數(shù)組或引用。Ref中的非數(shù)值型參數(shù)將被忽略。Order為一數(shù)字，指明排位的方式。如果order為0或省略，MicrosoftExcel將ref當(dāng)作按降序排列的數(shù)據(jù)清單進(jìn)行排位。如果order不為零，MicrosoftExcel將ref當(dāng)作按升序排列的

4、數(shù)據(jù)清單進(jìn)行排位。7、CountIF代替人工數(shù)數(shù)COUNTIF函數(shù)的16種公式設(shè)置1、返加包含值12的單元格數(shù)量=COUNTIF（DATA,12）2、返回包含負(fù)值的單元格數(shù)量=COUNTIF（DATA，0）3、返回不等于0的單元格數(shù)量=COUNTIF（DATA，0）4、返回大于5的單元格數(shù)量=COUNTIF（DATA，5）5、返回等于單元格A1中內(nèi)容的單元格數(shù)量=COUNTIF（DATA，A1）6、返回大于單元格A1中內(nèi)容的單元格數(shù)量=COUNTIF（DATA，“”&A1）7、返回包含文本內(nèi)容的單元格數(shù)量=COUNTIF（DATA，“*”）8、返回包含三個(gè)字符內(nèi)容的單元格數(shù)量=COUNITF（

5、DATA，“?”）9、返回包含單詞GOOD(不分大小寫(xiě))內(nèi)容的單元格數(shù)量=COUNTIF（DATA，“GOOD”）10、返回在文本中任何位置包含單詞GOOD字符內(nèi)容的單元格數(shù)量=COUNTIF（DATA，“*GOOD*”）11、返回包含以單詞AB(不分大小寫(xiě))開(kāi)頭內(nèi)容的單元格數(shù)量=COUNTIF（DATA，“AB*”）12、返回包含當(dāng)前日期的單元格數(shù)量=COUNTIF（DATA，TODAY()）13、返回大于平均值的單元格數(shù)量=COUNTIF（DATA，&AVERAGE(DATA)14、返回平均值上面超過(guò)三個(gè)標(biāo)準(zhǔn)誤差的值的單元格數(shù)量=COUNTIF（DATA，“&AVERAGE(DATA)+S

6、TDEV(DATA)*3)15、返回包含值為或-3的單元格數(shù)量=COUNTIF（DATA，3）+COUNIF（DATA，-3）16、返回包含值;邏輯值為T(mén)RUE的單元格數(shù)量=COUNTIF（DATA，TRUE）8、數(shù)據(jù)透視表EXCEL中統(tǒng)計(jì)學(xué)生成績(jī)常用的函數(shù)一、前言對(duì)于教師而言，經(jīng)常需要用Excel進(jìn)行學(xué)生成績(jī)統(tǒng)計(jì)，會(huì)被一些常見(jiàn)問(wèn)題難住。碰到的難題主要有：如何統(tǒng)計(jì)不同分?jǐn)?shù)段人數(shù)、如何在保持學(xué)號(hào)不變前提下進(jìn)行排名、如何將百分制轉(zhuǎn)換成不同分?jǐn)?shù)段與如何用紅色顯示不及格的分?jǐn)?shù)等，本文著重對(duì)這些問(wèn)題的解決方法與技巧加以分析和討論。本文假設(shè)讀者已對(duì)Excel的基本操作已經(jīng)有一定基礎(chǔ)，已經(jīng)掌握如何進(jìn)行求和、

7、求平均和如何使用自動(dòng)填充柄進(jìn)行復(fù)制公式等操作，本文對(duì)這些一般性操作不做詳細(xì)介紹，僅對(duì)一些難度較大的操作技巧進(jìn)行討論。二、Excel統(tǒng)計(jì)學(xué)生成績(jī)時(shí)的四個(gè)難題假設(shè)在統(tǒng)計(jì)學(xué)生成績(jī)時(shí)，我們需要統(tǒng)計(jì)出如圖1所示的相關(guān)結(jié)果。圖1學(xué)生成績(jī)統(tǒng)計(jì)所需要的結(jié)果圖這里，假設(shè)學(xué)號(hào)、姓名、成績(jī)等列及行15都已經(jīng)事先輸好，需要讓Excel統(tǒng)計(jì)其他的相關(guān)數(shù)據(jù)結(jié)果。這時(shí)，成績(jī)統(tǒng)計(jì)中主要難解決的問(wèn)題及它們?cè)趫D中的位置如下：?jiǎn)栴}1：如何統(tǒng)計(jì)不同分?jǐn)?shù)段的學(xué)生人數(shù)?(圖中A16E16)問(wèn)題2：如何在保持學(xué)號(hào)順序不變的前提下進(jìn)行學(xué)生成績(jī)名次排定?(圖中F2F13)問(wèn)題3：如何將百分制轉(zhuǎn)換成不同的等級(jí)分?(圖中“等級(jí)1”與“等級(jí)2”列)

8、問(wèn)題4：如何使不及格的分?jǐn)?shù)以紅色顯示?(圖中紅色顯示部分，即第12行)三、解決統(tǒng)計(jì)學(xué)生成績(jī)時(shí)的四個(gè)難題的方法下面，針對(duì)上面提出的四個(gè)難題分別討論解決的方法與技巧。1、統(tǒng)計(jì)不同分?jǐn)?shù)段的學(xué)生人數(shù)統(tǒng)計(jì)不同分?jǐn)?shù)段的學(xué)生人數(shù)是非常常見(jiàn)的需求，其所需結(jié)果如圖1中A16E16所示。這里，假設(shè)需要統(tǒng)計(jì)90100、8089、7079、6069及低于60分五個(gè)不同分?jǐn)?shù)段的人數(shù)。通常，統(tǒng)計(jì)不同分?jǐn)?shù)段最好的方法是利用COUNTIF(X，Y)函數(shù)。其中有兩個(gè)參數(shù)，第一個(gè)參數(shù)X為統(tǒng)計(jì)的范圍，一般最好用絕對(duì)引用;第二個(gè)參數(shù)Y為統(tǒng)計(jì)條件，要加引號(hào)。對(duì)于小于60分的人數(shù)只要用一個(gè)COUNTIF()函數(shù)，如在E16單元格中輸入公

9、式：=COUNTIF($C$2:$C$13,60)。對(duì)于其他在兩個(gè)分?jǐn)?shù)之間的分?jǐn)?shù)段的人數(shù)統(tǒng)計(jì)，需要用兩個(gè)COUNTIF()函數(shù)相減。如在A16單元格中輸入公式：=COUNTIF($C$2:$C$13,=100)-COUNTIF($C$2:$C$13,90)，即用小于等于100的人數(shù)減去小于90的人數(shù)。如果要統(tǒng)計(jì)8089、7079與6069分?jǐn)?shù)段的人數(shù)，只要利用自動(dòng)填充柄將該公式復(fù)制到右邊三個(gè)單元格，再把=100與=90,A,IF(C2=80,B,IF(C2=70,C,IF(C2=60,D,E)，然后，利用自動(dòng)填充柄將其復(fù)制到下方的幾個(gè)單元格。為了得到“等級(jí)2”列所要的等級(jí)結(jié)果，可以在E2單元格

10、中輸入公式：=IF(C2=90,優(yōu),IF(C2=75,良,IF(C2=60,中,不及格)，然后，利用自動(dòng)填充柄將其復(fù)制到下方的幾個(gè)單元格。4、使不及格的分?jǐn)?shù)以紅色顯示統(tǒng)計(jì)學(xué)生成績(jī)時(shí)經(jīng)常需要將不及格的分?jǐn)?shù)用紅色顯示，其結(jié)果如圖1中紅色顯示部分(如第12行)。使不及格的分?jǐn)?shù)以紅色顯示需要使用“格式”菜單中的“條件格式”命令。該命令會(huì)彈出一個(gè)對(duì)話框，其中要求確認(rèn)條件與相應(yīng)的格式。對(duì)于“成績(jī)”列，可先選中C2：C13，然后使用“格式”菜單中的“條件格式”命令，在彈出的對(duì)話框中，左邊使用默認(rèn)的“單元格數(shù)值”，中間選“小于”，右邊填寫(xiě)60，然后單擊右邊的“格式”按鈕，從中選擇紅色，最后單擊兩次“確定”按鈕

11、。對(duì)于“等級(jí)1”列，可先選中D2：D13，然后使用“格式”菜單中的“條件格式”命令，在彈出的對(duì)話框中，左邊使用默認(rèn)的“單元格數(shù)值”，中間選“等于”，右邊填寫(xiě)E，然后單擊右邊的“格式”按鈕，從中選擇紅色，最后單擊兩次“確定”按鈕?！暗燃?jí)2”列類(lèi)似。對(duì)于其他的一些統(tǒng)計(jì)計(jì)算要求，如怎樣計(jì)算各分?jǐn)?shù)段的百分比、如何計(jì)算機(jī)優(yōu)良率與合格率等功能，應(yīng)該比較簡(jiǎn)單，本文此處不贅述。Excel中用五種函數(shù)統(tǒng)計(jì)學(xué)生考試成績(jī)分?jǐn)?shù)段先看看原始的學(xué)生成績(jī)表。五門(mén)功課的成績(jī)分布在C2:G47單元格區(qū)域，如下圖所示。一、利用COUNTIF函數(shù)COUNTIF函數(shù)可以統(tǒng)計(jì)單元格區(qū)域內(nèi)滿足指定條件的單元格數(shù)目，所以用來(lái)統(tǒng)計(jì)分?jǐn)?shù)段人數(shù)

12、順理成章。我們用它來(lái)統(tǒng)計(jì)C列的語(yǔ)文成績(jī)分?jǐn)?shù)段。如圖2所示，我們需要在N2單元格統(tǒng)計(jì)語(yǔ)文分?jǐn)?shù)在90分以上的學(xué)生數(shù)。那么只需要在N2單元格輸入公式“=COUNTIF(C2:C47，=90)”就可以了。其含義就是統(tǒng)計(jì)C2:C47單元格區(qū)域中滿足大于等于90的單元格數(shù)目。所以，要統(tǒng)計(jì)80分至89分這一段的學(xué)生數(shù)，那么就需要輸入公式“=COUNTIF(C2:C47，=80)-COUNTIF(C2:C47，=90)”。很明顯，大于等于80分的人數(shù)減去大于等于90分的人數(shù)正是我們想要的人數(shù)。其他分?jǐn)?shù)段可以依此類(lèi)推。二、利用FREQUENCY函數(shù)這是一個(gè)專(zhuān)門(mén)用于統(tǒng)計(jì)單元格區(qū)域中數(shù)據(jù)的頻率分布的函數(shù)，用它來(lái)統(tǒng)計(jì)

13、分?jǐn)?shù)段自然名正言順。以D列的數(shù)學(xué)成績(jī)的統(tǒng)計(jì)為例。我們先在M8:M12設(shè)置好分?jǐn)?shù)段，再在L8:L12單元格區(qū)域設(shè)置好各分?jǐn)?shù)段的分隔數(shù)(即為該分?jǐn)?shù)段的上限數(shù)字)，如圖3所示。選中N8:N12單元格，在編輯欄輸入公式“=FREQUENCY($D$2:$D$47，$L$8:$L$12)”，然后按下“Ctrl+Shift+Enter”組合鍵確認(rèn)，即可在公式的兩端添加數(shù)組公式的標(biāo)志“”，同時(shí)可以看到各分?jǐn)?shù)段的人數(shù)已經(jīng)統(tǒng)計(jì)完成了。需要注意的是公式輸入完成后必須按“Ctrl+Shift+Enter”組合鍵確認(rèn)以產(chǎn)生數(shù)組公式，而且數(shù)組公式的標(biāo)志“”也不可以手工輸入。三、利用DCOUNT函數(shù)DCOUNT函數(shù)平時(shí)我

14、們不大用，它可以從滿足指定條件的數(shù)據(jù)庫(kù)記錄的字段中計(jì)算數(shù)值單元格數(shù)目。這話聽(tīng)起來(lái)似乎不太容易懂，但用它統(tǒng)計(jì)分?jǐn)?shù)段是很不錯(cuò)的，比如統(tǒng)計(jì)E列的英語(yǔ)成績(jī)。我們需要事先做一個(gè)設(shè)置。如圖4所示，以統(tǒng)計(jì)80分至89分的人數(shù)為例：我們需要在Q16和R16單元格分?jǐn)?shù)輸入“英語(yǔ)”(必須與原始成績(jī)表的列標(biāo)題一致)，然后在Q17和R17單元格分別輸入“=80”，“=90)*($F$2:$F$47=70)*($F$2:$F$47=90)*($G$2:$G$47=80)*($G$2:$G$4790)”，其他依此類(lèi)推就行了。好了，五種方法統(tǒng)計(jì)分?jǐn)?shù)段人數(shù)，相信一定有一種方法適合你。以上各種方法使用環(huán)境均為Excel2007

15、，其他版本可以參照使用。一、求各種分?jǐn)?shù)求總分：主要用SUM函數(shù)，其語(yǔ)法格式為SUM(Ref)，此處Ref為參與計(jì)算的單元格區(qū)域。例如：SUM(B2:E2)是表示求B2、C2、D2、E2四個(gè)單元格內(nèi)數(shù)字的和。另外還用到SUMIF函數(shù)，語(yǔ)法格式為SUMIF(Range,Criteria,Sum_range)，其功能是根據(jù)指定條件對(duì)若干單元格求和，參數(shù)Range表示引用，用于條件判斷的單元格區(qū)域。Criteria表示數(shù)字、表達(dá)式或文本，指出哪些單元格符合被相加求和的條件。Sum_range表示引用，需要求和的實(shí)際單元格。注意：Criteria如果是文本，那么引號(hào)應(yīng)該是半角的，而不是全角的，否則會(huì)出錯(cuò)

16、！求平均分：用AVERAGE函數(shù)，其語(yǔ)法格式為AVERAGE(Ref)，此處Ref為參與計(jì)算的單元格區(qū)域。例如AVERAGE(F2:F50)是求F2:F50區(qū)域內(nèi)數(shù)字的平均值。默認(rèn)情況下，Excel2002會(huì)忽略掉空白的單元格，但是它不忽略數(shù)值為0的單元格，要想忽略數(shù)值為0的單元格需要用到COUNTIF函數(shù)，其語(yǔ)法為COUNTIF(Range,Criteria)，其含義是計(jì)算某個(gè)區(qū)域中滿足給定條件的單元格數(shù)目。本例求F2:F50的平均分，如果忽略數(shù)值為0的單元格可以這樣計(jì)算：SUM(F2:F50)/COUNTIF(F2:F50,0)。另外如果要求去掉幾個(gè)最高分和幾個(gè)最低分然后取平均分的話，用到

17、LARGE和SMALL函數(shù)，其語(yǔ)法格式為L(zhǎng)ARGE(array,k)，含義是返回?cái)?shù)組中第k個(gè)最大值，SMALL(array,k)的含義是返回?cái)?shù)組中第k個(gè)最小值。如果我們求F2:F50中去掉兩個(gè)最高分和兩個(gè)最低分之后的平均分可以這樣計(jì)算：“SUM(F2:F50)-LARGE(F2:F50,1)-LARGE(F2:F50,2)-SMALL(F2:F50,1)-SMALL(F2:F50,2)/COUNTIF(F2:F50-4)”。求最高分、最低分：MAX和MIN函數(shù)，語(yǔ)法格式分別為MAX(Ref)和MIN(Ref)，如上例中求F2:F50的最高分和最低分，應(yīng)該這樣：MAX(F2:F50)和MIN(F

18、2:F50)。HYPERLINK/gxd2980449/gxd2980449二、求及格率、優(yōu)秀率求及格率：及格率即一個(gè)班級(jí)中某一科大于等于60分的比例，例如：B2:B50中是某一個(gè)班的語(yǔ)文成績(jī)，可以這樣求及格率：COUNTIF(B2:B50,=60)/COUNT(B2:B50)。求優(yōu)秀率：例如：B2:B50存放的是初一一班的語(yǔ)文期末考試成績(jī)，B2:B500存放的是初一全年級(jí)語(yǔ)文考試的成績(jī)，如果規(guī)定全年級(jí)20%的學(xué)生為優(yōu)秀，那么初一一班語(yǔ)文的優(yōu)秀率應(yīng)該這樣計(jì)算：“COUNTIF(B2:B50,=&LARGE(B2:B500,INT(0.2COUNT(B2:B500)/COUNT(B2:B50)”

19、，其中LARGE(B2:B500,INT(0.2COUNT(B2:B500)所求的是全年級(jí)語(yǔ)文分?jǐn)?shù)前20%中最低的一個(gè)同學(xué)的分?jǐn)?shù)，COUNTIF(B2:B50,=&LARGE(B2:B500,INT(0.2*COUNT(B2:B500)則是求出了初一一班語(yǔ)文高于或等于這個(gè)同學(xué)分?jǐn)?shù)的人數(shù)，最后再除以初一一班的總?cè)藬?shù)COUNT(B2:B50)，所得就是初一一班的語(yǔ)文優(yōu)秀率，如果想求其他班其他科目的優(yōu)秀率，道理都是一樣。用Excel函數(shù)快速統(tǒng)計(jì)學(xué)生期中考試成績(jī)期中考試結(jié)束，各學(xué)科考試成績(jī)迅速匯總到班主任這里。這時(shí)候，班主任最忙的，就是要迅速統(tǒng)計(jì)各學(xué)科成績(jī)的各項(xiàng)指標(biāo)，比如平均分、最高分、優(yōu)秀率、及格率

20、以及各學(xué)科分?jǐn)?shù)的頻率統(tǒng)計(jì)等等。雖然現(xiàn)在普遍都在使用Excel進(jìn)行這項(xiàng)工作，不過(guò)，要想使這項(xiàng)工作能夠高效準(zhǔn)確地完成，那還得請(qǐng)Excel函數(shù)來(lái)幫忙才行。匯總到班主任這里的成績(jī)表如圖1所示，各科成績(jī)分布在C2:C95單元格區(qū)域。我們先在K2:Q15單元格區(qū)域建立如圖2所示表格用以存放各項(xiàng)統(tǒng)計(jì)結(jié)果。先點(diǎn)擊M3單元格，輸入如下公式：=AVERAGE(C2:C95)，回車(chē)后即可得到語(yǔ)文平均分。點(diǎn)擊M4單元格，輸入公式：=MAX(C$2:C$95)，回車(chē)即可得到語(yǔ)文成績(jī)中的最高分。優(yōu)秀率是計(jì)算分?jǐn)?shù)高于或等于85分的學(xué)生的比率。點(diǎn)擊M5單元格，輸入公式：=COUNTIF(C$2:C$95,=85)/COUNT

21、(C$2:C$95)，回車(chē)所得即為語(yǔ)文學(xué)科的優(yōu)秀率。點(diǎn)擊M6單元格，輸入公式：=COUNTIF(C$2:C$95,=60)/COUNT(C$2:C$95)，回車(chē)所得即為及格率。選中M3:M6單元格，拖動(dòng)填充句柄向右填充公式至Q6單元格，松開(kāi)HYPERLINK/hardware/input/t_blank鼠標(biāo)，各學(xué)科的統(tǒng)計(jì)數(shù)據(jù)就出來(lái)了。再選中M5:Q6單元格區(qū)域，點(diǎn)擊菜單命令“格式單元格”，打開(kāi)“單元格格式”對(duì)話框。點(diǎn)擊“數(shù)字”選項(xiàng)卡，在左側(cè)“分類(lèi)”列表中選擇“百分比”，如圖3所示，確定后可將M5:Q6單元格區(qū)域的數(shù)據(jù)轉(zhuǎn)變成百分比形式。至于各科分?jǐn)?shù)段人數(shù)的統(tǒng)計(jì)，那得先選中M8:M15單元格，在編

22、輯欄中輸入公式：=FREQUENCY(C$2:C$95,$K$8:$K$15)。然后按下“Ctrl+Shift+Enter”快捷鍵，可以看到在公式的最外層加上了一對(duì)大括號(hào)?，F(xiàn)在，我們就已經(jīng)得到了語(yǔ)文學(xué)科各分?jǐn)?shù)段人數(shù)了。在K列中的那些數(shù)字，就是我們統(tǒng)計(jì)各分?jǐn)?shù)段時(shí)的分?jǐn)?shù)分界點(diǎn)?，F(xiàn)在再選中M8:M15單元格，拖動(dòng)其填充句柄向右至Q列，那么，其它學(xué)科的分?jǐn)?shù)段人數(shù)也立即顯示在我們眼前了。最終的結(jié)果如圖4所示。如果覺(jué)得K列的數(shù)據(jù)有礙觀瞻，那么可以選中它們，然后設(shè)置它們的字體顏色為白色就可以了。Excel2007巧妙統(tǒng)計(jì)混合編班分?jǐn)?shù)段期末考試之后，自然就是成績(jī)的統(tǒng)計(jì)和分析了。為了使成績(jī)的錄入和分析更為公平，

23、我們學(xué)校都是由專(zhuān)人統(tǒng)一錄入的。平行班級(jí)的成績(jī)都錄入在Excel的同一工作表中。但是這樣做之后，也為我們后期的成績(jī)分析制造了麻煩。由于工作表中混合編班，那么在統(tǒng)計(jì)各班各學(xué)科各分?jǐn)?shù)段的人數(shù)時(shí)，就不得不進(jìn)行多次按班、按學(xué)科進(jìn)行排序，然后人工數(shù)出相應(yīng)分?jǐn)?shù)段人數(shù)，非常麻煩。其實(shí)，如果我們是用Excel2007進(jìn)行相應(yīng)的工作的話，那么就可以利用新增的COUNTIFS函數(shù)來(lái)完成這項(xiàng)任務(wù)，非常方便。一、1班語(yǔ)文學(xué)科分?jǐn)?shù)段統(tǒng)計(jì)圖1學(xué)生成績(jī)表圖1所示為一次考試的學(xué)生成績(jī)表。在不對(duì)成績(jī)進(jìn)行排序等操作的前提下，我們將完成圖2所示分?jǐn)?shù)段統(tǒng)計(jì)表。圖2Excel2007繪制表格具體過(guò)程如下：以1班語(yǔ)文學(xué)科的分?jǐn)?shù)段統(tǒng)計(jì)為例。

24、將HYPERLINK/hardware/input/t_blank鼠標(biāo)定位于統(tǒng)計(jì)表中的N3單元格，在編輯欄輸入如下公式“=COUNTIFS($A$2:$A$108,$L3,$C$2:$C$108,100)”，按下回車(chē)鍵，則可以得到1班語(yǔ)文成績(jī)?yōu)?00分的學(xué)生人數(shù)。還是先說(shuō)說(shuō)這個(gè)COUNTIFS函數(shù)吧。COUNTIFS函數(shù)的作用是統(tǒng)計(jì)某個(gè)區(qū)域中滿足多重條件的單元格個(gè)數(shù)。其語(yǔ)法格式是：COUNTIFS(range1,criteria1,range2,criteria2)。其中Range1,range2,是指對(duì)哪些區(qū)域在統(tǒng)計(jì)時(shí)施加條件。Criteria1,criteria2,則是指具體施加的條件是什

25、么。比如上例的公式的含義則是統(tǒng)計(jì)A2:A108單元格區(qū)域中等于L3單元格（即1班）且C2:C108單元格區(qū)域中等于100分的單元格個(gè)數(shù)。COUNTIFS所支持的施加條件個(gè)數(shù)為127個(gè)，足夠我們使用的。因此，統(tǒng)計(jì)1班語(yǔ)文分?jǐn)?shù)在90分至100分之間的人數(shù)時(shí)，我們只需要再增加一個(gè)條件。在O3單元格輸入如下公式：“=COUNTIFS($A$2:$A$108,$L3,$C$2:$C$108,=90,$C$2:$C$108,100)”即可。拖動(dòng)該單元格的填充句柄向右復(fù)制公式，至S3單元格。然后將公式中的分?jǐn)?shù)作出相應(yīng)的調(diào)整，那么就會(huì)迅速得到各分?jǐn)?shù)段的相應(yīng)人數(shù)了。至于最后的那個(gè)50分以下的人數(shù)統(tǒng)計(jì)，很顯然，其

26、公式應(yīng)該是“=COUNTIFS($A$2:$A$108,$L3,$C$2:$C$108,50)”。二、其它學(xué)科及班級(jí)分?jǐn)?shù)段統(tǒng)計(jì)選中N3:T3單元格區(qū)域，向下拖動(dòng)填充句柄至T7，則可以將1班的分?jǐn)?shù)段表格迅速填充完成。但是，現(xiàn)在每一行所填充的數(shù)據(jù)，都是語(yǔ)文學(xué)科相應(yīng)的分?jǐn)?shù)段人數(shù)。我們還需要將公式中的“$C”（語(yǔ)文成績(jī)所在列）改成各學(xué)科相應(yīng)的列號(hào)。不過(guò)，這個(gè)工作也不是太麻煩。以數(shù)學(xué)科為例。只要選中N4:T4單元格區(qū)域，然后按下“Ctrl+F”快捷鍵，打開(kāi)“查找和替換”對(duì)話框。點(diǎn)擊“替換”選項(xiàng)卡，在“查找內(nèi)容”的輸入框中輸入“$C”，在“替換為”輸入框中輸入“$D”（數(shù)學(xué)成績(jī)所在的列號(hào)），如圖3所示。然

27、后點(diǎn)擊“全部替換”按鈕，那么就可以得到數(shù)學(xué)學(xué)科的各分?jǐn)?shù)段了。圖3Excel2007中執(zhí)行替換其它幾個(gè)學(xué)科照此辦理，將公式中的“$C”分別再替換為“$E”、“$F”、“$G”，很快就可以得到1班各學(xué)科的分?jǐn)?shù)段統(tǒng)計(jì)。1班各學(xué)科分?jǐn)?shù)段全部統(tǒng)計(jì)完成后，其他各班就非常容易了。只要選中N3:T7單元格區(qū)域，然后按下“Ctrl+C”快捷鍵進(jìn)行復(fù)制，再將HYPERLINK/hardware/input/t_blank鼠標(biāo)定位于N8單元格，按下“Ctrl+V”快捷鍵進(jìn)行粘貼，那么2班分?jǐn)?shù)段統(tǒng)計(jì)立刻完成了。照此辦法，只要復(fù)制、粘貼，呵呵，瞬間完成。所得結(jié)果如圖4所示。圖4Excel2007完成表格提醒一下：L列中

28、的班級(jí)為輔助列，是為了我們?cè)诠街性O(shè)置班級(jí)方便而加的，不能刪除。從表格美觀的角度出發(fā)，可以選中整個(gè)L列后，點(diǎn)擊右鍵，在彈出的菜單中點(diǎn)擊“隱藏”命令，將其隱藏。用Excel統(tǒng)計(jì)各分?jǐn)?shù)段學(xué)生數(shù)教師常常要統(tǒng)計(jì)各學(xué)科相應(yīng)分?jǐn)?shù)段的學(xué)生人數(shù)，以方便對(duì)考試情況作全方位的對(duì)比分析。在Excel中，有多種函數(shù)可以實(shí)現(xiàn)這種統(tǒng)計(jì)工作，筆者以圖1所示的成績(jī)表為例，給出多種統(tǒng)計(jì)方法。文章末尾提供.xls文件供大家下載參考。文章導(dǎo)讀：方法一：用COUNTIF函數(shù)統(tǒng)計(jì)這是最常用、最容易理解的一種方法，我們用它來(lái)統(tǒng)計(jì)“語(yǔ)文”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。如果某些學(xué)科（如體育），其成績(jī)是不具體數(shù)值，而是字符等級(jí)（如“優(yōu)秀、良好”等），我

29、們也可以用COUNTIF函數(shù)來(lái)統(tǒng)計(jì)各等級(jí)的學(xué)生人數(shù)。方法二：用DCOUNT函數(shù)統(tǒng)計(jì)這個(gè)函數(shù)不太常用，但用來(lái)統(tǒng)計(jì)分?jǐn)?shù)段學(xué)生數(shù)效果很不錯(cuò)。我們用它統(tǒng)計(jì)“數(shù)學(xué)”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。方法三：用FREQUENCY函數(shù)統(tǒng)計(jì)這是一個(gè)專(zhuān)門(mén)用于統(tǒng)計(jì)某個(gè)區(qū)域中數(shù)據(jù)的頻率分布函數(shù)，我們用它來(lái)統(tǒng)計(jì)“英語(yǔ)”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。方法四：用SUM函數(shù)統(tǒng)計(jì)我們知道SUM函數(shù)通常是用來(lái)求和的，其實(shí)，他也可以用來(lái)進(jìn)行多條件計(jì)數(shù)，我們用它來(lái)統(tǒng)計(jì)“政治”學(xué)科各分?jǐn)?shù)段的學(xué)生數(shù)。方法一：用COUNTIF函數(shù)統(tǒng)計(jì)這是最常用、最容易理解的一種方法，我們用它來(lái)統(tǒng)計(jì)“語(yǔ)文”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。函數(shù)功能及用法介紹分別選中C63、C67單元格，輸入

30、公式：=COUNTIF(C3:C62,=90)，即可統(tǒng)計(jì)出“語(yǔ)文”成績(jī)“低于60分”和“大于等于90”的學(xué)生人數(shù)。分別選中C64、C65和C66單元格，輸入公式：=COUNTIF(C3:C62,=60)-COUNTIF(C3:C62,=70)、=COUNTIF(C3:C62,=70)-COUNTIF(C3:C62,=80)和=COUNTIF(C3:C62,=80)-COUNTIF(C3:C62,=90)，即可統(tǒng)計(jì)出成績(jī)?cè)?0-69分、70-79分、80-89分區(qū)間段的學(xué)生人數(shù)。注意：同時(shí)選中C63至C67單元格，將鼠標(biāo)移至C67單元格右下角，成細(xì)十字線狀時(shí)，按住左鍵向右拖拉至I列，就可以統(tǒng)計(jì)出

31、其它學(xué)科各分?jǐn)?shù)段的學(xué)生數(shù)。如果某些學(xué)科（如體育），其成績(jī)是不具體數(shù)值，而是字符等級(jí)（如“優(yōu)秀、良好”等），我們可以用COUNTIF函數(shù)來(lái)統(tǒng)計(jì)各等級(jí)的學(xué)生人數(shù)。如果某些學(xué)科（如體育），其成績(jī)是不具體數(shù)值，而是字符等級(jí)（如“優(yōu)秀、良好”等），我們可以用COUNTIF函數(shù)來(lái)統(tǒng)計(jì)各等級(jí)的學(xué)生人數(shù)。在K64至K67單元格中，分別輸入成績(jī)等級(jí)字符（參見(jiàn)圖2）。選中L64單元格，輸入公式：=COUNTIF($L$3:$L$62,K64)，統(tǒng)計(jì)出“優(yōu)秀”的學(xué)生人數(shù)。再次選中L64單元格，用“填充柄”將上述公式復(fù)制到L65至L67單元格中，統(tǒng)計(jì)出其它等級(jí)的學(xué)生人數(shù)。上述全部統(tǒng)計(jì)結(jié)果參見(jiàn)圖1。方法二：用DCOUN

32、T函數(shù)統(tǒng)計(jì)這個(gè)函數(shù)不太常用，但用來(lái)統(tǒng)計(jì)分?jǐn)?shù)段學(xué)生數(shù)效果很不錯(cuò)。我們用它統(tǒng)計(jì)“數(shù)學(xué)”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。分別選中M63至N72單元格區(qū)域（不一定非得不這個(gè)區(qū)域），輸入學(xué)科名稱(chēng)（與統(tǒng)計(jì)學(xué)科名稱(chēng)一致，如“數(shù)學(xué)”等）及相應(yīng)的分?jǐn)?shù)段（如圖2）。分別選中D63、D64D67單元格，輸入公式：=DCOUNT(D2:D62,數(shù)學(xué),M63:N64)、=DCOUNT(D2:D62,數(shù)學(xué),M65:N66)、=DCOUNT(D2:D62,數(shù)學(xué),M67:N68)、=DCOUNT(D2:D62,數(shù)學(xué),M69:N70)、=DCOUNT($D$2:$D$62,數(shù)學(xué),M71:N72)，確認(rèn)即可。注意：將上述公式中的“DCOUN

33、T”函數(shù)換成“DCOUNTA”函數(shù)，同樣可以實(shí)現(xiàn)各分?jǐn)?shù)段學(xué)生人數(shù)的統(tǒng)計(jì)。方法三：用FREQUENCY函數(shù)統(tǒng)計(jì)這是一個(gè)專(zhuān)門(mén)用于統(tǒng)計(jì)某個(gè)區(qū)域中數(shù)據(jù)的頻率分布函數(shù)，我們用它來(lái)統(tǒng)計(jì)“英語(yǔ)”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。分別選中O64至O67單元格，輸入分?jǐn)?shù)段的分隔數(shù)值（參見(jiàn)圖2）。同時(shí)選中E63至E67單元格區(qū)域，在“編輯欄”中輸入公式：=FREQUENCY(E3:E62,$O$64:$O$67)，輸入完成后，按下“Ctrl+Shift+Enter”組合鍵進(jìn)行確認(rèn)，即可一次性統(tǒng)計(jì)出“英語(yǔ)”學(xué)科各分?jǐn)?shù)段的學(xué)生人數(shù)。注意：實(shí)際上此處輸入的是一個(gè)數(shù)組公式，數(shù)組公式輸入完成后，不能按“Enter”鍵進(jìn)行確認(rèn)，而是要按“

34、Ctrl+Shift+Enter”組合鍵進(jìn)行確認(rèn)。確認(rèn)完成后，在公式兩端出現(xiàn)一個(gè)數(shù)組公式的標(biāo)志“”（該標(biāo)志不能用鍵盤(pán)直接輸入）。數(shù)組公式也支持用“填充柄”拖拉填充：同時(shí)選中E63至E67單元格區(qū)域，將鼠標(biāo)移至E67單元格右下角，成細(xì)十字線狀時(shí)，按住左鍵向右拖拉，就可以統(tǒng)計(jì)出其它學(xué)科各分?jǐn)?shù)段的學(xué)生數(shù)。方法四：用SUM函數(shù)統(tǒng)計(jì)我們知道SUM函數(shù)通常是用來(lái)求和的，其實(shí)，他也可以用來(lái)進(jìn)行多條件計(jì)數(shù)，我們用它來(lái)統(tǒng)計(jì)“政治”學(xué)科各分?jǐn)?shù)段的學(xué)生數(shù)。分別選中P64至P69單元格，輸入分?jǐn)?shù)段的分隔數(shù)值（參見(jiàn)圖2）。選中F63單元格，輸入公式：=SUM($F$3:$F$62=P64)*($F$3:$F$62再次選

35、中F63單元格，用“填充柄”將上述公式復(fù)制到F64至F67單元格中，統(tǒng)計(jì)出其它各分?jǐn)?shù)段的學(xué)生人數(shù)。注意：用此法統(tǒng)計(jì)時(shí)，可以不引用單元格，而直接采用分?jǐn)?shù)值。例如，在F64單元格中輸入公式：=SUM($F$3:$F$62=60)*($F$3:$F$6270)，也可以統(tǒng)計(jì)出成績(jī)?cè)?0-69分之間的學(xué)生人數(shù)。注意：為了表格整體的美觀，我們將M至P列隱藏起來(lái)：同時(shí)選中M至P列，右擊鼠標(biāo)，在隨后出現(xiàn)的快捷菜單中，選“隱藏”選項(xiàng)。輕松實(shí)現(xiàn)多條件下的數(shù)據(jù)統(tǒng)計(jì)在日常對(duì)數(shù)據(jù)的統(tǒng)計(jì)過(guò)程中，我們經(jīng)常會(huì)遇到指定條件的數(shù)據(jù)統(tǒng)計(jì)問(wèn)題，用公式或分類(lèi)匯總可以滿足我們的需要，但對(duì)于復(fù)雜條件的數(shù)據(jù)統(tǒng)計(jì)，不少初學(xué)用戶覺(jué)得非常棘手，其

36、實(shí)用好自帶的“條件求和”功能，這些問(wèn)題就可以迎刃而解了。圖1為某單位職工數(shù)據(jù)表，我們以統(tǒng)計(jì)二車(chē)間的高級(jí)工程師中男性的人數(shù)為例，介紹一下如何利用“條件求和”功能實(shí)現(xiàn)多條件限制下的數(shù)據(jù)統(tǒng)計(jì)過(guò)程。圖1單位職工數(shù)據(jù)表1.依次選擇“工具向?qū)l件求和”菜單命令，在彈出的“條件求和向?qū)?4步驟之1”界面中，確認(rèn)條件求和的數(shù)據(jù)區(qū)域?yàn)檎麄€(gè)表格（只需單擊表格內(nèi)的任一單元格再執(zhí)行該命令，系統(tǒng)會(huì)自動(dòng)選中整個(gè)表格區(qū)域），單擊“下一步”按鈕。2.在“條件求和向?qū)?4步驟之2”界面中選擇“求和列”為“性別”，將限制的條件“崗位=二車(chē)間、職稱(chēng)=高級(jí)工程師、性別=男”逐條選擇并添加到條件列表中，單擊“下一步”按鈕（如圖2）。圖

37、2指定條件數(shù)據(jù)3.在“條件求和向?qū)?4步驟之3”界面中選擇統(tǒng)計(jì)結(jié)果的顯示方式為“復(fù)制公式及條件”，單擊“下一步”按鈕。4.最后要選擇目標(biāo)數(shù)據(jù)存放的單元格，本例中依次選擇C18、D18、E18、F18單元格，并在每次選擇后單擊“下一步”按鈕，直到最后單擊“完成”按鈕。此時(shí)在F18單元格中就會(huì)出現(xiàn)我們要統(tǒng)計(jì)的數(shù)據(jù)結(jié)果。在本例中，我們可以看出，如果是簡(jiǎn)單條件的數(shù)據(jù)統(tǒng)計(jì)其實(shí)也可以用公式來(lái)實(shí)現(xiàn)，如統(tǒng)計(jì)“二車(chē)間”每月計(jì)發(fā)的工資總額，可以用公式“=SUMIF(E2E15,二車(chē)間,F2F15)”來(lái)實(shí)現(xiàn)，當(dāng)然也可以使用分類(lèi)匯總命令來(lái)實(shí)現(xiàn)。但對(duì)于復(fù)雜條件的數(shù)據(jù)統(tǒng)計(jì)用公式就顯得有些難度了，特別是對(duì)于不熟悉函數(shù)的讀者，而用“條件求和”功能則可以很方便地實(shí)現(xiàn)我們的統(tǒng)計(jì)需求。Cico提示：初次使用“條件求和”功能必須先安裝。單擊“工具”菜單中的“加載宏”，在“加載宏”對(duì)話框中勾選“條件求和向?qū)А睆?fù)選框，單擊“確定”按鈕，此時(shí)系統(tǒng)excel筆記之兩表內(nèi)容比較VLOOKUP函數(shù)在平常的工作中，同一個(gè)班，同一級(jí)，會(huì)有許多的表，每一個(gè)經(jīng)過(guò)幾次處理之后，也許姓名變動(dòng)了，也許人少了，或多了，想統(tǒng)計(jì)它的變動(dòng)情況比較困難，我想了許多辦法，最終VLOOKUP函數(shù)幫我解決了這個(gè)問(wèn)題。格式：VLOOKUP(lookup_v