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

..第一套題目：醫(yī)院病人護(hù)理統(tǒng)計(jì)表

第二套題目：3月份銷售統(tǒng)計(jì)表第三套題目：采購(gòu)表第四套題目：采購(gòu)情況表第五套題目：號(hào)碼第六套題目：房產(chǎn)銷售表第七套題目：公司員工人事信息表第八套題目：公務(wù)員考試成績(jī)表第九套題目：教材訂購(gòu)情況表第十套題目：零件檢測(cè)結(jié)果表第十一套題目：停車情況記錄表第十二套題目：通訊年度計(jì)劃表第十三套題目：圖書訂購(gòu)信息表第十四套題目：學(xué)生成績(jī)表第十五套題目：?jiǎn)T工資料表第十六套題目：學(xué)生成績(jī)表之二第一套：醫(yī)院病人護(hù)理統(tǒng)計(jì)表

題目要求

使用數(shù)據(jù)庫函數(shù),按以下要求計(jì)算:

要求：

〔a計(jì)算Sheet1"醫(yī)院病人護(hù)理統(tǒng)計(jì)表"中,性別為女性,護(hù)理級(jí)別

為中級(jí)護(hù)理,護(hù)理天數(shù)大于30天的人數(shù),并保存N13單元格中；

〔b計(jì)算護(hù)理級(jí)別為高級(jí)護(hù)理的護(hù)理費(fèi)用總和,并保存N22單元格中。

第二套題目：3月份銷售統(tǒng)計(jì)表

題一：使用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à)"列中。

題二：使用統(tǒng)計(jì)函數(shù),根據(jù)"3月份銷售統(tǒng)計(jì)表"中的數(shù)據(jù),計(jì)算"分部銷售業(yè)績(jī)統(tǒng)計(jì)表"

中的總銷售額,并將結(jié)果填入該表的"總銷售額"列。

題三：在Sheet1中,使用RANK函數(shù),在"分部銷售業(yè)績(jī)統(tǒng)計(jì)"表中,根據(jù)"總銷售額"對(duì)

各部門進(jìn)行排名,并將結(jié)果填入到"銷售排名"列中。

第三套題目：采購(gòu)表

1.

使用VLOOKUP函數(shù),對(duì)Sheet1中"采購(gòu)表"的"商品單價(jià)"列進(jìn)行填充。

要求：

根據(jù)"價(jià)格表"中的商品單價(jià),使用VLOOKUP函數(shù),將其單價(jià)填

充到采購(gòu)表中的"單價(jià)"列中。

注意：

函數(shù)中參數(shù)如果需要用到絕對(duì)地址的,請(qǐng)使用絕對(duì)地址進(jìn)行答題,

其他方式無效。

2.使用IF函數(shù),對(duì)Sheet1"采購(gòu)表"中的"折扣"列進(jìn)行填充。

要求：

根據(jù)"折扣表"中的商品折扣率,使用相應(yīng)的函數(shù),

將其折扣率填充到采購(gòu)表中的"折扣"列中。

很多同學(xué)喜歡vlookup函數(shù)來做,那就來一個(gè)啦。3.使用公式,對(duì)Sheet1中"采購(gòu)表"的"合計(jì)"列進(jìn)行計(jì)算。

要求：

根據(jù)"采購(gòu)數(shù)量","單價(jià)"和"折扣",計(jì)算采購(gòu)的合計(jì)金額,

將結(jié)果保存在"合計(jì)"列中。

計(jì)算公式：?jiǎn)蝺r(jià)*采購(gòu)數(shù)量*〔1-折扣率4.使用SUMIF函數(shù),計(jì)算各種商品的采購(gòu)總量和采購(gòu)總金額,將結(jié)果保存

在Sheet1中的"統(tǒng)計(jì)表"當(dāng)中相應(yīng)位置。第四套題目：采購(gòu)情況表

1.使用數(shù)組公式,計(jì)算Sheet1"采購(gòu)情況表"中的每種產(chǎn)品的采購(gòu)總額,

將結(jié)果保存到表中的"采購(gòu)總額"列中。

計(jì)算方法為：采購(gòu)總額=單價(jià)*每盒數(shù)量*采購(gòu)盒數(shù)。

2.根據(jù)Sheet1中的"采購(gòu)情況表",使用數(shù)據(jù)庫函數(shù)及已設(shè)置的條件區(qū)域,

計(jì)算以下情況的結(jié)果。

〔a計(jì)算：商標(biāo)為上海,壽命小于100的白熾燈的平均單價(jià),并將

結(jié)果填入Sheet1的G25單元格中；

〔b計(jì)算：產(chǎn)品為白熾燈,其瓦數(shù)大于等于80且小于等于100的數(shù)量,并將

結(jié)果填入Sheet1的G26單元格中。

