2025年MSOffice高級應用計算機二級考試真題卷（Excel數據分析）考試時間：______分鐘總分：______分姓名：______一、Excel基本操作與數據處理要求：熟練掌握Excel的基本操作，包括數據輸入、格式化、排序、篩選、數據驗證等，并能對數據進行簡單的統計分析。1.在Excel中創建一個工作簿，包含三個工作表：Sheet1、Sheet2、Sheet3。2.在Sheet1中輸入以下數據：|姓名|性別|年齡|學號||----|----|----|----||張三|男|20|20250001||李四|女|22|20250002||王五|男|21|20250003||趙六|女|23|20250004||周七|男|20|20250005|3.在Sheet2中，將Sheet1中的數據復制一份。4.對Sheet2中的數據進行以下操作：a.將性別列的“男”替換為“男同學”，“女”替換為“女同學”。b.對年齡列進行升序排序。c.在姓名列中篩選出“張”字開頭的記錄。d.對學號列應用數據驗證，允許輸入數字，輸入范圍為1-20250005。e.計算年齡列的平均值、最大值、最小值。f.在Sheet2的底部插入一個新行，包含以下數據：|姓名|性別|年齡|學號||----|----|----|----||王老師|男|45|20251001|5.在Sheet3中，將Sheet1中的數據復制一份。6.對Sheet3中的數據進行以下操作：a.將性別列的“男”替換為“男生”，“女”替換為“女生”。b.在姓名列中篩選出“李”字開頭的記錄。c.對學號列應用數據驗證，允許輸入數字，輸入范圍為1-20250005。d.計算年齡列的平均值、最大值、最小值。二、Excel函數與公式應用要求：熟練掌握Excel中的常用函數，并能運用公式解決實際問題。1.在Sheet1中，計算以下數據：|姓名|學號|成績||----|----|----||張三|20250001|90||李四|20250002|85||王五|20250003|95||趙六|20250004|80||周七|20250005|75|2.使用公式計算以下內容：a.成績大于90的學生人數。b.成績低于80的學生人數。c.成績最高的學生姓名。d.成績最低的學生姓名。e.成績平均分。f.成績標準差。3.在Sheet2中，將Sheet1中的成績數據復制一份。4.使用公式計算以下內容：a.成績大于90的學生人數。b.成績低于80的學生人數。c.成績最高的學生姓名。d.成績最低的學生姓名。e.成績平均分。f.成績標準差。5.在Sheet3中，將Sheet1中的成績數據復制一份。6.使用公式計算以下內容：a.成績大于90的學生人數。b.成績低于80的學生人數。c.成績最高的學生姓名。d.成績最低的學生姓名。e.成績平均分。f.成績標準差。三、Excel圖表制作與分析要求：熟練掌握Excel中的圖表制作方法，并能對數據進行直觀分析。1.在Sheet1中，將以下數據輸入到表格中：|學科|分數||----|----||數學|80||英語|90||物理|85||化學|95||生物|75|2.在Sheet1中，根據以上數據制作以下圖表：a.折線圖：展示各學科分數隨時間的變化趨勢。b.餅圖：展示各學科分數在總分數中的占比。c.柱狀圖：展示各學科分數的對比情況。3.在Sheet2中，將Sheet1中的數據復制一份。4.在Sheet2中，根據以上數據制作以下圖表：a.折線圖：展示各學科分數隨時間的變化趨勢。b.餅圖：展示各學科分數在總分數中的占比。c.柱狀圖：展示各學科分數的對比情況。5.在Sheet3中，將Sheet1中的數據復制一份。6.在Sheet3中，根據以上數據制作以下圖表：a.折線圖：展示各學科分數隨時間的變化趨勢。b.餅圖：展示各學科分數在總分數中的占比。c.柱狀圖：展示各學科分數的對比情況。四、Excel高級功能應用要求：掌握Excel的高級功能，包括條件格式、數據透視表、宏等，并能運用這些功能提高數據處理效率。1.在Sheet1中，對以下數據設置條件格式：|學號|姓名|成績||----|----|----||20250001|張三|90||20250002|李四|85||20250003|王五|95||20250004|趙六|80||20250005|周七|75|a.成績高于90分的記錄，背景色設置為綠色。b.成績低于80分的記錄，背景色設置為紅色。c.成績為85分和95分的記錄，字體顏色設置為藍色。2.在Sheet2中，根據Sheet1的數據創建一個數據透視表，包含以下字段：a.學號b.姓名c.成績d.學科（假設學科信息在另一張工作表中）3.在Sheet3中，使用宏功能自動將Sheet1中的成績數據按照學號排序，并篩選出成績大于90分的學生。五、Excel數據分析與展示要求：運用Excel的數據分析工具，對數據進行深入分析，并通過圖表進行展示。1.在Sheet1中，對以下銷售數據進行分析：|產品|銷售額（萬元）|銷售量（件）||----|--------------|------------||A|100|200||B|150|300||C|200|400||D|250|500||E|300|600|a.計算每個產品的銷售額占比。b.計算每個產品的銷售量占比。c.制作一個復合餅圖，展示每個產品的銷售額占比和銷售量占比。2.在Sheet2中，根據Sheet1的數據創建一個數據透視表，包含以下字段：a.產品b.銷售額（萬元）c.銷售量（件）3.在Sheet3中，使用圖表功能制作以下圖表：a.柱狀圖：展示每個產品的銷售額。b.折線圖：展示每個產品的銷售量隨時間的變化趨勢。六、Excel數據共享與保護要求：掌握Excel的數據共享和保護方法，確保數據的安全性和可靠性。1.在Sheet1中，設置以下數據共享權限：a.允許所有用戶查看數據。b.允許特定用戶編輯數據。c.設置密碼保護工作簿，防止未授權用戶訪問。2.在Sheet2中，將Sheet1的數據復制一份，并對以下數據進行加密：a.學號b.姓名c.成績3.在Sheet3中，設置以下數據保護措施：a.對工作表中的公式進行隱藏。b.對工作表中的公式進行鎖定，防止用戶修改。c.對工作簿進行數字簽名，確保數據來源可靠。本次試卷答案如下：一、Excel基本操作與數據處理1.在Excel中創建一個工作簿，包含三個工作表：Sheet1、Sheet2、Sheet3。解析：打開Excel軟件，點擊“文件”菜單，選擇“新建”，在模板中選擇“空白工作簿”，創建三個工作表。2.在Sheet1中輸入以下數據：|姓名|性別|年齡|學號||----|----|----|----||張三|男|20|20250001||李四|女|22|20250002||王五|男|21|20250003||趙六|女|23|20250004||周七|男|20|20250005|解析：在Sheet1的A1單元格輸入“姓名”，在B1單元格輸入“性別”，在C1單元格輸入“年齡”，在D1單元格輸入“學號”。然后選中A2到D5單元格區域，輸入上述數據。3.在Sheet2中，將Sheet1中的數據復制一份。解析：在Sheet1中選中A1到D5單元格區域，右鍵點擊選擇“復制”，切換到Sheet2，右鍵點擊任意空白單元格選擇“粘貼”。4.對Sheet2中的數據進行以下操作：a.將性別列的“男”替換為“男同學”，“女”替換為“女同學”。解析：選中性別列（B2到B5），點擊“開始”選項卡下的“查找和選擇”按鈕，選擇“替換”，在“查找內容”框中輸入“男”，在“替換為”框中輸入“男同學”，點擊“全部替換”。重復此操作替換“女”為“女同學”。b.對年齡列進行升序排序。解析：選中年齡列（C2到C5），點擊“數據”選項卡下的“排序”，在“排序依據”中選擇“年齡”，點擊“確定”。c.在姓名列中篩選出“張”字開頭的記錄。解析：選中姓名列（A2到A5），點擊“開始”選項卡下的“篩選”，點擊姓名列的篩選箭頭，選擇“文本篩選”，在“自定義自動篩選”中輸入“張*”，點擊“確定”。d.對學號列應用數據驗證，允許輸入數字，輸入范圍為1-20250005。解析：選中學號列（D2到D5），點擊“數據”選項卡下的“數據驗證”，在“設置”選項卡中，選擇“整數”，設置“最小值”為1，“最大值”為20250005，點擊“確定”。e.計算年齡列的平均值、最大值、最小值。解析：在Sheet2的底部插入一個新行（A6到D6），在A6單元格輸入“年齡平均值”，在B6單元格輸入公式“=AVERAGE(C2:C5)”，在C6單元格輸入“年齡最大值”，在D6單元格輸入公式“=MAX(C2:C5)”，在E6單元格輸入“年齡最小值”，在F6單元格輸入公式“=MIN(C2:C5)”。f.在Sheet2的底部插入一個新行，包含以下數據：|姓名|性別|年齡|學號||----|----|----|----||王老師|男|45|20251001|解析：在Sheet2的底部插入一個新行（A7到D7），輸入上述數據。5.在Sheet3中，將Sheet1中的數據復制一份。解析：在Sheet1中選中A1到D5單元格區域，右鍵點擊選擇“復制”，切換到Sheet3，右鍵點擊任意空白單元格選擇“粘貼”。6.對Sheet3中的數據進行以下操作：a.將性別列的“男”替換為“男生”，“女”替換為“女生”。解析：選中性別列（B2到B5），點擊“開始”選項卡下的“查找和選擇”按鈕，選擇“替換”，在“查找內容”框中輸入“男”，在“替換為”框中輸入“男生”，點擊“全部替換”。重復此操作替換“女”為“女生”。b.在姓名列中篩選出“李”字開頭的記錄。解析：選中姓名列（A2到A5），點擊“開始”選項卡下的“篩選”，點擊姓名列的篩選箭頭，選擇“文本篩選”，在“自定義自動篩選”中輸入“李*”，點擊“確定”。c.對學號列應用數據驗證，允許輸入數字，輸入范圍為1-20250005。解析：選中學號列（D2到D5），點擊“數據”選項卡下的“數據驗證”，在“設置”選項卡中，選擇“整數”，設置“最小值”為1，“最大值”為20250005，點擊“確定”。d.計算年齡列的平均值、最大值、最小值。解析：在Sheet3的底部插入一個新行（A6到D6），在A6單元格輸入“年齡平均值”，在B6單元格輸入公式“=AVERAGE(C2:C5)”，在C6單元格輸入“年齡最大值”，在D6單元格輸入公式“=MAX(C2:C5)”，在E6單元格輸入“年齡最小值”，在F6單元格輸入公式“=MIN(C2:C5)”。二、Excel函數與公式應用1.在Sheet1中，計算以下數據：|學號|姓名|成績||----|----|----||20250001|張三|90||20250002|李四|85||20250003|王五|95||20250004|趙六|80||20250005|周七|75|解析：在Sheet1的G2單元格輸入“成績大于90的學生人數”，在G3單元格輸入公式“=COUNTIF(E2:E5,">90")”，在H2單元格輸入“成績低于80的學生人數”，在H3單元格輸入公式“=COUNTIF(E2:E5,"<80")”，在I2單元格輸入“成績最高的學生姓名”，在I3單元格輸入公式“=INDEX(F2:F5,MATCH(MAX(E2:E5),E2:E5,0))”，在J2單元格輸入“成績最低的學生姓名”，在J3單元格輸入公式“=INDEX(F2:F5,MATCH(MIN(E2:E5),E2:E5,0))”，在K2單元格輸入“成績平均分”，在K3單元格輸入公式“=AVERAGE(E2:E5)”，在L2單元格輸入“成績標準差”，在L3單元格輸入公式“=STDEVA(E2:E5)”。2.使用公式計算以下內容：a.成績大于90的學生人數。解析：使用COUNTIF函數計算成績大于90的記錄數。b.成績低于80的學生人數。解析：使用COUNTIF函數計算成績低于80的記錄數。c.成績最高的學生姓名。解析：使用INDEX和MATCH函數找到成績最高的記錄的姓名。d.成績最低的學生姓名。解析：使用INDEX和MATCH函數找到成績最低的記錄的姓名。e.成績平均分。解析：使用AVERAGE函數計算成績的平均值。f.成績標準差。解析：使用STDEVA函數計算成績的標準差。3.在Sheet2中，將Sheet1中的成績數據復制一份。解析：在Sheet1中選中E2到E5單元格區域，右鍵點擊選擇“復制”，切換到Sheet2，右鍵點擊任意空白單元格選擇“粘貼”。4.使用公式計算以下內容：a.成績大于90的學生人數。解析：使用COUNTIF函數計算成績大于90的記錄數。b.成績低于80的學生人數。解析：使用COUNTIF函數計算成績低于80的記錄數。c.成績最高的學生姓名。解析：使用INDEX和MATCH函數找到成績最高的記錄的姓名。d.成績最低的學生姓名。解析：使用INDEX和MATCH函數找到成績最低的記錄的姓名。e.成績平均分。解析：使用AVERAGE函數計算成績的平均值。f.成績標準差。解析：使用STDEVA函數計算成績的標準差。5.在Sheet3中，將Sheet1中的成績數據復制一份。解析：在Sheet1中選中E2到E5單元格區域，右鍵點擊選擇“復制”，切換到Sheet3，右鍵點擊任意空白單元格選擇“粘貼”。6.使用公式計算以下內容：a.成績大于90的學生人數。解析：使用COUNTIF函數計算成績大于90的記錄數。b.成績低于80的學生人數。解析：使用COUNTIF函數計算成績低于80的記錄數。c.成績最高的學生姓名。解析：使用INDEX和MATCH函數找到成績最高的記錄的姓名。d.成績最低的學生姓名。解析：使用INDEX和MATCH函數找到成績最低的記錄的姓名。e.成績平均分。解析：使用AVERAGE函數計算成績的平均值。f.成績標準差。解析：使用STDEVA函數計算成績的標準差。三、Excel圖表制作與分析1.在Sheet1中，將以下數據輸入到表格中：|學科|分數||----|----||數學|80||英語|90||物理|85||化學|95||生物|75|解析：在Sheet1的A1單元格輸入“學科”，在B1單元格輸入“分數”。然后分別輸入上述數據。2.在Sheet1中，根據以上數據制作以下圖表：a.折線圖：展示各學科分數隨時間的變化趨勢。解析：選中B2到B6單元格區域，點擊“插入”選項卡下的“折線圖”，選擇合適的折線圖樣式。b.餅圖：展示各學科分數在總分數中的占比。解析：選中B2到B6單元格區域，點擊“插入”選項卡下的“餅圖”，選擇合適的餅圖樣式。c.柱狀圖：展示各學科分數的對比情況。解析：選中B2到B6單元格區域，點擊“插入”選項卡下的“柱狀圖”，選擇合適的柱狀圖樣式。3.在Sheet2中，將Sheet1中的數據復制一份。解析：在Sheet1中選中A1到B6單元格區域，右鍵點擊選擇“復制”，切換到Sheet2，右鍵點擊任意空白單元格選擇“粘貼”。4.在Sheet2中，根據以上數據制作以下圖表：a.折線圖：展示各學科分數隨時間的變化趨勢。解析：選中B2到B6單元格區域，點擊“插入”選項卡下的“折線圖”，選擇合適的折線圖樣式。b.餅圖：展示各學科分數在總分數中的占比。解析：選中B2到B6單元格區域，點擊“插入”選項卡下的“餅圖”，選擇合適的餅圖樣式。c.柱狀圖：展示各學科分數的對比情況。解析：選中B2到B6單元格區域，點擊“插入”選項卡下的“柱狀圖”，選擇合適的柱狀圖樣式。5.在Sheet3中，將Sheet1中的數據復制一份。解析：在Sheet1中選中A1到B6單元格區域，右鍵點擊選擇“復制”，切換到Sheet3，右鍵點擊任意空白單元格選擇“粘貼”。6.在Sheet3中，根據以上數據制作以下圖表：a.折線圖：展示各學科分數隨時間的變化趨勢。解析：選中B2到B6單元格區域，點擊“插入”選項卡下的“折線圖”，選擇合適的折線圖樣式。b.餅圖：展示各學科分數在總分數中的占比。解析：選中B2到B6單元格區域，點擊“插入”選項卡下的“餅圖”，選擇合適的餅圖樣式。c.柱狀圖：展示各學科分數的對比情況。解析：選中B2到B6單元格區域，點擊“插入”選項卡下的“柱狀圖”，選擇合適的柱狀圖樣式。四、Excel高級功能應用1.在Sheet1中，對以下數據設置條件格式：|學號|姓名|成績||----|----|----||20250001|張三|90||20250002|李四|85||20250003|王五|95||20250004|趙六|80||20250005|周七|75|解析：選中成績列（C2到C5），點擊“開始”選項卡下的“條件格式”，選擇“新建規則”，選擇“使用公式確定要設置格式的單元格”，在“格式值等于以下公式時”框中輸入“>90”，點擊“格式”，選擇合適的背景色，點擊“確定”。重復此操作設置成績低于80分的條件格式。2.在Sheet2中，根據Sheet1的數據創建一個數據透視表，包含以下字段：a.學號b.姓名c.成績d.學科（假設學科信息在另一張工作表中）解析：在Sheet1中選中A1到D5單元格區域，點擊“插入”選項卡下的“數據透視表”，在“創建數據透視表”對話框中，選擇“新工作表”，點擊“確定”。在數據透視表字段列表中，將“學號”拖到“行”區域，將“姓名”拖到“列”區域，將“成績”拖到“值”區域，將“學科”拖到“值”區域。3.在Sheet3中，使用宏功能自動將Sheet1中的成績數據按照學號排序，并篩選出成績大于90分的學生。解析：在Sheet1中選中E2到E5單元格區域，點擊“開發工具”選項卡下的“宏”，選擇“錄制新宏”，在“宏名”框中輸入“自動排序篩選”，點擊“確定”。在錄制宏的過程中，依次點擊“數據”選項卡下的“排序”，選擇“按學號升序排序”，然后點擊“篩選”，選擇“學號”，在“數字篩選”中選擇“大于”，輸入“90”，點擊“確定”。完成錄制后，點擊“停止錄制”，在“宏”對話框中，點擊“關閉”。在Sheet1中，點擊“開發工具”選項卡下的“宏”，選擇“自動排序篩選”，即可執行宏操作。五、Excel數據分析與展示1.在Sheet1中，對以下銷售數據進行分析：|產品|銷售額（萬元）|銷售量（件）||----|--------------|------------||A|100|200||B|150|300||C|200|400||D|250|500||E|300|600|解析：在Sheet1的G2單元格輸入“每個產品的銷售額占比”，在G3單元格