AOA考試寶典三合一

1、省二級(jí)aoa考試 寶典 aoa excel 考試題（對(duì)照）參考(一) 學(xué)號(hào)(數(shù)組公式if).xls1. 使用數(shù)組公式，對(duì)sheet1計(jì)算總分和平均分，將其計(jì)算結(jié)果保存到表中的“總分”列和“平均分”列當(dāng)中?？偡郑?c2:c39+d2:d39+e2:e39 平均分：=f2:f39/3（注意，題目明確要求用數(shù)組公式，就必須用數(shù)組公式，否則不得分。數(shù)組公式中的一對(duì)不是人為錄入的，必須用ctrl+shift+enter組合健輸入。）2. 使用rank函數(shù)，對(duì)sheet1中的每個(gè)同學(xué)排名情況進(jìn)行統(tǒng)計(jì)，并將排名結(jié)果保存到表中的“排名”列當(dāng)中。排名：=rank(g2,g$2:g$39) 或者 =rank(f2

2、,f$2:f$39) 然后利用填充柄復(fù)制公式3. 使用邏輯函數(shù)判斷sheet1中每個(gè)同學(xué)的每門功課是否均高于平均分，如果是，保存結(jié)果為true，否則，保存結(jié)果為false，將結(jié)果保存在表中的“三科成績是否均超過平均”列當(dāng)中。=if(and(c2average($g$2:$g$39),d2average($g$2:$g$39),e2average($g$2:$g$39),true,false)或者：=if(c2average($c$2:$c$39),if(d2average($d$2:$d$39),if(e2average($e$2:$e$39),true,false),false),false

3、)4. 根據(jù)sheet1中的結(jié)果，使用統(tǒng)計(jì)函數(shù)，統(tǒng)計(jì)“數(shù)學(xué)”考試成績各個(gè)分?jǐn)?shù)段的同學(xué)人數(shù)，將統(tǒng)計(jì)結(jié)果保存到sheet2中的相應(yīng)位置。數(shù)學(xué)分?jǐn)?shù)位于0到20分的人數(shù)：=countif(sheet1!$d$2:$d$39,20)數(shù)學(xué)分?jǐn)?shù)位于20到40分的人數(shù)：=countif(sheet1!$d$2:$d$39,40)-countif(sheet1!$d$2:$d$39,20)數(shù)學(xué)分?jǐn)?shù)位于40到60分的人數(shù)：=countif(sheet1!$d$2:$d$39,60)-countif(sheet1!$d$2:$d$39,40)數(shù)學(xué)分?jǐn)?shù)位于60到80分的人數(shù)：=countif(sheet1!$d$2:

4、$d$39,80)-countif(sheet1!$d$2:$d$39,60)數(shù)學(xué)分?jǐn)?shù)位于80到100分的人數(shù)：=countif(sheet1!$d$2:$d$39,100)-countif(sheet1!$d$2:$d$39,=0=20=40=60=8075，“數(shù)學(xué)”75，“英語”75，“總分”250；篩選的條件區(qū)間如下：語文數(shù)學(xué)英語總分=75=75=75=250 b. 將結(jié)果保存在sheet3中。注：（a）無需考慮是否刪除或移動(dòng)篩選條件; （b）復(fù)制過程中，將標(biāo)題項(xiàng)“學(xué)生成績表”連同數(shù)據(jù)一同復(fù)制; （c）復(fù)制數(shù)據(jù)表后，粘貼時(shí)，數(shù)據(jù)表必須頂格放置。6. 根據(jù)sheet1中的結(jié)果，在sheet

5、4中創(chuàng)建一張數(shù)據(jù)透視表，要求： a. 顯示是否三科均超過平均分的學(xué)生人數(shù)； b. 行區(qū)域設(shè)置為：“三科成績是否均超過平均”； c. 計(jì)數(shù)項(xiàng)為三科成績是否均超過平均。完成的數(shù)據(jù)透視表應(yīng)如下所示。計(jì)數(shù)項(xiàng):三科成績是否均超過平均三科成績是否均超過平均匯總false27true11總計(jì)38(二) 折扣表(采購表)(vlookup函數(shù)).xls1. 使用vlookup函數(shù)，對(duì)sheet1中的商品單價(jià)進(jìn)行自動(dòng)填充。 要求：根據(jù)“價(jià)格表”中的商品單價(jià)，利用vlookup函數(shù)，將其單價(jià)自動(dòng)填充到采購表中的“單價(jià)”列中。=vlookup(a11,f$2:g$5,2,0)或者用數(shù)組公式做：=vlookup($a$

6、11:$a$43,$f$2:$g$5,2,0)2. 使用邏輯函數(shù)，對(duì)sheet1中的商品折扣率進(jìn)行自動(dòng)填充。要求：根據(jù)“折扣表”中的商品折扣率，利用相應(yīng)的函數(shù)，將其折扣率自動(dòng)填充到采購表中的“折扣“列中。=if(b11=a$6,b$6,if(b11=a$5,b$5,if(b11=a$4,b$4,b$3)3. 利用公式，計(jì)算sheet1中的“合計(jì)金額”。 要求：根據(jù)“采購數(shù)量”，“單價(jià)”和“折扣”，計(jì)算采購的“合計(jì)金額”。計(jì)算公式：單價(jià)*采購數(shù)量*（1-折扣）=d11*b11*(1-e11)4. 使用sumif函數(shù)，統(tǒng)計(jì)各種商品的采購總量和采購總金額，將結(jié)果保存在sheet1中的“統(tǒng)計(jì)表”當(dāng)中。

7、采購總量：=sumif(a$11:a$43,i12,b$11:b$43)采購總金額：=sumif(a$11:a$43,i12,f$11:f$43)5. 對(duì)sheet2中的“采購表”進(jìn)行高級(jí)篩選。a. 篩選條件為：“采購數(shù)量”150，“折扣”0；采購數(shù)量折扣1500 b. 將篩選結(jié)果保存在sheet2中。6. 根據(jù)sheet1中的采購表，新建一個(gè)數(shù)據(jù)透視圖chart1，要求： a. 該圖形顯示每個(gè)采購時(shí)間點(diǎn)所采購的所有項(xiàng)目數(shù)量匯總情況； b. x坐標(biāo)設(shè)置為“采購時(shí)間”；c. 將對(duì)應(yīng)的數(shù)據(jù)透視表保存在sheet3中。透視圖chat1如下所示；sheet3中的透視表如下所示：求和項(xiàng):采購數(shù)量項(xiàng)目采購

8、時(shí)間褲子鞋子衣服總計(jì)2008-1-124570201352008-2-51851401254502008-3-142102602256952008-4-3035031538510502008-5-15120340254852008-6-241251002654902008-7-104001253208452008-8-192752403859002008-910-241552102956602008-11-4160275395830總計(jì)2350219528007345(三) 客戶（教材）(數(shù)組公式if).xls1. 使用數(shù)組公式，計(jì)算sheet1中的訂購

9、金額，將結(jié)果保存到表中的“金額”列當(dāng)中。=g2:g51*h2:h512. 使用統(tǒng)計(jì)函數(shù)，對(duì)sheet1中結(jié)果按以下條件進(jìn)行統(tǒng)計(jì)，并將結(jié)果保存在sheet1中的相應(yīng)位置，要求：a. 統(tǒng)計(jì)出版社名稱為“高等教育出版社”的書的種類數(shù)；=countif(d2:d51,高等教育出版社)或者：=dcount(a1:i51,7，k14:k15)其中k14:k15為自己構(gòu)建的條件區(qū)域。b. 統(tǒng)計(jì)訂購數(shù)量大于110且小于850的書的種類數(shù)。=countif(g2:g51,110)-countif(g2:g51,=850)或者：=dcount(a1:i51,7,l14:m15)條件區(qū)域如下：出版社訂數(shù)訂數(shù)高等教育

10、出版社1108503. 使用函數(shù)計(jì)算，每個(gè)用戶所訂購圖書所需支付的金額總數(shù)，將結(jié)果保存在sheet1中的相應(yīng)位置。=sumif(a$2:a$51,k8,i$2:i$51)4. 使用函數(shù)，判斷sheet2中的年份是否為閏年，如果是，結(jié)果保存“閏年”，如果不是，則結(jié)果保存“平年”，并將結(jié)果保存在“是否為閏年”列中。 說明：閏年定義：年數(shù)能被4整除而不能被100整除，或者能被400整除的年份。=if(mod(a2,400)=0,閏年,if(mod(a2,4)0,平年,if(mod(a2,100)0,閏年,平年)或者：=if(or(and(a2/4=trunc(a2/4),a2/100trunc(a2

11、/100),a2/400=trunc(a2/400),閏年,平年)5. 將sheet1復(fù)制到sheet3中，對(duì)sheet3進(jìn)行高級(jí)篩選，要求：a. 篩選條件為“訂數(shù)=500，且金額總數(shù)=500=40男性”。=if(d2=40,if(b2=男,true,false),false)4. 對(duì)sheet1中的數(shù)據(jù)，根據(jù)以下條件,利用函數(shù)進(jìn)行統(tǒng)計(jì)：a. 統(tǒng)計(jì)性別為“男”的用戶人數(shù)，將結(jié)果填入sheet2的b1單元格中；=countif(sheet1!b2:b37,男)b. 統(tǒng)計(jì)年齡為“40”歲的用戶人數(shù)，將結(jié)果填入sheet2的b2單元格中。=countif(sheet1!d2:d37,40)5. 將s

12、heet1復(fù)制到sheet3，并對(duì)sheet3進(jìn)行高級(jí)篩選。 a. 篩選條件為：“性別”女、“所在區(qū)域”西湖區(qū)；所在區(qū)域性 別西湖區(qū)女b. 將篩選結(jié)果保存在sheet3中。6. 根據(jù)sheet1的結(jié)果，創(chuàng)建一數(shù)據(jù)透視圖chart1，要求：a. 顯示每個(gè)區(qū)域所擁有的用戶數(shù)量；b. x坐標(biāo)設(shè)置為“所在區(qū)域”； c. 計(jì)數(shù)項(xiàng)為“所在區(qū)域”；d. 將對(duì)應(yīng)的數(shù)據(jù)透視表保存在sheet4中。透視圖chart1如下所示；數(shù)據(jù)透視表如下：計(jì)數(shù)項(xiàng):所在區(qū)域所在區(qū)域匯總拱墅區(qū)6江干區(qū)6上城區(qū)5西湖區(qū)6下城區(qū)6余杭區(qū)7總計(jì)36(五) 產(chǎn)品(數(shù)組公式if).xls1. 使用數(shù)組公式，計(jì)算sheet1中的每種產(chǎn)品的價(jià)值

13、，將結(jié)果保存 到表中的“價(jià)值”列中。 計(jì)算價(jià)值的計(jì)算方法為：“單價(jià)*每盒數(shù)量*采購盒數(shù)”。=e2:e17*f2:f17*g2:g172. 在sheet2中，利用數(shù)據(jù)庫函數(shù)及已設(shè)置的條件區(qū)域，計(jì)算以下情況的結(jié)果，并將結(jié)果保存相應(yīng)的單元格中。a. 計(jì)算：商標(biāo)為上海，瓦數(shù)小于100的白熾燈的平均單價(jià)；=daverage(a1:h17,e1,j2:l3)b. 計(jì)算：產(chǎn)品為白熾燈，其瓦數(shù)大于等于80且小于等于100的數(shù)量。=dsum(a1:h17,g1,j7:l8)3. 某公司對(duì)各個(gè)部門員工吸煙情況進(jìn)行統(tǒng)計(jì)，作為人力資源搭配的一個(gè)數(shù)據(jù)依據(jù)。對(duì)于調(diào)查對(duì)象，只能回答y（吸煙）或者n（不吸煙）。根據(jù)調(diào)查情況，

14、制做出sheet3。請(qǐng)使用函數(shù)，統(tǒng)計(jì)符合以下條件的數(shù)值。a. 統(tǒng)計(jì)未登記的部門個(gè)數(shù)；=countblank(b2:e11)b. 統(tǒng)計(jì)在登記的部門中，吸煙的部門個(gè)數(shù)。=countif(b2:e11,y)4. 使用函數(shù)，對(duì)sheet3中的b21單元格中的內(nèi)容進(jìn)行判斷，判斷其是否問文本，如果是，結(jié)果為“true”；如果不是，結(jié)果為“false”，并將結(jié)果保存在sheet3中的b22單元格當(dāng)中。=istext(b21)5. 將sheet1復(fù)制到sheet4中，對(duì)sheet4進(jìn)行高級(jí)篩選，要求：a. 篩選條件：“產(chǎn)品為白熾燈，商標(biāo)為上?！?，并將結(jié)果保存；產(chǎn)品商標(biāo)白熾燈上海b. 將結(jié)果保存在sheet4中

15、。6. 根據(jù)sheet1的結(jié)果，在sheet5中創(chuàng)建一張數(shù)據(jù)透視表，要求： a. 顯示不同商標(biāo)的不同產(chǎn)品的采購數(shù)量； b. 行區(qū)域設(shè)置為“產(chǎn)品”； c. 列區(qū)域設(shè)置為“商標(biāo)”；d. 計(jì)數(shù)項(xiàng)為“采購盒數(shù)”。數(shù)據(jù)透視表如下：計(jì)數(shù)項(xiàng):采購盒數(shù)商標(biāo)產(chǎn)品北京上海(空白)總計(jì)白熾燈459氖管112其他22日光燈22總計(jì)7815(六) 房產(chǎn)銷售表(數(shù)組公式).xls1. 利用公式，計(jì)算sheet1中的房價(jià)總額。房價(jià)總額的計(jì)算公式為：“面積*單價(jià)”=f3*g32. 使用數(shù)組公式，計(jì)算sheet1中的契稅總額。 契稅總額的計(jì)算公式為：“契稅*房價(jià)總額”=h3:h26*i3:i263. 使用函數(shù)，根據(jù)sheet1

16、中的結(jié)果，統(tǒng)計(jì)每個(gè)銷售人員的銷售總額，將結(jié)果保存在sheet2中的相應(yīng)的單元格中。人員甲：=sumif(sheet1!k3:k26,a2,sheet1!i3:i26)然后利用填充柄復(fù)制公式4. 使用rank函數(shù)，根據(jù)sheet2的結(jié)果，對(duì)每個(gè)銷售人員的銷售情況 進(jìn)行排序，并將結(jié)果保存在“排名”列當(dāng)中。人員甲：=rank(b2,b$2:b$6)然后利用填充柄復(fù)制公式5. 將sheet1復(fù)制到sheet3中，并對(duì)sheet3進(jìn)行高級(jí)篩選，要求：a. 篩選條件為：“戶型”為兩室一廳，“房價(jià)總額”1000000；戶型房價(jià)總額兩室一廳1000000 b. 將結(jié)果保存在sheet3中。6. 根據(jù)sheet

17、1的結(jié)果，創(chuàng)建一張數(shù)據(jù)透視圖chart1，要求； a. 顯示每個(gè)銷售人員銷售房屋所繳納契稅總額； b. 行區(qū)域設(shè)置為“銷售人員”； c. 計(jì)數(shù)項(xiàng)設(shè)置為契稅總額；d. 將對(duì)應(yīng)的數(shù)據(jù)透視表保存在sheet4中。數(shù)據(jù)透視圖chart1如下所示:數(shù)據(jù)透視表如下：求和項(xiàng):契稅總額銷售人員匯總?cè)藛T丙199857.4008人員丁59564.1012人員甲244122.8748人員戊147790.5024人員乙86253.5637總計(jì)737588.4429(七) 公務(wù)員考試成績表(if函數(shù)).xls1. 使用if函數(shù)，對(duì)sheet1中的“學(xué)位”列進(jìn)行自動(dòng)填充。 要求：填充的內(nèi)容根據(jù)“學(xué)歷”列的內(nèi)容來確定（假定

18、學(xué)生均已獲得相應(yīng)學(xué)位）： - 博士研究生博士 - 碩士研究生碩士 - 本科學(xué)士 - 其他無=if(g3=博士研究生,博士,if(g3=碩士研究生,碩士,if(g3=本科,學(xué)士,無)2. 使用數(shù)組公式，在sheet1中計(jì)算： a.“筆試比例分”，計(jì)算方法為：（筆試成績/3）*60 =i3:i18/3*0.6 b.“面試比例分”，計(jì)算方法為：面試成績*40 =k3:k18*0.4 c.“總成績”，計(jì)算方法為：筆試比例分+面試比例分 =j3:j18+l3:l183. 修改數(shù)組公式，將sheet1復(fù)制到sheet2，在sheet2中計(jì)算：要求：修改“筆試比例分”的計(jì)算，計(jì)算方法為：（筆試成績/2）*6

19、0）。=i3:i18/2*0.64. 在 sheet2中，添加一列，將其命名為“排名”。要求：使用rank函數(shù)，根據(jù)“總成績”對(duì)所有考生排名。=rank(m3,m$3:m$18)5. 將sheet2復(fù)制到sheet3，并對(duì)sheet3進(jìn)行高級(jí)篩選。a. 篩選條件為：“報(bào)考單位”中院、“性別”男、“學(xué)歷”碩士研究生報(bào)考單位性別學(xué)歷一中院男碩士研究生三中院男碩士研究生 b. 將篩選結(jié)果保存在sheet3中6. 根據(jù)sheet2，在sheet4中新建一數(shù)據(jù)透視表。要求： a. 顯示每個(gè)報(bào)考單位的人的不同學(xué)歷的總?cè)藬?shù) b. 行區(qū)域設(shè)置為“報(bào)考單位” c. 列區(qū)域設(shè)置為“學(xué)歷” d. 數(shù)據(jù)區(qū)域設(shè)置為“學(xué)

20、歷”e. 計(jì)數(shù)項(xiàng)為學(xué)歷數(shù)據(jù)透視表如下：計(jì)數(shù)項(xiàng):學(xué)歷學(xué)歷報(bào)考單位本科博士研究生大專碩士研究生總計(jì)區(qū)法院314三中院33市高院11125一中院134總計(jì)722516(八) 員工姓名(replace函數(shù)).xls1. 使用replace函數(shù)，對(duì)sheet1中的員工代碼進(jìn)行升級(jí)，要求： a. 升級(jí)方法：在pa后面加上0； b. 將升級(jí)后的員工代碼結(jié)果填入表中的“升級(jí)員工代碼”列中。=replace(b2,3,4,0&right(b2,3)2. 使用時(shí)間函數(shù)，對(duì)sheet1員工的“年齡”和“工齡”進(jìn)行計(jì)算，并將結(jié)果填入到表中的“年齡”列和“工齡”列中。年齡：=year(today()-year(e2)

21、工齡：=year(today()-year(g2)3. 使用統(tǒng)計(jì)函數(shù)，對(duì)sheet1中的數(shù)據(jù)，根據(jù)以下統(tǒng)計(jì)條件進(jìn)行如下統(tǒng)計(jì)。 a. 統(tǒng)計(jì)男性員工的人數(shù)，結(jié)果填入n3單元格中；countif(d2:d65,=男) b. 統(tǒng)計(jì)高級(jí)工程師人數(shù)，結(jié)果填入n4單元格中；countif(i2:i65,=高級(jí)工程師) c. 統(tǒng)計(jì)工齡大于等于10的人數(shù)，果填入n5單元格中。countif(h2:h65,=10)4. 使用邏輯函數(shù)，判斷員工是否有資格評(píng)“高級(jí)工程師”。 評(píng)選條件為：工齡大于20，且為工程師的員工。=if(i2=工程師,if(h220,true,false),false)=if(and(h220,

22、i2=工程師),是,否)5. 將sheet1復(fù)制到sheet2中，并對(duì)sheet2進(jìn)行高級(jí)篩選，要求： a. 篩選條件為：“性別”男，“年齡”30，“工齡”10，“職稱”助工；性別年齡工齡職稱男30=10助工 b. 將結(jié)果保存在sheet2中。6. 根據(jù)sheet1中的數(shù)據(jù)，創(chuàng)建一張數(shù)據(jù)透視圖chart1，要求： a. 顯示工廠中各個(gè)職稱的人數(shù)； b. x坐標(biāo)設(shè)置為“職稱”； c. 計(jì)數(shù)項(xiàng)為職稱； d. 將對(duì)應(yīng)的數(shù)據(jù)透視表保存在sheet3中。數(shù)據(jù)透視圖chart1如下：數(shù)據(jù)透視表如下：計(jì)數(shù)項(xiàng):職稱職稱匯總高級(jí)工2高級(jí)工程師14工程師11技工5技師1技術(shù)員11中級(jí)工1助工19總計(jì)64(九) 停

23、車價(jià)目表(hlookup函數(shù)).xls1. 使用hlookup函數(shù)，對(duì)sheet1中的停車單價(jià)進(jìn)行自動(dòng)填充。 要求：根據(jù)sheet1中的“停車價(jià)目表”價(jià)格，利用hlookup函數(shù)對(duì)“停車情況記錄表”中的“單價(jià)”列根據(jù)不同的車型進(jìn)行自動(dòng)填充。=hlookup(b9,a$2:c$3,2,false)或者用數(shù)組公式：=hlookup($b$9:$b$39,$a$2:$c$3,2,0)2. 在sheet1中，利用時(shí)間函數(shù)計(jì)算汽車在停車庫中的停放時(shí)間，要求： a. 公式計(jì)算方法為“出庫時(shí)間-入庫時(shí)間” b. 格式為：“小時(shí)：分鐘：秒” （例如：一小時(shí)十五分十二秒在停放時(shí)間中的表示為：“1：15：12”）

24、=e9-d93. 使用函數(shù)公式，計(jì)算停車費(fèi)用，要求： 根據(jù)停放時(shí)間的長短計(jì)算停車費(fèi)用，將計(jì)算結(jié)果填入到“應(yīng)付金額”列中。 注意： a. 停車按小時(shí)收費(fèi)，對(duì)于不滿一個(gè)小時(shí)的按照一個(gè)小時(shí)計(jì)費(fèi)； b. 對(duì)于超過整點(diǎn)小時(shí)數(shù)十五分鐘的多累積一個(gè)小時(shí)。 （例如1小時(shí)23分，將以2小時(shí)計(jì)費(fèi)）=if(and(hour(f9)=0,minute(f9)0),1*c9,if(minute(f9)15,(hour(f9)+1)*c9,hour(f9)*c9)4. 使用統(tǒng)計(jì)函數(shù)，對(duì)sheet1中的“停車情況記錄表”根據(jù)下列條件進(jìn)行統(tǒng)計(jì)，要求： a. 統(tǒng)計(jì)停車費(fèi)用大于等于40元的停車記錄條數(shù) =countif(g9:g

25、39,=40) b. 統(tǒng)計(jì)最高的停車費(fèi)用 = max(g9:g39)5. 對(duì)sheet2，進(jìn)行高級(jí)篩選，要求： a. 篩選條件為：“車型”小汽車，“應(yīng)付金額”30； b. 將結(jié)果保存在sheet2中。車型應(yīng)付金額小汽車=306. 根據(jù)sheet1，創(chuàng)建一個(gè)數(shù)據(jù)透視圖chart1，要求： a. 顯示各種車型所收費(fèi)用的匯總； b. 行區(qū)域設(shè)置為“車型”； c. 計(jì)數(shù)項(xiàng)為“應(yīng)付金額”； d. 將對(duì)應(yīng)的數(shù)據(jù)透視表保存在sheet3中。數(shù)據(jù)透視圖chart1如下：數(shù)據(jù)透視表如下：求和項(xiàng):應(yīng)付金額車型匯總大客車200小汽車155中客車264總計(jì)619(十) 平均氣溫日期(if函數(shù)).xls1. 使用if函

26、數(shù)，對(duì)sheet1中的“溫度較高的城市”列進(jìn)行自動(dòng)填充。=if(b2c2,杭州,上海)2. 使用數(shù)組公式，對(duì)sheet1中的相差溫度值（杭州相對(duì)于上海的溫差）進(jìn)行填充。=b2:b16-c2:c163. 利用函數(shù)，根據(jù)sheet1中的結(jié)果，符合以下條件的進(jìn)行統(tǒng)計(jì)。 a. 杭州這半個(gè)月以來的最高氣溫和最低氣溫；=max(b2:b16) =min(b3:b17) b. 上海這半個(gè)月以來的最高氣溫和最低氣溫。=max(c2:c16) = min(c3:c17)4. 將sheet1復(fù)制到sheet2中，在sheet2中，重新編輯數(shù)組公式，將sheet2中的“相差的溫度值”中的數(shù)值取其絕對(duì)值（均為正數(shù)）。

27、=abs(b2:b16-c2:c16)5. 將sheet2復(fù)制到sheet3中，并對(duì)sheet3進(jìn)行高級(jí)篩選，要求： 篩選條件：“杭州平均氣溫”=20，“上海平均氣溫”=20 2009001=replace(a3,1,0,2009)2. 使用數(shù)組公式，對(duì)sheet1中“學(xué)生成績表”的“總分”列進(jìn)行計(jì)算。 計(jì)算方法：總分 =語文 + 數(shù)學(xué) + 英語 + 信息技術(shù) + 體育=e3:e24+f3:f24+g3:g24+h3:h24+i3:i243. 使用if函數(shù)，根據(jù)以下條件，對(duì)sheet1中“學(xué)生成績表”的“考評(píng)”列進(jìn)行計(jì)算。 條件：如果總分=350，填充為“合格”；否則，填充為“不合格”。=if

28、(j3=350,合格,不合格)4. 在sheet1中，利用數(shù)據(jù)庫函數(shù)及已設(shè)置的條件區(qū)域，根據(jù)以下情況計(jì)算，并將結(jié)果填入到相應(yīng)的單元格當(dāng)中。 條件： （a）計(jì)算：“語文”和“數(shù)學(xué)”成績都大于或等于85的學(xué)生人數(shù)；=dcounta(a2:k24,b2,m2:n3) （b）計(jì)算：“體育”成績大于或等于90的“女生”姓名；=dget(a2:k24,c2,m7:n8) （c）計(jì)算：“體育”成績中男生的平均分；=daverage(a2:k24,i2,m12:m13) （d）計(jì)算：“體育”成績中男生的最高分。=dmax(a2:k24,i2,m12:m13)5. 將sheet1中的“學(xué)生成績表”復(fù)制到shee

29、t2當(dāng)中，并對(duì)sheet2進(jìn)行高級(jí)篩選。 要求： （a）篩選條件為：“性別” - 男；“英語” - 90；“信息技術(shù)” - 95； （b）將篩選結(jié)果保存在sheet2中。性別英語信息技術(shù)男9095注意： （a）無需考慮是否刪除或移動(dòng)篩選條件; （b）復(fù)制過程中，將標(biāo)題項(xiàng)“學(xué)生成績表”連同數(shù)據(jù)一同復(fù)制; （c）復(fù)制數(shù)據(jù)表后，粘貼時(shí)，數(shù)據(jù)表必須頂格放置。6. 根據(jù)sheet1中“學(xué)生成績表”，在sheet3中新建一張數(shù)據(jù)透視表。 要求： （a）顯示不同性別、不同考評(píng)結(jié)果的學(xué)生人數(shù)情況； （b）行區(qū)域設(shè)置為“性別”； （c）列區(qū)域設(shè)置為“考評(píng)”； （d）數(shù)據(jù)區(qū)域設(shè)置為“考評(píng)”； （e）計(jì)數(shù)項(xiàng)為“考評(píng)

30、”。數(shù)據(jù)透視表；計(jì)數(shù)項(xiàng):考評(píng)考評(píng)性別不合格合格總計(jì)男31215女167總計(jì)41822(十二) 銷售統(tǒng)計(jì)表.xls1. 使用vlookup函數(shù)， 對(duì)sheet1中的“3月份銷售統(tǒng)計(jì)表”的“產(chǎn)品名稱”列和“產(chǎn)品單價(jià)”列進(jìn)行填充。 要求：根據(jù)“企業(yè)銷售產(chǎn)品清單”，使用vlookup函數(shù)，將產(chǎn)品名稱和產(chǎn)品單價(jià)填充到“3月份銷售統(tǒng)計(jì)表”的“產(chǎn)品名稱”列和“產(chǎn)品單價(jià)”列中。“產(chǎn)品名稱”列；=vlookup(f3,$a$2:$c$10,2,false)“產(chǎn)品單價(jià)”列：=vlookup(f3,$a$2:$c$10,3,false)2. 使用數(shù)組公式，計(jì)算sheet1中的“3月份銷售統(tǒng)計(jì)表”中的銷售金額，并將結(jié)

31、果填入到該表的“銷售金額”列中。計(jì)算方法：銷售金額 = 產(chǎn)品單價(jià) * 銷售數(shù)量=h3:h44*i3:i443. 使用統(tǒng)計(jì)函數(shù)，根據(jù)“3月份銷售統(tǒng)計(jì)表”中的數(shù)據(jù)，計(jì)算“分部銷售業(yè)績統(tǒng)計(jì)表”中的總銷售額，并將結(jié)果填入該表的“總銷售額”列。=sumif($k$3:$k$44,n3,$l$3:$l$44)4. 在sheet1中，使用rank函數(shù)，在“分部銷售業(yè)績統(tǒng)計(jì)”表中，根據(jù)“總銷售額”對(duì)各部門進(jìn)行排名，并將結(jié)果填入到“銷售排名”列中。=rank(o3,$o$3:$o$5)5. 將sheet1中的“三月份銷售統(tǒng)計(jì)表”復(fù)制到sheet2中，對(duì)sheet2進(jìn)行高級(jí)篩選。 要求： （a）篩選條件為：“銷售

32、數(shù)量”3、“所屬部門”市場(chǎng)1部、“銷售金額”-1000 （b）將篩選結(jié)果保存在sheet2中。銷售數(shù)量所屬部門銷售金額3市場(chǎng)1部1000 注意： （a）無需考慮是否刪除或移動(dòng)篩選條件; （b）復(fù)制過程中，將標(biāo)題項(xiàng)“三月份銷售統(tǒng)計(jì)表”連同數(shù)據(jù)一同復(fù)制; （c）復(fù)制數(shù)據(jù)表后，粘貼時(shí)，數(shù)據(jù)表必須頂格放置。6. 根據(jù)sheet1的“3月份銷售統(tǒng)計(jì)表”中的數(shù)據(jù)，新建一個(gè)數(shù)據(jù)透視圖chart1。 要求： （a）該圖形顯示每位經(jīng)辦人的總銷售額情況 （b）x坐標(biāo)設(shè)置為“經(jīng)辦人”; （c）數(shù)據(jù)區(qū)域設(shè)置為“銷售金額”; （d）求和項(xiàng)為銷售金額; （e）將對(duì)應(yīng)的數(shù)據(jù)透視表保存在sheet3中。數(shù)據(jù)透視圖chart1

33、：數(shù)據(jù)透視表：求和項(xiàng):銷售金額經(jīng)辦人匯總甘倩琦5752李成蹊4416劉 惠18232孫國成6920王 勇3976吳 仕12116吳小平3424許 丹11352趙 榮6436總計(jì)72624(十三) 學(xué)生成績表.xls1. 使用數(shù)組公式，根據(jù)sheet1中“學(xué)生成績表”的數(shù)據(jù)，計(jì)算考試總分， 并將結(jié)果填入到“總分”列中。 計(jì)算方法：總分 = 單選題 + 判斷題 + windows操作題 + excel操作題 + powerpoint操作題 + ie操作題=d3:d57+e3:e57+f3:f57+g3:g57+h3:h57+i3:i572. 使用文本函數(shù)中的一個(gè)函數(shù)，在sheet1中，利用“學(xué)號(hào)”

34、列的數(shù)據(jù)，根據(jù)以下要求獲得考生所考級(jí)別，并將結(jié)果填入“級(jí)別”列中。 要求： （a）學(xué)號(hào)中的第八位指示的考生所考級(jí)別，例如：“085200821023080”中的“2”標(biāo)識(shí)了該考生所考級(jí)別為二級(jí) （b）在“級(jí)別”列中，填入的數(shù)據(jù)是函數(shù)的返回值。=mid(a3,8,1)3. 使用統(tǒng)計(jì)函數(shù)，根據(jù)以下要求對(duì)sheet1中“學(xué)生成績表”的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)。 要求： （a）統(tǒng)計(jì)“考1級(jí)的考生人數(shù)”，并將計(jì)算結(jié)果填入到n2單元格中；=countif(c3:c57,1) （b）統(tǒng)計(jì)“考試通過人數(shù)（=60）”,并將計(jì)算結(jié)果填入到n3單元格中；=countif(j3:j57,=60) （c）統(tǒng)計(jì)“全體1級(jí)考生的考試平

35、均分”，并將計(jì)算結(jié)果填入到n4單元格中。（其中，計(jì)算時(shí)候的分母直接使用“n2”單元格的數(shù)據(jù)）=sumif(c3:c57,1,j3:j57)/n24. 使用財(cái)務(wù)函數(shù)，根據(jù)以下要求對(duì)sheet2中的數(shù)據(jù)進(jìn)行計(jì)算。 要求： （a）根據(jù)“投資情況表1”中的數(shù)據(jù)，計(jì)算10年以后得到的金額，并將結(jié)果填入到b7單元格中；=fv(b3,b5,b4,b2) （b）根據(jù)“投資情況表2”中的數(shù)據(jù)，計(jì)算預(yù)計(jì)投資金額，并將結(jié)果填入到e7單元格中。=pv(e3,e4,e2)5. 將sheet1中的“學(xué)生成績表”復(fù)制到sheet3，并對(duì)sheet3進(jìn)行高級(jí)篩選。 要求： （a）篩選條件為：“級(jí)別”2、“總分”=70 ； （

36、b）將篩選結(jié)果保存在sheet3中。級(jí)別總分2=70 注意： （a）無需考慮是否刪除或移動(dòng)篩選條件; （b）復(fù)制過程中，將標(biāo)題項(xiàng)“學(xué)生成績表”連同數(shù)據(jù)一同復(fù)制; （c）復(fù)制數(shù)據(jù)表后，粘貼時(shí)，數(shù)據(jù)表必須頂格放置。6. 根據(jù)sheet1中的“學(xué)生成績表”，在sheet4中新建一張數(shù)據(jù)透視表。 要求： （a）顯示每個(gè)級(jí)別不同總分的人數(shù)匯總情況； （b）行區(qū)域設(shè)置為“級(jí)別”； （c）列區(qū)域設(shè)置為“總分”； （d）數(shù)據(jù)區(qū)域設(shè)置為“總分”； （e）計(jì)數(shù)項(xiàng)為總分。計(jì)數(shù)項(xiàng):總分總分級(jí)別39454849515255575860626364656667686970717273747578828393總計(jì)11112

37、11111131321113312113421111231111311112111110總計(jì)211121111133415234114142211155二、aoa excel 考試題用到的函數(shù)參考1排名函數(shù)：rank（被排名的單元，排名的范圍_絕對(duì)引用，0降序、非0 升序）rank(g2,$g$2:$g$39,0)2判斷函數(shù)：if（條件，真取值，假取值）3 數(shù)據(jù)庫計(jì)數(shù)函數(shù)：dcount（數(shù)據(jù)庫范圍，被計(jì)數(shù)列，放條件的區(qū)域）dcount(sheet1!a1:i39,sheet1!d1,b10:c11)4縱向?qū)φ毡聿檎也⑻钪岛瘮?shù)：vlookup（被對(duì)照的區(qū)域，對(duì)照的表格區(qū)域，填對(duì)照表格2列，0）v

38、lookup($a$11:$a$43,$f$2:$g$4,2,0)5. 橫向?qū)φ毡聿檎也⑻钪岛瘮?shù)：hlookup（被對(duì)照的區(qū)域，對(duì)照的表格區(qū)域，填對(duì)照表格2行，0）hlookup($b$9:$b$39,$a$2:$c$3,2,0)6. 條件求和函數(shù)：sumif(放各種品種的區(qū)域，指定求和的品種，被求和的區(qū)域)sumif(a11:a43,”=衣服“,b11:b43)7. 取日期的年份函數(shù)：year(日期) 當(dāng)天日期函數(shù)：today()year(today()-year(c2)8. 字符串替換函數(shù)：replace(被替換的單元，第幾個(gè)字符開始，共幾個(gè)，新字符串)replace(f2,5,8,8&r

39、ight(f2,7)9取字符串函數(shù)：right(被取的單元，右起取幾個(gè)) left(被取的單元，左起取幾個(gè)) right(f2,7)10. 條件計(jì)數(shù)函數(shù)：countif(被計(jì)數(shù)的區(qū)域范圍,條件)countif(sheet1!$b$2:$b$37,=男)11.數(shù)據(jù)庫中求平均值函數(shù)：daverage(數(shù)據(jù)庫范圍，被求平均值的列，放條件的區(qū)域)daverage(a1:g17,e1,j2:l3)12.計(jì)數(shù)空白單元格個(gè)數(shù)函數(shù)：countblank(被計(jì)數(shù)區(qū)域范圍) countblank(b2:e11)13.判定是否文本單元格的函數(shù)： istext(單元格)if(istext(c21),true,fals

40、e)14.橫向?qū)φ毡聿檎也⑻钪岛瘮?shù)：hlookup（被對(duì)照的區(qū)域，對(duì)照的表格區(qū)域，填對(duì)照表格2行，0）hlookup($b$9:$b$39,$a$2:$c$3,2,0)15.財(cái)務(wù)函數(shù)：pmt 貸了多少款，年利息是多少，貸多少年，等額分期按年償還貸款金額（年末）功能：基于固定利率及等額分期付款方式，返回貸款的每期付款額 格式：pmt(rate，nper，pv，fv，type)rate：貸款利率（年利息）nper：該項(xiàng)貸款的總貸款期限或者總投資期（貸款年限）pv：從該項(xiàng)貸款（或投資）開始計(jì)算時(shí)已經(jīng)入賬的款項(xiàng)（貸款金額）fv：未來值，或在最后一次付款后希望得到的現(xiàn)金余額，如果忽略該值，將自動(dòng)默認(rèn)為0

41、type：一個(gè)邏輯值，用以指定付款時(shí)間是在期初還是在期末，1表示期初，0表示期末例：按年償還貸款金額（年末） =pmt(b4,b3,b2,0,0)ab1貸款情況2貸款金額：10000003貸款年限：154年利息：4.98%16. 財(cái)務(wù)函數(shù)：ipmt 貸了多少款，年利息是多少，貸多少年，等額分期按年償還貸款后，利息逐月減少，求某月所交的利息。功能：基于固定利率及等額分期付款方式，返回投資或貸款在某一給定期限內(nèi)的利息償還額 格式：ipmt(rate，per，nper，pv，fv)rate：各期利率 （月利息，年利息/12）per：用于計(jì)算利息數(shù)額的期數(shù)，介于1nper之間 （第9月）nper：總投

42、資（或貸款）期，即該項(xiàng)投資（或貸款）的付款期總數(shù) （年數(shù)*12月）pv：從該項(xiàng)投資（或貸款）開始計(jì)算時(shí)已經(jīng)入賬的款項(xiàng)（貸款金額） fv：未來值，或在最后一次付款后希望得到的現(xiàn)金余額，如果忽略該值，將自動(dòng)默認(rèn)為0例：第9月貸款利息金額 =ipmt(b4/12,9,b3*12,b2,0)17財(cái)務(wù)函數(shù)：fv 先投一筆錢，每年再投一筆錢，有年利率回報(bào)，多少年以后的總金額。功能：基于固定利率及等額分期付款方式，返回某項(xiàng)投資的未來值格式：fv (rate，nper，pmt，pv，type)rate：各期利率（年利率）nper：總投資（或貸款）期，即該項(xiàng)投資（或貸款）的付款期總數(shù)（再投資年限）pmt：各期所

43、應(yīng)支付的金額（每年再投資金額）pv：現(xiàn)值，即從該項(xiàng)投資開始計(jì)算時(shí)已經(jīng)入賬的款項(xiàng)，也稱為本金 （先投資金額）type：一個(gè)邏輯值，用以指定付款時(shí)間是在期初還是在期末，1表示期初，0表示期末。例：計(jì)算10年以后得到的金額： =fv(b3,b5,b4,b2,0)ab1投資情況表12先投資金額：-10000003年利率：5%4每年再投資金額：-100005再投資年限：106710年以后得到的金額：18財(cái)務(wù)函數(shù)：pv 每年投一筆錢，有年利率回報(bào)，多少年以后預(yù)計(jì)投資總金額。功能：一系列未來付款的當(dāng)前值的累積和，返回的是投資現(xiàn)值 格式：pv(rate，nper，pmt，fv，type)rate：貸款利率（年

44、利率）nper：該項(xiàng)貸款的總貸款期限或者總投資期 （年限）pmt：各期所應(yīng)支付的金額 （每年投資金額）fv：未來值，或在最后一次付款后希望得到的現(xiàn)金余額，如果忽略該值，將自動(dòng)默認(rèn)為0 type：一個(gè)邏輯值，用以指定付款時(shí)間是在期初還是在期末，1表示期初，0表示期末例：計(jì)算預(yù)計(jì)投資金額 =pv(e3,e4,e2,0,0)de1投資情況表22每年投資金額：-15000003年利率：10%4年限：20567預(yù)計(jì)投資金額：三、aoa excel 考試題用到的數(shù)據(jù)分析參考1 數(shù)組公式： 數(shù)組包含數(shù)個(gè)單元格，這些單元格形成一個(gè)整體范圍，對(duì)應(yīng)同一個(gè)不變公式運(yùn)算 與填充柄的區(qū)別是：填充柄的公式隨著單元格的變化而變化。2. 分類匯總：按不同的項(xiàng)目匯總對(duì)分類匯總的列按項(xiàng)目先排序。 “分類匯總”