浙江省計(jì)算機(jī)二級(jí)辦公軟件高級(jí)應(yīng)用技術(shù)之EXCEL題解

1、第一套題目：醫(yī)院病人護(hù)理統(tǒng)計(jì)表第二套題目：3月份銷(xiāo)售統(tǒng)計(jì)表 第三套題目：采購(gòu)表第四套題目：采購(gòu)情況表第五套題目：電話號(hào)碼第六套題目：房產(chǎn)銷(xiāo)售表第七套題目：公司員工人事信息表第八套題目：公務(wù)員考試成績(jī)表第九套題目：教材訂購(gòu)情況表第十套題目：零件檢測(cè)結(jié)果表第十一套題目：停車(chē)情況記錄表第十二套題目：通訊年度計(jì)劃表第十三套題目：圖書(shū)訂購(gòu)信息表第十四套題目：學(xué)生成績(jī)表第十五套題目：?jiǎn)T工資料表第十六套題目：學(xué)生成績(jī)表之二第一套：醫(yī)院病人護(hù)理統(tǒng)計(jì)表 題目要求 使用數(shù)據(jù)庫(kù)函數(shù)，按以下要求計(jì)算: 要求：（a）計(jì)算sheet1“醫(yī)院病人護(hù)理統(tǒng)計(jì)表”中，性別為女性，護(hù)理級(jí)別為中級(jí)護(hù)理，護(hù)理天數(shù)大于30天的人數(shù)，并保

2、存n13單元格中；dcount.png (27.32 kb)2009-12-8 14:41 （b）計(jì)算護(hù)理級(jí)別為高級(jí)護(hù)理的護(hù)理費(fèi)用總和，并保存n22單元格中。dsum.png (27.09 kb)2009-12-8 19:35第二套題目：3月份銷(xiāo)售統(tǒng)計(jì)表題一：使用vlookup函數(shù)， 對(duì)sheet1中的“3月份銷(xiāo)售統(tǒng)計(jì)表”的“產(chǎn)品名稱(chēng)”列和“產(chǎn)品單價(jià)”列進(jìn)行填充。要求：根據(jù)“企業(yè)銷(xiāo)售產(chǎn)品清單”，使用vlookup函數(shù)，將產(chǎn)品名稱(chēng)和產(chǎn)品單價(jià)填充到“3月份銷(xiāo)售統(tǒng)計(jì)表”的“產(chǎn)品名稱(chēng)”列和“產(chǎn)品單價(jià)”列中。vlookup.png (15.92 kb)2009-12-8 20:31vlookup2.pn

3、g (15.97 kb)2009-12-8 20:27題二：使用統(tǒng)計(jì)函數(shù)，根據(jù)“3月份銷(xiāo)售統(tǒng)計(jì)表”中的數(shù)據(jù)，計(jì)算“分部銷(xiāo)售業(yè)績(jī)統(tǒng)計(jì)表”中的總銷(xiāo)售額，并將結(jié)果填入該表的“總銷(xiāo)售額”列。 sumif.png (18.61 kb)2009-12-8 20:08題三：在sheet1中，使用rank函數(shù)，在“分部銷(xiāo)售業(yè)績(jī)統(tǒng)計(jì)”表中，根據(jù)“總銷(xiāo)售額”對(duì)各部門(mén)進(jìn)行排名，并將結(jié)果填入到“銷(xiāo)售排名”列中。rank.png (14.18 kb)2009-12-8 20:08第三套題目：采購(gòu)表1.使用vlookup函數(shù)，對(duì)sheet1中“采購(gòu)表”的“商品單價(jià)”列進(jìn)行填充。要求：根據(jù)“價(jià)格表”中的商品單價(jià)，使用vlo

4、okup函數(shù)，將其單價(jià)填充到采購(gòu)表中的“單價(jià)”列中。注意：函數(shù)中參數(shù)如果需要用到絕對(duì)地址的，請(qǐng)使用絕對(duì)地址進(jìn)行答題，其他方式無(wú)效。vlookup.png (34.31 kb)2009-12-9 08:232. 使用if函數(shù)，對(duì)sheet1“采購(gòu)表”中的“折扣”列進(jìn)行填充。 要求：根據(jù)“折扣表”中的商品折扣率，使用相應(yīng)的函數(shù)，將其折扣率填充到采購(gòu)表中的“折扣“列中。if.png (42.89 kb)2009-12-9 08:23很多同學(xué)喜歡vlookup函數(shù)來(lái)做，那就來(lái)一個(gè)啦。 vlookup.png (14.96 kb)2010-4-6 06:453. 使用公式，對(duì)sheet1中“采購(gòu)表”的“

