2023年3月計(jì)算機(jī)二級(jí)ecel答案及解析

第2題：（電子表格題，本題30分）

請(qǐng)?jiān)凇敬痤}】菜單下選擇【進(jìn)入考生文獻(xiàn)夾】命令,并按照題目規(guī)定完畢下面的

操作。

注意：以下的文獻(xiàn)必須都保存在考生文獻(xiàn)夾下。

在考生文獻(xiàn)夾下打開(kāi)文檔EXCEL.XLSXo

【背景素材】

財(cái)務(wù)部助理小王需要向主管報(bào)告2023年度公司差旅報(bào)銷情況，現(xiàn)在請(qǐng)按照如

下需求，在EXCEL.XLSX文檔中完畢工作：

（1）在"費(fèi)用報(bào)銷管理"工作表"日期"列的所有單元格中，標(biāo)注每個(gè)報(bào)銷日期屬

于星期幾，例如日期為"2023年1月20日"的單元格應(yīng)顯示為"2023年1

月20日星期日1日期為"2023年1月21日"的單元格應(yīng)顯示為"2023年

1月21日星期一

（2）假如"日期"列中的日期為星期六或星期日，則在"是否加班"列的單元格中顯

示"是"，否則顯示"否"（必須使用公式）。

（3）使用公式記錄每個(gè)活動(dòng)地點(diǎn)所在的省份或直轄市，并將其填寫在"地區(qū)"列所

相應(yīng)的單元格中，例如"北京市"、"浙江省"。

（4）依據(jù)"費(fèi)用類別編號(hào)"列內(nèi)容，使用VL00KUP函數(shù)，生成"費(fèi)用類別"列內(nèi)容。

對(duì)照關(guān)系參考"費(fèi)用類別"工作表。

（5）在"差旅成本分析報(bào)告"工作表B3單元格中，記錄2023年第二季度發(fā)生在

北京市的差旅費(fèi)用總金額。

（6）在"差旅成本分析報(bào)告”工作表B4單元格中，記錄2023年員工錢順卓

報(bào)銷的火車票費(fèi)用總額。

（7）在"差旅成本分析報(bào)告"工作表B5單元格中，記錄2023年差旅費(fèi)用中，飛機(jī)

票費(fèi)用占所有報(bào)銷費(fèi)用的比例，并保存2位小數(shù)。

（8）在"差旅成本分析報(bào)告"工作表B6單元格中，記錄2023年發(fā)生在周末（星

期六和星期日)的通訊補(bǔ)貼總金額。

答疑：(1)【解題環(huán)節(jié)】

環(huán)節(jié)1：打開(kāi)考生文獻(xiàn)夾下的EXCELXLSXo

環(huán)節(jié)2：在"費(fèi)用報(bào)銷管理"工作表中，選中"日期"數(shù)據(jù)列，單擊鼠標(biāo)右鍵，在

彈出的快捷菜單中選擇"設(shè)立單元格格式"命令，彈出"設(shè)立單元格格式"對(duì)話框。

切換至"數(shù)字"選項(xiàng)卡，在"分類"列表框中選擇"自定義"命令，在右側(cè)的"示例"

組中"類型"列表框中輸入"yyyy"年"m"月"d"日"aaaa設(shè)立完畢后單

擊"擬定"按鈕即可。

(2)【解題環(huán)節(jié)】

環(huán)節(jié)：在"費(fèi)用報(bào)銷管理”工作表的H3單元格中輸入"=IF(WEEKDAY(A3,

2)>5,"是"，"否")"，表達(dá)在星期六或者星期日情況下顯示"是"，否則顯示"否",

按"Enter"鍵確認(rèn)。然后向下填充公式到最后一個(gè)日期即可完畢設(shè)立。

(3)【解題環(huán)節(jié)】

環(huán)節(jié)：在"費(fèi)用報(bào)銷管理"工作表的D3單元格中輸入"=LEFT(C3,3)",表達(dá)

取當(dāng)前文字左側(cè)的前三個(gè)字符，按"Enter"鍵確認(rèn)。然后向下填充公式到最后一

個(gè)日期即可完畢設(shè)立。

(4)【解題環(huán)節(jié)】

環(huán)節(jié):在"費(fèi)用報(bào)銷管理”工作表的F3單元格中輸入"=VLOOKUP(E3,費(fèi)用類別！

$A$3：$B$12,2.FALSE)",按"Enter"后完畢"費(fèi)用類別"的填充。然后向

下填充公式到最后一個(gè)日期即可完畢設(shè)立。

(5)【解題環(huán)節(jié)】

環(huán)節(jié)：選中A列單元格，在【編輯】選項(xiàng)組中，單擊"排序和篩選"下拉菜單中的"

升序"按鈕，在彈出的"排序提醒"對(duì)話框中單擊"排序"按鈕。在"差旅成本分析

報(bào)告"工作表的B3單元格中輸入"=SUMPRODUCT(1*(費(fèi)用報(bào)銷管理！D74:

D340="北京市"），費(fèi)用報(bào)銷管理!G74：G340）",按"Enter"鍵確認(rèn)。

（6）【解題環(huán)節(jié)】

環(huán)節(jié)：在"差旅成本分析報(bào)告"工作表的B4單元格中輸入"=SUMPRODUCT（1*

（費(fèi)用報(bào)銷管理!B3：B401="錢順卓"），1*（費(fèi)用報(bào)銷管理!F3：F401="火車票"）,

費(fèi)用報(bào)銷管理！G3：G401",按"Enter"鍵確認(rèn)。

（7）【解題環(huán)節(jié)】

環(huán)節(jié)：在"差旅成本分析報(bào)告"工作表的B5單元格中輸入"=SUMPRODU

CT（1*（費(fèi)用報(bào)銷管理!F3：F401="飛機(jī)票"），費(fèi)用報(bào)銷管理!G3：G40D/SUM（費(fèi)

用報(bào)銷管理!G3：G40D"，按"Enter"鍵確認(rèn)，并設(shè)立數(shù)字格式，保存兩位小數(shù)。

（8）【解題環(huán)節(jié)】

環(huán)節(jié)：在"差旅成本分析報(bào)告"工作表的B6單元格中輸入"=SUMPR0DUCT（（費(fèi)

用報(bào)銷管理！H3：H401="是"）*（費(fèi)用報(bào)銷管理!F3：F401="通訊補(bǔ)貼"），費(fèi)

用報(bào)銷管理!63：6401）",按"Enter"鍵確認(rèn)。

第2題：（電子表格題，本題30分）

請(qǐng)?jiān)凇敬痤}】菜單下選擇【進(jìn)入考生文獻(xiàn)夾】命令，并按照題目規(guī)定完畢下面的

操作。

注意：以下的文獻(xiàn)必須都保存在考生文獻(xiàn)夾下。

期末考試結(jié)束了，初三（14）班的班主任助理王老師需要對(duì)本班學(xué)生的各科考試

成績(jī)進(jìn)行記錄分析，并為每個(gè)學(xué)生制作一份成績(jī)告知單下發(fā)給家長(zhǎng)。按照下列

規(guī)定完畢該班的成績(jī)記錄工作并按原文獻(xiàn)名進(jìn)行保存：

1.打開(kāi)工作簿“學(xué)生成績(jī).X1SX”，在最左側(cè)插入一個(gè)空白工作表，重命名

為“初三學(xué)生檔案”，并將該工作表標(biāo)簽顏色設(shè)為“紫色（標(biāo)準(zhǔn)色）

2.將以制表符分隔的文本文獻(xiàn)“學(xué)生檔案.txt”自Al單元格開(kāi)始導(dǎo)入到工作

表“初三學(xué)生檔案”中，注意不得改變?cè)紨?shù)據(jù)的排列順序。將第1列數(shù)據(jù)從左

到右依次提成“學(xué)號(hào)”和“姓名”兩列顯示。最后創(chuàng)建一個(gè)名為“檔案”、包含

數(shù)據(jù)區(qū)域A1：G56、包含標(biāo)題的表，同時(shí)刪除外部鏈接。

3.在工作表“初三學(xué)生檔案”中，運(yùn)用公式及函數(shù)依次輸入每個(gè)學(xué)生的性別“男”

或“女”、出生日期“XXXX年XX月XX日”和年齡。其中：身份證號(hào)的倒數(shù)

第2位用于判斷性別，奇數(shù)為男性，偶數(shù)為女性；身份證號(hào)的第7~14位代表出

生年月日；年齡需要按周歲計(jì)算，滿1年才計(jì)1歲。最后適當(dāng)調(diào)整工作表的行高

和列寬、對(duì)齊方式等，以方便閱讀。

4.參考工作表“初三學(xué)生檔案”，在工作表“語(yǔ)文”中輸入與學(xué)號(hào)相應(yīng)的“姓名”；

按照平時(shí)、期中、期末成績(jī)各占30%,30%、40%的比例計(jì)算每個(gè)學(xué)生的“學(xué)期

