資源描述：
《Excel學(xué)生成績(jī)統(tǒng)計(jì)常用公式復(fù)習(xí)過(guò)程.doc》由會(huì)員上傳分享，免費(fèi)在線(xiàn)閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫(kù)。
1、精品好文檔，推薦學(xué)習(xí)交流Excel學(xué)生成績(jī)、各種報(bào)表統(tǒng)計(jì)常用公式1、分?jǐn)?shù)段人數(shù)統(tǒng)計(jì)90-100分：=COUNTIF(C4:C18,">=90")80-89分：=COUNTIF(C4:C18,">=80")-COUNTIF(C4:C18,">=90")70-79分：=COUNTIF(C4:C18,">=70")-COUNTIF(C4:C18,">=80")60-69分：=COUNTIF(C4:C18,">=60")-COUNTIF(C4:C18,">=70")50-59分：=COUNTIF(C4:C18,">=50")-COUNTIF(C4:C18,">=60")41-49分：=CO
2、UNTIF(C4:C18,">40")-COUNTIF(C4:C18,">=50")40分及以下：=COUNTIF(C4:C18,"<=40")2、最高分：=MAX(C4:C18,G4:G18,K4:K18,O4:O13)最低分：=MIN(C4:C18,G4:G18,K4:K18,O4:O13)總分：=SUM(C4:C18,G4:G18,K4:K18,O4:O13)平均分：=AVERAGE(B4:D4)3、與考人數(shù)、記分人數(shù)：=COUNT(C4:C18,G4:G18,K4:K18,O4:O18)優(yōu)生人數(shù)：=COUNTIF(C4:C18,">=80")差生人數(shù)：=COUNTIF(C4
3、:C18,"<=40")雙科合格人數(shù)：=SUMPRODUCT((C4:C18>=60)*(D4:D18>=60))雙科優(yōu)秀人數(shù)：=SUMPRODUCT((C4:C18>=80)*(D4:D18>=80))4、條件求和：=SUMIF(B2:B56,"男"，K2:K56)——假設(shè)B列存放學(xué)生的性別，K列存放學(xué)生的分?jǐn)?shù)，則此函數(shù)返回的結(jié)果表示求該班男生的成績(jī)之和；5、學(xué)生分?jǐn)?shù)轉(zhuǎn)化為等級(jí)評(píng)定：=IF(A4>=80,"優(yōu)",IF(A4>=70,"良",IF(A4>=60,"合格","待合格")))1、2、3、4要與優(yōu)、良、合格、待合格相對(duì)應(yīng)=CHOOSE(IF(A1>=80,1,IF(A1>
4、=70,2,IF(A1>=60,3,4))),"優(yōu)","良","合格","待合格")僅供學(xué)習(xí)與交流，如有侵權(quán)請(qǐng)聯(lián)系網(wǎng)站刪除謝謝8精品好文檔，推薦學(xué)習(xí)交流這兩個(gè)函數(shù)指不錄入成績(jī)時(shí)，單元格為空。錄入0時(shí)，評(píng)定為待合=IF(C4>=80,"優(yōu)",IF(C4>=70,"良",IF(C4>=60,"合格",IF(ISNUMBER(C4),"待合",IF(ISBLANK(C4),"")))))=LOOKUP(A1,{0,60,70,80},{"D","C","B","A"})=LOOKUP(A1,{0,60,70,80,90},{"不及格","及格","中","良","優(yōu)"})=IF(E4<6
5、0,"D",IF(E4<70,"C",IF(E4<80,"B","A")))=if(A1<60,"E",if(A1<70,"D",if(A1<80,"C",if(A1<90,"B","A"))))=IF(A1<60,"不及格",IF(A1<70,"及格",IF(A1<80,"中",IF(A1<90,"良","優(yōu)"))))=IF(A1>=90,"優(yōu)",IF(A1<90,"良","if(a1<70,"中",if(a1=<60,"及格","很差")))=IF(AND(C5>=90,C5<=100),"A",IF(AND(C5>=80,C5<90),"B",IF(AND(C5>=70,C5
6、<80),"C",IF(C5<70,"D",E)))).多科目等級(jí)評(píng)定：=CONCATENATE(IF(C2>=80,"A",IF(C2>=60,"B","C")),IF(D2>=80,"A",IF(D2>=60,"B","C")),IF(E2>=80,"A",IF(E2>=60,"B","C")))=IF(C2>=80,"A",IF(C2>=60,"B","C"))&IF(D2>=80,"A",IF(D2>=60,"B","C"))&IF(E2>=80,"A",IF(E2>=60,"B","C"))6、18位身份證號(hào)碼轉(zhuǎn)換成出生日期的函數(shù)公式：如果E2中是身份證,在F2中求出出生
7、日期，=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2))格式為yyyy-m-d，可更改為yyyy-mm-dd的格式。補(bǔ)充說(shuō)明：僅供學(xué)習(xí)與交流，如有侵權(quán)請(qǐng)聯(lián)系網(wǎng)站刪除謝謝8精品好文檔，推薦學(xué)習(xí)交流注意檢查括號(hào)是否成對(duì)出現(xiàn)其余的為18位的身證，就從第9位開(kāi)始提取8位是15位的，就從第7位開(kāi)始，提取6位（1）=IF(LEN(A1)=15，MID（A1，7，6），MID（A1，7，8）)注意檢查括號(hào)是否成對(duì)出現(xiàn)判斷是幾位的身份證提取
