Excel中PERCENTILE函数计算逻辑与取值调整咨询

一、Excel PERCENTILE（即PERCENTILE.INC）的计算逻辑 #

你使用的=PERCENTILE($A$2:$A$260,0.1)采用线性插值法计算百分位，具体步骤如下：

1. 将$A$2:$A$260的259个数值按升序排序；
2. 计算索引位置：i = (n-1)*p + 1，其中n=259（数据总数），p=0.1（10th百分位），代入得i=(259-1)*0.1+1=26.8；
3. 拆分索引为整数部分k=26和小数部分f=0.8；
4. 最终结果 = 第26位数值 + 小数部分×(第27位数值 - 第26位数值)。

结合你的数据场景：第26位数值为0，第27位数值为0.03左右，通过插值得到中间值0.027，这就是公式返回非原数据值的原因。

二、返回数据区域内最接近的实际值 #

如果要直接提取数据中存在的数值，可使用以下两种方法：

方法1：按位置取整后提取原数据 #

先计算百分位对应的近似位置，再提取排序后对应位置的数值：

• 取向下取整的位置：=INDEX(SORT($A$2:$A$260),ROUNDDOWN(259*0.1,0))，返回第25位的0；
• 取向上取整的位置：=INDEX(SORT($A$2:$A$260),ROUNDUP(259*0.1,0))，返回第26位的0；
• 若要找最接近插值结果（0.027）的原数据，可通过比较差值实现：

  =IF(ABS(0.027-INDEX(SORT($A$2:$A$260),26)) < ABS(0.027-INDEX(SORT($A$2:$A$260),27)),
      INDEX(SORT($A$2:$A$260),26),
      INDEX(SORT($A$2:$A$260),27))
  

  该公式会返回0.03（因0.027与0.03的差值更小）。

方法2：使用PERCENTILE.EXC（可选） #

PERCENTILE.EXC采用排除首尾值的百分位算法，结果会落在原数据范围内，无需插值，公式为：

=PERCENTILE.EXC($A$2:$A$260,0.1)

内容的提问来源于stack exchange，提问作者dsv054