全國計算機(jī)等級考試二級MSoffice高級應(yīng)用考試真題解析

1、2015年12月全國計算機(jī)等級考試二級MS office高級應(yīng)用考試真題解析題干：【背景素材】曉雨任職人力資源部門，她需要對企業(yè)員工Office應(yīng)用能力考核報告進(jìn)行完善和分析。按照如下要求幫助曉雨完成數(shù)據(jù)處理工作。1 .在考生文件夾下，將"Excel素材.xlsx ”文件另存為"Excel.xlsx ”，后續(xù)操作 均基于此文件，否則不得分。2 .在“成績單”工作中，設(shè)置工作表標(biāo)簽顏色為標(biāo)準(zhǔn)紅色；對數(shù)據(jù)區(qū)域套用“表樣 式淺色16”表格格式，取消鑲邊行后將其轉(zhuǎn)換為區(qū)域。3 .刪除“姓名”列中所有漢語拼音字母，只保留漢字。4 .設(shè)置“員工編號”列的數(shù)據(jù)格式為“001, 002,

2、334”；在G3:K336單元格區(qū)域中所有空單元格中輸入數(shù)值005 .計算每個員工 5個考核科目（Word、Excel、PowerPoint、Outlook和Visio ）的 平均成績，并填寫在“平均成績”歹U。6 .在“等級”列中計算并填寫每位員工的考核成績等級，等級的計算規(guī)則如下：等級分類計算規(guī)則不合格5個考核科目中科目成績低于60分及格60分=平均75分良75=平均成績85分優(yōu)平均成績=85分7.適當(dāng)調(diào)整“成績單”工作表 B2:M336單元格區(qū)域內(nèi)各列列寬，并將該區(qū)域內(nèi)數(shù)據(jù) 設(shè)置為水平、垂直方向均居中對齊。8 .依據(jù)自定義序列”研發(fā)部一物流部一采購部一行政部一生產(chǎn)部一市場部”的順序進(jìn)行排

3、序；如果部門名稱相同，則按照平均成績由高到低的順序排序。9 .設(shè)置“分?jǐn)?shù)段統(tǒng)計”工作表標(biāo)簽顏色為藍(lán)色；參考考生文件夾中的“成績分布及 比例.png”示例，以該工作表 B2單元格為起始位置創(chuàng)建數(shù)據(jù)透視表，計算“成績單” 工作中平均成績在各分?jǐn)?shù)段的人數(shù)以及所占比例（數(shù)據(jù)透視表的數(shù)據(jù)格式設(shè)置以參考示例為準(zhǔn)，其中平均成績各分?jǐn)?shù)段下限包含臨界值）；根據(jù)數(shù)據(jù)透視表在單元格區(qū)域 E2:L17 內(nèi)創(chuàng)建數(shù)據(jù)透視圖（數(shù)據(jù)透視圖圖表類型、數(shù)據(jù)系列、坐標(biāo)軸、圖例等設(shè)置以參考示例 為準(zhǔn)）。10 .根據(jù)“成績單”工作表中的“年齡”和“平均成績”兩列數(shù)據(jù)，創(chuàng)建名為“成 績與年齡”的圖表工作表（參考考生文件夾中的“成績與年齡

4、.png”示例，圖表類型、樣式、圖表元素均以此示例為準(zhǔn)）。設(shè)置圖表工作表標(biāo)簽顏色為綠色，并將其放置在全部工作表的最右側(cè)。11 . 將“成績單”工作表中的數(shù)據(jù)區(qū)域設(shè)置為打印區(qū)域，并設(shè)置標(biāo)題行在打印時可以重復(fù)出現(xiàn)在每頁頂端。12. 將所有工作表的紙張方向都設(shè)置為橫向，并為所有工作表添加頁眉和頁腳，頁眉中間位置顯示“成績報告”文本，頁腳樣式為“第 1 頁，共？頁”。解析：1. 雙擊考生文件夾下的文檔“Excel 素材 .xlsx ”用 Excel2010 打開它。在Excel窗口單擊“文件”菜單的“另存為”命令，在彈出的“另存為”的對話框輸入文件名“ Excel.xlsx ”（其中 .xlsx 可省

