数据分析Excel面试题目及答案（实操导向版）.docxVIP

数据分析Excel面试题目及答案（实操导向版）

一、基础操作与数据规范（10分）

题目1：

现有一份销售数据表（含“日期”“地区”“产品类别”“销售额”“客户ID”列），其中“日期”列格式为文本（如“2023-10-05”“2023/10/05”“10-05-2023”混合），“销售额”列部分单元格含千位分隔符（如“1,250”）且部分为空值。请说明如何标准化这两列数据，确保可用于后续计算。

答案1：

日期列标准化：

选中“日期”列→数据选项卡→点击“分列”→选择“分隔符号”（无需分隔）→直接下一步→列数据格式选择“日期”（对应原格式，如“YMD”）→完成，系统自动统一日期格式；

若仍有异常值，用DATEVALUE(SUBSTITUTE(A2,/,-))函数转换文本为日期（A2为目标单元格），下拉填充后复制粘贴为数值格式。

销售额列标准化：

选中“销售额”列→查找替换（Ctrl+H）→查找内容输入“,”→替换为空→全部替换，去除千位分隔符；

选中列→数据选项卡→点击“定位条件”→选择“空值”→输入0→按Ctrl+Enter批量填充空值；

选中列→右键设置单元格格式为“数值”（保留0位小数），确保可参与计算。

二、函数应用（30分）

题目2：

基于上述销售数据表，需完成以下计算（写出具体函数公式及逻辑）：

计算“华东地区2023年10月家电类产品的总销售额”；

计算“每个地区的平均单笔销售额（排除销售额≤100的订单）”；

找出“每个产品类别的销售额TOP3的客户ID”。

答案2：

假设数据范围：A列（日期）、B列（地区）、C列（产品类别）、D列（销售额）、E列（客户ID），数据行1为表头，数据行2-1000为明细。

总销售额计算（多条件求和）：

=SUMIFS(D2:D1000,B2:B1000,华东,C2:C1000,家电,A2:A1000,=DATE(2023,10,1),A2:A1000,=DATE(2023,10,31))

逻辑：用SUMIFS多条件筛选地区、产品类别、日期范围，求和对应销售额。

各地区平均单笔销售额（排除低额订单）：

=AVERAGEIFS(D2:D1000,B2:B1000,B2,D2:D1000,100)

逻辑：AVERAGEIFS按“地区匹配当前行地区”“销售额100”筛选，计算平均值；下拉填充可得到所有地区结果（需配合去除重复地区）。

各产品类别销售额TOP3客户ID（数组公式，需按Ctrl+Shift+Enter输入）：

=INDEX(E:E,MATCH(LARGE(IF(C:C=C2,D:D,),ROW(A1)),IF(C:C=C2,D:D,),0))

逻辑：

内层IF筛选当前产品类别的所有销售额，LARGE取出第N名（ROW(A1)对应第1名，下拉为第2、3名）；

MATCH找到该销售额对应的行号，INDEX提取客户ID；

需对产品类别去重后，在对应类别旁输入公式下拉3行。

三、数据清洗与透视表（30分）

题目3：

销售数据表中存在以下问题：①部分“地区”列有错别字（如“华动”“华南省”“华北区”，正确应为“华东”“华南”“华北”）；②存在重复订单（同一客户ID、同一日期、同一产品类别的记录视为重复）；③需按“季度”“地区”“产品类别”统计销售额、订单数、平均客单价。请分步说明处理流程。

答案3：

错别字修正：

方法1：选中“地区”列→数据选项卡→点击“数据验证”→设置“允许”为“序列”，来源输入“华东，华南，华北，西北，西南，东北”→点击错误值单元格，直接选择正确地区替换；

方法2：用查找替换批量修正（Ctrl+H）→查找“华动”替换为“华东”，查找“华南省”“华南区”统一替换为“华南”，同理修正其他地区。

去除重复订单：

选中数据区域（含表头）→数据选项卡→点击“删除重复项”→勾选“客户ID”“日期”“产品类别”三列→确定，系统自动删除完全重复的记录。

透视表统计：

选中清洗后的数据区域→插入选项卡→点击“数据透视表”→选择放置位置（新工作表/现有工作表）；

行字段：拖入“地区”“产品类别”；列字段：拖入“日期”（右键点击日期字段→分组→选择“季度”）；

值字段：

销售额：拖入“销售额”→右键值字段设置→选择“求和”；

订单数：拖入“客户ID”→右键值字段设置→选择“计数”；

平均