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

1、精選優(yōu)質(zhì)文檔-傾情為你奉上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 能夠運(yùn)算在給定的分?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ù)值的引用，

2、用來(lái)運(yùn)算頻率。如果 data_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ù)。講明在選定相鄰單元格區(qū)域（該區(qū)域用于顯示返回的分布結(jié)果）后，函數(shù) FREQUENCY 應(yīng)以數(shù)組公式的形式輸入。 返回的數(shù)組中的元素個(gè)數(shù)比 bins_array（數(shù)組）中的元素個(gè)數(shù)多 1。返回的數(shù)組中所多出來(lái)的元素表示超出最高

3、間隔的數(shù)值個(gè)數(shù)。例如，如果要運(yùn)算輸入到三個(gè)單元格中的三個(gè)數(shù)值區(qū)間（間隔），請(qǐng)一定在四個(gè)單元格中輸入 FREQUENCY 函數(shù)運(yùn)算的結(jié)果。多出來(lái)的單元格將返回 data_array 中大于第三個(gè)間隔值的數(shù)值個(gè)數(shù)。 函數(shù) FREQUENCY 將忽略空白單元格和文本。 關(guān)于返回結(jié)果為數(shù)組的公式，必須以數(shù)組公式的形式輸入。5、不及格紅色顯示 “格式”菜單中的“條件格式”命令6、排名顯示 =RANK(number,ref,order) 其中number為需要找到排位的數(shù)字。 Ref為包含一組數(shù)字的數(shù)組或引用。Ref 中的非數(shù)值型參數(shù)將被忽略。 Order為一數(shù)字，指明排位的方式。 ·如果 or

4、der 為 0 或省略，Microsoft Excel 將 ref 當(dāng)作按降序排列的數(shù)據(jù)清單進(jìn)行排位。 ·如果 order 不為零，Microsoft Excel 將 ref 當(dāng)作按升序排列的數(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，&qu

5、ot;>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（DATA，“?”）9、返回包含單詞"GOOD"(不分大小寫(xiě))內(nèi)容的單元格數(shù)量=COUNTIF（DATA，“GOOD”）10、返回在文本中任何位置包含單詞"GOOD"字符內(nèi)容的單元格數(shù)量=COUNTIF（DATA，“*GOOD*”）11、返

6、回包含以單詞"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)+STDEV(DATA)*3)15、返回包含值為或-3的單元格數(shù)量=COUNTIF（DATA，3）+COUNIF（DATA，-3）16、返回包

7、含值;邏輯值為T(mén)RUE的單元格數(shù)量=COUNTIF（DATA，TRUE）8、數(shù)據(jù)透視表EXCEL中統(tǒng)計(jì)學(xué)生成績(jī)常用的函數(shù)一、前言 關(guān)于教師而言，經(jīng)常需要用Excel進(jìn)行學(xué)生成績(jī)統(tǒng)計(jì)，會(huì)被一些常見(jiàn)咨詢題難住。碰到的難題要緊有：如何統(tǒng)計(jì)不同分?jǐn)?shù)段人數(shù)、如何在保持學(xué)號(hào)不變前提下進(jìn)行排名、如何將百分制轉(zhuǎn)換成不同分?jǐn)?shù)段與如何用紅色顯示不及格的分?jǐn)?shù)等，本文著重對(duì)這些咨詢題的解決方法與技巧加以分析和討論。本文假設(shè)讀者已對(duì)Excel的差不多操作差不多有一定基礎(chǔ)，差不多把握如何進(jìn)行求和、求平均和如何使用自動(dòng)填充柄進(jìn)行復(fù)制公式等操作，本文對(duì)這些一樣性操作不做詳細(xì)介紹，僅對(duì)一些難度較大的操作技巧進(jìn)行討論。二、Exc

