AOA考試寶典(Excel)

1、省二級AOA考試 寶典 AOA Excel 考試題（對照）參考(一) 學(xué)號(數(shù)組公式if).xls1. 使用數(shù)組公式，對Sheet1計算總分和平均分，將其計算結(jié)果保存到表中的“總分”列和“平均分”列當(dāng)中?？偡郑?C2:C39+D2:D39+E2:E39 平均分：=F2:F39/3（注意，題目明確要求用數(shù)組公式，就必須用數(shù)組公式，否則不得分。數(shù)組公式中的一對不是人為錄入的，必須用Ctrl+Shift+Enter組合健輸入。）數(shù)組公式的解讀：1利用F9鍵：在編輯中，用鼠標(biāo)選中需要進(jìn)行計算的某段公式，將其抹黑，然后按住F9，就得到了公式得及算結(jié)果。當(dāng)你對公式利用F9計算結(jié)果后，返回時要按ESC或者點

2、擊編輯欄左側(cè)的 取消 鍵。2. 使用RANK函數(shù)，對Sheet1中的每個同學(xué)排名情況進(jìn)行統(tǒng)計，并將排名結(jié)果保存到表中的“排名”列當(dāng)中。排名：=RANK(G2,G$2:G$39) 或者 =RANK(F2,F$2:F$39) 然后利用填充柄復(fù)制公式3. 使用邏輯函數(shù)判斷Sheet1中每個同學(xué)的每門功課是否均高于平均分，如果是，保存結(jié)果為TRUE，否則，保存結(jié)果為FALSE，將結(jié)果保存在表中的“三科成績是否均超過平均”列當(dāng)中。=IF(AND(C2>AVERAGE($G$2:$G$39),D2>AVERAGE($G$2:$G$39),E2>AVERAGE($G$2:$G$39),TR

3、UE,FALSE)或者：=IF(C2>AVERAGE($C$2:$C$39),IF(D2>AVERAGE($D$2:$D$39),IF(E2>AVERAGE($E$2:$E$39),TRUE,FALSE),FALSE),FALSE)4. 根據(jù)Sheet1中的結(jié)果，使用統(tǒng)計函數(shù)，統(tǒng)計“數(shù)學(xué)”考試成績各個分?jǐn)?shù)段的同學(xué)人數(shù)，將統(tǒng)計結(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,"

4、<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:$D$39,"<80")-COUNTIF(Sheet1!$D$2:$D$39,"<60")數(shù)學(xué)分?jǐn)?shù)位于80到100分的人數(shù)：=COUNTIF

5、(Sheet1!$D$2:$D$39,"<100")-COUNTIF(Sheet1!$D$2:$D$39,"<80")或者：利用數(shù)據(jù)庫函數(shù)Dcount，如下所示，在sheet2表中自己構(gòu)建條件區(qū)間，如；B10:C11。=DCOUNT(Sheet1!A1:I39,Sheet1!D1, B10:C11)數(shù)學(xué)數(shù)學(xué)數(shù)學(xué)數(shù)學(xué)數(shù)學(xué)數(shù)學(xué)數(shù)學(xué)數(shù)學(xué)數(shù)學(xué)數(shù)學(xué)>=0<20>=20<40>=40<60>=60<80>=80<=1005. 將Sheet1復(fù)制到Sheet3中，并對Sheet3進(jìn)行高級篩選，要求

6、： a. 篩選條件：“語文”>75，“數(shù)學(xué)”>75，“英語”>75，“總分”>250；篩選的條件區(qū)間如下：語文數(shù)學(xué)英語總分>=75>=75>=75>=250 b. 將結(jié)果保存在Sheet3中。注：（a）無需考慮是否刪除或移動篩選條件; （b）復(fù)制過程中，將標(biāo)題項“學(xué)生成績表”連同數(shù)據(jù)一同復(fù)制; （c）復(fù)制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。6. 根據(jù)Sheet1中的結(jié)果，在Sheet4中創(chuàng)建一張數(shù)據(jù)透視表，要求： a. 顯示是否三科均超過平均分的學(xué)生人數(shù)； b. 行區(qū)域設(shè)置為：“三科成績是否均超過平均”； c. 計數(shù)項為三科成績是否均超過平均。

7、完成的數(shù)據(jù)透視表應(yīng)如下所示。計數(shù)項:三科成績是否均超過平均三科成績是否均超過平均匯總FALSE27TRUE11總計38(二) 折扣表(采購表)(VLOOKUP函數(shù)).xls1. 使用VLOOKUP函數(shù)，對Sheet1中的商品單價進(jìn)行自動填充。 要求：根據(jù)“價格表”中的商品單價，利用VLOOKUP函數(shù)，將其單價自動填充到采購表中的“單價”列中。=VLOOKUP(A11,F$2:G$5,2,0)或者用數(shù)組公式做：=VLOOKUP($A$11:$A$43,$F$2:$G$5,2,0)2. 使用邏輯函數(shù)，對Sheet1中的商品折扣率進(jìn)行自動填充。要求：根據(jù)“折扣表”中的商品折扣率，利用相應(yīng)的函數(shù)，將其

