《Excel常用函數(shù)功能、用法及實例剖析》

1、書名：excel常用函數(shù)功能、用法及實例剖析作者：來源：天極網(wǎng) isbn：頁數(shù)：0開本：出版時間：定價：未經(jīng)授權(quán)，不得轉(zhuǎn)載！閱讀評論投票內(nèi)容簡介：我們在使用excel制作表格整理數(shù)據(jù)的時候，常常要用到它的函數(shù)功能來自動統(tǒng)計處理表格中的數(shù)據(jù)。本專題整理了excel中使用頻率最高的函數(shù)的功能、使用方法，以及這些函數(shù)在實際應(yīng)用中的實例剖析，并配有詳細的介紹和圖示.excel常用函數(shù)實例剖析 excel制作獎金計算表(1) excel制作獎金計算表(2) 用excel制作一份萬年歷(1) 用excel制作一份萬年歷(2) 用excel制作自動評分計算表(1) 用excel制作自動評分計算表(2) ex

2、cel自動統(tǒng)計學(xué)生成績(1) excel自動統(tǒng)計學(xué)生成績(2) 用excel統(tǒng)計各分?jǐn)?shù)段學(xué)生數(shù)(1) 用excel統(tǒng)計各分?jǐn)?shù)段學(xué)生數(shù)(2) excel自動生成員工簡歷表(1) excel自動生成員工簡歷表(2) excel常用函數(shù)功能及用法介紹 1、abs函數(shù) 2、and函數(shù) 3、average函數(shù) 4、column 函數(shù) 5、concatenate函數(shù) 6、countif函數(shù) 7、date函數(shù) 8、datedif函數(shù) 9、day函數(shù) 10、dcount函數(shù) 11、frequency函數(shù) 12、if函數(shù) 13、index函數(shù) 14、int函數(shù) 15、iserror函數(shù) 16、left函數(shù) 17

3、、len函數(shù) 18、match函數(shù) 19、max函數(shù) 20、mid函數(shù) 21、min函數(shù) 22、mod函數(shù) 23、month函數(shù) 24、now函數(shù) 25、or函數(shù) 26、rank函數(shù) 27、right函數(shù) 28、subtotal函數(shù) 29、sum函數(shù) 30、sumif函數(shù) 31、text函數(shù) 32、today函數(shù) 33、value函數(shù) 34、vlookup函數(shù) 35、weekday函數(shù) excel常用函數(shù)實例剖析excel制作獎金計算表(1) 某公司規(guī)定：一個月獎金基數(shù)為300元，病假1天扣15元，事假一天扣30元，曠工一天扣60元，扣完為止。使用這個獎金計算表時，只要將員工的出勤情況記錄在表

4、中，該員工的獎金將自動計算出來，兼有考勤和計算獎金兩種功能。自動統(tǒng)計表做好以后還可以保存成模板，以便以后使用。本文所涉及到的excel函數(shù)有： 1、countif(range,criteria) 2、month(serial_number) 3、today()注意：圖中符號的含義是：b表示病假，s表示事假，g表示曠工，q表示出勤，j表示法定休息日。 1、啟動excel2003，按照圖1所示的樣式，制作一張二維表格，并填入相關(guān)文本。 2、將a1至bm1單元格合并成一個單元格后，輸入表格標(biāo)題（如“曉風(fēng)公司考勤及獎金表”），并設(shè)置好字體、字號等。 3、將c2和d2單元格合并后，輸入公式：=month