8、el統(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é)果圖那個(gè)地點(diǎn)，假設(shè)學(xué)號(hào)、姓名、成績(jī)等列及行15都差不多事先輸好，需要讓Excel統(tǒng)計(jì)其他的有關(guān)數(shù)據(jù)結(jié)果。這時(shí)，成績(jī)統(tǒng)計(jì)中要緊難解決的咨詢題及它們?cè)趫D中的位置如下：咨詢題1：如何統(tǒng)計(jì)不同分?jǐn)?shù)段的學(xué)生人數(shù)?(圖中A16E16)咨詢題2：如何在保持學(xué)號(hào)順序不變的前提下進(jìn)行學(xué)生成績(jī)名次排定?(圖中F2F13)咨詢題3：如何將百分制轉(zhuǎn)換成不同的等級(jí)分?(圖中“等級(jí)1”與“等級(jí)2”列)咨詢題4：如何使不及格的分?jǐn)?shù)以紅色顯示?(圖中紅色顯示部分，即第12行)三、解決統(tǒng)計(jì)學(xué)生成績(jī)時(shí)的四個(gè)難題的方法下

9、面，針對(duì)上面提出的四個(gè)難題分不討論解決的方法與技巧。1、統(tǒng)計(jì)不同分?jǐn)?shù)段的學(xué)生人數(shù)統(tǒng)計(jì)不同分?jǐn)?shù)段的學(xué)生人數(shù)是專門(mén)常見(jiàn)的需求，其所需結(jié)果如圖1中A16E16所示。那個(gè)地點(diǎn)，假設(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)。關(guān)于小于60分的人數(shù)只要用一個(gè)COUNTIF( )函數(shù)，如在E16單元格中輸入公式：=COUNTIF($C$2:$C$13,"<60")。關(guān)于其他在兩個(gè)分?jǐn)?shù)之

10、間的分?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"作相應(yīng)的修改，就能夠得到正確的結(jié)果。2.保持學(xué)號(hào)順序不變的前提下進(jìn)行成績(jī)排名學(xué)生成績(jī)排定在學(xué)生成績(jī)統(tǒng)計(jì)中經(jīng)常用到。專門(mén)要

11、強(qiáng)調(diào)的是，那個(gè)地點(diǎn)所談的方法不是一樣的排序，因?yàn)槟菢訒?huì)使學(xué)生的學(xué)號(hào)順序發(fā)生變化。那個(gè)地點(diǎn)所需要的是在保持學(xué)號(hào)順序不變的情形下進(jìn)行學(xué)生成績(jī)名次排定的功能，其所需結(jié)果如圖1中F2F13所示。要進(jìn)行保持學(xué)號(hào)順序不變的情形下進(jìn)行學(xué)生成績(jī)名次的排定，最好使用RANK(X，Y，Z)函數(shù)。其中有三個(gè)參數(shù)，第一個(gè)參數(shù)X為某個(gè)學(xué)生的成績(jī)所在單元格;第二個(gè)參數(shù)Y為整個(gè)班級(jí)成績(jī)所在的區(qū)域;第三個(gè)參數(shù)Z是可選的，表示統(tǒng)計(jì)方式，若省寫(xiě)或?qū)?，則成績(jī)高的名次靠前，一樣都使用這種方式，如果寫(xiě)1，則成績(jī)高的名次靠后，這種情形一樣較少用。為了在保持學(xué)號(hào)順序不變的前提下進(jìn)行學(xué)生成績(jī)名次排定，能夠在 F2單元格中輸入公式：=RAN

12、K(C2,$C$2:$C$13,0)，然后，利用自動(dòng)填充柄將其復(fù)制到下方的幾個(gè)單元格。注意，那個(gè)地點(diǎn)$C$2:$C $13用的是絕對(duì)地址，是為了保證公式在復(fù)制時(shí)此處不變，因?yàn)樽鳛榈诙€(gè)參數(shù)，那個(gè)地點(diǎn)差不多上指整個(gè)班級(jí)成績(jī)所在的區(qū)域，那個(gè)區(qū)域是相同的。3、將百分制轉(zhuǎn)換成不同的等級(jí)分將百分制轉(zhuǎn)換成不同的等級(jí)分有多種不同的劃分方法，其所需結(jié)果如圖1中“等級(jí)1”與“等級(jí)2”列所示。那個(gè)地點(diǎn)，“等級(jí)1”列是將百分制的分?jǐn)?shù)轉(zhuǎn)換成A (90100)、B(8089)、C(7079)、D(6069)與E(低于60)五個(gè)等級(jí);“等級(jí)2”列是將百分制的分?jǐn)?shù)轉(zhuǎn)換成優(yōu) (90100)、良(7589)、中(6074)與不

