如何在 Excel 中突出顯示基於百分位排名的數據

百分位等級表示特定數據值相對於樣本中其他數據值的地位。百分位數最常用於確定標準化測試的性能。如果一個孩子在標準化考試中得分在 90%，這意味著他或她的分數高於參加考試的其他孩子的 90%。

另一種看待它的方式是說孩子的分數在所有參加考試的孩子中排名前 10%。

百分位數通常用於數據分析中，作為衡量受試者相對於整個集團的表現的一種方法——例如，根據年收入確定每位員工的百分位數排名。

在 Excel 中，您可以使用 PERCENTILE 函數輕鬆獲得關鍵的百分位排名。此函數需要兩個參數：數據范圍和您想要查看的百分位數。

在顯示的示例中，單元格 D7 中的值是以下公式的結果，該公式根據范圍 B3:B20 中的數據提取第 75 個百分位數：

=PERCENTILE($B$3:$B$20,0.75)

這個公式告訴你，任何收入超過 52,651 美元的員工都屬於前 75% 的員工。

單元格 D16 中的值是以下公式的結果，該公式根據范圍 B3:B20 中的數據提取第 25 個百分位數：

=PERCENTILE($B$3:$B$20,0.25)

該公式告訴您，收入低於 24,656 美元的任何員工都屬於表現最差的 25%。

使用這些百分位標記，本示例應用條件格式，以便第 75 個百分位中的任何值將被著色為綠色，而第 25 個百分位中的任何值將被著色為紅色。

要構建此基本格式規則，請執行以下步驟：

選擇目標範圍內的數據單元格（本例中為單元格 B3:B20），單擊 Excel 功能區的“主頁”選項卡，然後選擇“條件格式”→“新建規則”。

這將打開“新建格式規則”對話框。

在對話框頂部的列錶框中，單擊使用公式確定要設置格式的單元格選項。

此選擇根據您指定的公式計算值。如果特定值的計算結果為 TRUE，則條件格式將應用於該單元格。

在公式輸入框中，輸入此步驟顯示的公式。

在此公式中，您將評估目標單元格 (B3) 中的數據是否在第 25 個百分位數內。如果是，則將應用條件格式。

=B3<>

請注意，在公式中，您排除了目標單元格 (B3) 的絕對引用美元符號 ($)。如果單擊單元格 B3 而不是鍵入單元格引用，Excel 會自動將單元格引用設為絕對引用。重要的是不要在目標單元格中包含絕對引用美元符號，因為您需要 Excel 根據每個單元格自己的值應用此格式規則。

單擊格式按鈕。

這將打開“單元格格式”對話框，您可以在其中使用一整套選項來設置目標單元格的字體、邊框和填充格式。完成格式選項的選擇後，單擊“確定”按鈕確認更改並返回“新建格式規則”對話框。

返回“新建格式規則”對話框，單擊“確定”按鈕以確認您的格式規則。

此時，您應該位於“條件格式規則管理器”對話框中。

單擊新建規則按鈕。

這將打開“新建格式規則”對話框。

在對話框頂部的列錶框中，單擊使用公式確定要設置格式的單元格選項。此選擇根據您指定的公式計算值。如果特定值的計算結果為 TRUE，則條件格式將應用於該單元格。

在公式輸入框中，輸入此處顯示的公式。

在此公式中，您要評估目標單元格 (B3) 中的數據是否在第 75 個百分位內。如果是，則將應用條件格式。

=B3>=PERCENTILE($B$3:$B$20,0.75)

單擊格式按鈕。

這將打開“單元格格式”對話框，您可以在其中使用一整套選項來設置目標單元格的字體、邊框和填充格式。完成格式選項的選擇後，單擊“確定”按鈕確認更改並返回“新建格式規則”對話框。

返回“新建格式規則”對話框，單擊“確定”按鈕以確認您的格式規則。

如果您需要編輯條件格式規則，只需將光標放在格式化範圍內的任何數據單元格中，然後轉到“主頁”選項卡並選擇“條件格式”→“管理規則”。這將打開條件格式規則管理器對話框。單擊要編輯的規則，然後單擊“編輯規則”按鈕。