5、合計(jì)”列進(jìn)行計(jì)算。 要求：根據(jù)“采購(gòu)數(shù)量”，“單價(jià)”和“折扣”，計(jì)算采購(gòu)的合計(jì)金額，將結(jié)果保存在“合計(jì)”列中。計(jì)算公式：?jiǎn)蝺r(jià)*采購(gòu)數(shù)量*（1-折扣率）sum.png (29.26 kb)2009-12-9 08:234. 使用sumif函數(shù)，計(jì)算各種商品的采購(gòu)總量和采購(gòu)總金額，將結(jié)果保存在sheet1中的“統(tǒng)計(jì)表”當(dāng)中相應(yīng)位置。sumif1.png (11.58 kb)2009-12-9 08:23sumif2.png (11.44 kb)2009-12-9 08:23第四套題目： 采購(gòu)情況表 1. 使用數(shù)組公式，計(jì)算sheet1“采購(gòu)情況表”中的每種產(chǎn)品的采購(gòu)總額，將結(jié)果保存到表中的“采購(gòu)總

6、額”列中。計(jì)算方法為：采購(gòu)總額 = 單價(jià) * 每盒數(shù)量 * 采購(gòu)盒數(shù)。數(shù)組.png (38.46 kb)2009-12-9 10:222. 根據(jù)sheet1中的“采購(gòu)情況表”，使用數(shù)據(jù)庫(kù)函數(shù)及已設(shè)置的條件區(qū)域， 計(jì)算以下情況的結(jié)果。（a）計(jì)算：商標(biāo)為上海，壽命小于100的白熾燈的平均單價(jià)，并將結(jié)果填入sheet1的g25單元格中；daverage.png (12.21 kb)2009-12-9 10:22（b）計(jì)算：產(chǎn)品為白熾燈，其瓦數(shù)大于等于80且小于等于100的數(shù)量，并將 結(jié)果填入sheet1的g26單元格中。dcounta.png (24.88 kb)2009-12-9 10:223.

7、某公司對(duì)各個(gè)部門(mén)員工吸煙情況進(jìn)行統(tǒng)計(jì)，作為人力資源搭配的一個(gè)數(shù)據(jù)依據(jù)。對(duì)于調(diào)查對(duì)象，只能回答y（吸煙）或者n（不吸煙）。根據(jù)調(diào)查情況，制做出sheet2中的“吸煙情況調(diào)查表”。請(qǐng)使用函數(shù)，統(tǒng)計(jì)符合以下條件的數(shù)值。 （a）統(tǒng)計(jì)未登記的部門(mén)個(gè)數(shù)，將結(jié)果保存在b14單元格中； （b）統(tǒng)計(jì)在登記的部門(mén)中，吸煙的部門(mén)個(gè)數(shù)，將結(jié)果保存在b15單元格中。 countif.png (20.4 kb)2009-12-9 10:22countblank.gif (20.79 kb)2009-12-22 08:044. 使用函數(shù)，對(duì)sheet2中的b21單元格中的內(nèi)容進(jìn)行判斷，判斷其是否為文本，如果是，單元格填充為

8、“true”；如果不是，單元格填充為“false”，并將結(jié)果保存在sheet2中的b22單元格當(dāng)中。istext.png (14.55 kb)2009-12-9 10:22第五套題目：電話號(hào)碼1. 使用時(shí)間函數(shù)，對(duì)sheet1中用戶的年齡進(jìn)行計(jì)算。要求：假設(shè)當(dāng)前時(shí)間是“2008-11-15”，結(jié)合用戶的出生年月，計(jì)算用戶的年齡，并將其計(jì)算結(jié)果保存在“年齡”列當(dāng)中。計(jì)算方法為兩個(gè)時(shí)間年份之差。year.png (17.36 kb)2009-12-9 21:062. 使用replace函數(shù)，對(duì)sheet1中用戶的電話號(hào)碼進(jìn)行升級(jí)。 要求： 對(duì)“原電話號(hào)碼”列中的電話號(hào)碼進(jìn)行升級(jí)。升級(jí)方法是在區(qū)號(hào)（