8、折扣率自動填充到采購表中的“折扣“列中。=IF(B11>=A$6,B$6,IF(B11>=A$5,B$5,IF(B11>=A$4,B$4,B$3)3. 利用公式，計算Sheet1中的“合計金額”。 要求：根據(jù)“采購數(shù)量”，“單價”和“折扣”，計算采購的“合計金額”。計算公式：單價*采購數(shù)量*（1-折扣）=D11*B11*(1-E11)4. 使用SUMIF函數(shù)，統(tǒng)計各種商品的采購總量和采購總金額，將結(jié)果保存在Sheet1中的“統(tǒng)計表”當(dāng)中。采購總量：=SUMIF(A$11:A$43,I12,B$11:B$43)采購總金額：=SUMIF(A$11:A$43,I12,F$11:F$

9、43)5. 對Sheet2中的“采購表”進(jìn)行高級篩選。a. 篩選條件為：“采購數(shù)量”>150，“折扣”>0；采購數(shù)量折扣>150>0 b. 將篩選結(jié)果保存在Sheet2中。6. 根據(jù)Sheet1中的采購表，新建一個數(shù)據(jù)透視圖Chart1，要求： a. 該圖形顯示每個采購時間點所采購的所有項目數(shù)量匯總情況； b. x坐標(biāo)設(shè)置為“采購時間”；c. 將對應(yīng)的數(shù)據(jù)透視表保存在Sheet3中。透視圖chat1如下所示；Sheet3中的透視表如下所示：求和項:采購數(shù)量項目采購時間褲子鞋子衣服總計2008-1-124570201352008-2-51851401254502008-3

10、-142102602256952008-4-3035031538510502008-5-15120340254852008-6-241251002654902008-7-104001253208452008-8-192752403859002008-910-241552102956602008-11-4160275395830總計2350219528007345(三) 客戶（教材）(數(shù)組公式if).xls1. 使用數(shù)組公式，計算Sheet1中的訂購金額，將結(jié)果保存到表中的“金額”列當(dāng)中。=G2:G51*H2:H512. 使用統(tǒng)計函數(shù)，對Sheet1中結(jié)果

11、按以下條件進(jìn)行統(tǒng)計，并將結(jié)果保存在Sheet1中的相應(yīng)位置，要求：a. 統(tǒng)計出版社名稱為“高等教育出版社”的書的種類數(shù)；=COUNTIF(D2:D51,"高等教育出版社")或者：=DCOUNT(A1:I51,7，K14:K15)其中K14:K15為自己構(gòu)建的條件區(qū)域。b. 統(tǒng)計訂購數(shù)量大于110且小于850的書的種類數(shù)。=COUNTIF(G2:G51,">110")-COUNTIF(G2:G51,">=850")或者：=DCOUNT(A1:I51,7,L14:M15)條件區(qū)域如下：出版社訂數(shù)訂數(shù)高等教育出版社>110

12、<8503. 使用函數(shù)計算，每個用戶所訂購圖書所需支付的金額總數(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,"閏年"

13、;,"平年")或者：=IF(OR(AND(A2/4=TRUNC(A2/4),A2/100<>TRUNC(A2/100),A2/400=TRUNC(A2/400),"閏年","平年")5. 將Sheet1復(fù)制到Sheet3中，對Sheet3進(jìn)行高級篩選，要求：a. 篩選條件為“訂數(shù)>=500，且金額總數(shù)<=30000”；訂數(shù)金額>=500<=30000 b. 將結(jié)果保存在Sheet2中。6. 根據(jù)Sheet1中的結(jié)果，在Sheet4中新建一張數(shù)據(jù)透視表，要求： a. 顯示每個客戶在每個出版社所訂的教材

14、數(shù)目； b. 行區(qū)域設(shè)置為：“出版社”； c. 列區(qū)域設(shè)置為：“客戶”；d. 計數(shù)項為訂數(shù)。透視表如下：求和項:訂數(shù)客戶出版社c1c2c3c4總計北京航大6363北京理工421421電子工業(yè)出版社55571626東北財經(jīng)大學(xué)出版社7575復(fù)旦大學(xué)106106高等教育10611061高等教育出版社1071910719高教509509華東師大7676科學(xué)203203科學(xué)出版社29402940立信會計637637立信會計出版社8080遼寧美術(shù)出版社5858南京大學(xué)240240清華大學(xué)120120人民大學(xué)721721人民衛(wèi)生366366上海外語教育出版社500500天津人民美術(shù)出版社5858外語教學(xué)與

15、研究出版社98559855浙江科技出版社15041504浙江科學(xué)技術(shù)106106浙江科學(xué)技術(shù)出版社500500中國金融160160中國金融出版社645645中國人大224224中國人民大學(xué)出版社585167752中國物資109109總計2715819651968234333434(四) 姓名(時間函數(shù)).xls1. 使用時間函數(shù)，對Sheet1中用戶的年齡進(jìn)行計算。 要求：計算用戶的年齡，并將其計算結(jié)果填充到“年齡”列當(dāng)中。=YEAR(NOW()-YEAR(C2)或者：=YEAR(TODAY()-YEAR(C2)2. 使用REPLACE函數(shù)，對Sheet1中用戶的電話號碼進(jìn)行升級。 要求：對“

16、原電話號碼”列中的電話號碼進(jìn)行升級。 升級方法是在區(qū)號（0571）后面加上“8”， 并將其計算結(jié)果保存在“升級電話號碼”列的相應(yīng)單元格中。=REPLACE(F2,1,4,"05718")或者：=REPLACE(F2,5,8,"8"&RIGHT(F2,7)（注意；先設(shè)單元格格式設(shè)為常規(guī)，再用公式。）3. 使用邏輯函數(shù)，判斷Sheet1中的“大于等于40歲的男性”，將結(jié)果保存在Sheet1中的“是否>=40男性”。=IF(D2>=40,IF(B2="男",TRUE,FALSE),FALSE)4. 對Sheet1中的數(shù)據(jù)

17、，根據(jù)以下條件,利用函數(shù)進(jìn)行統(tǒng)計：a. 統(tǒng)計性別為“男”的用戶人數(shù)，將結(jié)果填入Sheet2的B1單元格中；=COUNTIF(Sheet1!B2:B37,"男")b. 統(tǒng)計年齡為“>40”歲的用戶人數(shù)，將結(jié)果填入Sheet2的B2單元格中。=COUNTIF(Sheet1!D2:D37,">40")5. 將Sheet1復(fù)制到Sheet3，并對Sheet3進(jìn)行高級篩選。 a. 篩選條件為：“性別”女、“所在區(qū)域”西湖區(qū)；所在區(qū)域性 別西湖區(qū)女b. 將篩選結(jié)果保存在Sheet3中。6. 根據(jù)Sheet1的結(jié)果，創(chuàng)建一數(shù)據(jù)透視圖Chart1，要求：a.

18、 顯示每個區(qū)域所擁有的用戶數(shù)量；b. x坐標(biāo)設(shè)置為“所在區(qū)域”； c. 計數(shù)項為“所在區(qū)域”；d. 將對應(yīng)的數(shù)據(jù)透視表保存在Sheet4中。透視圖chart1如下所示；數(shù)據(jù)透視表如下：計數(shù)項:所在區(qū)域所在區(qū)域匯總拱墅區(qū)6江干區(qū)6上城區(qū)5西湖區(qū)6下城區(qū)6余杭區(qū)7總計36(五) 產(chǎn)品(數(shù)組公式if).xls1. 使用數(shù)組公式，計算Sheet1中的每種產(chǎn)品的價值，將結(jié)果保存 到表中的“價值”列中。 計算價值的計算方法為：“單價*每盒數(shù)量*采購盒數(shù)”。=E2:E17*F2:F17*G2:G172. 在Sheet2中，利用數(shù)據(jù)庫函數(shù)及已設(shè)置的條件區(qū)域，計算以下情況的結(jié)果，并將結(jié)果保存相應(yīng)的單元格中。a.

19、 計算：商標(biāo)為上海，瓦數(shù)小于100的白熾燈的平均單價；=DAVERAGE(A1:H17,E1,J2:L3)b. 計算：產(chǎn)品為白熾燈，其瓦數(shù)大于等于80且小于等于100的數(shù)量。=DSUM(A1:H17,G1,J7:L8)3. 某公司對各個部門員工吸煙情況進(jìn)行統(tǒng)計，作為人力資源搭配的一個數(shù)據(jù)依據(jù)。對于調(diào)查對象，只能回答Y（吸煙）或者N（不吸煙）。根據(jù)調(diào)查情況，制做出Sheet3。請使用函數(shù)，統(tǒng)計符合以下條件的數(shù)值。a. 統(tǒng)計未登記的部門個數(shù)；=COUNTBLANK(B2:E11)b. 統(tǒng)計在登記的部門中，吸煙的部門個數(shù)。=COUNTIF(B2:E11,"Y")4. 使用函數(shù)，對

20、Sheet3中的B21單元格中的內(nèi)容進(jìn)行判斷，判斷其是否問文本，如果是，結(jié)果為“TRUE”；如果不是，結(jié)果為“FALSE”，并將結(jié)果保存在Sheet3中的B22單元格當(dāng)中。=ISTEXT(B21)5. 將Sheet1復(fù)制到Sheet4中，對Sheet4進(jìn)行高級篩選，要求：a. 篩選條件：“產(chǎn)品為白熾燈，商標(biāo)為上?！?，并將結(jié)果保存；產(chǎn)品商標(biāo)白熾燈上海b. 將結(jié)果保存在Sheet4中。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. 計數(shù)項為“采購盒數(shù)”。數(shù)據(jù)透視表如下

21、：計數(shù)項:采購盒數(shù)商標(biāo)產(chǎn)品北京上海(空白)總計白熾燈459氖管112其他22日光燈22總計7815(六) 房產(chǎn)銷售表(數(shù)組公式).xls1. 利用公式，計算Sheet1中的房價總額。房價總額的計算公式為：“面積*單價”=F3*G32. 使用數(shù)組公式，計算Sheet1中的契稅總額。 契稅總額的計算公式為：“契稅*房價總額”=H3:H26*I3:I263. 使用函數(shù)，根據(jù)Sheet1中的結(jié)果，統(tǒng)計每個銷售人員的銷售總額，將結(jié)果保存在Sheet2中的相應(yīng)的單元格中。人員甲：=SUMIF(Sheet1!K3:K26,A2,Sheet1!I3:I26)然后利用填充柄復(fù)制公式4. 使用RANK函數(shù)，根據(jù)S

22、heet2的結(jié)果，對每個銷售人員的銷售情況 進(jìn)行排序，并將結(jié)果保存在“排名”列當(dāng)中。人員甲：=RANK(B2,B$2:B$6)然后利用填充柄復(fù)制公式5. 將Sheet1復(fù)制到Sheet3中，并對Sheet3進(jìn)行高級篩選，要求：a. 篩選條件為：“戶型”為兩室一廳，“房價總額”>1000000；戶型房價總額兩室一廳>1000000 b. 將結(jié)果保存在Sheet3中。6. 根據(jù)Sheet1的結(jié)果，創(chuàng)建一張數(shù)據(jù)透視圖Chart1，要求； a. 顯示每個銷售人員銷售房屋所繳納契稅總額； b. 行區(qū)域設(shè)置為“銷售人員”； c. 計數(shù)項設(shè)置為契稅總額；d. 將對應(yīng)的數(shù)據(jù)透視表保存在Sheet4

23、中。數(shù)據(jù)透視圖chart1如下所示:數(shù)據(jù)透視表如下：求和項:契稅總額銷售人員匯總?cè)藛T丙199857.4008人員丁59564.1012人員甲244122.8748人員戊147790.5024人員乙86253.5637總計737588.4429(七) 公務(wù)員考試成績表(if函數(shù)).xls1. 使用IF函數(shù)，對Sheet1中的“學(xué)位”列進(jìn)行自動填充。 要求：填充的內(nèi)容根據(jù)“學(xué)歷”列的內(nèi)容來確定（假定學(xué)生均已獲得相應(yīng)學(xué)位）： - 博士研究生博士 - 碩士研究生碩士 - 本科學(xué)士 - 其他無=IF(G3="博士研究生","博士",IF(G3="碩士研究

24、生","碩士",IF(G3="本科","學(xué)士","無")2. 使用數(shù)組公式，在Sheet1中計算： a.“筆試比例分”，計算方法為：（筆試成績/3）*60 =I3:I18/3*0.6 b.“面試比例分”，計算方法為：面試成績*40 =K3:K18*0.4 c.“總成績”，計算方法為：筆試比例分+面試比例分 =J3:J18+L3:L183. 修改數(shù)組公式，將Sheet1復(fù)制到Sheet2，在Sheet2中計算：要求：修改“筆試比例分”的計算，計算方法為：（筆試成績/2）*60）。=I3:I18/2*0.64

25、. 在 Sheet2中，添加一列，將其命名為“排名”。要求：使用RANK函數(shù)，根據(jù)“總成績”對所有考生排名。=RANK(M3,M$3:M$18)5. 將Sheet2復(fù)制到Sheet3，并對Sheet3進(jìn)行高級篩選。a. 篩選條件為：“報考單位”中院、“性別”男、“學(xué)歷”碩士研究生報考單位性別學(xué)歷一中院男碩士研究生三中院男碩士研究生 b. 將篩選結(jié)果保存在Sheet3中6. 根據(jù)Sheet2，在Sheet4中新建一數(shù)據(jù)透視表。要求： a. 顯示每個報考單位的人的不同學(xué)歷的總?cè)藬?shù) b. 行區(qū)域設(shè)置為“報考單位” c. 列區(qū)域設(shè)置為“學(xué)歷” d. 數(shù)據(jù)區(qū)域設(shè)置為“學(xué)歷”e. 計數(shù)項為學(xué)歷數(shù)據(jù)透視表如

26、下：計數(shù)項:學(xué)歷學(xué)歷報考單位本科博士研究生大專碩士研究生總計區(qū)法院314三中院33市高院11125一中院134總計722516(八) 員工姓名(REPLACE函數(shù)).xls1. 使用REPLACE函數(shù)，對Sheet1中的員工代碼進(jìn)行升級，要求： a. 升級方法：在PA后面加上0； b. 將升級后的員工代碼結(jié)果填入表中的“升級員工代碼”列中。=REPLACE(B2,3,4,"0"&RIGHT(B2,3)2. 使用時間函數(shù)，對Sheet1員工的“年齡”和“工齡”進(jìn)行計算，并將結(jié)果填入到表中的“年齡”列和“工齡”列中。年齡：=YEAR(TODAY()-YEAR(E2) 工

27、齡：=YEAR(TODAY()-YEAR(G2)3. 使用統(tǒng)計函數(shù)，對Sheet1中的數(shù)據(jù)，根據(jù)以下統(tǒng)計條件進(jìn)行如下統(tǒng)計。 a. 統(tǒng)計男性員工的人數(shù)，結(jié)果填入N3單元格中；COUNTIF(D2:D65,"=男") b. 統(tǒng)計高級工程師人數(shù)，結(jié)果填入N4單元格中；COUNTIF(I2:I65,"=高級工程師") c. 統(tǒng)計工齡大于等于10的人數(shù)，果填入N5單元格中。COUNTIF(H2:H65,">=10")4. 使用邏輯函數(shù)，判斷員工是否有資格評“高級工程師”。 評選條件為：工齡大于20，且為工程師的員工。=IF(I2=&quo

28、t;工程師",IF(H2>20,TRUE,FALSE),FALSE)=IF(AND(H2>20,I2="工程師"),"是","否")5. 將Sheet1復(fù)制到Sheet2中，并對Sheet2進(jìn)行高級篩選，要求： a. 篩選條件為：“性別”男，“年齡”>30，“工齡”>10，“職稱”助工；性別年齡工齡職稱男>30>=10助工 b. 將結(jié)果保存在Sheet2中。6. 根據(jù)Sheet1中的數(shù)據(jù)，創(chuàng)建一張數(shù)據(jù)透視圖Chart1，要求： a. 顯示工廠中各個職稱的人數(shù)； b. x坐標(biāo)設(shè)置為“職稱”；

29、 c. 計數(shù)項為職稱； d. 將對應(yīng)的數(shù)據(jù)透視表保存在Sheet3中。數(shù)據(jù)透視圖chart1如下：數(shù)據(jù)透視表如下：計數(shù)項:職稱職稱匯總高級工2高級工程師14工程師11技工5技師1技術(shù)員11中級工1助工19總計64(九) 停車價目表(HLOOKUP函數(shù)).xls1. 使用HLOOKUP函數(shù)，對Sheet1中的停車單價進(jìn)行自動填充。 要求：根據(jù)Sheet1中的“停車價目表”價格，利用HLOOKUP函數(shù)對“停車情況記錄表”中的“單價”列根據(jù)不同的車型進(jìn)行自動填充。=HLOOKUP(B9,A$2:C$3,2,FALSE)或者用數(shù)組公式：=HLOOKUP($B$9:$B$39,$A$2:$C$3,2,0

30、)2. 在Sheet1中，利用時間函數(shù)計算汽車在停車庫中的停放時間，要求： a. 公式計算方法為“出庫時間-入庫時間” b. 格式為：“小時：分鐘：秒” （例如：一小時十五分十二秒在停放時間中的表示為：“1：15：12”）=E9-D93. 使用函數(shù)公式，計算停車費用，要求： 根據(jù)停放時間的長短計算停車費用，將計算結(jié)果填入到“應(yīng)付金額”列中。 注意： a. 停車按小時收費，對于不滿一個小時的按照一個小時計費； b. 對于超過整點小時數(shù)十五分鐘的多累積一個小時。 （例如1小時23分，將以2小時計費）=IF(AND(HOUR(F9)=0,MINUTE(F9)>0),1*C9,IF(MINUTE

31、(F9)>15,(HOUR(F9)+1)*C9,HOUR(F9)*C9)4. 使用統(tǒng)計函數(shù)，對Sheet1中的“停車情況記錄表”根據(jù)下列條件進(jìn)行統(tǒng)計，要求： a. 統(tǒng)計停車費用大于等于40元的停車記錄條數(shù) =COUNTIF(G9:G39,">=40") b. 統(tǒng)計最高的停車費用 = MAX(G9:G39)5. 對Sheet2，進(jìn)行高級篩選，要求： a. 篩選條件為：“車型”小汽車，“應(yīng)付金額”>30； b. 將結(jié)果保存在Sheet2中。車型應(yīng)付金額小汽車>=306. 根據(jù)Sheet1，創(chuàng)建一個數(shù)據(jù)透視圖Chart1，要求： a. 顯示各種車型所收費用

32、的匯總； b. 行區(qū)域設(shè)置為“車型”； c. 計數(shù)項為“應(yīng)付金額”； d. 將對應(yīng)的數(shù)據(jù)透視表保存在Sheet3中。數(shù)據(jù)透視圖chart1如下：數(shù)據(jù)透視表如下：求和項:應(yīng)付金額車型匯總大客車200小汽車155中客車264總計619(十) 平均氣溫日期(if函數(shù)).xls1. 使用IF函數(shù)，對Sheet1中的“溫度較高的城市”列進(jìn)行自動填充。=IF(B2>C2,"杭州","上海")2. 使用數(shù)組公式，對Sheet1中的相差溫度值（杭州相對于上海的溫差）進(jìn)行填充。=B2:B16-C2:C163. 利用函數(shù)，根據(jù)Sheet1中的結(jié)果，符合以下條件的進(jìn)行統(tǒng)

33、計。 a. 杭州這半個月以來的最高氣溫和最低氣溫；=MAX(B2:B16) =MIN(B3:B17) b. 上海這半個月以來的最高氣溫和最低氣溫。=MAX(C2:C16) = MIN(C3:C17)4. 將Sheet1復(fù)制到Sheet2中，在Sheet2中，重新編輯數(shù)組公式，將Sheet2中的“相差的溫度值”中的數(shù)值取其絕對值（均為正數(shù)）。=ABS(B2:B16-C2:C16)5. 將Sheet2復(fù)制到Sheet3中，并對Sheet3進(jìn)行高級篩選，要求： 篩選條件：“杭州平均氣溫”>=20，“上海平均氣溫”<20杭州平均氣溫上海平均氣溫>=20<206. 根據(jù)Sheet

34、1中的結(jié)果，在sheet4中創(chuàng)建一張數(shù)據(jù)透視表，要求： a. 顯示杭州氣溫高于上海氣溫的天數(shù)和上海氣溫高于杭州氣溫的天數(shù)； b. 行區(qū)域設(shè)置為“溫度較高的城市”； c. 計數(shù)項設(shè)置為溫度較高的城市。數(shù)據(jù)透視表如下；計數(shù)項:溫度較高的城市溫度較高的城市匯總杭州11上海4總計15(十一) 學(xué)生成績表.xls1. 使用REPLACE函數(shù)，將Sheet1中“學(xué)生成績表”的學(xué)生學(xué)號進(jìn)行更改，并將更改的學(xué)號填入到“新學(xué)號”列中，學(xué)號更改的方法為：在原學(xué)號的前面加上“2009”。 例如："001" -> "2009001"=REPLACE(A3,1,0,&quo

35、t;2009")2. 使用數(shù)組公式，對Sheet1中“學(xué)生成績表”的“總分”列進(jìn)行計算。 計算方法：總分 =語文 + 數(shù)學(xué) + 英語 + 信息技術(shù) + 體育=E3:E24+F3:F24+G3:G24+H3:H24+I3:I243. 使用IF函數(shù)，根據(jù)以下條件，對Sheet1中“學(xué)生成績表”的“考評”列進(jìn)行計算。 條件：如果總分>=350，填充為“合格”；否則，填充為“不合格”。=IF(J3>=350,"合格","不合格")4. 在Sheet1中，利用數(shù)據(jù)庫函數(shù)及已設(shè)置的條件區(qū)域，根據(jù)以下情況計算，并將結(jié)果填入到相應(yīng)的單元格當(dāng)中。 條

36、件： （a）計算：“語文”和“數(shù)學(xué)”成績都大于或等于85的學(xué)生人數(shù)；=DCOUNTA(A2:K24,B2,M2:N3) （b）計算：“體育”成績大于或等于90的“女生”姓名；=DGET(A2:K24,C2,M7:N8) （c）計算：“體育”成績中男生的平均分；=DAVERAGE(A2:K24,I2,M12:M13) （d）計算：“體育”成績中男生的最高分。=DMAX(A2:K24,I2,M12:M13)5. 將Sheet1中的“學(xué)生成績表”復(fù)制到Sheet2當(dāng)中，并對Sheet2進(jìn)行高級篩選。 要求： （a）篩選條件為：“性別” - 男；“英語” - >90；“信息技術(shù)” - >9

37、5； （b）將篩選結(jié)果保存在Sheet2中。性別英語信息技術(shù)男>90>95注意： （a）無需考慮是否刪除或移動篩選條件; （b）復(fù)制過程中，將標(biāo)題項“學(xué)生成績表”連同數(shù)據(jù)一同復(fù)制; （c）復(fù)制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。6. 根據(jù)Sheet1中“學(xué)生成績表”，在Sheet3中新建一張數(shù)據(jù)透視表。 要求： （a）顯示不同性別、不同考評結(jié)果的學(xué)生人數(shù)情況； （b）行區(qū)域設(shè)置為“性別”； （c）列區(qū)域設(shè)置為“考評”； （d）數(shù)據(jù)區(qū)域設(shè)置為“考評”； （e）計數(shù)項為“考評”。數(shù)據(jù)透視表；計數(shù)項:考評考評性別不合格合格總計男31215女167總計41822(十二) 銷售統(tǒng)計表.xl

38、s1. 使用VLOOKUP函數(shù)， 對Sheet1中的“3月份銷售統(tǒng)計表”的“產(chǎn)品名稱”列和“產(chǎn)品單價”列進(jìn)行填充。 要求：根據(jù)“企業(yè)銷售產(chǎn)品清單”，使用VLOOKUP函數(shù)，將產(chǎn)品名稱和產(chǎn)品單價填充到“3月份銷售統(tǒng)計表”的“產(chǎn)品名稱”列和“產(chǎn)品單價”列中。“產(chǎn)品名稱”列；=VLOOKUP(F3,$A$2:$C$10,2,FALSE)“產(chǎn)品單價”列：=VLOOKUP(F3,$A$2:$C$10,3,FALSE)2. 使用數(shù)組公式，計算Sheet1中的“3月份銷售統(tǒng)計表”中的銷售金額，并將結(jié)果填入到該表的“銷售金額”列中。計算方法：銷售金額 = 產(chǎn)品單價 * 銷售數(shù)量=H3:H44*I3:I443.

39、 使用統(tǒng)計函數(shù)，根據(jù)“3月份銷售統(tǒng)計表”中的數(shù)據(jù)，計算“分部銷售業(yè)績統(tǒng)計表”中的總銷售額，并將結(jié)果填入該表的“總銷售額”列。=SUMIF($K$3:$K$44,N3,$L$3:$L$44)4. 在Sheet1中，使用RANK函數(shù)，在“分部銷售業(yè)績統(tǒng)計”表中，根據(jù)“總銷售額”對各部門進(jìn)行排名，并將結(jié)果填入到“銷售排名”列中。=RANK(O3,$O$3:$O$5)5. 將sheet1中的“三月份銷售統(tǒng)計表”復(fù)制到Sheet2中，對Sheet2進(jìn)行高級篩選。 要求： （a）篩選條件為：“銷售數(shù)量”>3、“所屬部門”市場1部、“銷售金額”->1000 （b）將篩選結(jié)果保存在Sheet2中。

40、銷售數(shù)量所屬部門銷售金額>3市場1部>1000 注意： （a）無需考慮是否刪除或移動篩選條件; （b）復(fù)制過程中，將標(biāo)題項“三月份銷售統(tǒng)計表”連同數(shù)據(jù)一同復(fù)制; （c）復(fù)制數(shù)據(jù)表后，粘貼時，數(shù)據(jù)表必須頂格放置。6. 根據(jù)Sheet1的“3月份銷售統(tǒng)計表”中的數(shù)據(jù)，新建一個數(shù)據(jù)透視圖Chart1。 要求： （a）該圖形顯示每位經(jīng)辦人的總銷售額情況 （b）x坐標(biāo)設(shè)置為“經(jīng)辦人”; （c）數(shù)據(jù)區(qū)域設(shè)置為“銷售金額”; （d）求和項為銷售金額; （e）將對應(yīng)的數(shù)據(jù)透視表保存在Sheet3中。數(shù)據(jù)透視圖Chart1：數(shù)據(jù)透視表：求和項:銷售金額經(jīng)辦人匯總甘倩琦5752李成蹊4416劉 惠18

41、232孫國成6920王 勇3976吳 仕12116吳小平3424許 丹11352趙 榮6436總計72624(十三) 學(xué)生成績表.xls1. 使用數(shù)組公式，根據(jù)Sheet1中“學(xué)生成績表”的數(shù)據(jù)，計算考試總分， 并將結(jié)果填入到“總分”列中。 計算方法：總分 = 單選題 + 判斷題 + windows操作題 + Excel操作題 + PowerPoint操作題 + IE操作題=D3:D57+E3:E57+F3:F57+G3:G57+H3:H57+I3:I572. 使用文本函數(shù)中的一個函數(shù)，在Sheet1中，利用“學(xué)號”列的數(shù)據(jù)，根據(jù)以下要求獲得考生所考級別，并將結(jié)果填入“級別”列中。 要求： （

42、a）學(xué)號中的第八位指示的考生所考級別，例如：“085200821023080”中的“2”標(biāo)識了該考生所考級別為二級 （b）在“級別”列中，填入的數(shù)據(jù)是函數(shù)的返回值。=MID(A3,8,1)3. 使用統(tǒng)計函數(shù)，根據(jù)以下要求對Sheet1中“學(xué)生成績表”的數(shù)據(jù)進(jìn)行統(tǒng)計。 要求： （a）統(tǒng)計“考1級的考生人數(shù)”，并將計算結(jié)果填入到N2單元格中；=COUNTIF(C3:C57,"1") （b）統(tǒng)計“考試通過人數(shù)（>=60）”,并將計算結(jié)果填入到N3單元格中；=COUNTIF(J3:J57,">=60") （c）統(tǒng)計“全體1級考生的考試平均分”，并將計

43、算結(jié)果填入到N4單元格中。（其中，計算時候的分母直接使用“N2”單元格的數(shù)據(jù)）=SUMIF(C3:C57,"1",J3:J57)/N24. 使用財務(wù)函數(shù)，根據(jù)以下要求對Sheet2中的數(shù)據(jù)進(jìn)行計算。 要求： （a）根據(jù)“投資情況表1”中的數(shù)據(jù)，計算10年以后得到的金額，并將結(jié)果填入到B7單元格中；=FV(B3,B5,B4,B2) （b）根據(jù)“投資情況表2”中的數(shù)據(jù)，計算預(yù)計投資金額，并將結(jié)果填入到E7單元格中。=PV(E3,E4,E2)5. 將Sheet1中的“學(xué)生成績表”復(fù)制到Sheet3，并對Sheet3進(jìn)行高級篩選。 要求： （a）篩選條件為：“級別”2、“總分”&g

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

45、828393總計1111211111131321113312113421111231111311112111110總計21112111113341523411414221115521二、AOA Excel 考試題用到的函數(shù)參考1排名函數(shù)：RANK（被排名的單元，排名的范圍_絕對引用，0降序、非0 升序）RANK(G2,$G$2:$G$39,0)2判斷函數(shù)：IF（條件，真取值，假取值）3 數(shù)據(jù)庫計數(shù)函數(shù)：DCOUNT（數(shù)據(jù)庫范圍，被計數(shù)列，放條件的區(qū)域）DCOUNT(Sheet1!A1:I39,Sheet1!D1,B10:C11)4縱向?qū)φ毡聿檎也⑻钪岛瘮?shù)：VLOOKUP（被對照的區(qū)域，對照的表

46、格區(qū)域，填對照表格2列，0）VLOOKUP($A$11:$A$43,$F$2:$G$4,2,0)5. 橫向?qū)φ毡聿檎也⑻钪岛瘮?shù)：HLOOKUP（被對照的區(qū)域，對照的表格區(qū)域，填對照表格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(被替換的單元，第幾個字符開始，共幾個，新字符串)REP

47、LACE(F2,5,8,"8"&RIGHT(F2,7)9取字符串函數(shù)：RIGHT(被取的單元，右起取幾個) LEFT(被取的單元，左起取幾個) RIGHT(F2,7)10. 條件計數(shù)函數(shù)：COUNTIF(被計數(shù)的區(qū)域范圍,"條件")COUNTIF(Sheet1!$B$2:$B$37,"=男")11.數(shù)據(jù)庫中求平均值函數(shù)：DAVERAGE(數(shù)據(jù)庫范圍，被求平均值的列，放條件的區(qū)域)DAVERAGE(A1:G17,E1,J2:L3)12.計數(shù)空白單元格個數(shù)函數(shù)：COUNTBLANK(被計數(shù)區(qū)域范圍) COUNTBLANK(B2:E11)13.判定是否文本單元格的函數(shù)： ISTEXT(單元格)IF(ISTEXT(C21),"TRUE","FALSE")14.橫向?qū)φ毡聿檎也⑻钪岛瘮?shù)：HLOOKUP（被對照的區(qū)域，對照的表格區(qū)域，填對照表格2行，0）HLOOKUP($B$9:$B$39,$A$2:$C$3,2,0)15.財務(wù)函數(shù)：PMT 貸了多少款，年利息是多少，貸多少年，等額分期按年償還貸款金額（年末）功能：基于固定利率及等額分期付款方式，返回貸款的每期付款額 格式：PMT(rate，nper，pv，fv，type)rate：貸款