5、略） ， 文件類型選擇“ Excel 工作簿 （ .xlsx ） ”， 單擊 “保存”按鈕以新的文件名保存保存文件。 Excel 窗口自動關(guān)閉了“Excel 素材 .xlsx ”，并自動切換為對“Excel.xlsxi ”的編輯狀態(tài)，使后續(xù)操作均基于此文件。2. （ 1）右擊“成績單”工作表標(biāo)簽，從快捷菜單中選擇“工作表標(biāo)簽顏色”中的。（ 2）選中數(shù)據(jù)區(qū)的任意一個單元格，單擊“開始”選項卡“樣式”工具組的“套用表格格式”，從下列表中選擇“表樣式淺色 16”。在彈出的對話框中，確認(rèn)“表數(shù)據(jù)的來源”為“ 二$B$2:$M$336”，勾選“表包含標(biāo)題”，單擊“確定”按鈕。3）在“表格工具設(shè)計”選項卡

6、“表格樣式選項”中，取消勾選“鑲邊行”（ 4）在“表格工具- 設(shè)計”選項卡“工具”工具組中單擊“轉(zhuǎn)換為區(qū)域”按鈕，在彈出的提示框選擇“是”。3. （1）選中C3: C336單元格區(qū)域，按Ctrl+C鍵復(fù)制。啟動一個 Word程序，在word 中單擊“開始”選項卡“剪貼板”工具組的“粘貼”按鈕的向下箭頭，從下列拉表中選擇“只保留文本”圖標(biāo)（A形圖標(biāo)），將這些“姓名”的文本粘貼到word中。（2）在word 中，單擊“開始”選項卡“編輯”工具組的“替換”按鈕，打開“查找和替換”對話框。單擊對話框左下角的“更多”按鈕，展開更多選項。將插入點定位到 “查找內(nèi)容”框中， 然后單擊對話框底部的 “特殊格式

7、”按鈕， 從下拉菜單中選擇 “任意字母” ，則在“查找內(nèi)容”框中自動插入了內(nèi)容“$”。保持“替換為”框中內(nèi)容為空白，單擊“全部替換”按鈕。則所有英文字母被刪除。（3）刪除“查找內(nèi)容”框中的任何內(nèi)容，然后在其中輸入一個空格；保持“替換為”框中的內(nèi)容為空白，單擊“全部替換”按鈕。則所有空格被刪除。單擊“關(guān)閉”按鈕關(guān)閉“查找和替換”對話框。注意以上“（ 2） ”、 “（ 3） ”都是在 word 中操作，不是在Excel 中操作的。4. 4） 在 word 中按 Cttrl+A 鍵選中所有文本，按 Ctrl+C 鍵復(fù)制。 然后切換回Excel窗口，保持C3:C336單元格區(qū)域中的選中狀態(tài)，單擊Exc

8、el的“開始”選項卡“剪貼板”工具組的“粘貼”按鈕的向下箭頭，從下拉列表中選擇“匹配目標(biāo)格式”圖標(biāo)，則將刪除所有漢語拼音字母和空格的姓名粘貼回Excel 。5. （ 1）在B3 單元格中輸入“'001 ” （同時單元格原來的內(nèi)容被覆蓋，注意輸入時開頭的英文單引號），按回車鍵確認(rèn)輸入。然后雙擊B3單元格的填充柄，自動填充到B336 單元格（考生如直接填充1、2的數(shù)字，然后再設(shè)置單元格格式的代碼為“000”，考試時也可得分）。（2）選中G3: K336單元格區(qū)域，然后單擊“開始”選項卡“編輯”工具組的“查找和選擇”按鈕，從下拉菜單中選擇“定位條件”。在彈出的對話框中，選擇“空值”單選框，單

