Excel QUARTILE函数计算逻辑

从微软网站上找到的关于 Excel 上 QUARTILE() 函数的计算逻辑，与分位值的常规计算方式略有差异，因此记录下来，供需要时参考：

The following is the algorithm used to calculate QUARTILE():

1) Find the kth smallest member in the array of values, where:
k=(quart/4)*(n-1))+1

If k is not an integer, truncate it but store the fractional portion (f) for use in step 3. And where:

• quart = value between 0 and 4 depending on which quartile you want to find
• n = number of values in the array

2) Find the smallest data point in the array of values that is greater than the kth smallest – the (k+1)th smallest member.

3) Interpolate between the kth smallest and the (k+1)th smallest values:

Output = a[k]+(f*(a[k+1]-a[k])) a[k] = the kth smallest a[k+1] = the k+1th smallest

Example

To find the third quartile in the array of values 0,2,3,5,6,8,9, follow these steps:

1) Find k and f:

k=TRUNC((3/4*(7-1))+1)=5 f=(3/4*(7-1))-TRUNC(3/4*(7-1))=.5

2) The fifth (kth) smallest value is 6, and the (5+1)th smallest value is 8.

3) Interpolate:

6+(.5*(8-6))=7