【干货】Excel数据分析从清洗到可视化5步全攻略

作者：李诗怡，CDA二级持证人，大数据工程技术专业大三在读

很多人的Excel水平，还停留在会求和、会画柱状图的阶段。遇到脏数据就手动删，遇到复杂匹配就VLOOKUP，遇到每周重复的报表就每天复制粘贴。其实不用VBA，也不用Python，纯Excel就能完成从脏数据到动态仪表盘的完整流程。

一、数据清洗

真实工作中，80%的时间花在清洗数据上。下面这三个功能，能帮你把清洗效率提升不少。

（一）分列

场景：从系统导出的数据，往往是2025-05-11 14:30:00这样日期和时间混在一起，或者张三-销售部-北京这种用分隔符连起来的字段。

操作：选中列 → 数据 → 分列 → 按分隔符或固定宽度 → 按提示完成。

两个常用例子：

• ●日期与时间分离：用空格作为分隔符，一次拆出日期和时间两列。
• ●从身份证号里提取出生日期：用固定宽度分出中间8位数字，再转成日期格式。

（二）快速填充

场景：从张明 13812345678中只提取手机号，或者从邮箱地址里提取用户名。

操作：在第一行手动写一个你想要的结果 → 选中下一个单元格 → 按 Ctrl+E。Excel会自动模仿你的规律，填充整列。

经典案例：

• ●从北京市朝阳区XX路12号提取北京两个字。
• ●把20250101批量转成2025-01-01。
• ●合并姓名和职位：张三+经理 →张经理。

（三）删除重复项 与 标记重复值

删除重复项：数据 → 删除重复项。

标记重复：选中区域 → 条件格式 → 突出显示单元格规则 → 重复值 → 标红。

进阶技巧：

• ●如果希望保留第一次出现的行，删除重复前先按时间排序。
• ●可以只检查某几列是否重复。

清洗检查清单：

• ●统一日期格式
• ●统一文本大小写，用 UPPER / LOWER
• ●去除前后空格，用 =TRIM(A1)
• ●处理合并单元格，取消合并 → Ctrl+Enter 批量填充

二、核心函数应用

很多人还在用VLOOKUP、SUMIF。下面这三个函数更直观、更强大，学会了它们，你的工作效率会有明显提升。

（一）XLOOKUP

语法：=XLOOKUP(查找值, 查找列, 返回列, [未找到时显示], [匹配模式])

为什么比VLOOKUP好用呢？因为它默认精确匹配，不用像VLOOKUP那样总惦记着是否要近似匹配，查找列可以在返回列的右边这是VLOOKUP做不到的，也没找到时不会显示烦人的 #N/A，可以自定义显示文字。

举例：

（二）SUMIFS

语法：=SUMIFS(求和列, 条件列1, 条件1, 条件列2, 条件2, …)

常见场景：

求东北区且支票的销售额：

=

SUMIFS(销售额列, 区域列, "东北", 月份列, "支票")

注意：条件列和求和列的行数必须一致。

（三）FILTER

动态返回所有满足条件的行

语法：=FILTER(数据区域, 条件数组, [无结果时显示])

示例：

进阶组合：

• ●=SORT(FILTER(…), 3, -1) 先筛选，再按第3列降序排序。
• ●=UNIQUE(FILTER(…)) 筛选后去重。

小提醒：如果你还在用Excel 2019或更老版本，FILTER可能不可用。可以考虑升级到Office 365，或者改用 INDEX+SMALL+IF 数组公式。

三、数据透视表高阶玩法

数据透视表不只是拖拽求和。

（一）计算字段

场景：需要计算价格（销售额/销量），但原数据里没有这个列。

操作：点击透视表 → 数据透视表分析 → 字段、项目和集 → 计算字段 → 名称填价格，公式 = 销售额 / 销量。

注意：计算字段是基于汇总后的数据再运算，不是先逐行算再汇总。比如平均单价应该是总销售额/总销量，而不是单价取平均。这点和你的预期可能不一样，建议算完核对一下。

（二）值显示方式

可以瞬间算出占比、排名、同比

在值字段上右键 → 值显示设置，有很多有用的选项：

• ●行汇总百分比：每行占该行总计的比例，适合看各区域内部品类结构。
• ●父行汇总百分比：有多层行标签时，子级占父级的比例。
• ●差异：直接算出本期-上期或环比差额。
• ●升序/降序排列：不用手动拖拽，透视表可以直接按值排序。

经典应用：把销售额再拖一次到值区域 → 右键设为父行汇总百分比 → 立刻得到各品类占本区域的份额。

（三）切片器 + 日程表

右键点击透视表 → 插入切片器，选你想筛选的字段，比如城市、品类 → 瞬间变成按钮式筛选。

