【干货】别再死磕 Excel 函数了!5分钟学会数据透视表,轻松搞定多维度数据分析

点击蓝字 关注我们

写公式太烧脑？函数太多记不牢？在日常办公中，我们经常需要对大量数据进行多维度汇总分析——比如统计员工的日均产量、最高产量、最低产量，或是按部门、日期汇总业绩。

手动计算不仅耗时费力，还容易出错。其实80%的日常分析，Excel里的一个“数据透视表”就能解决。

一

数据透视表实际上等于Excel里的“拖拽式SQL”。利用鼠标一拖就能把几万行明细瞬间分组、汇总、算占比，实现不会写公式也能3分钟出报表的效果。

1.核心功能

首先是分组，实现日期自动按年/季/月/周、数字按区间、文本按类别分组的效果。其次是汇总，包括求和、计数、平均、最大/最小、标准差等11种聚合等。最后是显示值，实现占比、差异、累计、排名，一键切换。

2.典型使用场景

销售：各区域、各产品销售额 + 占比

运营：新老客留存率对比、活动渠道 ROI 排名

财务：按月汇总成本、自动算同比环比

人力：部门-职级交叉统计平均薪酬

电商：Top10 畅销 SKU、库存量预警

日志：错误代码出现次数、按小时趋势

3.数据透视表常见步骤：

使用数据透视表进行数据分析，通常可按以下步骤进行：

第一步：准备数据源

确保数据是规范的表格格式，第一行是列标题（字段名），数据区域无空行、空列，且每列数据类型一致。

避免合并单元格，保证数据连续性，以便数据透视表正确识别数据范围。

第二步：插入数据透视表

在Excel中，选中数据区域内的任意一个单元格。

点击菜单栏中的“插入”选项卡，选择“数据透视表”。

在弹出的对话框中，确认数据源区域是否正确，选择将数据透视表放置在“新工作表”或“现有工作表”的指定位置，点击“确定”。

第三步：布局字段

右侧会出现“数据透视表字段”窗格，列出数据源的所有列标题（字段）。将需要的字段拖放到以下四个区域：

行：作为分析的横向维度，用于分组行数据（如日期、部门、产品等）。

列：作为分析的纵向维度，与行标签交叉分析（如地区、客户类型等）。

值：需要被汇总计算的字段，如销售额、数量、金额等，数据透视表会默认对数值字段进行求和计算。

筛选器：用于对整个报表进行筛选的字段，筛选后，行和列区域的结果会相应变化（如年份、产品类别等）。

第四步：配置值字段计算方式

点击“值”区域中的字段，选择“值字段设置”。在对话框中可更改汇总方式，如求和、计数、平均值、最大值、最小值、乘积等。

还可设置值显示方式，如占同行总计的百分比、占同列总计的百分比、差异、累计值等，以满足不同的分析需求。

第五步：格式化和调整

在“设计”选项卡中，可应用样式、设置分类汇总/总计的显示方式、调整布局（如表格形式、大纲形式、压缩形式）。手动调整列宽，应用数字格式，使数据透视表更清晰易读。

二

用数据透视表进行数据分析的重点是根据核心场景需求，搭建分析框架。

Step1：创建数据透视表

选定数据源并确定报表位置，创建数据透视表。

操作步骤：选中原始数据区域任意单元格（如A1:D121，含表头）→点击顶部菜单栏【插入】→找到【数据透视表】按钮→在弹出的对话框中，默认勾选“选择一个表或区域”（已自动识别数据源）→选择透视表放置位置（推荐“新工作表”，避免占用原数据区域）→点击【确定】。

效果：Excel会自动新建一个工作表，并生成“数据透视表字段”面板，此时报表区域显示“若要生成报表，请从数据透视表字段列表中选择字段”。

Step2：拖拽字段，确定行与值的维度

接下来通过拖拽字段，明确“按谁汇总”和“汇总什么指标”。

操作步骤：在“数据透视表字段”面板中，找到“员工姓名”字段，拖拽到“行”区域（表示按员工姓名分组汇总）；找到“生产数量”字段，连续拖拽3次到“值”区域（分别用于计算平均值、最大值、最小值）。

效果：报表区域会自动显示员工姓名列表，以及3列“求和项：生产数量”（默认汇总方式为求和），后续只需修改汇总方式即可。

Step3：修改值汇总依据，精准计算目标指标

默认的“求和”不符合需求，需要分别将3列“生产数量”的汇总方式改为“平均值”“最大值”“最小值”。

1.计算日均产量（平均值）

操作步骤：右键单击任意一列“求和项：生产数量”的表头或数据单元格→在弹出的菜单中选择【值汇总依据】→点击【平均值】。

效果：该列会自动变为“平均值项：生产数量”，显示每名员工的日均产量（如安俞帆日均产量517.375）。

2.计算最高日产量（最大值）

操作步骤：右键单击第二列“求和项：生产数量2”→选择【值汇总依据】→点击【最大值】。

效果：该列变为“最大值项：生产数量2”，显示每名员工的最高日产量（如安俞帆最高产量955）。

3.计算最低日产量（最小值）

操作步骤：右键单击第三列“求和项：生产数量3”→选择【值汇总依据】→点击【最小值】。

效果：该列变为“最小值项：生产数量3”，显示每名员工的最低日产量（如安俞帆最低产量38）。

Step4：优化报表格式，让结果更清晰

默认的字段名称不够直观，可修改表头并删除多余的“总计”行，让报表更规范。

1.修改字段标题

操作步骤：双击“平均值项：生产数量”表头，直接修改为“日均产量”；同理，将“最大值项：生产数量2”改为“最高产量”，“最小值项：生产数量3”改为“最低产量”。

2.删除总计行（可选）

操作步骤：右键单击报表中的“总计”行→在弹出的菜单中选择【删除总计】。

效果：报表仅显示每名员工的具体指标，避免总计行干扰查看（若需要整体汇总数据，可保留总计）。

职场上，掌握数据分析技能，可以极大地提升工作效率。CDA数据分析师在各行业的数据岗中认可度非常高，一般都要求考过CDA数据分析师二级，CDA二级中包含了模型搭建的详细内容，对于数据岗的工作来说特别有帮助。

扫码“CDA认证”小程序，获取更多行业模型、知识资料。