9、擊“確定”按鈕。 Excel 自動選中了該區(qū)域的所有空白單元格。保持這些空白單元格的選中狀態(tài)，在編輯欄中輸入0， 按 Ctrl+Enter 鍵， 則這些單元格同時被填充了 0。6. 在 L3 單元格中輸入或通過對話框構(gòu)造公式“ =AVERAG（ EG3： K3） ”， 確認(rèn)輸入后，雙擊L3單元格的填充柄，完成其他人平均成績的填充計算。7. 判斷不及格的方法有很多，考生可任意選用。這里介紹一種使用COUNTIF®數(shù)的做法：用“COUNTI（F G3： K3， ” <60”） ”求出第 1 位員工的低于60 分的科目個數(shù)，如此值>0就說明不合格。根據(jù)這一思路，在M3單元格中輸

10、入或通過對話框構(gòu)造公式為“=IF（ COUNTIF（G3： K3， ” <60”） >0” , 不合格” IF（L3<75 ， 及格 “ IF（L3<85 ，“良”，“優(yōu)”） ） ） ”。確認(rèn)輸入后，雙擊 M3單元格的填充柄，完成所有員工的“等級”的填充計算。注意： 經(jīng)過以上計算和雙擊填充柄復(fù)制公式的操作，表格某些單元格的邊框線可能發(fā)生變化，例如很單元格的“上框線”都變成了與第 1 為員工單元格相同的、 “較粗” 的上框線線性。應(yīng)選中B2: M336單元格區(qū)域，先單擊“開始”選項卡“編輯”工具組的“清除”按鈕，從下拉菜單中選擇“清除格式”。然后重復(fù)“2（ 2） -4 ”

11、的步驟重新套用表格格式、取消鑲邊行、轉(zhuǎn)換區(qū)域，以修復(fù)線性。8. 選中B2:M336單元格區(qū)域，單擊“開始”選項卡“對齊方式”工具組的“垂直居 中” 、 “居中”按鈕，使這兩個按鈕為高亮狀態(tài)。適當(dāng)設(shè)置列寬，使各列數(shù)據(jù)都能顯示完全即可；不必過多設(shè)置列寬，考試時對列寬不做評分。9. （ 1）選中數(shù)據(jù)區(qū)的任意一個單元格，單擊“數(shù)據(jù)”選項卡“排序和篩選”工具組的“排序”按鈕。打開“排序”對話框。在對話框中設(shè)置“主要關(guān)鍵字”為“部門”，“排序依據(jù)”為“數(shù)值”， “次序”為“自定義序列”。（ 2）這時彈出“自定義序列”對話框。在對話框中左側(cè)選擇“新序列”，在右側(cè)的“輸入序列”框中， 依次輸入幾個部門的名稱，

12、輸入每個名稱按Enter 鍵換行。 即在 “輸入序列”框中輸入內(nèi)容如下所示：研發(fā)部物流部采購部行政部生產(chǎn)部市場部單擊“確定”按鈕關(guān)閉“自定義序列”對話框，回到“排序”對話框。（ 4）單擊“添加條件”按鈕添加一個條件。然后設(shè)置“次要關(guān)鍵字”為“平均成績” ， “排序依據(jù)”為“數(shù)值”，次序為“降序”。單擊“確定”按鈕，完成排序。10. （ 1）右擊“分?jǐn)?shù)段統(tǒng)計”工作表標(biāo)簽，從快捷菜單中選擇“工作表標(biāo)簽顏色”中的“標(biāo)準(zhǔn)色”的“藍(lán)色”。（ 2）在創(chuàng)建數(shù)據(jù)透視表之前，先在“成績單”工作表中添加一個輔助列：在“成績單”工作表的N2單元格中輸入“平均成績分?jǐn)?shù)段”。在N3單元格中輸入或通過對話框構(gòu)造公式“ =

