Excel中条件格式中的百分点值和百分比之间的区别

时间:2019-11-20
阅读:

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

一、不同点

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

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

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

=PERCENTILE.INC(array,k)

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

二、示例

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

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

结果如下。

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

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

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

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

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

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

其中0.125为1/(n-1)。可以看出返回的百分点值(或百分位值)与数组中的每个数值都有可能相关,改变其中的某个值可能会对返回的百分点值产生影响。

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

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

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

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

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

返回顶部
顶部