日程表是专门用于日期的切片器，可以按年/季/月滑动选择。

（四）分组

• ●日期分组：右键日期字段 → 组合 → 按年-季度-月分组。
• ●数值分组：右键年龄字段 → 组合 → 设置步长。
• ●文本分组：选中北京、上海、广州 → 右键 → 组合 → 命名为一线城市。

（五）多重合并计算区域

场景：1月、2月、3月的销售表结构相同，但分别放在不同的Sheet里。

操作：按 Alt + D + P调出老版透视表向导→ 选择多重合并计算数据区域 → 逐个添加三个Sheet的区域 → 完成。得到一张能同时汇总多个月份的总透视表。

四、Excel自动化方法

每天或每周都要出同一份报表？完全可以一键刷新，不用重复复制粘贴。

（一）用表格让公式和透视表自动扩展

操作：选中数据区域 → 按 Ctrl+T 转换为表格。

好处是新增行时，表格会自动扩展。所有引用这张表的公式、下拉列表、透视表也会自动包含新行。

公式里可以写 =[@销量]*[@单价]，很清楚。

添加切片器后，样式也更整齐。

（二）Power Query

场景：每周下载的原始Excel格式完全一样，比如第1列是姓名，第3列是手机号，前几行还有多余的标题。

操作：

• ●数据 → 获取数据 → 从文件 → 从Excel工作簿。
• ●进入Power Query编辑器 → 执行清洗。
• ●点击关闭并上载至 → 选择仅创建连接。
• ●下次有新文件时，在查询上右键 → 刷新，清洗好的结果就会自动更新。

把清洗步骤记录下来，以后只要刷新就行，和编程一样。

（三）数据验证 + 条件格式实现自动预警

场景：实际销量小于计划销量时自动标红。

自动标红：选中销量列 → 条件格式 → 新建规则 → 使用公式确定要设置格式的单元格→ 输入 =C6<D6→ 设置红色填充。

限制输入：数据 → 数据验证 → 允许自定义 → 公式 =B2<=C2 → 超出时禁止输入并弹窗提示。

这样其他人填表时会被强制规范，你的自动化报表也就不会因为错误数据而出错了。

五、动态仪表盘制作

最后一步，把前面所有内容整合成一个可交互、可刷新、可分享的数据仪表盘。

核心思路：保持连接

原始数据 → Power Query清洗 / 表格 → 数据透视表 → 图表 → 切片器同时控制透视表和图表

（一）Step 1：准备数据源 + 透视表

基于表格创建数据透视表。

• ●行：日期或产品类别
• ●值：销售额、销量、利润等
• ●列：地区或渠道

（二）Step 2：插入图表并简单美化

• ●销售趋势 → 折线图
• ●品类占比 → 饼图或条形图
• ●区域分布 → 地图或横向条形图

（三）Step 3：用切片器联动所有图表

选中图表 → 右键数据透视图 → 在数据透视图分析里插入切片器，比如年份。

右键该切片器 → 报表连接 → 把你想联动的其他透视表都勾上。

这样点击切片器时，所有图表会同时刷新。

高级技巧：

• ●插入多个切片器，并排排列，做出一个筛选面板。
• ●关闭切片器标题，右键 → 大小和属性，让界面更干净。
• ●把切片器和图表放在同一个工作表，隐藏网格线，视图 → 取消网格线，看起来就像专业的管理驾驶舱。

（四）Step 4：让仪表盘每天自动更新

• ●如果原始数据是导出的CSV：每天替换文件 → 刷新Power Query → 刷新透视表 → 仪表盘数字自动更新。
• ●如果原始数据是手动录入的表单：在表格末行下方继续粘贴 → 刷新透视表即可。
• ●如果只想给老板看结果，可以把做好的仪表盘另存为模板（.xltx），甚至保护工作表，隐藏公式区域，只留下图表和切片器。

六、总结

这篇文章的五个板块，其实是一个完整的闭环：

脏数据 → 清洗 → 函数辅助计算 → 透视表聚合 → 自动化刷新 → 动态仪表盘

你可以根据自己的当前水平，只练习其中某一个板块。但记住几个小习惯：

多用快捷键；多关注表格和Power Query这两个被很多人低估的功能；不要只用鼠标点点点，试着把常做的操作记下来，逐步变成一键刷新。

职场内卷下，不懂数据分析的你，是不是总在汇报时拿不出有力数据、晋升时被卡在“数据能力”关？数据时代，CDA数据分析师证书早已成为职场刚需，不管是零基础转型，还是在职提升，都是最稳妥的突围路径。扫码CDA认证小程序，开启学习之旅。

往期干货文章：

~~~~龙虾+数据分析技能~~~~~