13、及格(低于60)四個(gè)等級(jí)。具體使用哪種等級(jí)劃分方法可按照實(shí)際情形自己確定。在百分制轉(zhuǎn)換成不同的等級(jí)分時(shí)，一樣使用IF(X，Y，Z)函數(shù)。其中有三個(gè)參數(shù)，第一個(gè)參數(shù)X為條件，不能加引號(hào);第二個(gè)參數(shù)為條件成立時(shí)的結(jié)果，如果是顯示某個(gè)值，則要加引號(hào);第三個(gè)參數(shù)為條件不成立時(shí)的結(jié)果，如果是顯示某個(gè)值，同樣要加引號(hào)。該函數(shù)能夠嵌套，即在第二個(gè)或第三個(gè)參數(shù)處能夠再寫(xiě)一個(gè) IF函數(shù)。為了得到“等級(jí)1”列所要的等級(jí)結(jié)果，能夠在D2單元格中輸入公式：=IF(C2>= 90,"A",IF(C2>=80,"B",IF(C2>=70,"C"

14、,IF(C2>=60,"D","E")，然后，利用自動(dòng)填充柄將其復(fù)制到下方的幾個(gè)單元格。為了得到“等級(jí)2”列所要的等級(jí)結(jié)果，能夠在E2單元格中輸入公式：=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ù)以紅色顯示需要使用“格式

15、”菜單中的“條件格式”命令。該命令會(huì)彈出一個(gè)對(duì)話框，其中要求確認(rèn)條件與相應(yīng)的格式。關(guān)于“成績(jī)”列，可先選中C2：C13，然后使用“格式”菜單中的“條件格式”命令，在彈出的對(duì)話框中，左邊使用默認(rèn)的“單元格數(shù)值”，中間選“小于”，右邊填寫(xiě)60，然后單擊右邊的“格式”按鈕，從中選擇紅色，最后單擊兩次“確定”按鈕。關(guān)于“等級(jí)1”列，可先選中D2：D13，然后使用“格式”菜單中的“條件格式”命令，在彈出的對(duì)話框中，左邊使用默認(rèn)的“單元格數(shù)值”，中間選“等于”，右邊填寫(xiě)E，然后單擊右邊的“格式”按鈕，從中選擇紅色，最后單擊兩次“確定”按鈕?！暗燃?jí)2”列類似。關(guān)于其他的一些統(tǒng)計(jì)運(yùn)算要求，如如何樣運(yùn)算各分?jǐn)?shù)段

16、的百分比、如何運(yùn)算機(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ù)順理成章。我們用它來(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")”就能夠了。其含義確實(shí)是統(tǒng)計(jì)C2:C47單元格區(qū)域中滿足大于等于90的單元格

17、數(shù)目。因此，要統(tǒng)計(jì)80分至89分這一段的學(xué)生數(shù)，那么就需要輸入公式“=COUNTIF(C2:C47，">=80")-COUNTIF(C2:C47，">=90")”。專門(mén)明顯，大于等于80分的人數(shù)減去大于等于90分的人數(shù)正是我們想要的人數(shù)。其他分?jǐn)?shù)段能夠依此類推。二、利用FREQUENCY函數(shù)這是一個(gè)專門(mén)用于統(tǒng)計(jì)單元格區(qū)域中數(shù)據(jù)的頻率分布的函數(shù)，用它來(lái)統(tǒng)計(jì)分?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單元格