5、(today()&月，用于顯示出當(dāng)前的月份。 注意：在上述單元格中直接輸入月份數(shù)值也是可行的。 4、選中bm4單元格，輸入公式：=if(300-countif(c4:bl4,b)*7.5-countif(c4:bl4,s)*15-countif(c4:bl4,g)*300,300-countif(c4:bl4,b)*7.5-countif(c4:bl4,s)*15-countif(c4:bl4,g)*30,0)，確認，計算出第一位員工的當(dāng)月獎金額。 注意：上述公式的含義是：用“countif(c4:bl4,b)、countif(c4:bl4,s)、countif(c4:bl4,g)”分別用于統(tǒng)

6、計每位員工的病假、事假、曠工的次數(shù)，再分別乘以“7.5、15、30”（每“半天”為一次）計算出所要扣去的獎勵額，再用基數(shù)“300”減去上述扣去的金額。如果（if）差大于“0”，則顯示差（即員工的獎金額），否則顯示“0”（扣完為止）。 5、再次選中bm4單元格，用“填充柄”將上述公式復(fù)制到下面的單元格中，用于計算其它員工的當(dāng)月獎勵額。 注意：具體復(fù)制的數(shù)量，請根據(jù)員工的實際數(shù)目確定。excel常用函數(shù)實例剖析excel制作獎金計算表(2) 6、清空表格中所填寫的數(shù)據(jù)，執(zhí)行“文件保存”命令，打開“另存為”對話框（如圖2），按“保存類型”右邊的下拉按鈕，選中“模板（*.xlt）”選項，在文件名方框中

7、輸入一名稱（如“獎金表.xlt”），單擊“保存”按鈕，將制作好的表格文檔保存為模板。 7、以后，每個月第一天，啟動excel，執(zhí)行“文件新建”命令，展開“新建工作簿”任務(wù)窗格（如圖3）。點擊其中的“本機上的模板”選項，打開“模板”對話框（如圖4），選中“獎金表”模板文件，單擊“確定”按鈕即可新建一個空的“獎金表1”工作簿文檔。選中c2單元格（即保存月份的單元格），將鼠標(biāo)移到編輯欄中，然后按一下f9功能鍵，將公式（=month(today()&月）轉(zhuǎn)換為具體的值（如“12月”），最后將文檔保存一下。 8、以后就可以用這個計算表逐日對員工進行考勤，到了月底就可以自動計算出獎金數(shù)據(jù)了。excel常用

8、函數(shù)實例剖析用excel制作一份萬年歷(1) 今天我們介紹用excel制作萬年歷的方法。這個萬年歷可以顯示當(dāng)月的月歷，還可以隨意查閱任何日期所屬的月歷，非常方便。如果你愿意，還可以讓它在特殊的日子里顯示不同的提醒文字，一起來試試吧！文章末尾提供.xls文件供大家下載參考。本文所涉及到的函數(shù)有：1、and (logical1,logical2, .)2、date (year,month,day)3、day (serial_number)4、if (logical,value_if_true,value_if_false)5、int (number)6、month (serial_number)7

9、、now ()8、or (logical1,logical2, .) 1、啟動excel2003，新建一個工作表，取名保存（如萬年歷.xls），并在相應(yīng)的單元格中，輸入如圖1所示的文本。2、同時選中b1、c1、d1單元格，按“格式”工具欄上的“合并及居中”按鈕，將其合并成一個單元格，并輸入公式：=today（）。選中b1（合并后的）單元格，執(zhí)行“格式單元格”命令，打開“單元格格式”對話框（如圖2），在“數(shù)字”標(biāo)簽中的“分類”下面選中“日期”選項，再在右側(cè)“類型”下面選中“二一年三月十四日”選項，“確定”退出，將日期設(shè)置成中文形式。注意：today（）函數(shù)用于提取當(dāng)前系統(tǒng)日期，請將系統(tǒng)日期一定要

10、調(diào)整準(zhǔn)確喲。3、選中f1單元格，輸入公式：=if(weekday(b1,2)=7,日,weekday(b1,2)；選中h1單元格，輸入公式：=now（）。選中f1單元格，打開“單元格格式”對話框，在“數(shù)字”標(biāo)簽中的“分類”下面選中“特殊”選項，再在右側(cè)“類型”下面選中“中文小寫數(shù)字”選項，“確定”退出，將“星期數(shù)”設(shè)置成中文小寫形式；選中h1單元格，打開“單元格格式”對話框，在“數(shù)字”標(biāo)簽中的“分類”下面選中“時間”選項，再在右側(cè)“類型”下面選中一款時間格式，“確定”退出。注意：上述前面一個公式的含義是：如果（if）當(dāng)前日期（b1）是星期“7”（weekday(b1,2)=7），則在f1單元格

11、中顯示“日”，否則，直接顯示出星期的數(shù)值（weekday(b1,2)）。上述第二個函數(shù)（now（）用于提取當(dāng)前系統(tǒng)日期和時間，也請將系統(tǒng)日期和時間調(diào)整準(zhǔn)確。4、在i1、i2單元格分別輸入1900、1901，然后同時選中i1、i2單元格，用“填充柄”向下拖拉至i151單元格，輸入19002050年份序列。同樣的方法，在j1至j12單元格中輸入112月份序列。5、選中d13單元格，執(zhí)行“數(shù)據(jù)有效性”命令，打開“數(shù)據(jù)有效性”對話框（如圖3），按“允許”右側(cè)的下拉按鈕，選中“序列”選項，在“來源”下面的方框輸入：=$i$1:$i$151，“確定”退出。同樣的操作，將f15單元格數(shù)據(jù)有效性設(shè)置為“=$j

12、$1:$j$12”序列。注意：經(jīng)過這樣的設(shè)置以后，當(dāng)我們選中d15（或f15）單元格時，在單元格右側(cè)出現(xiàn)一個下拉按鈕，按此下拉按鈕，即可選擇年份（或月份）數(shù)值，快速輸入需要查詢的年、月值。excel常用函數(shù)實例剖析用excel制作一份萬年歷(2) 6、選中a2單元格（不一定非得是a2喲），輸入公式：=if(f13=2,if(or(d13/400=int(d13/400),and(d13/4=int(d13/4),d13/100int(d13/100),29,28),if(or(f13=4,f13=6,f13=9,f13=11),30,31)，用于獲取查詢“月份”所對應(yīng)的天數(shù)（28、29、30、

13、31）。注意：上述函數(shù)的含義是：如果查詢“月份”為“2月”（f13=2）時，并且“年份”數(shù)能被400整除d13/400=int(d13/400)，或者（or）“年份”能被4整除，但不能被100整除and(d13/4=int(d13/4),d13/100int(d13/100)，則該月為29天（也就是我們通常所說的“閏年”），否則為28天。如果“月份”不是2月，但是“4、6、9、11”月，則該月為30天。其他月份天數(shù)為31天。7、選中b2單元格，輸入公式：=if(weekday(date($d$13,$f$13,1),2)=b3,1,0)。再次選中b2單元格，用“填充柄”將上述公式復(fù)制到c2h2

14、單元格中。注意：上述b2公式的含義是：如果“查詢年月”的第1天是星期“7”（weekday(date)($d$13,$f$13,1),2)=b3）時，在該單元格顯示“1”，反之顯示“0”），為“查詢年月”獲取一個對照值，為下面制作月歷做準(zhǔn)備。上述c2h2單元條中公式的含義與b2相似。在用拖拉法復(fù)制公式時，公式“絕對引用”的單元格（加了“$”號的，如“$d$13”等）不會發(fā)生改變，而“相對引用”的單元格（沒有加“$”號的，如“b3”等），則會智能化地發(fā)生變化，例如在e2單元格中，“b3”變成了“e3”，整個公式成為：=if(weekday(date($d$13,$f$13,1),2)=e3,1,

15、0)。8、選中b6單元格，輸入公式：=if(b2=1,1,0)。選中b7單元格，輸入公式：=h6+1。用“填充柄”將b7單元格中的公式復(fù)制到b8、b9單元格中。分別選中b10、b11單元格，輸入公式：=if(h9=a2,0,h9+1)和=if(h10=a2,0,if(h100,h10+1,0)。選中c6單元格，輸入公式：=if(b60,b6+1,if(c2=1,1,0)。用“填充柄”將c6單元格中的公式復(fù)制到d6h6單元格中。 選中c7單元格，輸入公式：=b7+1。用“填充柄”將c7單元格中的公式復(fù)制到c8、c9單元格中。同時選中c7c9單元格，用“填充柄”將其中的公式復(fù)制到d7h9單元格中。

16、選中c10單元格，輸入公式：=if(b11=$a$2,0,if(b110,b11+1,if(c6=1,1,0)。用“填充柄”將c10單元格中的公式復(fù)制到d10h10單元格和c11單元格中。至此，整個萬年歷（其實沒有萬年，只有從19002050的151年）制作完成。下面，我們一起來將其裝飾一下。9、選中相應(yīng)的單元格，利用工具欄上的相應(yīng)按鈕，設(shè)置好字體、字號、字符顏色等。選中相應(yīng)的單元格，打開“單元格格式”對話框，在“對齊”標(biāo)簽下，設(shè)置好單元格中文本的對齊方式（通常情況下，垂直對齊可以一次性設(shè)置為“居中”，水平“對齊”根據(jù)具體情況設(shè)置）。同時選中i列和j列，右擊鼠標(biāo)，選“隱藏”選項，將相應(yīng)的列隱藏

17、起來，使得界面更加友好。用同樣的方法，將第2和第3行也隱藏起來。10、選中b5h11單元格區(qū)域，打開“單元格格式”對話框，進入“邊框”標(biāo)簽，選擇好“顏色、樣式”，并“預(yù)置”好邊框范圍，然后“確定”退出，為月歷加上邊框。11、執(zhí)行“工具選項”命令，打開“選項”對話框（如圖4），在“視圖”標(biāo)簽下（通常是默認標(biāo)簽），清除“零值”和“網(wǎng)格線”復(fù)選框中的“”號，“確定”退出，讓“零值”和“網(wǎng)格線”不顯示出來。12、將b14h14和b15h15單元格分別合并成一個單元格，并在b14和b15單元格中輸入公式：=if(and(month(d1)=1,day(d1)=1),新的新氣象！加油呀！,if(and(m

18、onth(d1)=3,day(d1)=8),向女同胞們致敬！,if(and(month(d1)=5,day(d1)=1),勞動最光榮,if(and(month(d1)=5,day(d1)=4),青年是祖國的棟梁,if(and(month(d1)=6,day(d1)=1),原天下所有的兒童永遠快樂,0)和=if(and(month(d1)=7,day(d1)=1),黨的恩情永不忘,if(and(month(d1)=8,day(d1)=1),提高警惕，保衛(wèi)祖國！,if(and(month(d1)=9,day(d1)=10),老師，您辛苦了！,if(and(month(d1)=10,day(d1)=

19、1),祝我們偉大的祖國繁榮富強,0)。設(shè)置好b14和b15單元格的字體、字號、字符顏色。注意：上述公式的含義是：如果當(dāng)前日期逢到相關(guān)的節(jié)日（如“元旦”等），則在b14或b15單元格顯示出相應(yīng)的祝福語言（如“新的新氣象！加油呀！”，參見圖5）。由于if函數(shù)只能嵌套7層，而節(jié)日數(shù)量超過7個（我們這里給出了9個），因此，我們用兩個單元格來顯示。 13、執(zhí)行“格式工作表背景”命令，打開“工作表背景”對話框（如圖6），選擇一張合適的圖片后，按“插入”按鈕，將其襯于工作表文字下面。14、在按住“ctrl”鍵的同時，單擊d13和f13單元格，同時選中兩個單元格，開“單元格格式”對話框，切換“保護”標(biāo)簽（如圖

20、7），清除“鎖定”前面復(fù)選框中的“”號，“確定”退出。15、執(zhí)行“工具保護保護工作表”命令，打開“保護工作表”對話框（如圖8），兩次輸入密碼后，確定退出。注意：經(jīng)過這樣的設(shè)置后，整個工作表中除了d13和f13單元格中的內(nèi)容可以改變外，其它單元格中的內(nèi)容均不能改變，保證了萬年歷的使用可靠性。excel常用函數(shù)實例剖析用excel制作自動評分計算表(1) excel自動評分計算表功能：參加比賽的選手為20人，評委9人，去掉1個最高分和1個最低分后，求出平均分，然后根據(jù)平均分的高低排定選手的名次。 本實例所涉及到的函數(shù)有： sum(number1,number2) max(number1,numbe

21、r2) min(number1,number2) rank(number,ref,order) 一、評委評分表的制作 1、啟動excel2003，新建一空白工作簿。 2、在sheet1工作表中，仿照如圖1所示的樣式，制作一份空白表格。 3、執(zhí)行“文件保存”命令（或按“ctrl+s”組合鍵），打開“另存為”對話框（如圖2）。 4、單擊工具條上“工具”按鈕，在隨后出現(xiàn)的下拉列表（參見圖2）中，選擇“常規(guī)選項”，打開“保存選項”對話框（如圖3），設(shè)置好“打開權(quán)限密碼”后，確定返回。注意：密碼需要重新確認輸入一次。此處只需要設(shè)置“打開權(quán)限密碼”，如果設(shè)置了“修改權(quán)限密碼”，則評委在保存評分時，必須提供

22、密碼，反而造成不須要的麻煩。 5、然后取名（如1.xls）保存。 6、再執(zhí)行“文件另存為”命令，再次打開“另存為”對話框，然后仿照上面的操作重新設(shè)置一個密碼后，另取一個名稱（如2.xls）保存一下。 7、重復(fù)第6步的操作，按照評委數(shù)目，制作好多份工作表（此處為9份）。excel常用函數(shù)實例剖析用excel制作自動評分計算表(2) 二、匯總表的制作 1、新建一工作簿，仿照如圖4所示的樣式，制作一張空白表格。 2、分別選中b3至j3單元格，依次輸入公式：=1.xlssheet1!b3、=2.xlssheet1!b3=3.xlssheet1!b3，用于調(diào)用各評委給第一位選手的評分。 注意：請將評委評

23、分表和匯總表保存在同一文件夾內(nèi)。 3、選中k3單元格，輸入公式：=(sum(b3:j3)-max(b3:j3)-min(b3:j3)/7，用于計算選手的最后平均得分。 4、選中l(wèi)3單元格，輸入公式：=rank(k3,$k$3:$k$22)，用于確定選手的名次。 5、同時選中b3至l3單元格區(qū)域，用“填充柄”將上將上述公式復(fù)制到下面的單元格區(qū)域，完成其它選手的成績統(tǒng)計和名次的排定。 6、取名（如hz.xls）保存該工作簿。 注意：在保存匯總表的時候，最好設(shè)置“打開權(quán)限密碼”和“修改權(quán)限密碼”。三、電子評分表的使用 1、將上述工作簿文件放在局域網(wǎng)上某臺電腦的一個共享文件夾中，供各們評委調(diào)用。 注意

24、：當(dāng)我們移動整個工作簿所在的文件夾時，系統(tǒng)會自動調(diào)整公式相應(yīng)的路徑，不影響表格的正常使用。 2、比賽開始前，將工作簿名稱和對應(yīng)的打開權(quán)限密碼分別告知不同的評委，然后通過局域網(wǎng)，讓每位評委打開各自相應(yīng)的工作簿（如1.xls、2.xls）文檔。 注意：評委在打開文檔中，系統(tǒng)會彈出一個如圖5所示的對話框，輸入“打開權(quán)限密碼”，確定即可。 2、某位選手比賽完成后，評位將其成績輸入到相應(yīng)的單元格中，并要求評委執(zhí)行一下保存操作。 注意：每次要求評委評分成執(zhí)行一次保存操作，其目的是為了防止出現(xiàn)意外情況而造成數(shù)據(jù)丟失。 3、整個比賽結(jié)束后，主持人只要打開“hz.xls”工作簿，即可公布比賽結(jié)果了（如圖6）。

25、注意：主持人在打開“hz.xls”工作簿時，系統(tǒng)會彈出如圖7所示的對話框，請單擊其中的“更新”按鈕。 excel常用函數(shù)實例剖析excel自動統(tǒng)計學(xué)生成績(1) 這個自動統(tǒng)計表可以自動統(tǒng)計最高分、最低分、總分、平均分、名次等數(shù)據(jù)信息，還可以根據(jù)自定條件以不同的顏色顯示分?jǐn)?shù)。自動統(tǒng)計表做好以后還可以保存成模板，以便以后使用。文章末尾提供.xls文件供大家下載參考。每到學(xué)期結(jié)束時，教師的一項重要工作就是要統(tǒng)計學(xué)生的成績，在電腦逐步普及的今天，我們就把這項繁雜的工作交給excel去自動完成吧。 涉及函數(shù)：average、countif 、if、iserror、max、min、rank、sum 1、啟

26、動excel，同時選中a1至k1單元格，按“格式”工具條上的“合并及居中”按鈕，將其合并成一個單元格，然后輸入統(tǒng)計表的標(biāo)題（參見圖1）。 2、根據(jù)統(tǒng)計表的格式，將有關(guān)列標(biāo)題及相關(guān)內(nèi)容輸入到相應(yīng)的單元格中（參見圖1）。 3、選中j3單元格，輸入公式：=sum(c3:i3)，用于計算第一位學(xué)生的總分。 4、選中k3單元格，輸入公式：=rank(j3,$j$3:$j$62)，計算出第一位學(xué)生總分成績的名次（此處，假定共有60位學(xué)生）。 注意：如果將上述公式修改為：=countif($j$3:$j$62,=&j3)，同樣可以計算出第一位學(xué)生的名次。5、同時選中j3和k3單元格，將鼠標(biāo)移至k3單元格右下

27、角的成“細十字”狀時（通常稱這種狀態(tài)為“填充柄”狀態(tài)），按住左鍵向下拖拉至k62單元格，完成其他學(xué)生的總分及名次的統(tǒng)計處理工作。 6、分別選中c63、c64單元格，輸入公式：=max(c3:c62)和=min(c3:c62)，用于統(tǒng)計“語文”學(xué)科的最高分和最低分。 7、選中c65單元格，輸入公式：=if(iserror(average(c3:c62),average(c3:c62)，用于統(tǒng)計“語文”學(xué)科的平均分。 注意：直接用“=average(c3:c64)”公式也可以計算平均分，但是，如果成績表中沒有輸入成績時，這一公式將顯示出一個錯誤的值，因此，我們用“iserror”函數(shù)來排除這一錯誤

28、。 8、選中c66單元格，輸入公式：=sum(c3:c62)，用于統(tǒng)計“語文”學(xué)科的總分。 9、同時選中c63至c66單元格，用“填充柄”將上述公式復(fù)制到d63至j66單元格中，完成其它學(xué)科及總分的最高分、最低分、平均分和總分的統(tǒng)計工作。 至此，一個基本的成績統(tǒng)計表制作完成（參見xls文件下載），下面我們來進一步處理一下。 10、選中c3單元格，執(zhí)行“格式、條件格式”命令，打開“條件格式”對話框（如圖2），按最左邊一個方框右側(cè)的下拉按鈕，在隨后出現(xiàn)的下拉列表中，選“公式”選項，并在右側(cè)的方框中輸入公式：=c3=average(c3:c62)，然后按“格式”按鈕，打開“單元格格式”對話框，將字體

29、顏色設(shè)置為“藍色”。再按“添加”按鈕，仿照上面的操作，添加一個公式為“=c3average(c3:c62)”、字體顏色為“紅色”的條件格式（參見圖2）。=average($c$3:$c$62)和=c3average($c$3:$c$62)，確定返回（參見圖3）。average($c$3:$c$62)，確定返回（參見圖3）。依照上面的操作，將d3至j3單元格的條件格式中的公式修改為絕對引用（添加“$”符號）。 注意：這樣做的目的，是為了將其條件格式，通過“格式刷”復(fù)制到下面的各單元格中。 13、同時選中c3至j3單元格，按“格式”工具條上的“格式刷”按鈕，然后在c4至j62單元格區(qū)域上拖拉一遍，

30、將上述條件格式復(fù)制到相應(yīng)的區(qū)域中，完成其他學(xué)生各科成績及總分的條件格式設(shè)置工作。 把學(xué)生的成績填入到表格中試試看，效果不錯吧。 如果你經(jīng)常要統(tǒng)計學(xué)生的成績，我們將其保存為模板，方便隨時調(diào)用。14、將工作表中的學(xué)生成績等內(nèi)容刪除，執(zhí)行“文件、保存（另存為）”命令，打開“另存為”對話框（如圖4），按“保存類型”右側(cè)的下拉按鈕，在隨后出現(xiàn)的下拉列表中，選“模板（*.xlt）”選項，然后給定一個名稱（如“成績統(tǒng)計”），按下保存按鈕。 15、以后需要統(tǒng)計成績時，啟動excel，執(zhí)行“文件、新建”命令，展開“新建工作簿”任務(wù)窗格（如圖5），點擊其中的“本機上的模板”選項，打開“模板”對話框，雙擊“成績統(tǒng)計

31、”模板，即可新建一份新的成績統(tǒng)計表。 16、將學(xué)生的成績填入相應(yīng)的單元格中，取名保存即可快速完成成績統(tǒng)計處理工作。excel常用函數(shù)實例剖析用excel統(tǒng)計各分?jǐn)?shù)段學(xué)生數(shù)(1) 教師常常要統(tǒng)計各學(xué)科相應(yīng)分?jǐn)?shù)段的學(xué)生人數(shù)，以方便對考試情況作全方位的對比分析。在excel中，有多種函數(shù)可以實現(xiàn)這種統(tǒng)計工作，筆者以圖1所示的成績表為例，給出多種統(tǒng)計方法。文章末尾提供.xls文件供大家下載參考。 文章導(dǎo)讀：方法一：用countif函數(shù)統(tǒng)計這是最常用、最容易理解的一種方法，我們用它來統(tǒng)計“語文”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。 如果某些學(xué)科（如體育），其成績是不具體數(shù)值，而是字符等級（如“優(yōu)秀、良好”等），我們也可

32、以用countif函數(shù)來統(tǒng)計各等級的學(xué)生人數(shù)。方法二：用dcount函數(shù)統(tǒng)計這個函數(shù)不太常用，但用來統(tǒng)計分?jǐn)?shù)段學(xué)生數(shù)效果很不錯。我們用它統(tǒng)計“數(shù)學(xué)”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。 方法三：用frequency函數(shù)統(tǒng)計這是一個專門用于統(tǒng)計某個區(qū)域中數(shù)據(jù)的頻率分布函數(shù)，我們用它來統(tǒng)計“英語”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。 方法四：用sum函數(shù)統(tǒng)計我們知道sum函數(shù)通常是用來求和的，其實，他也可以用來進行多條件計數(shù)，我們用它來統(tǒng)計“政治”學(xué)科各分?jǐn)?shù)段的學(xué)生數(shù)。方法一：用countif函數(shù)統(tǒng)計這是最常用、最容易理解的一種方法，我們用它來統(tǒng)計“語文”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。函數(shù)功能及用法介紹 分別選中c63、c67單元格，輸入

33、公式：=countif(c3:c62,=90)，即可統(tǒng)計出“語文”成績“低于60分”和“大于等于90”的學(xué)生人數(shù)。 分別選中c64、c65和c66單元格，輸入公式：=countif(c3:c62,=60)-countif(c3:c62,=70)、=countif(c3:c62,=70)-countif(c3:c62,=80)和=countif(c3:c62,=80)-countif(c3:c62,=90)，即可統(tǒng)計出成績在60-69分、70-79分、80-89分區(qū)間段的學(xué)生人數(shù)。 注意：同時選中c63至c67單元格，將鼠標(biāo)移至c67單元格右下角，成細十字線狀時，按住左鍵向右拖拉至i列，就可以統(tǒng)

34、計出其它學(xué)科各分?jǐn)?shù)段的學(xué)生數(shù)。如果某些學(xué)科（如體育），其成績是不具體數(shù)值，而是字符等級（如“優(yōu)秀、良好”等），我們可以用countif函數(shù)來統(tǒng)計各等級的學(xué)生人數(shù)。如果某些學(xué)科（如體育），其成績是不具體數(shù)值，而是字符等級（如“優(yōu)秀、良好”等），我們可以用countif函數(shù)來統(tǒng)計各等級的學(xué)生人數(shù)。 在k64至k67單元格中，分別輸入成績等級字符（參見圖2）。選中l(wèi)64單元格，輸入公式：=countif($l$3:$l$62,k64)，統(tǒng)計出“優(yōu)秀”的學(xué)生人數(shù)。 再次選中l(wèi)64單元格，用“填充柄”將上述公式復(fù)制到l65至l67單元格中，統(tǒng)計出其它等級的學(xué)生人數(shù)。 上述全部統(tǒng)計結(jié)果參見圖1。excel

35、常用函數(shù)實例剖析用excel統(tǒng)計各分?jǐn)?shù)段學(xué)生數(shù)(2) 方法二：用dcount函數(shù)統(tǒng)計這個函數(shù)不太常用，但用來統(tǒng)計分?jǐn)?shù)段學(xué)生數(shù)效果很不錯。我們用它統(tǒng)計“數(shù)學(xué)”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。 分別選中m63至n72單元格區(qū)域（不一定非得不這個區(qū)域），輸入學(xué)科名稱（與統(tǒng)計學(xué)科名稱一致，如“數(shù)學(xué)”等）及相應(yīng)的分?jǐn)?shù)段（如圖2）。 分別選中d63、d64d67單元格，輸入公式：=dcount(d2:d62,數(shù)學(xué),m63:n64)、=dcount(d2:d62,數(shù)學(xué),m65:n66)、=dcount(d2:d62,數(shù)學(xué),m67:n68)、=dcount(d2:d62,數(shù)學(xué),m69:n70)、=dcount($d$2:

36、$d$62,數(shù)學(xué),m71:n72)，確認即可。 注意：將上述公式中的“dcount”函數(shù)換成“dcounta”函數(shù)，同樣可以實現(xiàn)各分?jǐn)?shù)段學(xué)生人數(shù)的統(tǒng)計。方法三：用frequency函數(shù)統(tǒng)計這是一個專門用于統(tǒng)計某個區(qū)域中數(shù)據(jù)的頻率分布函數(shù)，我們用它來統(tǒng)計“英語”學(xué)科各分?jǐn)?shù)段學(xué)生數(shù)。 分別選中o64至o67單元格，輸入分?jǐn)?shù)段的分隔數(shù)值（參見圖2）。同時選中e63至e67單元格區(qū)域，在“編輯欄”中輸入公式：=frequency(e3:e62,$o$64:$o$67)，輸入完成后，按下“ctrl+shift+enter”組合鍵進行確認，即可一次性統(tǒng)計出“英語”學(xué)科各分?jǐn)?shù)段的學(xué)生人數(shù)。 注意：實際上此處

