2016年3月計算機二級excel答案及解析

1、第 2 題：（電子表格題，本題 30 分） 請在【答題】菜單下選擇【進入考生文件夾】命令，并按照題目要求完成下面的 操作。注意：以下的文件必須都保存在考生文件夾下。在考生文件夾下打開文檔 EXCEL.XLSX【背景素材】財務部助理小王需要向主管匯報 2013 年度公司差旅報銷情況，現(xiàn)在請按照如下 需求，在EXCEL.XLSX文檔中完成工作：（ 1）在費用報銷管理 工作表日期列的所有單元格中，標注每個報銷日期屬 于星期幾，例如日期為 2013 年 1月20日的單元格應顯示為 2013 年 1月 20 日 星期日 ，日期為2013年1月21日的單元格應顯示為 2013 年1月21日 星 期一。（2

2、）如果日期列中的日期為星期六或星期日，則在 是否加班 列的單元格中 顯示是 ，否則顯示 否 （必須使用公式） 。（3） 使用公式統(tǒng)計每個活動地點所在的省份或直轄市，并將其填寫在 地區(qū)列 所對應的單元格中，例如 北京市、浙江省。（4）依據(jù)費用類別編號列內(nèi)容，使用VLOOKU函數(shù)，生成費用類別列內(nèi)容。 對照關(guān)系參考 費用類別工作表。（5）在差旅成本分析報告工作表B3單元格中，統(tǒng)計2013年第二季度發(fā)生在 北京市的差旅費用總金額。（6）在差旅成本分析報告工作表B4單元格中，統(tǒng)計2013年員工錢順卓報銷 的火車票費用總額。（7）在差旅成本分析報告工作表B5單元格中，統(tǒng)計2013年差旅費用中，飛 機票費

3、用占所有報銷費用的比例，并保留 2位小數(shù)。（8）在差旅成本分析報告工作表B6單元格中，統(tǒng)計2013年發(fā)生在周末（星 期六和星期日）的通訊補助總金額。答疑： （1）【解題步驟】步驟1:打開考生文件夾下的EXCEL.XLSX步驟 2：在費用報銷管理 工作表中，選中 日期數(shù)據(jù)列，單擊鼠標右鍵，在彈 出的快捷菜單中選擇設(shè)置單元格格式命令，彈出設(shè)置單元格格式對話框。切 換至數(shù)字選項卡，在分類列表框中選擇 自定義命令，在右側(cè)的 示例組中 類型列表框中輸入yyyy年m月d日aaaa 。設(shè)置完畢后單擊確定按 鈕即可。（ 2）【解題步驟】步驟：在費用報銷管理工作表的H3單元格中輸入=IF（WEEKDAY（A3,

4、2）5,是,否），表示在星期六或者星期日情況下顯示是，否則顯示否，按Enter 鍵確認。然后向下填充公式到最后一個日期即可完成設(shè)置。（ 3）【解題步驟】步驟：在費用報銷管理工作表的D3單元格中輸入=LEFT（C3,3），表示取當 前文字左側(cè)的前三個字符，按Enter鍵確認。然后向下填充公式到最后一個日期即可完成設(shè)置。（4）【解題步驟】步驟：在費用報銷管理工作表的F3單元格中輸入二VL00KUP（E3費用類 別!$A$3:$B$12,2,FALSE），按Enter后完成費用類別的填充。然后向下填充 公式到最后一個日期即可完成設(shè)置。（5）【解題步驟】步驟：選中A列單元格，在【編輯】選項組中，單擊排

5、序和篩選下拉菜單中的 升序按鈕，在彈出的 排序提醒對話框中單擊 排序按鈕。在差旅成本分析 報告工作表的B3單元格中輸入二SUMPRODUCT（費用報銷管理！D74:D340=北 京市）,費用報銷管理!G74:G340），按Enter鍵確認。（ 6）【解題步驟】步驟：在差旅成本分析報告工作表的B4單元格中輸入=SUMPRODUCT（費用 報銷管理 !B3:B401= 錢順卓 ）,1*（ 費用報銷管理 !F3:F401= 火車票 ）, 費用報銷 管理!G3:G401，按Enter鍵確認。（ 7）【解題步驟】步驟：在差旅成本分析報告工作表的B5單元格中輸入二SUMPRODUCT（費用 報銷管理!F3

6、:F401=飛機票）,費用報銷管理!G3:G401）/SUM（費用報銷管 理!G3:G401），按Enter鍵確認，并設(shè)置數(shù)字格式，保留兩位小數(shù)。（ 8）【解題步驟】步驟：在差旅成本分析報告工作表的B6單元格中輸入二SUMPRODUC費用報 銷管理!H3:H401=是）*（費用報銷管理!F3:F401=通訊補助）,費用報銷管 理!G3:G401），按Enter鍵確認。第2題：（電子表格題，本題 30分）請在【答題】菜單下選擇【進入考生文件夾】命令，并按照題目要求完成下面的 操作。注意：以下的文件必須都保存在考生文件夾下。 期末考試結(jié)束了，初三（ 14）班的班主任助理王老師需要對本班學生的各科考

7、 試成績進行統(tǒng)計分析，并為每個學生制作一份成績通知單下發(fā)給家長。按照下 列要求完成該班的成績統(tǒng)計工作并按原文件名進行保存：1. 打開工作簿“學生成績.xlsx ”在最左側(cè)插入一個空白工作表，重命名為“初 三學生檔案”，并將該工作表標簽顏色設(shè)為“紫色 （標準色） ”。2將以制表符分隔的文本文件“學生檔案.txt ”自A1單元格開始導入到工作表 “初三學生檔案” 中，注意不得改變原始數(shù)據(jù)的排列順序。 將第1列數(shù)據(jù)從左到 右依次分成“學號”和“姓名”兩列顯示。最后創(chuàng)建一個名為“檔案” 、包含數(shù) 據(jù)區(qū)域A1:G56包含標題的表，同時刪除外部鏈接。3. 在工作表“初三學生檔案” 中，利用公式及函數(shù)依次輸

