excel - Excel公式中如何匹配Stata百分位数计算？

Question

Stata 使用称为 R-2 ( https://en.wikipedia.org/wiki/Quantile )的分位数计算方法，而 Excel 使用带有 percentile.inc 函数的 R-7。我的目标是在 Excel 中找到一个正确的公式，它给出的结果与使用 R-2 方法的 Stata 中的结果相同。

目前，我可以看到 percentile.inc 仅匹配奇数和离散样本的 Stata 结果（我正在处理离散样本）。但是，即使此处显示的示例也会出现此问题

从概念上讲，在 Excel 中使用 percentile.inc 似乎不正确，因为它是一种 R-7 方法，即使它与奇数和离散样本的 R-2 方法相匹配。

我的问题是在 Excel 中正确使用以匹配 Stata 百分位数结果的最简单公式是什么？

标签： excelstata

Answer 1

因此，对于 N=4，R-2 到 Excel 的相当直译将如下所示（假设数据已排序）：

=(INDEX(A$2:A$5,CEILING(C2*4,1))+INDEX(A$2:A$5,FLOOR(C2*4+1,1)))/2

如果您尝试输入一个零分位数确实会出错，因此这将是一种特殊情况，就像分位数为 1 一样。我假设 Stata 在这两种情况下给出了集合中的最低值和最高值？

一个包含所有检查的更动态的公式如下所示：

=IFS(OR(C2<0,C2>1),"Out of range",C2=0,A$2,C2=1,INDEX(A:A,COUNT(A:A)+1),TRUE,(INDEX(A$2:INDEX(A:A,COUNT(A:A)+1),CEILING(C2*COUNT(A:A),1))+INDEX(A$2:INDEX(A:A,COUNT(A:A)+1),FLOOR(C2*COUNT(A:A)+1,1)))/2)

尽管您可以使用 Microsoft 365 中的 Let 构造使其更短。

在 VBA 中将其实现为函数可能会很好，它将对数据进行排序并返回分位数值，或者当然您也可以在 Microsoft 365 公式中进行排序：

=LET(N,COUNT(A:A),sortedRange,SORT(A$2:INDEX(A:A,N+1)),IFS(OR(C2<0,C2>1),"Out of range",C2=0,INDEX(sortedRange,1),C2=1,INDEX(sortedRange,N),TRUE,(INDEX(sortedRange,CEILING(C2*N,1))+INDEX(sortedRange,FLOOR(C2*N+1,1)))/2))