excel實(shí)驗(yàn)指導(dǎo)-函數(shù)的使用

1、實(shí)驗(yàn)八學(xué)生成績(jī)表數(shù)學(xué)函數(shù)、統(tǒng)計(jì)函數(shù)一、實(shí)驗(yàn)?zāi)康?、掌握數(shù)組公式、Sum()函數(shù)2、掌握Averageif()和Sumif()3、掌握COUNT()、COUNTA()、COUNTIF()、Countblank()4、5、數(shù)據(jù)庫(kù)函數(shù)的使用實(shí)驗(yàn)內(nèi)容1、利用數(shù)組公式或Sum()函數(shù)來統(tǒng)計(jì)每個(gè)同學(xué)上學(xué)期的總分。2、利用Averageif()和Sumif()統(tǒng)計(jì)平均分和總分。3、利用統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)班級(jí)人數(shù)，每門課程不及格人數(shù)，缺考科目數(shù)。4、對(duì)班級(jí)同學(xué)的考試情況進(jìn)行排名。5、選擇合適的數(shù)據(jù)庫(kù)函數(shù)統(tǒng)計(jì)信息實(shí)驗(yàn)任務(wù)小王是班級(jí)學(xué)習(xí)委員，現(xiàn)正值新學(xué)期評(píng)優(yōu)時(shí)期，班主任委托小王統(tǒng)計(jì)班級(jí)同學(xué)上學(xué)期的考試成績(jī)情況。小王要應(yīng)

2、用函數(shù)分析學(xué)生信息、計(jì)算考試成績(jī)，分析每科成績(jī)的最高分、最低分和平均分，統(tǒng)計(jì)每個(gè)學(xué)生的總分排名，并統(tǒng)計(jì)不同寢室的學(xué)習(xí)情況。本例效果圖如錯(cuò)誤!未找到引用源。所示，小王需要完成的工作包括：(1)統(tǒng)計(jì)每個(gè)同學(xué)各門課程的總分并排名。(2)統(tǒng)計(jì)每個(gè)寢室的平均分。(3)統(tǒng)計(jì)每門課程的不及格人數(shù)和缺考人數(shù)。(4)統(tǒng)計(jì)符合特定條件的學(xué)生信息。成數(shù)組公式的編輯，如成數(shù)組公式的編輯，如錯(cuò)誤未找到引用源。所示。成數(shù)組公式的編輯，如成數(shù)組公式的編輯，如錯(cuò)誤未找到引用源。所示。AE1)7GHI,TEL0PqL學(xué)生.成績(jī)表2閩孔茂武空的的的TS國(guó)曲阮計(jì)跑西羊生J.盛如男_4_石清華tr型17468馴用65打523蛇十立H

3、才干乎詢?nèi)宋?3S李舉感女2dmTS91的能用ggEtq人次鼬S性別大學(xué)語(yǔ)交G怕小鬼也卸靠05網(wǎng)82732抵文平均HT6.tT女7石更1401軌?mèng)?691-44,期主幫平均分Bl.粕B井生至空1402即湍TS的叁收612訊由室總分取Mff.fi性性9在捐皿61TT676973河T2fiIQ國(guó)Lu李國(guó)華空品皿62港7/q2111葉否督如7595馴059&四5fi排名X號(hào)12李斌財(cái)空%幻招能72：T922y14AS13起建民里1403玲950389鑰：卻314特海至里14AST2前馥裝鑿13再注語(yǔ)言布于8Jr315張平光里1由嘉903995100鴻4T2諾文中干如弁的女生姓名本門白許動(dòng)用或1月皿6

