2025年計算機二級MSOffice高級應用考試真題卷：Excel數據驗證與數據透視表應用試題考試時間：______分鐘總分：______分姓名：______一、Excel數據驗證與數據驗證規則的應用要求：運用數據驗證功能，設置合適的規則，對數據進行驗證，確保數據的準確性和一致性。1.在Excel中創建一個名為“學生信息”的工作簿，包含以下列：學號、姓名、性別、年齡、班級。2.在“學號”列中輸入以下數據：1001、1002、1003、1004、1005。3.使用數據驗證功能，設置學號的輸入規則為：只能輸入數字，并且數字范圍在1000到2000之間。4.在“姓名”列中輸入以下數據：張三、李四、王五、趙六、錢七。5.使用數據驗證功能，設置姓名的輸入規則為：只能輸入漢字，并且長度在2到4個字符之間。6.在“性別”列中輸入以下數據：男、女、男、女、男。7.使用數據驗證功能，設置性別的輸入規則為：只能輸入“男”或“女”。8.在“年齡”列中輸入以下數據：20、21、22、23、24。9.使用數據驗證功能，設置年齡的輸入規則為：只能輸入數字，并且數字范圍在18到30之間。10.在“班級”列中輸入以下數據：1班、2班、3班、4班、5班。11.使用數據驗證功能，設置班級的輸入規則為：只能輸入“1班”、“2班”、“3班”、“4班”或“5班”。二、Excel數據透視表的基本操作要求：運用數據透視表功能，對數據進行匯總和分析。1.在“學生信息”工作簿中，選中所有數據區域（包含標題行）。2.在“插入”選項卡中，點擊“數據透視表”按鈕。3.在彈出的“創建數據透視表”對話框中，將“數據透視表放置在”選項設置為“新工作表”。4.在“數據透視表字段列表”中，將“學號”字段拖放到“行”區域。5.將“姓名”字段拖放到“列”區域。6.將“年齡”字段拖放到“值”區域，并選擇“求和”作為計算方式。7.將“班級”字段拖放到“篩選”區域，以便篩選特定班級的數據。8.在數據透視表中，修改“求和”字段的顯示格式為：整數格式。9.在數據透視表中，添加一個計算字段，字段名為“平均年齡”，計算方式為：（年齡總和）/（人數）。10.在數據透視表中，設置數據透視表的標題為“學生信息統計”。四、Excel數據透視表的高級應用要求：運用數據透視表的高級功能，進行更復雜的數據分析和匯總。1.在數據透視表中，添加一個計算字段，字段名為“年齡區間”，計算方式為：如果年齡大于20且小于30，則返回“20-30歲”，否則返回“其他”。2.在數據透視表中，添加一個計算項，字段名為“班級人數”，計算方式為：使用COUNTA函數統計每個班級的學生人數。3.在數據透視表中，設置“班級人數”字段的顯示格式為：文本格式，顯示為千位分隔符。4.在數據透視表中，對“年齡區間”字段進行排序，按照“20-30歲”優先級最高，其次是“其他”。5.在數據透視表中，使用切片器功能，允許用戶根據“性別”和“年齡區間”篩選數據。6.在數據透視表中，設置一個數據透視圖，展示每個班級的學生人數。7.在數據透視圖上，添加一個數據標簽，顯示每個班級的具體人數。8.在數據透視表中，隱藏不包含任何數據的行和列。9.在數據透視表中，使用“值顯示方式”功能，將“年齡”字段的值顯示為百分比。10.在數據透視表中，使用“分組”功能，將“年齡”字段按照10歲為一個區間進行分組。五、Excel數據驗證的高級規則應用要求：使用數據驗證的高級規則，創建更復雜的數據驗證規則。1.在“學生信息”工作簿中，針對“學號”列，設置一個數據驗證規則，要求輸入的學號必須符合特定格式，例如：“20XX12345”，其中“20XX”代表年份，“12345”代表學號。2.在“姓名”列中，設置一個數據驗證規則，要求輸入的姓名必須包含至少一個漢字，并且不能包含數字或特殊字符。3.在“性別”列中，使用數據驗證功能，創建一個下拉列表，包含“男”、“女”和“未知”三個選項。4.在“年齡”列中，設置一個數據驗證規則，要求輸入的年齡必須是一個介于18歲到60歲之間的整數。5.在“班級”列中，使用數據驗證功能，創建一個下拉列表，包含所有可能的班級選項，例如：“1班”、“2班”、“3班”等。6.在“入學日期”列中，設置一個數據驗證規則，要求輸入的日期必須晚于當前日期。7.在“聯系電話”列中，設置一個數據驗證規則，要求輸入的聯系電話必須符合中國的手機號碼格式。8.在“郵箱地址”列中，設置一個數據驗證規則，要求輸入的郵箱地址格式正確。9.在“身高”列中，設置一個數據驗證規則，要求輸入的身高必須是一個介于0到300厘米之間的數值。10.在“體重”列中，設置一個數據驗證規則，要求輸入的體重必須是一個介于0到200公斤之間的數值。六、Excel數據驗證與數據透視表的結合應用要求：將數據驗證與數據透視表的功能結合，實現數據的有效管理和分析。1.在“學生信息”工作簿中，使用數據驗證功能，對“學號”列進行驗證，確保學號的唯一性。2.在“學生信息”工作簿中，使用數據驗證功能，對“姓名”列進行驗證，確保姓名的準確性。3.在“學生信息”工作簿中，使用數據驗證功能，對“性別”列進行驗證，確保性別的正確性。4.在“學生信息”工作簿中，使用數據驗證功能，對“年齡”列進行驗證，確保年齡的合理性。5.在“學生信息”工作簿中，使用數據驗證功能，對“班級”列進行驗證，確保班級信息的完整性。6.在“學生信息”工作簿中，使用數據驗證功能，對“入學日期”列進行驗證，確保日期的正確性。7.在“學生信息”工作簿中，使用數據驗證功能，對“聯系電話”列進行驗證，確保電話號碼的有效性。8.在“學生信息”工作簿中，使用數據驗證功能，對“郵箱地址”列進行驗證，確保郵箱地址的正確性。9.在“學生信息”工作簿中，使用數據驗證功能，對“身高”和“體重”列進行驗證，確保體型的合理性。10.在“學生信息”工作簿中，使用數據驗證和數據透視表功能，創建一個分析報告，展示不同班級、性別、年齡區間的學生信息統計。本次試卷答案如下：一、Excel數據驗證與數據驗證規則的應用1.在“學生信息”工作簿中創建工作表“學生信息”，輸入學號列數據：1001、1002、1003、1004、1005。解析思路：首先，創建一個新的工作表“學生信息”，然后在A列輸入學號數據。2.使用數據驗證功能，設置學號的輸入規則為：只能輸入數字，并且數字范圍在1000到2000之間。解析思路：選中A列，點擊“數據”選項卡下的“數據驗證”按鈕，在彈出的對話框中設置允許為“整數”，最小值為1000，最大值為2000。3.在“姓名”列中輸入以下數據：張三、李四、王五、趙六、錢七。解析思路：在B列輸入姓名數據，確保每個姓名都是漢字，長度在2到4個字符之間。4.使用數據驗證功能，設置姓名的輸入規則為：只能輸入漢字，并且長度在2到4個字符之間。解析思路：選中B列，點擊“數據”選項卡下的“數據驗證”按鈕，在彈出的對話框中設置允許為“文本”，設置條件為“自定義”，輸入公式“=AND(ISNUMBER(VALUE(LEFT(A1,1))),LEN(A1)>=2,LEN(A1)<=4)”。5.在“性別”列中輸入以下數據：男、女、男、女、男。解析思路：在C列輸入性別數據，確保每個性別都是“男”或“女”。6.使用數據驗證功能，設置性別的輸入規則為：只能輸入“男”或“女”。解析思路：選中C列，點擊“數據”選項卡下的“數據驗證”按鈕，在彈出的對話框中設置允許為“序列”，在來源框中輸入“男,女”。7.在“年齡”列中輸入以下數據：20、21、22、23、24。解析思路：在D列輸入年齡數據，確保每個年齡都是整數。8.使用數據驗證功能，設置年齡的輸入規則為：只能輸入數字，并且數字范圍在18到30之間。解析思路：選中D列，點擊“數據”選項卡下的“數據驗證”按鈕，在彈出的對話框中設置允許為“整數”，最小值為18，最大值為30。9.在“班級”列中輸入以下數據：1班、2班、3班、4班、5班。解析思路：在E列輸入班級數據，確保每個班級都是“1班”、“2班”、“3班”、“4班”或“5班”。10.使用數據驗證功能，設置班級的輸入規則為：只能輸入“1班”、“2班”、“3班”、“4班”或“5班”。解析思路：選中E列，點擊“數據”選項卡下的“數據驗證”按鈕，在彈出的對話框中設置允許為“序列”，在來源框中輸入“1班,2班,3班,4班,5班”。二、Excel數據透視表的基本操作1.在“學生信息”工作簿中，選中所有數據區域（包含標題行）。解析思路：在“學生信息”工作表中選擇包含標題行的數據區域，包括學號、姓名、性別、年齡、班級等列。2.在“插入”選項卡中，點擊“數據透視表”按鈕。解析思路：在“插入”選項卡中找到“數據透視表”按鈕，點擊打開創建數據透視表的對話框。3.在彈出的“創建數據透視表”對話框中，將“數據透視表放置在”選項設置為“新工作表”。解析思路：在創建數據透視表對話框中，選擇“新工作表”選項，讓數據透視表放在一個新的工作表中。4.在“數據透視表字段列表”中，將“學號”字段拖放到“行”區域。解析思路：將“學號”字段從字段列表拖放到數據透視表中的“行”區域。5.將“姓名”字段拖放到“列”區域。解析思路：將“姓名”字段從字段列表拖放到數據透視表中的“列”區域。6.將“年齡”字段拖放到“值”區域，并選擇“求和”作為計算方式。解析思路：將“年齡”字段從字段列表拖放到數據透視表中的“值”區域，并在彈出的對話框中選擇“求和”作為計算方式。7.將“班級”字段拖放到“篩選”區域，以便篩選特定班級的數據。解析思路：將“班級”字段從字段列表拖放到數據透視表中的“篩選”區域，以便用戶可以篩選特定的班級數據。8.在數據透視表中，修改“求和”字段的顯示格式為：整數格式。解析思路：選中“求和”字段，右鍵點擊選擇“格式化單元格”，在彈出的對話框中設置數字格式為“整數”。9.在數據透視表中，添加一個計算字段，字段名為“平均年齡”，計算方式為：（年齡總和）/（人數）。解析思路：在數據透視表的“值”區域，點擊“值字段設置”，在彈出的對話框中創建一個新的計算字段，命名為“平均年齡”，并輸入公式“=AVERAGE(年齡)”。10.在數據透視表中，設置數據透視表的標題為“學生信息統計”。解析思路：在數據透視表上方的標題欄中，直接輸入“學生信息統計”作為標題。三、Excel數據透視表的高級應用1.在數據透視表中，添加一個計算字段，字段名為“年齡區間”，計算方式為：如果年齡大于20且小于30，則返回“20-30歲”，否則返回“其他”。解析思路：在數據透視表的“值”區域，點擊“值字段設置”，在彈出的對話框中創建一個新的計算字段，命名為“年齡區間”，并輸入公式“=IF(AND(年齡>20,年齡<30),"20-30歲","其他")”。2.在數據透視表中，添加一個計算項，字段名為“班級人數”，計算方式為：使用COUNTA函數統計每個班級的學生人數。解析思路：在數據透視表的“值”區域，點擊“值字段設置”，在彈出的對話框中創建一個新的計算字段，命名為“班級人數”，并輸入公式“=COUNTA(班級)”。3.在數據透視表中，設置“班級人數”字段的顯示格式為：文本格式，顯示為千位分隔符。解析思路：選中“班級人數”字段，右鍵點擊選擇“格式化單元格”，在彈出的對話框中設置數字格式為“文本”，然后在“數字”選項卡中選擇“千位分隔符”。4.在數據透視表中，對“年齡區間”字段進行排序，按照“20-30歲”優先級最高，其次是“其他”。解析思路：選中“年齡區間”字段，點擊“排序與篩選”組中的“排序”，在彈出的對話框中選擇“升序”排序，然后在“排序依據”中選擇“年齡區間”。5.在數據透視表中，使用切片器功能，允許用戶根據“性別”和“年齡區間”篩選數據。解析思路：在數據透視表上方的工具欄中，點擊“插入”選項卡下的“切片器”，然后分別將“性別”和“年齡區間”字段拖放到切片器中。6.在數據透視表中，設置一個數據透視圖，展示每個班級的學生人數。解析思路：在數據透視表上方的工具欄中，點擊“插入”選項卡下的“數據透視圖”，選擇柱形圖，然后將“班級”字段拖放到“列”區域，將“班級人數”字段拖放到“值”區域。7.在數據透視圖上，添加一個數據標簽，顯示每個班級的具體人數。解析思路：選中數據透視圖，點擊“圖表工具”下的“設計”選項卡，在“圖表布局”組中點擊