3.某公司對(duì)各個(gè)部門員工吸煙情況進(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ì)未登記的部門個(gè)數(shù),將結(jié)果保存在B14單元格中；

〔b統(tǒng)計(jì)在登記的部門中,吸煙的部門個(gè)數(shù),將結(jié)果保存在B15單元格中。

4.使用函數(shù),對(duì)Sheet2中的B21單元格中的內(nèi)容進(jìn)行判斷,

判斷其是否為文本,如果是,單元格填充為"TRUE"；如果不是,

單元格填充為"FALSE",并將結(jié)果保存在Sheet2中的B22單元格當(dāng)中。第五套題目：號(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í)間年份之差。2.使用REPLACE函數(shù),對(duì)Sheet1中用戶的號(hào)碼進(jìn)行升級(jí)。

要求：

對(duì)"原號(hào)碼"列中的號(hào)碼進(jìn)行升級(jí)。升級(jí)方法是在區(qū)號(hào)〔0571

后面加上"8”,并將其計(jì)算結(jié)果保存在"升級(jí)號(hào)碼"列的相應(yīng)單元格中。

例如：號(hào)碼"05716742808”升級(jí)后為"057186742808”3.在Sheet1中,使用AND函數(shù),根據(jù)"性別"及"年齡"列中的數(shù)據(jù),判斷所有用戶

是否為大于等于40歲的男性,并將結(jié)果保存在"是否>=40男性"列中。

注意：如果是,保存結(jié)果為TRUE；否則,保存結(jié)果為FALSE。

4.根據(jù)Sheet1中的數(shù)據(jù),對(duì)以下條件,使用統(tǒng)計(jì)函數(shù)進(jìn)行統(tǒng)計(jì)。

要求：

〔a統(tǒng)計(jì)性別為"男"的用戶人數(shù),將結(jié)果填入Sheet2的B2單元格中；

〔b統(tǒng)計(jì)年齡為">40”歲的用戶人數(shù),將結(jié)果填入Sheet2的B3單元格中。第六套題目：房產(chǎn)銷售表

2.使用數(shù)組公式,計(jì)算Sheet1中"房產(chǎn)銷售表"的契稅總額,并保存在

"契稅總額"列中。計(jì)算公式為：契稅總額=契稅*房?jī)r(jià)總額。3.使用函數(shù),根據(jù)Sheet1中"房產(chǎn)銷售表"的結(jié)果,在sheet2中統(tǒng)計(jì)每個(gè)

銷售人員的銷售總額,將結(jié)果保存在Sheet2中的"銷售總額"列中。

4.使用RANK函數(shù),根據(jù)Sheet2中"銷售總額"列的結(jié)果,對(duì)每個(gè)銷售人員的銷售情況

進(jìn)行排序,并將結(jié)果保存在"排名"列當(dāng)中。第七套題目：公司員工人事信息表

1.使用大小寫轉(zhuǎn)換函數(shù),根據(jù)Sheet1中"公司員工人事信息表"的"編號(hào)"列,

對(duì)"新編號(hào)"列進(jìn)行填充。

要求：

把編號(hào)中的小寫字母改為大寫字母,并將結(jié)果保存在"新編號(hào)"列中。

例如,"a001"更改后為"A001"。2.使用文本函數(shù)和時(shí)間函數(shù),根據(jù)Sheet1中"公司員工人事信息表"的"身份證

號(hào)碼"列,計(jì)算用戶的年齡,并保存在"年齡"列中。

注意：

〔a身份證的第7位~第10位表示出生年份；

〔b計(jì)算方法：年齡=當(dāng)前年份-出生年份。其中"當(dāng)前年份"使用

時(shí)間函數(shù)計(jì)算。

3.在Sheet1中,利用數(shù)據(jù)庫函數(shù)及已設(shè)置的條件區(qū)域,根據(jù)以下情況計(jì)算,并將結(jié)果

填入到相應(yīng)的單元格當(dāng)中。

計(jì)算：

獲取具有碩士學(xué)歷,職務(wù)為經(jīng)理助理的員工姓名,

并將結(jié)果保存在Sheet1中的E31單元格中。

4.使用函數(shù),判斷Sheet1中L12和M12單元格中的文本字符串是否完全相同。

注意：

〔a如果完全相同,結(jié)果保存為TRUE,否則保存為FALSE。

〔b將結(jié)果保存在Sheet1中的N11單元格中。

第八套題目：公務(wù)員考試成績(jī)表

1.使用IF函數(shù),對(duì)Sheet1中的"學(xué)位"列進(jìn)行自動(dòng)填充。

要求：

填充的內(nèi)容根據(jù)"學(xué)歷"列的內(nèi)容來確定〔假定學(xué)生均已獲得相應(yīng)學(xué)位：

-博士研究生－博士

-碩士研究生－碩士

-本科－學(xué)士