37、輸入的是一個數(shù)組公式，數(shù)組公式輸入完成后，不能按“enter”鍵進行確認，而是要按“ctrl+shift+enter”組合鍵進行確認。確認完成后，在公式兩端出現(xiàn)一個數(shù)組公式的標(biāo)志“”（該標(biāo)志不能用鍵盤直接輸入）。數(shù)組公式也支持用“填充柄”拖拉填充：同時選中e63至e67單元格區(qū)域，將鼠標(biāo)移至e67單元格右下角，成細十字線狀時，按住左鍵向右拖拉，就可以統(tǒng)計出其它學(xué)科各分?jǐn)?shù)段的學(xué)生數(shù)。方法四：用sum函數(shù)統(tǒng)計我們知道sum函數(shù)通常是用來求和的，其實，他也可以用來進行多條件計數(shù)，我們用它來統(tǒng)計“政治”學(xué)科各分?jǐn)?shù)段的學(xué)生數(shù)。 分別選中p64至p69單元格，輸入分?jǐn)?shù)段的分隔數(shù)值（參見圖2）。 選中f63

38、單元格，輸入公式：=sum($f$3:$f$62=p64)*($f$3:$f$62=60)*($f$3:$f$62=60,b5=60)，確認。如果c5中返回true，說明a5和b5中的數(shù)值均大于等于60，如果返回false，說明a5和b5中的數(shù)值至少有一個小于60。 特別提醒：如果指定的邏輯條件參數(shù)中包含非邏輯值時，則函數(shù)返回錯誤值“#value!”或“#name”。 3、average函數(shù) 函數(shù)名稱：average 主要功能：求出所有參數(shù)的算術(shù)平均值。 使用格式：average(number1,number2,) 參數(shù)說明：number1,number2,：需要求平均值的數(shù)值或引用單元格（區(qū)