13、IF（L3<60 ， ” <60” ,IF（L3<70 ， ” 60-70” ,IF（L3<80 ， ” 70-80” ,IF（L3<90 ， ”80-90” , ” 90-100) ”，這樣是平均成績各分?jǐn)?shù)段下限包含臨界值，如“60-70”檔包含 60 但不包含70。 注意公式中的“ 60-70 ”， “ 70-80” 等內(nèi)容要放入一對英文引導(dǎo)之內(nèi)，否則將被作為公式計算出結(jié)果-10,就不正確了。確認(rèn)輸入后，雙擊N3單元格的填充柄填充到N336單元格。( 3)選中數(shù)據(jù)區(qū)域的任意一個單元格，單擊“插入”選項卡“表格”工具組的“數(shù)據(jù)透視表”按鈕的下半部分，從下拉菜單中

14、選擇“數(shù)據(jù)透視表”。在打開的“創(chuàng)建數(shù)據(jù)透視表” 對話框中，確認(rèn)選擇了 “選擇一個表或區(qū)域”， 其 “表 /區(qū)域” 為 “成績單！$B$2：$N$336'。在"選擇放置數(shù)據(jù)透視表的位置”中選擇“現(xiàn)有工作表” ，單擊“位置”框右 側(cè)的“折疊對話框”按鈕折疊對話框，再單擊“分?jǐn)?shù)段統(tǒng)計”工作表標(biāo)簽，當(dāng)視圖切換到“分?jǐn)?shù)段統(tǒng)計”工作表中時，再單擊其中的B2單元格，使“位置”框中的內(nèi)容為“分?jǐn)?shù)段統(tǒng)計！ $B$23”。再次單擊“折疊對話框”按鈕還原對話框，單擊“確定”按鈕。則 “分?jǐn)?shù)段統(tǒng)計”工作表進(jìn)入了數(shù)據(jù)透視表的設(shè)計環(huán)境。( 3)在“分?jǐn)?shù)段統(tǒng)計”工作表右側(cè)的“數(shù)據(jù)透視表字段列表”任務(wù)窗格中

15、，拖動“分?jǐn)?shù)段”到“行標(biāo)簽”區(qū)域，拖動“平均成績”到“匯數(shù)值”區(qū)域，再次拖動一個“平 均成績”到“匯數(shù)值”區(qū)域，使“匯數(shù)值”區(qū)域中有兩項“求和項：平均成績” 。(4)單擊“匯數(shù)值”區(qū)域中的第1項“求和項：平均成績”，從下拉菜單中選擇“值字段設(shè)置”，打開“值字段設(shè)置”對話框。在對話框中選擇“值匯總方式”標(biāo)簽頁的“計數(shù)” ，單擊“確定”按鈕。(5)單擊“匯數(shù)值”區(qū)域中的第2項“求和項：平均成績”，從下拉菜單中選擇“值字段設(shè)置”，打開“值字段設(shè)置”對話框。在對話框中選擇“值匯總方式”標(biāo)簽頁，在“計算類型”中選擇“計數(shù)”。然后切換到“值顯示方式”標(biāo)簽頁，在“值顯示方式”下拉列表中設(shè)置為“列匯總的百分比

16、”，單擊“確定”按鈕。(6)在C2單元格單元格中刪除原有內(nèi)容，輸入“人數(shù)”；在D2單元格中刪除原有內(nèi)容，輸入“所占比例”。選中B2: D8單元格區(qū)域，在“開始”選項卡“對齊方式”工具組中單擊“居中”按鈕，使按鈕為高亮狀態(tài)，各單元格內(nèi)容水平居中對齊。選中D3:D8單元格區(qū)域，單擊“開始”選項卡“數(shù)字”工具組的“減少小數(shù)位數(shù)”按鈕，使百分比數(shù)值請保留1 位小數(shù)。拖動列標(biāo)區(qū)各列之間的分隔線，適當(dāng)縮小C列和D列列寬，適當(dāng)增大 B列列寬。選中B2:D7的單元格區(qū)域，單機(jī)“插入”選項卡“圖表”工具組右下角的對話框開啟按鈕，打開 “插入圖表”對話框。 在對話框中選擇 “柱形圖”中的 “簇狀柱形圖”，單擊“確