-其他－無

2.使用數(shù)組公式,在Sheet1中計(jì)算：

〔1計(jì)算筆試比例分,并將結(jié)果保存在"公務(wù)員考試成績(jī)表"中的"筆試比例分"中。

計(jì)算方法為：筆試比例分=〔筆試成績(jī)/3*60％

〔2計(jì)算面試比例分,并將結(jié)果保存在"公務(wù)員考試成績(jī)表"中的"面試比例分"中。

計(jì)算方法為：面試比例分=面試成績(jī)*40％

〔3計(jì)算總成績(jī),并將結(jié)果保存在"公務(wù)員考試成績(jī)表"中的"總成績(jī)"中。

計(jì)算方法為：總成績(jī)=筆試比例分+面試比例分

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ì)出版社名稱為"高等教育出版社"的書的種類數(shù),

并將結(jié)果保存在sheet1中L2單元格中；

〔b統(tǒng)計(jì)訂購(gòu)數(shù)量大于110且小于850的書的種類數(shù),

并將結(jié)果保存在sheet1中L3單元格中。

3.使用函數(shù),計(jì)算每個(gè)用戶所訂購(gòu)圖書所需支付的金額總數(shù),并將結(jié)果

保存在Sheet1中"用戶支付情況表"的"支付總額"列中。

4.使用函數(shù),判斷Sheet2中的年份是否為閏年,如果是,結(jié)果保存"閏年",

如果不是,則結(jié)果保存"平年",并將結(jié)果保存在"是否為閏年"列中。

說明：

閏年定義：年數(shù)能被4整除而不能被100整除,或者能被400整除的年份。第十套題目：零件檢測(cè)結(jié)果表

1.使用數(shù)組公式,根據(jù)Sheet1中"零件檢測(cè)結(jié)果表"的"外輪直徑"和

"內(nèi)輪直徑"列,計(jì)算內(nèi)外輪差,并將結(jié)果表保存在"輪差"列中。

計(jì)算方法為：輪差=外輪直徑-內(nèi)輪直徑。2.使用IF函數(shù),對(duì)Sheet1中"零件檢測(cè)結(jié)果表"的"檢測(cè)結(jié)果"列進(jìn)行填充。

要求：

〔a如果"輪差"<4mm,測(cè)量結(jié)果保存為"合格",否則為"不合格"；

〔b將計(jì)算結(jié)果保存在Sheet1中"零件檢測(cè)結(jié)果表"的"檢測(cè)結(jié)果"列。3.使用統(tǒng)計(jì)函數(shù),根據(jù)以下要求進(jìn)行計(jì)算,并將結(jié)果保存在相應(yīng)位置。

要求：

〔a統(tǒng)計(jì)：輪差為0的零件個(gè)數(shù),并將結(jié)果保存在Sheet1中K4單元格中；

〔b統(tǒng)計(jì)：零件的合格率,并將結(jié)果保存在Sheet1中的K5單元格中。

注意：

〔a計(jì)算合格率時(shí),分子分母必須用函數(shù)計(jì)算；

〔b合格率的計(jì)算結(jié)果保存為數(shù)值型小數(shù)點(diǎn)后兩位。4.使用文本函數(shù),判斷Sheet1中"字符串2”在"字符串1”中的起始位置

并把返回結(jié)果保存在Sheet1中的K9單元格中。

第十一套題目：停車情況記錄表

1.使用HLOOKUP函數(shù),對(duì)Sheet1"停車情況記錄表"中的"單價(jià)"列進(jìn)行填充。

要求：

根據(jù)Sheet1中的"停車價(jià)目表"價(jià)格,使用HLOOKUP函數(shù)對(duì)"停車情況

記錄表"中的"單價(jià)"列根據(jù)不同的車型進(jìn)行填充。

注意：

函數(shù)中如果需要用到絕對(duì)地址的將使用絕對(duì)地址進(jìn)行計(jì)算,其他方式無效。3.使用函數(shù)公式,對(duì)"停車情況記錄表"的停車費(fèi)用進(jìn)行計(jì)算。

要求：

根據(jù)Sheet1停放時(shí)間的長(zhǎng)短計(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)

4.使用統(tǒng)計(jì)函數(shù),對(duì)Sheet1中的"停車情況記錄表"根據(jù)下列條件進(jìn)行統(tǒng)計(jì)。

要求：

〔a統(tǒng)計(jì)停車費(fèi)用大于等于40元的停車記錄條數(shù),并將結(jié)果保存在J8單元格中；

〔b統(tǒng)計(jì)最高的停車費(fèi)用,并將結(jié)果保存在J9單元格中。第十二套題目：通訊費(fèi)年度計(jì)劃表

1.使用VLOOKUP函數(shù),根據(jù)Sheet1中的"崗位最高限額明細(xì)表",填充