39、域），參數(shù)不超過30個。 應(yīng)用舉例：在b8單元格中輸入公式：=average(b7:d7,f7:h7,7,8)，確認后，即可求出b7至d7區(qū)域、f7至h7區(qū)域中的數(shù)值和7、8的平均值。 特別提醒：如果引用區(qū)域中包含“0”值單元格，則計算在內(nèi)；如果引用區(qū)域中包含空白或字符單元格，則不計算在內(nèi)。4、column 函數(shù) 函數(shù)名稱：column 主要功能：顯示所引用單元格的列標(biāo)號值。 使用格式：column(reference) 參數(shù)說明：reference為引用的單元格。 應(yīng)用舉例：在c11單元格中輸入公式：=column(b11)，確認后顯示為2（即b列）。 特別提醒：如果在b11單元格中輸入公式

40、：=column()，也顯示出2；與之相對應(yīng)的還有一個返回行標(biāo)號值的函數(shù)row(reference)。5、concatenate函數(shù) 函數(shù)名稱：concatenate 主要功能：將多個字符文本或單元格中的數(shù)據(jù)連接在一起，顯示在一個單元格中。 使用格式：concatenate(text1，text) 參數(shù)說明：text1、text2為需要連接的字符文本或引用的單元格。 應(yīng)用舉例：在c14單元格中輸入公式：=concatenate(a14,b14,.com)，確認后，即可將a14單元格中字符、b14單元格中的字符和.com連接成一個整體，顯示在c14單元格中。 特別提醒：如果參數(shù)不是引用的單元格，