8、入每個學生的性別 “男”或“女”、出生日期“ xxxx年XX月XX0 ”和年齡。其中：身份證號的倒 數(shù)第 2 位用于判斷性別，奇數(shù)為男性，偶數(shù)為女性；身份證號的第714位代表出生年月日；年齡需要按周歲計算， 滿1年才計1歲。最后適當調(diào)整工作表的行 高和列寬、對齊方式等，以方便閱讀。4. 參考工作表“初三學生檔案”，在工作表“語文”中輸入與學號對應的 “姓名”； 按照平時、期中、期末成績各占 30%、30%、40%的比例計算每個學生的“學期成 績”并填入相應單元格中；按成績由高到低的順序統(tǒng)計每個學生的“學期成績” 排名并按“第 n 名”的形式填入“班級名次”列中；按照下列條件填寫“期末總 評”：

9、語文、數(shù)學的學期成績 其他科目的學期成績 期末總評 102 90 優(yōu)秀 84 75良好 72 60及格 72 60 不合格5. 將工作表“語文”的格式全部應用到其他科目工作表中，包括行高（各行行 高均為 22 默認單位）和列寬（各列列寬均為 14 默認單位）。并按上述 4. 中的 要求依次輸入或統(tǒng)計其他科目的“姓名” 、“學期成績”、“班級名次”和“期末總 評”。6. 分別將各科的“學期成績”引入到工作表“期末總成績”的相應列中，在工作表“期末總成績”中依次引入姓名、計算各科的平均分、每個學生的總分，并 按成績由高到底的順序統(tǒng)計每個學生的總分排名、并以1、2、3形式標識名次，最后將所有成績的數(shù)

10、字格式設(shè)為數(shù)值、保留兩位小數(shù)。7. 在工作表“期末總成績”中分別用紅色（標準色）和加粗格式標出各科第一 名成績。同時將前 1 0名的總分成績用淺藍色填充。8. 調(diào)整工作表“期末總成績”的頁面布局以便打?。杭垙埛较驗闄M向，縮減打 印輸出使得所有列只占一個頁面寬（但不得縮小列寬） ，水平居中打印在紙上。 答疑：1. 【解題步驟】步驟：打開素材文件“學生成績 .xlsx ”，單擊工作表最右側(cè)的“插入工作表”按 鈕，然后雙擊工作表標簽，將其重命名為“初三學生檔案” 。在該工作表標簽上 單擊鼠標右鍵，在彈出的快捷菜單中選擇“工作表標簽顏色” ，在彈出的級聯(lián)菜 單中選擇標準色中的“紫色” 。2. 【解題步

11、驟】步驟 1：選中 A1 單元格，單擊【數(shù)據(jù)】選項卡下【獲取外部數(shù)據(jù)】組中的“自 文本”按鈕，彈出“導入文本文件”對話框，在該對話框中選擇考生文件夾下的 “學生檔案 .txt ”選項，然后單擊“導入”按鈕。步驟 2：在彈出的對話框中選擇“分隔符號”單選按鈕，將“文件原始格式”設(shè)置為“ 54936:簡體中文（GB18O30”。單擊“下一步”按鈕，只勾選“分隔符” 列表中的“Tab鍵”復選項。然后單擊“下一步”按鈕，選中“身份證號碼”列， 然后點擊“文本”單選按鈕，單擊“完成”按鈕，在彈出的對話框中保持默認， 單擊“確定”按鈕。步驟3:選中B列單元格，單擊鼠標右鍵，在彈出的快捷菜單中選擇“插入”選

12、 項。然后選中A1單元格，將光標置于“學號”和“名字”之間，按 3次空格鍵， 然后選中A列單元格，單擊【數(shù)據(jù)工具】組中的“分列”按鈕，在彈出的對話框 中選擇“固定寬度” 單選按鈕， 單擊“下一步” 按鈕，然后建立分列線。 單擊“下 一步”按鈕，保持默認設(shè)置，單擊“完成”按鈕。步驟4:選中A1:G56單元格，單擊【開始】選項卡下【樣式】組中的“套用表 格格式”下拉按鈕，在彈出的下拉列表中選擇“表樣式中等深淺2”。步驟 5:在彈出的對話框中勾選“表包含標題”復選框，單擊“確定”按鈕，然 后再在彈出的對話框中選擇 “是”按鈕。在【設(shè)計】選項卡下【屬性】組中將“表 名稱”設(shè)置為檔案。 3. 【解題步驟

13、】步驟1:選中D2單元格，在該單元格內(nèi)輸入函數(shù)“=IF(MOD(MID(C2,17,1),2)=1, 男, 女) ”，按 Enter 鍵完成操作。 然后利用自動的填充功能對其他單元格進行 填充。步 驟 2 : 選 中 E2 單 元 格 ， 在 該 單 元 格 內(nèi) 輸 入 函 數(shù)“ =-TEXT(M I D (C2 , 7 ,8) , 0-00-00)”，按 Enter 鍵完成操作，利用自動填充功能對剩余的單元格進行填充。然后選擇 E2: E56 單元格，單擊鼠標右鍵，在彈出 的快捷菜單中選擇“設(shè)置單元格格式” 選項。切換至“數(shù)字”選項卡， 將“分類” 設(shè)置為“日期”，然后單擊“確定”按鈕。步

14、驟 3 : 選 中 F2 單 元 格 ， 在 該 單 元 格 內(nèi) 輸 入 函 數(shù) “ = DATED I F (-TEXT(M I D (C2 , 7 ,8) , 0-00-00) ,TO DAY() , y)”，按 Enter 鍵，利用自動的填充功能對其他單元格進行填充。步驟4:選中A1: G56區(qū)域，單擊【開始】選項卡下【對齊方式】組中的“居中” 按鈕。適當調(diào)整表格的行高和列寬。4. 【解題步驟】步驟 1:進入到“語文”工作表中，選擇 B2 單元格，在該單元格內(nèi)輸入函數(shù)“ =VLOOKUP(A初三學生檔案！$A$2:$B$56,2,0) ”，按Enter鍵完成操作。然后 利用自動的填充功能

