Excel中条件格式中的百分点值和百分比之间的区分-英雄云拓展知识分享

Excel2016的条件格式中，在“基于各自值设置所有单元格的条件格式”规则类型中，最小值和最大值的选项都有“百分比”和“百分点值”，这两项有何不同呢?

一、不同点

条件格式中的“百分比”和“百分点值”都要针对一个百分数从数组中返回一个数值，这是它们的相同点，不同是计算方法不一样，自然含义也不同。

条件格式中的“百分比”的计算方法是：一个数组对给定的百分比，所返回的数值为：最小值+(最大值-最小值)×百分比，从算式中可以看出其返回值仅与数组中的最大值和最小值有关，而与其它值无关。

“百分点值”是用函数PERCENTILE.INC来计算的，其计算公式是：对给定的数组array和百分点k，返回：

=PERCENTILE.INC(array,k)

其计算原理是：对数组中的每一个值，都会依照从小到大的顺序给定一个百分位(基于n⑴)，假设数组有n个数值，这n个百分位分别是：0/(n⑴)、1/(n⑴)、2/(n⑴)……n⑴/(n⑴)，当k值与这些百分位相同时，即k是1/(n⑴)的倍数，直接返回数组中对应的数值，如果k不是 1/(n⑴) 的倍数，则 PERCENTILE.INC 使用插值法来肯定第k个百分点的值。插值法的原理见后面的示例。

2、示例

假设在某次诗词比赛中，有9名选手，他们各自的得分见下图。

为了比较各选手的得分情况，我们取默许的两个阀值：67%和33%，即用上下3分之一的刻度来划分得分范围，分别用“百分点值”和“百分比”来设置条件格式。下图为百分点值的条件格式设置方法。

结果以下。

从B列的“百分点值”条件格式中，可以看到9个数据被分割成3段后，每段各有3人(说明无并列)，得分靠前的1/3──即比2/3的选手得分高的是”乔峰“、”段誉“和”虚竹“。而从C列的“百分比”条件格式中，可以看到有5人处于前1/3，无疑“郭靖”和“周伯通”也都属于优秀选手;还可以看到这9位选手的得分显现出两极分化，由于中间3分之一分数段没有选手。

以下百分点值和百分比的对照图中，我们把67%和33%两个分割点用红色箭头标识出来了，2者的区分一目了然。

3、PERCENTILE.INC函数插值法的计算方法

PERCENTILE.INC函数插值法的原理以下图。

上图以k=0.67为例，由于0.67处于0.625-0.75之间，因此PERCENTILE.INC函数返回的百分点值将处于257.3⑵70.4之间，计算方法是：

257.3+(270.4⑵57.3)*(0.67-0.625)/0.125=262.016

其中0.125为1/(n⑴)。可以看出返回的百分点值(或百分位值)与数组中的每一个数值都有可能相干，改变其中的某个值可能会对返回的百分点值产生作用。

如果用公式来摹拟PERCENTILE.INC的计算方法，假设9个数值在B2:B10区域，k值在I3单元格，可以用下面的公式：

=SMALL(B2:B10,I3*(COUNT(B2:B10)⑴)+1)*(1-(I3-LOOKUP(I3,(ROW(B1:B9)⑴)/(COUNT(B2:B10)⑴)))*(COUNT(B2:B10)⑴))+SMALL(B2:B10,INT(I3*(COUNT(B2:B10)⑴)+2-INT(I3)))*(I3-LOOKUP(I3,(ROW(B1:B9)⑴)/(COUNT(B2:B10)⑴)))*(COUNT(B2:B10)⑴)

返回的百分点值(或称作百分位值)为262.016，说明数组中有67%的数比262.016小，或有33%的数比262.016大。

与PERCENTILE.INC函数相干的几个函数是PERCENTILE.EXC、QUARTILE.INC、QUARTILE.EXC和MEDIAN，QUARTILE.INC函数返回数据集的4分位数，MEDIAN函数返回数据集的中值(中位数)。

看完以上的分析，明白了条件格式中的百分点值和百分比有何不同吗?