9、0571） 后面加上“8”，并將其計(jì)算結(jié)果保存在“升級(jí)電話號(hào)碼”列的相應(yīng)單元格中。 例如：電話號(hào)碼“05716742808” 升級(jí)后為 “057186742808” replace.png (16.6 kb)2009-12-9 21:063. 在sheet1中，使用and函數(shù)，根據(jù)“性別”及“年齡”列中的數(shù)據(jù)，判斷所有用戶是否為大于等于40歲的男性，并將結(jié)果保存在“是否=40男性”列中。注意：如果是，保存結(jié)果為true；否則，保存結(jié)果為false。and.png (19.29 kb)2009-12-9 21:064. 根據(jù)sheet1中的數(shù)據(jù)，對(duì)以下條件,使用統(tǒng)計(jì)函數(shù)進(jìn)行統(tǒng)計(jì)。要求：（a）統(tǒng)計(jì)

10、性別為“男”的用戶人數(shù)，將結(jié)果填入sheet2的b2單元格中；（b）統(tǒng)計(jì)年齡為“40”歲的用戶人數(shù)，將結(jié)果填入sheet2的b3單元格中。第六套題目：房產(chǎn)銷(xiāo)售表 2. 使用數(shù)組公式，計(jì)算sheet1中“房產(chǎn)銷(xiāo)售表”的契稅總額，并保存在“契稅總額”列中。計(jì)算公式為：契稅總額 = 契稅 * 房?jī)r(jià)總額。數(shù)組.png (39.14 kb)2009-12-10 08:363. 使用函數(shù)，根據(jù)sheet1中“房產(chǎn)銷(xiāo)售表”的結(jié)果，在sheet2中統(tǒng)計(jì)每個(gè) 銷(xiāo)售人員的銷(xiāo)售總額，將結(jié)果保存在sheet2中的“銷(xiāo)售總額”列中。sumif.png (18.18 kb)2009-12-10 08:364. 使用ran

11、k函數(shù)，根據(jù)sheet2中“銷(xiāo)售總額”列的結(jié)果，對(duì)每個(gè)銷(xiāo)售人員的銷(xiāo)售情況 進(jìn)行排序，并將結(jié)果保存在“排名”列當(dāng)中。 第七套題目：公司員工人事信息表1. 使用大小寫(xiě)轉(zhuǎn)換函數(shù)，根據(jù)sheet1中“公司員工人事信息表”的“編號(hào)”列， 對(duì)“新編號(hào)”列進(jìn)行填充。要求： 把編號(hào)中的小寫(xiě)字母改為大寫(xiě)字母， 并將結(jié)果保存在“新編號(hào)”列中。例如，“a001”更改后為“a001”。upper.png (19.69 kb)2009-12-11 08:062. 使用文本函數(shù)和時(shí)間函數(shù)，根據(jù)sheet1中“公司員工人事信息表”的“身份證號(hào)碼”列，計(jì)算用戶的年齡，并保存在“年齡”列中。 注意： （a）身份證的第7位第10

12、位表示出生年份； （b）計(jì)算方法：年齡 = 當(dāng)前年份 - 出生年份。其中“當(dāng)前年份使用時(shí)間函數(shù)計(jì)算。year.png (25.37 kb)2009-12-11 08:06 3. 在sheet1中，利用數(shù)據(jù)庫(kù)函數(shù)及已設(shè)置的條件區(qū)域，根據(jù)以下情況計(jì)算，并將結(jié)果 填入到相應(yīng)的單元格當(dāng)中。 計(jì)算：獲取具有碩士學(xué)歷，職務(wù)為經(jīng)理助理的員工姓名，并將結(jié)果保存在sheet1中的e31單元格中。2.png (58.5 kb)2009-12-11 08:06 4. 使用函數(shù)，判斷sheet1中l(wèi)12和m12單元格中的文本字符串是否完全相同。注意：（a）如果完全相同，結(jié)果保存為true，否則保存為false。（b）