41、且為文本格式的，請給參數(shù)加上英文狀態(tài)下的雙引號，如果將上述公式改為：=a14&b14&.com，也能達到相同的目的。6、countif函數(shù) 函數(shù)名稱：countif 主要功能：統(tǒng)計某個單元格區(qū)域中符合指定條件的單元格數(shù)目。 使用格式：countif(range,criteria) 參數(shù)說明：range代表要統(tǒng)計的單元格區(qū)域；criteria表示指定的條件表達式。 應(yīng)用舉例：在c17單元格中輸入公式：=countif(b1:b13,=80)，確認后，即可統(tǒng)計出b1至b13單元格區(qū)域中，數(shù)值大于等于80的單元格數(shù)目。 特別提醒：允許引用的單元格區(qū)域中有空白單元格出現(xiàn)。7、date函數(shù) 函數(shù)名稱：d

42、ate 主要功能：給出指定數(shù)值的日期。 使用格式：date(year,month,day) 參數(shù)說明：year為指定的年份數(shù)值（小于9999）；month為指定的月份數(shù)值（可以大于12）；day為指定的天數(shù)。 應(yīng)用舉例：在c20單元格中輸入公式：=date(2003,13,35)，確認后，顯示出2004-2-4。 特別提醒：由于上述公式中，月份為13，多了一個月，順延至2004年1月；天數(shù)為35，比2004年1月的實際天數(shù)又多了4天，故又順延至2004年2月4日。8、datedif函數(shù) 函數(shù)名稱：datedif 主要功能：計算返回兩個日期參數(shù)的差值。 使用格式：=datedif(date1,d

43、ate2,y)、=datedif(date1,date2,m)、=datedif(date1,date2,d) 參數(shù)說明：date1代表前面一個日期，date2代表后面一個日期；y（m、d）要求返回兩個日期相差的年（月、天）數(shù)。 應(yīng)用舉例：在c23單元格中輸入公式：=datedif(a23,today(),y)，確認后返回系統(tǒng)當(dāng)前日期用today()表示）與a23單元格中日期的差值，并返回相差的年數(shù)。 特別提醒：這是excel中的一個隱藏函數(shù)，在函數(shù)向?qū)е惺钦也坏降模梢灾苯虞斎胧褂?，對于計算年齡、工齡等非常有效。9、day函數(shù) 函數(shù)名稱：day 主要功能：求出指定日期或引用單元格中的日期的天