4、。燈5559即5T10女生同拈怪才暴苗分9617井云ir240272的施TT鴇融即博名?SL。甲黑主的平均總分BSEU廿Z4OZS305T3909&-JBT2Wi19朱國(guó)強(qiáng)空印強(qiáng)E0妨俎290瞿建峰里1生薰能02669T鑰：45ZL該噴蜿土初工,日5馥第制422假月軒里1403938890%-672Z3王麗土留流B6B6TS92421400”，注意在Excel中輸入的標(biāo)點(diǎn)符號(hào)均為英文格式，單擊確定按鈕完成計(jì)算，如錯(cuò)誤!未找到引用源。所示。COUHTIP=19計(jì)算某個(gè)區(qū)域中前足給定條件的單元格數(shù)目匚門fW以數(shù)字、表達(dá)式或文本形式定義的條件計(jì)算結(jié)果=19有關(guān)該函物的郊附(H確定取消|I一一一圖9-

5、6Countif()函數(shù)參數(shù)對(duì)話框.使用Countblank()函數(shù)統(tǒng)計(jì)缺考人次數(shù)Countblank()函數(shù)用來計(jì)算指定區(qū)域中空白單元格的個(gè)數(shù)。選中N5單元格，使用“公式”T“插入函數(shù)”按鈕，找到Countblank()函數(shù)后打開參數(shù)對(duì)話框，在Range參數(shù)中輸入要統(tǒng)計(jì)的區(qū)域范圍，課程成績(jī)區(qū)域?yàn)镈3:H32,單擊確定按鈕完成計(jì)算。3統(tǒng)計(jì)平均分、總分.使用Average。函數(shù)統(tǒng)計(jì)語(yǔ)文平均分在N6單元格中計(jì)算語(yǔ)文平均分。選中N6單元格，使用“公式”T“插入函數(shù)”按鈕，找到Average。函數(shù)后打開參數(shù)對(duì)話框，在Numbe門參數(shù)中輸入要統(tǒng)計(jì)的區(qū)域范圍，語(yǔ)文成績(jī)區(qū)域?yàn)镈3:D32,如錯(cuò)誤!未找到引用

6、源。所示，單擊確定求得平均成績(jī)。國(guó)數(shù)學(xué)數(shù)國(guó)數(shù)學(xué)數(shù)圖9-7Average。函數(shù)參數(shù)對(duì)話框.使用AverageifO函數(shù)統(tǒng)計(jì)男生C語(yǔ)言程序設(shè)計(jì)平均分在N7單元格中計(jì)算班級(jí)所有男生C語(yǔ)言程序設(shè)計(jì)的平均分。選中N7單元格，使用“公式”T“插入函數(shù)”按鈕，找到AverageifO函數(shù)后打開參數(shù)對(duì)話框，在Range參數(shù)中輸入要統(tǒng)計(jì)平均值的條件所在的區(qū)域，條件區(qū)域?yàn)樾詣e男即B3:B32,Criteria參數(shù)中輸入條件,我們可以直接輸入“男二也可以從性別列中引用內(nèi)容為男的單元格如B3,Average_range參數(shù)中輸入要計(jì)算平均值的實(shí)際單元格區(qū)域，輸入C程課程考分G3:G32,如錯(cuò)誤!未找到引用源。所示，單

7、擊確定求得男生C程的平均成績(jī)。圖9-8AverageifO函數(shù)參數(shù)對(duì)話框.使用Sumif()函數(shù)統(tǒng)計(jì)1401寢室學(xué)生總分Sumif()函數(shù)是根據(jù)指定條件對(duì)若干個(gè)單元格、區(qū)域或引用進(jìn)行求和。選中N8單元格,使用“公式”T“插入函數(shù)”按鈕，找到sumifO函數(shù)后打開參數(shù)對(duì)話框，在Range參數(shù)中輸入要統(tǒng)計(jì)求和值的條件所在的區(qū)域，條件區(qū)域?yàn)閷嬍姨?hào)1401即C3:C32,Criteria參數(shù)中輸入條件，我們可以直接輸入“1401”，也可以從寢室號(hào)列中引用內(nèi)容為1401的單元格如C3,Sumjange參數(shù)中輸入要計(jì)算求和值的實(shí)際單元格區(qū)域，輸入總分13:132,如錯(cuò)誤!未找到引用源。所示，單擊確定求得1

