分位数计算，分析Excel中函数实现原理

背景

Excel 2010及以上版本，分位数计算方法由PERCENTILE拆分为PERCENTILE.INC、PERCENTILE.EXC
WPS分位数计算方法为PERCENTILE

文档对比区别

PERCENTILE.INC
K  必需。 0 到 1 之间的百分点值，包含 0 和 1。
PERCENTILE.EXC
K    必需。 0 到 1 之间的百分点值，不包含 0 和 1。

测试结果
[0...10]      PERCENTILE.INC(A1:A11,0.25)  = 2.5
[0...10]      PERCENTILE.EXC(A1:A11,0.25)  = 2

Excel中测试

计算过程分析

[0...10]      PERCENTILE.INC(A1:A11,0.25) 
P25_address =（观察总数 - 1）* k + 1 = （11 - 1）* 0.25  + 1 = 3.5
i = P25_address取整数 = 3
j = P25_address取小数 = 0.5
P25_value = 第N次观察 +（第N+1次观察 - N次观察）× j = 2 + ( 3 - 2 ) * 0.5 = 2.5 

[0...10]      PERCENTILE.EXC(A1:A11,0.25) 
P25_address =（观察总数 + 1）* k = （11 + 1）* 0.25 = 3
i = P25_address取整数 = 3
j = P25_address取小数 = 0
P25_value = 第N次观察 +（第N+1次观察 - N次观察）× j = 2 + ( 3 - 2 )  * 0 = 2 

代码实现

import math

def percentile_inc(array,k):
    
    if len(array) == 0:
        return "数组不能为空"
    if k > 1 or k < 0:
        return "系数需为 0 到 1 之间的百分点值，包含 0 和 1"
    array_sort = sorted(array)

    address = (len(array_sort) - 1) * k + 1

    if address == len(array_sort):
        return array_sort[len(array_sort) - 1]

    i = int(math.modf(address)[1]) #取出整数部分
    j = math.modf(address)[0] #取出小数部分

    value = array_sort[i-1] + (array_sort[i] - array_sort[i-1]) * j
    #print("数组为：" + str(array), "系数为：" + str(k),"百分位数为：" + str(value))
    return value

def percentile_exc(array,k):
    
    if len(array) == 0:
        return "数组不能为空"
    if k >= 1 or k <= 0:
        return "系数需为 0 到 1 之间的百分点值，不包含 0 和 1 "
    array_sort = sorted(array)

    address = (len(array_sort) + 1) * k
    if address < 1:
        return "因系数过小，不能通过插入值来确定指定的百分点的值"

    i = int(math.modf(address)[1]) #取出整数部分
    j = math.modf(address)[0] #取出小数部分

    value = array_sort[i-1] + (array_sort[i] - array_sort[i-1]) * j
    #print("数组为：" + str(array), "系数为：" + str(k),"百分位数为：" + str(value))
    return value

print(percentile_inc([10,9,8,7,6,5,4,3,2,1,0],0))
print(percentile_inc([10,9,8,7,6,5,4,3,2,1,0],0.01))
print(percentile_inc([10,9,8,7,6,5,4,3,2,1,0],0.25))
print(percentile_inc([10,9,8,7,6,5,4,3,2,1,0],1))
print(percentile_inc([1,3,2,4],0.3)) # 官网测试数据

print(percentile_exc([10,9,8,7,6,5,4,3,2,1,0],0))
print(percentile_exc([10,9,8,7,6,5,4,3,2,1,0],0.01))
print(percentile_exc([10,9,8,7,6,5,4,3,2,1,0],0.09))
print(percentile_exc([10,9,8,7,6,5,4,3,2,1,0],0.25))
print(percentile_exc([1,2,3,6,6,6,7,8,9],0.25)) # 官网测试数据

参考资料

Excel PERCENTILE.INC 函数
Excel PERCENTILE.EXC 函数
Excel PERCENTILE.INC vs PERCENTILE.EXC