17、定”按鈕。在 “數(shù)據(jù)透視圖工具 -布局” 選項卡 “當(dāng)前所選內(nèi)容”工具組中，從“圖表元素”下拉框中選擇“系列 "所占比例"”選中該系列。然后單擊“數(shù)據(jù)透視圖工具 -設(shè)計”選項卡 “類型” 工具組的 “更改圖表類型”按鈕， 在打開的 “更改圖表類型”的對話框中，選擇“折線圖”中的“帶數(shù)據(jù)標(biāo)記的折線圖”，單擊“確定”按鈕。在 “數(shù)據(jù)透視圖工具 -布局” 選項卡 “當(dāng)前所選內(nèi)容”工具組中，從“圖表元素”下拉框中選擇“系列 " 所占比例" ”選中該系列。然后單擊該工具組中的“設(shè)置所選內(nèi)容格式”按鈕， 彈出“設(shè)置數(shù)據(jù)系列格式”對話框。在對話框中，左側(cè)選擇“系列選項

18、”，右側(cè)在“系列繪制在”中選擇“次坐標(biāo)軸”,單擊“關(guān)閉”按鈕。在“數(shù)據(jù)透視圖工具-布局”選項卡“標(biāo)簽”工具組中，單擊“圖例”按鈕，從 下拉菜單中選擇“在底部顯示圖例”。在 “數(shù)據(jù)透視圖工具 -布局” 選項卡 “當(dāng)前所選內(nèi)容”工具組中，從“圖表元素”下拉框中選擇“次坐標(biāo)軸 垂直（值）軸”選中次坐標(biāo)軸。然后單擊該工具組中的“設(shè)置所選內(nèi)容格式”按鈕， 彈出 “設(shè)置坐標(biāo)軸格式”對話框。在對話框左側(cè)選擇“數(shù)字”，右側(cè)設(shè)置“小數(shù)位數(shù)”為0，單擊“關(guān)閉”按鈕。拖動數(shù)據(jù)透視圖的邊框調(diào)整圖表大小，拖動數(shù)據(jù)透視圖上的空白位置移動圖表位置，將圖表調(diào)整到覆蓋工作表的E2:L7 單元格區(qū)域內(nèi)。11. （1） 切換到 “

19、成績單”工作表， 在工作表的 “名稱框”中輸入 “ E2:E336， L2:L336”，按 Enter 鍵，同時選中工作表的E2:E336 和 L2:L336 單元格區(qū)域（這比按住Ctrl 鍵的同時選中單元格區(qū)域要簡便）。然后單擊“插入”選項卡“圖表”工具組右下角的對話框開啟按鈕，在打開的“插入圖表”對話框中，選擇“ X Y （散點圖）”中的“僅帶數(shù)據(jù) 標(biāo)記的散點圖”，單擊“確定”按鈕。（2）選中圖表，在“圖表工具- 布局”選項卡“當(dāng)前所選內(nèi)容”工具組中，從“圖表元素”下拉框中選擇“系列”" 平均成績" ”選中該系列。然后單擊該工具組中的“設(shè)置所選內(nèi)容格式”按鈕，打開“設(shè)置

