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

一、不同點

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

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

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

=PERCENTILE.INC(array,k)

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

二、示例

假如在某次詩詞比賽中，有9名選手，他們各自的得分見下圖。

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

結(jié)果如下。

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

以下百分點值和百分比的對比圖中，我們把67%和33%兩個分割點用紅色箭頭標識出來了，二者的區(qū)別一目了然。

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

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

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

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

其中0.125為1/(n-1)。可以看出返回的百分點值(或百分位值)與數(shù)組中的每個數(shù)值都有可能相關(guān)，改變其中的某個值可能會對返回的百分點值產(chǎn)生影響。

如果用公式來模擬PERCENTILE.INC的計算方法，假如9個數(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)

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

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