如何在 Excel 中计算百分位数

如何在 Excel 中计算百分位数

Excel 提供了两个主要函数用于计算百分位数：PERCENTILE.INC() 和 PERCENTILE.EXC()。

严格来说，还有第三个函数——PERCENTILE()——曾用于旧版本软件（Excel 2007 及更早版本），现在保留用于兼容性。该旧函数的工作方式与 PERCENTILE.INC() 相同。

在本教程中，我们将重点关注较新的函数——PERCENTILE.INC() 和 PERCENTILE.EXC()。

PERCENTILE.INC() Excel 函数

语法

PERCENTILE.INC(array, k)

其中：

• array——要计算百分位数的数据数组或区域，

• k——在 0 到 1 范围内（包含端点）的百分位值。

两个参数均为必填。

PERCENTILE.INC() 函数返回区间内值的第 k 个百分位数，其中 k 在 0 到 1 范围内，包括 0 和 1。换言之，此函数包含数组的首尾值。

算法

在底层，PERCENTILE.INC() 函数执行以下步骤：

1. 将给定数组的值按升序排序。
2. 基于参数 k 和数组中的数据点数量 N，用公式 k(N-1) + 1 确定百分位位置。
3. 如果上一步的结果不是整数，算法将在数组中最接近的值之间进行插值。

示例

假设我们想用 PERCENTILE.INC() 函数计算从 1 到 10（包含）的整数数组的第 30 个百分位数。我们的公式如下：

注意：虽然上面数组的值为了演示而进行了排序，但我们实际上无需事先排序，因为算法会在后台完成这一步。

以下是计算结果：

可能的错误

• #NUM!——当提供的数组为空，或 k < 0，或 k > 1。

• #VALUE!——当 k 为非数值。

PERCENTILE.EXC() Excel 函数

语法

PERCENTILE.EXC(array, k)

其中：

• array——要计算百分位数的数据数组或区域，

• k——在 0 到 1 范围内（不包含端点）的百分位值。

两个参数均为必填。

PERCENTILE.EXC() 函数返回区间内值的第 k 个百分位数，其中 k 在 0 到 1 范围内，但不包含 0 和 1。更准确地说，对于包含 N 个数据点的数组，k 的取值应同时满足以下条件：

1. K > 1/(N+1)
2. K < N/(N+1)

简单来说，PERCENTILE.EXC() 仅对严格介于 0 与 1 之间的概率定义百分位数。这意味着它不会计算绝对最小值（第 0 个百分位）或最大值（第 100 个百分位），但在插值时仍会使用所有数据点。

算法

在底层，PERCENTILE.EXC() 函数执行以下步骤：

1. 将给定数组的值按升序排序。
2. 基于参数 k 和数组中的数据点数量 N，用公式 k(N+1) 确定百分位位置。
3. 如果上一步的结果不是整数，算法将在数组中最接近的值之间进行插值。

示例

假设我们想用 PERCENTILE.EXC() 函数计算从 1 到 10（包含）的整数数组的第 30 个百分位数。我们的公式如下：

注意：虽然上面数组的值为了演示而进行了排序，但我们实际上无需事先排序，因为算法会在后台完成这一步。

以下是计算结果：

可能的错误

• #NUM!——当提供的数组为空，或 k <= 1/(N+1)，或 k >= N/(N+1)。

• #VALUE!——当 k 为非数值。

若要学习或复习其他常用的 Excel 函数，请查看此教程：The 15 Basic Excel Formulas Everyone Needs to Know。

Excel 中的 PERCENTILE.INC 与 PERCENTILE.EXC

到目前为止，我们可以清楚看到 PERCENTILE.INC() 和 PERCENTILE.EXC() 函数之间有很多相似之处。然而，它们之间也存在显著差异，使其成为两个独立的函数，并导致不同的结果。让我们回顾一下这两个函数在 Excel 中的相同点与不同点。

相同点：

1. 函数语法。

2. 整体算法逻辑（用于确定百分位位置的公式除外）。

3. 可能的错误（当数组为空或 k 超出可用范围时为 #NUM!，当 k 为非数值时为 #VALUE!）。

不同点：

1. k 参数的可取范围：

• PERCENTILE.INC()——从 0 到 1，包含端点。

• PERCENTILE.EXC()——从 1/(N+1) 到 N/(N+1)，不包含端点。

2. 对数组值的处理：

• PERCENTILE.INC()——包含首尾值。

• PERCENTILE.EXC()——不在首尾值（0% 和 100%）处定义百分位数，但在插值时仍包含全部数据。

3. 确定百分位位置的公式：

• PERCENTILE.INC()——k(N-1) + 1。

• PERCENTILE.EXC()——k(N+1)。

让我们将 PERCENTILE.INC() 和 PERCENTILE.EXC() 同时应用于同一个从 1 到 10（包含）的整数数组，并尝试不同的 k 值，看看它们的表现：

上表显示，当 k 的取值位于或超出其有效范围（k ≤ 1/(N+1) 或 k ≥ N/(N+1)）时，PERCENTILE.EXC() 函数未定义。这并非缺陷，而是反映了排除式百分位数的数学定义。接近两端时 .INC() 与 .EXC() 结果的细微差异，源自它们在计算百分位位置时的插值方式不同。

另一方面，我们也应注意，在绝大多数情况下，过低或过高的 k 值从统计角度并无太大意义。这些限制在实践中很少造成问题。此外，现实中我们通常需要对比上述示例更大的数组计算百分位数，这会减弱结果差异。

在大多数情况下，更适合选择 PERCENTILE.INC() 函数，这也与包括 Python 和 R 在内的许多编程语言用于计算百分位数的默认公式一致。

然而，如果我们希望避免在数据集的极端位置定义百分位数，则应选择 PERCENTILE.EXC()。它不会计算第 0 个或第 100 个百分位，但在按秩次进行插值时仍会使用所有数据点。

要从数据中提取有价值的商业洞见，干净且具有代表性的数据至关重要。Data Preparation in Excel课程和Data Manipulation in Excel Cheat Sheet可帮助您高效地为后续分析做好数据准备。

结论

我们讨论了百分位数背后的基本统计理论、其定义、主要应用领域，以及在 Excel 中用于计算百分位数的两个主要函数，包括它们的语法、算法、计算示例和可能出现的错误。

我们还比较了这两个 Excel 函数，概述了它们的相同点和重要差异，并在同一数据数组上运行两者以对比结果，从而了解各自的最佳适用场景。

要充分释放 Excel 的威力，建议学习面向初学者的Excel Fundamentals技能路径，通过动手练习掌握 Excel 中用于数据准备、可视化和分析的基础与高级功能与公式。