成績(jī)”并填入相應(yīng)單元格中；按成績(jī)由高到低的順序記錄每個(gè)學(xué)生的“學(xué)期成績(jī)”

排名并按“第n名”的形式填入“班級(jí)名次”列中；按照下列條件填寫“期末總

評(píng)”：

語(yǔ)文、數(shù)學(xué)的學(xué)期成績(jī)其他科目的學(xué)期成績(jī)期末總評(píng)

2102290優(yōu)秀

284275良好

272260及格

（72<60不合格

5.將工作表''語(yǔ)文”的格式所有應(yīng)用到其他科目工作表中，涉及行高（各行行

高均為22默認(rèn)單位）和列寬（各列列寬均為14默認(rèn)單位）。并按上述4.中的

規(guī)定依次輸入或記錄其他科目的“姓名”、“學(xué)期成績(jī)”、“班級(jí)名次”和“期末總

評(píng)”。

6.分別將各科的“學(xué)期成績(jī)”引入到工作表”期末總成績(jī)”的相應(yīng)列中，在工

作表“期末總成績(jī)”中依次引入姓名、計(jì)算各科的平均分、每個(gè)學(xué)生的總分，并

按成績(jī)由高到底的順序記錄每個(gè)學(xué)生的總分排名、并以1、2、3……形式標(biāo)記名

次，最后將所有成績(jī)的數(shù)字格式設(shè)為數(shù)值、保存兩位小數(shù)。

7.在工作表“期末總成績(jī)”中分別用紅色（標(biāo)準(zhǔn)色）和加粗格式標(biāo)出各科第一

名成績(jī)。同時(shí)將前10名的總提成績(jī)用淺藍(lán)色填充。

8.調(diào)整工作表”期末總成績(jī)”的頁(yè)面布局以便打?。杭垙埛较?yàn)闄M向，縮減打印

輸出使得所有列只占一個(gè)頁(yè)面寬(但不得縮小列寬)，水平居中打印在紙上。

答疑：1.【解題環(huán)節(jié)】

環(huán)節(jié)：打開(kāi)素材文獻(xiàn)”學(xué)生成績(jī).X1SX”，單擊工作表最右側(cè)的“插入工作表”

按鈕，然后雙擊工作表標(biāo)簽，將其重命名為“初三學(xué)生檔案二在該工作表標(biāo)簽上

單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“工作表標(biāo)簽顏色”，在彈出的級(jí)聯(lián)菜單

中選擇標(biāo)準(zhǔn)色中的“紫色

2.【解題環(huán)節(jié)】

環(huán)節(jié)1：選中A1單元格，單擊【數(shù)據(jù)】選項(xiàng)卡下【獲取外部數(shù)據(jù)】組中的“自

文本”按鈕，彈出“導(dǎo)入文本文獻(xiàn)”對(duì)話框，在該對(duì)話框中選擇考生文獻(xiàn)夾下的

“學(xué)生檔案.txt”選項(xiàng)，然后單擊“導(dǎo)入”按鈕。

環(huán)節(jié)2：在彈出的對(duì)話框中選擇“分隔符號(hào)”單選按鈕，將“文獻(xiàn)原始格式”設(shè)立

為“54936：簡(jiǎn)體中文(GB18030)”。單擊“下一步”按鈕，只勾選“分隔符”

列表中的“Tab鍵”復(fù)選項(xiàng)。然后單擊“下一步”按鈕，選中“身份證號(hào)碼”列，

然后點(diǎn)擊“文本”單選按鈕，單擊“完畢"按鈕，在彈出的對(duì)話框中保持默認(rèn)，

單擊“擬定”按鈕。

環(huán)節(jié)3：選中B列單元格，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“插入”選項(xiàng)。

然后選中A1單元格，將光標(biāo)置于“學(xué)號(hào)”和“名字”之間，按3次空格鍵，然后

選中A列單元格，單擊【數(shù)據(jù)工具】組中的“分列”按鈕，在彈出的對(duì)話框中選

擇“固定寬度”單選按鈕，單擊“下一步”按鈕，然后建立分列線。單擊“下一

步”按鈕，保持默認(rèn)設(shè)立，單擊“完畢”按鈕。

環(huán)節(jié)4：選中A1：G56單元格，單擊【開(kāi)始】選項(xiàng)卡下【樣式】組中的“套用表

格格式”下拉按鈕，在彈出的下拉列表中選擇“表樣式中檔深淺2”。

環(huán)節(jié)5：在彈出的對(duì)話框中勾選“表包含標(biāo)題”復(fù)選框，單擊“擬定”按鈕，然后

再在彈出的對(duì)話框中選擇“是"按鈕。在【設(shè)計(jì)】選項(xiàng)卡下【屬性】組中將“表

名稱”設(shè)立為檔案。3.【解題環(huán)節(jié)】

環(huán)節(jié)1：選中D2單元格，在該單元格內(nèi)輸入函數(shù)“=IF(M0D(MID(C2,17,1),

2)=1,"男"，"女")"，按Enter鍵完畢操作。然后運(yùn)用自動(dòng)的填充功能對(duì)其他

單元格進(jìn)行填充。

環(huán)節(jié)2選中E2單元格，在該單元格內(nèi)輸入函數(shù)“=-TEXT(MID(C2,7,8),"0-

00-00")",按Enter鍵完畢操作，運(yùn)用自動(dòng)填充功能對(duì)剩余的單元格進(jìn)行填充。

然后選擇E2：E56單元格，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“設(shè)立單元格

格式”選項(xiàng)。切換至“數(shù)字”選項(xiàng)卡，將“分類"設(shè)立為“日期”，然后單擊“擬

定”按鈕。

環(huán)節(jié)3：選中F2單元格，在該單元格內(nèi)輸入函數(shù)”=DATEDIF(―TEXT(MID(C

2,7,8),"0-00-00").TODAY(),"y")",按Enter鍵，運(yùn)用自動(dòng)的填充功

能對(duì)其他單元格進(jìn)行填充。

環(huán)節(jié)4：選中A1：G56區(qū)域，單擊【開(kāi)始】選項(xiàng)卡下【對(duì)齊方式】組中的“居中”

按鈕。適當(dāng)調(diào)整表格的行高和列寬。

4.【解題環(huán)節(jié)】

環(huán)節(jié)1：進(jìn)入到“語(yǔ)文”工作表中，選擇B2單元格，在該單元格內(nèi)輸入函數(shù)”=

VLOOKUP(A2,初三學(xué)生檔案!$A$2：$B$56,2,0)”，按Enter鍵完畢操作。然

后運(yùn)用自動(dòng)的填充功能對(duì)其他單元格進(jìn)行填充。

環(huán)節(jié)2：選擇F2單元格，在該單元格中輸入函數(shù)”=SUM(C2*309D+(D2*30%)+(E

2*40%)"，按Enter鍵確認(rèn)操作。

環(huán)節(jié)3：選擇G2單元格，在該單元格內(nèi)輸入函數(shù)"="第"&RANK(F

2,$F$2：$F$45)&"名"”，然后運(yùn)用自動(dòng)填充功能對(duì)其他單元格進(jìn)行填充。

環(huán)節(jié)4：選擇H2單元格，在該單元格中輸入公式<<=IF(F2>=102/^^",IF(F2

>=84,"良好"，IF(F2>=72,"及格",IF(F2>72,"及格"，"不及格"))))”，

按Enter鍵完畢操作，然后運(yùn)用自動(dòng)填充對(duì)其他單元格進(jìn)行填充。

5.【解題環(huán)節(jié)】

環(huán)節(jié)1：選擇“語(yǔ)文”工作表中Al：H45單元格區(qū)域，按Ctr1+C鍵進(jìn)行復(fù)制，

進(jìn)入到“數(shù)學(xué)”工作表中，選擇A1：H45區(qū)域，單擊鼠標(biāo)右鍵，在彈出的快捷菜

單中選擇“粘貼選項(xiàng)”下的“格式”按鈕。

環(huán)節(jié)2：繼續(xù)選擇“數(shù)學(xué)”工作表中的Al：H45區(qū)域，單擊【開(kāi)始】選項(xiàng)卡下

【單元格】組中的“格式”下拉按鈕，在彈出的下拉列表中選擇“行高”選項(xiàng)，

在彈出的對(duì)話框中將“行高”設(shè)立為22,單擊“擬定”按鈕。單擊“格式”下

拉按鈕，在彈出的下拉列表中選擇“列寬”選項(xiàng)，在彈出的對(duì)話框中將“列寬”

設(shè)立為14,單擊“擬定”按鈕。

環(huán)節(jié)3：使用同樣的方法為其他科目的工作表設(shè)立相同的格式，涉及行高和列寬。

環(huán)節(jié)4：將“語(yǔ)文”工作表中的公式粘貼到數(shù)學(xué)科目工作表中的相應(yīng)的單元格內(nèi)，

然后運(yùn)用自動(dòng)填充功能對(duì)單元格進(jìn)行填充。

