Excel中條件格式中的百分點(diǎn)值和百分比之間的區(qū)別（怎么求excel中的百分比）

Excel中條件格式中的百分點(diǎn)值和百分比之間的區(qū)別（怎么求excel中的百分比）

Excel2016的條件格式中，在“基于各自值設(shè)置所有單元格的條件格式”規(guī)則類型中，最小值和最大值的選項(xiàng)都有“百分比”和“百分點(diǎn)值”，這兩項(xiàng)有何不同呢?

一、不同點(diǎn)

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

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

“百分點(diǎn)值”是用函數(shù)PERCENTILE.INC來計算的，其計算公式是：對于給定的數(shù)組array和百分點(diǎn)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)，當(dāng)k值與這些百分位相同時，即k是1/(n-1)的倍數(shù)，直接返回數(shù)組中對應(yīng)的數(shù)值，如果k不是 1/(n-1) 的倍數(shù)，則 PERCENTILE.INC 使用插值法來確定第k個百分點(diǎn)的值。插值法的原理見后面的示例。

二、示例

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

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

結(jié)果如下。

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

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

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

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

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

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

其中0.125為1/(n-1)。可以看出返回的百分點(diǎn)值(或百分位值)與數(shù)組中的每個數(shù)值都有可能相關(guān)，改變其中的某個值可能會對返回的百分點(diǎ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)

返回的百分點(diǎn)值(或稱作百分位值)為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ù))。

看完以上的分析，明白了條件格式中的百分點(diǎn)值和百分比有何不同嗎?

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時內(nèi)刪除侵權(quán)內(nèi)容。