数据分析Excel面试题目及答案.docxVIP

数据分析Excel面试题目及答案

一、基础操作篇（考察基本功熟练度）

题目：用VLOOKUP函数从“产品清单”表中匹配“销售表”中对应产品的单价时，出现#N/A错误，可能有哪些原因？如何排查？

答案：

可能原因：①两张表中“产品名称”（匹配关键字）不一致（如空格、大小写差异，例：“苹果”vs“苹果”）；②匹配范围未锁定（未加$，下拉时范围偏移）；③目标值在源表中不存在；④匹配类型设错（需要精确匹配时用FALSE，却用了TRUE）。

排查步骤：

先检查匹配关键字：选中两表的“产品名称”列，用“=A1=产品清单！A1”对比，返回FALSE的就是不一致的单元格，再用TRIM()函数清除空格，UPPER()/LOWER()统一大小写；

检查VLOOKUP公式的范围：确认第二参数（源数据范围）是否加了绝对引用（如A1:B100）；

若前两步没问题，用“COUNTIF(产品清单！A:A,销售表！A1)”检查目标值是否存在，返回0说明源表无该数据。

题目：如何用数据透视表快速计算“各区域每月销售额”，并在表中新增“环比增长率”字段？

答案：

步骤：

插入数据透视表：选中数据源，插入→数据透视表，选择放置位置；

布局字段：将“区域”拖到“行”，“日期”拖到“行”（右键“日期”字段→组合→选“月”），“销售额”拖到“值”（确保值字段为“求和”）；

新增环比字段：在数据透视表内右键→插入计算字段，字段名称填“环比增长率”，公式写“=(销售额-上月销售额)/上月销售额”（注意：数据透视表中“上月销售额”需用OFFSET函数引用上一行数据，实际公式为“=(B2-OFFSET(B2,-1,0))/OFFSET(B2,-1,0)”，下拉时自动适配）；

格式调整：选中“环比增长率”列，设置为“百分比”格式，保留2位小数。

二、进阶应用篇（考察问题解决能力）

题目：现有一份包含1000行的“客户消费数据”，其中“消费金额”列有缺失值，“客户ID”列有重复（同一客户多次消费）。请说明：①如何处理“消费金额”的缺失值？②如何快速提取“每个客户的首次消费日期”？

答案：

①缺失值处理（需结合业务场景）：

若缺失量少（5%）：看“消费金额”是否与“产品数量”“单价”相关，若有则用“产品数量*单价”补全；若无关联，用该客户的“平均消费金额”（=AVERAGEIF(客户ID列，当前客户ID,消费金额列)）或同行业务的“中位数消费金额”补全（避免均值受极端值影响）；

若缺失量多（20%）：不建议直接填充，可新增“消费金额是否缺失”字段标记，后续分析时单独说明，或删除缺失行（需确认删除后样本是否有代表性）。

②提取首次消费日期：

方法1（函数法）：在空白列输入“=MINIFS(消费日期列，客户ID列，A2)”（A2为当前行客户ID），下拉即可得到每个客户的最小日期（首次消费）；

方法2（数据透视表法）：将“客户ID”拖到“行”，“消费日期”拖到“值”，右键“消费日期”值字段→选择“最小值”，直接生成每个客户的首次消费日期。

题目：用Excel计算“满足‘区域为华东’且‘消费金额500’的客户数”，有哪些方法？请写出具体公式。

答案：

两种常用方法：

SUMIFS函数（多条件计数，推荐）：

公式：=SUMIFS(客户ID列，区域列，华东,消费金额列，500)

原理：SUMIFS第1参数为“要计数的列”（客户ID列，非空即可），后续参数成对出现（条件列1,条件1,条件列2,条件2...），自动统计同时满足所有条件的行数。

SUMPRODUCT函数（数组计数）：

公式：=SUMPRODUCT((区域列=华东)*(消费金额列500))

原理：括号内的条件会返回TRUE/FALSE，相乘时自动转为1/0，SUMPRODUCT对结果求和，即满足条件的数量（注意：区域列和消费金额列需为同一长度，不能有整列引用如A:A，需指定具体范围如A1:A1000）。

三、综合分析篇（考察业务落地能力）

题目：现有“2024年1-6月销售数据”，包含字段：日期、区域（北/上/广/深）、产品类别（家电/数码/服装）、销售额、成本。请用Excel完成以下分析，写出关键步骤：

计算各区域上半年的“毛利率”（毛利率=(销售额-成本)/销售额）；

找出“数码类产品”在各月份的“销售额TOP2的区域”；

制作“各区域上半年销售额趋势图”，要求能清晰对比各区域