環(huán)節(jié)5：在“英語(yǔ)”工作表中的H2單元格中輸入公式“=IF（F2〉=90,"優(yōu)秀

",IF（F2〉=75,"良好"，IF（F2>=60,"及格"，IF（F2>60,"及格"，"不及格

"））））"，按Enter鍵完畢操作，然后運(yùn)用自動(dòng)填充對(duì)其他單元格進(jìn)行填充。

環(huán)節(jié)6：將“英語(yǔ)”工作表H2單元格中的公式粘貼到“物理”、“化學(xué)”、“品德”、

“歷史”工作表中的H2單元格中，然后運(yùn)用自動(dòng)填充功能對(duì)其他單元格進(jìn)行填

充o

6.【解題環(huán)節(jié)】

環(huán)節(jié)1：進(jìn)入到“期末總成績(jī)”工作表中，選擇B3單元格，在該單元格內(nèi)輸入公

式“=VL00KUP（A3,初三學(xué)生檔案！$A$2：$B$56,2,0）"，按Enter鍵完畢

操作，然后運(yùn)用自動(dòng)填充功能將其填充至B46單元格。

環(huán)節(jié)2：選擇C3單元格，在該單元格內(nèi)輸入公式”=VL00KUP（A3,語(yǔ)文!$A$2：

$F$45,6,0）"，按Enter鍵完畢操作，然后運(yùn)用自動(dòng)填充功能將其填充至

C46單元格。

環(huán)節(jié)3：選擇D3單元格，在該單元格內(nèi)輸入公式”=VLOOKUP（A3,數(shù)學(xué)!$A$2：

$F$45,6,0）"，按Enter鍵完畢操作，然后運(yùn)用自動(dòng)填充功能將其填充至D46

單元格。

環(huán)節(jié)4：使用相同的的方法為其他科目填充平均分。選擇J3單元格，在該單元

格內(nèi)輸入公式”=SUM（C3：13）"，按Enter鍵，然后運(yùn)用自動(dòng)填充功能將其填

充至J46單元格。

環(huán)節(jié)5：選擇A3：K46單元格，單擊【開(kāi)始】選項(xiàng)卡【編輯】組中“排序和篩選”

下拉按鈕，在彈出的下拉列表中選擇''自定義排序”選項(xiàng)，彈出''排序”對(duì)話框，

在該對(duì)話框中將''重要關(guān)鍵字”設(shè)立為“總分”，將“排序依據(jù)”設(shè)立為“數(shù)值”，

將“順序”設(shè)立為“降序”，單擊“擬定”按鈕。

環(huán)節(jié)6：在K3單元格內(nèi)輸入數(shù)字1,然后按住Ctrl鍵，運(yùn)用自動(dòng)填充功能將其填

充至K46單元格。

環(huán)節(jié)7：選擇C47單元格，在該單元格內(nèi)輸入公式JAVERAGE（C3：C46）”，按

Enter鍵完畢操作，運(yùn)用自動(dòng)填充功能進(jìn)行將其填充至J47單元格。

環(huán)節(jié)8：選擇C3：J47單元格，在選擇的單元格內(nèi)單擊鼠標(biāo)右鍵，在彈出的快捷菜

單中選擇“設(shè)立單元格格式”選項(xiàng)。在彈出的對(duì)話框中選擇“數(shù)字”選項(xiàng)卡，將

“分類”設(shè)立為數(shù)值，將“小數(shù)位數(shù)”設(shè)立為2,單擊“擬定”按鈕。

7.【解題環(huán)節(jié)】

環(huán)節(jié)1：選擇C3：C46單元格，單擊【開(kāi)始】選項(xiàng)卡下【樣式】組中的“條件

格式”按鈕，在彈出的下拉列表中選擇“新建規(guī)則”選項(xiàng)，在彈出的對(duì)話框中將

“選擇規(guī)則類型”設(shè)立為“僅對(duì)排名靠前或靠后的數(shù)值設(shè)立格式”，然后將“編

輯規(guī)則說(shuō)明”設(shè)立為"前”、“1明

環(huán)節(jié)2：?jiǎn)螕簟案袷健卑粹o,在彈出的對(duì)話框中將“字形”設(shè)立為加粗，將“顏色”

設(shè)立為標(biāo)準(zhǔn)色中的“紅色”，單擊兩次“擬定”按鈕。按同樣的操作方式為其他

六科分別用紅色和加粗標(biāo)出各科第一名成績(jī)。

環(huán)節(jié)3：選擇J3：J12單元格，單擊鼠標(biāo)右鍵，在彈出的快捷菜單中選擇“設(shè)立單

元格格式”選項(xiàng)，在彈出的對(duì)話框中切換至''填充”選項(xiàng)卡，然后單擊“淺藍(lán)”

顏色塊，單擊“擬定”按鈕。

8.【解題環(huán)節(jié)】

環(huán)節(jié)1：在【頁(yè)面邊距】選項(xiàng)卡下【頁(yè)面設(shè)立】組中單擊對(duì)話框啟動(dòng)器按鈕，在

彈出的對(duì)話框中切換至“頁(yè)邊距”選項(xiàng)卡勾選“居中方式”選項(xiàng)組中的“水平”

復(fù)選框。

環(huán)節(jié)2：切換至“頁(yè)面”選項(xiàng)卡，將“方向”設(shè)立為橫向。選擇“縮放”選項(xiàng)組

下的“調(diào)整為”單選按鈕，將其設(shè)立為1頁(yè)寬1頁(yè)高，單擊“擬定”按鈕。

第2題：（電子表格題，本題30分）

請(qǐng)?jiān)凇敬痤}】菜單下選擇【進(jìn)入考生文獻(xiàn)夾】命令,并按照題目規(guī)定完畢下面的

操作。

小林是北京某師范大學(xué)財(cái)務(wù)處的會(huì)計(jì)，計(jì)算機(jī)系計(jì)算機(jī)基礎(chǔ)室提交了該教研室

2023年的課程授課情況，希望財(cái)務(wù)處盡快核算并發(fā)放他們室的課時(shí)費(fèi)。請(qǐng)根據(jù)

考生文獻(xiàn)夾下"素材.xlsx"中的各種情況,幫助小林核算出計(jì)算機(jī)基礎(chǔ)室2023

年度每個(gè)教員的課時(shí)費(fèi)情況。具體規(guī)定如下：

1.將"素材.xlsx"另存為"課時(shí)費(fèi).xlsx"的文獻(xiàn)，所有的操作基于此新保存

好的文獻(xiàn)。

2.將"課時(shí)費(fèi)登記表"標(biāo)簽顏色更改為紅色，將第一行根據(jù)表格情況合并為一個(gè)單

元格，并設(shè)立合適的字體、字號(hào)，使其成為該工作表的標(biāo)題。對(duì)A2：122區(qū)域

套用合適的中檔深淺的、帶標(biāo)題行的表格格式。前6列對(duì)齊方式設(shè)為居中；其余

與數(shù)值和金額有關(guān)的列，標(biāo)題為居中，值為右對(duì)齊，學(xué)時(shí)數(shù)為整數(shù),金額為貨幣樣

式并保存2位小數(shù)。

3."課時(shí)費(fèi)登記表"中的F至I列中的空白內(nèi)容必須采用公式的方式計(jì)算結(jié)果。

根據(jù)"教師基本信息"工作表和"課時(shí)費(fèi)標(biāo)準(zhǔn)”工作表計(jì)算"職稱"和"課時(shí)標(biāo)準(zhǔn)"

列內(nèi)容，根據(jù)"授課信息表"和"課程基本信息"工作表計(jì)算"學(xué)時(shí)數(shù)”列內(nèi)容，最

后完畢"課時(shí)費(fèi)”列的計(jì)算?！咎嵝?建議對(duì)"授課信息表"中的數(shù)據(jù)按姓名排序

后增長(zhǎng)"學(xué)時(shí)數(shù)"列，并通過(guò)VL00KUP查詢"課程基本信息"表獲得相應(yīng)的值。】

4.為"課時(shí)費(fèi)登記表"創(chuàng)建一個(gè)數(shù)據(jù)透視表，保存在新的工作表中。其中報(bào)表篩選

條件為"年度"，列標(biāo)簽為"教研室"，行標(biāo)簽為"職稱"，求和項(xiàng)為"課時(shí)費(fèi)"。

并在該透視表下方的A12：F24區(qū)域內(nèi)插入一個(gè)餅圖，顯示計(jì)算機(jī)基礎(chǔ)室課時(shí)費(fèi)

對(duì)職稱的分布情況。并將該工作表命名為"數(shù)據(jù)透視圖"，表標(biāo)簽顏色為藍(lán)色。

5.保存"課時(shí)費(fèi).x1sx"文獻(xiàn)。

答疑：1.【解題環(huán)節(jié)】

環(huán)節(jié)1：打開(kāi)素材文獻(xiàn)"素材.X1sx",單擊【文獻(xiàn)】選項(xiàng)卡下的"另存為"按鈕

