幕雪

excel统计函数深入掌握：功能、用途、使用方法、常见函数详解与实用案例

Microsoft Excel 不仅仅是一个表格处理工具，它内置的强大函数库使其成为一个功能全面的数据分析利器。在众多函数类别中，统计函数占据着举足轻重的地位。它们是处理和理解大量数据的关键，能够帮助我们快速获取数据的概览、趋势、离散程度等重要信息。

是什么：Excel 统计函数的核心功能

Excel 的统计函数是一系列预设的公式，用于对一组数值进行统计计算，从而描述这组数据的特征。它们能帮助你：

  • 描述数据的集中趋势（平均值、中位数、众数）。
  • 衡量数据的离散程度（标准差、方差、极差）。
  • 对数据进行计数、排名和百分比计算。
  • 执行基于特定条件的统计计算（条件计数、条件求和、条件平均）。

简单来说，它们是将原始数据转化为有意义的统计指标的工具。

为什么用：使用统计函数的必要性

在数据分析中，统计函数的使用并非可有可无，而是至关重要的：

  • 效率与速度： 手动计算大量数据的平均值、标准差等既耗时又易出错。函数可以在瞬间完成复杂计算，大幅提高工作效率。
  • 准确性： 内置函数经过严格测试，确保计算结果的准确性，避免人为计算或公式输入错误。
  • 数据概览： 快速获取数据的核心统计特征，帮助你迅速了解数据集的全貌和关键属性，例如销售数据的平均值能告诉你大致的销售水平。
  • 决策支持： 通过统计函数得出的指标，如平均分、合格率、波动性等，为决策提供量化的依据。
  • 动态更新： 当源数据发生变化时，使用了统计函数的公式会自动重新计算，无需手动修改或重复操作。

想象一下，如果需要分析几千行甚至几万行的销售数据，手动计算每个产品的平均销量、总销量、最高销量等，几乎是不可能完成的任务。而统计函数让这一切变得轻松自如。

在哪里：在 Excel 中找到和使用统计函数

在 Excel 中使用统计函数非常直观：

  • 直接输入公式： 在任意单元格中输入等号（=），然后输入函数名称（如 AVERAGE），接着输入函数所需的参数，最后按 Enter 键。例如：=AVERAGE(A1:A10)
  • 利用函数库： Excel 功能区的“公式”选项卡下有一个“函数库”组。点击“更多函数”，选择“统计”，即可看到 Excel 提供的所有统计函数列表。你可以浏览这些函数及其说明，找到你需要的函数。
  • 使用函数参数对话框： 在输入函数名称后，点击公式栏中的“Fx”按钮，或者在函数库中选择函数后，会弹出“函数参数”对话框。这个对话框会清晰地列出函数所需的每个参数，并提供参数的说明和当前输入值的计算结果预览，非常适合不熟悉函数参数的用户。

函数的使用位置通常是工作表中的一个空白单元格，你将计算结果放置于此。

有多少：Excel 统计函数的种类与数量

Excel 提供了种类繁多、数量众多的统计函数，涵盖了从基础到高级的各种统计分析需求。具体数量可能因 Excel 版本而略有差异，但总计有几十个甚至上百个统计函数。它们可以大致分为以下几类：

  • 描述统计函数： 计算平均值、中位数、众数、标准差、方差、偏度、峰度等。
  • 计数函数： 计算单元格的数量（总数、非空数、空数、满足特定条件的数量）。
  • 排名与百分位函数： 计算数值在数据集中的排名或百分位位置。
  • 条件统计函数： 基于一个或多个条件进行求和、计数或平均。
  • 分布函数： 用于概率分布计算，如正态分布、T 分布等。
  • 回归与相关性函数： 计算相关系数、执行线性回归分析等。

你不需要记住所有函数，但了解常用的函数及其应用场景是高效分析数据的关键。

如何：使用统计函数的基本步骤与方法

