2025年MSOffice高級應用計算機二級考試試卷：Excel數據透視表與圖表優化應用試題考試時間：______分鐘總分：______分姓名：______一、Excel數據透視表創建與應用要求：請根據以下數據源創建一個數據透視表，并完成相應的分析。數據源：某公司員工銷售數據表，包括員工編號、姓名、部門、銷售日期、銷售額等字段。1.在Excel中，打開數據源，選擇“插入”選項卡，點擊“數據透視表”按鈕，在彈出的對話框中選擇“創建數據透視表”。2.在“創建數據透視表”對話框中，選擇數據源所在的區域，點擊“確定”。3.在數據透視表字段列表中，將“部門”字段拖動到“行”區域，將“銷售額”字段拖動到“值”區域。4.計算部門銷售額的平均值，并將結果放置在“值”區域。5.對部門銷售額的平均值進行排序，將銷售額最高的部門放在頂部。6.添加一個計算字段，計算銷售額的同比增長率。7.在數據透視表上，添加一個篩選按鈕，用于篩選特定部門的數據。8.將數據透視表中的數據導出為CSV格式。二、Excel圖表創建與應用要求：請根據以下數據源創建一個合適的圖表，并完成相應的分析。數據源：某公司近一年的月銷售額數據，包括月份、銷售額等字段。1.在Excel中，打開數據源，選擇“插入”選項卡，點擊“圖表”按鈕，在彈出的圖表類型中選擇“折線圖”。2.在創建折線圖時，將“月份”字段拖動到“X軸”區域，將“銷售額”字段拖動到“Y軸”區域。3.對折線圖進行美化，包括添加標題、圖例、網格線等。4.計算銷售額的月增長率，并將增長率以數據標簽的形式顯示在折線圖上。5.將折線圖與柱狀圖組合，以便更直觀地展示銷售額的變化趨勢。6.對組合圖表進行美化，包括調整顏色、字體等。7.將組合圖表導出為PNG格式。三、Excel公式與函數應用要求：請根據以下數據源，使用Excel公式和函數完成相應的計算。數據源：某公司員工工資表，包括員工編號、姓名、基本工資、獎金、請假天數等字段。1.計算每位員工的總工資，包括基本工資和獎金。2.計算每位員工的請假扣款金額，假設每天扣款100元。3.計算每位員工的實發工資，即總工資減去請假扣款金額。4.對實發工資進行排序，將實發工資最高的員工放在頂部。5.計算實發工資的平均值。6.使用VLOOKUP函數查找特定員工的實發工資。7.使用SUMIF函數計算特定部門的總工資。8.使用COUNTIF函數計算請假天數大于等于5天的員工數量。四、Excel條件格式與高級篩選應用要求：請根據以下數據源，使用條件格式和高級篩選功能進行數據管理和分析。數據源：某公司員工考核數據表，包括員工編號、姓名、部門、考核得分等字段。1.對考核得分低于60分的員工，使用條件格式將該行的背景顏色設置為紅色。2.使用高級篩選功能，篩選出所有部門為“研發部”且考核得分在80分以上的員工。3.對篩選出的數據，使用條件格式將考核得分為90分及以上的員工的字體顏色設置為綠色。4.在數據透視表中對部門進行分組，并使用條件格式突出顯示部門平均考核得分高于80分的部門。5.使用高級篩選功能，篩選出所有請假天數超過5天的員工，并將結果導出為新的工作表。6.使用條件格式，將員工編號中包含“01”的員工姓名的字體加粗。五、Excel數據驗證與數據有效性設置要求：請根據以下數據源，使用數據驗證功能確保數據的準確性和有效性。數據源：某公司員工請假申請表，包括員工編號、姓名、部門、請假類型（事假、病假、年假）、請假開始日期、請假結束日期等字段。1.在“請假類型”字段中，使用數據驗證功能設置下拉列表，只允許選擇“事假”、“病假”、“年假”三種類型。2.在“請假開始日期”和“請假結束日期”字段中，使用數據驗證功能設置日期格式，確保只能輸入有效的日期。3.在“請假天數”字段中，使用數據驗證功能設置輸入范圍為1至30，確保請假天數在合理范圍內。4.使用數據驗證功能，對“請假類型”字段進行設置，只允許輸入預設的類型，并彈出提示信息，如果輸入錯誤則無法提交。5.在“請假開始日期”字段中，設置數據驗證規則，確保請假開始日期不能晚于當前日期。6.在“請假結束日期”字段中，設置數據驗證規則，確保請假結束日期不能早于請假開始日期。六、Excel數據排序與篩選功能應用要求：請根據以下數據源，使用Excel的數據排序和篩選功能對數據進行管理和分析。數據源：某公司銷售數據表，包括銷售日期、銷售額、客戶名稱、銷售人員等字段。1.使用排序功能，根據銷售額從高到低對數據進行排序。2.使用篩選功能，篩選出特定銷售人員的銷售記錄。3.使用排序功能，根據客戶名稱的字母順序對數據進行排序。4.使用篩選功能，篩選出特定時間段內的銷售記錄。5.使用排序功能，根據銷售日期從舊到新對數據進行排序。6.使用篩選功能，篩選出銷售額超過10000元的客戶名稱。本次試卷答案如下：一、Excel數據透視表創建與應用1.在Excel中，選擇“插入”選項卡，點擊“數據透視表”按鈕，在彈出的對話框中選擇“創建數據透視表”。2.在“創建數據透視表”對話框中，選擇數據源所在的區域，點擊“確定”。3.將“部門”字段拖動到“行”區域，將“銷售額”字段拖動到“值”區域。4.在“值”區域右側的下拉菜單中選擇“值字段設置”，選擇“平均值”，點擊“確定”。5.在“數據透視表”工具欄中，點擊“排序”按鈕，選擇“值”，然后選擇“降序”。6.在數據透視表字段列表中，添加一個計算字段，字段名稱為“同比增長率”，公式為：（當前年銷售額/去年銷售額）-1。7.在數據透視表上，右鍵點擊行標簽區域，選擇“插入篩選器”，然后選擇“部門”，在彈出的下拉列表中選擇特定部門。8.在“數據透視表”工具欄中，點擊“導出”按鈕，選擇“導出工作表”，然后選擇“CSV文件”格式。二、Excel圖表創建與應用1.在Excel中，打開數據源，選擇“插入”選項卡，點擊“圖表”按鈕，在彈出的圖表類型中選擇“折線圖”。2.將“月份”字段拖動到“X軸”區域，將“銷售額”字段拖動到“Y軸”區域。3.在圖表工具欄中，點擊“圖表樣式”按鈕，選擇合適的樣式進行美化。4.在圖表上，右鍵點擊數據點，選擇“添加數據標簽”，然后在數據標簽格式中設置顯示增長率。5.在數據源旁邊插入一個柱狀圖，將“月份”字段拖動到“X軸”區域，將“銷售額”字段拖動到“Y軸”區域。6.在組合圖表上，右鍵點擊圖表，選擇“組合圖表”選項，然后在彈出的對話框中選擇“折線圖和柱狀圖”。7.對組合圖表進行美化，包括調整顏色、字體等。8.在“另存為”對話框中，選擇“PNG文件”格式，點擊“保存”。三、Excel公式與函數應用1.使用公式：=SUM(基本工資,獎金)。2.使用公式：=基本工資-(請假天數*100)。3.使用公式：=SUM(實發工資)。4.使用排序功能，選擇“實發工資”列，然后選擇“降序”。5.使用公式：=AVERAGE(實發工資)。6.使用VLOOKUP函數，公式為：=VLOOKUP(員工編號,員工工資表,2,FALSE)。7.使用SUMIF函數，公式為：=SUMIF(部門,"研發部",實發工資)。8.使用COUNTIF函數，公式為：=COUNTIF(請假天數,">5")。四、Excel條件格式與高級篩選應用1.在“考核得分”列中，選擇“條件格式”按鈕，選擇“新建規則”，選擇“使用公式確定要設置的格式”，輸入公式：=考核得分<60，點擊“格式”按鈕，設置背景顏色為紅色，點擊“確定”，然后點擊“確定”。2.在數據透視表上，右鍵點擊“部門”行標簽，選擇“插入篩選器”，在彈出的下拉列表中選擇“研發部”，然后選擇“確定”。3.在數據透視表上，選擇“考核得分”列，點擊“條件格式”按鈕，選擇“新建規則”，選擇“使用公式確定要設置的格式”，輸入公式：=考核得分>=90，點擊“格式”按鈕，設置字體顏色為綠色，點擊“確定”，然后點擊“確定”。4.在數據透視表上，右鍵點擊“部門”行標簽，選擇“插入篩選器”，在彈出的下拉列表中選擇“部門”，然后選擇特定部門。5.在數據透視表上，點擊“數據透視表”工具欄中的“導出”按鈕，選擇“導出工作表”，然后選擇“CSV文件”格式。6.在員工編號列中，選擇“條件格式”按鈕，選擇“新建規則”，選擇“使用公式確定要設置的格式”，輸入公式：=LEFT(員工編號,2)="01"，點擊“格式”按鈕，設置字體加粗，點擊“確定”，然后點擊“確定”。五、Excel數據驗證與數據有效性設置1.在“請假類型”列中，選擇“數據驗證”按鈕，選擇“設置”，在“允許”下拉列表中選擇“序列”，在“來源”框中輸入“事假,病假,年假”，點擊“確定”。2.在“請假開始日期”和“請假結束日期”列中，選擇“數據驗證”按鈕，選擇“設置”，在“允許”下拉列表中選擇“日期”，在“數據”下拉列表中選擇“介于”，在“開始日期”框中輸入當前日期，在“結束日期”框中輸入當前日期后的日期，點擊“確定”。3.在“請假天數”列中，選擇“數據驗證”按鈕，選擇“設置”，在“允許”下拉列表中選擇“整數”，在“數據”下拉列表中選擇“介于”，在“最小值”框中輸入1，在“最大值”框中輸入30，點擊“確定”。4.在“請假類型”列中，選擇“數據驗證”按鈕，選擇“設置”，在“輸入信息”選項卡中輸入標題和提示信息，點擊“確定”，在“出錯警告”選項卡中輸入標題和錯誤信息，點擊“確定”。5.在“請假開始日期”列中，選擇“數據驗證”按鈕，選擇“設置”，在“數據”下拉列表中選擇“介于”，在“開始日期”框中輸入當前日期，點擊“確定”。6.在“請假結束日期”列中，選擇“數據驗證”按鈕，選擇“設置”，在“數據”下拉列表中選擇“介于”，在“開始日期”框中輸入與“請假開始日期”相同的日期，點擊“確定”。六、Excel數據排序與篩選功能應用1.在“銷售額”列中，選擇“排序”按鈕，選擇“降序”