8、401寢室學(xué)生的總分。圖9-9Sumif()函數(shù)參數(shù)對(duì)話框9.3.4使用0統(tǒng)計(jì)班級(jí)學(xué)生排名在J列中統(tǒng)計(jì)每個(gè)學(xué)生的總分由高分到低分的排名，我們可以使用0函數(shù)，該函數(shù)功能為返回某個(gè)數(shù)字在數(shù)字列表中的排名。選擇J3單元格，使用“公式”T“插入函數(shù)”按鈕，找到0函數(shù)后打開參數(shù)對(duì)話框，在Number參數(shù)中輸入要計(jì)算哪個(gè)數(shù)值的排名，在此我們輸入13；在Ref中輸入要進(jìn)行排名的區(qū)域，在此我們輸入13:132；前兩個(gè)參數(shù)合并起來的意思就是計(jì)算I3在13:132區(qū)域中的大小排名；Order參數(shù)為排序的方式，如果忽略不填或填0表示降序排列，否則就是升序排列。如錯(cuò)誤!未找到引用源。所示，單擊確定按鈕，這時(shí)在J3單元

9、格中顯示結(jié)果為14,表示I3單元格的數(shù)據(jù)在13:132區(qū)域中從高到低降序排列為第14名。RABK.EQNumberRABK.EQNumber13:132421；375；439；402；444；426；347；3.13:132邏輯值品酷露在一列數(shù)字中相對(duì)于其他數(shù)值的大小排得如果紡個(gè)數(shù)值排翎晌則返回該組數(shù)值Number指定的數(shù)字計(jì)算結(jié)果=14有關(guān)謨困船的新助(H)圖9-10()函數(shù)參數(shù)對(duì)話框?qū)τ贘列其余單元格的排名，如果簡(jiǎn)單的使用填充柄進(jìn)行填充,我們將會(huì)發(fā)現(xiàn)是錯(cuò)誤的,因?yàn)楹瘮?shù)參數(shù)有所改變。但是Ref參數(shù)13:132應(yīng)該是不變的，因此需要對(duì)Ref參數(shù)13:132使用絕對(duì)地址，使其在用填充柄填充的時(shí)候

10、不發(fā)生變化。選中已經(jīng)編輯好的J3單元格，單擊編輯欄上的“插入函數(shù)”按鈕，直接彈出編輯好的0函數(shù)對(duì)話框，選中Ref參數(shù)中的13:132,按下功能鍵F4,直接給行列號(hào)添加上$絕對(duì)地址引用符號(hào)，如錯(cuò)誤未找到引用源。所示，單擊確定完成，之后我們就可以使用填充柄正確計(jì)算其余同學(xué)的總分排名。圖9-11()函數(shù)參數(shù)絕對(duì)地址引用9.3.5數(shù)據(jù)庫(kù)函數(shù)的使用Excel數(shù)據(jù)庫(kù)函數(shù)共有12個(gè)，每個(gè)數(shù)據(jù)庫(kù)函數(shù)的首字母都是D,我們也稱為D函數(shù)。常用數(shù)據(jù)庫(kù)函數(shù)有Dmin()、Dmax()、Daverage0 xDsum()、Dcount0o這些函數(shù)去掉前面的D就和普通的常用函數(shù)一樣，分別用來計(jì)算數(shù)據(jù)庫(kù)清單在一定條件下的最小

11、值、最大值、平均值、求和、計(jì)數(shù)。對(duì)于數(shù)據(jù)庫(kù)函數(shù)，我們可以理解為多條件求值函數(shù)，如無條件計(jì)數(shù)函數(shù)為Count0,單條件計(jì)數(shù)函數(shù)為Countif(),而多條件計(jì)數(shù)函數(shù)為Dcount(),因此在使用數(shù)據(jù)庫(kù)函數(shù)前必須先設(shè)計(jì)好條件區(qū)域以供引用。數(shù)據(jù)庫(kù)函數(shù)的參數(shù)均是一樣的，都包含三個(gè)參數(shù)：Datebase：所求的數(shù)據(jù)清單或數(shù)據(jù)庫(kù)單元格區(qū)域。Field：指定函數(shù)運(yùn)算所使用的列，可輸入加上雙引號(hào)的字段名或直接引用該字段名，也可以輸入代表列表中列位置的數(shù)字。Criteria：給定的條件區(qū)域。.使用Dcount()函數(shù)統(tǒng)計(jì)男生語(yǔ)文高于90分的學(xué)生人數(shù)要統(tǒng)計(jì)男生語(yǔ)文高于90分的學(xué)生人數(shù)，需要兩個(gè)條件，分別是性別為男

