PDF转Excel:高效去除AI特征,实现数据自由编辑
1344
2022-09-06
Excel中条件格式中的百分点值和百分比之间的区别(怎么求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函数返回数据集的中值(中位数)。
看完以上的分析,明白了条件格式中的百分点值和百分比有何不同吗?
版权声明:本文内容由网络用户投稿,版权归原作者所有,本站不拥有其著作权,亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容,请联系我们jiasou666@gmail.com 处理,核实后本网站将在24小时内删除侵权内容。