excel统计分析:方法与实践

郑洁军2026-07-04约 12 分钟2,239 字主编

excel统计分析:方法与实践 是运用 Excel 内置功能（函数、数据分析工具库、透视表、图表）对数据进行整理、计算、描述和推断的一系列操作。通过 Excel 完成统计分析的核心价值在于：无需学习专门的统计软件（如 SPSS、R、Python），日常销售报表、运营数据、实验数据、调查问卷处理均可在熟悉的电子表格界面中快速完成。以下按职场真实场景给出操作路径。

入口位置：你知道的功能区路径

基础统计函数（公式选项卡）

菜单路径：公式 → 函数库 → 其他函数 → 统计

常用统计函数类别：

描述统计：AVERAGE（平均值）、MEDIAN（中位数）、MODE.SNGL（众数）、STDEV.S（样本标准差）、VAR.S（样本方差）
排名与百分位：RANK.EQ（排名）、PERCENTRANK.INC（百分比排位）、QUARTILE.INC（四分位数）
分布与检验：NORM.DIST（正态分布）、T.DIST（t分布）、F.DIST（F分布）、CHISQ.DIST（卡方分布）

数据分析工具库（加载项）

菜单路径：数据 → 分析 → 数据分析

如果没看到"数据分析"选项：文件 → 选项 → 加载项 → 转到 → 勾选"分析工具库" → 确定

工具库中包含：描述统计、直方图、回归、t检验（双样本/配对）、方差分析（单因素/双因素）、相关系数、移动平均、指数平滑、F检验、z检验等。这是快速完成统计分析最实用的入口。

透视表做分组统计

菜单路径：插入 → 表格 → 数据透视表（或快捷键 Alt + N + V）

搭配值字段设置中的"值字段设置 → 计算类型"可切换：求和、计数、平均值、最大值、最小值、乘积、标准差、方差。这是做分组统计分析最快的方式（按地区、产品、月份聚合）。

统计图表

菜单路径：插入 → 图表（直方图、箱线图（Excel 2016+）、散点图、正态分布概率图可用散点图模拟）

操作示例：一个完整的分析流程

场景：销售数据描述统计

数据集（节选自一份销售表）

日期	区域	产品	销售额	负责人
2025-01-05	华东	A	1200	张三
2025-01-05	华北	B	850	李四
2025-01-06	华东	A	1600	王五
2025-01-06	华东	C	950	张三
2025-01-06	华南	B	1300	李四

步骤 1：计算关键描述指标

在空白区域输入：

指标	公式	预期结果
平均值	`=AVERAGE(D2:D6)`	1180
中位数	`=MEDIAN(D2:D6)`	1200
样本标准差	`=STDEV.S(D2:D6)`	307.01
样本方差	`=VAR.S(D2:D6)`	94250
最小值	`=MIN(D2:D6)`	850
最大值	`=MAX(D2:D6)`	1600
计数	`=COUNT(D2:D6)`	5

步骤 2：用数据分析工具库一键输出描述统计

选中数据区域（D1:D6，包含标题"销售额"）
点击 数据 → 数据分析 → 描述统计 → 确定
输入区域：$D$1:$D$6，勾选"标志位于第一行"（因为区域包含标题）
输出选项选择"新工作表组"
勾选"汇总统计" → 确定

对应输出结果（正常会输出平均值、标准误差、中位数、众数、标准差、方差、峰度、偏度、区域、最小值、最大值、求和、观测数、置信度(95.0%)等十几个统计量）。

关键检查点：输出中的"观测数"应当与实际数据行数一致（这里为 5）。如果小于实际行数，说明数据区域内有空单元格或非数字单元格。

步骤 3：用数据透视表做分组平均

选中原数据任意单元格 → 插入 → 数据透视表
行标签：区域
值：销售额（默认计数 → 右键 → 值字段设置 → 平均值）

结果（预期）：

行标签	平均值项:销售额
华东	1250
华北	850
华南	1300

步骤 4：可视化——带误差线的柱形图

选中步骤 3 的透视表（复制并粘贴为数值到新区域）
插入 → 簇状柱形图
选中图表 → 图表设计 → 添加图表元素 → 误差线 → 标准偏差（或标准误差，取决于你要展示变异程度还是抽样误差）

公式或快捷键示例

可直接复制的统计公式

描述统计一键公式组

=AVERAGE(数据区域)
=MEDIAN(数据区域)
=STDEV.S(数据区域)
=VAR.S(数据区域)
=SKEW(数据区域)      ''偏度
=KURT(数据区域)      ''峰度
=COUNT(数据区域)      ''数值计数
=COUNTA(数据区域)     ''非空计数

排名与百分位

=RANK.EQ(值, 数据区域, 0)      ''降序排名（1 最高）
=RANK.EQ(值, 数据区域, 1)      ''升序排名（1 最低）
=PERCENTRANK.INC(数据区域, 值) ''值在数据集中的百分比排位

相关性

=CORREL(区域1, 区域2)         ''皮尔逊相关系数

t检验（仅限借助函数计算统计量）

=T.TEST(区域1, 区域2, 2, 2)   ''双尾、双样本等方差 t检验

快捷键

Alt + N + V：插入数据透视表
Alt + M + U：自动求和下拉菜单（平均值、计数、最大值、最小值）
Alt + A + W：定义名称（配合公式使用让公式可读）
Ctrl + Shift + Enter：数组公式（Excel 365 不再需要，自动溢出）

常见错误

1. 数字储存为文本格式

现象：计算公式（如 AVERAGE、SUM）结果为 0 或忽略某些行。

排查与处理：

选中数据列 → 检查单元格左上角是否有绿色三角标记
选中整列 → 数据 → 分列 → 完成（最快捷的转换方法，不修改任何分隔符直接完成即可将文本数字转为真数字）
或者在一个空单元格输入 1 → 复制 → 选中问题数据区域 → 右键选择性粘贴 → 乘（强制转换）

2. 区域未使用绝对引用

现象：将公式（如 =RANK.EQ(B2,$B$2:$B$100,0)）向下填充时，引用区域发生偏移导致结果错误。

解决方案：选中公式中的区域引用 → 按 F4 键循环切换引用类型：相对 → 绝对（$A$1）→ 混合行（A$1）→ 混合列（$A1）。

3. 数据透视表手动修改导致数据不一致

现象：修改了透视表原始数据源，但透视表未刷新。

解决方案：右键单击透视表任意位置 → 刷新（或快捷键 Alt + F5）。如果数据源有新增行，还需修改透视表的数据源范围：选中透视表 → 透视表分析 → 更改数据源。

4. 分组时包含总行列

现象：透视表或描述统计中包含汇总行、合计行，导致统计量失真。

解决方案：确认数据区域仅包含明细数据。可在源数据后用 =COUNTA(明细数据区域) 检查行数。

5. 缺失值处理不当

现象：AVERAGE 自动跳过空白单元格；但空白单元格在图表中表现可能不连续。

检查与处理：

使用 =AVERAGEIF(区域, "<>", 范围) 排除空值
在图表中右键选择"选择数据"→"隐藏的单元格和空单元格"→选择"用零值连接"或"用直线连接数据点"

常见问题