13、將結(jié)果保存在sheet1中的n11單元格中。 exact.png (13.28 kb)2009-12-11 08:06 第八套題目：公務(wù)員考試成績(jī)表1. 使用if函數(shù)，對(duì)sheet1中的“學(xué)位”列進(jìn)行自動(dòng)填充。 要求：填充的內(nèi)容根據(jù)“學(xué)歷”列的內(nèi)容來(lái)確定（假定學(xué)生均已獲得相應(yīng)學(xué)位）：- 博士研究生博士- 碩士研究生碩士- 本科學(xué)士- 其他無(wú) if.png (24.74 kb)2009-12-11 14:002. 使用數(shù)組公式，在sheet1中計(jì)算： （1） 計(jì)算筆試比例分，并將結(jié)果保存在“公務(wù)員考試成績(jī)表”中的“筆試比例分”中。計(jì)算方法為：筆試比例分 =（筆試成績(jī) / 3）* 60 （2） 計(jì)

14、算面試比例分，并將結(jié)果保存在“公務(wù)員考試成績(jī)表”中的“面試比例分”中。計(jì)算方法為：面試比例分 = 面試成績(jī) * 40 （3） 計(jì)算總成績(jī)，并將結(jié)果保存在“公務(wù)員考試成績(jī)表”中的“總成績(jī)”中。計(jì)算方法為：總成績(jī) = 筆試比例分 + 面試比例分shift.png (39.23 kb)2009-12-11 14:00 4. 在sheet2中，使用rank函數(shù)，根據(jù)“總成績(jī)”列對(duì)所有考生進(jìn)行排名。要求：將排名結(jié)果保存在“排名”列中。 第九套題目：教材訂購(gòu)情況表2. 使用統(tǒng)計(jì)函數(shù)，對(duì)sheet1中“教材訂購(gòu)情況表”的結(jié)果按以下條件進(jìn)行統(tǒng)計(jì)， 并將結(jié)果保存在sheet1中的相應(yīng)位置。 要求：（a）統(tǒng)計(jì)出版

15、社名稱(chēng)為“高等教育出版社”的書(shū)的種類(lèi)數(shù),并將結(jié)果保存在sheet1中l(wèi)2單元格中；（b）統(tǒng)計(jì)訂購(gòu)數(shù)量大于110且小于850的書(shū)的種類(lèi)數(shù), 并將結(jié)果保存在sheet1中l(wèi)3單元格中。countif1.png (14.04 kb)2009-12-11 16:30countif2.png (14.22 kb)2009-12-11 16:303. 使用函數(shù)，計(jì)算每個(gè)用戶所訂購(gòu)圖書(shū)所需支付的金額總數(shù)，并將結(jié)果 保存在sheet1中“用戶支付情況表”的“支付總額”列中。sumif.png (19.41 kb)2009-12-11 16:304. 使用函數(shù)，判斷sheet2中的年份是否為閏年，如果是，結(jié)果保

16、存“閏年”， 如果不是，則結(jié)果保存“平年”，并將結(jié)果保存在“是否為閏年”列中。 說(shuō)明：閏年定義：年數(shù)能被4整除而不能被100整除，或者能被400整除的年份。 if.png (15.77 kb)2009-12-11 16:30 if2.png (16.97 kb)2009-12-16 15:47第十套題目：零件檢測(cè)結(jié)果表 1. 使用數(shù)組公式，根據(jù)sheet1中“零件檢測(cè)結(jié)果表”的“外輪直徑”和“內(nèi)輪直徑”列，計(jì)算內(nèi)外輪差，并將結(jié)果表保存在“輪差”列中。 計(jì)算方法為：輪差 = 外輪直徑 -內(nèi)輪直徑。 數(shù)組.png (13.17 kb)2009-12-11 16:532. 使用if函數(shù)，對(duì)sheet

17、1中“零件檢測(cè)結(jié)果表”的“檢測(cè)結(jié)果”列進(jìn)行填充。要求：（a）如果“輪差” =60）”,并將計(jì)算結(jié)果填入到n3單元格中；countif2.png (11.2 kb)2009-12-18 07:54（c）統(tǒng)計(jì)“全體1級(jí)考生的考試平均分”，并將計(jì)算結(jié)果填入到n4單元格中。（其中，計(jì)算時(shí)候的分母直接使用“n2”單元格的數(shù)據(jù)）sumif.png (12.18 kb)2009-12-18 07:54 4. 使用財(cái)務(wù)函數(shù)，根據(jù)以下要求對(duì)sheet2中的數(shù)據(jù)進(jìn)行計(jì)算。 要求：（a）根據(jù)“投資情況表1”中的數(shù)據(jù)，計(jì)算10年以后得到的金額，并將結(jié)果填入到b7單元格中；fv.png (13.18 kb)2009-1

