Excel學(xué)生成績統(tǒng)計常用公式

1、Excel學(xué)生成績、各種報表統(tǒng)計常用公式1、分數(shù)段人數(shù)統(tǒng)計90-100分：二COUNTIF(C4:C28/二90”)80-89分：二COUNTIF(C4:C18,”=80“)-COUNTIF(C4:C28,“二90”)70-79分：=COUNTIF(C4：C18/,=70,)-COUNTIF(C4:C18；,=80)60-69分：二COUNTIF(C4:C18,”x60“)-COUNTIF(C4:C28,“二70”)50-59分:=COUNTIF(C4:C18/=50,)-COUNTIF(C4:C18；,=60)41-49分：二COUNTIF(C4:C18,”40”)-COUNTIF(C4:C

2、1&”二50”)40分及以下：=COUNTIF(C4:C18；,=40n)2、最高分：=MAX(C4:C18/G4:G18/K4:K18,04:013)最低分：=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:G18ZK4:K18,O4:O18)優(yōu)生人數(shù)：二COUNTIF(C4:C18,”x80“)差生人數(shù)：=COUNTIF(C4:C18/=60)*(D4:D28二60)雙科優(yōu)秀人數(shù)：二SUMPRODUC

3、T(C4:C28=80)*(D4:D18=80)4、條件求和：=SUMIF(B2：B56；男“，K2:K56)假設(shè)B列存放學(xué)生 的性別，K列存放學(xué)生的分數(shù)，則此函數(shù)返回的結(jié)果表示求該班男生 的成績之和；5、學(xué)生分數(shù)轉(zhuǎn)化為等級評定:=IF(A4=80/優(yōu)”,IF(A4二70,”良“,IF(A4=60,“合格”，”待合格“)二CHOOSE(IF(Al=8(UF(Al=702IF(Al=6034)r優(yōu)T良T合格丁二IF(C4*8(V優(yōu),F(C4=7(V，良,F(C4x6(V合格l,F(ISNUMBER(C4)/待這兩個函數(shù)指不錄入成績時，單元格為空 錄入o時，評立為待合二LOOKUPSbQGCVOK

4、OLCDTCTBTA”)=LOOKUP(Al/0/60/70/80,90/11不及格羅及格T中T良丁優(yōu)) 二IF(E4v6(VD： IF(E4v70C,F(E4v8(VBTA”)二if(Alv60ETf(A2v7(V，DTf(A：lv80rC,f(Alv90rBTA”)=IF(Al60,不及格”,IF(Alv70,”及格JFfAKSO；*中”,IF(Alv90,良優(yōu)“)二IF(A1X9(V ttlFfAKgO；1良 丁肝佃270中恥2二=90/C5=80/C5=70/C580)；,C,/IF(C5=80；,A,/IF(C2=60；,B,/C,)JF(D2=80；,A/IF(D2二60,“BTC

5、“),IF(E2=80/A“,IF(E2=60,”BTC”)待合格冷r 2、3. 4要與優(yōu)、良、合格、待合格相對應(yīng)合JF(ISBLANK(C4)J)二IF(C2=80,“A”,IF(C2=60,”BTC“)&IF(D2=80,“A”,IF(D2=6(VBTC”) &IF(E2=80；,A,JF(E2=60；,B,/,C)6、28位身份證號碼轉(zhuǎn)換成出生日期的函數(shù)公式： 如果E2中是身份 證， 在F2中求出出生日期,=DATE(MIDB(E2/7,4)/MIDB(E2/ll/2)/MIDB(E 2,13,2)格式為yyyy-m-d,可更改為yyyy-mm-dd的格式。補充說明：提取

6、后格式為670520或。(2)如果身份證都是18位的，也可能直接用二MID (Al, 7, 8)將多個文本字符竺二二巴提取后的出生日期是連續(xù)的，可以中間加或=CONCATENATE (LEFT (Al, 2),年”,MID (Al, 3, 2),月”,RIGHT (Al, 2),日)格式為67年05月20日，年月日換成 就成為67-05-20(4)也可以從18位身份證號碼中直接提取=CONCATENATE(MID(A1?9,2), MID(A1,H,2)格式為7、根據(jù)身份證號碼自動錄入男女：=IF(MOD(IF(LEN(e2)=18/MID(e2,17zl),MID(e2,15,1) ,2)=

7、0,女“，”男“)8、根據(jù)身份證號碼轉(zhuǎn)化在年齡：(適合于不計月份的)=YEAR(TODAY()-VALUE(MID(A1,7,4)第2利|利用身份證號碼生成年齡：=IF(LEN(D4)=18,YEAR (TODAY()-MID(D4,7,4),YEAR (TODAY()-MID(D4,7,2)-1900)9、學(xué)生年齡的公式應(yīng)用:說明：生成年齡的單元格C2要設(shè)置成： 格式一單元格-數(shù)字,選擇“數(shù)值”,并將“小數(shù)位數(shù)”設(shè)為 6 其中IF語名是判斷學(xué)生的出生月份是否超過8月，如 超過則要用年份之差再減1。 第一個學(xué)生年齡計算出來后， 將鼠標(biāo)移到此單元格 的右下角，光標(biāo)變成“ + ”后，向下拖動復(fù)制單

8、元格，快速完成每個學(xué)生“年齡” 的計算。ABCDEFGH121 二60”,B2:B5,“二60”)可計算語文合格， 數(shù)學(xué)也合格 (雙科合格)的人數(shù)。n=SUMPRODUCT(C4：C18=80)*(D4:D18=80)是一樣的。 但是在Excel 2003中沒有COUNTIFS函數(shù)， 在WPS20007、exce!2007以上版本才有。12、計算符合“男生、4歲”這兩個條件的單元格的個數(shù):=SUMPRODUCT(C479:C493=,男”)*1,(E479:E493二4)*2)用SUMPRODUCT函數(shù)代替COUNTIFSo符合多個條件的，方法相同。(1)還可以用：二SUM(IF(C8&

9、;C102=男“)*(E88：102=4),1,0)注意：2、輸入完公式后要按Ctrl+Shift+Enter鍵,讓它自動加上數(shù)組公 式符號“。2、也可以用于雙科合格率的人數(shù)統(tǒng)計，還可以是符合多 個條件的單元格人數(shù)統(tǒng)計。3、文本的參數(shù)加“蔦數(shù)字的參數(shù)不加”“。4、年齡不能用文本格式，用常規(guī)，其它的符號如“丿”用文本格式。(2)還可以使用COUNT函數(shù)和IF函數(shù)嵌套計算:=COUNT(IF(B1:B380)*(Cl:C380)*(Dl:D380)*(El:E380)*(Fl:F380),Al:A3),B語文C數(shù)學(xué)D物理E化學(xué)F英語A學(xué)生姓名，按Ctrl+Shift+Enter鍵,讓它自動加上數(shù)組

10、公式符號”“。/大于0且小于30的單元格個數(shù)/=SUM(IF($V$4:$V$75=0”)-COUNTIF(V4:V75,”=30“) /=SUMPRODUCT(V4:V750)冷=SUM(V4:V750)*(V4:V7520“,B2:B21,” Cl： Bl輸入二LEFT (Al, 2), C2輸入二RIGHT (Al, 4)“k_ _)k_)Y右4個字符17.平方.立方的計算：二5八乙也可以用函數(shù)二POWER (4, 2)計算4的平方。時間顯示牌：如距放假還有=DATE(2013,6,29)-TODAY()&,天年齡的單元格要設(shè)置為常規(guī)TODAY ()差生的 日期放宜的單元 出生的日期單元 格21.生日提醒函數(shù):二IF(AND(MONTH仃ODAY()二MONTH(E3),DAY(TODAY()二DAY(E3)生R左2個字符20、計算教齡:工作時間二計算年齡=YEAR($A$1 )-YEAR ( Bl )不計月份T非生日fl)22、電子表格的列中自動添