2025年excel提取考试试题及答案

2025年excel提取考试试题及答案一、基础操作题（共4题，每题15分）1.文本型数据提取（素材：A列存储"员工姓名-工号-部门"组合字符串，如"张三-2025001-技术部"，共50条记录）要求：在B列提取工号（工号固定为7位数字），C列提取部门名称（部门名称为2-4个汉字）。操作步骤及答案：（1）B列工号提取：使用MID函数，工号位于"-"分隔的第二部分。首先通过FIND函数定位分隔符位置，公式为`=MID(A2,FIND("-",A2)+1,FIND("-",A2,FIND("-",A2)+1)-FIND("-",A2)-1)`。解析：第一个FIND("-",A2)返回第一个分隔符位置（如"张三-2025001-技术部"中第一个"-"在第3位），+1后定位工号起始位置；第二个FIND("-",A2,起始位置)从第一个分隔符后查找第二个"-"的位置（如第10位），两者相减-1得到工号长度（7位），最终提取结果为"2025001"。（2）C列部门提取：使用RIGHT函数结合LEN函数，公式为`=RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2,"-","|",2)))`。解析：SUBSTITUTE(A2,"-","|",2)将第二个"-"替换为"|"，FIND("|",...)返回第二个分隔符的位置（如第10位），LEN(A2)减去该位置得到部门名称长度（如"技术部"为3位），RIGHT函数提取结果为"技术部"。2.日期型数据提取（素材：D列存储"2025-03-1514:30:22"格式的时间戳，共80条记录）要求：在E列提取年份，F列提取月份（补0，如"03"），G列提取当日星期（如"星期三"）。操作步骤及答案：（1）E列年份提取：使用YEAR函数，公式`=YEAR(D2)`，结果为"2025"。（2）F列月份提取：使用TEXT函数格式化，公式`=TEXT(D2,"mm")`，结果为"03"（若原月份为3月则显示"03"）。（3）G列星期提取：使用TEXT函数结合星期格式，公式`=TEXT(D2,"aaaa")`，结果为"星期三"（具体结果根据日期调整）。3.数值型数据提取（素材：H列存储"收入：￥12800.50；支出：￥8900.30"格式的字符串，共60条记录）要求：在I列提取收入金额（保留2位小数），J列提取支出金额（取整）。操作步骤及答案：（1）I列收入提取：使用MID+RIGHT组合，先通过FIND定位"收入：￥"的结束位置（长度为5），再通过RIGHT提取后续字符，公式`=VALUE(MID(H2,FIND("收入：￥",H2)+5,FIND("；",H2)-FIND("收入：￥",H2)-5))`。解析：FIND("收入：￥",H2)返回起始位置（如第1位），+5后定位金额起始位置；FIND("；",H2)返回分号位置，两位置相减-5得到金额长度（如"12800.50"为8位），VALUE函数转换为数值，结果为12800.50。（2）J列支出提取：使用SUBSTITUTE+MID，公式`=INT(VALUE(MID(SUBSTITUTE(H2,"支出：￥",""),FIND("支出：￥",H2)+5,LEN(H2))))`。解析：SUBSTITUTE替换"支出：￥"为空，MID从原位置+5开始提取剩余字符（如"8900.30"），VALUE转换后用INT取整，结果为8900。4.固定宽度分列提取（素材：K列存储"202503151001技术部张三"格式字符串，规则：前8位为日期（YYYYMMDD），接下来4位为订单号，其后3位为部门代码（技术部=001，销售部=002），最后2-4位为姓名）要求：在L列提取日期（格式YYYY-MM-DD），M列提取订单号，N列提取部门名称，O列提取姓名。操作步骤及答案：（1）L列日期提取：使用LEFT+TEXT，公式`=TEXT(LEFT(K2,8),"0000-00-00")`，结果为"2025-03-15"。（2）M列订单号提取：使用MID，公式`=MID(K2,9,4)`，结果为"1001"。（3）N列部门名称提取：使用VLOOKUP，先在P1:Q4建立映射表（P列"001","002"；Q列"技术部","销售部"），公式`=VLOOKUP(MID(K2,13,3),P:Q,2,0)`，结果为"技术部"。（4）O列姓名提取：使用RIGHT+LEN，公式`=RIGHT(K2,LEN(K2)-16)`（总长度-16位固定部分），结果为"张三"。二、进阶函数题（共3题，每题20分）1.多条件跨表提取（素材：表1"销售记录"包含字段[订单ID,客户ID,销售日期,金额]；表2"客户信息"包含字段[客户ID,客户姓名,所属区域]）要求：在表1中新增"客户姓名"列（E列）和"所属区域"列（F列），提取条件为订单ID>2025000且销售日期在2025/1/1至2025/6/30之间。操作步骤及答案：（1）E列客户姓名提取：使用XLOOKUP函数，公式`=XLOOKUP(AND(B2>2025000,D2>=DATE(2025,1,1),D2<=DATE(2025,6,30)),表2!A:A,表2!B:B)`。解析：XLOOKUP的第一个参数为条件组合（需转换为逻辑值），若条件满足则返回匹配的客户姓名；若需处理多条件，可嵌套IFS函数：`=IFS(B2>2025000,D2>=DATE(2025,1,1),D2<=DATE(2025,6,30),XLOOKUP(B2,表2!A:A,表2!B:B),"")`。（2）F列所属区域提取：类似逻辑，公式`=IF(AND(B2>2025000,D2>=DATE(2025,1,1),D2<=DATE(2025,6,30)),VLOOKUP(B2,表2!A:C,3,0),"")`。注意：VLOOKUP需确保表2按客户ID升序排列，或使用XLOOKUP替代避免排序限制，公式`=XLOOKUP(B2,表2!A:A,表2!C:C,"",0,AND(B2>2025000,D2>=DATE(2025,1,1),D2<=DATE(2025,6,30)))`。2.动态数组提取唯一值（素材：G列存储重复的"产品型号"数据，共200条记录，包含"P2025A""P2025B""P2025C"等型号）要求：在H列自动提取不重复的产品型号，按字母升序排列。操作步骤及答案：使用UNIQUE+SORT组合函数，公式`=SORT(UNIQUE(G2:G201))`。解析：UNIQUE函数返回G列的唯一值数组（如{"P2025A","P2025B","P2025C"}），SORT函数按字母顺序排列，结果从上到下依次显示"P2025A""P2025B""P2025C"（实际数量根据唯一值个数动态调整）。3.复杂文本提取（素材：I列存储"[2025-03-15]项目A-完成；[2025-03-16]项目B-进行中；[2025-03-17]项目C-未开始"格式的多行备注，共30条记录）要求：在J列提取所有状态为"完成"的项目名称（如"项目A"），多个结果用逗号分隔。操作步骤及答案：使用FILTER+TEXTSPLIT+MID组合函数，公式`=TEXTJOIN(",",TRUE,FILTER(MID(TEXTSPLIT(I2,"；","-"),2,1),MID(TEXTSPLIT(I2,"；","-"),3,1)="完成"))`。解析：（1）TEXTSPLIT(I2,"；","-")按分号和短横线拆分文本，提供二维数组（如[["[2025-03-15]项目A","完成"],["[2025-03-16]项目B","进行中"],["[2025-03-17]项目C","未开始"]]）。（2）MID(...,2,1)提取第二列（项目名称，如"项目A""项目B""项目C"）。（3）FILTER筛选第三列为"完成"的项目名称（如"项目A"）。（4）TEXTJOIN用逗号合并结果，最终J列显示"项目A"。三、综合应用题（共2题，每题25分）1.数据透视表高级提取（素材：表3"库存明细"包含字段[产品ID,产品名称,入库日期,入库数量,出库日期,出库数量]，数据量500条）要求：通过数据透视表提取以下信息：（1）各产品截至2025-6-30的最新入库日期；（2）各产品2025年第二季度（4-6月）的总出库数量；（3）库存状态（库存=入库数量-出库数量，显示"正常""预警"（库存≤100）"缺货"（库存≤0））。操作步骤及答案：（1）最新入库日期提取：将"产品名称"拖入行区域，"入库日期"拖入值区域，值字段设置为"最大值"（选择"最大值"计算最新日期）。（2）第二季度总出库数量：将"出库数量"拖入值区域，值字段设置为"求和"；添加日期筛选器，选择"2025/4/1"至"2025/6/30"。（3）库存状态计算：添加计算字段，公式`=SUM(入库数量)-SUM(出库数量)`，命名为"当前库存"；再添加计算字段，公式`=IF(当前库存<=0,"缺货",IF(当前库存<=100,"预警","正常"))`，命名为"库存状态"。2.PowerQuery数据清洗与提取（素材：表4"原始数据"包含乱序字段[日期（格式混乱）,金额（含￥符号和逗号）,备注（部分为空）,员工ID（含字母和数字）]，数据量1000条）要求：通过PowerQuery完成以下提取：（1）统一日期格式为"YYYY/MM/DD"；（2）提取金额为纯数值（如￥12,800.50转为12800.50）；（3）筛选员工ID以"E"开头且长度为6位的记录；（4）删除备注为空的行。操作步骤及答案：（1）日期格式转换：选中"日期"列→转换→日期→格式→选择"年/月/日"（或使用自定义格式"yyyy/mm/dd"）。