將此文獻(xiàn)另存為"課時(shí)費(fèi).xlsx"的文獻(xiàn)。

2.【解題環(huán)節(jié)】

環(huán)節(jié)1：右擊"課時(shí)費(fèi)登記表"，在彈出的列表中的"工作表標(biāo)簽顏色"中的"主體

顏色"中選擇"紅色"。

環(huán)節(jié)2：在"課時(shí)費(fèi)登記表"表中，選中第一行，單擊鼠標(biāo)右鍵，在彈出的下拉列表

中選擇"設(shè)立單元格格式"命令，彈出"設(shè)立單元格格式"對(duì)話框。在"對(duì)齊"選

項(xiàng)卡下的"文本控制"組中，勾選"合并單元格"；切換至"字體"選項(xiàng)卡，在"字體"

下拉列表中選擇一種合適的字體，此處我們選擇"黑體"。在"字號(hào)"下拉列表中

選擇一種合適的字號(hào)，此處我們選擇"14

環(huán)節(jié)3：選中A2：122區(qū)域，在【開(kāi)始】選項(xiàng)卡下的【樣式】組中單擊"套用表

格格式"按鈕，在打開(kāi)的下拉列表中選擇一種恰當(dāng)?shù)臉邮?。按照題意，此處我們

選擇"表樣式中檔深淺5"。

環(huán)節(jié)4：此時(shí)彈出"套用表格式"對(duì)話框，勾選"表包含標(biāo)題"復(fù)選框。

環(huán)節(jié)5：最后單擊"擬定"按鈕即可。

環(huán)節(jié)6：選中前6歹I,單擊鼠標(biāo)右鍵，在彈出的下拉列表中選擇"設(shè)立單元格格式"

命令，彈出"設(shè)立單元格格式"對(duì)話框。在"對(duì)齊”選項(xiàng)卡下的"文本對(duì)齊方式"組的

水平對(duì)齊"下拉列表框中選擇"居中"，而后單擊"擬定"即可。

環(huán)節(jié)7：根據(jù)題意，選中與數(shù)值和金額有關(guān)的列標(biāo)題，單擊鼠標(biāo)右鍵，在彈出的下

拉列表中選擇"設(shè)立單元格格式"命令，彈出"設(shè)立單元格格式"對(duì)話框。在"對(duì)齊

"選項(xiàng)卡下的"文本對(duì)齊方式"組的"水平對(duì)齊"下拉列表框中選擇"居中"。設(shè)立完

畢后單擊"擬定"按鈕即可。

環(huán)節(jié)8：然后再選中與數(shù)值和金額有關(guān)的列，按照同樣的方式打開(kāi)"設(shè)立單元格格

式"對(duì)話框，在"對(duì)齊”選項(xiàng)卡下的"文本對(duì)齊方式"組的"水平對(duì)齊"下拉列表框中

選擇"靠右（縮進(jìn)）"□設(shè)立完畢后單擊"擬定"按鈕即可。

環(huán)節(jié)9：選中"學(xué)時(shí)數(shù)"所在的列，單擊鼠標(biāo)右鍵在彈出的的下拉列表中選擇"設(shè)立

單元格格式"命令。在彈出的"設(shè)立單元格格式"對(duì)話框中切換至"數(shù)字"選項(xiàng)卡。

在"分類"中選擇"數(shù)值"，在右側(cè)的“小數(shù)位數(shù)"微調(diào)框中選擇"0"。設(shè)立完畢后單

擊"擬定"按鈕即可。

環(huán)節(jié)10：選中"課時(shí)費(fèi)"和"課時(shí)標(biāo)準(zhǔn)"所在的列，按照同樣的方式打開(kāi)"設(shè)立單

元格格式"對(duì)話框。切換至"數(shù)字"選項(xiàng)卡，在"分類"中選擇"貨幣"，在右側(cè)

的"小數(shù)位數(shù)"微調(diào)框中選擇"2"o設(shè)立完畢后單擊"擬定"按鈕即可。

3.【解題環(huán)節(jié)】

環(huán)節(jié)1：在采用公式的方式計(jì)算"課時(shí)費(fèi)登記表"中的F至L列中的空白內(nèi)容之前,

為了方便結(jié)果的計(jì)算，我們先對(duì)"教師基本信息"工作表和"課時(shí)費(fèi)標(biāo)準(zhǔn)”工作表

的數(shù)據(jù)區(qū)域定義名稱。一方面切換至"教師基本信息"表中，選中數(shù)據(jù)區(qū)域，單擊

鼠標(biāo)右鍵，在彈出的下拉列表中選擇"定義名稱"命令，打開(kāi)"新建名稱"對(duì)話框。

在"名稱"中輸入"教師信息"后單擊擬定即可。

環(huán)節(jié)2：按照同樣的方式為"課時(shí)費(fèi)標(biāo)準(zhǔn)"定義名稱為"費(fèi)用標(biāo)準(zhǔn)"。

環(huán)節(jié)3：先根據(jù)"教師基本信息"表計(jì)算"課時(shí)費(fèi)登記表"中"職稱"列的內(nèi)容。選中"

課時(shí)賽登記表"中的F3單元格，輸入公式"=VL00KUP（E3,教師信息,5,FAL

SE）"后按"Enter"鍵即可將相應(yīng)的職稱數(shù)據(jù)引用至"課時(shí)費(fèi)登記表"中。

環(huán)節(jié)4：按照根據(jù)"教師基本信息"計(jì)算"課時(shí)費(fèi)登記表"中"職稱"列的內(nèi)容同樣

的方式來(lái)計(jì)算"課時(shí)標(biāo)準(zhǔn)”列的內(nèi)容，此處不再贅述。

環(huán)節(jié)5：現(xiàn)在根據(jù)"授課信息表"和"課程基本信息"工作表計(jì)算"學(xué)時(shí)數(shù)"列內(nèi)

容。按照同樣的方式先對(duì)"課程基本信息"的數(shù)據(jù)區(qū)域定義名稱，此處定義為"課

程信息"。

環(huán)節(jié)6：再對(duì)"授課信息表"中的數(shù)據(jù)按姓名排序。選中"姓名"列，單擊【數(shù)

據(jù)】選項(xiàng)卡下【排序和篩選】組中的"升序"按鈕，打開(kāi)"排序提醒"對(duì)話框,默認(rèn)選

項(xiàng)，然后單擊"排序"按鈕即可。

環(huán)節(jié)7：然后在"授課信息表"的F2單元格中增長(zhǎng)"學(xué)時(shí)數(shù)"列，即輸入"學(xué)時(shí)數(shù)"

字樣。

環(huán)節(jié)8：再根據(jù)"教師基本信息"計(jì)算"課時(shí)費(fèi)登記表"中"職稱"列的內(nèi)容同樣的方

式引用"課程基本信息"中的學(xué)時(shí)數(shù)數(shù)據(jù)至相應(yīng)的"授課信息表"中。

環(huán)節(jié)9：最后我們來(lái)計(jì)算"課時(shí)費(fèi)登記表"中的"學(xué)時(shí)數(shù)"列。選中H3單元格，輸入

公式"=SUMIF（授課信息表！$D$3：$D$72,E3,授課信息表!$F$3：$F$72）",

然后按"Enter"鍵即可。

環(huán)節(jié)10：在13單元格中輸入公式"=G3*H3"即可完畢課時(shí)費(fèi)的計(jì)算。

4.【解題環(huán)節(jié)】

環(huán)節(jié)1：在創(chuàng)建數(shù)據(jù)透視表之前，要保證數(shù)據(jù)區(qū)域必須要有列標(biāo)題，并且該區(qū)域

中沒(méi)有空行。

環(huán)節(jié)2：選中"課時(shí)費(fèi)登記表"工作表的數(shù)據(jù)區(qū)域，在【插入】選項(xiàng)卡下的【表格】

組中單擊"數(shù)據(jù)透視表"按鈕，打開(kāi)"創(chuàng)建數(shù)據(jù)透視表"對(duì)話框。

環(huán)節(jié)3：在"選擇一個(gè)表或區(qū)域"項(xiàng)下的"表/區(qū)域”框顯示當(dāng)前已選擇的數(shù)據(jù)源

區(qū)域。此處對(duì)默認(rèn)選擇不作更改。

環(huán)節(jié)4：指定數(shù)據(jù)透視表存放的位置：選中"新工作表"，單擊"擬定"按鈕。

環(huán)節(jié)5：Exce1會(huì)將空的數(shù)據(jù)透視表添加到指定位置，并在右側(cè)顯示"數(shù)據(jù)透視

表字段列表"窗口。

環(huán)節(jié)6：根據(jù)題意，選擇要添加到報(bào)表的字段。將"年度"拖曳至"報(bào)表篩選"條

件，"教研室"拖曳至"列標(biāo)簽"，"職稱"拖曳至"行標(biāo)簽"，"課時(shí)費(fèi)"拖曳至"數(shù)值"