20、數(shù)據(jù)系列格式”對話框。在對話框左側(cè)選擇“數(shù)據(jù)標(biāo)記選項”，右側(cè)設(shè)置為“內(nèi)置”，從“類型”下拉框中選擇“圓形”，設(shè)置“大小”為較小一些的大小，如“2”。單擊“關(guān)閉”按鈕。（3）選中圖表，在“圖表工具- 布局”選項卡“當(dāng)前所選內(nèi)容”工具組中，從“圖表元素”下拉框中選擇“垂直（值）軸”選中垂直坐標(biāo)軸。然后單擊該工具組中的“設(shè)置所選內(nèi)容格式”按鈕，彈出“設(shè)置坐標(biāo)軸格式”對話框。在對話框左側(cè)選擇“坐標(biāo)軸選項” ，右側(cè)設(shè)置“最小值”為“固定”、 “ 40”，設(shè)置“最大值”為“固定”、 “ 100”，設(shè)置“主要刻度單位”為“固定”、 “ 10”，單擊“關(guān)閉”按鈕。（4）在“圖表工具- 布局”選項卡“當(dāng)前所選內(nèi)

21、容”工具組中，從“圖表元素”下拉框中選擇“水平（值）軸”選中水平坐標(biāo)軸。然后單擊該工具組中的“設(shè)置所選內(nèi)容格式”按鈕，彈出“設(shè)置坐標(biāo)軸格式”對話框。在對話框左側(cè)選擇“坐標(biāo)軸選項”，右側(cè)設(shè)置“最小值”為“固定”、 “ 20”設(shè)置“最大值”為“固定”、 “ 55”，設(shè)置“主要刻度單位”為“固定”、 “ 5”，單擊“關(guān)閉”按鈕。（5）在“圖表工具- 布局”選項卡“標(biāo)簽”工具組中，單擊“坐標(biāo)軸標(biāo)題”按鈕，從下拉菜單中選擇“主要縱坐標(biāo)軸標(biāo)題”的“豎排標(biāo)題”，然后在圖表中的“坐標(biāo)軸標(biāo)題”中刪除原有內(nèi)容，輸入縱坐標(biāo)軸標(biāo)題為“平均成績”。選中縱坐標(biāo)軸標(biāo)題，在“開始”選項卡“字體”工具組中適當(dāng)設(shè)置“字體”和“字

22、號”，例如“宋體”、 “ 18 磅” 。（6）再次單擊“坐標(biāo)軸標(biāo)題”按鈕，從下拉菜單中選擇“主要橫坐標(biāo)軸標(biāo)題”的“坐標(biāo)軸下方標(biāo)題”，然后在圖表中的“坐標(biāo)軸標(biāo)題”中刪除原有內(nèi)容，輸入橫坐標(biāo)軸標(biāo)題為“年齡”，選中橫坐標(biāo)軸標(biāo)題，在“開始”選項卡“字體”工具組中適當(dāng)設(shè)置“字體”和“字號”，例如“宋體”、 “ 18 磅” 。（7）在“圖表工具- 布局”選項卡“標(biāo)簽”工具組中，單擊“圖例”按鈕，從下拉 菜單中選擇“無（8）在圖表標(biāo)題“平均成績”中刪除原有內(nèi)容，輸入新圖表標(biāo)題“我單位中青年職工 Office 應(yīng)用水平更高”。選中圖表標(biāo)題，在“開始”選項卡“字體”工具組中是到設(shè)置“字體”和“字號”，例如微“微軟雅黑”、 “ 28 磅” 。（9）在“圖表工具- 布局”選項卡“坐標(biāo)軸”工具組中，單擊“網(wǎng)絡(luò)線”按鈕，從下拉菜單中選擇“主要橫網(wǎng)格線”中的“無”。（10）在“圖表工具- 布局”選項卡“分析”工具組中，單擊“趨勢線”按鈕，從下拉菜單中選擇“其他趨勢線選項”。在彈出的“設(shè)置趨勢線格式”對話框中，左側(cè)選擇“趨勢線選項”，右側(cè)選擇“線性”，并勾選“顯示公式”和“顯示 R 平方值” ，單擊“關(guān)閉”按鈕。拖動圖標(biāo)中的“公式”和“R平方值