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

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

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

題目要求

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

要求：

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

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

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

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

題一：使用VLOOKUP函數(shù),對Sheet1中的"3月份銷售統(tǒng)計(jì)表"的"產(chǎn)品名稱"列和

"產(chǎn)品單價"列進(jìn)行填充。

要求：根據(jù)"企業(yè)銷售產(chǎn)品清單",使用VLOOKUP函數(shù),將產(chǎn)品名稱和產(chǎn)品單價

填充到"3月份銷售統(tǒng)計(jì)表"的"產(chǎn)品名稱"列和"產(chǎn)品單價"列中。

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

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

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

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

第三套題目：采購表

1.

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

要求：

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

充到采購表中的"單價"列中。

注意：

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

其他方式無效。

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

要求：

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

將其折扣率填充到采購表中的"折扣"列中。

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

要求：

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

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

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

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

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

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

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

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

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

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

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

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

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

3.某公司對各個部門員工吸煙情況進(jìn)行統(tǒng)計(jì),作為人力資源搭配的一個

數(shù)據(jù)依據(jù)。對于調(diào)查對象,只能回答Y〔吸煙或者N〔不吸煙。

根據(jù)調(diào)查情況,制做出Sheet2中的"吸煙情況調(diào)查表"。

請使用函數(shù),統(tǒng)計(jì)符合以下條件的數(shù)值。

〔a統(tǒng)計(jì)未登記的部門個數(shù),將結(jié)果保存在B14單元格中；

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

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

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

單元格填充為"FALSE",并將結(jié)果保存在Sheet2中的B22單元格當(dāng)中。第五套題目：號碼

1.使用時間函數(shù),對Sheet1中用戶的年齡進(jìn)行計(jì)算。

要求：

假設(shè)當(dāng)前時間是"2008-11-15",結(jié)合用戶的出生年月,計(jì)算用戶的年齡,

并將其計(jì)算結(jié)果保存在"年齡"列當(dāng)中。計(jì)算方法為兩個時間年份之差。2.使用REPLACE函數(shù),對Sheet1中用戶的號碼進(jìn)行升級。

要求：

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

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

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

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

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

4.根據(jù)Sheet1中的數(shù)據(jù),對以下條件,使用統(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ì)算公式為：契稅總額=契稅*房價總額。3.使用函數(shù),根據(jù)Sheet1中"房產(chǎn)銷售表"的結(jié)果,在sheet2中統(tǒng)計(jì)每個

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

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

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

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

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

要求：

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

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

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

注意：

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

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

時間函數(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ù)員考試成績表

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

要求：

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

-博士研究生－博士

-碩士研究生－碩士

-本科－學(xué)士

-其他－無

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

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

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

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

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

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

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

4.在Sheet2中,使用RANK函數(shù),根據(jù)"總成績"列對所有考生進(jìn)行排名。

要求：將排名結(jié)果保存在"排名"列中。第九套題目：教材訂購情況表

2.使用統(tǒng)計(jì)函數(shù),對Sheet1中"教材訂購情況表"的結(jié)果按以下條件進(jìn)行統(tǒng)計(jì),

并將結(jié)果保存在Sheet1中的相應(yīng)位置。

要求：

〔a統(tǒng)計(jì)出版社名稱為"高等教育出版社"的書的種類數(shù),

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

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

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

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

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

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

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

說明：

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

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

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

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

要求：

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

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

要求：

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

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

注意：

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

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

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

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

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

要求：

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

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

注意：

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

要求：

根據(jù)Sheet1停放時間的長短計(jì)算停車費(fèi)用,將計(jì)算結(jié)果填入到"停車情況

記錄表"的"應(yīng)付金額"列中。

注意：

〔a停車按小時收費(fèi),對于不滿一個小時的按照一個小時計(jì)費(fèi)；

〔b對于超過整點(diǎn)小時數(shù)十五分鐘〔包含十五分鐘的多累積一個小時。

〔例如1小時23分,將以2小時計(jì)費(fèi)

4.使用統(tǒng)計(jì)函數(shù),對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)銷總時間"列。

要求：

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

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

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

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

要求：

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

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

第十三套題目：圖書訂購信息表

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

列進(jìn)行填充。

要求：

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

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

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

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

列進(jìn)行填充。

注意：

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

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

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

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

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

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

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

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

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

要求：

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

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

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

要求：

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

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

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

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

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

要求：

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

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

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