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:c18，=80 )60-69分鐘：=countif(c4:c18，=60)-countif(c4:c18，=70 )50-59分鐘：=countif(c4:c18，=50)-countif(c4:c18，=60 )41-49分鐘：=countif(c4:c18， 40)-countif(c4:c18

2、，=50 )40分以下：=countif(c4:c18，=40 )2、最高點(diǎn)：=max(c4:c18、g4:g18、k4:k18、o4:o13 )最低點(diǎn)：=最小(c 4: c 18，g4:g18，k4:k18，o4:o13 )合計(jì)點(diǎn)：=sum(c4:c18、g4:g18、k4:k18、o4:o13 )平均分：=average(b4:d4 )3、考生人數(shù)、評(píng)分人數(shù)：=count(c4:c18、g4:g18、k4:k18、o4:o18 )優(yōu)生人數(shù)：=countif(c4:c18，=80 )劣等學(xué)生人數(shù)：=countif(c4:c18，=40 )雙科錄取者：=sumproduct (c 4: c 1

3、8=60 ) * (d 4: d 18=60 ) )雙科優(yōu)秀人數(shù)：=sumproduct (c 4: c 18=80 ) * (d 4: d 18=80 ) )4、條件合計(jì)：=sumif(b2:b56，“男”，k2:k56) 假定在b列中加上學(xué)生的性別，在k列中加上學(xué)生的分?jǐn)?shù)，該函數(shù)返回該班男子成績(jī)之和的結(jié)果5、學(xué)生分?jǐn)?shù)轉(zhuǎn)換為等級(jí)評(píng)價(jià)=if(a4=80、優(yōu)、if(a4=70、良、if(a4=60、合格、等待合格 )一、二、三、四要應(yīng)對(duì)優(yōu)、良、合格、等待合格=選擇(if (a1=80，1，if (a1=70，2，if (a1=60，3，4 ) )。這兩個(gè)函數(shù)表示沒(méi)有輸入成績(jī)時(shí)，單元格為空。 輸入

4、0，就會(huì)被評(píng)定合格=if(c4=80、優(yōu)、if(c4=70、良、if(c4=60、合格)、r=lookup(a1， 0，60，70，80 ，d ， c ， b ， a )=lookup(a1， 0，60，70，80，90 ， 不合格，合格，中，良，優(yōu) )=if(e460， d ，if(e470， c ，if(e480， b ， a ) )=if(a160， e ，if(a170， d ，if(a180， c ，if(a190， b ， a ) )=if(a160，不合格，if(a170，合格，if(a1780，中，if(a1790，良，優(yōu))=if(a1=90，“優(yōu)”，if(a190，“良”，“

5、if(a170，“中”)，if(a1=60，“合格”，“差”)=if (與(c5=90，c5=100 )、 a 、if (與(c5=80，c590 )、 b 、if (與(c5=100，c580 )、 c 、if(c570， d ，e ) ) )多個(gè)帳戶(hù)的等級(jí)評(píng)估：=concatenate(if(c2=80，a，if(c2=60，b，c) )、if(d2=80，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，1

6、8位的身份證號(hào)碼被轉(zhuǎn)換為出生年月日的函數(shù)式：在e2中有身份證時(shí)，在f2中求出生年月日，=date (midb (e 2，7，4 )、midb (e 2，11，2 )、midb (e 2，13，2 ) )的形式為yyyy-m-d，yyyy-y補(bǔ)充說(shuō)明：請(qǐng)檢查括號(hào)是否成對(duì)剩下的是第18名的身份證，從第9名中抽出第8名第十五名，從第七名提取第六名(1)=if(len(a1)=15，mid (a1，7，6 )，mid (a1，7，8 ) )請(qǐng)檢查括號(hào)是否成對(duì)斷定是幾個(gè)人的身份證提取后的格式為670520或19670520。把多個(gè)文本字符跳成一個(gè)(2)如果身份證都是第18位，則也可以直接使用=mid(a

7、1、7、8 )(3)提取后的出生年月日連續(xù)，中途可以加“-”或“.”=concatenate (left (a1，2 )、“年”、mid (a1，3，2 )、“月”、right (a1，2 )、“日”換算為67年05月20日，年月日換算為“-”時(shí)為67-05-20(4)也可以從18位身份證號(hào)碼中直接提取=concatenate (mid (a1，9，2 )、.、 mid (a1，11，2 ) )形式為67.057、根據(jù)身份證號(hào)碼自動(dòng)輸入男女：=if (=if (len (e2)=18，mid (e 2，17，1 )，mid (e 2，15，1 ) )，2)=0，女，男)8、根據(jù)身份證號(hào)碼轉(zhuǎn)換為

8、年齡：(適合不數(shù)月)=year (today () ) -值(mid (a 1，7，4 ) )二是利用身份證號(hào)碼生成年齡。=if(len(d4)=18，年(today () )-mid (d4，7，4 )，年(today () )-mid (d4，7，2 )-1900 )9、學(xué)生年齡的正式應(yīng)用：=(年(b2) -年(a2) ) if (month (b2)-month (a2) ) 0，- 1，0 )出生年月日區(qū)域設(shè)定超時(shí)時(shí)間的范圍也可以絕對(duì)參照，只將一個(gè)單元作為超時(shí)時(shí)間。 學(xué)生的年齡計(jì)算中，截止時(shí)間為2013-08-31，大人的年齡計(jì)算截止時(shí)間為2013-12-31。說(shuō)明：生成年齡的單元c2

9、，設(shè)定為格式單元數(shù)字，選擇數(shù)值，將小數(shù)點(diǎn)后位數(shù)設(shè)定為0。 在此，if語(yǔ)名判斷學(xué)生的出生年月日是否超過(guò)8月，超過(guò)的話請(qǐng)用年差再減1。 計(jì)算完第一個(gè)學(xué)生的年齡后，將鼠標(biāo)移動(dòng)到此單元格的右下角，光標(biāo)變?yōu)椤啊焙?，向下拖?dòng)復(fù)制單元格，快速完成每個(gè)學(xué)生的“年齡”計(jì)算。10、男女混合表，計(jì)算男性或女性的年齡和(條件合計(jì))=sumif(c5:c19，男，e5:e19 )年齡區(qū)域性別要計(jì)算的區(qū)域11、計(jì)算男性人數(shù)(計(jì)算區(qū)域內(nèi)滿(mǎn)足條件的數(shù)量)=countif(c479:c493，男人)countifs滿(mǎn)足多個(gè)條件的個(gè)數(shù)，例如=countifs(a2:a5，“=60”，b2:b5，“=60”)可以計(jì)算語(yǔ)文合格、數(shù)學(xué)

10、也合格(雙科合格)的人數(shù)。 和=sumproduct (c 4: c 18=80 ) * (d 43360 d 18=80 ) )相同。 但是，excel 2003中沒(méi)有countifs函數(shù)的只有wps20007、excel2007以上。12 .計(jì)算滿(mǎn)足“男子，4歲”兩個(gè)條件的單元格數(shù)=sumproduct(c479:c493=男 ) *1、(e 4793360 e 493=4) *1)使用sumproduct函數(shù)代替countifs。 滿(mǎn)足多個(gè)條件的方法相同。(1)還可以使用=sum(if(c88:c102=男 ) *(e883360e102=4)，1，0 ) ): 1.輸入公式后，按ctr

11、l shift enter鍵，會(huì)自動(dòng)添加公式符號(hào)“”。 既可以用于兩科合格率的人數(shù)統(tǒng)計(jì)，也可以用于滿(mǎn)足多個(gè)條件的單元格的人數(shù)統(tǒng)計(jì)。 3、在文本參數(shù)上加“”，不在數(shù)字參數(shù)上加“”。 4、年齡不能是文本形式，一般而言，其他符號(hào)是文本形式，如“”。(還可以使用計(jì)數(shù)函數(shù)和if函數(shù)進(jìn)行嵌套計(jì)算：=count (if (b 1: b 380 ) * (c 1: c 380 ) * (d 1: d 380 ) * (e 1: e 380 ) * (f 1: f 380 ) )，a1:a3 ) )，b語(yǔ)言c數(shù)學(xué)d物理e化學(xué)f英語(yǔ)a學(xué)生名，按ctrl shift enter鍵后，會(huì)自動(dòng)添加數(shù)組式符號(hào)“”。大于0

12、小于30的單元格數(shù)=sum (if ) ($ v $ 4: $ v $ 7530 ) * ($ v $ 4: $ v $ 750，1，0 ) )=countif(v4:v75，=0)-countif(v4:v75，=30 )=sumproduct (v 43360 v 7530 ) * (v 4: v 750 ) )=sum(v4:v750)*(v4:v7530 ) )13 .直接計(jì)算女子平均年齡的三種方法：=sumif(c2:c21，女， b2:b21)/countif(c2:c21，女)=averageif(c2:c21，女， b2:b21 )=平均(if (c 2: c 21=女人，b2

13、:b21 ) )年齡區(qū)域性別區(qū)域計(jì)算14、20歲到30歲的女性人數(shù)(也可以是12個(gè)公式)。=countifs(c2:c21，女， b 23360 b 21， 20， b2:b21， 30 )地區(qū)的條件是文本地區(qū)的條件是數(shù)字15、count和counta公式的差異(全部求輸入內(nèi)容的單元格數(shù))16 .將兩個(gè)小區(qū)a1、b1的內(nèi)容組合成一個(gè)小區(qū)c1c1輸入=a1b1將一個(gè)單元a1的內(nèi)容分解為“福建古田杉洋”那樣的兩個(gè)單元b1、c1: b1輸入=left (a 1，2，2 )、c1輸入=right (a 1，4，4 )左兩個(gè)字右四個(gè)字十七、平方、立方的計(jì)算：=52、=53也可以用函數(shù)=power (4，2 )計(jì)算4的平方。今年年月日或today ()或者，設(shè)定截止時(shí)間。18、時(shí)刻表示卡：到休假為止=date (2013，6，29 )-today () 日19、計(jì)算教齡：=datedif(a1、b1、“y”)參加工作時(shí)間代表年變更為“m”時(shí)計(jì)算月，變更為“d”時(shí)計(jì)算日工作時(shí)間=出生日期單元格放置在不正確日期的單元格20、年齡=year($a$1 )-year(b1)不計(jì)月份年齡單元格設(shè)置為正常21、生日注意函數(shù)：=if (and (month (today () ) )=month (e3 )，day(today()=day(e3 ) )，生日，非生日)生日顯示，生日以外不顯示出生日期單