返回首页
当前位置: 主页 > Excel教程 > Excel2016教程 >

Excel中条件格式中的百分点值和百分比有何不同?

时间:2018-03-01 00:49来源:Office教程学习网 www.office68.com编辑:麦田守望者

 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函数插值法的原理如下图。

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函数返回数据集的中值(中位数)。

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

顶一下
(0)
0%
踩一下
(0)
0%
标签(Tag):excel excel2016 Excel条件格式
------分隔线----------------------------
------分隔线----------------------------
发表评论
请自觉遵守互联网相关的政策法规,严禁发布色情、暴力、反动的言论。
评价:
表情:
验证码:点击我更换图片
猜你感兴趣