Excel 表格多行多列数据，如何一次性算出平均值

在 Excel 多行多列数据中一次性以平均值为中心的计算方法

在数据分析中，经常需要以“平均值为中心”对一整块数据进行处理（即求出总体平均值，然后将每个数值以该平均值为基准进行后续运算或中心化）。本文围绕 Excel 中多行多列（矩形或不规则多区域）数据，介绍多种一次性计算平均值并以平均值为中心处理数据的实用方法与注意事项，适用于常用 Excel 版本（含支持动态数组的新版 Excel）。

一、直接求整体平均值（规则矩形区域）

– 基本公式：若数据在 A1:D10，整体平均值为：=AVERAGE(A1:D10) 。AVERAGE 可直接对多行多列的矩形区域求平均。

– 非连续区域：=AVERAGE(A1:A10,C1:C10) 可以同时计算多段区域。

– 排除 0 或空值：排除 0：=AVERAGEIF(A1:D10,”<>0″)；排除空值可用 AVERAGE 自带的行为（空白自动忽略），或用 AVERAGEIF(A1:D10,”<>“).

二、一次性把数据“以平均值为中心”（均值中心化）

– 现代 Excel（支持动态数组）：先在某单元格计算平均值，例如 G1: =AVERAGE(A1:D10)；然后在输出区域（比如 H1）输入：=A1:D10 – $G$1，结果会整体溢出（spill）成与原区域同尺寸的“中心化矩阵”（每个元素 = 原值 ? 平均值）。

– 传统 Excel（不支持溢出）：先计算平均值 G1，然后选中与原区域同样大小的输出区域，输入同样公式 =A1:D10 – $G$1，按 Ctrl+Shift+Enter（数组公式），即可一次性填充。

– 非公式方法：复制平均值单元格，选中原数据区域，选择“粘贴为数值 → 运算 → 减法”（Paste Special → Subtract），可在原地把均值从每个数中减去（操作前请先备份）。

三、处理筛选、错误值或含文本的情况

– 筛选后计算可用 SUBTOTAL：=SUBTOTAL(1,A1:A100) 对可见行求平均（若需忽略手动隐藏行使用 101 系列：=SUBTOTAL(101,A1:A100)）。

– 忽略错误值可用 AGGREGATE：=AGGREGATE(1,6,A1:D10) （1 表示 AVERAGE，选项 6 忽略错误）。

– 使用 FILTER（动态数组）或数组公式过滤非数值：=AVERAGE(FILTER(A1:D10,ISNUMBER(A1:D10))) 或 旧式数组：=AVERAGE(IF(ISNUMBER(A1:D10),A1:D10))（需 Ctrl+Shift+Enter）。

四、借助表格、数据透视表与 Power Query

– 将数据转换为 Excel 表格（Ctrl+T），使用结构化引用可让公式更直观：=AVERAGE(Table1[数值列])。

– 数据透视表可按分组或分类一次性计算各组平均值，适合分组中心化分析。

– Power Query（数据 → 获取并转换）适合对大数据做批量中心化处理：加载表格、添加自定义列（列值 ? 全列平均值），然后加载回工作表。

五、用 VBA 自动化一次性中心化

– 若需要重复操作或处理大区域，可以用宏自动计算平均值并全部替换或输出到新区域。示例：

Sub CenterData()

Dim rng As Range, c As Range

Set rng = Range(“A1:D10”)

Dim m As Double

m = Application.WorksheetFunction.Average(rng)

For Each c In rng

If IsNumeric(c.Value) And Not IsEmpty(c.Value) Then

c.Value = c.Value – m

End If

Next c

End Sub

– 运行前先备份原数据；也可改为输出到新区域以保留原始数据。

实用提示与注意事项

– 绝对引用：在引用平均值单元格时使用绝对引用（$G$1）以便复制公式不变。

– 保留原始数据：中心化通常会改变数值，建议先复制原表或把结果输出到新区域。

– 性能：大型区域使用数组公式或 VBA 时注意性能；Power Query 或逐列计算有时更高效。

– 分组中心化：若需要按组（例如按部门、按月份）计算组内平均并中心化，使用 AVERAGEIFS、数据透视表或按组的 Power Query 更方便。

结论：针对多行多列数据，Excel 提供了从简单的 AVERAGE 到 AVERAGEIF/AVERAGEIFS、SUBTOTAL、AGGREGATE、FILTER 以及 VBA、Power Query 等一整套方案，可以灵活地一次性计算平均值并把数据以平均值为中心进行批量处理。根据数据形态（是否有空值、错误、筛选或需分组）选择合适的方法，配合表格与绝对引用，可高效、稳健地完成中心化操作。