使用 Excel 统计函数的基本流程通常遵循以下步骤：

  1. 确定分析目标： 你想从数据中获得什么信息？（例如，计算平均销售额，统计某个产品的销量，找到最高分等）。
  2. 识别相关数据： 确定需要进行计算的数据范围（例如，某个列的所有销售数据，某个区域的学生分数）。
  3. 选择合适的函数： 根据你的分析目标，从统计函数库中选择最符合需求的函数（例如，计算平均值用 AVERAGE，统计非空单元格用 COUNTA）。
  4. 输入函数公式：
    • 在目标单元格中输入 =。
    • 输入函数名称。
    • 输入左括号 (。
    • 输入函数所需的参数。参数可以是数值、单元格引用、范围引用、文本字符串或逻辑值，多个参数之间用逗号隔开。
    • 输入右括号 )。
  5. 确认并查看结果： 按 Enter 键，Excel 会计算并显示结果。如果结果不符合预期，检查公式、参数和数据范围是否正确。

例如，要计算单元格区域 A1 到 A10 中所有数值的平均值，步骤如下：

在任一空白单元格中输入：

=AVERAGE(A1:A10)

按 Enter 键，该单元格就会显示区域 A1:A10 的平均值。

怎么用：常见统计函数详解与实例

以下是一些 Excel 中最常用且实用的统计函数及其使用方法和具体案例：

平均值类函数

AVERAGE

功能： 计算给定参数的平均值（算术平均值）。忽略文本和逻辑值以及空单元格。

语法： AVERAGE(number1, [number2], ...)

参数： number1, number2, … 是要求平均值的 1 到 255 个数字、单元格引用或范围。

实例： 计算 B2 到 B10 单元格区域的学生考试平均分数。

=AVERAGE(B2:B10)

如果 B2:B10 区域包含 {85, 90, 78, “缺考”, 92, 88, 70, “”, 95}，AVERAGE 函数会忽略 “缺考” 和 “”，只计算 {85, 90, 78, 92, 88, 70, 95} 的平均值。

AVERAGEA

功能： 计算给定参数的平均值。它将文本值视为 0，将逻辑值 TRUE 视为 1，FALSE 视为 0。空单元格被忽略。

语法： AVERAGEA(value1, [value2], ...)

参数： value1, value2, … 是要求平均值的 1 到 255 个值、单元格引用或范围。

实例： 如果上面的例子中需要将 “缺考” 视为 0 分参与计算：

=AVERAGEA(B2:B10)

AVERAGEIF

功能： 计算满足给定条件的单元格的平均值。

语法： AVERAGEIF(range, criteria, [average_range])

参数：

  • range：包含条件需要评估的单元格区域。
  • criteria：确定哪些单元格需要计算平均值的条件。可以是一个数字、表达式、单元格引用或文本。例如：80, “>70”, “优秀”, C1。
  • average_range (可选)：实际计算平均值的单元格区域。如果忽略，则使用 range 区域。

实例： 计算销售额大于 1000 元的产品的平均销售额。产品名称在 A 列，销售额在 B 列。

=AVERAGEIF(B2:B100, ">1000")

如果条件是根据 A 列的产品类别来判断，例如计算类别为“电子产品”的销售额平均值，产品类别在 A 列，销售额在 B 列：

=AVERAGEIF(A2:A100, "电子产品", B2:B100)

AVERAGEIFS

功能： 计算满足多个条件的单元格的平均值。

语法： AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

参数：

  • average_range：实际计算平均值的单元格区域。
  • criteria_range1：包含第一个条件需要评估的单元格区域。
  • criteria1：第一个条件的标准。
  • [criteria_range2, criteria2], ... (可选)：额外的条件区域和标准，最多可以有 127 组。

实例： 计算类别为“电子产品”并且销售额大于 500 元的产品的平均销售额。产品类别在 A 列，销售额在 B 列。

=AVERAGEIFS(B2:B100, A2:A100, "电子产品", B2:B100, ">500")

计数类函数

COUNT