44、數(shù)。 使用格式：day(serial_number) 參數(shù)說明：serial_number代表指定的日期或引用的單元格。 應(yīng)用舉例：輸入公式：=day(2003-12-18)，確認后，顯示出18。 特別提醒：如果是給定的日期，請包含在英文雙引號中。10、dcount函數(shù) 函數(shù)名稱：dcount 主要功能：返回數(shù)據(jù)庫或列表的列中滿足指定條件并且包含數(shù)字的單元格數(shù)目。 使用格式：dcount(database,field,criteria) 參數(shù)說明：database表示需要統(tǒng)計的單元格區(qū)域；field表示函數(shù)所使用的數(shù)據(jù)列（在第一行必須要有標(biāo)志項）；criteria包含條件的單元格區(qū)域。 應(yīng)用舉

45、例：如圖1所示，在f4單元格中輸入公式：=dcount(a1:d11,語文,f1:g2)，確認后即可求出“語文”列中，成績大于等于70，而小于80的數(shù)值單元格數(shù)目（相當(dāng)于分?jǐn)?shù)段人數(shù)）。 特別提醒：如果將上述公式修改為：=dcount(a1:d11,f1:g2)，也可以達到相同目的。11、frequency函數(shù) 函數(shù)名稱：frequency 主要功能：以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布。 使用格式：frequency(data_array,bins_array) 參數(shù)說明：data_array表示用來計算頻率的一組數(shù)據(jù)或單元格區(qū)域；bins_array表示為前面數(shù)組進行分隔一列數(shù)值。 應(yīng)