18、，在編輯欄輸入公式“=FREQUENCY($D$2:$D$47，$L$8:$L$12)”，然后按下“Ctrl+Shift+Enter”組合鍵確認(rèn)，即可在公式的兩端添加數(shù)組公式的標(biāo)志“”，同時(shí)能夠看到各分?jǐn)?shù)段的人數(shù)差不多統(tǒng)計(jì)完成了。需要注意的是公式輸入完成后必須按“Ctrl+Shift+Enter”組合鍵確認(rèn)以產(chǎn)生數(shù)組公式，而且數(shù)組公式的標(biāo)志“”也不能夠手工輸入。三、利用DCOUNT函數(shù)DCOUNT函數(shù)平常我們不大用，它能夠從滿足指定條件的數(shù)據(jù)庫(kù)記錄的字段中運(yùn)算數(shù)值單元格數(shù)目。這話聽(tīng)起來(lái)看起來(lái)不太容易明白，但用它統(tǒng)計(jì)分?jǐn)?shù)段是專門(mén)不錯(cuò)的，例如統(tǒng)計(jì)E列的英語(yǔ)成績(jī)。我們需要事先做一個(gè)設(shè)置。如圖4所示，

19、以統(tǒng)計(jì)80分至89分的人數(shù)為例：我們需要在Q16和R16單元格分?jǐn)?shù)輸入“英語(yǔ)”(必須與原始成績(jī)表的列標(biāo)題一致)，然后在Q17和R17單元格分不輸入“>=80”，“<90”。這四個(gè)單元格是作為指定條件使用的。在N16單元格輸入公式“=DCOUNT($E$1:$E$47，"英語(yǔ)"，Q16:R17)”，完成后按下回車鍵就能夠得到正確結(jié)果了。要注意的是公式中第一個(gè)參數(shù)為成績(jī)所在單元格區(qū)域，必須包括列標(biāo)題。依此類推，統(tǒng)計(jì)其他各分?jǐn)?shù)段人數(shù)，只需要改變一下公式中第三個(gè)參數(shù)，即條件所在的單元格區(qū)域就行了。四、利用SUM函數(shù)SUM函數(shù)是用來(lái)求和的，這地球人都明白。然而，它也能夠用

20、來(lái)進(jìn)行多條件計(jì)數(shù)，因此也能夠用來(lái)統(tǒng)計(jì)分?jǐn)?shù)段。因此，還得借助于數(shù)組公式才行。以F列的理論成績(jī)?yōu)槔?。如圖5所示，在N24單元格輸入公式“=SUM($F$2:$F$47>=90)*($F$2:$F$47<=100)”，然后按下“Ctrl+Shift+Enter”組合鍵產(chǎn)生數(shù)組公式，就能夠得到大于等于90分且小于等于100分的學(xué)生數(shù)了。同樣，數(shù)組公式“=SUM($F$2:$F$47>=70)*($F$2:$F$47<80)”能夠得到7079分這一段的人數(shù)。其他各分?jǐn)?shù)段能夠依此類推。五、利用SUMPRODUCT函數(shù)SUMPRODUCT函數(shù)能夠返回相應(yīng)的數(shù)組或區(qū)域的乘積之和，因此正

21、好能夠用它來(lái)統(tǒng)計(jì)分?jǐn)?shù)段。以G列的實(shí)踐課為例。如圖6所示。要統(tǒng)計(jì)90分以上的人數(shù)，那么能夠在N30單元格輸入公式“=SUMPRODUCT($G$2:$G$47>=90)*($G$2:$G$47<=100)”，按下回車鍵后就能夠得到G2:G47單元格區(qū)域大于等于90分小于等于100分的人數(shù)了。專門(mén)明顯，要統(tǒng)計(jì)8089分的人數(shù)，那能夠使用公式“=SUMPRODUCT($G$2:$G$47>=80)*($G$2:$G$47<90)”，其他依此類推就行了。好了，五種方法統(tǒng)計(jì)分?jǐn)?shù)段人數(shù)，相信一定有一種方法適合你。以上各種方法使用環(huán)境均為Excel 2007，其他版本能夠參照使用。用

