2025年excel實操考試題及答案一、基礎(chǔ)操作與數(shù)據(jù)規(guī)范（本大題共3小題，每題15分，共45分）1.打開考生文件夾下"2024銷售數(shù)據(jù).xlsx"工作簿，按以下要求操作：（1）將"1月-6月"工作表標(biāo)簽顏色設(shè)置為標(biāo)準(zhǔn)色中的橙色（索引值46）；（2）在"1月-6月"工作表后插入新工作表，命名為"數(shù)據(jù)匯總"，并將該工作表標(biāo)簽字體設(shè)置為加粗傾斜；（3）將"7月-12月"工作表移動到"數(shù)據(jù)匯總"工作表之前；（4）為"數(shù)據(jù)匯總"工作表設(shè)置保護，允許用戶編輯區(qū)域為A1:C10，密碼為"Hz2025"（區(qū)分大小寫）；（5）將工作簿另存為"2024銷售數(shù)據(jù)-終版.xlsx"到考生文件夾。答案：（1）右鍵點擊"1月-6月"標(biāo)簽→設(shè)置工作表標(biāo)簽顏色→選擇橙色（索引值46需通過顏色對話框自定義，紅255、綠192、藍0）；（2）右鍵點擊"1月-6月"標(biāo)簽→插入→選擇工作表→重命名為"數(shù)據(jù)匯總"→右鍵標(biāo)簽→設(shè)置單元格格式→字體→勾選加粗、傾斜；（3）按住"7月-12月"標(biāo)簽拖動至"數(shù)據(jù)匯總"前；（4）點擊"數(shù)據(jù)匯總"工作表→審閱→保護工作表→勾選"允許用戶編輯區(qū)域"→新建→區(qū)域選擇A1:C10→確定→輸入密碼"Hz2025"→再次輸入確認；（5）文件→另存為→選擇考生文件夾→文件名改為"2024銷售數(shù)據(jù)-終版.xlsx"→保存。2.在"1月-6月"工作表中完成以下數(shù)據(jù)規(guī)范操作：（1）將A列"訂單日期"列格式統(tǒng)一為"2024-01-01"樣式的日期格式；（2）使用快速填充功能，將B列"產(chǎn)品編碼"（如"PF-202401-001"）中的月份部分提取到C列，顯示為"01"（兩位數(shù)）；（3）為D列"銷售數(shù)量"設(shè)置數(shù)據(jù)驗證，允許整數(shù)，范圍1-1000，輸入提示"請輸入1-1000的整數(shù)"，錯誤提示"數(shù)值超出范圍，必須為1-1000"；（4）將E列"單價"中所有小于500的單元格填充為黃色背景（標(biāo)準(zhǔn)色）。答案：（1）選中A列→右鍵→設(shè)置單元格格式→日期→選擇"2024-01-01"類型；（2）在C2輸入"01"（對應(yīng)B2的"PF-202401-001"）→C3輸入"02"（對應(yīng)B3的"PF-202402-003"）→選中C2:C3→向下拖動填充柄→點擊自動填充選項→選擇"快速填充"；（3）選中D列→數(shù)據(jù)→數(shù)據(jù)驗證→設(shè)置→允許"整數(shù)"→最小值1→最大值1000→輸入信息→標(biāo)題"輸入提示"→輸入"請輸入1-1000的整數(shù)"→錯誤警告→標(biāo)題"錯誤提示"→輸入"數(shù)值超出范圍，必須為1-1000"→確定；（4）選中E列→開始→條件格式→新建規(guī)則→使用公式確定格式→輸入"=E1<500"→格式→填充→黃色→確定。3.打開"員工信息表.xlsx"，其中A列"身份證號"為18位，完成以下操作：（1）在B列提取出生年月，格式為"1990-05"；（2）在C列計算截至2024年12月31日的年齡（精確到周歲）；（3）在D列根據(jù)身份證第17位判斷性別，奇數(shù)為"男"，偶數(shù)為"女"；（4）將A列身份證號設(shè)置為文本格式，避免科學(xué)計數(shù)法顯示。答案：（1）B2輸入公式"=TEXT(MID(A2,7,8),"0000-00")"→向下填充；（2）C2輸入公式"=DATEDIF(TEXT(MID(A2,7,8),"0000-00"),"2024-12-31","Y")"→向下填充；（3）D2輸入公式"=IF(MOD(MID(A2,17,1),2)=1,"男","女")"→向下填充；（4）選中A列→右鍵→設(shè)置單元格格式→文本→確定。二、函數(shù)與公式應(yīng)用（本大題共4小題，每題20分，共80分）4.在"銷售分析表"中，A列"地區(qū)"（華北/華南/華東），B列"產(chǎn)品"（A/B/C），C列"月份"（1-12），D列"銷售額"，完成以下計算：（1）在E列計算各記錄的季度（1-3月為Q1，4-6月為Q2，7-9月為Q3，10-12月為Q4）；（2）在F列計算"華東地區(qū)產(chǎn)品B"的累計銷售額（按月份升序累計）；（3）在G2單元格計算"華南地區(qū)"所有產(chǎn)品在Q3的銷售額總和；（4）在H2單元格計算"產(chǎn)品A"各月銷售額的中位數(shù)（忽略0值）。答案：（1）E2輸入公式"=IF(C2<=3,"Q1",IF(C2<=6,"Q2",IF(C2<=9,"Q3","Q4")))"→向下填充；（2）F2輸入數(shù)組公式（需按Ctrl+Shift+Enter）"=SUM((地區(qū)="華東")(產(chǎn)品="B")(月份<=C2)銷售額)"→向下填充（注：實際操作中需將"地區(qū)""產(chǎn)品""月份""銷售額"替換為對應(yīng)列引用，如$A$2:$A$1000="華東"等）；（3）G2輸入公式"=SUMIFS(D:D,A:A,"華南",E:E,"Q3")"；（4）H2輸入公式"=MEDIAN(IF((B:B="A")(D:D>0),D:D))"→按Ctrl+Shift+Enter輸入為數(shù)組公式。5.在"庫存明細表"中，A列"商品ID"，B列"入庫日期"，C列"入庫數(shù)量"，D列"出庫日期"，E列"出庫數(shù)量"，要求：（1）在F列計算單個商品的當(dāng)前庫存（入庫數(shù)量-出庫數(shù)量）；（2）在G2單元格查找商品ID為"SP-2024005"的最近一次入庫日期（最新日期）；（3）在H2單元格計算所有2024年6月1日后入庫商品的平均入庫數(shù)量（保留2位小數(shù)）；（4）使用INDEX+MATCH組合，在I2單元格查找商品ID為"SP-2024018"的出庫數(shù)量。答案：（1）F2輸入公式"=SUMIF(A:A,A2,C:C)-SUMIF(A:A,A2,E:E)"→向下填充；（2）G2輸入公式"=MAX(IF(A:A="SP-2024005",B:B))"→按Ctrl+Shift+Enter；（3）H2輸入公式"=AVERAGEIF(B:B,">"&"2024-6-1",C:C)"→設(shè)置單元格格式為數(shù)值（2位小數(shù)）；（4）I2輸入公式"=INDEX(E:E,MATCH("SP-2024018",A:A,0))"。6.在"項目進度表"中，A列"項目ID"，B列"開始時間"，C列"結(jié)束時間"，D列"實際完成時間"，要求：（1）在E列計算項目計劃工期（結(jié)束時間-開始時間，單位：天）；（2）在F列計算項目延期天數(shù)（實際完成時間>結(jié)束時間時，實際完成時間-結(jié)束時間，否則為0）；（3）在G2單元格統(tǒng)計計劃工期超過30天且延期超過5天的項目數(shù)量；（4）在H列使用TEXT函數(shù)將開始時間轉(zhuǎn)換為"2024年05月12日星期一"格式。答案：（1）E2輸入公式"=DATEDIF(B2,C2,"d")"→向下填充；（2）F2輸入公式"=MAX(D2-C2,0)"→向下填充；（3）G2輸入公式"=COUNTIFS(E:E,">30",F:F,">5")"；（4）H2輸入公式"=TEXT(B2,"yyyy年mm月dd日aaaa")"→向下填充（注：部分系統(tǒng)可能顯示"星期"而非"周"，需根據(jù)Excel版本調(diào)整格式代碼）。7.在"客戶信息表"中，A列"客戶編碼"（如"KH-2024-001"），B列"注冊時間"，C列"最近消費時間"，D列"消費金額"，要求：（1）在E列提取客戶編碼中的年份部分（顯示為"2024"）；（2）在F列計算客戶的活躍天數(shù)（最近消費時間-注冊時間，若未消費則顯示"未活躍"）；（3）在G2單元格計算消費金額前10名客戶的平均消費金額（排除重復(fù)客戶）；（4）在H列使用IFS函數(shù)判斷客戶等級：消費金額≥10000為"VIP"，5000≤消費金額<10000為"白銀"，2000≤消費金額<5000為"青銅"，否則為"普通"。答案：（1）E2輸入公式"=MID(A2,5,4)"→向下填充；（2）F2輸入公式"=IF(C2="","未活躍",DATEDIF(B2,C2,"d"))"→向下填充；（3）G2輸入數(shù)組公式"=AVERAGE(LARGE(UNIQUE(D:D),ROW(1:10)))"→按Ctrl+Shift+Enter（注：需確認D列無空值，否則需調(diào)整UNIQUE參數(shù)）；（4）H2輸入公式"=IFS(D2>=10000,"VIP",D2>=5000,"白銀",D2>=2000,"青銅",TRUE,"普通")"→向下填充。三、數(shù)據(jù)處理與分析（本大題共3小題，每題25分，共75分）8.對"銷售數(shù)據(jù)總表"（包含地區(qū)、產(chǎn)品、月份、銷售額字段）進行以下操作：（1）按"地區(qū)"升序、"銷售額"降序進行多條件排序；（2）使用自動篩選，顯示"華東地區(qū)"且"產(chǎn)品為B"或"銷售額>20000"的記錄；（3）創(chuàng)建高級篩選，條件區(qū)域設(shè)置在Sheet2的A1:B2（A1="地區(qū)"，A2="華南"；B1="產(chǎn)品"，B2="C"），將結(jié)果復(fù)制到Sheet3的A1單元格開始的位置；（4）插入數(shù)據(jù)透視表到新工作表，行標(biāo)簽為"地區(qū)"和"產(chǎn)品"，列標(biāo)簽為"季度"（需將月份轉(zhuǎn)換為季度），值字段為"銷售額"求和，并顯示各地區(qū)產(chǎn)品的季度占比（相對于行）。答案：（1）選中數(shù)據(jù)區(qū)域→數(shù)據(jù)→排序→主要關(guān)鍵字"地區(qū)"→升序→添加條件→次要關(guān)鍵字"銷售額"→降序→確定；（2）數(shù)據(jù)→篩選→點擊"地區(qū)"篩選→勾選"華東"→點擊"產(chǎn)品"篩選→勾選"B"→按住Ctrl勾選"銷售額"篩選→數(shù)字篩選→大于→輸入20000→確定（注：實際操作為"地區(qū)=華東"且（"產(chǎn)品=B"或"銷售額>20000"），需使用自定義篩選組合條件）；（3）在Sheet2的A1輸入"地區(qū)"，A2輸入"華南"；B1輸入"產(chǎn)品"，B2輸入"產(chǎn)品C"（或直接寫"產(chǎn)品C"）→數(shù)據(jù)→高級篩選→列表區(qū)域選擇原數(shù)據(jù)區(qū)域→條件區(qū)域選擇Sheet2!A1:B2→復(fù)制到→選擇Sheet3!A1→確定；（4）選中數(shù)據(jù)區(qū)域→插入→數(shù)據(jù)透視表→新工作表→行字段拖入"地區(qū)""產(chǎn)品"→列字段拖入"月份"→右鍵列標(biāo)簽→組合→起始于1→結(jié)束于12→步長3→標(biāo)簽改為"Q1-Q4"→值字段拖入"銷售額"→值顯示方式→選擇"行匯總的百分比"。9.在"員工考勤表"（包含姓名、部門、入職日期、遲到次數(shù)、請假天數(shù)字段）中：（1）使用分類匯總功能，按"部門"匯總各部門的平均遲到次數(shù)和總請假天數(shù)（先匯總平均遲到次數(shù)，再匯總總請假天數(shù)）；（2）創(chuàng)建數(shù)據(jù)透視圖（簇狀柱形圖），顯示各部門的平均遲到次數(shù)對比；（3）為"入職日期"列創(chuàng)建組，按"入職年份"分組（2020年及以前/2021年/2022年/2023年/2024年），并統(tǒng)計每組的員工人數(shù)；（4）使用切片器關(guān)聯(lián)數(shù)據(jù)透視表，通過切片器可篩選部門（市場部/技術(shù)部/財務(wù)部）。答案：（1）按"部門"排序→數(shù)據(jù)→分類匯總→分類字段"部門"→匯總方式"平均值"→選定匯總項"遲到次數(shù)"→確定→再次分類匯總→替換當(dāng)前分類匯總→匯總方式"求和"→選定匯總項"請假天數(shù)"→勾選"保留原列數(shù)據(jù)"；（2）選中數(shù)據(jù)區(qū)域→插入→數(shù)據(jù)透視圖→選擇簇狀柱形圖→字段列表勾選"部門""遲到次數(shù)"（值為平均值）→調(diào)整圖表標(biāo)題和坐標(biāo)軸；（3）在數(shù)據(jù)透視表中右鍵"入職日期"字段→組合→起始于2020-1-1→結(jié)束于2024-12-31→步長1年→標(biāo)簽自定義為"2020年及以前""2021年""2022年""2023年""2024年"→值字段勾選"姓名"（計數(shù)）；（4）數(shù)據(jù)透視表工具→分析→插入切片器→勾選"部門"→調(diào)整切片器大小→選擇"市場部""技術(shù)部""財務(wù)部"作為可用篩選項。10.對"庫存預(yù)警表"（包含商品名稱、安全庫存、當(dāng)前庫存、最后更新時間字段）進行以下高級操作：（1）使用條件格式，將"當(dāng)前庫存"<"安全庫存"的單元格填充為紅色背景，"當(dāng)前庫存"在安全庫存100%-120%之間的填充為黃色背景；（2）創(chuàng)建動態(tài)圖表，當(dāng)通過下拉菜單選擇商品名稱時，圖表自動顯示該商品的安全庫存與當(dāng)前庫存對比；（3）使用PowerQuery將"庫存預(yù)警表"與"商品分類表"（商品名稱、分類）合并，按商品名稱匹配，添加"分類"字段；（4）錄制宏"提供預(yù)警報告"，功能為：自動篩選當(dāng)前庫存<安全庫存的記錄→復(fù)制到新工作表→命名為"預(yù)警商品"→保存工作簿。答案：（1）選中數(shù)據(jù)區(qū)域→開始→條件格式→新建規(guī)則→規(guī)則類型"使用公式確定格式"→輸入"=C2<B2"（假設(shè)B列安全庫存，C列當(dāng)前庫存）→格式→填充紅色→確定→新建規(guī)則→輸入"=AND(C2>=B2,C2<=B21.2)"→格式→填充黃色→確定；（2）數(shù)據(jù)→數(shù)據(jù)驗證→允許