考察国内电商数据分析能力的试卷含Excel数据处理题.docxVIP

第PAGE页共NUMPAGES页

考察国内电商数据分析能力的试卷含Excel数据处理题

一、数据清洗与处理（共3题，每题10分，共30分）

背景：某电商平台2023年11月部分订单数据存储在Excel文件“电商订单数据.xlsx”中，包含“订单ID”、“用户ID”、“商品ID”、“订单金额”、“订单时间”、“支付方式”、“地区”等字段。数据存在缺失值、重复值和异常值，需进行处理。

题目1（10分）：

利用Excel功能，清洗以下数据：

1.删除重复的订单记录（“订单ID”重复视为重复）；

2.填补“订单金额”的缺失值（使用该列的平均值填充）；

3.筛选出“地区”为“广东”或“上海”的订单，并将筛选结果复制到新工作表“广东上海订单”；

4.在“订单时间”列添加“年”和“月”两个辅助列，格式分别为“YYYY”和“MM”。

题目2（10分）：

针对“支付方式”字段，进行以下处理：

1.统一支付方式名称：将“微信支付”、“微信”合并为“微信支付”，将“支付宝”、“支付宝支付”合并为“支付宝”；

2.统计各支付方式的订单数量，创建数据透视表，以“支付方式”为行标签，以“订单数量”为值；

3.将数据透视表结果以表格形式输出，并添加“占比”计算列（占比=订单数量/总订单数）。

题目3（10分）：

处理异常值：

1.识别“订单金额”的异常值（使用箱线图法，剔除上下四分位数外1.5倍IQR之外的数据）；

2.对异常值进行标记（在原列右侧添加新列“金额标记”，异常值标记为“是”，正常值标记为“否”）；

3.计算处理后“订单金额”的平均值、中位数和标准差，并将结果填入单元格F1:F3。

二、数据透视表与图表制作（共2题，每题15分，共30分）

背景：继续使用“电商订单数据.xlsx”中的数据，需对用户行为和销售趋势进行分析。

题目4（15分）：

1.创建数据透视表，分析以下维度：

-行标签：用户ID（分组，每10个用户为一组）；

-列标签：支付方式；

-值：订单金额（求和）、订单数量（计数）；

2.将数据透视表制作成簇状柱形图，X轴为用户分组，Y轴为订单金额和订单数量，并设置图例为“支付方式”；

3.在图表中添加数据标签（仅显示订单金额），并调整标题为“用户分群支付行为分析”。

题目5（15分）：

1.按月统计各地区的订单数量和订单金额，创建数据透视表；

2.制作复合图表：主轴为“订单数量”的折线图，次轴为“订单金额”的柱形图，X轴为月份，图例包含“地区”和“指标”；

3.添加趋势线（订单金额），并标注R2值；

4.在图表中插入数据表，并调整坐标轴格式为“月/年”（如“11/2023”）。

三、高级Excel功能应用（共2题，每题20分，共40分）

背景：某电商平台2023年全年的用户注册和活跃数据存储在“用户行为数据.xlsx”中，包含“用户ID”、“注册时间”、“活跃天数”、“地区”、“设备类型”等字段。

题目6（20分）：

1.使用VLOOKUP函数，根据“用户ID”在“电商订单数据.xlsx”中匹配用户的订单金额，并在“用户行为数据”中新增“总订单金额”列；

2.使用IF函数，根据“总订单金额”计算用户等级：

-10000元：VIP

-5000-10000元：高级用户

-5000元：普通用户；

3.使用SUMIFS函数，统计“地区”为“四川”且“设备类型”为“手机”的VIP用户总订单金额；

4.使用高级筛选，将VIP用户数据复制到新工作表“VIP用户分析”。

题目7（20分）：

1.使用PowerQuery进行数据整合：

-合并“用户行为数据”和“电商订单数据”，匹配字段为“用户ID”；

-删除重复列（如“注册时间”和“订单时间”）；

-添加计算列：“用户活跃率”=活跃天数/30（假设每月30天）；

2.使用PowerPivot建立数据模型，创建关系：

-用户ID（文本）与用户ID（数字）的关系（需转换数据类型）；

-地区与订单金额的关系；

3.使用DAX公式计算“各地区用户平均订单金额”，并在数据透视表中验证结果；

4.保存数据模型为“电商分析模型.xlsx”。

答案与解析

一、数据清洗与处理

题目1（10分）：

1.删除重复值：选中“订单ID”列，点击“数据”→“删除重复项”；

2.填充缺失值：选中“订单金额”列，右键“设置单元格格式”→“数字”→“自定义”，输入`0`;点击“数据”→“查找与替换”，输入`=AVERAGE(C2:C1000)`（假设数据范围），按Ctrl+Enter；

3.筛选并复制：点击“数据”→“高级”，选择“将筛选结果复制到其他位置”，目标为“广东上海订单”工作表；

4.添加辅助列：在“订单时间”右侧插入两列