中求和。

環(huán)節(jié)7：?jiǎn)螕魯?shù)據(jù)透視表區(qū)域中的任意單元格，而后在"數(shù)據(jù)透視表工具"的"選

項(xiàng)"選項(xiàng)卡下，單擊"工具"組中的"數(shù)據(jù)透視圖"按鈕，打開(kāi)"插入圖表"對(duì)話框。

根據(jù)題意，此處我們選擇"餅圖"選項(xiàng)。

環(huán)節(jié)8：?jiǎn)螕?擬定"按鈕后返回?cái)?shù)據(jù)透視工作表中，根據(jù)題意，拖動(dòng)餅圖至A12：

F24單元格內(nèi)即可。

環(huán)節(jié)9：為該工作表命名。雙擊數(shù)據(jù)透視表的工作表名，重新輸入"數(shù)據(jù)透視圖"

字樣。

環(huán)節(jié)10：鼠標(biāo)右擊表名"數(shù)據(jù)透視圖"，在彈出的快捷菜單中選擇"工作表標(biāo)簽顏

色"級(jí)聯(lián)菜單"主體顏色"中的"藍(lán)色"。

環(huán)節(jié)11：?jiǎn)螕簟氨４妗卑粹o保存“課時(shí)費(fèi).xlsx”文獻(xiàn)。

第2題：（電子表格題，本題30分）

小李是公司的出納，單位沒(méi)有購(gòu)買財(cái)務(wù)軟件，因此她只能用手工記賬。為了節(jié)

省時(shí)間并保證記賬的準(zhǔn)確性，小李使用Excel編制銀行存款日記賬。請(qǐng)根據(jù)該

公司九月份的"銀行流水賬表格.docx",并按照下述規(guī)定，在Excel中建立銀

行存款日記賬：

1.按照表中所示依次輸入原始數(shù)據(jù)，其中：在"月"列中以填充的方式輸入"九",

將表中的數(shù)值的格式設(shè)為數(shù)值、保存2位小數(shù)。

2.輸入并填充公式：在"余額"列輸入計(jì)算公式，余額=上期余額+本期借方-

本期貸方，以自動(dòng)填充方式生成其他公式。

3."方向列中"只能有借、貸、平三種選擇，一方面用數(shù)據(jù)有效性控制該列的輸入

范圍為借、貸、平三種中的一種，然后通過(guò)IF函數(shù)輸入"方向"列內(nèi)容，判斷條

件如下所列：

余額大于0等于0小于0

方向借平貸

4.設(shè)立格式：將第一行中的各個(gè)標(biāo)題居中顯示；為數(shù)據(jù)列表自動(dòng)套用格式后將其

轉(zhuǎn)換為區(qū)域。

5.通過(guò)度類匯總，按日計(jì)算借方、貸方發(fā)生額總計(jì)并將匯總行放于明細(xì)數(shù)據(jù)下

月O

6.以文獻(xiàn)名"銀行存款日記賬.x1sx"進(jìn)行保存。

答疑：1.【解題環(huán)節(jié)】

環(huán)節(jié)1：一方面我們按照素材中"銀行流水賬表格.docx"所示依次輸入原始數(shù)

據(jù)。

環(huán)節(jié)2：然后我們?cè)?月"列中以填充的方式輸入"九"。將鼠標(biāo)光標(biāo)放置A2單元

格右下角的填充柄處，待鼠標(biāo)變成黑色十字形狀后拖住不放，向下填充直至A6

單元格處。

環(huán)節(jié)3：,將表中數(shù)值格式設(shè)為數(shù)值、保存2位小數(shù)。此處我們選擇E3單元格,

單擊鼠標(biāo)右鍵在彈出的的下拉列表中選擇"設(shè)立單元格格式"命令。

環(huán)節(jié)4：在彈出的"設(shè)立單元格格式"對(duì)話框中切換至"數(shù)字"選項(xiàng)卡。在"分類"

中選擇"數(shù)值"，在右側(cè)的"小數(shù)位數(shù)"微調(diào)框中選擇"2"。

環(huán)節(jié)5：?jiǎn)螕?擬定"按鈕后即可完畢設(shè)立。

環(huán)節(jié)6：按照同樣的方式分別為其他數(shù)值的格式設(shè)為數(shù)值、保存2位小數(shù)。

2.【解題環(huán)節(jié)】

環(huán)節(jié)1：根據(jù)題意，在H3單元格中輸入余額的計(jì)算公式"=H2+E3—F3",按

"Enter"鍵確認(rèn)即可得出本期余額的計(jì)算結(jié)果。

環(huán)節(jié)2：其他余額的計(jì)算采用自動(dòng)填充公式的方式。選中H3單元格，將鼠標(biāo)光標(biāo)

放置H3單元格右下角的填充柄處，待鼠標(biāo)變成黑色十字形狀后拖住不放，向下

填充直至H6單元格處。

3.【解題環(huán)節(jié)】

環(huán)節(jié)1：選定"方向"列中的G2：G6區(qū)域，在【數(shù)據(jù)】選項(xiàng)卡下的【數(shù)據(jù)工具】組

中，單擊"數(shù)據(jù)有效性"按鈕，打開(kāi)"數(shù)據(jù)有效性"對(duì)話框。

環(huán)節(jié)2：切換至"設(shè)立"選項(xiàng)卡，在"允許"下拉列表框中選擇"序列"命令;在"來(lái)

源"文本框中輸入"借，貸，平"（注意要用英文輸入狀態(tài)下的逗號(hào)分隔），勾選"忽略

空值"和"提供下拉菜單"兩個(gè)復(fù)選框。

環(huán)節(jié)3：再切換至"輸入信息"選項(xiàng)卡，勾選"選定單元格顯示輸入信息"復(fù)選框,

在"輸入信息"中輸入"請(qǐng)?jiān)谶@里選擇二

環(huán)節(jié)4：?jiǎn)螕?擬定"按鈕后即可在"方向"列中看到實(shí)際效果。

環(huán)節(jié)5：根據(jù)題意，通過(guò)IF函數(shù)輸入"方向"列內(nèi)容。在"G2"單元格中輸入"=

IF（H2=0,"平"，IF（H2>0,"借"，"貸"））",然后按"Enter"鍵確認(rèn)。

環(huán)節(jié)6：按照同樣的方式通過(guò)IF函數(shù)向方向列的其他單元格內(nèi)輸入內(nèi)容。

4.【解題環(huán)節(jié)】

環(huán)節(jié)1：選中Al：H1單元格，在【開(kāi)始】菜單下的【對(duì)齊方式】組中單擊"

居中"按鈕，即可將第一行中的各個(gè)標(biāo)題居中顯示。

環(huán)節(jié)2：選中數(shù)據(jù)區(qū)域，在【開(kāi)始】菜單下的【樣式】組中單擊"套用表格格式"

按鈕，在彈出的下拉列表中選中一種自動(dòng)套用的格式。此處，我們選擇"中檔深

淺"組中的"表樣式中檔深淺2"。

環(huán)節(jié)3：?jiǎn)螕羰髽?biāo)選中后彈出"套用表格式"對(duì)話框，在此對(duì)話框中勾選"表包含標(biāo)

題"復(fù)選框。

環(huán)節(jié)4：?jiǎn)螕?擬定"按鈕后即可為數(shù)據(jù)列表套用自動(dòng)表格樣式。

環(huán)節(jié)5：選中表格，在【表格工具】的【設(shè)計(jì)】選項(xiàng)卡下，單擊【工具】組中的"

轉(zhuǎn)換為區(qū)域"按鈕。

環(huán)節(jié)6：?jiǎn)螕?是"按鈕即可將表格轉(zhuǎn)換為普通區(qū)域。

5.【解題環(huán)節(jié)】

環(huán)節(jié)1：選中數(shù)據(jù)區(qū)域，在【數(shù)據(jù)】選項(xiàng)卡下的【分級(jí)顯示】組中，單擊"分類

匯總"按鈕。

環(huán)節(jié)2：彈出"分類匯總"對(duì)話框，在"分類字段"中選擇"日"，在"匯總方式"中選

擇"求和"，在"選定匯總項(xiàng)"中勾選"本期借方"和"本期貸方"。

環(huán)節(jié)3：?jiǎn)螕?擬定"按鈕后即可看到實(shí)際匯總效果。

6.【解題環(huán)節(jié)】

環(huán)節(jié)：?jiǎn)螕艄δ軈^(qū)中的【文獻(xiàn)】選項(xiàng)卡，在打開(kāi)的后臺(tái)視圖中單擊"另存為"按鈕,

在隨后彈出的"另存為"對(duì)話框中以文獻(xiàn)名"銀行存款日記賬.xlsx"進(jìn)行保存。

第2題：（電子表格題，本題30分）

小趙是某書店的銷售人員，負(fù)責(zé)計(jì)算機(jī)類圖書的銷售情況,并按月份上報(bào)分析結(jié)

