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

                               

                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函数返回数据集的中值(中位数)。
        看完以上的分析，明白了条件格式中的百分点值和百分比有何不同吗?
                更多相关阅读
                用Excel条件格式中的图标集来可视化标识项目状态的方法
使用Excel 2010条件格式中的数据条功能直观显示差异数字
Excel2013表格中实现奇数行与偶数行填充不同颜色的方法
Excel条件格式出错率最高的一个问题：公式的引用
Excel 2010中让不同类型的数字以不同方式显示的方法