"通訊費(fèi)年度計(jì)劃表"中的"崗位標(biāo)準(zhǔn)"列。

2.使用INT函數(shù),計(jì)算Sheet1中"通訊費(fèi)年度計(jì)劃表"的"預(yù)計(jì)報(bào)銷總時(shí)間"列。

要求：

〔a每月以30天計(jì)算；

〔b將結(jié)果填充在"預(yù)計(jì)報(bào)銷總時(shí)間"列中。

3.使用數(shù)組公式,計(jì)算Sheet1中"通訊費(fèi)年度計(jì)劃表"的"年度費(fèi)用"列。

計(jì)算方法為：年度費(fèi)用=崗位標(biāo)準(zhǔn)*預(yù)計(jì)報(bào)銷總時(shí)間。4.使用函數(shù),根據(jù)Sheet1中"通訊費(fèi)年度計(jì)劃表"的"年度費(fèi)用"列,計(jì)算預(yù)算總金額。

要求：

〔a將結(jié)果保存在Sheet1中的C2單元格中。

〔b并根據(jù)C2單元格中的結(jié)果,轉(zhuǎn)換為金額大寫形式,保存在Sheet1中的F2單元格中。

第十三套題目：圖書訂購(gòu)信息表

1.使用IF函數(shù),根據(jù)Sheet1中的"圖書訂購(gòu)信息表"中的"學(xué)號(hào)"列對(duì)"所屬學(xué)院"

列進(jìn)行填充。

要求：

根據(jù)每位學(xué)生學(xué)號(hào)的第七位填充對(duì)應(yīng)的"所屬學(xué)院"。

-學(xué)號(hào)第七位為1－計(jì)算機(jī)學(xué)院

-學(xué)號(hào)第七位為0－電子信息學(xué)院

2.使用COUNTBLANK函數(shù),對(duì)Sheet1中的"圖書訂購(gòu)信息表"中的"訂書種類數(shù)"

列進(jìn)行填充。

注意：

〔a其中"1”表示該同學(xué)訂購(gòu)該圖書,空格表示沒有訂購(gòu)；

〔b將結(jié)果保存在Sheet1中的"圖書訂購(gòu)信息表"中的"訂書種類數(shù)"列。

3.使用公式,對(duì)Sheet1中的"圖書訂購(gòu)信息表"中的"應(yīng)繳總額〔元"列進(jìn)行填充。

計(jì)算方法為：應(yīng)繳總額=C語言*單價(jià)+高等數(shù)學(xué)*單價(jià)+

大學(xué)語文*單價(jià)+大學(xué)英語*單價(jià)。

4.使用統(tǒng)計(jì)函數(shù),根據(jù)Sheet1中"圖書訂購(gòu)信息表"的數(shù)據(jù),統(tǒng)計(jì)應(yīng)繳總額大于100元

的學(xué)生人數(shù),將結(jié)果保存在Sheet1的M9單元格中。第十四套題目：學(xué)生成績(jī)表

2.使用文本函數(shù)中的一個(gè)函數(shù),在Sheet1中,利用"學(xué)號(hào)"列的數(shù)據(jù),根據(jù)以下要求

獲得考生所考級(jí)別,并將結(jié)果填入"級(jí)別"列中。

要求：

〔a學(xué)號(hào)中的第八位指示的考生所考級(jí)別,例如："085200821023080"中的"2"

標(biāo)識(shí)了該考生所考級(jí)別為二級(jí)

〔b在"級(jí)別"列中,填入的數(shù)據(jù)是函數(shù)的返回值。3.使用統(tǒng)計(jì)函數(shù),根據(jù)以下要求對(duì)Sheet1中"學(xué)生成績(jī)表"的數(shù)據(jù)進(jìn)行統(tǒng)計(jì)。

要求：

〔a統(tǒng)計(jì)"考1級(jí)的考生人數(shù)",并將計(jì)算結(jié)果填入到N2單元格中；

〔b統(tǒng)計(jì)"考試通過人數(shù)〔>=60",并將計(jì)算結(jié)果填入到N3單元格中；

〔c統(tǒng)計(jì)"全體1級(jí)考生的考試平均分",并將計(jì)算結(jié)果填入到N4單元格中。

〔其中,計(jì)算時(shí)候的分母直接使用"N2”單元格的數(shù)據(jù)

4.使用財(cái)務(wù)函數(shù),根據(jù)以下要求對(duì)Sheet2中的數(shù)據(jù)進(jìn)行計(jì)算。

要求：

〔a根據(jù)"投資情況表1”中的數(shù)據(jù),計(jì)算10年以后得到的金額,

并將結(jié)果填入到B7單元格中；

〔b根據(jù)"投資情況表2”中的數(shù)據(jù),計(jì)算預(yù)計(jì)投資金額,并將結(jié)