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)?？梢钥闯龇祷氐陌俜贮c值(或百分位值)與數組中的每個數值都有可能相關，改變其中的某個值可能會對返回的百分點值產生影響。

如果用公式來模擬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小時內刪除侵權內容。