2025年計(jì)算機(jī)二級MSOffice高級應(yīng)用考試真題卷：Excel數(shù)據(jù)透視表與高級篩選綜合試題考試時(shí)間：______分鐘總分：______分姓名：______一、Excel數(shù)據(jù)透視表創(chuàng)建與應(yīng)用要求：請根據(jù)以下數(shù)據(jù)，創(chuàng)建一個(gè)數(shù)據(jù)透視表，并完成相應(yīng)的數(shù)據(jù)分析和展示。數(shù)據(jù)來源：某公司員工銷售數(shù)據(jù)表（員工姓名、部門、銷售日期、銷售額、提成比例）1.創(chuàng)建數(shù)據(jù)透視表，將“員工姓名”作為行標(biāo)簽，“部門”作為列標(biāo)簽，“銷售額”作為數(shù)值字段，計(jì)算“銷售額”的平均值。2.在數(shù)據(jù)透視表中添加一個(gè)計(jì)算字段，計(jì)算“提成比例”的平均值。3.對“銷售額”的平均值進(jìn)行排序，從高到低。4.在數(shù)據(jù)透視表中添加一個(gè)篩選條件，只顯示“部門”為“銷售部”的員工數(shù)據(jù)。5.將數(shù)據(jù)透視表中的數(shù)據(jù)以圖表形式展示，選擇合適的圖表類型。6.對數(shù)據(jù)透視表進(jìn)行格式化，包括字體、字號、顏色等。7.將數(shù)據(jù)透視表保存為一個(gè)新的工作表。二、Excel高級篩選與條件格式要求：請根據(jù)以下數(shù)據(jù)，使用高級篩選和條件格式功能完成以下任務(wù)。數(shù)據(jù)來源：某公司員工信息表（員工姓名、性別、年齡、學(xué)歷、部門）1.使用高級篩選功能，篩選出年齡大于30歲的男性員工。2.使用高級篩選功能，篩選出學(xué)歷為“本科”且部門為“研發(fā)部”的員工。3.對篩選出的數(shù)據(jù)進(jìn)行排序，首先按“年齡”升序排序，然后按“學(xué)歷”降序排序。4.使用條件格式功能，將年齡大于40歲的員工姓名設(shè)置為紅色字體。5.使用條件格式功能，將學(xué)歷為“碩士”的員工姓名設(shè)置為加粗字體。6.將篩選后的數(shù)據(jù)保存為一個(gè)新的工作表。7.將篩選后的數(shù)據(jù)以圖表形式展示，選擇合適的圖表類型。三、Excel公式與函數(shù)應(yīng)用要求：請根據(jù)以下數(shù)據(jù)，使用Excel公式和函數(shù)完成以下任務(wù)。數(shù)據(jù)來源：某公司員工銷售數(shù)據(jù)表（員工姓名、銷售日期、銷售額）1.使用公式計(jì)算每位員工的銷售總額。2.使用公式計(jì)算每位員工的銷售日期對應(yīng)的星期幾。3.使用函數(shù)計(jì)算銷售日期為“2025-01-01”之前的銷售總額。4.使用函數(shù)計(jì)算銷售日期為“2025-01-01”之后的銷售總額。5.使用公式計(jì)算每位員工的銷售額占公司總銷售額的比例。6.使用函數(shù)計(jì)算銷售額最高的員工的姓名。7.將計(jì)算結(jié)果保存為一個(gè)新的工作表。四、Excel宏與VBA應(yīng)用要求：請根據(jù)以下數(shù)據(jù)，使用Excel宏錄制功能錄制一個(gè)宏，并使用VBA編寫代碼實(shí)現(xiàn)以下功能。數(shù)據(jù)來源：某公司員工銷售數(shù)據(jù)表（員工姓名、銷售日期、銷售額）1.錄制一個(gè)宏，將當(dāng)前工作表中的“銷售額”列增加5%。2.使用VBA代碼實(shí)現(xiàn)以下功能：a.檢查工作表中的“銷售額”列是否所有數(shù)值都大于0，如果是，則在B2單元格顯示“所有銷售額均大于0”，否則顯示“存在銷售額為0或負(fù)數(shù)的記錄”。b.將所有“銷售額”大于等于5000的記錄，將其“銷售日期”列中的日期格式更改為“月/日/年”。c.在工作表的末尾添加一個(gè)新的列，列名為“提成”，計(jì)算每位員工的提成金額（提成比例為10%）。五、Excel數(shù)據(jù)分析與圖表創(chuàng)建要求：請根據(jù)以下數(shù)據(jù)，進(jìn)行數(shù)據(jù)分析并創(chuàng)建相應(yīng)的圖表。數(shù)據(jù)來源：某公司員工銷售數(shù)據(jù)表（員工姓名、銷售日期、銷售額）1.對“銷售額”進(jìn)行分組，按照銷售額的區(qū)間（0-1000，1001-5000，5001-10000，10001以上）統(tǒng)計(jì)每個(gè)區(qū)間的員工數(shù)量。2.創(chuàng)建一個(gè)餅圖，展示每個(gè)銷售額區(qū)間的員工數(shù)量占比。3.創(chuàng)建一個(gè)柱狀圖，展示每個(gè)銷售額區(qū)間內(nèi)銷售額的平均值。4.分析并比較不同部門員工的銷售額差異，創(chuàng)建一個(gè)散點(diǎn)圖，展示員工姓名和銷售額之間的關(guān)系。5.使用數(shù)據(jù)透視圖分析不同銷售日期的銷售額趨勢，并突出顯示銷售額最高的月份。六、Excel安全與數(shù)據(jù)驗(yàn)證要求：請對以下Excel數(shù)據(jù)進(jìn)行安全設(shè)置和數(shù)據(jù)驗(yàn)證。數(shù)據(jù)來源：某公司員工信息表（員工姓名、性別、年齡、學(xué)歷、部門）1.設(shè)置工作簿的密碼保護(hù)，防止未授權(quán)訪問。2.對“性別”列進(jìn)行數(shù)據(jù)驗(yàn)證，允許用戶只輸入“男”或“女”。3.對“年齡”列進(jìn)行數(shù)據(jù)驗(yàn)證，設(shè)置允許輸入的年齡范圍在18至60歲之間。4.對“學(xué)歷”列進(jìn)行數(shù)據(jù)驗(yàn)證，設(shè)置允許用戶從下拉列表中選擇學(xué)歷。5.對“部門”列進(jìn)行數(shù)據(jù)驗(yàn)證，設(shè)置允許用戶從下拉列表中選擇部門。6.檢查數(shù)據(jù)表中的數(shù)據(jù)是否符合驗(yàn)證規(guī)則，對于不符合規(guī)則的記錄，提示用戶重新輸入。本次試卷答案如下：一、Excel數(shù)據(jù)透視表創(chuàng)建與應(yīng)用1.解析思路：首先選擇數(shù)據(jù)區(qū)域，插入數(shù)據(jù)透視表，將“員工姓名”拖動到行標(biāo)簽區(qū)域，“部門”拖動到列標(biāo)簽區(qū)域，“銷售額”拖動到值區(qū)域，并選擇“求和”計(jì)算方式。2.解析思路：在數(shù)據(jù)透視表字段列表中，右鍵點(diǎn)擊“銷售額”，選擇“插入計(jì)算字段”，在名稱框中輸入新字段的名稱，在公式框中輸入平均值的計(jì)算公式。3.解析思路：在數(shù)據(jù)透視表工具的“設(shè)計(jì)”選項(xiàng)卡中，點(diǎn)擊“排序”，選擇“銷售額”字段，然后點(diǎn)擊“降序排序”。4.解析思路：在數(shù)據(jù)透視表工具的“設(shè)計(jì)”選項(xiàng)卡中，點(diǎn)擊“篩選”，在“顯示”中選擇“此行”，然后勾選“部門”字段，在右側(cè)下拉菜單中選擇“銷售部”。5.解析思路：選中數(shù)據(jù)透視表，點(diǎn)擊“插入”選項(xiàng)卡，選擇合適的圖表類型，例如餅圖或柱狀圖。6.解析思路：選中數(shù)據(jù)透視表，右鍵點(diǎn)擊，選擇“格式化數(shù)據(jù)透視表”，根據(jù)需要調(diào)整字體、字號、顏色等格式。7.解析思路：選中數(shù)據(jù)透視表，右鍵點(diǎn)擊，選擇“移動或復(fù)制”，在“新工作表”選項(xiàng)中點(diǎn)擊“創(chuàng)建”。二、Excel高級篩選與條件格式1.解析思路：選中數(shù)據(jù)區(qū)域，點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡，選擇“高級”，在彈出的“高級篩選”對話框中，選擇“將篩選結(jié)果復(fù)制到其他位置”，設(shè)置條件為“年齡>30”和“性別=男”。2.解析思路：在數(shù)據(jù)區(qū)域中，使用相同的高級篩選方法，設(shè)置條件為“學(xué)歷=本科”和“部門=研發(fā)部”。3.解析思路：使用排序功能，先按“年齡”字段升序排序，再按“學(xué)歷”字段降序排序。4.解析思路：選中“年齡”列，點(diǎn)擊“開始”選項(xiàng)卡，選擇“條件格式”，然后選擇“突出顯示單元格規(guī)則”，設(shè)置條件為“單元格等于”和“40”，然后選擇紅色字體。5.解析思路：選中“學(xué)歷”列，使用相同的方法設(shè)置條件為“單元格等于”和“碩士”，然后選擇加粗字體。6.解析思路：選中篩選后的數(shù)據(jù)，點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡，選擇“新建工作表”，將篩選結(jié)果復(fù)制到新工作表中。7.解析思路：選中篩選后的數(shù)據(jù)，點(diǎn)擊“插入”選項(xiàng)卡，選擇合適的圖表類型，例如餅圖或柱狀圖。三、Excel公式與函數(shù)應(yīng)用1.解析思路：使用公式`=SUMIF(B:B,"條件",C:C)`，其中“條件”為要匹配的文本，“B:B”為條件所在的列，“C:C”為要相加的數(shù)值所在的列。2.解析思路：使用公式`=TEXT(A2,"ddd")`，其中“A2”為日期格式的單元格。3.解析思路：使用公式`=SUMIFS(C:C,A:A,"<="&DATE(2025,1,1),B:B,"條件")`，其中“條件”為日期前的篩選條件。4.解析思路：使用公式`=SUMIFS(C:C,A:A,">="&DATE(2025,1,1),B:B,"條件")`，其中“條件”為日期后的篩選條件。5.解析思路：使用公式`=SUM(C:C)/SUM(B:B)`，其中“C:C”為銷售額列，“B:B”為總銷售額列。6.解析思路：使用公式`=INDEX(A:A,MATCH(MAX(C:C),C:C,0))`，其中“C:C”為銷售額列，用于找到銷售額最高的員工姓名。7.解析思路：將計(jì)算結(jié)果復(fù)制到一個(gè)新的工作表中，可以使用公式或手動輸入。四、Excel宏與VBA應(yīng)用1.解析思路：錄制宏時(shí)，執(zhí)行增加銷售額5%的操作，然后停止錄制宏，保存宏。2.解析思路：使用VBA代碼：```vbaSubCheckSales()DimwsAsWorksheetSetws=ActiveSheetDimlastRowAsLonglastRow=ws.Cells(ws.Rows.Count,"B").End(xlUp).RowIfIsError(Application.CountIf(ws.Range("C2:C"&lastRow),0))Thenws.Range("B2").Value="所有銷售額均大于0"Elsews.Range("B2").Value="存在銷售額為0或負(fù)數(shù)的記錄"EndIfEndSub```3.解析思路：使用VBA代碼，使用`Format`函數(shù)更改日期格式。4.解析思路：使用VBA代碼，使用`Sheet1.Cells`添加新列，并使用`Application.WorksheetFunction`計(jì)算提成。5.解析思路：在VBA代碼中，使用循環(huán)遍歷數(shù)據(jù)區(qū)域，計(jì)算每個(gè)銷售額區(qū)間的員工數(shù)量，并存儲在數(shù)組中，然后使用圖表工具創(chuàng)建圖表。五、Excel數(shù)據(jù)分析與圖表創(chuàng)建1.解析思路：使用公式對銷售額進(jìn)行分組，并使用COUNTIF函數(shù)計(jì)算每個(gè)區(qū)間的員工數(shù)量。2.解析思路：使用圖表工具，選擇餅圖類型，將“銷售額區(qū)間”作為圖表數(shù)據(jù)源，將“員工數(shù)量”作為系列值。3.解析思路：使用圖表工具，選擇柱狀圖類型，將“銷售額區(qū)間”作為圖表數(shù)據(jù)源，將“員工數(shù)量”作為系列值。4.解析思路：使用散點(diǎn)圖，將“員工姓名”作為橫坐標(biāo)，“銷售額”作為縱坐標(biāo)。5.解析思路：使用數(shù)據(jù)透視圖，選擇銷售日期字段，然后添加銷售總額數(shù)值字段，設(shè)置篩選條件為銷售日期。六、Excel安全與數(shù)據(jù)驗(yàn)證1.解析思路：在Excel選項(xiàng)中，選擇“安全”，然后點(diǎn)擊“加密文檔”，輸入密碼并保存。2.解析思路：選中“性別”列，點(diǎn)擊“數(shù)據(jù)”選項(xiàng)卡，選擇“數(shù)據(jù)