18、2-18 07:54 （b）根據(jù)“投資情況表2”中的數(shù)據(jù)，計(jì)算預(yù)計(jì)投資金額，并將結(jié)果填入到e7單元格中。 pv.png (18.68 kb)2009-12-18 07:54第十五套題目：?jiǎn)T工資料表1. 僅使用mid函數(shù)和concatenate函數(shù)，對(duì)sheet1中“員工資料表”的 “出生日期”列進(jìn)行填充。要求： （a）填充的內(nèi)容根據(jù)“身份證號(hào)碼”列的內(nèi)容來(lái)確定： - 身份證號(hào)碼中的第7位第10位：表示出生年份; - 身份證號(hào)碼中的第11位第12位：表示出生月份; - 身份證號(hào)碼中的第13位第14位：表示出生日;（b）填充結(jié)果的格式為：xxxx年xx月xx日（注意：不使用單元格格式進(jìn)行設(shè)置）co

19、ncatenate.gif (18.19 kb)2009-12-22 07:582. 根據(jù)sheet1中“職務(wù)補(bǔ)貼率表”的數(shù)據(jù)，使用vlookup函數(shù)，對(duì)“員工資料表”中的 “職務(wù)補(bǔ)貼率”列進(jìn)行自動(dòng)填充。 vlookup.gif (16.32 kb)2009-12-22 07:58 4. 在sheet2中，根據(jù)“固定資產(chǎn)情況表”，使用財(cái)務(wù)函數(shù)，對(duì)以下條件進(jìn)行計(jì)算。條件：（a）計(jì)算“每天折舊值”，并將結(jié)果填入到e2單元格中；（b）計(jì)算“每月折舊值”，并將結(jié)果填入到e3單元格中；（c）計(jì)算“每年折舊值”，并將結(jié)果填入到e4單元格中 sln2.gif (15.03 kb)2009-12-22 09:

20、05第十六套題目： 學(xué)生成績(jī)表之21. 使用數(shù)組公式，根據(jù)sheet1中的數(shù)據(jù)，計(jì)算總分和平均分，將其計(jì)算結(jié)果保存到 表中的“總分”列和“平均分”列當(dāng)中?？偡?jpg (26.9 kb)2009-12-28 20:13 平均.jpg (26.52 kb)2009-12-28 20:132. 使用rank函數(shù)，根據(jù)sheet1中的“總分”列對(duì)每個(gè)同學(xué)排名情況進(jìn)行統(tǒng)計(jì)，并將排名結(jié)果保存到表中的“排名”列當(dāng)中。 rank.jpg (31.83 kb)2009-12-28 20:133. 使用邏輯函數(shù)，判斷sheet1中每個(gè)同學(xué)的每門(mén)功課是否均高于全班單科平均分。要求：如果是，保存結(jié)果為true，否則

21、，保存結(jié)果為false，將結(jié)果保存在表中的“優(yōu)等生”列當(dāng)中。注意：優(yōu)等生條件：每門(mén)功課均高于全班單科平均分。 and.jpg (41.25 kb)2009-12-28 20:134. 根據(jù)sheet1中的結(jié)果，使用統(tǒng)計(jì)函數(shù)，統(tǒng)計(jì)“數(shù)學(xué)”考試成績(jī)各個(gè)分?jǐn)?shù)段的同學(xué)人數(shù)，將統(tǒng)計(jì)結(jié)果保存到sheet2中的相應(yīng)位置。frequency.jpg (34.78 kb)2009-12-28 20:13acknowledgements my deepest gratitude goes first and foremost to professor aaa , my supervisor, for her co

22、nstant encouragement and guidance. she has walked me through all the stages of the writing of this thesis. without her consistent and illuminating instruction, this thesis could not havereached its present form. second, i would like to express my heartfelt gratitude to professor aaa, who led me into

23、 the world of translation. i am also greatly indebted to the professors and teachers at the department of english: professor dddd, professor ssss, who have instructed and helped me a lot in the past two years. last my thanks would go to my beloved family for their loving considerations and great con

24、fidence in me all through these years. i also owe my sincere gratitude to my friends and my fellow classmates who gave me their help and time in listening to me and helping me work out my problems during the difficult course of the thesis. my deepest gratitude goes first and foremost to professor aaa , my supervisor, for her constant encouragement and guidance