2026年数据分析师面试Excel高级函数与透视表

2026年数据分析师面试Excel高级函数与透视表一、Excel高级函数应用（共5题，每题6分，总分30分）1.数据清洗与验证（6分）某电商平台销售数据包含“客户注册地”（如“广东省深圳市”“浙江省杭州市”）、“订单金额”（含单位“元”），需通过Excel高级函数清洗数据，完成以下任务：（1）将“客户注册地”统一格式为“省份-城市”（如“广东省-深圳市”）；（2）将“订单金额”转换为数值型（去除“元”单位）；（3）统计“订单金额”大于5000的订单数量。2.动态条件计算（6分）某制造业公司2025年各部门销售数据如下：|部门|销售额（万元）|完成率|目标增长率||||--|||A|120|90%|15%||B|150|105%|20%||C|80|80%|10%|要求：（1）使用数组公式计算各部门“实际增长率”（实际增长率=(销售额×增长率）-销售额）；（2）使用`IF`嵌套`VLOOKUP`判断各部门是否达标（完成率≥100%且实际增长率≥目标增长率，结果为“达标”或“未达标”）。3.错误值处理与数据透视（6分）某银行客户数据包含“交易金额”列，部分数据因系统错误显示为`#DIV/0!`，需通过Excel高级函数：（1）隐藏错误值，并计算有效交易金额的总和；（2）使用`INDEX+MATCH`查找“交易金额”最大的前3位客户姓名；（3）若某客户“交易金额”为`#N/A`，则标记为“无交易记录”。4.多条件动态查找（6分）某连锁超市2025年分店数据如下：|分店编号|地区|营业额（万元）|老板姓名||-||-|-||001|华东|200|张三||002|华东|180|李四||003|华南|250|王五||004|华北|150|赵六|要求：（1）使用`SUMIFS`计算“华东地区”分店中“老板姓名”为“张三”的营业额；（2）使用`INDEX+MATCH+IF`查找“华北地区”营业额最高的分店编号。5.动态排名与条件格式（6分）某公司员工销售数据如下：|员工ID|部门|销售额（万元）|排名||--||-|||1001|A|120|3||1002|B|150|1||1003|A|110|5|要求：（1）使用`RANK.EQ`计算各部门内部销售额排名；（2）使用`IF`函数根据排名奖励规则（前3名奖励10000元，4-5名奖励5000元）计算奖金。二、数据透视表应用（共5题，每题6分，总分30分）6.多维度数据聚合（6分）某零售企业2025年各门店商品销售数据如下表：|日期|门店|商品类别|销售量|销售额（元）||--|--|-|--|--||2025-01-01|A店|服装|200|12000||2025-01-01|B店|鞋类|150|7500||2025-01-02|A店|鞋类|180|9000|要求：（1）创建数据透视表，按“门店”和“商品类别”汇总“销售额”，并显示“总计”；（2）添加切片器筛选“2025年”数据，并计算各门店“销售额”占比。7.动态切片与计算字段（6分）某餐饮企业2025年分店数据如下：|日期|分店|餐品类型|客单价|人数||--|--|-|--|||2025-01-01|东门分店|午餐|80|120||2025-01-01|西门分店|晚餐|120|80|要求：（1）创建数据透视表，按“分店”和“餐品类型”汇总“总人数”，并添加计算字段“总营业额”（总人数×客单价）；（2）使用切片器筛选“2025年”数据，并显示“午晚餐占比”（总营业额/总人数）。8.透视表更新与数据刷新（6分）某物流公司2025年分线路运输数据如下：|日期|线路|货物类型|成本（元）|利润率||--|--|-||--||2025-01-01|线路1|服装|5000|20%||2025-01-02|线路2|电子产品|8000|30%|要求：（1）创建数据透视表，按“线路”汇总“成本”和“利润率”，并显示“平均值”；（2）若数据源新增2025年2月数据，如何确保透视表自动更新？说明操作步骤。9.透视表交叉分析（6分）某医药公司2025年药品销售数据如下：|日期|地区|药品类别|销售量|销售额（元）||--|--|-|--|--||2025-01-01|北方|西药|300|18000||2025-01-01|南方|中药|400|12000|要求：（1）创建数据透视表，按“地区”和“药品类别”交叉汇总“销售额”，并排序显示“最高销售额”地区；（2）使用“值字段设置”将“销售额”显示为“货币格式”，并保留两位小数。10.透视表数据透传（6分）某房地产公司2025年楼盘销售数据如下：|日期|楼盘|区域|房价（万元）|销售套数||--|--|--|--|-||2025-01-01|楼盘A|A区|500|10||2025-01-02|楼盘B|B区|600|8|要求：（1）创建数据透视表，按“区域”汇总“销售套数”，并计算“平均房价”；（2）将透视表数据透传到新工作表，并调整布局为“以表格形式显示”。答案与解析一、Excel高级函数应用1.数据清洗与验证（6分）答案：（1）公式：=TEXT(LEFT(D2,FIND("省",D2)-1),"0")&"-"&MID(D2,FIND("省",D2)+2,LEN(D2))（2）公式：=VALUE(LEFT(E2,LEN(E2)-2))（3）公式：=COUNTIF(E2:E100,">5000")解析：-第（1）题利用`FIND`和`MID`提取省份和城市，`TEXT`格式化；-第（2）题去除“元”后用`VALUE`转为数值；-第（3）题`COUNTIF`统计大于5000的行。2.动态条件计算（6分）答案：（1）公式：={SUM(B2:B4(1+C2:C4))}-SUM(B2:B4)（2）公式：=IF(AND(C2:C4>=1,(B2:B4(1+C2:C4))-B2:B4>=D2:D4),"达标","未达标")解析：-第（1）题数组公式计算实际增长率；-第（2）题`IF`嵌套`VLOOKUP`判断条件。3.错误值处理与数据透视（6分）答案：（1）公式：=SUMIF(F2:F100,"<>",G2:G100)（2）公式：=INDEX(A2:A100,MATCH(MAX(B2:B100),B2:B100,0))（3）公式：=IF(ISNA(H2),"无交易记录",H2)解析：-第（1）题`SUMIF`忽略错误值；-第（2）题`INDEX+MATCH`查找最大值；-第（3）题`ISNA`判断`#N/A`。4.多条件动态查找（6分）答案：（1）公式：=SUMIFS(C2:C100,A2:A100,"华东",B2:B100,"张三")（2）公式：=INDEX(A2:A100,MATCH(MAX(C2:C100),C2:C100,0))解析：-第（1）题`SUMIFS`多条件求和；-第（2）题`INDEX+MATCH`查找最大销售额行。5.动态排名与条件格式（6分）答案：（1）公式：=RANK.EQ(C2:C4,C:C,0)（2）公式：=IF(D2<=3,10000,IF(AND(D2>3,D2<=5),5000,0))解析：-第（1）题`RANK.EQ`排名；-第（2）题`IF`多条件计算奖金。二、数据透视表应用6.多维度数据聚合（6分）答案：（1）操作：插入数据透视表，行标签“门店”“商品类别”，值“销售额”求和；（2）操作：添加切片器筛选“日期”字段，透视表字段设置“销售额”为“值显示方式”-“占总计百分比”。解析：-多维度聚合需设置层级；-百分比计算需字段设置调整。7.动态切片与计算字段（6分）答案：（1）操作：插入数据透视表，行标签“分店”“餐品类型”，值“人数”求和，添加计算字段“总营业额”=[人数][客单价]；（2）操作：添加切片器筛选“日期”，在值字段设置中显示“午晚餐占比”=[总营业额]/[人数]。解析：-计算字段需在“值字段设置”中添加；-百分比需自定义公式。8.透视表更新与数据刷新（6分）答案：（1）操作：插入数据透视表，行标签“线路”，值“成本”“利润率”求平均值；（2）操作：右键透视表选择“刷新”；或设置“数据透视表选项”-“数据刷新”为“自动”。解析：-手动刷新需右键操作；-自动刷新需调整选项。9.透视表交叉分析（6分）答案：（1）操作：插入数据透视表，行标签“地区”，列标签“药品类别”，值“销售额”求和，排序显示“总计”行；（2）操作：右键“销售额”字