條件格式中的百分點(diǎn)值和百分比之間的區(qū)別

一、不同點(diǎn)

條件格式中的“百分比”和“百分點(diǎn)值”都要根據(jù)一個(gè)百分?jǐn)?shù)從數(shù)組中返回一個(gè)數(shù)值，這是它們的相同點(diǎn)，不同是計(jì)算方法不一樣，自然含義也不同。

條件格式中的“百分比”的的計(jì)算方法是：一個(gè)數(shù)組對(duì)于給定的百分比，所返回的數(shù)值為：最小值+(最大值-最小值)×百分比，從算式中可以看出其返回值僅與數(shù)組中的最大值和最小值有關(guān)，而與其它值無(wú)關(guān)。

“百分點(diǎn)值”是用函數(shù)PERCENTILE.INC來(lái)計(jì)算的，其計(jì)算公式是：對(duì)于給定的數(shù)組array和百分點(diǎn)k，返回：

=PERCENTILE.INC(array,k)

其計(jì)算原理是：對(duì)于數(shù)組中的每個(gè)值，都會(huì)按照從小到大的順序給定一個(gè)百分位(基于n-1)，假如數(shù)組有n個(gè)數(shù)值，這n個(gè)百分位分別是：0/(n-1)、1/(n-1)、2/(n-1)……n-1/(n-1)，當(dāng)k值與這些百分位相同時(shí)，即k是1/(n-1)的倍數(shù)，直接返回?cái)?shù)組中對(duì)應(yīng)的數(shù)值，如果k不是 1/(n-1) 的倍數(shù)，則 PERCENTILE.INC 使用插值法來(lái)確定第k個(gè)百分點(diǎn)的值。插值法的原理見(jiàn)后面的示例。

二、示例

假如在某次詩(shī)詞比賽中，有9名選手，他們各自的得分見(jiàn)下圖。

為了比較各選手的得分情況，我們?nèi)∧J(rèn)的兩個(gè)閥值：67%和33%，即用上下三分之一的刻度來(lái)劃分得分范圍，分別用“百分點(diǎn)值”和“百分比”來(lái)設(shè)置條件格式。下圖為百分點(diǎn)值的條件格式設(shè)置方法。

結(jié)果如下。

從B列的“百分點(diǎn)值”條件格式中，可以看到9個(gè)數(shù)據(jù)被分割成3段后，每段各有3人(說(shuō)明無(wú)并列)，得分靠前的1/3──即比2/3的選手得分高的是”喬峰“、”段譽(yù)“和”虛竹“。而從C列的“百分比”條件格式中，可以看到有5人處于前1/3，無(wú)疑“郭靖”和“周伯通”也都屬于優(yōu)秀選手;還可以看到這9位選手的得分呈現(xiàn)出兩極分化，因?yàn)橹虚g三分之一分?jǐn)?shù)段沒(méi)有選手。

以下百分點(diǎn)值和百分比的對(duì)比圖中，我們把67%和33%兩個(gè)分割點(diǎn)用紅色箭頭標(biāo)識(shí)出來(lái)了，二者的區(qū)別一目了然。

三、PERCENTILE.INC函數(shù)插值法的計(jì)算方法

PERCENTILE.INC函數(shù)插值法的原理如下圖。

上圖以k=0.67為例，由于0.67處于0.625-0.75之間，因而PERCENTILE.INC函數(shù)返回的百分點(diǎn)值將處于257.3-270.4之間，計(jì)算方法是：

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

其中0.125為1/(n-1)?？梢钥闯龇祷氐陌俜贮c(diǎn)值(或百分位值)與數(shù)組中的每個(gè)數(shù)值都有可能相關(guān)，改變其中的某個(gè)值可能會(huì)對(duì)返回的百分點(diǎn)值產(chǎn)生影響。

如果用公式來(lái)模擬PERCENTILE.INC的計(jì)算方法，假如9個(gè)數(shù)值在B2:B10區(qū)域，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)

返回的百分點(diǎn)值(或稱(chēng)作百分位值)為262.016，說(shuō)明數(shù)組中有67%的數(shù)比262.016小，或者有33%的數(shù)比262.016大。

與PERCENTILE.INC函數(shù)相關(guān)的幾個(gè)函數(shù)是PERCENTILE.EXC、QUARTILE.INC、QUARTILE.EXC和MEDIAN，QUARTILE.INC函數(shù)返回?cái)?shù)據(jù)集的四分位數(shù)，MEDIAN函數(shù)返回?cái)?shù)據(jù)集的中值(中位數(shù))。