果。2023年1月份時(shí)，她需要將2023年12月份的銷售情況進(jìn)行匯總，請(qǐng)根據(jù)

提供的"Excel,xlsx"文獻(xiàn)幫助她完畢下列工作：

1.對(duì)"Excel.xlsx"進(jìn)行如下設(shè)立：將"銷售記錄"表中的"單價(jià)"列數(shù)值的格式

設(shè)為會(huì)計(jì)專用、保存2位小數(shù)。

2.對(duì)數(shù)據(jù)表"銷售記錄"進(jìn)行適當(dāng)?shù)母袷交?，操作如下：合并Al：E1單元格，為

標(biāo)題名"12月份計(jì)算機(jī)圖書銷售情況登記表"設(shè)立適當(dāng)?shù)膶?duì)齊方式、字體、字

號(hào)以及顏色；為數(shù)據(jù)區(qū)域設(shè)立邊框底紋以使工作表更加美觀等。

3.將工作表"銷量"中的區(qū)域B3：D16定義名稱為"銷量信息"。在"銷售記錄"

表中的"銷量”列右側(cè)增長(zhǎng)一列"銷售額"，根據(jù)"銷售額=銷量X單價(jià)"構(gòu)建公式

計(jì)算出各類圖書的銷售額。規(guī)定在公式中通過(guò)VLOOKUP函數(shù)自動(dòng)在工作表"銷量

中查找相關(guān)商品的具體銷量。并在公式中引用所定義的名稱"銷量信息"。

4.為"銷售記錄"工作表創(chuàng)建一個(gè)數(shù)據(jù)透視表，放在一個(gè)名為“數(shù)據(jù)透視分析”的

新的工作表中。

5.為數(shù)據(jù)透視表數(shù)據(jù)創(chuàng)建一個(gè)類型為餅圖的數(shù)據(jù)透視圖，設(shè)立數(shù)據(jù)標(biāo)簽顯示在外

側(cè)，將圖表的標(biāo)題改為“12月份計(jì)算機(jī)圖書銷量"。

6.將工作表另存為"計(jì)算機(jī)類圖書12月份銷售情況記錄.xlsx"文獻(xiàn)。

答疑：1.【解題環(huán)節(jié)】

環(huán)節(jié)1：將表中數(shù)值格式設(shè)為會(huì)計(jì)專用、保存2位小數(shù)。此處我們選擇C4單元格，

單擊鼠標(biāo)右鍵在彈出的的下拉列表中選擇"設(shè)立單元格格式"命令。

環(huán)節(jié)2：在彈出的"設(shè)立單元格格式"對(duì)話框中切換至"數(shù)字"選項(xiàng)卡。在"分類"中

選擇"會(huì)計(jì)專用"，在右側(cè)的"小數(shù)位數(shù)"微調(diào)框中選擇"2"。

環(huán)節(jié)3：?jiǎn)螕?擬定"按鈕后即可完畢設(shè)立。

環(huán)節(jié)4：按照同樣的方式分別為其他數(shù)值的格式設(shè)為會(huì)計(jì)專用、保存2位小數(shù)。

2.【解題環(huán)節(jié)】

環(huán)節(jié)1：在"銷售記錄"表中，選中A1：E1單元格，單擊鼠標(biāo)右鍵，在彈出的下拉列

表中選擇"設(shè)立單元格格式"命令，彈出"設(shè)立單元格格式"對(duì)話框。在"對(duì)齊"選項(xiàng)

卡下的"文本控制"組中，勾選"合并單元格"；在"文本對(duì)齊方式"組的"水平對(duì)齊

"選項(xiàng)下選擇"居中"。而后單擊"擬定"即可。

環(huán)節(jié)2：按照同樣的方式打開(kāi)"設(shè)立單元格格式"對(duì)話框，切換至"字體"選項(xiàng)卡,

在"字體"下拉列表中選擇一種合適的字體，此處我們選擇"黑體"。在"字號(hào)"下

拉列表中選擇一種合適的字號(hào)，此處我們選擇"14"。在"顏色"下拉列表中選擇

合適的顏色，此處我們選擇"紅色"，單擊"擬定"按鈕后即可看到實(shí)際效果。

環(huán)節(jié)3：選中數(shù)據(jù)區(qū)域，單擊鼠標(biāo)右鍵，在彈出的下拉菜單中選擇"設(shè)立單元格格

式"命令。

環(huán)節(jié)4：在打開(kāi)的"設(shè)立單元格格式"對(duì)話框中，切換至"邊框"選項(xiàng)卡，在"預(yù)置

"組中單擊"外邊框"按鈕后即可在"邊框"組中預(yù)覽效果。

環(huán)節(jié)5：再切換至"填充"選項(xiàng)卡下，在"背景色"組中選擇一種合適的填充顏色。

環(huán)節(jié)6：?jiǎn)螕?擬定"按鈕后即可在工作表中看到實(shí)際效果。

3.【解題環(huán)節(jié)】

環(huán)節(jié)1：在"銷量"工作表中選中B3：D16區(qū)域，單擊鼠標(biāo)右鍵，在彈出的下拉列

表中選擇"定義名稱"命令，打開(kāi)"新建名稱"對(duì)話框。在"名稱"中輸入"銷量信

息"后單擊擬定即可。

環(huán)節(jié)2：在"銷售記錄"表中選中E3單元格，輸入"銷售額"。

環(huán)節(jié)3：在D4單元格中輸入"=VLOOKUP（A4,銷量信息，3,FALSE）"，然

后按"Enter"鍵確認(rèn)即可把"銷量”工作表中相應(yīng)的銷量數(shù)據(jù)引入到“銷售記錄"

工作表中。

環(huán)節(jié)4：拖動(dòng)D4右下角的填充柄直至最下一行數(shù)據(jù)處，完畢銷量數(shù)據(jù)的填充。

環(huán)節(jié)5：根據(jù)銷量以及單價(jià)計(jì)算銷售額。在E4單元格中輸入"=C4*D4",即可得出

結(jié)果。

環(huán)節(jié)6：拖動(dòng)E4右下角的填充柄直至最下一行數(shù)據(jù)處,完畢銷售額的填充。

4.【解題環(huán)節(jié)】

環(huán)節(jié)1：在創(chuàng)建數(shù)據(jù)透視表之前，要保證數(shù)據(jù)區(qū)域必須要有列標(biāo)題，并且該區(qū)域

中沒(méi)有空行。

環(huán)節(jié)2：選中"銷售記錄"工作表的數(shù)據(jù)區(qū)域，在【插入】選項(xiàng)卡下的【表格】組

中單擊"數(shù)據(jù)透視表"按鈕，打開(kāi)"創(chuàng)建數(shù)據(jù)透視表"對(duì)話框。

環(huán)節(jié)3：在"選擇一個(gè)表或區(qū)域"項(xiàng)下的"表/區(qū)域"框顯示當(dāng)前已選擇的數(shù)據(jù)源區(qū)

域。此處對(duì)默認(rèn)選擇不作更改。

環(huán)節(jié)4：指定數(shù)據(jù)透視表存放的位置：選中"新工作表"，單擊"擬定"按鈕。

環(huán)節(jié)5：Excel會(huì)將空的數(shù)據(jù)透視表添加到指定位置，并在右側(cè)顯示"數(shù)據(jù)透視

表字段列表"窗口。雙擊"Sheet1",重命名為"數(shù)據(jù)透視分析"。

5.【解題環(huán)節(jié)】

環(huán)節(jié)1：根據(jù)題意，在創(chuàng)建數(shù)據(jù)透視圖之前，先選擇要添加到報(bào)表的字段。默認(rèn)

情況下，非數(shù)值字段將會(huì)自動(dòng)添加到"行標(biāo)簽"區(qū)域，數(shù)值字段會(huì)添加到"數(shù)值"區(qū)

域。此處，我們?cè)谧侄瘟斜碇幸来螁螕暨x中"書名"和"銷售額"兩個(gè)字段,透視表

區(qū)域中將自動(dòng)匯總銷售額合計(jì)值。

環(huán)節(jié)2：?jiǎn)螕魯?shù)據(jù)透視表區(qū)域中的任意單元格，而后在"數(shù)據(jù)透視表工具"的"選項(xiàng)

"選項(xiàng)卡下，單擊"工具"組中的"數(shù)據(jù)透視圖"按鈕，打開(kāi)"插入圖表"對(duì)話框。根

據(jù)題意，此處我們選擇"餅圖"。

環(huán)節(jié)3:單擊"擬定"按鈕后即可看到實(shí)際效果。

環(huán)節(jié)4：選中數(shù)據(jù)透視圖，在【數(shù)據(jù)透視圖工具】中的【布局】選項(xiàng)卡下單擊【標(biāo)

簽】組中的"數(shù)據(jù)標(biāo)簽"按鈕，在彈出的下拉列表中選擇"數(shù)據(jù)標(biāo)簽外"命令。

環(huán)節(jié)5：按照題意修改圖表的標(biāo)題。一方面選中圖表的標(biāo)題，輸入新的標(biāo)題名"1