46、用舉例：如圖2所示，同時選中b32至b36單元格區(qū)域，輸入公式：=frequency(b2:b31,d2:d36)，輸入完成后按下“ctrl+shift+enter”組合鍵進行確認，即可求出b2至b31區(qū)域中，按d2至d36區(qū)域進行分隔的各段數(shù)值的出現(xiàn)頻率數(shù)目（相當(dāng)于統(tǒng)計各分?jǐn)?shù)段人數(shù)）。 特別提醒：上述輸入的是一個數(shù)組公式，輸入完成后，需要通過按“ctrl+shift+enter”組合鍵進行確認，確認后公式兩端出現(xiàn)一對大括號（），此大括號不能直接輸入。12、if函數(shù) 函數(shù)名稱：if 主要功能：根據(jù)對指定條件的邏輯判斷的真假結(jié)果，返回相對應(yīng)的內(nèi)容。 使用格式：=if(logical,value_

47、if_true,value_if_false) 參數(shù)說明：logical代表邏輯判斷表達式；value_if_true表示當(dāng)判斷條件為邏輯“真（true）”時的顯示內(nèi)容，如果忽略返回“true”；value_if_false表示當(dāng)判斷條件為邏輯“假（false）”時的顯示內(nèi)容，如果忽略返回“false”。 應(yīng)用舉例：在c29單元格中輸入公式：=if(c26=18,符合要求,不符合要求)，確信以后，如果c26單元格中的數(shù)值大于或等于18，則c29單元格顯示“符合要求”字樣，反之顯示“不符合要求”字樣。 特別提醒：本文中類似“在c29單元格中輸入公式”中指定的單元格，讀者在使用時，并不需要受其約束，此處只是配合本文所附的實例需要而給出的相應(yīng)單元格，具體請大家參考所附的實例文件。13