口學(xué)習(xí)，能夠運(yùn)用Excel進(jìn)行學(xué)生成績(jī)的統(tǒng)計(jì)分析。口統(tǒng)計(jì)各分?jǐn)?shù)段的人數(shù)及比例口圖示成績(jī)分布(餅圖繪制)口學(xué)生總成績(jī)及名次口成績(jī)等級(jí)及積點(diǎn)計(jì)算口試卷質(zhì)量評(píng)價(jià)口示例1：用IF函數(shù)計(jì)算成績(jī)等級(jí)口>=90—A80-89—B70-79—C60-69—D<60—E口示例2：用COUNTIF函數(shù)統(tǒng)計(jì)各分?jǐn)?shù)段的人數(shù)及比例79COUNTIF80分?jǐn)?shù)段人數(shù)81=COUNTIF "<60")<=596782二COUNTIF(FhFTT,"<70")-COUNTIF(F2:F77J"<60")60-6915IS83二COUNTIF(F2:F77J"<80")-COUNTIF(F2:F77,m70-7925咒84二COUNTIF(F2:F77J"<90")-COUNTIF(F2:F77,"<80")80-89212r85二COUNTIF(FhFTT,">=90")>=90g1]8688COUNTIF89分?jǐn)?shù)段人數(shù)90二COUNTIF($G$2:$G$TT,它)<=596791二COUNTIF($G$2:$G$TT,0)60-6915IS92=COUNTIF($G$2:$G$77J?)70-792593二COUNTIF($G$2:$G$TT,T)80-89212r94=COUNTIF($G$2:$G$77,"A")>=90g11口示例3：用FREQUENCY函數(shù)統(tǒng)計(jì)人數(shù)口FREQUENCY(data_array,bins_array)口Data_array為一數(shù)組或?qū)σ唤M數(shù)值的引用口Bins_array為間隔的數(shù)組引用，該間隔用于對(duì)data_array中的數(shù)值進(jìn)行分組。口對(duì)于返回結(jié)果為數(shù)組的公式，必須以數(shù)組公式的形式輸入?？跀?shù)組的使用口選擇區(qū)域(H81:H85)口輸入公式“=FREQUENCY(C71:C77,G81:G85)”口使用組合鍵“Ctrl+Shift+Enter

1LJ79FREQUENCY80分?jǐn)?shù)段分?jǐn)?shù)分段點(diǎn)人數(shù)比例81<=595967.89%{=FEEQS260-69691519.74%{=FEEQS370-79792532.8935{=FEEQ8480-89892127.63%{=FREQ85>=90100911.84%{=FEEQ口示例4：使用數(shù)據(jù)分析統(tǒng)計(jì)口工具一加載宏一分析工具庫(kù)口工具一數(shù)據(jù)分析一直方圖口成績(jī)分段統(tǒng)計(jì)(P59-62)口各分?jǐn)?shù)段人數(shù)占總?cè)藬?shù)的比例口餅圖口注意數(shù)據(jù)源，切忌勿先算比例口圖的修飾和格式化口示例5：學(xué)生成績(jī)名次計(jì)算口總評(píng)成績(jī)計(jì)算Chapter0502.xls口絕對(duì)引用=C2*$H$2+D2*$I$2口混合飲用=C2*H$2+D2*I$2口區(qū)域命名=C2*平時(shí)比例+D2*期末比例口名次計(jì)算口=RANK(E2,$E$2:$E$77)ABCDEFGH1序號(hào)姓名平時(shí)成績(jī)考試成績(jī)總評(píng)成績(jī)名次平時(shí)比127)203017張曉雯969092737)210035董琦939494247)215078錢俊媚8687871357)220039倪曉雯867578143二RANK(78t79二ROUND(C5*H$2+D5*I$2,0)on口示例6：用IF函數(shù)計(jì)算成績(jī)等級(jí)口=IF(ISNUMBER(C2),IF(C2>=85,"A",IF(C2>=75,"B",IF(C2>=60,"C","D"))),IF(ISBLANK(C2),"缺考"，”輸入有誤"))口ISBLANK值為空白單元格口ISNUMBER值為數(shù)字口ISTEXT值為文本口INDEX口INDEX函數(shù)口INDEX(reference,row_num,column_num,area_num)口返回引用中指定單元格或單元格區(qū)域的引用口Reference為對(duì)一個(gè)或多個(gè)單元格區(qū)域的引用口Row_num為引用中某行的行序號(hào)，函數(shù)從該行返回一個(gè)引用口Column_num為引用中某列的列序號(hào)，函數(shù)從該列返回一個(gè)引用口MATCH()口返回在指定方式下與指定數(shù)值匹配的數(shù)組中元素的相應(yīng)位置口MATCH(lookup_valuelookup_array,match_type)口Lookup_value為需要在數(shù)據(jù)表中查找的數(shù)值，它可以是數(shù)值(或數(shù)字、文本或邏輯值)、對(duì)數(shù)字、文本或邏輯值的單元格引用?？贚ookup_array是可能包含所要查找的數(shù)值的連續(xù)單元格區(qū)域口Match_type為數(shù)字-1、0或1，它說(shuō)明Excel如何在lookup_array中查找lookup_value?？谌绻鹠atch_type為1,函數(shù)MATCH查找小于或等于lookup_value的最大數(shù)值口如果match_type為0,函數(shù)MATCH查找等于lookup_value的第一個(gè)數(shù)值?？谌绻鹠atch_type為-1，函數(shù)MATCH查找大于或等于lookup_value的最小數(shù)值?？谑纠?：用INDEX和MATCH函數(shù)計(jì)算成績(jī)等級(jí)口=INDEX(H$2:H$11,MATCH(1,(C2>=F$2:F$11)*(C2v=G$2:G$11),0))ABCDEFG]1序號(hào)姓名考試成績(jī)等級(jí)開(kāi)始分?jǐn)?shù)結(jié)束分?jǐn)?shù)等12^0302014周婷85A-95100A3^0302015陸晴77B909414^0302016楊逸雯91A8589A5^0302018鄒雯婕72B-8084E6^0302021洪致穎87A-7579]7^0302024包銘倫65C+7074E8^0303087戴連萍70B-6569C91張婉50D6064(10^0303089葛艷華95A+5559C11^0303090陳依娜88A-+054]7475blNDEX(H$2:H$11,MATCH(1,(Cl1>=F$2:F$11)*(Cl1<=G$2:G$11ABCDEF1序號(hào)姓名考試成績(jī)等級(jí)開(kāi)始分?jǐn)?shù)纟27)302014周婷853.59537)302015陸晴772.59047)302016楊逸雯9148557)302018鄒雯婕7228067)302021洪致穎873.57577)302024包銘倫651.57087)303087戴連萍7026597)303088張婉50060107)303089葛艷華95455117)303090