12、和語(yǔ)文成績(jī)高于90,屬于多條件計(jì)數(shù)，因此用數(shù)據(jù)庫(kù)函數(shù)Dcount()。先在P2:Q3設(shè)置條件區(qū)域如錯(cuò)誤!未找到引用源。所示，注意：數(shù)據(jù)清單中能復(fù)制的內(nèi)容盡量復(fù)制。在N14單元格中，使用“公式”T“插入函數(shù)”按鈕，找到Dcount()函數(shù)后打開參數(shù)對(duì)話框，在Datebase參數(shù)中輸入數(shù)據(jù)清單范圍，即A2:J32,在此注意第二行必須選，作為數(shù)據(jù)清單的首行；Field中引用D2,表示要算計(jì)大學(xué)語(yǔ)文的個(gè)數(shù)；Criteria中輸入P2:Q3,如錯(cuò)誤!未找到引用源。所示，單擊確定后完成計(jì)算。J生別大學(xué)語(yǔ)文男90圖9-12條件區(qū)域曾數(shù)奏數(shù)DCOUNTDatabase國(guó)=姓名:“性別:“寢室號(hào):大學(xué)語(yǔ)文Fie

13、ldD2|=大學(xué)語(yǔ)文“CriteriaF2:Q3亶=F2:Q3從商是給定條件的數(shù)據(jù)庫(kù)記錄的字段因)中，計(jì)算數(shù)值單元格數(shù)目一3Fild用雙引號(hào)括住的列標(biāo)簽，或是表示該列在列表中位置的額值計(jì)算結(jié)果=3確定取消確定取消圖9-13DcountO函數(shù)參數(shù)對(duì)話框.使用Dget()函數(shù)統(tǒng)計(jì)語(yǔ)文高于90分的女生姓名DgetO函數(shù)的功能是提取符合條件的唯一記錄，如果記錄不唯一則返回#川乂！。條件區(qū)域設(shè)置如錯(cuò)誤!未找到引用源。所示，函數(shù)參數(shù)如錯(cuò)誤!未找到引用源。所示。圖9-14條件區(qū)域圖9-15Dget()函數(shù)參數(shù)對(duì)話框.使用Dmax()函數(shù)統(tǒng)計(jì)女生中網(wǎng)絡(luò)技術(shù)的最高分Dmax()函數(shù)的功能是提取滿足條件記錄指定列

14、的最大值，函數(shù)參數(shù)如錯(cuò)誤!未找到引用源。所示。因數(shù)參數(shù)UMAX因數(shù)參數(shù)UMAXA2:J32國(guó)H2屋F5:F6|DatabaseFieldCriteriaF5:F6=姓名:“性別寢室號(hào)二大學(xué)語(yǔ)文DatabaseFieldCriteriaF5:F6=網(wǎng)絡(luò)技術(shù)”96返回滿足給定條件的數(shù)據(jù)庫(kù)中記錄的字段因D中數(shù)據(jù)的最大值庫(kù)條件的單元格區(qū)域。該區(qū)慟包括列標(biāo)簽及列標(biāo)箜下菽足條件計(jì)算結(jié)果=96I取消育蕓謖函凝的甜購(gòu)00I取消圖9-16Dmax()函數(shù)參數(shù)對(duì)話框.使用DaverageO函數(shù)統(tǒng)計(jì)排名前10名中男生的平均總分DaverageO函數(shù)的功能是求滿足條件記錄指定列的平均值，條件區(qū)域如錯(cuò)誤!未找到引用源。