2月份計(jì)算機(jī)圖書銷量

6.【解題環(huán)節(jié)】

環(huán)節(jié)：最后單擊"文獻(xiàn)"選項(xiàng)卡下的"另存為"按鈕將文獻(xiàn)保存"計(jì)算機(jī)類圖書12

月份銷售情況記錄.xlsx"文獻(xiàn)。

第2題：（電子表格題，本題30分）

小偉在自己在讀的學(xué)院里面勤工助學(xué)，兼職當(dāng)副院長(zhǎng)的助理一職，平時(shí)重要負(fù)

責(zé)對(duì)各種文案或者數(shù)據(jù)的整理。現(xiàn)在,信息與計(jì)算科學(xué)專業(yè)的期末考試的部提成

績(jī)需要錄入文獻(xiàn)名為"考生成績(jī)單.X1sx"的Excel工作薄文檔中去。

請(qǐng)根據(jù)下列規(guī)定幫助小偉對(duì)該成績(jī)單進(jìn)行分析整理：

1.運(yùn)用"條件格式"功能進(jìn)行下列設(shè)立：將大學(xué)物理和大學(xué)英語(yǔ)兩科中低于80

分的成績(jī)所在的單元格以一種顏色填充，其他五科中大于或等于95分的成績(jī)以

另一種顏色標(biāo)出，所用顏色以不遮擋數(shù)據(jù)為宜。

2.對(duì)工作表"考生成績(jī)單.xlsx"中的數(shù)據(jù)列表進(jìn)行如下格式化操作：將第一列

學(xué)號(hào)"設(shè)立為文本，設(shè)立成績(jī)列為保存兩位小數(shù)的數(shù)值。改變數(shù)據(jù)列表中的行高、

列寬，改變字體、字號(hào)，設(shè)立邊框和底紋、設(shè)立對(duì)齊方式。

3.運(yùn)用sum和average函數(shù)計(jì)算每一個(gè)學(xué)生的總分以及平均成績(jī)。

4.復(fù)制工作表"考生成績(jī)單.xlsx",將副本放置于原表之后，新表重新命名為"

成績(jī)單分類匯總"。

5.通過(guò)度類匯總功能求出每個(gè)班各科的平均成績(jī)，并將每組結(jié)果分頁(yè)顯示。

6.創(chuàng)建一個(gè)簇狀柱形圖，對(duì)每個(gè)班各科平均成績(jī)進(jìn)行比較。

答疑：1.【解題環(huán)節(jié)】

環(huán)節(jié)1：選中F2：G14,單擊【開(kāi)始】選項(xiàng)卡下【樣式】組中的"條件格式"按鈕,

選擇"突出顯示單元格規(guī)則"中的"其他規(guī)則"，彈出"新建格式規(guī)則"對(duì)話框，在"

編輯規(guī)則說(shuō)明"選項(xiàng)下設(shè)立單元格值小于80o然后單擊"格式"按鈕，彈出"設(shè)立

單元格格式"對(duì)話框，在"填充"選項(xiàng)卡下選擇"紅色"，單擊"擬定"按鈕。

環(huán)節(jié)2：設(shè)立完畢后返回工作表界面。

環(huán)節(jié)3：先選中D2：E14,然后按住Ctr1鍵再選中H2：J14,按照上述同樣方法，

把單元格值大于或等于95的字體顏色設(shè)立為黃色。

2.【解題環(huán)節(jié)】

環(huán)節(jié)1：選中"學(xué)號(hào)"所在的列，單擊鼠標(biāo)右鍵，在彈出的下拉菜單中選擇"設(shè)立單

元格格式"命令，在彈出的的"設(shè)立單元格格式"對(duì)話框中切換至"數(shù)字選項(xiàng)卡"，

在"分類"下選擇"文本

環(huán)節(jié)2：選中所有成績(jī)列，單擊鼠標(biāo)右鍵，選擇"設(shè)立單元格格式"命令，在彈出

的的"設(shè)立單元格格式"對(duì)話框中切換至"數(shù)字選項(xiàng)卡"，在"分類"下選擇"數(shù)值"，

在小數(shù)位數(shù)微調(diào)框中設(shè)立小數(shù)位數(shù)為"2"。

環(huán)節(jié)3：設(shè)立完畢。

環(huán)節(jié)4：選中數(shù)據(jù)區(qū)域，單擊【開(kāi)始】選項(xiàng)卡下【單元格】組中的"格式"按鈕，

在下拉菜單中選擇"行高"，彈出"行高"對(duì)話框，設(shè)立行高為16。

環(huán)節(jié)5：?jiǎn)螕簟鹃_(kāi)始】選項(xiàng)卡下【單元格】組中的"格式"按鈕，在彈出的下拉

菜單中選擇"列寬"，彈出"列寬"對(duì)話框，設(shè)立列寬為11。

環(huán)節(jié)6：設(shè)立完畢。

環(huán)節(jié)7：右擊鼠標(biāo)選擇"設(shè)立單元格格式"，在彈出的"設(shè)立單元格格式"對(duì)話框

中的"字體"選項(xiàng)卡下，設(shè)立"字體"為"黑體"，字號(hào)為10o再選中第一行的單元格,

按照同樣的方式設(shè)立字形為"加粗

環(huán)節(jié)8：繼續(xù)選中數(shù)據(jù)區(qū)域，按照上述同樣的方式打開(kāi)"設(shè)立單元格格式"對(duì)話框,

在"設(shè)立單元格格式"對(duì)話框中的"對(duì)齊"選項(xiàng)卡下，設(shè)立"水平對(duì)齊"與"垂直對(duì)齊

"都為"居中"。

環(huán)節(jié)9：切換至"邊框"選項(xiàng)卡，在"預(yù)置"組中選擇"外邊框"和"內(nèi)部"。

環(huán)節(jié)10：再切換至"填充"選項(xiàng)卡下，在"背景色"組中選擇"淺綠"。

環(huán)節(jié)11:最后單擊"擬定"按鈕。

3.【解題環(huán)節(jié)】

環(huán)節(jié)1：在K2單元格中輸入"=SUM（D2：J2）",按"Enter"鍵后該單元格值

為"629.50",拖動(dòng)K2右下角的填充柄直至最下一行數(shù)據(jù)處，完畢總分的填充。

環(huán)節(jié)2：在L2單元格中輸入"=AVERAGE（D2：J2）",按"Enter"鍵后該

單元格值為"89.93",拖動(dòng)L2右下角的填充柄直至最下一行數(shù)據(jù)處，完畢平均分

的填充。

4.【解題環(huán)節(jié)】

環(huán)節(jié)1：復(fù)制工作表"學(xué)生成績(jī)單"，粘貼到"Sheet2"工作表中。在"期末成績(jī)

"上右擊鼠標(biāo)，在彈出的的快捷菜單中選擇"移動(dòng)或復(fù)制"命令。然后在彈出的"

移動(dòng)或復(fù)制工作表"對(duì)話框中的"下列選定工作表之前"中選擇"Sheet2",并勾選"

建立副本"復(fù)選框。

環(huán)節(jié)2：建立的副本完畢。

環(huán)節(jié)3：為副表重新命名。雙擊”期末成績(jī)（2）"呈可編輯狀態(tài)，然后輸入"成績(jī)

單分類匯總"字樣。

5.【解題環(huán)節(jié)】

環(huán)節(jié)1：對(duì)班級(jí)按升序進(jìn)行排序，選中C2：C14區(qū)域列表，單擊【數(shù)據(jù)】選項(xiàng)卡下

【排序和篩選】組中的"升序"按鈕，彈出"排序提醒"對(duì)話框，單擊"擴(kuò)展選定區(qū)

域"單選按鈕。然后單擊"排序"按鈕。

環(huán)節(jié)2：選中C15,單擊【數(shù)據(jù)】選項(xiàng)卡下【分級(jí)顯示】組中的"分類匯總"按鈕，

彈出"分類匯總"對(duì)話框，單擊"分類字段"組中的下拉按鈕選擇"班級(jí)"，單擊"

匯總方式"組中的下拉按鈕選擇"平均值"，在"選定匯總項(xiàng)"組中勾選"高等代

數(shù)"、"數(shù)學(xué)分析"、"大學(xué)英語(yǔ)"、"大學(xué)物理"、"VB程序設(shè)計(jì)"、"C語(yǔ)言程

序設(shè)計(jì)"、"Matlab"復(fù)選框，并勾選"每組數(shù)據(jù)分頁(yè)"復(fù)選框。

環(huán)節(jié)3：?jiǎn)螕?擬定"按鈕。

6.【解題環(huán)節(jié)】

環(huán)節(jié)1：選中每個(gè)班各科平均成績(jī)所在的單元格，單擊【插入】選項(xiàng)卡下【圖表】

組中"柱形圖"按鈕，在彈出的下拉列表中選擇"簇狀柱形圖"。