功能： 统计单元格区域中包含数字的单元格个数，以及参数列表中数字的个数。

语法： COUNT(value1, [value2], ...)

参数： value1, value2, … 是包含或引用需要计数的各种类型数据的 1 到 255 个参数。

实例： 统计学生分数列表中（B2:B10区域）实际有分数（数字）的学生人数。

=COUNT(B2:B10)

COUNTA

功能： 统计单元格区域中非空单元格的个数（包括数字、文本、逻辑值、错误值等）。

语法： COUNTA(value1, [value2], ...)

参数： value1, value2, … 是包含或引用需要计数的各种类型数据的 1 到 255 个参数。

实例： 统计学生名单列表（A2:A10 区域）中所有已填写姓名的学生人数。

=COUNTA(A2:A10)

COUNTBLANK

功能： 统计单元格区域中空单元格的个数。

语法： COUNTBLANK(range)

参数： range 是要计算空单元格个数的区域。

实例： 统计学生分数列表中（B2:B10区域）未填写分数（空单元格）的学生人数。

=COUNTBLANK(B2:B10)

COUNTIF

功能： 统计某个区域中满足给定条件的单元格个数。

语法： COUNTIF(range, criteria)

参数：

  • range：要对其进行条件计数的区域。
  • criteria：确定哪些单元格需要计数的条件。

实例： 统计学生分数列表中（B2:B10区域）分数大于或等于 90 分的学生人数。

=COUNTIF(B2:B10, ">=90")

统计产品列表（A2:A100）中名称为“笔记本电脑”的产品数量。

=COUNTIF(A2:A100, "笔记本电脑")

COUNTIFS

功能： 统计满足多个条件的单元格个数。

语法： COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)

参数：

  • criteria_range1：包含第一个条件需要评估的单元格区域。
  • criteria1：第一个条件的标准。
  • [criteria_range2, criteria2], ... (可选)：额外的条件区域和标准，最多可以有 127 组。

实例： 统计类别为“电子产品”并且销售额大于 500 元的产品的数量。产品类别在 A 列，销售额在 B 列。

=COUNTIFS(A2:A100, "电子产品", B2:B100, ">500")

中位数、众数类函数

MEDIAN

功能： 返回给定数值集合中的中位数。中位数是集合中居于中间位置的数。

语法： MEDIAN(number1, [number2], ...)

参数： number1, number2, … 是要计算中位数的 1 到 255 个数字。

实例： 计算一组销售额数据的中位数（D2:D20区域）。

=MEDIAN(D2:D20)

如果数据有奇数个，中位数是排序后的中间那个数；如果数据有偶数个，中位数是排序后中间两个数的平均值。

MODE.SNGL

功能： 返回在数值集合中出现频率最高的数值（众数）。如果存在多个众数，返回第一个找到的众数。忽略文本和逻辑值。

语法： MODE.SNGL(number1, [number2], ...)

参数： number1, number2, … 是要计算众数的 1 到 255 个数字。

实例： 找出在客户满意度调查（E2:E50区域，数值为 1-5）中出现次数最多的评分。

=MODE.SNGL(E2:E50)

MODE.MULT

功能： 返回在数值集合中出现频率最高的所有数值（众数集合）。这是一个数组函数，需要使用 Ctrl+Shift+Enter 输入或在溢出区域使用。

语法： MODE.MULT(number1, [number2], ...)

参数： number1, number2, … 是要计算众数的 1 到 255 个数字。

实例： 找出客户评分（E2:E50区域）中所有出现频率最高的评分。

输入公式后按 Ctrl+Shift+Enter（如果使用旧版 Excel），或者在 O365 中直接输入即可溢出显示结果。
=MODE.MULT(E2:E50)

最大/最小值与排名函数

MAX

功能： 返回参数列表中的最大值。

语法： MAX(number1, [number2], ...)

参数： number1, number2, … 是要查找最大值的 1 到 255 个数字。

实例： 找出本月最高销售额（B2:B100区域）。

=MAX(B2:B100)