15、所示，函數(shù)參數(shù)如錯(cuò)誤!未找到引用源。所示。圖9-17條件區(qū)域圖9-18DaverageO函數(shù)參數(shù)對(duì)話框.使用Dproduct()函數(shù)統(tǒng)計(jì)排名前5名中的1403寢室學(xué)生物理成績(jī)的乘積Dproduct()函數(shù)的功能是將數(shù)據(jù)庫(kù)中符合條件記錄的特定字段中的值相乘。條件區(qū)域如錯(cuò)誤!未找到引用源。所示，函數(shù)參數(shù)如錯(cuò)誤!未找到引用源。所示。3二咯寤室號(hào)1403圖9-19條件區(qū)域圖9-20Dproduct()函數(shù)參數(shù)對(duì)話框9.4案例總結(jié)口本項(xiàng)目主要學(xué)習(xí)了Excel中數(shù)組公式的使用，基本函數(shù)中計(jì)數(shù)函數(shù)Count()、Counta()、Countif()、Countblank()用以統(tǒng)計(jì)個(gè)數(shù)，求和函數(shù)Sum()、

16、Sumif()，平均值函數(shù)Average。，排序函數(shù)()并進(jìn)一步強(qiáng)化了絕對(duì)地址的應(yīng)用。項(xiàng)目中也介紹了數(shù)據(jù)庫(kù)函數(shù)，用于對(duì)數(shù)據(jù)清單中滿足條件的記錄中指定列的數(shù)據(jù)進(jìn)行分析，數(shù)據(jù)庫(kù)函數(shù)具有相同的參數(shù)，求解的結(jié)果是由函數(shù)自身決定的。5課后練習(xí)口打開采購(gòu)情況表.xlsx，完成如下設(shè)置，效果如錯(cuò)誤未找到引用源。所示。.使用數(shù)組公式，計(jì)算采購(gòu)情況表中每種產(chǎn)品的采購(gòu)總額，將結(jié)果填到“采購(gòu)總額”列中，采購(gòu)總額的計(jì)算方法為：采購(gòu)總額=單價(jià)*每盒數(shù)量*采購(gòu)盒數(shù)。.使用統(tǒng)計(jì)函數(shù)統(tǒng)計(jì)采購(gòu)表中共采購(gòu)產(chǎn)品單數(shù)，填入單元格中。.使用統(tǒng)計(jì)函數(shù)計(jì)算未知壽命產(chǎn)品類數(shù)，填入的單元格中。.計(jì)算不同種類的白熾燈平均單價(jià)，填入K4單元格中。

17、.使用SumifO統(tǒng)計(jì)不同種類產(chǎn)品的總采購(gòu)盒數(shù)和總采購(gòu)金額。.使用數(shù)據(jù)庫(kù)函數(shù)及設(shè)置好的條件區(qū)域，計(jì)算商標(biāo)為上海，壽命小于100的白熾燈平均單價(jià)，并將結(jié)果填入在G22單元格中，保留兩位小數(shù)。.使用數(shù)據(jù)庫(kù)函數(shù)及設(shè)置好的條件區(qū)域，計(jì)算產(chǎn)品為白熾燈，瓦數(shù)小于等于100且大于等于80的品種數(shù)，將結(jié)果填入在G23單元格中。AE71FC_rJKL1采啊閽次表,2F3瓦數(shù)W（小時(shí)麗梨的爆赦產(chǎn)品總記錄敬16白熾燈2DD鼻毗1上海4.504354.00信想璉竭124缸百1002D00上海2152eo-oo白懼燈平均甲f1.275日北打603000上海2.001C5ico.oa其他10SODO北京0.8025120.007白熾燈801000上海U.2。救324.00產(chǎn)品兌采購(gòu)津例息采購(gòu)泉慚艮日北打1DD上濯1.2511?450.00白怛燈2T：1殂59日光燈20D3DD0上