22、Excel函數(shù)快速統(tǒng)計(jì)學(xué)生期中考試成績(jī)期中考試終止，各學(xué)科考試成績(jī)迅速匯總到班主任那個(gè)地點(diǎn)。這時(shí)候，班主任最忙的，確實(shí)是要迅速統(tǒng)計(jì)各學(xué)科成績(jī)的各項(xiàng)指標(biāo)，例如平均分、最高分、優(yōu)秀率、及格率以及各學(xué)科分?jǐn)?shù)的頻率統(tǒng)計(jì)等等。盡管現(xiàn)在普遍都在使用Excel進(jìn)行這項(xiàng)工作，只是，要想使這項(xiàng)工作能夠高效準(zhǔn)確地完成，那還得請(qǐng)Excel函數(shù)來(lái)幫忙才行。匯總到班主任那個(gè)地點(diǎn)的成績(jī)表如圖1所示，各科成績(jī)分布在C2:C95單元格區(qū)域。我們先在K2:Q15單元格區(qū)域建立如圖2所示表格用以存放各項(xiàng)統(tǒng)計(jì)結(jié)果。先點(diǎn)擊M3單元格，輸入如下公式：=AVERAGE(C2:C95)，回車后即可得到語(yǔ)文平均分。點(diǎn)擊M4單元格，輸入公式：

23、=MAX(C$2:C$95)，回車即可得到語(yǔ)文成績(jī)中的最高分。優(yōu)秀率是運(yùn)算分?jǐn)?shù)高于或等于85分的學(xué)生的比率。點(diǎn)擊M5單元格，輸入公式：=COUNTIF(C$2:C$95,">=85")/COUNT(C$2:C$95)，回車所得即為語(yǔ)文學(xué)科的優(yōu)秀率。點(diǎn)擊M6單元格，輸入公式：=COUNTIF(C$2:C$95,">=60")/COUNT(C$2:C$95)，回車所得即為及格率。再選中M5:Q6單元格區(qū)域，點(diǎn)擊菜單命令“格式單元格”，打開(kāi)“單元格格式”對(duì)話框。點(diǎn)擊“數(shù)字”選項(xiàng)卡，在左側(cè)“分類”列表中選擇“百分比”，如圖3所示，確定后可將M5:Q6

24、單元格區(qū)域的數(shù)據(jù)轉(zhuǎn)變成百分比形式。至于各科分?jǐn)?shù)段人數(shù)的統(tǒng)計(jì)，那得先選中M8:M15單元格，在編輯欄中輸入公式：=FREQUENCY(C$2:C$95,$K$8:$K$15)。然后按下“Ctrl+Shift+Enter”快捷鍵，能夠看到在公式的最外層加上了一對(duì)大括號(hào)。現(xiàn)在，我們就差不多得到了語(yǔ)文學(xué)科各分?jǐn)?shù)段人數(shù)了。在K列中的那些數(shù)字，確實(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è)置它們的字體顏色為白色就能夠了。Excel 20

25、07巧妙統(tǒng)計(jì)混合編班分?jǐn)?shù)段期末考試之后，自然確實(shí)是成績(jī)的統(tǒng)計(jì)和分析了。為了使成績(jī)的錄入和分析更為公平，我們學(xué)校差不多上由專人統(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ù)，專門(mén)苦惱。事實(shí)上，如果我們是用Excel 2007進(jìn)行相應(yīng)的工作的話，那么就能夠利用新增的COUNTIFS函數(shù)來(lái)完成這項(xiàng)任務(wù)，專門(mén)方便。 一、1班語(yǔ)文學(xué)科分?jǐn)?shù)段統(tǒng)計(jì)圖1 學(xué)生成績(jī)表圖1所示為一次考試的學(xué)生成績(jī)表。在不對(duì)成績(jī)進(jìn)行排序等操作的前

