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

1、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:C18Z">=80")60-69 分：=COUNTIF(C4:C18/,>=60")-COUNTIF(C4：C18/">=70

2、")50-59 分:=COUNTIF(C4:C18/">=50")-COUNTIF(C4:C18/">=60")41-49 分：=COUNTIF(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,04:013)總分：=SUM(C4:C18,G4:G

3、18,K4:K18,04:013)平均分：=AVERAGE(B4:D4)3、與考人數(shù)、記分人數(shù)：=COUNT(C4:C18,G4:G18ZK4:K18,04:018)優(yōu)生人數(shù)：=COUNTIF(C4：C18；,>=80")差生人數(shù)：=COUNTIF(C4: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è)

4、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,“合格，“待合格”)二 CHOOSE(IF(A1>=80,1JF(A1>=70,2,IF(A1>=6034),''優(yōu)"，"良合格=”待合格”)1、2、3、4要與優(yōu)、良、合格、待合格相對(duì)應(yīng)合”,IF(ISBLANK(C4)J")二 IF(C4>=80, “優(yōu)”,IF(C4>=70，”良”,IF(C4>=6

5、0,“合格”,IF(ISNUMBER(C4)J 待這兩個(gè)函數(shù)指不錄入成績(jī)時(shí)，單元格為空。 錄入。時(shí)，評(píng)定為待合=LOOKUP(A1,0,60,70,80,"D"/"C","B"；,A") = 1_001<1/1,0,60,70,80,90,不及格”,”及格”,中,良”,'優(yōu)")=IF(E4<60；'D',JF(E4<70,C,/IF(E4<80/"B"/"A")二 if(Al<60,”E“,if(Al<70JD”,i

6、f(Al<80JC”,if(Al<9(VBTA”)=IF(A1<6OJ 不及格”,IF(Al<70,“及格”,IF(A1<8OJ 中”,IF(Al<90,“良“，”優(yōu) ")=IF(Al>=90，“優(yōu)”,IF(Al<90,“ 良”3的1<70,“中”,if(al=<60,“及格，"很差")=IF(AND(C5>=90/C5<=100)/"A",IF(AND(C5>=80/C5<90)；,B"JF(AND(C5>=7 0,C5<80),&quo

7、t;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"zIF(E2>=60；,B","C")=IF(C2>=80/"A"/IF(C2>=60,"B"

8、z"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 中求出出生日期，=DATE(MIDB(E2,7,4),MIDB(E2,11,2),MIDB(E2,13,2)格式為yyyy-m-d,可更改為yyyy-mm-dd的格式。補(bǔ)充說(shuō)明：提取后格式為670520

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

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

11、格，快速完成每個(gè)學(xué)生“年齡” 的計(jì)算。= 亍 T 史 J / 3彩包品 口 2a / 1 7 TM 工 2燈曲1。族C2/ =(YEAR (B2) -YEAR (A2) ) +1F ( (MONTH (B2) -flONTH (A2) ) <0, -1. 0)ABCDEFGHI231967-05-201967To - 202013-08-312013-08-31464541988-11-152013-08-312456一 10、男女混合的表格中，計(jì)算男或女的年齡和(條件求和)=SUMIF(C5:C19,"",E5:E19)11、計(jì)算男的個(gè)數(shù)(計(jì)算區(qū)域內(nèi)符合條件的個(gè)數(shù))

12、 =COUNTIF(C479:C493,"55")COUNTIFS符合多條件的個(gè)數(shù)，如，=COUNTIFS(A2:A5,“>=60，B2:B5,“>=60”)可計(jì)算語(yǔ)文合格,數(shù)學(xué)也合格 (雙科合格)的人數(shù)。n=SUMPRODUCT(C4:C18>=80)*(D4:D18>=80) 是一樣的。但是在Excel 2003中沒(méi)有COUNTIFS函數(shù)，在WPS20007、 excel2007以上版本才有。12、計(jì)算符合“男生、4歲”這兩個(gè)條件的單元格的個(gè)數(shù):=SUMPRODUCT(C479:C493="")*1,(E479:E493=4)

13、*1)用SUMPRODUCT函數(shù)代替COUNTIFS。符合多個(gè)條件的，方法相同。(1)還可以用：=SUM(IF(C88:C102二”男")*(E88:E102=4),l,0)注意：1、輸入完公式后要按Ctrl+Shift+Enter鍵,讓它自動(dòng)加上數(shù)組公 式符號(hào)”“。2、也可以用于雙科合格率的人數(shù)統(tǒng)計(jì)，還可以是符合多 個(gè)條件的單元格人數(shù)統(tǒng)計(jì)。3、文本的參數(shù)加"數(shù)字的參數(shù)不加” 4、年齡不能用文本格式，用常規(guī)，其它的符號(hào)如用文本格式。(2)還可以使用COUNT函數(shù)和IF函數(shù)嵌套計(jì)算：二COUNT(IF(B1:B 3>80)*(Cl:C3>80)*(Dl:D3>

14、;80)*(El:E3>80)*(Fl:F3>80),Al:A3),B語(yǔ)文C數(shù)學(xué)D物理E化學(xué)F英語(yǔ)A學(xué)生姓名，按Ctrl+Shift+Enter 鍵,讓它自動(dòng)加上數(shù)組公式符號(hào)”/大于0且小于30的單元格個(gè)數(shù)/=SUM(IF($V$4:$V$75<30)*($V$4:$V$750),l,0)/uCOUNTlFWdA'yCOUNTlFWdMSjyO11) / =SUMPRODUCT(V4:V75<30)*(V4:V75>0)Q=SUM(V4:V75>0)*(V4:V75<30)13、直接計(jì)算女生平均年齡的三種方法：二 SUMIF(C2:C21,“女

15、”,B2:B21)/COUNTIF(C2:C21J 女”)二 AVERAGEIF(C2:C21J 女”,B2:B21)二 AVERAGE(IF(C2:C21 二”女，B2:B21)1 /性別區(qū)域：1%計(jì)算與 30歲年齡段步晨飛12的兩個(gè)公式也行)=COUNTIFS(C2:C21J 女”,B2:B21J>20)B2:B21J<30”)15、COUNT和COUNTA公式的區(qū)別(都是求錄入內(nèi)容的單元格的個(gè)、 pt數(shù)) 區(qū)域的條件是數(shù)域的條件是文字16、將兩個(gè)單元格Al、Bl的內(nèi)容合并到一個(gè)單元格Cl：Cl 輸入=A1&B1將一個(gè)單元格Al的內(nèi)容如“福建古田杉洋”，分解到兩個(gè)單元格Bl、Cl： B1 輸入=LEFT (Al, 2), Bl 輸入二RIGHT (Al, 4) .k)k_)YY左2個(gè)字符右4個(gè)字符17、平方、立方的計(jì)算：二5A2,二5A3也可以用函數(shù)二POWER (4, 2)計(jì)算4的平方。18、19、計(jì)算教齡:工作時(shí)間二時(shí)間顯示牌：如距放假還有 二DATE(2013,6,29卜TODAYOG,天,不計(jì)月份20、計(jì)算年齡=YEAR($A$1 )-YEAR ( Bl )年齡的單元格要設(shè)置為常規(guī)TODAY ()差生的 日期放置的單元出生的日期單元 格21、