2025年計算機二級MSOffice高級應用考試真題卷（Excel高級數據分析案例）考試時間：______分鐘總分：______分姓名：______一、Excel基本操作與應用要求：熟練掌握Excel的基本操作，包括單元格的編輯、格式設置、公式的應用等。1.在以下單元格中輸入公式，計算每個學生的總分：-A1:姓名-B1:語文成績-C1:數學成績-D1:英語成績-E1:總分-數據如下：-A2:張三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:932.根據以下條件，對數據進行篩選：-語文成績高于85分-數學成績低于90分3.對以下數據進行排序：-按總分從高到低排序4.在以下單元格中輸入公式，計算每個學生的平均分：-A5:平均分-數據如下：-A2:張三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:935.在以下單元格中輸入公式，計算每個學生的及格率：-A6:及格率-數據如下：-A2:張三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:936.在以下單元格中輸入公式，計算每個學生的優秀率：-A7:優秀率-數據如下：-A2:張三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:937.在以下單元格中輸入公式，計算每個學生的不及格率：-A8:不及格率-數據如下：-A2:張三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:938.在以下單元格中輸入公式，計算每個學生的優秀人數：-A9:優秀人數-數據如下：-A2:張三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:939.在以下單元格中輸入公式，計算每個學生的不及格人數：-A10:不及格人數-數據如下：-A2:張三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:9310.在以下單元格中輸入公式，計算每個學生的及格人數：-A11:及格人數-數據如下：-A2:張三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:93二、Excel圖表制作與應用要求：熟練掌握Excel圖表制作，包括柱狀圖、折線圖、餅圖等。1.根據以下數據，制作一個柱狀圖，展示每個學生的總分：-A1:姓名-B1:總分-數據如下：-A2:張三-B2:85-A3:李四-B3:78-A4:王五-B4:922.根據以下數據，制作一個折線圖，展示每個學生的成績趨勢：-A1:姓名-B1:語文成績-C1:數學成績-D1:英語成績-數據如下：-A2:張三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:933.根據以下數據，制作一個餅圖，展示每個學生的成績占比：-A1:姓名-B1:語文成績-C1:數學成績-D1:英語成績-數據如下：-A2:張三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:934.根據以下數據，制作一個組合圖表，展示每個學生的語文、數學、英語成績：-A1:姓名-B1:語文成績-C1:數學成績-D1:英語成績-數據如下：-A2:張三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:935.根據以下數據，制作一個散點圖，展示每個學生的總分與平均分的關系：-A1:姓名-B1:總分-C1:平均分-數據如下：-A2:張三-B2:85-C2:88-A3:李四-B3:78-C3:82-A4:王五-B4:92-C4:936.根據以下數據，制作一個條形圖，展示每個學生的及格人數：-A1:姓名-B1:及格人數-數據如下：-A2:張三-B2:2-A3:李四-B3:1-A4:王五-B4:37.根據以下數據，制作一個環形圖，展示每個學生的優秀率：-A1:姓名-B1:優秀率-數據如下：-A2:張三-B2:0.2-A3:李四-B3:0.1-A4:王五-B4:0.38.根據以下數據，制作一個雷達圖，展示每個學生的語文、數學、英語成績：-A1:姓名-B1:語文成績-C1:數學成績-D1:英語成績-數據如下：-A2:張三-B2:85-C2:90-D2:88-A3:李四-B3:78-C3:85-D3:82-A4:王五-B4:92-C4:88-D4:939.根據以下數據，制作一個氣泡圖，展示每個學生的總分與平均分的關系：-A1:姓名-B1:總分-C1:平均分-數據如下：-A2:張三-B2:85-C2:88-A3:李四-B3:78-C3:82-A4:王五-B4:92-C4:9310.根據以下數據，制作一個面積圖，展示每個學生的及格人數：-A1:姓名-B1:及格人數-數據如下：-A2:張三-B2:2-A3:李四-B3:1-A4:王五-B4:3四、Excel數據透視表與數據分析要求：熟練掌握Excel數據透視表的應用，能夠進行數據的分類匯總、分析等。1.建立一個數據透視表，展示每個學生的總分、平均分、及格人數。-數據源如下：-A1:姓名-B1:總分-C1:平均分-D1:及格人數-數據如下：-A2:張三-B2:85-C2:88-D2:2-A3:李四-B3:78-C3:82-D3:1-A4:王五-B4:92-C4:93-D4:32.在數據透視表中，對總分進行排序，從高到低。3.在數據透視表中，計算每個科目的平均分。4.在數據透視表中，篩選出總分大于90分的學生。5.在數據透視表中，計算每個學生的及格率。6.在數據透視表中，計算每個學生的優秀率。五、Excel高級函數應用要求：熟練掌握Excel的高級函數，如VLOOKUP、HLOOKUP、SUMIF、COUNTIF等。1.使用VLOOKUP函數，從以下數據中查找張三的總分：-A1:姓名-B1:總分-數據如下：-A2:張三-B2:85-A3:李四-B3:78-A4:王五-B4:922.使用HLOOKUP函數，從以下數據中查找語文成績大于85分的學生姓名：-A1:姓名-B1:語文成績-數據如下：-A2:張三-B2:85-A3:李四-B3:78-A4:王五-B4:923.使用SUMIF函數，計算語文成績大于85分的學生總分。-數據如下：-A2:張三-B2:85-A3:李四-B3:78-A4:王五-B4:924.使用COUNTIF函數，計算數學成績大于90分的學生人數。-數據如下：-A2:張三-B2:85-A3:李四-B3:78-A4:王五-B4:925.使用VLOOKUP和HLOOKUP函數組合，查找姓名為李四的數學成績。-數據如下：-A1:姓名-B1:數學成績-數據如下：-A2:張三-B2:85-A3:李四-B3:90-A4:王五-B4:926.使用SUMIF和COUNTIF函數組合，計算數學成績大于90分的學生總分和人數。六、Excel數據驗證與條件格式要求：熟練掌握Excel的數據驗證和條件格式功能，能夠對數據進行驗證和格式化。1.在以下單元格中設置數據驗證，只允許輸入1到10之間的整數：-數據如下：-A2:5-A3:15-A4:82.在以下單元格中設置條件格式，當數值大于90時，字體顏色為紅色：-數據如下：-A2:85-A3:92-A4:783.在以下單元格中設置數據驗證，只允許輸入郵箱地址格式：-數據如下：-A2:zhangsan@-A3:lisi@163.com-A4:wangwu@4.在以下單元格中設置條件格式，當數值為空時，背景色為黃色：-數據如下：-A2:-A3:85-A4:925.在以下單元格中設置數據驗證，只允許輸入日期格式：-數據如下：-A2:2025-01-01-A3:2025-01-15-A4:2025-01-206.在以下單元格中設置條件格式，當數值小于60時，字體顏色為綠色：-數據如下：-A2:58-A3:65-A4:72本次試卷答案如下：一、Excel基本操作與應用1.解析：在E2單元格中輸入公式`=SUM(B2:D2)`，將B2、C2、D2單元格的數值相加，得到張三的總分。將此公式向下拖動應用到其他學生。2.解析：在Excel的“數據”選項卡中，點擊“篩選”按鈕，然后分別對語文成績和數學成績進行篩選，篩選條件分別為“大于85”和“小于90”。3.解析：在Excel的“數據”選項卡中，點擊“排序”按鈕，選擇“主要關鍵字”為“總分”，排序方式為“降序”。4.解析：在E5單元格中輸入公式`=AVERAGE(B2:D2)`，將B2、C2、D2單元格的數值求平均值，得到張三的平均分。將此公式向下拖動應用到其他學生。5.解析：在E6單元格中輸入公式`=COUNTIF(B2:D2,">=60")/COUNT(B2:D2)`，計算及格人數（分數大于等于60的人數）除以總人數，得到張三的及格率。將此公式向下拖動應用到其他學生。6.解析：在E7單元格中輸入公式`=COUNTIF(B2:D2,">=85")/COUNT(B2:D2)`，計算優秀人數（分數大于等于85的人數）除以總人數，得到張三的優秀率。將此公式向下拖動應用到其他學生。7.解析：在E8單元格中輸入公式`=COUNTIF(B2:D2,"<60")/COUNT(B2:D2)`，計算不及格人數（分數小于60的人數）除以總人數，得到張三的不及格率。將此公式向下拖動應用到其他學生。8.解析：在E9單元格中輸入公式`=COUNTIF(B2:D2,">=85")/COUNT(B2:D2)`，計算優秀人數（分數大于等于85的人數）除以總人數，得到張三的優秀人數。將此公式向下拖動應用到其他學生。9.解析：在E10單元格中輸入公式`=COUNTIF(B2:D2,"<60")/COUNT(B2:D2)`，計算不及格人數（分數小于60的人數）除以總人數，得到張三的不及格人數。將此公式向下拖動應用到其他學生。10.解析：在E11單元格中輸入公式`=COUNTIF(B2:D2,">=60")/COUNT(B2:D2)`，計算及格人數（分數大于等于60的人數）除以總人數，得到張三的及格人數。將此公式向下拖動應用到其他學生。二、Excel圖表制作與應用1.解析：在B2:B5單元格中輸入學生的姓名，在C2:C5單元格中輸入對應學生的總分，然后在C2單元格中插入柱狀圖，選擇C2:C5單元格作為數據源。2.解析：在B2:B5單元格中輸入學生的姓名，在C2:D5單元格中分別輸入語文、數學、英語成績，然后在C2:D5單元格中插入折線圖，選擇C2:D5單元格作為數據源。3.解析：在B2:B5單元格中輸入學生的姓名，在C2:D5單元格中分別輸入語文、數學、英語成績，然后在C2:D5單元格中插入餅圖，選擇C2:D5單元格作為數據源。4.解析：在B2:B5單元格中輸入學生的姓名，在C2:D5單元格中分別輸入語文、數學、英語成績，然后在C2:D5單元格中插入組合圖表，選擇C2:D5單元格作為數據源，并設置圖表類型為“簇狀柱形圖”和“折線圖”。5.解析：在B2:B5單元格中輸入學生的姓名，在C2:D5單元格中分別輸入總分和平均分，然后在C2:D5單元格中插入散點圖，選擇C2:D5單元格作為數據源。6.解析：在B2:B5單元格中輸入學生的姓名，在C2:C5單元格中輸入及格人數，然后在C2:C5單元格中插入條形圖，選擇C2:C5單元格作為數據源。7.解析：在B2:B5單元格中輸入學生的姓名，在C2:C5單元格中輸入優秀率，然后在C2:C5單元格中插入環形圖，選擇C2:C5單元格作為數據源。8.解析：在B2:B5單元格中輸入學生的姓名，在C2:D5單元格中分別輸入語文、數學、英語成績，然后在C2:D5單元格中插入雷達圖，選擇C2:D5單元格作為數據源。9.解析：在B2:B5單元格中輸入學生的姓名，在C2:D5單元格中分別輸入總分和平均分，然后在C2:D5單元格中插入氣泡圖，選擇C2:D5單元格作為數據源。10.解析：在B2:B5單元格中輸入學生的姓名，在C2:C5單元格中輸入及格人數，然后在C2:C5單元格中插入面積圖，選擇C2:C5單元格作為數據源。三、Excel數據透視表與數據分析1.解析：選中數據源，在“插入”選項卡中選擇“數據透視表”，在彈出的對話框中指定數據透視表放置的位置，然后按照提示設置數據透視表字段，包括姓名、總分、平均分、及格人數。2.解析：在數據透視表中，選擇“總分”字段，然后點擊“排序”按鈕，選擇“降序”。3.解析：在數據透視表中，選擇“總分”字段，然后點擊“值字段設置”按鈕，選擇“平均值”作為匯總方式。4.解析：在數據透視表中，選擇“總分”字段，然后點擊“篩選”按鈕，選擇“大于90”。5.解析：在數據透視表中，選擇“及格人數”字段，然后點擊“值字段設置”按鈕，選擇“計數”作為匯總方式。四、Excel高級函數應用1.解析：在新的單元格中輸入公式`=VLOOKUP("張三",A:B,2,FALSE)`，其中A:B是包含姓名和總分的列，2表示返回第二個字段（即總分），FALSE表示進行精確匹配。2.解析：在新的單元格中輸入公式`=HLOOKUP(85,A:B,2,FALSE)`，其中A:B是包含姓名和語文成績的列，2表示返回第二個字段（即語文成績），FALSE表示進行精確匹配。3.解析：在新的單元格中輸入公式`=SUMIF(B:B,">85",C:C)`，其中B:B是包含語文成績的列