26、提下，我們將完成圖2所示分?jǐn)?shù)段統(tǒng)計(jì)表。圖2 Excel 2007繪制表格具體過(guò)程如下：依舊先講講那個(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, 則是指具體施加的條件是什么。例如上例的公式的含義則是統(tǒng)計(jì)A2:A108單元格區(qū)域中等于L3單元格（即1班）且C2:C108單元格區(qū)域中等于100分的單元格個(gè)數(shù)。COUNTIFS所支持的施加條

27、件個(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ì)，專門(mén)明顯，其公式應(yīng)該是“=COUNTIFS($A$2:$A$108,$L3,$C$2:$C$108,"

28、<50")”。二、其它學(xué)科及班級(jí)分?jǐn)?shù)段統(tǒng)計(jì)選中N3:T3單元格區(qū)域，向下拖動(dòng)填充句柄至T7，則能夠?qū)?班的分?jǐn)?shù)段表格迅速填充完成。然而，現(xiàn)在每一行所填充的數(shù)據(jù)，差不多上語(yǔ)文學(xué)科相應(yīng)的分?jǐn)?shù)段人數(shù)。我們還需要將公式中的“$C”（語(yǔ)文成績(jī)所在列）改成各學(xué)科相應(yīng)的列號(hào)。只是，那個(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所示。然后點(diǎn)擊“全部替換”按鈕，那么就能夠得到數(shù)學(xué)學(xué)科的各分?jǐn)?shù)段了。圖3

29、 Excel 2007中執(zhí)行替換其它幾個(gè)學(xué)科照此辦理，將公式中的“$C”分不再替換為“$E”、“$F”、“$G”，專門(mén)快就能夠得到1班各學(xué)科的分?jǐn)?shù)段統(tǒng)計(jì)。圖4 Excel 2007完成表格提醒一下：L列中的班級(jí)為輔助列，是為了我們?cè)诠街性O(shè)置班級(jí)方便而加的，不能刪除。從表格美觀的角度動(dòng)身，能夠選中整個(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ù)能夠?qū)崿F(xiàn)這種統(tǒng)計(jì)工作，筆者以圖1所示的成績(jī)表為例，給出多種統(tǒng)計(jì)方法。文章末尾提供.xls文件供大伙兒下載

30、參考。 文章導(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)秀、良好”等），我們也能夠用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ù)成效專門(mén)不錯(cuò)。我們用它統(tǒng)計(jì)“數(shù)學(xué)”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。 方法三：用FREQUENCY函數(shù)統(tǒng)計(jì)這是一個(gè)專門(mén)用于統(tǒng)計(jì)某個(gè)區(qū)域中數(shù)據(jù)的頻率分布函數(shù)，我們用它來(lái)統(tǒng)計(jì)“英語(yǔ)”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。 方法四：用SUM函

31、數(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單元格，輸入公式：=COUNTIF(C3:C62,"<60")和=COUNTIF(C3:C62,">=90")，即可統(tǒng)計(jì)出“語(yǔ)文”成績(jī)“低于60分”和“大于等于90”的學(xué)生人數(shù)。 分不選中C64、C65和C66單元格，輸入公式：=COUNTIF(C3:C62,"&

32、gt;=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ì)出其它學(xué)科各分?jǐn)?shù)

33、段的學(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。  &#

34、160;   方法二：用DCOUNT函數(shù)統(tǒng)計(jì)那個(gè)函數(shù)不太常用，但用來(lái)統(tǒng)計(jì)分?jǐn)?shù)段學(xué)生數(shù)成效專門(mén)不錯(cuò)。我們用它統(tǒng)計(jì)“數(shù)學(xué)”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。 分不選中M63至N72單元格區(qū)域（不一定非得不那個(gè)區(qū)域），輸入學(xué)科名稱（與統(tǒng)計(jì)學(xué)科名稱一致，如“數(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:D6

35、2,"數(shù)學(xué)",M69:N70)、=DCOUNT($D$2:$D$62,"數(shù)學(xué)",M71:N72)，確認(rèn)即可。 注意：將上述公式中的“DCOUNT”函數(shù)換成“DCOUNTA”函數(shù)，同樣能夠?qū)崿F(xiàn)各分?jǐn)?shù)段學(xué)生人數(shù)的統(tǒng)計(jì)。方法三：用FREQUENCY函數(shù)統(tǒng)計(jì)這是一個(gè)專門(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+Sh

36、ift+Enter”組合鍵進(jìn)行確認(rèn)，即可一次性統(tǒng)計(jì)出“英語(yǔ)”學(xué)科各分?jǐn)?shù)段的學(xué)生人數(shù)。 注意：實(shí)際上此處輸入的是一個(gè)數(shù)組公式，數(shù)組公式輸入完成后，不能按“Enter”鍵進(jìn)行確認(rèn)，而是要按“Ctrl+Shift+Enter”組合鍵進(jìn)行確認(rèn)。確認(rèn)完成后，在公式兩端顯現(xiàn)一個(gè)數(shù)組公式的標(biāo)志“”（該標(biāo)志不能用鍵盤(pán)直截了當(dāng)輸入）。數(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ì)