15、對其他單元格進行填充。步 驟 2 : 選 擇 F2 單 元 格 ， 在 該 單 元 格 中 輸 入 函 數(shù)“ =SUM(C2*30%)+(D2*30%)+(E2*40%，按 Enter 鍵確認操作。步驟3:選擇G2單元格，在該單元格內(nèi)輸入函數(shù)“=第&RANK(F2,$F$2:$F$45)& 名”，然后利用自動填充功能對其他單元格進行填充。步驟 4: 選擇 H2 單元 格， 在該單元 格中 輸入公式 “=IF(F2=102, 優(yōu)秀 ,IF(F2=84,良好,IF(F2=72,及格,IF(F272,及格,不及格)”，按 Enter 鍵完成操作，然后利用自動填充對其他單元格進行填充。5. 【解題步驟

16、】步驟1:選擇“語文”工作表中 A1: H45單元格區(qū)域，按Ctrl+C鍵進行復制， 進入到“數(shù)學”工作表中，選擇 A1: H45區(qū)域，單擊鼠標右鍵，在彈出的快捷菜 單中選擇“粘貼選項”下的“格式”按鈕。步驟2:繼續(xù)選擇“數(shù)學”工作表中的A1: H45區(qū)域，單擊【開始】選項卡下【單 元格】組中的“格式”下拉按鈕，在彈出的下拉列表中選擇“行高”選項，在彈 出的對話框中將“行高”設(shè)置為 22，單擊“確定”按鈕。單擊“格式”下拉按 鈕，在彈出的下拉列表中選擇“列寬”選項，在彈出的對話框中將“列寬”設(shè)置 為 14，單擊“確定”按鈕。步驟 3:使用同樣的方法為其他科目的工作表設(shè)置相同的格式， 包括行高和

17、列寬。 步驟 4:將“語文”工作表中的公式粘貼到數(shù)學科目工作表中的對應的單元格內(nèi)， 然后利用自動填充功能對單元格進行填充。步驟 5:在“英語”工作表中的 H2 單元格中輸入公式“ =IF(F2=90, 優(yōu)秀 ,IF(F2=75,良好,IF(F2=60,及格,IF(F260,及格,不及格)”，按 Enter 鍵完成操作，然后利用自動填充對其他單元格進行填充。步驟6:將“英語”工作表H2單元格中的公式粘貼到“物理”、“化學”、“品德”、“歷史”工作表中的H2單元格中，然后利用自動填充功能對其他單元格進行填 充。6. 【解題步驟】步驟 1：進入到“期末總成績”工作表中，選擇 B3 單元格，在該單元格

18、內(nèi)輸入公式“ =VLOOKUP(A初三學生檔案！$A$2:$B$56,2,0) ”，按Enter鍵完成操作， 然后利用自動填充功能將其填充至 B46單元格。步驟2 :選擇C3單元格，在該單元格內(nèi)輸入公式“ =VLOOKUP(A語 文!$A$2:$F$45,6,0) ”，按Enter鍵完成操作，然后利用自動填充功能將其填充 至C46單元格。步驟3 :選擇D3單元格，在該單元格內(nèi)輸入公式“ =VLOOKUP(A數(shù) 學!$A$2:$F$45,6,0) ”，按Enter鍵完成操作，然后利用自動填充功能將其填充 至D46單元格。步驟 4:使用相同的的方法為其他科目填充平均分。選擇 J3 單元格，在該單元

19、 格內(nèi)輸入公式“ =SUM(C3:I3)”，按Enter鍵，然后利用自動填充功能將其填充至 J46 單元格。步驟5:選擇A3: K46單元格，單擊【開始】選項卡【編輯】組中“排序和篩選” 下拉按鈕， 在彈出的下拉列表中選擇 “自定義排序” 選項， 彈出“排序” 對話框， 在該對話框中將“主要關(guān)鍵字” 設(shè)置為“總分”，將“排序依據(jù)” 設(shè)置為“數(shù)值”， 將“次序”設(shè)置為“降序” ，單擊“確定”按鈕。步驟6:在K3單元格內(nèi)輸入數(shù)字1,然后按住Ctrl鍵，利用自動填充功能將其 填充至K46單元格。步驟7:選擇C47單元格，在該單元格內(nèi)輸入公式“ =AVERAGE(C3:C46)按Enter 鍵完成操作

20、，利用自動填充功能進行將其填充至 J47 單元格。步驟 8:選擇 C3: J47 單元格，在選擇的單元格內(nèi)單擊鼠標右鍵，在彈出的快捷 菜單中選擇“設(shè)置單元格格式”選項。在彈出的對話框中選擇“數(shù)字”選項卡， 將“分類”設(shè)置為數(shù)值，將“小數(shù)位數(shù)”設(shè)置為 2，單擊“確定”按鈕。7. 【解題步驟】步驟1:選擇C3: C46單元格，單擊【開始】選項卡下【樣式】組中的“條件 格式”按鈕，在彈出的下拉列表中選擇“新建規(guī)則”選項，在彈出的對話框中將 “選擇規(guī)則類型”設(shè)置為“僅對排名靠前或靠后的數(shù)值設(shè)置格式” ，然后將“編 輯規(guī)則說明”設(shè)置為“前” 、“1”。步驟 2：單擊“格式”按鈕，在彈出的對話框中將“字形

21、”設(shè)置為加粗，將“顏 色”設(shè)置為標準色中的“紅色” ，單擊兩次“確定”按鈕。按同樣的操作方式為 其他六科分別用紅色和加粗標出各科第一名成績。步驟 3：選擇 J3：J12 單元格，單擊鼠標右鍵，在彈出的快捷菜單中選擇“設(shè)置 單元格格式” 選項，在彈出的對話框中切換至 “填充” 選項卡， 然后單擊“淺藍” 顏色塊，單擊“確定”按鈕。8. 【解題步驟】步驟 1：在【頁面邊距】選項卡下【頁面設(shè)置】組中單擊對話框啟動器按鈕，在 彈出的對話框中切換至“頁邊距”選項卡勾選“居中方式”選項組中的“水平” 復選框。步驟 2：切換至“頁面”選項卡，將“方向”設(shè)置為橫向。選擇“縮放”選項組 下的“調(diào)整為”單選按鈕，

22、將其設(shè)置為 1頁寬1頁高，單擊“確定”按鈕。 第2題：(電子表格題，本題 30分)請在【答題】菜單下選擇【進入考生文件夾】命令，并按照題目要求完成下面的 操作。小林是北京某師范大學財務處的會計，計算機系計算機基礎(chǔ)室提交了該教研室2012 年的課程授課情況，希望財務處盡快核算并發(fā)放他們室的課時費。請根據(jù) 考生文件夾下 素材 .xlsx 中的各種情況，幫助小林核算出計算機基礎(chǔ)室2012年度每個教員的課時費情況。具體要求如下：1. 將素材.xlsx另存為課時費.xlsx的文件，所有的操作基于此新保存好的 文件。2. 將課時費統(tǒng)計表 標簽顏色更改為紅色， 將第一行根據(jù)表格情況合并為一個單元格，并設(shè)置合

23、適的字體、字號，使其成為該工作表的標題。對A2：I22 區(qū)域套用合適的中等深淺的、 帶標題行的表格格式。 前 6列對齊方式設(shè)為居中； 其余與 數(shù)值和金額有關(guān)的列，標題為居中，值為右對齊，學時數(shù)為整數(shù)，金額為貨幣樣 式并保留 2 位小數(shù)。3. 課時費統(tǒng)計表中的F至I列中的空白內(nèi)容必須采用公式的方式計算結(jié)果。 根 據(jù)教師基本信息 工作表和課時費標準工作表計算職稱和課時標準列內(nèi) 容，根據(jù)授課信息表和課程基本信息 工作表計算學時數(shù)列內(nèi)容，最后完成 課時費 列的計算。 【提示：建議對 授課信息表 中的數(shù)據(jù)按姓名排序后增加 學時數(shù)列，并通過VLOOKU查詢課程基本信息表獲得相應的值?！?. 為課時費統(tǒng)計表

24、 創(chuàng)建一個數(shù)據(jù)透視表， 保存在新的工作表中。 其中報表篩選 條件為年度 ，列標簽為教研室 ，行標簽為職稱 ，求和項為課時費 。并在 該透視表下方的A12: F24區(qū)域內(nèi)插入一個餅圖，顯示計算機基礎(chǔ)室課時費對職 稱的分布情況。并將該工作表命名為 數(shù)據(jù)透視圖 ，表標簽顏色為藍色。5保存課時費.xlsx文件。答疑: 1 . 【解題步驟】步驟1:打開素材文件素材.xlsx，單擊【文件】選項卡下的另存為按鈕將 此文件另存為課時費.xlsx的文件。2. 【解題步驟】步驟 1:右擊課時費統(tǒng)計表 ，在彈出的列表中的 工作表標簽顏色 中的主體 顏色中選擇紅色。步驟 2:在課時費統(tǒng)計表 表中，選中第一行，單擊鼠標

25、右鍵，在彈出的下拉列 表中選擇 設(shè)置單元格格式 命令，彈出設(shè)置單元格格式 對話框。在對齊選項 卡下的文本控制組中，勾選 合并單元格 ；切換至字體選項卡，在 字體 下拉列表中選擇一種合適的字體， 此處我們選擇 黑體。在字號下拉列表中選 擇一種合適的字號，此處我們選擇 14。步驟 3:選中 A2: I22 區(qū)域，在【開始】選項卡下的【樣式】組中單擊 套用表 格格式按鈕，在打開的下拉列表中選擇一種恰當?shù)臉邮?。按照題意，此處我們 選擇表樣式中等深淺 5。步驟4:此時彈出套用表格式對話框，勾選表包含標題復選框。步驟 5:最后單擊 確定按鈕即可。步驟 6:選中前 6 列，單擊鼠標右鍵，在彈出的下拉列表中選

26、擇 設(shè)置單元格格 式命令，彈出設(shè)置單元格格式 對話框。在對齊選項卡下的文本對齊方式 組的水平對齊下拉列表框中選擇 居中，而后單擊確定即可。步驟 7:根據(jù)題意，選中與數(shù)值和金額有關(guān)的列標題，單擊鼠標右鍵，在彈出的 下拉列表中選擇 設(shè)置單元格格式 命令，彈出 設(shè)置單元格格式 對話框。在 對 齊選項卡下的 文本對齊方式 組的水平對齊 下拉列表框中選擇 居中 。設(shè)置 完畢后單擊 確定 按鈕即可。步驟 8：然后再選中與數(shù)值和金額有關(guān)的列，按照同樣的方式打開 設(shè)置單元格格式對話框，在對齊選項卡下的文本對齊方式 組的水平對齊下拉列表框 中選擇 靠右（縮進） 。設(shè)置完畢后單擊 確定按鈕即可。步驟 9：選中學時

27、數(shù) 所在的列，單擊鼠標右鍵在彈出的的下拉列表中選擇設(shè)置單元格格式 命令。在彈出的 設(shè)置單元格格式 對話框中切換至 數(shù)字選項卡。 在分類中選擇數(shù)值，在右側(cè)的 小數(shù)位數(shù)微調(diào)框中選擇 0 。設(shè)置完畢后單 擊確定按鈕即可。步驟 10：選中課時費和課時標準 所在的列，按照同樣的方式打開 設(shè)置單元 格格式對話框。切換至數(shù)字選項卡，在分類中選擇貨幣 ，在右側(cè)的 小數(shù) 位數(shù)微調(diào)框中選擇 2 。設(shè)置完畢后單擊 確定按鈕即可。3. 【解題步驟】步驟1:在采用公式的方式計算課時費統(tǒng)計表中的F至L列中的空白內(nèi)容之前， 為了方便結(jié)果的計算，我們先對教師基本信息 工作表和課時費標準工作表的 數(shù)據(jù)區(qū)域定義名稱。 首先切換至

28、教師基本信息 表中，選中數(shù)據(jù)區(qū)域， 單擊鼠標 右鍵，在彈出的下拉列表中選擇 定義名稱命令，打開新建名稱對話框。在 名稱中輸入教師信息后單擊確定即可。步驟2:按照同樣的方式為 課時費標準 定義名稱為費用標準 。 步驟3:先根據(jù)教師基本信息表計算課時費統(tǒng)計表中職稱列的內(nèi)容。選中 課時費統(tǒng)計表中的F3單元格，輸入公式=VLOOKUP（E3教師信息,5,FALSE） 后按 Enter 鍵即可將對應的職稱數(shù)據(jù)引用至 課時費統(tǒng)計表中。步驟 4:按照根據(jù)教師基本信息 計算課時費統(tǒng)計表 中職稱列的內(nèi)容同樣的 方式來計算 課時標準 列的內(nèi)容，此處不再贅述。步驟5:現(xiàn)在根據(jù)授課信息表和課程基本信息工作表計算學時數(shù)

29、列內(nèi)容。 按照同樣的方式先對 課程基本信息 的數(shù)據(jù)區(qū)域定義名稱，此處定義為 課程信 息。步驟 6:再對授課信息表 中的數(shù)據(jù)按姓名排序。選中 姓名列，單擊【數(shù)據(jù)】 選項卡下【排序和篩選】組中的 升序按鈕，打開排序提醒對話框，默認選項， 然后單擊 排序按鈕即可。步驟7:然后在授課信息表的F2單元格中增加學時數(shù)列，即輸入學時數(shù) 字樣。步驟8:再根據(jù)教師基本信息計算課時費統(tǒng)計表中職稱列的內(nèi)容同樣的方 式引用課程基本信息 中的學時數(shù)數(shù)據(jù)至對應的 授課信息表中。步驟9:最后我們來計算課時費統(tǒng)計表中的學時數(shù)列。選中H3單元格，輸 入公式二SUMIF（授課信息表！$D$3:$D$72,E3,授課信息表！$F$

30、3:$F$72），然后按 Enter 鍵即可。步驟10:在I3單元格中輸入公式=G3*H3即可完成課時費的計算。4【解題步驟】步驟 1 :在創(chuàng)建數(shù)據(jù)透視表之前，要保證數(shù)據(jù)區(qū)域必須要有列標題，并且該區(qū)域 中沒有空行。步驟2:選中課時費統(tǒng)計表 工作表的數(shù)據(jù)區(qū)域， 在【插入】選項卡下的【表格】 組中單擊數(shù)據(jù)透視表按鈕，打開創(chuàng)建數(shù)據(jù)透視表對話框。步驟 3：在選擇一個表或區(qū)域 項下的表/ 區(qū)域框顯示當前已選擇的數(shù)據(jù)源區(qū) 域。此處對默認選擇不作更改。步驟 4：指定數(shù)據(jù)透視表存放的位置：選中 新工作表 ，單擊 確定按鈕。步驟 5：Excel 會將空的數(shù)據(jù)透視表添加到指定位置， 并在右側(cè)顯示 數(shù)據(jù)透視表 字段

31、列表 窗口。步驟 6：根據(jù)題意，選擇要添加到報表的字段。 將年度拖曳至報表篩選 條件， 教研室拖曳至 列標簽 ，職稱拖曳至行標簽 ，課時費拖曳至數(shù)值中求 和。步驟 7：單擊數(shù)據(jù)透視表區(qū)域中的任意單元格，而后在 數(shù)據(jù)透視表工具 的選項選項卡下，單擊 工具組中的數(shù)據(jù)透視圖 按鈕，打開 插入圖表對話框。 根據(jù)題意，此處我們選擇 餅圖 選項。步驟 8 ：單擊 確定 按鈕后返回數(shù)據(jù)透視工作表中， 根據(jù)題意，拖動餅圖至 A12： F24單元格內(nèi)即可。步驟 9： 為該工作表命名。雙擊數(shù)據(jù)透視表的工作表名，重新輸入 數(shù)據(jù)透視圖 字樣。步驟 10：鼠標右擊表名 數(shù)據(jù)透視圖 ，在彈出的快捷菜單中選擇 工作表標簽顏

32、 色級聯(lián)菜單 主體顏色 中的藍色。步驟 11：單擊“保存”按鈕保存“課時費 .xlsx ”文件。 第2題：（電子表格題，本題 30分） 小李是公司的出納，單位沒有購買財務軟件，因此她只能用手工記賬。為了節(jié) 省時間并保證記賬的準確性，小李使用 Excel 編制銀行存款日記賬。 請根據(jù)該 公司九月份的 銀行流水賬表格 .docx ，并按照下述要求，在 Excel 中建立銀行 存款日記賬：1. 按照表中所示依次輸入原始數(shù)據(jù)， 其中：在月列中以填充的方式輸入 九 ， 將表中的數(shù)值的格式設(shè)為數(shù)值、保留 2 位小數(shù)。2 .輸入并填充公式：在 余額列輸入計算公式，余額 =上期余額 +本期借方-本期 貸方，以

33、自動填充方式生成其他公式。3. 方向列中 只能有借、貸、平三種選擇， 首先用數(shù)據(jù)有效性控制該列的輸入范 圍為借、貸、平三種中的一種，然后通過 IF 函數(shù)輸入 方向列內(nèi)容，判斷條件 如下所列：余額 大于 0 等于 0 小于 0方向 借 平 貸4. 設(shè)置格式：將第一行中的各個標題居中顯示； 為數(shù)據(jù)列表自動套用格式后將其 轉(zhuǎn)換為區(qū)域。5. 通過分類匯總，按日計算借方、貸方發(fā)生額總計并將匯總行放于明細數(shù)據(jù)下方。6. 以文件名 銀行存款日記賬 .xlsx 進行保存。答疑：1. 【解題步驟】步驟 1：首先我們按照素材中 銀行流水賬表格 .docx 所示依次輸入原始數(shù)據(jù)。 步驟2:然后我們在月列中以填充的方

34、式輸入九。將鼠標光標放置 A2單元 格右下角的填充柄處，待鼠標變成黑色十字形狀后拖住不放，向下填充直至 A6 單元格處。步驟3:,將表中數(shù)值格式設(shè)為數(shù)值、保留 2位小數(shù)。此處我們選擇E3單元格， 單擊鼠標右鍵在彈出的的下拉列表中選擇 設(shè)置單元格格式 命令。步驟 4:在彈出的 設(shè)置單元格格式 對話框中切換至 數(shù)字選項卡。在 分類中 選擇數(shù)值，在右側(cè)的小數(shù)位數(shù)微調(diào)框中選擇 2。步驟 5:單擊 確定按鈕后即可完成設(shè)置。步驟 6:按照同樣的方式分別為其他數(shù)值的格式設(shè)為數(shù)值、保留 2位小數(shù)。2. 【解題步驟】步驟1：根據(jù)題意，在H3單元格中輸入余額的計算公式二H2+E3-F3，按Enter 鍵確認即可得

35、出本期余額的計算結(jié)果。步驟2:其他余額的計算米用自動填充公式的方式。選中H3單兀格，將鼠標光標放置H3單元格右下角的填充柄處，待鼠標變成黑色十字形狀后拖住不放，向 下填充直至H6單元格處。3. 【解題步驟】步驟1:選定方向列中的G2 G6區(qū)域，在【數(shù)據(jù)】選項卡下的【數(shù)據(jù)工具】 組中，單擊 數(shù)據(jù)有效性 按鈕，打開 數(shù)據(jù)有效性 對話框。步驟 2：切換至設(shè)置選項卡，在 允許下拉列表框中選擇 序列命令；在來 源文本框中輸入 借,貸,平（注意要用英文輸入狀態(tài)下的逗號分隔） ，勾選忽 略空值 和 提供下拉菜單 兩個復選框。步驟 3：再切換至 輸入信息選項卡，勾選 選定單元格顯示輸入信息 復選框， 在輸入信

36、息中輸入請在這里選擇 。步驟4：單擊確定按鈕后即可在 方向列中看到實際效果。步驟5:根據(jù)題意，通過IF函數(shù)輸入方向列內(nèi)容。在G2單元格中輸入=IF（H2=0,平,IF（H20,借,貸），然后按Enter鍵確認。步驟 6:按照同樣的方式通過 IF 函數(shù)向方向列的其他單元格內(nèi)輸入內(nèi)容。4. 【解題步驟】步驟1:選中A1: H1單元格，在【開始】菜單下的【對齊方式】組中單擊 居中 按鈕，即可將第一行中的各個標題居中顯示。步驟 2:選中數(shù)據(jù)區(qū)域，在【開始】菜單下的【樣式】組中單擊 套用表格格式 按鈕，在彈出的下拉列表中選中一種自動套用的格式。此處，我們選擇 中等深 淺組中的 表樣式中等深淺 2。步驟

37、3:單擊鼠標選中后彈出 套用表格式 對話框，在此對話框中勾選 表包含 標題 復選框。步驟 4:單擊確定按鈕后即可為數(shù)據(jù)列表套用自動表格樣式。步驟 5:選中表格，在【表格工具】的【設(shè)計】選項卡下，單擊【工具】組中的 轉(zhuǎn)換為區(qū)域 按鈕。步驟 6:單擊是按鈕即可將表格轉(zhuǎn)換為普通區(qū)域。5. 【解題步驟】步驟 1:選中數(shù)據(jù)區(qū)域，在【數(shù)據(jù)】選項卡下的【分級顯示】組中，單擊 分類 匯總按鈕。步驟2:彈出分類匯總對話框，在分類字段中選擇日 ，在匯總方式中選 擇求和，在選定匯總項中勾選本期借方和本期貸方。步驟 3 :單擊確定按鈕后即可看到實際匯總效果。6【解題步驟】步驟：單擊功能區(qū)中的 【文件】選項卡，在打開的

38、后臺視圖中單擊 另存為 按鈕， 在隨后彈出的 另存為 對話框中以文件名 銀行存款日記賬 .xlsx 進行保存。第 2 題：（電子表格題，本題 30 分） 小趙是某書店的銷售人員，負責計算機類圖書的銷售情況，并按月份上報分析 結(jié)果。 2013年 1月份時，她需要將 2012年 12月份的銷售情況進行匯總，請根 據(jù)提供的 Excel.xlsx 文件幫助她完成下列工作：1. 對Excel.xlsx 進行如下設(shè)置：將銷售統(tǒng)計表中的單價列數(shù)值的格式設(shè) 為會計專用、保留 2 位小數(shù)。2. 對數(shù)據(jù)表銷售統(tǒng)計進行適當?shù)母袷交?，操作如下：合?A1： E1單元格，為 標題名12 月份計算機圖書銷售情況統(tǒng)計表 設(shè)

39、置適當?shù)膶R方式、字體、字號 以及顏色；為數(shù)據(jù)區(qū)域設(shè)置邊框底紋以使工作表更加美觀等。3. 將工作表銷量中的區(qū)域 B3:D1 6定義名稱為 銷量信息。在銷售統(tǒng)計表中 的銷量列右側(cè)增加一列銷售額，根據(jù)銷售額二銷量X單價構(gòu)建公式計算出 各類圖書的銷售額。要求在公式中通過VLOOKU函數(shù)自動在工作表銷量中查找 相關(guān)商品的具體銷量。并在公式中引用所定義的名稱 銷量信息。4. 為銷售統(tǒng)計工作表創(chuàng)建一個數(shù)據(jù)透視表， 放在一個名為 數(shù)據(jù)透視分析 的新 的工作表中。5. 為數(shù)據(jù)透視表數(shù)據(jù)創(chuàng)建一個類型為餅圖的數(shù)據(jù)透視圖， 設(shè)置數(shù)據(jù)標簽顯示在外 側(cè)，將圖表的標題改為 12 月份計算機圖書銷量 。6. 將工作表另存為

40、 計算機類圖書 12月份銷售情況統(tǒng)計 .xlsx 文件。答疑： 1 . 【解題步驟】步驟1:將表中數(shù)值格式設(shè)為會計專用、保留 2位小數(shù)。此處我們選擇 C4單元 格，單擊鼠標右鍵在彈出的的下拉列表中選擇 設(shè)置單元格格式 命令。步驟 2：在彈出的 設(shè)置單元格格式 對話框中切換至 數(shù)字選項卡。在分類中 選擇會計專用，在右側(cè)的小數(shù)位數(shù)微調(diào)框中選擇2。步驟 3 ：單擊確定按鈕后即可完成設(shè)置。步驟 4：按照同樣的方式分別為其他數(shù)值的格式設(shè)為會計專用、保留 2位小數(shù)。2. 【解題步驟】步驟 1：在銷售統(tǒng)計 表中，選中 A1： E1 單元格，單擊鼠標右鍵，在彈出的下 拉列表中選擇 設(shè)置單元格格式 命令，彈出設(shè)

41、置單元格格式 對話框。在對齊 選項卡下的文本控制組中，勾選合并單元格；在文本對齊方式組的水平 對齊選項下選擇居中。而后單擊確定即可。步驟 2：按照同樣的方式打開 設(shè)置單元格格式 對話框，切換至 字體選項卡， 在字體下拉列表中選擇一種合適的字體， 此處我們選擇 黑體。在字號下拉 列表中選擇一種合適的字號， 此處我們選擇14。在顏色下拉列表中選擇合適 的顏色，此處我們選擇 紅色 ，單擊確定按鈕后即可看到實際效果。步驟 3：選中數(shù)據(jù)區(qū)域，單擊鼠標右鍵，在彈出的下拉菜單中選擇 設(shè)置單元格 格式 命令。步驟4：在打開的設(shè)置單元格格式 對話框中，切換至 邊框選項卡，在預置 組中單擊 外邊框按鈕后即可在 邊

42、框組中預覽效果。步驟 5：再切換至 填充選項卡下，在 背景色組中選擇一種合適的填充顏色。 步驟 6：單擊確定按鈕后即可在工作表中看到實際效果。3【解題步驟】步驟 1：在銷量工作表中選中 B3:D16 區(qū)域，單擊鼠標右鍵，在彈出的下拉列 表中選擇 定義名稱命令，打開新建名稱 對話框。在名稱中輸入銷量信息 后單擊確定即可。步驟2:在銷售統(tǒng)計表中選中E3單元格，輸入銷售額。步驟3:在D4單元格中輸入二VL00KUPA4，銷量信息，3, FALSE ，然后按Enter 鍵確認即可把 銷量工作表中對應的銷量數(shù)據(jù)引入到 銷售統(tǒng)計工作表 中。步驟4:拖動D4右下角的填充柄直至最下一行數(shù)據(jù)處，完成銷量數(shù)據(jù)的填

43、充。步驟5:根據(jù)銷量以及單價計算銷售額。在 E4單元格中輸入=C4*D4,即可得出 結(jié)果。步驟6:拖動E4右下角的填充柄直至最下一行數(shù)據(jù)處，完成銷售額的填充。4【解題步驟】步驟 1:在創(chuàng)建數(shù)據(jù)透視表之前，要保證數(shù)據(jù)區(qū)域必須要有列標題，并且該區(qū)域 中沒有空行。步驟2:選中銷售統(tǒng)計工作表的數(shù)據(jù)區(qū)域，在【插入】選項卡下的【表格】組 中單擊數(shù)據(jù)透視表按鈕，打開創(chuàng)建數(shù)據(jù)透視表 對話框。步驟 3:在選擇一個表或區(qū)域 項下的表/區(qū)域框顯示當前已選擇的數(shù)據(jù)源區(qū) 域。此處對默認選擇不作更改。步驟 4:指定數(shù)據(jù)透視表存放的位置:選中 新工作表，單擊確定按鈕。步驟5: Excel 會將空的數(shù)據(jù)透視表添加到指定位置，

44、 并在右側(cè)顯示 數(shù)據(jù)透視表 字段列表 窗口。雙擊 Sheet1 ，重命名為 數(shù)據(jù)透視分析 。5【解題步驟】步驟 1:根據(jù)題意，在創(chuàng)建數(shù)據(jù)透視圖之前，先選擇要添加到報表的字段。默認 情況下，非數(shù)值字段將會自動添加到 行標簽區(qū)域，數(shù)值字段會添加到 數(shù)值 區(qū)域。此處， 我們在字段列表中依次單擊選中 書名和銷售額兩個字段， 透視 表區(qū)域中將自動匯總銷售額合計值。步驟 2:單擊數(shù)據(jù)透視表區(qū)域中的任意單元格，而后在 數(shù)據(jù)透視表工具 的選 項選項卡下，單擊 工具組中的數(shù)據(jù)透視圖 按鈕，打開插入圖表對話框。 根據(jù)題意，此處我們選擇 餅圖。步驟 3 :單擊確定按鈕后即可看到實際效果。步驟 4:選中數(shù)據(jù)透視圖，

45、在【數(shù)據(jù)透視圖工具】 中的【布局】選項卡下單擊 【標 簽】組中的 數(shù)據(jù)標簽按鈕，在彈出的下拉列表中選擇 數(shù)據(jù)標簽外 命令。步驟 5:按照題意修改圖表的標題。首先選中圖表的標題，輸入新的標題名12月份計算機圖書銷量 。6【解題步驟】步驟:最后單擊 文件選項卡下的 另存為按鈕將文件保存 計算機類圖書 12 月份銷售情況統(tǒng)計 .xlsx 文件。第2題:（電子表格題，本題 30分）小偉在自己在讀的學院里面勤工助學，兼職當副院長的助理一職，平時主要負 責對各種文案或者數(shù)據(jù)的整理?，F(xiàn)在，信息與計算科學專業(yè)的期末考試的部分 成績需要錄入文件名為 考生成績單 .xlsx 的 Excel 工作薄文檔中去。 請根

46、據(jù)下列要求幫助小偉對該成績單進行分析整理:1. 利用條件格式 功能進行下列設(shè)置：將大學物理和大學英語兩科中低于 80 分的成績所在的單元格以一種顏色填充，其他五科中大于或等于 95 分的成績以 另一種顏色標出，所用顏色以不遮擋數(shù)據(jù)為宜。2. 對工作表 考生成績單 .xlsx 中的數(shù)據(jù)列表進行如下格式化操作：將第一列 學號 設(shè)置為文本，設(shè)置成績列為保留兩位小數(shù)的數(shù)值。 改變數(shù)據(jù)列表中的行高、 列寬，改變字體、字號，設(shè)置邊框和底紋、設(shè)置對齊方式。3. 利用sum和average函數(shù)計算每一個學生的總分以及平均成績。4. 復制工作表 考生成績單 .xlsx ，將副本放置于原表之后，新表重新命名為 成

47、績單分類匯總 。5. 通過分類匯總功能求出每個班各科的平均成績，并將每組結(jié)果分頁顯示。6. 創(chuàng)建一個簇狀柱形圖，對每個班各科平均成績進行比較。答疑： 1. 【解題步驟】步驟1:選中F2: G14,單擊【開始】選項卡下【樣式】組中的條件格式按鈕, 選擇突出顯示單元格規(guī)則 中的其他規(guī)則 ，彈出新建格式規(guī)則 對話框，在 編輯規(guī)則說明選項下設(shè)置單元格值小于 80。然后單擊格式按鈕,彈出設(shè)置 單元格格式 對話框,在 填充選項卡下選擇 紅色,單擊確定按鈕。步驟 2:設(shè)置完畢后返回工作表界面。步驟3：先選中D2: E14,然后按住Ctrl鍵再選中H2: J14,按照上述同樣方法， 把單元格值大于或等于 95

48、 的字體顏色設(shè)置為黃色。2. 【解題步驟】步驟 1:選中 學號所在的列,單擊鼠標右鍵,在彈出的下拉菜單中選擇 設(shè)置 單元格格式 命令,在彈出的的 設(shè)置單元格格式 對話框中切換至 數(shù)字選項卡, 在分類下選擇文本。步驟 2:選中所有成績列,單擊鼠標右鍵,選擇 設(shè)置單元格格式 命令,在彈出 的的設(shè)置單元格格式 對話框中切換至 數(shù)字選項卡,在分類下選擇數(shù)值, 在小數(shù)位數(shù)微調(diào)框中設(shè)置小數(shù)位數(shù)為 2 。步驟 3:設(shè)置完畢。步驟 4:選中數(shù)據(jù)區(qū)域,單擊【開始】選項卡下【單元格】組中的格式按鈕,在下拉菜單中選擇 行高 ,彈出行高對話框,設(shè)置行高為 16。步驟 5:單擊【開始】選項卡下【單元格】組中的 格式按鈕

49、,在彈出的下拉菜 單中選擇 列寬,彈出列寬對話框,設(shè)置列寬為 11。步驟 6:設(shè)置完畢。步驟 7:右擊鼠標選擇 設(shè)置單元格格式 ,在彈出的 設(shè)置單元格格式 對話框中 的字體選項卡下,設(shè)置 字體為黑體,字號為 10。再選中第一行的單元格, 按照同樣的方式設(shè)置字形為 加粗。步驟 8:繼續(xù)選中數(shù)據(jù)區(qū)域, 按照上述同樣的方式打開 設(shè)置單元格格式 對話框, 在設(shè)置單元格格式 對話框中的 對齊選項卡下,設(shè)置水平對齊與垂直對齊 都為居中。步驟9:切換至邊框選項卡,在 預置組中選擇外邊框和內(nèi)部。 步驟10:再切換至填充選項卡下,在 背景色組中選擇淺綠步驟 11：最后單擊 確定 按鈕。3. 【解題步驟】步驟 1

50、：在 K2 單元格中輸入 =SUM（D2:J2） ，按 Enter 鍵后該單元格值為 629.50 ，拖動 K2 右下角的填充柄直至最下一行數(shù)據(jù)處，完成總分的填充。步驟2:在L2單元格中輸入二AVERAGE（D2:J2）：按Enter鍵后該單元格值為89.93 ，拖動 L2 右下角的填充柄直至最下一行數(shù)據(jù)處，完成平均分的填充。4. 【解題步驟】步驟 1:復制工作表 學生成績單 ，粘貼到 Sheet2 工作表中。 在期末成績 上右擊鼠標，在彈出的的快捷菜單中選擇 移動或復制 命令。然后在彈出的 移 動或復制工作表對話框中的下列選定工作表之前中選擇Sheet2，并勾選 建立副本 復選框。步驟 2:

51、建立的副本完畢。步驟 3:為副表重新命名。雙擊 期末成績（ 2） 呈可編輯狀態(tài)，然后輸入 成績 單分類匯總 字樣。5. 【解題步驟】步驟1:對班級按升序進行排序，選中 C2 C14區(qū)域列表，單擊【數(shù)據(jù)】選項卡 下【排序和篩選】組中的 升序按鈕，彈出 排序提醒對話框，單擊 擴展選定 區(qū)域單選按鈕。然后單擊 排序按鈕。步驟2:選中C15,單擊【數(shù)據(jù)】選項卡下【分級顯示】組中的分類匯總按鈕, 彈出分類匯總對話框，單擊 分類字段組中的下拉按鈕選擇 班級，單擊匯 總方式組中的下拉按鈕選擇 平均值，在選定匯總項 組中勾選高等代數(shù) 、 數(shù)學分析 、大學英語 、大學物理 、VB 程序設(shè)計 、C 語言程序設(shè)計

52、、 Matlab 復選框，并勾選 每組數(shù)據(jù)分頁 復選框。步驟 3:單擊確定按鈕。6. 【解題步驟】步驟 1:選中每個班各科平均成績所在的單元格， 單擊【插入】 選項卡下【圖表】 組中柱形圖按鈕，在彈出的下拉列表中選擇 簇狀柱形圖 。步驟 2:右擊圖表區(qū)，選擇 選擇數(shù)據(jù) ，彈出選擇數(shù)據(jù)源 對話框。首先選中 圖例項（系列） 選項中的 系列 1，而后單擊 圖例項（系列） 選項中的 編輯 按鈕，彈出 編輯數(shù)據(jù)系列 對話框，在 系列名稱中輸入1 。步驟 3:單擊確定按鈕后，即可返回 選擇數(shù)據(jù)源 對話框。步驟 4:按照同樣方法編輯 系列 2、系列 3為2 、3 。步驟 5:按照同樣的方式打開 選擇數(shù)據(jù)源

53、對話框，選中 水平（分類）軸標簽 下的1 ，單擊編輯按鈕，彈出軸標簽對話框， 在軸標簽區(qū)域 下輸入高等 代數(shù),數(shù)學分析,大學英語,大學物理,VB程序設(shè)計,C語言程序設(shè)計，Matlab。 步驟 6:單擊確定按鈕后，返回 選擇數(shù)據(jù)源 對話框。步驟 7:單擊確定按鈕后，返回工作表中的簇狀柱形圖。 第2題:（電子表格題，本題 30分）請在【答題】菜單下選擇【進入考生文件夾】命令，并按照題目要求完成下面的 操作。注意:以下的文件必須保存在考生文件夾下 小劉是一所初中的學生處負責人，負責本院學生的成績管理。他通過 Excel 來 管理學生成績，現(xiàn)在第一學期期末考試剛剛結(jié)束，小劉將初一年級三個班級部 分學生

54、成績錄入了文件名為 第一學期期末成績 .xlsx 的 Excel 工作簿文檔中。 請你根據(jù)下列要求幫助小劉同學對該成績單進行整理和分析：1. 請對第一學期期末成績 工作表進行格式調(diào)整， 通過套用表格格式方法將所有 的成績記錄調(diào)整為一致的外觀格式， 并對該工作表 第一學期期末成績 中的數(shù)據(jù) 列表進行格式化操作： 將第一列 學號列設(shè)為文本，將所有成績列設(shè)為保留兩位 小數(shù)的數(shù)值，設(shè)置對齊方式，增加適當?shù)倪吙蚝偷准y以使工作表更加美觀。2. 利用條件格式 功能進行下列設(shè)置：將語文、數(shù)學、外語三科中不低于110分的成績所在的單元格以一種顏色填充，所用顏色深淺以不遮擋數(shù)據(jù)為宜。3. 利用sum和average函數(shù)計算每一個學生的總分及平均成績。4.