2025年excel考試訓(xùn)練題附答案一、數(shù)據(jù)錄入與格式設(shè)置（共30分）1.打開考生文件夾下"2025Q1科技產(chǎn)品銷售表.xlsx"，完成以下操作（15分）：（1）將A1:H1單元格區(qū)域合并后居中，設(shè)置字體為"微軟雅黑"、16號、加粗，背景色為"淺藍(lán)"（調(diào)色板第4行第2列）；（2）將"銷售日期"列（B列）中格式混亂的日期統(tǒng)一轉(zhuǎn)換為"YYYY年MM月DD日"格式（示例：2025-01-15需轉(zhuǎn)為2025年01月15日）；（3）為"銷售額（萬元）"列（G列）添加數(shù)據(jù)條條件格式，數(shù)據(jù)條顏色設(shè)置為"橙色"，僅顯示數(shù)據(jù)條不顯示數(shù)值；（4）將所有文本型數(shù)據(jù)（除標(biāo)題行外）的對齊方式設(shè)置為"垂直居中"，數(shù)字型數(shù)據(jù)設(shè)置為"水平右對齊"。答案：（1）選中A1:H1，點(diǎn)擊"開始"選項(xiàng)卡-對齊方式組-"合并后居中"；在字體組設(shè)置字體、字號、加粗；點(diǎn)擊填充顏色下拉菜單，選擇淺藍(lán)（第4行第2列）；（2）選中B列數(shù)據(jù)，點(diǎn)擊"數(shù)據(jù)"選項(xiàng)卡-"數(shù)據(jù)工具"組-"分列"，選擇"分隔符號"下一步，取消所有分隔符勾選，下一步選擇"日期"格式為"YMD"，完成；右鍵B列單元格-設(shè)置單元格格式-日期-選擇"YYYY年MM月DD日"；（3）選中G2:G200，點(diǎn)擊"開始"選項(xiàng)卡-條件格式-數(shù)據(jù)條-其他規(guī)則，在"填充"中選擇橙色，勾選"僅顯示數(shù)據(jù)條"；（4）選中A2:H200，點(diǎn)擊"開始"選項(xiàng)卡-對齊方式組-"垂直居中"；選中C列（產(chǎn)品型號）、E列（銷售人員）、F列（銷售門店），設(shè)置"水平居中"；選中D列（數(shù)量）、G列（銷售額），設(shè)置"水平右對齊"。2.在"員工信息表"工作表中完成以下操作（15分）：（1）在A列前插入一列，列標(biāo)題為"工號"，工號規(guī)則為：部門代碼（市場部=SC，技術(shù)部=JS，財(cái)務(wù)部=CW）+入職年份后兩位+3位流水號（如市場部2023年入職的第5位員工工號為SC23005）；（2）將"出生日期"列（E列）中"1990-05-20"形式的日期轉(zhuǎn)換為"農(nóng)歷日期"（需調(diào)用TEXT函數(shù)，農(nóng)歷轉(zhuǎn)換規(guī)則：=TEXT(出生日期,"[$-130000]yyyy年m月d日")）；（3）為"聯(lián)系方式"列（G列）設(shè)置數(shù)據(jù)驗(yàn)證，要求輸入格式為"11位數(shù)字"，錯(cuò)誤提示信息為"請輸入11位有效手機(jī)號"。答案：（1）右鍵A列-插入，A1輸入"工號"；A2輸入公式：=IF(B2="市場部","SC",IF(B2="技術(shù)部","JS","CW"))&RIGHT(D2,2)&TEXT(ROW()-1,"000")，下拉填充（假設(shè)D列為入職日期）；（2）選中E2:E50，輸入公式：=TEXT(E2,"[$-130000]yyyy年m月d日")，按Ctrl+Enter批量填充；（3）選中G2:G50，點(diǎn)擊"數(shù)據(jù)"選項(xiàng)卡-數(shù)據(jù)驗(yàn)證-設(shè)置，允許"文本長度"，數(shù)據(jù)"等于"，長度"11"；切換到"出錯(cuò)警告"，標(biāo)題"輸入錯(cuò)誤"，信息"請輸入11位有效手機(jī)號"，確定。二、函數(shù)與公式應(yīng)用（共40分）1.在"銷售分析表"中完成以下計(jì)算（20分）：（1）用VLOOKUP函數(shù)在H列"產(chǎn)品類別"中匹配"產(chǎn)品型號"對應(yīng)的類別（類別信息存儲在"產(chǎn)品字典"工作表A:B列，A列為型號，B列為類別）；（2）用SUMIFS函數(shù)在I列計(jì)算"區(qū)域累計(jì)銷售額"（區(qū)域信息在C列，需統(tǒng)計(jì)同區(qū)域、同產(chǎn)品類別截至當(dāng)前行的累計(jì)銷售額）；（3）用IF函數(shù)在J列標(biāo)記"高銷產(chǎn)品"：若當(dāng)前行銷售額≥所在區(qū)域該產(chǎn)品類別的平均銷售額，則標(biāo)記"是"，否則"否"；（4）用TEXT函數(shù)在K列將"銷售日期"轉(zhuǎn)換為"季度+月份"形式（如2025-03-10轉(zhuǎn)為Q1-03）。答案：（1）H2公式：=VLOOKUP(D2,產(chǎn)品字典!A:B,2,0)（假設(shè)D列為產(chǎn)品型號）；（2）I2公式：=SUMIFS(G$2:G2,C$2:C2,C2,H$2:H2,H2)（G列為銷售額，C列為區(qū)域，H列為產(chǎn)品類別）；（3）J2公式：=IF(G2>=AVERAGEIFS(G:G,C:C,C2,H:H,H2),"是","否")；（4）K2公式：=TEXT(B2,"Q"m"-"mm)（B列為銷售日期）。2.在"員工績效表"中完成以下計(jì)算（20分）：（1）用INDEX+MATCH組合函數(shù)在F列"直屬領(lǐng)導(dǎo)"中查找員工對應(yīng)的領(lǐng)導(dǎo)姓名（領(lǐng)導(dǎo)信息存儲在"組織架構(gòu)"工作表A:C列，A列為員工姓名，B列為部門，C列為領(lǐng)導(dǎo)姓名）；（2）用DATEDIF函數(shù)在G列計(jì)算"司齡（年）"（截至2025年12月31日，結(jié)果保留1位小數(shù)）；（3）用MAXIFS函數(shù)在H列計(jì)算"部門最高績效分"（績效分在E列，部門在B列）；（4）用數(shù)組公式在I列計(jì)算"績效排名"（相同分?jǐn)?shù)并列排名，如95、95、90分別排名1、1、3）。答案：（1）F2公式：=INDEX(組織架構(gòu)!C:C,MATCH(A2,組織架構(gòu)!A:A,0))（A列為員工姓名）；（2）G2公式：=DATEDIF(D2,"2025-12-31","Y")+DATEDIF(D2,"2025-12-31","M")/12（D列為入職日期），設(shè)置單元格格式為數(shù)值（1位小數(shù)）；（3）H2公式：=MAXIFS(E:E,B:B,B2)（E列為績效分，B列為部門）；（4）I2公式：=RANK.EQ(E2,E:E,0)+COUNTIF(E:E,">"&E2)-COUNTIF(E:E,"="&E2)，按Ctrl+Shift+Enter輸入（若使用WPS，直接輸入=RANK(E2,E:E)）。三、數(shù)據(jù)透視表與圖表（共25分）1.基于"2025Q1科技產(chǎn)品銷售表"數(shù)據(jù)創(chuàng)建數(shù)據(jù)透視表（15分）：（1）將數(shù)據(jù)透視表放置在"透視分析"工作表A1單元格；（2）行字段為"銷售門店"，列字段為"產(chǎn)品類別"，值字段為"銷售額（萬元）"（求和）；（3）添加"切片器"關(guān)聯(lián)"區(qū)域"字段，調(diào)整切片器大小為寬12cm、高8cm；（4）設(shè)置值顯示方式為"列匯總的百分比"（如手機(jī)類各門店銷售額占手機(jī)類總銷售額的比例）。答案：（1）選中數(shù)據(jù)區(qū)域，點(diǎn)擊"插入"選項(xiàng)卡-數(shù)據(jù)透視表，選擇放置位置為"透視分析"!A1；（2）在數(shù)據(jù)透視表字段列表中，將"銷售門店"拖入行區(qū)域，"產(chǎn)品類別"拖入列區(qū)域，"銷售額（萬元）"拖入值區(qū)域（確保值字段為求和）；（3）點(diǎn)擊數(shù)據(jù)透視表-分析選項(xiàng)卡-插入切片器，勾選"區(qū)域"字段，調(diào)整切片器大小：右鍵切片器-大小和屬性-設(shè)置寬度12cm、高度8cm；（4）右鍵值字段"求和項(xiàng)：銷售額（萬元）"-值顯示方式-列匯總的百分比。2.基于"銷售趨勢"工作表數(shù)據(jù)制作圖表（10分）：（1）選擇"月份"（A列）和"智能手表銷售額"（B列）、"平板電腦銷售額"（C列）數(shù)據(jù)，創(chuàng)建帶數(shù)據(jù)標(biāo)簽的簇狀柱形圖；（2）設(shè)置圖表標(biāo)題為"2025年1-6月智能設(shè)備銷售對比"，字體14號、加粗；（3）將垂直軸最小值設(shè)置為50，主要刻度單位為100；（4）添加次要坐標(biāo)軸，將"總銷售額"（D列）以折線圖形式顯示在次要軸上。答案：（1）選中A1:C7，點(diǎn)擊"插入"選項(xiàng)卡-圖表-柱形圖-簇狀柱形圖；右鍵數(shù)據(jù)系列-添加數(shù)據(jù)標(biāo)簽；（2）點(diǎn)擊圖表標(biāo)題-輸入"2025年1-6月智能設(shè)備銷售對比"，在"開始"選項(xiàng)卡設(shè)置字體14號、加粗；（3）雙擊垂直軸-設(shè)置坐標(biāo)軸格式-最小值"固定"50，主要刻度單位"固定"100；（4）選中D列數(shù)據(jù)（總銷售額），復(fù)制后粘貼到圖表中；右鍵新數(shù)據(jù)系列-設(shè)置數(shù)據(jù)系列格式-系列繪制在"次要坐標(biāo)軸"；圖表類型改為折線圖。四、高級操作（共5分）在"數(shù)據(jù)清洗"工作表中完成以下操作：（1）使用PowerQuery合并"1月銷售"、"2月銷售"、"3月銷售"三個(gè)工作表（結(jié)構(gòu)相同）；（2）刪除"備注"列中包含"測試"字樣的記錄；（3）將"產(chǎn)品型號"列中"X-2025"和"X-2025Pro"統(tǒng)一更名為"X系列2025款"；（4）將清洗后的數(shù)據(jù)加載到當(dāng)前工作簿的新工作表，命名為"Q1整合銷售數(shù)據(jù)"。答案：（1）點(diǎn)擊"數(shù)據(jù)"選項(xiàng)卡-獲取數(shù)據(jù)-來自文件-從工作簿，選擇當(dāng)前文件