37、“政治”學(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 再次選中F63單元格，用“填充柄”將上述公式復(fù)制到F64至F67單元格中，統(tǒng)計(jì)出其它各分?jǐn)?shù)段的學(xué)生人數(shù)。 注意：用此法統(tǒng)計(jì)時(shí)，能夠不引用單元格，而直截了當(dāng)采納分?jǐn)?shù)值。例如，在F64單元格中輸入公式：=SUM($F$3:$F$62>=60)*($F$3:$F$62<70)，也能夠統(tǒng)計(jì)出成績(jī)?cè)?0-69分之間的學(xué)生人數(shù)。 注意：為了表格整體的美觀，我們將M至P列隱藏起來(lái)：同時(shí)選中M至P列，

38、右擊鼠標(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ì)咨詢題，用公式或分類匯總能夠滿足我們的需要，但關(guān)于復(fù)雜條件的數(shù)據(jù)統(tǒng)計(jì)，許多初學(xué)用戶覺(jué)得專門(mén)棘手，事實(shí)上用好自帶的“條件求和”功能，這些咨詢題就能夠迎刃而解了。 圖1為某單位職工數(shù)據(jù)表，我們以統(tǒng)計(jì)二車間的高級(jí)工程師中男性的人數(shù)為例，介紹一下如何利用“條件求和”功能實(shí)現(xiàn)多條件限制下的數(shù)據(jù)統(tǒng)計(jì)過(guò)程

39、。 圖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”界面中選擇“求和列”為“性不”，將限制的條件“崗位=二車間、職稱=高級(jí)工程師、性不=男”逐條選擇并添加到條件列表中，單擊“下一步”按鈕（如圖2）。 圖2 指定條件數(shù)據(jù)3. 在“條件求和向?qū)?4步驟之3”界面中選擇統(tǒng)計(jì)結(jié)果的顯示方式為“復(fù)制公式及條件”，單擊“下一步”按鈕。 4. 最后要選擇目標(biāo)數(shù)據(jù)存放的單元格，本例中依次選擇

40、C18、D18、E18、F18單元格，并在每次選擇后單擊“下一步”按鈕，直到最后單擊“完成”按鈕?，F(xiàn)在在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ì)“二車間”每月計(jì)發(fā)的工資總額，能夠用公式“=SUMIF (E2E15,"二車間",F2F15)”來(lái)實(shí)現(xiàn)，因此也能夠使用分類匯總命令來(lái)實(shí)現(xiàn)。但關(guān)于復(fù)雜條件的數(shù)據(jù)統(tǒng)計(jì)用公式就顯得有些難度了，專門(mén)是關(guān)于不熟悉函數(shù)的讀者，而用“條件求和”功能則能夠?qū)ｉT(mén)方便地實(shí)現(xiàn)我們的統(tǒng)計(jì)需求。 Cico提示：初次使用“條件求和”功能必須先安裝。單擊“工具”菜單中的“加載宏

41、”，在“加載宏”對(duì)話框中勾選“條件求和向?qū)А睆?fù)選框，單擊“確定”按鈕，現(xiàn)在系統(tǒng)excel筆記之兩表內(nèi)容比較VLOOKUP函數(shù)在平常的工作中，同一個(gè)班，同一級(jí)，會(huì)有許多的表，每一個(gè)通過(guò)幾次處理之后，也許姓名變動(dòng)了，也許人少了，或多了，想統(tǒng)計(jì)它的變動(dòng)情形比較困難，我想了許多方法，最終VLOOKUP函數(shù)幫我解決了那個(gè)咨詢題。格式：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)Lookup_value    為需要在第一列中查找的數(shù)值。Lookup_value 能夠?yàn)閿?shù)值、引用或文本字符串。Table_array    為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表。能夠使用對(duì)區(qū)域或區(qū)域名稱的引用，例如數(shù)據(jù)庫(kù)或列表。如果 range_lookup 為 TRUE，則 table_array 的第一列中的數(shù)值必須按升序排列：、-2、-1、0、1、2、-Z、FALSE、TRU