環(huán)節(jié)2：右擊圖表區(qū)，選擇"選擇數(shù)據(jù)"，彈出"選擇數(shù)據(jù)源"對(duì)話框。一方面選中"

圖例項(xiàng)（系列）"選項(xiàng)中的"系列1",而后單擊”圖例項(xiàng)（系列）"選項(xiàng)中的"編輯"

按鈕，彈出"編輯數(shù)據(jù)系列"對(duì)話框，在"系列名稱"中輸入"1"。

環(huán)節(jié)3：?jiǎn)螕?擬定"按鈕后，即可返回"選擇數(shù)據(jù)源"對(duì)話框。

環(huán)節(jié)4：按照同樣方法編輯"系列2"、"系列3"為"2"、"3"。

環(huán)節(jié)5：按照同樣的方式打開(kāi)"選擇數(shù)據(jù)源"對(duì)話框，選中"水平（分類）軸標(biāo)簽"下

的"1",單擊"編輯"按鈕，彈出"軸標(biāo)簽"對(duì)話框，在“軸標(biāo)簽區(qū)域"下輸入"

高等代數(shù)，數(shù)學(xué)分析，大學(xué)英語(yǔ)，大學(xué)物理,VB程序設(shè)計(jì),C語(yǔ)言程序設(shè)計(jì),Mat1a

b"o

環(huán)節(jié)6：?jiǎn)螕?擬定"按鈕后，返回"選擇數(shù)據(jù)源"對(duì)話框。

環(huán)節(jié)7：?jiǎn)螕?擬定"按鈕后，返回工作表中的簇狀柱形圖。

第2題：（電子表格題，本題30分）

請(qǐng)?jiān)凇敬痤}】菜單下選擇【進(jìn)入考生文獻(xiàn)夾】命令，并按照題目規(guī)定完畢下面的

操作。

注意：以下的文獻(xiàn)必須保存在考生文獻(xiàn)夾下

小劉是一所初中的學(xué)生處負(fù)責(zé)人，負(fù)責(zé)本院學(xué)生的成績(jī)管理。他通過(guò)Excel來(lái)

管理學(xué)生成績(jī)，現(xiàn)在第一學(xué)期期末考試剛剛結(jié)束，小劉將初一年級(jí)三個(gè)班級(jí)部

分學(xué)生成績(jī)錄入了文獻(xiàn)名為"第一學(xué)期期末成績(jī).xlsx"的Excel工作簿文檔

中。

請(qǐng)你根據(jù)下列規(guī)定幫助小劉同學(xué)對(duì)該成績(jī)單進(jìn)行整理和分析：

1.請(qǐng)對(duì)"第一學(xué)期期末成績(jī)”工作表進(jìn)行格式調(diào)整，通過(guò)套用表格格式方法將所

有的成績(jī)記錄調(diào)整為一致的外觀格式，并對(duì)該工作表"第一學(xué)期期末成績(jī)"中的

數(shù)據(jù)列表進(jìn)行格式化操作：將第一列"學(xué)號(hào)"列設(shè)為文本，將所有成績(jī)列設(shè)為保

存兩位小數(shù)的數(shù)值，設(shè)立對(duì)齊方式，增長(zhǎng)適當(dāng)?shù)倪吙蚝偷准y以使工作表更加美觀。

2.運(yùn)用"條件格式"功能進(jìn)行下列設(shè)立：將語(yǔ)文、數(shù)學(xué)、外語(yǔ)三科中不低于110

分的成績(jī)所在的單元格以一種顏色填充，所用顏色深淺以不遮擋數(shù)據(jù)為宜。

3.運(yùn)用sum和average函數(shù)計(jì)算每一個(gè)學(xué)生的總分及平均成績(jī)。

4.學(xué)號(hào)第4、5位代表學(xué)生所在的班級(jí),例如："C120231"代表12級(jí)1班。

請(qǐng)通過(guò)函數(shù)提取每個(gè)學(xué)生所在的專業(yè)并按下列相應(yīng)關(guān)系填寫在"班級(jí)"列中：

"學(xué)號(hào)"的4、5位相應(yīng)班級(jí)

011班

022班

033班

5.根據(jù)學(xué)號(hào)，請(qǐng)?jiān)?第一學(xué)期期末成績(jī)"工作表的"姓名"列中，使用VLOOKUP

函數(shù)完畢姓名的自動(dòng)填充。"姓名"和"學(xué)號(hào)"的相應(yīng)關(guān)系在"學(xué)號(hào)對(duì)照"工作表中。

6.在"成績(jī)分類匯總"中通過(guò)度類匯總功能求出每個(gè)班各科的最大值，并將匯總

結(jié)果顯示在數(shù)據(jù)下方。

7.以分類匯總結(jié)果為基礎(chǔ)，創(chuàng)建一個(gè)簇狀條形圖，對(duì)每個(gè)班各科最大值進(jìn)行比

較。

答疑：1.【解題環(huán)節(jié)】

環(huán)節(jié)1：?jiǎn)?dòng)考生文獻(xiàn)下的"第一學(xué)期期末成績(jī).X1SX

環(huán)節(jié)2：選中單元格A2：L20,單擊【開(kāi)始】選項(xiàng)卡下【樣式】組中的"套用表格

格式"，在下拉列表中我們選擇“表樣式淺色16"o

環(huán)節(jié)3：選中"學(xué)號(hào)"列，右擊鼠標(biāo)選擇"設(shè)立單元格格式"，彈出"設(shè)立單元格格

式"對(duì)話框，在"數(shù)字"選項(xiàng)卡的"分類"中選擇"文本"，單擊"擬定"按鈕即可完畢

設(shè)立。

環(huán)節(jié)4：選中所有成績(jī)列，右擊鼠標(biāo)選擇"設(shè)立單元格格式"，彈出"設(shè)立單元格格

式"對(duì)話框，在"數(shù)字"選項(xiàng)卡的"分類"中選擇"數(shù)值"，在小數(shù)位數(shù)微調(diào)框中設(shè)立小

數(shù)位數(shù)為2,單擊“擬定”按鈕。

環(huán)節(jié)5：選中所有文字內(nèi)容單元格，單擊【開(kāi)始】選項(xiàng)卡下【對(duì)齊方式】組中的"

居中"按鈕。

環(huán)節(jié)6：在"設(shè)立單元格格式"對(duì)話框中"邊框"選項(xiàng)卡下"預(yù)置"選項(xiàng)中選擇"

外邊框"和"內(nèi)部"。單擊"擬定"按鈕完畢設(shè)立。

2.【解題環(huán)節(jié)】

環(huán)節(jié)：選中單元格D2：F20,單擊【開(kāi)始】選項(xiàng)卡下【樣式】組中的"條件格式"

按鈕，選擇"突出顯示單元格規(guī)則"中的"其他規(guī)則"，彈出"新建格式規(guī)則"對(duì)話

框，在"編輯規(guī)則說(shuō)明"選項(xiàng)下設(shè)立單元格值大于或等于110,單擊"格式"按鈕,

彈出"設(shè)立單元格格式"對(duì)話框，在"填充"選項(xiàng)卡下選擇"紅色"，然后單擊"擬定

"按鈕。

3.【解題環(huán)節(jié)】

環(huán)節(jié)1：在K3單元格中輸入"=SUM（D3：J3）",按Enter鍵后完畢總分的自動(dòng)填

充。

環(huán)節(jié)2：在L3單元格中輸入"=AVERAGE（D3：J3）",按Enter鍵后完畢平均分

的自動(dòng)填充。

4.【解題環(huán)節(jié)】

環(huán)節(jié)：在C3單元格中輸入"=IF（MID（A3,4,2）="01","1班",IF（MID（A3,4,

2）="02","2班"，"3班"））"，按Enter鍵后完畢班級(jí)的自動(dòng)填充。

5.【解題環(huán)節(jié)】

環(huán)節(jié)：在B3單元格中輸入"=VL00KUP（A3,學(xué)號(hào)對(duì)照！$A$3：$B$20,

2,FALSE）",按Enter鍵后完畢姓名的自動(dòng)填充。

6.【解題環(huán)節(jié)】

環(huán)節(jié)1：在"成績(jī)分類匯總"工作表中選中單元格C3：C20,單擊【數(shù)據(jù)】選項(xiàng)

卡下【排序和篩選】組中的"升序"按鈕，彈出"排序提醒"對(duì)話框，單擊"擴(kuò)展

選定區(qū)域"單選按鈕。單擊"排序"按鈕，完畢"班級(jí)"列的升序排序。

環(huán)節(jié)2：選中單元格C21,單擊【數(shù)據(jù)】選項(xiàng)卡下【分級(jí)顯示】組中的"分類匯

總"按鈕，彈出"分類匯總"對(duì)話框，單擊"分類字段"下拉按鈕選擇"班級(jí)"，單

擊"匯總方式"下拉按鈕選擇"最大值"，在"選定匯總項(xiàng)"復(fù)選框中勾選"語(yǔ)