MIN

功能： 返回参数列表中的最小值。

语法： MIN(number1, [number2], ...)

参数： number1, number2, … 是要查找最小值的 1 到 255 个数字。

实例： 找出本月最低销售额（B2:B100区域）。

=MIN(B2:B100)

LARGE

功能： 返回数据集中第 k 个最大值。

语法： LARGE(array, k)

参数：

  • array：需要计算第 k 个最大值的数据区域或数组。
  • k：要返回的数据在数据集中的位置（从最大数开始计算），例如 1 为最大值，2 为第二大值。

实例： 找出销售额数据（B2:B100区域）中的第三高销售额。

=LARGE(B2:B100, 3)

SMALL

功能： 返回数据集中第 k 个最小值。

语法： SMALL(array, k)

参数：

  • array：需要计算第 k 个最小值的数据区域或数组。
  • k：要返回的数据在数据集中的位置（从最小数开始计算），例如 1 为最小值，2 为第二小值。

实例： 找出销售额数据（B2:B100区域）中的第二低销售额。

=SMALL(B2:B100, 2)

RANK.EQ

功能： 返回数值在列表中的排位（即相对于列表中其他数值的大小）。如果列表包含重复数值，这些数值将获得相同的排位，下一个数值的排位将是跳过的排位。排位是按照数值在已排序列表中的位置计算的。

语法： RANK.EQ(number, ref, [order])

参数：

  • number：需要确定排位的数值。
  • ref：包含数值列表的数组或区域。非数值项将被忽略。
  • order (可选)：一个数值，指定排位的方式：0 或省略表示降序（最大值排位为 1）；非零值表示升序（最小值排位为 1）。

实例： 计算学生分数列表（B2:B20区域）中某个学生分数（例如 B2单元格的分数）的班级排名（分数越高排名越靠前）。

在 C2 单元格输入公式：
=RANK.EQ(B2, B$2:B$20, 0)
然后可以将 C2 单元格的公式向下填充到 C20，计算所有学生的排名。注意使用了绝对引用 B$2:B$20 确保比较范围不变。

RANK.AVG

功能： 返回数值在列表中的排位。如果列表包含重复数值，这些数值将获得相同的排位，但排位是重复数值排位的平均值。

语法： RANK.AVG(number, ref, [order])

参数： 同 RANK.EQ。

实例： 如果有两个学生并列第二名，使用 RANK.EQ 两个都会显示排位 2，第三名显示排位 4。而使用 RANK.AVG，并列第二的显示排位 (2+3)/2 = 2.5，第三名显示排位 4。

在 C2 单元格输入公式：
=RANK.AVG(B2, B$2:B$20, 0)
然后向下填充。

离散程度函数 (示例)

STDEV.S

功能： 基于样本计算标准差。标准差衡量数值相对于平均值的离散程度。

语法： STDEV.S(number1, [number2], ...)

参数： number1, number2, … 是对应于总体的样本的 1 到 255 个数字参数。

实例： 计算一个产品样本销售额（F2:F50区域）的标准差，了解销售额的波动性。

=STDEV.S(F2:F50)

VAR.S

功能： 基于样本计算方差。方差是标准差的平方，也衡量数据的离散程度。

语法： VAR.S(number1, [number2], ...)

参数： 同 STDEV.S。

实例： 计算一个产品样本销售额（F2:F50区域）的方差。

=VAR.S(F2:F50)

这仅仅是 Excel 统计函数冰山一角，还有许多其他函数用于更高级的统计分析，如百分位、四分位数、偏度、峰度、协方差、相关系数等等。

总结

Excel 的统计函数是处理和分析数据的强大工具。掌握它们的使用方法，能够极大地提高你在数据整理、报告生成、趋势分析和决策支持方面的能力。从简单的平均值和计数，到复杂的条件统计和排名，这些函数覆盖了日常数据分析的方方面面。通过不断练习和应用，你将能够更深入地从数据中挖掘有价值的信息。

excel统计函数