当业务方甩给你一份几万行的订单表开口就问“本月各区域毛利为什么跌了”的时候你会发现SQL 还没学透、Python 还在入门、报表工具刚装好。但桌面上那个从大学用到现在的 Excel反而成了最快能交付答案的工具。这不是 Excel 不行而是多数人对它的认知还停留在“电子记账本”阶段。实际上Excel 函数、数据透视表、BI 可视化三者组合起来就是一套完整的数据分析流水线函数负责清洗和计算透视表负责聚合与探查BI 工具负责呈现和汇报。本文围绕这套组合拳整理一份 30 天的零基础实战路径覆盖核心函数、透视表实操、可视化报表设计以及偏业务向的数据分析案例所有示例均可直接照做。如果你是运营、产品、财务、销售等岗位想系统补齐数据分析能力或者你是刚转行数据岗的新人想先从最通用的工具建立分析手感这篇文章都很适合。学完后你能独立完成“数据获取 → 清洗 → 计算 → 透视 → 可视化 → 输出结论”的完整闭环。1. 数据分析三件套Excel、透视表、BI 到底各管什么1.1 一套完整数据分析流程需要什么先看一个最常见的业务问题电商运营要分析“上个月各品类在不同渠道的退货率变化”。要回答这个问题分析链路大致是这样先把订单明细、退货表、渠道表合并成一张宽表这需要 Excel 函数或 Power Query 做数据清洗。然后按品类、渠道分组计算退货率这需要数据透视表。最后把退货率趋势、品类排名做成图表放进日报或汇报 PPT 里这需要 BI 可视化或 Excel 图表。可见Excel 函数、数据透视表、BI 可视化不是三个独立软件而是同一条流水线上的三道工序。1.2 Excel 函数数据清洗与计算的“手术刀”Excel 函数擅长处理表格内的结构化数据尤其是中小数据量几万行到几十万行。它的强项在于字段拆分、合并、格式转换文本函数、日期函数。条件统计与多条件求和SUMIFS、COUNTIFS。跨表匹配数据VLOOKUP、XLOOKUP、INDEXMATCH。逻辑判断与异常标记IF、IFS、AND、OR。简言之函数解决的是“怎么把原始数据变成能分析的数据”。1.3 数据透视表拖拽即可完成的聚合分析透视表的核心价值是“多维度的即时聚合”。它不需要写任何公式只需把字段拖到行、列、值、筛选四个区域就能在十几秒内完成类似 GROUP BY 的汇总操作。对于业务人员来说透视表是体验“数据分析思维”的最佳入口因为它强迫你思考维度是什么、度量是什么、筛选条件是什么。1.4 BI 可视化把数据变成决策依据BI 工具以 Power BI、Tableau 为代表解决的是规模化与动态化的问题。它比 Excel 图表更适合处理大数据量、多表关联和实时刷新。但要注意BI 很依赖底层数据模型而 Excel 透视表往往是建立 BI 数据思维之前的“学前班”。本文 BI 部分会以 Power BI Desktop 为例讲解通用流程因为它的下载、学习和社区资料都相对友好且与 Excel 同属微软生态入门曲线最平滑。这里有必要区分一个概念Excel 是分析工具BI 是展示和分发工具而数据透视表是两者之间的连接器。你完全可以先用 Excel 清洗数据再导出给 BI 做可视化也可以直接在 Power BI 里完成建模和可视化这正是偏业务数据分析岗位的日常状态。2. 30 小时学习路线总览从函数到业务实战怎么排标题里的“30 小时”听起来很紧实际上按每天 1~2 小时计算刚好是 3~4 周的学习周期。这里给出一份可执行的时间分配表学习阶段建议时长核心内容阶段产出阶段一Excel 函数基础6 小时逻辑判断、查找引用、统计求和、文本日期掌握 15 个高频函数能独立完成数据清洗阶段二数据透视表6 小时透视表布局、值汇总方式、切片器、分组能完成多维度聚合分析阶段三BI 可视化6 小时Power BI 或 Tableau 的基础操作、图表选择、仪表板设计能制作一份动态可视化报表阶段四业务分析实战8 小时电商/零售/财务案例分析、指标体系设计能独立输出一份完整分析报告阶段五复盘与面试准备4 小时作品集整理、分析思路总结、常见面试题沉淀个人数据分析作品集这个顺序不是随便排的它的内在逻辑是先有数据加工能力再有分析思维最后才有展示能力。很多新手一上来就学 Power BI结果数据清洗不过关做出来的图表只能叫“截图”不能叫“分析”。反过来如果函数和透视表熟练了BI 的学习成本会大幅降低因为 Power BI 里的 DAX 和 Excel 函数本身就有很多相似之处。3. 环境准备与版本说明3.1 Excel 版本选择Excel 函数和透视表的功能在 Office 2016、Office 2019、Microsoft 365 之间略有差异。例如 XLOOKUP 函数是 Microsoft 365 和 Office 2021 才内置的旧版本可能不支持。本文示例尽量兼容常见版本部分函数会给出替代方案。如果你用的是 WPS大部分函数和透视表操作也能对应上但需要留意个别菜单位置不同这是正常现象。建议有条件的情况下优先使用 Microsoft Excel方便与 Power BI 联动。3.2 BI 工具版本说明Power BI Desktop 可以免费下载微软官方持续更新功能以实际安装版本为准。本文演示的是通用操作流程不在某个具体版本细节上展开。主题是“BI 可视化思路 操作步骤”你只要跟着思路在自己的版本里找对应按钮即可。3.3 准备一份练手数据这套课程不需要乱七八糟的付费数据源一个最经典的场景就是“模拟电商订单表”。建议你自己建一个 Excel 文件包含下面这些字段订单编号, 订单日期, 客户ID, 客户城市, 商品类目, 商品单价, 销售数量, 销售额, 成本, 渠道字段虽然简单但能覆盖函数、透视表、图表三个环节。后续所有案例都基于这份数据展开。4. Excel 函数核心拆解工作里真正高频的就这五类很多人在“背函数”上浪费了太多时间。实际上企业日常分析中用到的函数不超过 30 个其中八成以上集中在五类。下面分类梳理每个函数都给可复制的公式和说明。4.1 逻辑判断类IF、IFS、AND、ORIF 是 Excel 最核心的逻辑函数格式是IF(条件, 条件为真时的返回值, 条件为假时的返回值)一个常见的业务场景根据销售额判断订单是否达标。IF(F210000, 达标, 未达标)如果有多层级判断旧版只能嵌套多个 IFIF(F230000, A级, IF(F210000, B级, C级))如果使用 Excel 2019 以上版本可以直接用 IFSIFS(F230000, A级, F210000, B级, TRUE, C级)这里需要注意IFS 的最后一个条件要写成 TRUE表示“以上都不满足时兜底”。新手最容易漏掉这一点。4.2 查找引用类VLOOKUP、INDEXMATCHVLOOKUP 是跨表匹配使用率最高的函数VLOOKUP(查找值, 查找区域, 返回第几列, 精确匹配还是近似匹配)比如要根据客户ID从客户表里匹配客户城市VLOOKUP(B2, 客户表!A:B, 2, FALSE)关键误区在于第四参数。平时业务分析场景几乎都要用 FALSE精确匹配一旦写成 TRUE 或省略容易出现“看起来差不多但其实是错的”结果。另外VLOOKUP 只能从左往右查也就是查找值必须在区域的第一列。如果需要从右往左查或者查找列不固定推荐用 INDEXMATCH 组合INDEX(返回区域, MATCH(查找值, 查找列, 0))例如按订单编号查找销售额INDEX(销售数据!F:F, MATCH(A2, 销售数据!A:A, 0))如果你是 Microsoft 365 用户XLOOKUP 是更简洁的升级方案XLOOKUP(A2, 销售数据!A:A, 销售数据!F:F)4.3 统计求和类SUMIF、SUMIFS、COUNTIFS求和类函数是 Excel 数据分析中使用频率最高的一块因为在业务分析中“按条件汇总”是刚需。SUMIF 是单条件求和SUMIF(条件区域, 条件, 求和区域)例如求“华为手机”的总销售额SUMIF(C:C, 手机, G:G)SUMIFS 是多条件求和要注意它的参数顺序与 SUMIF 不一样求和区域在第一位SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2)一个电商运营常用的例子统计“渠道为天猫、类目为女装”的总销售额SUMIFS(G:G, J:J, 天猫, C:C, 女装)COUNTIFS 用于多条件计数比如统计“上海地区销售额超过5000元的订单数”COUNTIFS(D:D, 上海, G:G, 5000)这类函数是数据透视表之外最实用的聚合手段尤其是做临时性校验时比透视表更灵活。4.4 文本函数类LEFT、RIGHT、MID、TEXT文本清洗是数据分析里非常耗时的一步。订单号里提取地区编码、日期里提取月份、把数字格式化为文本都需要文本函数。LEFT 从左边截取LEFT(A2, 3)MID 从中间截取MID(A2, 4, 2)TEXT 用于把数字或日期转换成指定格式例如把日期转换为“2025-06”的月份格式TEXT(B2, YYYY-MM)这里要注意TEXT 函数的结果是文本类型不能再直接参与求和需要转换或用 DATEVALUE 等函数配合。4.5 日期函数类YEAR、MONTH、DATE、DATEDIF日期类函数主要用于时间维度分析例如按月份、季度、年度汇总。YEAR(B2) MONTH(B2)如果要计算两个日期之间的间隔天数、月数可以用 DATEDIF这是 Excel 隐藏函数不会出现在公式提示里但能使用DATEDIF(B2, TODAY(), M)上述公式计算“B2 日期距离今天相差多少个月”注意 DATEDIF 的第三参数有 Y年、M月、D日等。这个函数在计算客户生命周期、账龄分析时很常用。5. 数据透视表实战拖拽间完成多维度分析函数能帮我们处理数据但真正做探索性分析时透视表的效率是函数无法比的。它不需要写公式只要拖拽字段就能快速回答“不同类目哪个卖得好”“哪个城市的退货率最高”这类问题。5.1 快速创建第一张数据透视表操作步骤将光标放在订单明细表任意单元格。点击“插入 → 数据透视表”。确认选择区域无误后选择放入新建工作表。在右侧字段列表中勾选“商品类目”字段并将其拖动到“行”区域。勾选“销售额”字段默认会显示在“值”区域汇总方式默认为“求和”。这时你会得到一张按类目汇总销售额的表。不要小看这一步它对应到 SQL 里就是SELECT 商品类目, SUM(销售额) FROM 订单表 GROUP BY 商品类目;透视表的本质就是可视化的 GROUP BY。5.2 值字段设置求和之外还有这些用途很多人只知道“值”默认求和实际上值字段的汇总方式还支持计数、平均值、最大值、最小值、乘积、标准差等。右键点击值区域任意数字选择“值字段设置”即可切换汇总方式。这里有一个重要技巧当需要计算“占比”时可以在值字段设置中选择“值显示方式 → 总计的百分比”。例如求每个类目占总销售额的比例不需要写任何公式Excel 会自动计算。这种“不写公式完成聚合分析”的能力是业务人员接触数据分析思维的最短路径。尤其是做经营分析时频率最高的就是这种“维度 指标”的组合查询。5.3 透视表筛选查看指定人员的月度数据在最新网络热词里有一个高频搜索是“透视表筛选选个人的月度数据”。这个需求在实际工作里非常典型销售主管要分别查看每个销售员的月业绩。实现方式有两种先看第一种将“销售员”字段拖入“筛选”区域然后在下拉框里选择指定人员。这种做法的缺点是一次性只能看一个人汇报时切换比较麻烦。更推荐的做法是将“销售员”字段拖入“行”区域将“订单日期”字段拖入“行”区域且位于“销售员”下方再将“订单日期”字段的汇总方式改为“按月分组”右键日期字段 → 组合 → 按月完成后透视表会按“销售员 → 月份”的层级展示展开加号即可查看每个人每个月的业绩。新建的透视表默认会启用日期自动分组在旧版本里可能需要手动右键组合。这种方法更符合公司周报、月报的上报场景。5.4 切片器让透视表变成动态分析面板切片器是透视表最吸引人的功能之一。它相当于可视化筛选器点击按钮就能切换图表和表格数据。操作步骤选中透视表任意单元格。点击“插入 → 切片器”。勾选需要筛选的字段例如“渠道”“城市”。把生成的切片器按钮拖动到合适位置。多个透视表可以共享同一个切片器这样就能实现“一个筛选器联动多张分析表”的动态效果。很多中小公司所谓的数据看板其实用 Excel 切片器就能做出来不一定要上重型 BI。6. BI 可视化实战从静态表格到动态仪表板当数据量变大、分析维度变多时Excel 会开始卡顿透视表也会显得拥挤。这时候就需要 BI 工具出场。Power BI Desktop 是最适合从 Excel 过渡过来的工具下面以它为例讲解 BI 可视化的完整流程。6.1 数据导入与清洗Excel 到 Power BI 的第一步打开 Power BI Desktop点击“获取数据 → Excel”选择之前准备好的订单表文件。Power Query数据查询编辑器会自动打开这里你可以继续做清洗比如删除重复值、填充空值、修改列类型。从 Excel 到 Power BI很多人会忽略一个动作标记日期列的“日期”类型。日期类型不正确的表在后续时间智能计算中会出现各种莫名其妙的问题这是一个非常高频的坑。6.2 建立数据模型理解维度表和事实表进入 Power BI 主界面后最容易出错的操作是把 Excel 里所有表一股脑拖入画布创建视觉对象却不建立关系。正规做法是区分两类表事实表记录业务事件如订单表包含订单编号、销售额、成本、销量等。维度表描述业务对象如客户表、商品表、日期表。在“模型”视图中把维度表的主键拖拽到事实表对应字段上建立一对多关系。常见的关系如“订单表[客户ID]”与“客户表[客户ID]”“订单表[订单日期]”与“日期表[日期]”。这一步是 Excel 透视表和 BI 之间最大的思维差异Excel 倾向把数据做成一张大宽表而 BI 强调按星型模型拆分表。掌握这一点是理解数据建模的关键一步。6.3 创建度量值使用 DAX 计算关键指标在 Power BI 中推荐使用“度量值”进行动态计算。新建度量值的方法是点击“建模 → 新建度量值”然后在公式栏输入 DAX 表达式。最基本的销售额度量值销售额总计 SUM(订单表[销售额])成本度量值成本总计 SUM(订单表[成本])毛利率度量值毛利率 DIVIDE(SUM(订单表[销售额]) - SUM(订单表[成本]), SUM(订单表[销售额]))DAX 的函数风格与 Excel 非常接近尤其上面这个 DIVIDE 函数本质上就是 Excel 的 IFERROR 安全除法。这也是为什么我强调先学好 Excel 函数再学 BI 会更轻松。6.4 常用可视化图表选择10 种图表怎么选做数据分析可视化时最容易犯的错误是“图表选择不匹配分析目标”。下面这个表格总结了常见分析诉求对应的图表类型分析诉求推荐图表适用场景展示趋势折线图时间序列分析如月销售额变化对比排名柱状图/条形图类目对比、区域对比占比构成饼图/环形图渠道占比、类目占比多变量分布散点图价格与销量的关系地理分布地图城市维度分析完成进度堆积柱状图目标达成情况关键指标突出卡片图总销售额、转化率矩阵明细表/矩阵多维度交叉汇总漏斗分析漏斗图转化率分析异常监控条件格式表阈值预警值得强调的一点是饼图能用但要慎用。当分类超过 5 个时饼图的可读性会变得很差不如改成横向条形图。6.5 制作动态仪表板把所有业务指标汇总到一个页面上就构成了仪表板Dashboard。Power BI 的操作流程是新建一个空白页面。放置顶部卡片图显示总销售额、总订单量、客单价。中间区域放“月份 销售额”折线图。下方并列放“类目销售额”柱状图和“渠道占比”环形图。右侧添加切片器字段可选择“年份”和“客户城市”。调整主题颜色统一字体保持干净整洁。这里有一个实用建议BI 仪表板不是图表的堆砌每个视觉对象都应该能回答一个明确的业务问题。如果你不确定某张图为什么放在页面上建议先删掉它。7. 偏业务数据分析实战一份电商经营周报的完整产出下面把函数、透视表、BI 组合到一个完整业务场景中。案例背景设定为一家小型电商公司需要运营每周输出一份经营周报核心指标包括销售额、订单量、客单价、退货率、类目排名。7.1 业务指标拆解与口径定义在做任何分析之前先定义清楚指标的口径销售额 各订单金额求和订单量 订单编号计数注意同一订单多个商品不能重复计数客单价 销售额 / 订单量退货率 退货订单量 / 总订单量类目排名 按类目销售额降序排名这个步骤看起来“不技术”但却是业务分析的核心。很多分析结论不可信不是因为数据算错了而是指标口径没有对齐。比如“订单量”是按订单编号数还是按商品行数会对结果影响很大。7.2 在 Excel 中完成核心计算先创建一个 Sheet 作为“计算底稿”。用 SUMIFS 计算不同渠道的销售额。假设原始表包含 A~J 列其中 J 列是渠道SUMIFS(G:G, J:J, 天猫)计算总订单量时不能用 COUNTA 对订单编号列直接计数因为如果展开后有多行数据会造成重复。正确做法是使用透视表精准去重计数。更稳妥的计算方式是借助透视表把“订单编号”拖入“值”区域右键修改值字段设置将“值汇总方式”改为“非重复计数”。注意非重复计数功能在 Excel 2013 以上版本中内置支持旧版本需要借助 SUMPRODUCT 公式。一段经典的 SUMPRODUCT 去重计数公式是SUMPRODUCT((A2:A1000)/COUNTIF(A2:A1000, A2:A1000))不过这种公式在数据量大时性能较差建议优先用透视表的非重复计数功能。7.3 用数据透视表完成多维度探查接下来连续创建多张透视表分别回答以下问题按类目汇总销售额确定 Top 3 类目。按渠道和类目交叉汇总找出渠道优势类目。按月份和类目汇总观察趋势变化。按城市汇总销售额定位核心市场。每张透视表生成后插入对应的图表并调整颜色和标题。到这一步你已经初步具备“分析视角”而不是停留在“我会用函数”的层面。7.4 在 Power BI 中排布完整仪表板将数据处理好的 Excel 文件导入 Power BI Desktop建立订单表与日期表的关系创建以下度量值销售额总计 SUM(订单表[销售额]) 订单量 COUNTROWS(订单表) 客单价 DIVIDE([销售额总计], [订单量]) 退货率 DIVIDE(CALCULATE([订单量], 订单表[是否退货] 是), [订单量])然后设计页面布局生成折线图、柱状图、环形图、卡片图。这样一个从 Excel 到 BI 的完整周报流程就闭环了。7.5 输出结论而不是只丢图表很多新人做完图表就以为任务完成。实际上业务方最关心的是“所以呢”也就是结论与行动建议。例如通过可视化发现华东地区销售额环比下降 12%进一步下钻后定位到原因是某头部商品缺货。这时你应该给出的结论是“缺货导致销售缺口建议补货并评估替代品曝光”而不是只说“华东下降了 12%”。这就是偏业务数据分析实战与纯技术操作的本质区别技术工具有限但业务解读能力才是分析师的护城河。8. 常见问题与排查思路学习这套流程中有几个高频问题几乎每个人都会遇到提前列出来遇到时可以直接对照排查。问题现象常见原因解决思路VLOOKUP 返回 #N/A查找值在查找区域不存在或存在空格、格式不一致检查数据前后是否有空格用 TRIM 清理确认查找区域首列为查找值SUMIFS 计算结果为 0条件区域与求和区域行数不一致或条件为文本但写成数字检查区域范围是否一致将条件用双引号包裹透视表新增数据后不更新透视表数据源范围固定没有扩展为新区域选择“更改数据源”或使用“表”功能让透视表自动扩展范围日期透视后自动按月分组但无法取消Excel 自动日期分组特性右键日期字段选择取消组合Power BI 显示不出视觉对象表之间没有建立关系或字段类型不匹配检查模型关系核对字段数据类型BI 中销售额是一个值而不是按维度展示忘记把维度字段拖入“轴”或“图例”确认视觉对象的字段配置完整分析报告被业务方质疑口径说明不清或图表误导性强在报告中增加指标口径说明和前提假设数据里有合并单元格导致分析异常合并单元格影响透视表引用和公式计算尽量使用一维表结构避免合并单元格必要时取消合并并填充遇到问题时最有效的排查办法是先定位数据源再看公式最后看展示层。按这个顺序排查能省下大半时间。9. 最佳实践与工程建议9.1 维护一维表结构Excel 分析中最重要的规范是“一维表原则”每一行是一条记录每一列是一个字段。横向合并单元格、多级表头、字段里夹带单位这些做法对人和软件都不友好。透视表、BI 工具、SQL 导入都极度依赖一维表结构。9.2 使用 Excel 表格功能代替普通区域选中数据区域后按 CtrlT可以把普通区域转换为“表格”。这样做的优势是公式自动扩展、透视表引用自动更新、筛选更方便。很多老手不推荐直接选整列引用公式因为会造成大量无效计算表格功能则能完美解决这个问题。9.3 指标口径要写清楚交付任何一份分析报告都要包含“指标定义”和“数据口径”部分。例如“销售额”是否含税、是否剔除退款、统计周期是自然月还是财月。口径一致的报告经得起追问口径模糊的报告即使图表再花哨也容易被推翻。9.4 数据权限与脱敏意识在使用业务数据或客户数据进行练习和分析时要注意最小权限原则和脱敏处理。练习用数据尽量使用模拟数据或对真实数据中的客户姓名、手机号、地址等敏感字段做去标识化。正式环境中操作前应先确认权限范围避免越权访问。9.5 分析输出优先给结论汇报时尽量遵循“结论先行”原则。先给核心结论和行动建议再放数据佐证和图表明细。业务方没有耐心在一堆图表里找重点你的价值恰恰在于把“数据洞察”翻译成“业务动作”。9.6 构建个人分析模板库每完成一个分析任务可以把透视表布局、Excel 模板、Power BI 主题保存成模板文件积累成自己的分析素材库。长期下来做同类分析的时间会大幅缩短模板化是提升分析效率最直接的方法之一。10. 总结与下一步学习方向到这一步你已经走完了从 Excel 函数到数据透视表再到 BI 可视化的完整路径并完成了一个偏业务向的电商经营周报案例。再往后冲刺有两条路可以选如果你还在业务岗优先修炼的是“分析思路”和“业务理解”可以继续学习 Excel 高级公式、Power Query 数据清洗、Power BI DAX 度量值同时尝试分析自己公司的真实业务问题。如果你想往专职数据分析师、商业分析岗发展下一步建议补充 SQL数据库查询几乎是数据分析岗位的必选项以及 Python 的 pandas 和 Matplotlib用于更灵活的统计分析和自动化报表。Excel、透视表、BI 解决的是“当下能干活”的问题SQL 和 Python 则决定“能走多远”的上限。最后给你三个实用建议不要追求背下所有函数重点掌握 SUMIFS、VLOOKUP、IF 这类高频函数遇到不会的随时用 Excel 自带搜索和备注功能。每学一个功能就结合自己的业务数据做一个小练习比如“分析上个月个人业绩完成情况”这样才算真正掌握。在学习到第四周时整理一份属于自己的分析作品集作为求职或内部汇报的材料它比你口头描述“我会 Excel”更有说服力。动手做一份自己的数据报表吧。数据不会直接告诉你答案但只要你开始分析它就会越来越清晰。
Excel函数与数据透视表、BI可视化：30天零基础数据分析实战路径
当业务方甩给你一份几万行的订单表开口就问“本月各区域毛利为什么跌了”的时候你会发现SQL 还没学透、Python 还在入门、报表工具刚装好。但桌面上那个从大学用到现在的 Excel反而成了最快能交付答案的工具。这不是 Excel 不行而是多数人对它的认知还停留在“电子记账本”阶段。实际上Excel 函数、数据透视表、BI 可视化三者组合起来就是一套完整的数据分析流水线函数负责清洗和计算透视表负责聚合与探查BI 工具负责呈现和汇报。本文围绕这套组合拳整理一份 30 天的零基础实战路径覆盖核心函数、透视表实操、可视化报表设计以及偏业务向的数据分析案例所有示例均可直接照做。如果你是运营、产品、财务、销售等岗位想系统补齐数据分析能力或者你是刚转行数据岗的新人想先从最通用的工具建立分析手感这篇文章都很适合。学完后你能独立完成“数据获取 → 清洗 → 计算 → 透视 → 可视化 → 输出结论”的完整闭环。1. 数据分析三件套Excel、透视表、BI 到底各管什么1.1 一套完整数据分析流程需要什么先看一个最常见的业务问题电商运营要分析“上个月各品类在不同渠道的退货率变化”。要回答这个问题分析链路大致是这样先把订单明细、退货表、渠道表合并成一张宽表这需要 Excel 函数或 Power Query 做数据清洗。然后按品类、渠道分组计算退货率这需要数据透视表。最后把退货率趋势、品类排名做成图表放进日报或汇报 PPT 里这需要 BI 可视化或 Excel 图表。可见Excel 函数、数据透视表、BI 可视化不是三个独立软件而是同一条流水线上的三道工序。1.2 Excel 函数数据清洗与计算的“手术刀”Excel 函数擅长处理表格内的结构化数据尤其是中小数据量几万行到几十万行。它的强项在于字段拆分、合并、格式转换文本函数、日期函数。条件统计与多条件求和SUMIFS、COUNTIFS。跨表匹配数据VLOOKUP、XLOOKUP、INDEXMATCH。逻辑判断与异常标记IF、IFS、AND、OR。简言之函数解决的是“怎么把原始数据变成能分析的数据”。1.3 数据透视表拖拽即可完成的聚合分析透视表的核心价值是“多维度的即时聚合”。它不需要写任何公式只需把字段拖到行、列、值、筛选四个区域就能在十几秒内完成类似 GROUP BY 的汇总操作。对于业务人员来说透视表是体验“数据分析思维”的最佳入口因为它强迫你思考维度是什么、度量是什么、筛选条件是什么。1.4 BI 可视化把数据变成决策依据BI 工具以 Power BI、Tableau 为代表解决的是规模化与动态化的问题。它比 Excel 图表更适合处理大数据量、多表关联和实时刷新。但要注意BI 很依赖底层数据模型而 Excel 透视表往往是建立 BI 数据思维之前的“学前班”。本文 BI 部分会以 Power BI Desktop 为例讲解通用流程因为它的下载、学习和社区资料都相对友好且与 Excel 同属微软生态入门曲线最平滑。这里有必要区分一个概念Excel 是分析工具BI 是展示和分发工具而数据透视表是两者之间的连接器。你完全可以先用 Excel 清洗数据再导出给 BI 做可视化也可以直接在 Power BI 里完成建模和可视化这正是偏业务数据分析岗位的日常状态。2. 30 小时学习路线总览从函数到业务实战怎么排标题里的“30 小时”听起来很紧实际上按每天 1~2 小时计算刚好是 3~4 周的学习周期。这里给出一份可执行的时间分配表学习阶段建议时长核心内容阶段产出阶段一Excel 函数基础6 小时逻辑判断、查找引用、统计求和、文本日期掌握 15 个高频函数能独立完成数据清洗阶段二数据透视表6 小时透视表布局、值汇总方式、切片器、分组能完成多维度聚合分析阶段三BI 可视化6 小时Power BI 或 Tableau 的基础操作、图表选择、仪表板设计能制作一份动态可视化报表阶段四业务分析实战8 小时电商/零售/财务案例分析、指标体系设计能独立输出一份完整分析报告阶段五复盘与面试准备4 小时作品集整理、分析思路总结、常见面试题沉淀个人数据分析作品集这个顺序不是随便排的它的内在逻辑是先有数据加工能力再有分析思维最后才有展示能力。很多新手一上来就学 Power BI结果数据清洗不过关做出来的图表只能叫“截图”不能叫“分析”。反过来如果函数和透视表熟练了BI 的学习成本会大幅降低因为 Power BI 里的 DAX 和 Excel 函数本身就有很多相似之处。3. 环境准备与版本说明3.1 Excel 版本选择Excel 函数和透视表的功能在 Office 2016、Office 2019、Microsoft 365 之间略有差异。例如 XLOOKUP 函数是 Microsoft 365 和 Office 2021 才内置的旧版本可能不支持。本文示例尽量兼容常见版本部分函数会给出替代方案。如果你用的是 WPS大部分函数和透视表操作也能对应上但需要留意个别菜单位置不同这是正常现象。建议有条件的情况下优先使用 Microsoft Excel方便与 Power BI 联动。3.2 BI 工具版本说明Power BI Desktop 可以免费下载微软官方持续更新功能以实际安装版本为准。本文演示的是通用操作流程不在某个具体版本细节上展开。主题是“BI 可视化思路 操作步骤”你只要跟着思路在自己的版本里找对应按钮即可。3.3 准备一份练手数据这套课程不需要乱七八糟的付费数据源一个最经典的场景就是“模拟电商订单表”。建议你自己建一个 Excel 文件包含下面这些字段订单编号, 订单日期, 客户ID, 客户城市, 商品类目, 商品单价, 销售数量, 销售额, 成本, 渠道字段虽然简单但能覆盖函数、透视表、图表三个环节。后续所有案例都基于这份数据展开。4. Excel 函数核心拆解工作里真正高频的就这五类很多人在“背函数”上浪费了太多时间。实际上企业日常分析中用到的函数不超过 30 个其中八成以上集中在五类。下面分类梳理每个函数都给可复制的公式和说明。4.1 逻辑判断类IF、IFS、AND、ORIF 是 Excel 最核心的逻辑函数格式是IF(条件, 条件为真时的返回值, 条件为假时的返回值)一个常见的业务场景根据销售额判断订单是否达标。IF(F210000, 达标, 未达标)如果有多层级判断旧版只能嵌套多个 IFIF(F230000, A级, IF(F210000, B级, C级))如果使用 Excel 2019 以上版本可以直接用 IFSIFS(F230000, A级, F210000, B级, TRUE, C级)这里需要注意IFS 的最后一个条件要写成 TRUE表示“以上都不满足时兜底”。新手最容易漏掉这一点。4.2 查找引用类VLOOKUP、INDEXMATCHVLOOKUP 是跨表匹配使用率最高的函数VLOOKUP(查找值, 查找区域, 返回第几列, 精确匹配还是近似匹配)比如要根据客户ID从客户表里匹配客户城市VLOOKUP(B2, 客户表!A:B, 2, FALSE)关键误区在于第四参数。平时业务分析场景几乎都要用 FALSE精确匹配一旦写成 TRUE 或省略容易出现“看起来差不多但其实是错的”结果。另外VLOOKUP 只能从左往右查也就是查找值必须在区域的第一列。如果需要从右往左查或者查找列不固定推荐用 INDEXMATCH 组合INDEX(返回区域, MATCH(查找值, 查找列, 0))例如按订单编号查找销售额INDEX(销售数据!F:F, MATCH(A2, 销售数据!A:A, 0))如果你是 Microsoft 365 用户XLOOKUP 是更简洁的升级方案XLOOKUP(A2, 销售数据!A:A, 销售数据!F:F)4.3 统计求和类SUMIF、SUMIFS、COUNTIFS求和类函数是 Excel 数据分析中使用频率最高的一块因为在业务分析中“按条件汇总”是刚需。SUMIF 是单条件求和SUMIF(条件区域, 条件, 求和区域)例如求“华为手机”的总销售额SUMIF(C:C, 手机, G:G)SUMIFS 是多条件求和要注意它的参数顺序与 SUMIF 不一样求和区域在第一位SUMIFS(求和区域, 条件区域1, 条件1, 条件区域2, 条件2)一个电商运营常用的例子统计“渠道为天猫、类目为女装”的总销售额SUMIFS(G:G, J:J, 天猫, C:C, 女装)COUNTIFS 用于多条件计数比如统计“上海地区销售额超过5000元的订单数”COUNTIFS(D:D, 上海, G:G, 5000)这类函数是数据透视表之外最实用的聚合手段尤其是做临时性校验时比透视表更灵活。4.4 文本函数类LEFT、RIGHT、MID、TEXT文本清洗是数据分析里非常耗时的一步。订单号里提取地区编码、日期里提取月份、把数字格式化为文本都需要文本函数。LEFT 从左边截取LEFT(A2, 3)MID 从中间截取MID(A2, 4, 2)TEXT 用于把数字或日期转换成指定格式例如把日期转换为“2025-06”的月份格式TEXT(B2, YYYY-MM)这里要注意TEXT 函数的结果是文本类型不能再直接参与求和需要转换或用 DATEVALUE 等函数配合。4.5 日期函数类YEAR、MONTH、DATE、DATEDIF日期类函数主要用于时间维度分析例如按月份、季度、年度汇总。YEAR(B2) MONTH(B2)如果要计算两个日期之间的间隔天数、月数可以用 DATEDIF这是 Excel 隐藏函数不会出现在公式提示里但能使用DATEDIF(B2, TODAY(), M)上述公式计算“B2 日期距离今天相差多少个月”注意 DATEDIF 的第三参数有 Y年、M月、D日等。这个函数在计算客户生命周期、账龄分析时很常用。5. 数据透视表实战拖拽间完成多维度分析函数能帮我们处理数据但真正做探索性分析时透视表的效率是函数无法比的。它不需要写公式只要拖拽字段就能快速回答“不同类目哪个卖得好”“哪个城市的退货率最高”这类问题。5.1 快速创建第一张数据透视表操作步骤将光标放在订单明细表任意单元格。点击“插入 → 数据透视表”。确认选择区域无误后选择放入新建工作表。在右侧字段列表中勾选“商品类目”字段并将其拖动到“行”区域。勾选“销售额”字段默认会显示在“值”区域汇总方式默认为“求和”。这时你会得到一张按类目汇总销售额的表。不要小看这一步它对应到 SQL 里就是SELECT 商品类目, SUM(销售额) FROM 订单表 GROUP BY 商品类目;透视表的本质就是可视化的 GROUP BY。5.2 值字段设置求和之外还有这些用途很多人只知道“值”默认求和实际上值字段的汇总方式还支持计数、平均值、最大值、最小值、乘积、标准差等。右键点击值区域任意数字选择“值字段设置”即可切换汇总方式。这里有一个重要技巧当需要计算“占比”时可以在值字段设置中选择“值显示方式 → 总计的百分比”。例如求每个类目占总销售额的比例不需要写任何公式Excel 会自动计算。这种“不写公式完成聚合分析”的能力是业务人员接触数据分析思维的最短路径。尤其是做经营分析时频率最高的就是这种“维度 指标”的组合查询。5.3 透视表筛选查看指定人员的月度数据在最新网络热词里有一个高频搜索是“透视表筛选选个人的月度数据”。这个需求在实际工作里非常典型销售主管要分别查看每个销售员的月业绩。实现方式有两种先看第一种将“销售员”字段拖入“筛选”区域然后在下拉框里选择指定人员。这种做法的缺点是一次性只能看一个人汇报时切换比较麻烦。更推荐的做法是将“销售员”字段拖入“行”区域将“订单日期”字段拖入“行”区域且位于“销售员”下方再将“订单日期”字段的汇总方式改为“按月分组”右键日期字段 → 组合 → 按月完成后透视表会按“销售员 → 月份”的层级展示展开加号即可查看每个人每个月的业绩。新建的透视表默认会启用日期自动分组在旧版本里可能需要手动右键组合。这种方法更符合公司周报、月报的上报场景。5.4 切片器让透视表变成动态分析面板切片器是透视表最吸引人的功能之一。它相当于可视化筛选器点击按钮就能切换图表和表格数据。操作步骤选中透视表任意单元格。点击“插入 → 切片器”。勾选需要筛选的字段例如“渠道”“城市”。把生成的切片器按钮拖动到合适位置。多个透视表可以共享同一个切片器这样就能实现“一个筛选器联动多张分析表”的动态效果。很多中小公司所谓的数据看板其实用 Excel 切片器就能做出来不一定要上重型 BI。6. BI 可视化实战从静态表格到动态仪表板当数据量变大、分析维度变多时Excel 会开始卡顿透视表也会显得拥挤。这时候就需要 BI 工具出场。Power BI Desktop 是最适合从 Excel 过渡过来的工具下面以它为例讲解 BI 可视化的完整流程。6.1 数据导入与清洗Excel 到 Power BI 的第一步打开 Power BI Desktop点击“获取数据 → Excel”选择之前准备好的订单表文件。Power Query数据查询编辑器会自动打开这里你可以继续做清洗比如删除重复值、填充空值、修改列类型。从 Excel 到 Power BI很多人会忽略一个动作标记日期列的“日期”类型。日期类型不正确的表在后续时间智能计算中会出现各种莫名其妙的问题这是一个非常高频的坑。6.2 建立数据模型理解维度表和事实表进入 Power BI 主界面后最容易出错的操作是把 Excel 里所有表一股脑拖入画布创建视觉对象却不建立关系。正规做法是区分两类表事实表记录业务事件如订单表包含订单编号、销售额、成本、销量等。维度表描述业务对象如客户表、商品表、日期表。在“模型”视图中把维度表的主键拖拽到事实表对应字段上建立一对多关系。常见的关系如“订单表[客户ID]”与“客户表[客户ID]”“订单表[订单日期]”与“日期表[日期]”。这一步是 Excel 透视表和 BI 之间最大的思维差异Excel 倾向把数据做成一张大宽表而 BI 强调按星型模型拆分表。掌握这一点是理解数据建模的关键一步。6.3 创建度量值使用 DAX 计算关键指标在 Power BI 中推荐使用“度量值”进行动态计算。新建度量值的方法是点击“建模 → 新建度量值”然后在公式栏输入 DAX 表达式。最基本的销售额度量值销售额总计 SUM(订单表[销售额])成本度量值成本总计 SUM(订单表[成本])毛利率度量值毛利率 DIVIDE(SUM(订单表[销售额]) - SUM(订单表[成本]), SUM(订单表[销售额]))DAX 的函数风格与 Excel 非常接近尤其上面这个 DIVIDE 函数本质上就是 Excel 的 IFERROR 安全除法。这也是为什么我强调先学好 Excel 函数再学 BI 会更轻松。6.4 常用可视化图表选择10 种图表怎么选做数据分析可视化时最容易犯的错误是“图表选择不匹配分析目标”。下面这个表格总结了常见分析诉求对应的图表类型分析诉求推荐图表适用场景展示趋势折线图时间序列分析如月销售额变化对比排名柱状图/条形图类目对比、区域对比占比构成饼图/环形图渠道占比、类目占比多变量分布散点图价格与销量的关系地理分布地图城市维度分析完成进度堆积柱状图目标达成情况关键指标突出卡片图总销售额、转化率矩阵明细表/矩阵多维度交叉汇总漏斗分析漏斗图转化率分析异常监控条件格式表阈值预警值得强调的一点是饼图能用但要慎用。当分类超过 5 个时饼图的可读性会变得很差不如改成横向条形图。6.5 制作动态仪表板把所有业务指标汇总到一个页面上就构成了仪表板Dashboard。Power BI 的操作流程是新建一个空白页面。放置顶部卡片图显示总销售额、总订单量、客单价。中间区域放“月份 销售额”折线图。下方并列放“类目销售额”柱状图和“渠道占比”环形图。右侧添加切片器字段可选择“年份”和“客户城市”。调整主题颜色统一字体保持干净整洁。这里有一个实用建议BI 仪表板不是图表的堆砌每个视觉对象都应该能回答一个明确的业务问题。如果你不确定某张图为什么放在页面上建议先删掉它。7. 偏业务数据分析实战一份电商经营周报的完整产出下面把函数、透视表、BI 组合到一个完整业务场景中。案例背景设定为一家小型电商公司需要运营每周输出一份经营周报核心指标包括销售额、订单量、客单价、退货率、类目排名。7.1 业务指标拆解与口径定义在做任何分析之前先定义清楚指标的口径销售额 各订单金额求和订单量 订单编号计数注意同一订单多个商品不能重复计数客单价 销售额 / 订单量退货率 退货订单量 / 总订单量类目排名 按类目销售额降序排名这个步骤看起来“不技术”但却是业务分析的核心。很多分析结论不可信不是因为数据算错了而是指标口径没有对齐。比如“订单量”是按订单编号数还是按商品行数会对结果影响很大。7.2 在 Excel 中完成核心计算先创建一个 Sheet 作为“计算底稿”。用 SUMIFS 计算不同渠道的销售额。假设原始表包含 A~J 列其中 J 列是渠道SUMIFS(G:G, J:J, 天猫)计算总订单量时不能用 COUNTA 对订单编号列直接计数因为如果展开后有多行数据会造成重复。正确做法是使用透视表精准去重计数。更稳妥的计算方式是借助透视表把“订单编号”拖入“值”区域右键修改值字段设置将“值汇总方式”改为“非重复计数”。注意非重复计数功能在 Excel 2013 以上版本中内置支持旧版本需要借助 SUMPRODUCT 公式。一段经典的 SUMPRODUCT 去重计数公式是SUMPRODUCT((A2:A1000)/COUNTIF(A2:A1000, A2:A1000))不过这种公式在数据量大时性能较差建议优先用透视表的非重复计数功能。7.3 用数据透视表完成多维度探查接下来连续创建多张透视表分别回答以下问题按类目汇总销售额确定 Top 3 类目。按渠道和类目交叉汇总找出渠道优势类目。按月份和类目汇总观察趋势变化。按城市汇总销售额定位核心市场。每张透视表生成后插入对应的图表并调整颜色和标题。到这一步你已经初步具备“分析视角”而不是停留在“我会用函数”的层面。7.4 在 Power BI 中排布完整仪表板将数据处理好的 Excel 文件导入 Power BI Desktop建立订单表与日期表的关系创建以下度量值销售额总计 SUM(订单表[销售额]) 订单量 COUNTROWS(订单表) 客单价 DIVIDE([销售额总计], [订单量]) 退货率 DIVIDE(CALCULATE([订单量], 订单表[是否退货] 是), [订单量])然后设计页面布局生成折线图、柱状图、环形图、卡片图。这样一个从 Excel 到 BI 的完整周报流程就闭环了。7.5 输出结论而不是只丢图表很多新人做完图表就以为任务完成。实际上业务方最关心的是“所以呢”也就是结论与行动建议。例如通过可视化发现华东地区销售额环比下降 12%进一步下钻后定位到原因是某头部商品缺货。这时你应该给出的结论是“缺货导致销售缺口建议补货并评估替代品曝光”而不是只说“华东下降了 12%”。这就是偏业务数据分析实战与纯技术操作的本质区别技术工具有限但业务解读能力才是分析师的护城河。8. 常见问题与排查思路学习这套流程中有几个高频问题几乎每个人都会遇到提前列出来遇到时可以直接对照排查。问题现象常见原因解决思路VLOOKUP 返回 #N/A查找值在查找区域不存在或存在空格、格式不一致检查数据前后是否有空格用 TRIM 清理确认查找区域首列为查找值SUMIFS 计算结果为 0条件区域与求和区域行数不一致或条件为文本但写成数字检查区域范围是否一致将条件用双引号包裹透视表新增数据后不更新透视表数据源范围固定没有扩展为新区域选择“更改数据源”或使用“表”功能让透视表自动扩展范围日期透视后自动按月分组但无法取消Excel 自动日期分组特性右键日期字段选择取消组合Power BI 显示不出视觉对象表之间没有建立关系或字段类型不匹配检查模型关系核对字段数据类型BI 中销售额是一个值而不是按维度展示忘记把维度字段拖入“轴”或“图例”确认视觉对象的字段配置完整分析报告被业务方质疑口径说明不清或图表误导性强在报告中增加指标口径说明和前提假设数据里有合并单元格导致分析异常合并单元格影响透视表引用和公式计算尽量使用一维表结构避免合并单元格必要时取消合并并填充遇到问题时最有效的排查办法是先定位数据源再看公式最后看展示层。按这个顺序排查能省下大半时间。9. 最佳实践与工程建议9.1 维护一维表结构Excel 分析中最重要的规范是“一维表原则”每一行是一条记录每一列是一个字段。横向合并单元格、多级表头、字段里夹带单位这些做法对人和软件都不友好。透视表、BI 工具、SQL 导入都极度依赖一维表结构。9.2 使用 Excel 表格功能代替普通区域选中数据区域后按 CtrlT可以把普通区域转换为“表格”。这样做的优势是公式自动扩展、透视表引用自动更新、筛选更方便。很多老手不推荐直接选整列引用公式因为会造成大量无效计算表格功能则能完美解决这个问题。9.3 指标口径要写清楚交付任何一份分析报告都要包含“指标定义”和“数据口径”部分。例如“销售额”是否含税、是否剔除退款、统计周期是自然月还是财月。口径一致的报告经得起追问口径模糊的报告即使图表再花哨也容易被推翻。9.4 数据权限与脱敏意识在使用业务数据或客户数据进行练习和分析时要注意最小权限原则和脱敏处理。练习用数据尽量使用模拟数据或对真实数据中的客户姓名、手机号、地址等敏感字段做去标识化。正式环境中操作前应先确认权限范围避免越权访问。9.5 分析输出优先给结论汇报时尽量遵循“结论先行”原则。先给核心结论和行动建议再放数据佐证和图表明细。业务方没有耐心在一堆图表里找重点你的价值恰恰在于把“数据洞察”翻译成“业务动作”。9.6 构建个人分析模板库每完成一个分析任务可以把透视表布局、Excel 模板、Power BI 主题保存成模板文件积累成自己的分析素材库。长期下来做同类分析的时间会大幅缩短模板化是提升分析效率最直接的方法之一。10. 总结与下一步学习方向到这一步你已经走完了从 Excel 函数到数据透视表再到 BI 可视化的完整路径并完成了一个偏业务向的电商经营周报案例。再往后冲刺有两条路可以选如果你还在业务岗优先修炼的是“分析思路”和“业务理解”可以继续学习 Excel 高级公式、Power Query 数据清洗、Power BI DAX 度量值同时尝试分析自己公司的真实业务问题。如果你想往专职数据分析师、商业分析岗发展下一步建议补充 SQL数据库查询几乎是数据分析岗位的必选项以及 Python 的 pandas 和 Matplotlib用于更灵活的统计分析和自动化报表。Excel、透视表、BI 解决的是“当下能干活”的问题SQL 和 Python 则决定“能走多远”的上限。最后给你三个实用建议不要追求背下所有函数重点掌握 SUMIFS、VLOOKUP、IF 这类高频函数遇到不会的随时用 Excel 自带搜索和备注功能。每学一个功能就结合自己的业务数据做一个小练习比如“分析上个月个人业绩完成情况”这样才算真正掌握。在学习到第四周时整理一份属于自己的分析作品集作为求职或内部汇报的材料它比你口头描述“我会 Excel”更有说服力。动手做一份自己的数据报表吧。数据不会直接告诉你答案但只要你开始分析它就会越来越清晰。