新增、變更、尋找或清除條件化格式

使用設定格式化的條件,有助於您以視覺方式探索及分析資料、發現重大問題,及找出模式和趨勢。

設定格式化的條件可讓您輕鬆地醒目提示有趣的儲存格或儲存格範圍、強調不尋常的數值,以及使用資料橫條、色階和圖示集來視覺化資料。設定格式化的條件能根據條件 (或準則) 來變更儲存格範圍的外觀。條件為 True 時,會依據條件格式化儲存格範圍;若條件為 False,則不會依據條件格式化儲存格範圍。

無論您是以手動設定或以條件化設定儲存格的格式,都可以依格式進行排序和篩選,包括儲存格背景色彩和字型色彩等。下列範例顯示使用儲存格背景顏色、一個 3 三箭號圖示組和資料橫條的設定格式化的條件。

儲存格背景、圖示集及資料橫條皆為設定格式化的條件

您可以將設定格式化的條件套用至儲存格範圍、Excel 表格或樞紐分析表。您在樞紐分析表中使用設定格式化的條件時,務必了解其中不同之處。

附註: 建立設定格式化的條件時,您只能夠參照同一工作表中的其他儲存格,在特定情況下,則可以參照目前所開啟同一份活頁簿中多份工作表上的儲存格。您無法在另一個活頁簿的外部參照上使用設定格式化的條件。

為樞紐分析表設定格式化的條件

在樞紐分析表中的設定格式化的條件,與在儲存格範圍或 Excel 表格中的設定格式化的條件有所不同:

  • 如果您使用篩選、隱藏層級、摺疊及展開層級,或是移動欄位等方式,來變更樞紐分析表的版面配置,那麼只要未移除基本資料中的欄位,便會維持設定格式化的條件。

  • [值] 區域之欄位的格式化條件範圍,可以根據資料階層訂定,並由一欄或多欄的列之父項 (階層中的上一層),或是一列或多列的欄之父項的所有可見子項 (階層中的下一層) 來決定。

    附註: 在資料階層中,子項不會從父項繼承設定格式化的條件,父項也不會從子項繼承設定格式化的條件。

  • [值] 區域中有三種建立欄位格式化條件範圍的方法:依選取範圍、依對應欄位以及依值欄位。

[值] 區域中設定範圍欄位的預設方法是選取範圍。您可以使用 [套用格式化規則至] 選項按鈕、[新增格式化規則] 對話方塊或 [編輯格式化規則] 對話方塊,來變更對應欄位或 [值] 欄位的範圍設定方法。

方法

依您想要的選取情況,使用下列方法

依選取範圍建立範圍

  • [值] 區域中一組連續的欄位,例如一個區域中的所有產品加總。

  • [值] 區域中一組非連續的欄位,例如資料階層的層級間不同區域的產品加總。

依值欄位建立範圍

  • 避免多個非連續的選取範圍。

  • 在資料階層的所有層級中,條件化設定 [值] 區域中一組欄位的格式。

  • 包含小計和總計。

依對應欄位建立範圍

  • 避免多個非連續的選取範圍。

  • 設定格式化條件資料階層中,一個層級的 [值] 區域中的一組欄位。

  • 排除小計。

當您以最高、最低、高於平均值或低於平均值來條件化設定 [值] 區域中欄位的格式時,預設規則會根據所有可見的值來執行。不過,當您依對應欄位設定範圍,而非使用所有可見的值時,您可以套用下列每種組合的條件化格式:

  • 欄及其父項列欄位。

  • 列及其父項欄欄位。

附註: [快速分析] 並不適用於 Excel 2010 及先前的版本。

使用 [快速分析] 按鈕 [快速分析] 按鈕 以將選取的格式化條件套用至所選的資料。[快速分析] 按鈕會在您選取試算表中的資料時自動出現。

  1. 在您的試算表中選取資料。[快速分析] 按鈕隨即出現在選取範圍右下角。

    以 [快速分析] 按鈕選定的資料

  2. 按一下 [快速分析] 按鈕 [快速分析] 按鈕 ,或按 Ctrl+Q。

  3. 接著,在 [設定格式] 索引標籤上,將滑鼠移至不同的選項上方,以查看您資料的 [即時預覽],然後按一下格式化選項。

    [快速分析] 庫中的 [格式設定] 索引標籤

    附註: 

    • 出現在 [設定格式] 索引標籤中的格式化選項會依您所選的資料而定。如果您的選取項目只包含文字,則可用的選項為 [文字]、[重複]、[唯一]、[等於] 和 [清除]。當選取項目只包含數字或同時有文字和數字時,則選項為 [資料橫條]、[色彩]、[圖示集]、[大於]、[前 10%] 和 [清除]。

    • 即時預覽只會呈現可用於您資料的格式化選項。舉例來說,如果所選的儲存格不包含相符資料而您卻選取了 [重複] 時,即時預覽將沒有作用。

  4. 如果出現 [包含下列的文字] 對話方塊,請輸入您要套用的格式化選項,然後按一下 [確定]。

若需要更複雜的設定格式化的條件,您可使用邏輯公式指定條件格式的準則。例如,您可能想要將值與函數傳回的結果相互比較,或評估選取範圍外 (例如,同一活頁簿中其他工作表) 的儲存格資料。

  1. 在 [常用] 索引標籤的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,然後按一下 [管理規則]。

    設定格式化的條件

    [設定格式化的條件規則管理員] 對話方塊會隨即顯示。

  2. 請執行下列其中一項操作:

    • 若要新增格式化條件,請按一下 [新增規則]。[新增格式化規則] 對話方塊會隨即顯示。

    • 若要變更格式化條件,請執行下列動作:

      1. 確定已在 [顯示格式化規則] 清單方塊中選取了適當的工作表、表格或樞紐分析表。

      2. 您也可以選擇變更儲存格範圍,方式如下:按一下 [套用到] 方塊中的 [摺疊對話方塊] 按鈕影像 暫時隱藏對話方塊,並在工作表或其他工作表上選取新的儲存格範圍,然後選取 [展開對話方塊] 按鈕影像

      3. 選取規則,然後按一下 [編輯規則]。[編輯格式化規則] 對話方塊會隨即顯示。

  3. 在 [套用規則至] 下方,依下列項目選擇性地變更樞紐分析表 [值] 區域之欄位的範圍:

    • 依選取範圍:    按一下 [選取的儲存格]。

    • 依對應欄位:   按一下 [所有顯示 <值欄位> 值的儲存格]。

    • 依值欄位:   按一下 [所有顯示 <列> 的 <值欄位> 的儲存格]。

  4. 按一下 [選取規則類型] 底下的 [使用公式決定要格式化哪些儲存格]。

    1. 在 [編輯規則說明] 下方的 [格式化在此公式為 True 的值] 清單方塊中輸入公式。

      公式的開頭一定要是等號 (=),而且必須傳回 TRUE (1) 或 FALSE (0) 的邏輯值。

    2. 按一下 [格式] 以顯示 [儲存格格式] 對話方塊。

    3. 選取當儲存格值符合條件時所要套用的數值、字型、外框或填滿格式,然後按一下 [確定]。

      您可以選擇一種以上的格式。您所選取的格式會顯示在 [預覽] 方塊中。

      範例 1:使用兩個含有使用 AND 和 OR 測試之準則的條件化格式   

      此處所示的範例中,如果兩個條件皆為 True,第一條規則會將兩個儲存格的格式設定為綠色。如果測試的結果不為 True,亦即任一條件為 False 時,第二個規則會將兩個儲存格的格式設定為紅色。

      儲存格 B4 和 B5 符合條件,因此已將它們的格式設定為綠色

      家庭購買者家將頭期款的預算列為 $75,000,並將每個月的貸款預算列為 $1,500。如果頭期款和月付款皆符合這些要求,則將儲存格 B4 和 B5 的格式均設定為綠色。

      如果頭期款或月付款符合購買者的預算,則將 B4 和 B5 的格式接設定為紅色。變更部分數值,如年利率、貸款期限、頭期款和購買金額,以查看條件化設定的儲存格上有什麼變化。

      第一個規則的公式 (套用綠色)

      ==AND(IF($B$4<=75000,1),IF(ABS($B$5)<=1500,1))

      第二個規則的公式 (套用紅色)

      =OR(IF($B$4>=75000,1),IF(ABS($B$5)>=1500,1))

      範例 2:使用 MOD 及 ROW 函數,每隔一列加上網底   

      套用至此工作表中每個儲存格的格式化條件,會在儲存格範圍中每隔一個列加上網底色彩 (藍色儲存格色彩)。您可以按一下列 1 上方及欄 A 左邊的方形,即可選取工作表中的所有儲存格。MOD 函數會在被除數 (第一個引數) 除以除數 (第二個引數) 後傳回餘數。ROW 函數會傳回目前列號。將目前列號除以 2,永遠會得到偶數的餘數為 0,奇數的餘數為 1。因為 0 為 FALSE 而 1 為 TRUE,所以會格式化每一個奇數編號的列。規則會使用此公式:=MOD(ROW(),2)=1。

      每隔一列便加上藍色陰影

      附註: 您可以直接在工作表或其他工作表上選取儲存格,藉此在公式中輸入儲存格參照。選取工作表上的儲存格,就會插入絕對儲存格參照。如果您希望 Excel 為選取範圍中的每一個儲存格調整參照,請使用相對儲存格參照。

如需使用公式設定格式化條件的詳細資訊,請參閱使用公式套用設定格式化的條件

色階是一種視覺輔助,能協助您了解資料的分配及變化。雙色色階可協助您使用雙色漸層來比較儲存格範圍。色彩的濃淡代表較高值或較低值。例如,在綠 - 黃色階中,您可以指定較高值的儲存格使用較綠的色彩,而較低值的儲存格使用較黃的色彩。

使用雙色色階設定所有儲存格的格式

提示: 如果範圍中的某個儲存格含有傳回錯誤的公式,設定格式化的條件就不會套用到這些儲存格。若要確保設定格式化的條件會套用至那些儲存格,請使用 ISIFERROR 函數,以傳回非錯誤值的值。

快速格式

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,然後按一下 [色階]。

    設定格式化的條件

  3. 選取雙色色階。

    將游標移到色階圖示上面,查看哪一個圖示是雙色色階。上方的色彩代表較高值,下方的色彩代表較低值。

您可以使用套用設定格式化的條件之樞紐分析表欄位旁顯示的 [格式化選項] 按鈕,來變更樞紐分析表 [值] 區域中欄位的範圍界定方法。

進階格式設定

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,然後按一下 [管理規則]。[設定格式化的條件規則管理員] 對話方塊會隨即顯示。

  3. 請執行下列其中一項操作:

    • 若要新增格式化條件,請按一下 [新增規則]。[新增格式化規則] 對話方塊會隨即顯示。

    • 若要變更格式化條件,請執行下列動作:

      1. 確定已在 [顯示格式化規則] 清單方塊中選取了適當的工作表、表格或樞紐分析表。

      2. 您也可以選擇變更儲存格範圍,方式如下:按一下 [套用到] 方塊中的 [摺疊對話方塊] 按鈕影像 暫時隱藏對話方塊,並在工作表或其他工作表上選取新的儲存格範圍,然後選取 [展開對話方塊] 按鈕影像

      3. 選取規則,然後按一下 [編輯規則]。[編輯格式化規則] 對話方塊會隨即顯示。

  4. 在 [套用規則至] 下方,依下列項目選擇性地變更樞紐分析表 [值] 區域之欄位的範圍:

    • 依選取範圍:按一下 [選取的儲存格]。

    • 依 [值] 標籤的所有儲存格:按一下 [所有顯示 <值標籤> 值的儲存格]。

    • 依 [值] 標籤的所有儲存格,但不包括小計與總計:按一下 [所有顯示 <列標籤> 的 <值標籤> 值的儲存格]。

  5. 按一下 [選取規則類型] 底下的 [根據其值格式化所有儲存格] (預設)。

  6. 在 [編輯規則說明] 底下,選取 [格式樣式] 清單方塊中的 [雙色色階]。

  7. 若要在 [最小值] 與 [最大值] 的 [類型] 方塊中選取類型,請執行下列其中一項操作:

    • 設定最低值及最高值的格式:   選取 [最低值] 及 [最高值]。

      在這種情況下,不要輸入 [最小值] 及 [最大值] 的 [值]。

    • 設定數值、日期或時間值的格式:   選取 [數值],然後輸入 [最小值] 和 [最大值] 的 [值]。

    • 設定百分比的 [百分比] 格式:   輸入 [最小值] 和 [最大值] 的 [值]。

      有效的值是從 0 (零) 到 100。請不要輸入百分比符號。

      因為數值是按比例分配,所以想要依比例來視覺化所有數值時,請使用百分比。

    • 設定百分位數格式:   選取 [百分位數],然後輸入 [最小值] 及 [最大值] 的 [值]。有效的百分位數是從 0 (零) 到 100。

      如果想要使用某個色級比例來視覺化一組最高值 (例如前 20個百分位數),並且依另一個色級比例來視覺化一組最低值 (例如,後 20個百分位數),請使用百分位數,因為最高值及最低值都是代表極值,可能會影響資料視覺呈現的偏斜度。

    • 設定公式結果格式:   選取 [公式],然後輸入 [最小值] 和 [最大值] 的值。

      • 公式必須傳回數值、日期或時間值。

      • 公式是以等號 (=) 開頭。

      • 無效的公式不會套用任何格式。

      • 建議您先測試公式,確定不會傳回錯誤值。

        附註: 

        • 請確認 [最小值] 中的值小於 [最大值] 中的值。

        • 您可以選擇不同的 [最小值] 與 [最大值] 類型。例如,您可以選擇 [最小值] 是數值,而 [最大值] 是百分比。

  8. 若要選擇 [最小值] 及 [最大值] 色階,請分別按一下這些值的 [色彩],然後選取色彩。

    若要選擇其他色彩或建立自訂色彩,請按一下 [其他色彩]。您所選取的色階隨即會顯示在 [預覽] 方塊中。

色階是一種視覺輔助,能協助您了解資料的分配及變化。三色色階可協助您使用三色漸層來比較儲存格範圍。色彩的濃淡代表較高值或較低值。例如,在綠/黃/紅色階中,您可以指定較高值的儲存格使用綠色,中間值的儲存格使用黃色,而較低值的儲存格使用紅色。

提示: 如果範圍中的某個儲存格含有傳回錯誤的公式,設定格式化的條件就不會套用到這些儲存格。若要確保設定格式化的條件會套用至那些儲存格,請使用 ISIFERROR 函數來傳回您指定的值 (例如 0 或 "N/A"),才不會傳回錯誤值。

快速格式

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,然後按一下 [色階]。

    設定格式化的條件

  3. 選取三色色階。最上方的色彩代表較高值、中間的色彩代表中間值,最下方的色彩則代表較低值。

    將游標移到色階圖示上面,查看哪一個圖示是三色色階。

您可以使用套用設定格式化的條件之樞紐分析表欄位旁所顯示的 [格式化選項] 按鈕,變更樞紐分析表 [值] 區域中欄位的範圍界定方法。

進階格式設定

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,然後按一下 [管理規則]。[設定格式化的條件規則管理員] 對話方塊會隨即顯示。

  3. 請執行下列其中一項操作:

    • 若要新增格式化條件,請按一下 [新增規則]。[新增格式化規則] 對話方塊會隨即顯示。

    • 若要變更格式化條件,請執行下列動作:

      1. 確定已在 [顯示格式化規則] 清單方塊中選取了適當的工作表、表格或樞紐分析表。

      2. 您也可以選擇變更儲存格範圍,方式如下:按一下 [套用到] 方塊中的 [摺疊對話方塊] 按鈕影像 暫時隱藏對話方塊,並在工作表或其他工作表上選取新的儲存格範圍,然後選取 [展開對話方塊] 按鈕影像

      3. 選取規則,然後按一下 [編輯規則]。[編輯格式化規則] 對話方塊會隨即顯示。

  4. 在 [套用規則至] 下方,依下列項目選擇性地變更樞紐分析表 [值] 區域之欄位的範圍:

    • 依選取範圍:   按一下 [僅這些儲存格]。

    • 依對應欄位:   按一下 [具有相同欄位的所有 <值欄位> 儲存格]。

    • 依值欄位:   按一下 [所有 <值欄位> 儲存格]。

  5. 按一下 [選取規則類型] 底下的 [根據其值格式化所有儲存格]。

  6. 在 [編輯規則說明] 底下,選取 [格式樣式] 清單方塊中的 [三色色階]。

  7. 選取 [最小值]、[中間點] 及 [最大值] 的類型。請執行下列其中一項操作:

    • 設定最低值及最高值的格式:   選取 [中間點]。

      在這種情況下,不要輸入 [最低值] 及 [最高值] 的 [值]。

    • 設定數值、日期或時間值的格式:   選取 [數值],然後輸入 [最小值]、[中間點],及 [最大值] 的值。

    • 設定百分比的格式:   選取 [百分比],然後輸入 [最小值]、[中間點] 及 [最大值] 的值。有效的值是從 0 (零) 到 100。請不要輸入百分比 (%) 符號。

      因為數值是按比例分配,所以想要依比例來視覺化所有數值時,請使用百分比。

    • 設定百分位數的格式:   選取 [百分位數],然後輸入 [最小值]、[中間點] 及 [最大值] 的值。

      有效的百分位數是從 0 (零) 到 100。

      如果想要使用某個色級比例來視覺化一組最高值 (例如前 20個百分位數),並且依另一個色級比例來視覺化一組最低值 (例如,後 20個百分位數),請使用百分位數,因為最高值及最低值都是代表極值,可能會影響資料視覺呈現的偏斜度。

    • 設定公式結果的格式:   選取 [公式],然後輸入 [最小值]、[中間點],及 [最大值] 的值。

      公式必須傳回數值、日期或時間值。公式是以等號 (=) 開頭。無效的公式不會套用任何格式。建議您先測試公式,確定不會傳回錯誤值。

      附註: 

      • 您可以為儲存格範圍設定最小值、中間點值和最大值。請確認 [最小值] 中的值小於 [中間點] 中的值,而中間點中的值也要小於 [最大值] 中的值。

      • 您可以選擇不同的 [最小值]、[中間點] 與 [最大值] 類型。例如,您可以選擇 [最小值] 是數值,[中間點] 是百分位數,而 [最大值] 是百分比。

      • 在大多數情況下,預設的 [中間點] (為百分之 50) 效果最好,但您可以調整此值,使之符合特定的要求。

  8. 若要選擇 [最小值]、[中間點] 及 [最大值] 色階,請分別按一下 [色彩],然後選取色彩。

    • 若要選擇其他色彩或建立自訂色彩,請按一下 [其他色彩]。

    • 您所選取的色階隨即會顯示在 [預覽] 方塊中。

資料橫條可協助您查看與其他儲存格相對的儲存格值。資料橫條的長度代表儲存格中的值。資料橫條越長代表值越高,越短代表值越低。資料橫條很好用,特別在找出大量資料中的較高值及較低值的時候,例如假日銷售報表中最暢銷及最不暢銷的玩具。

此處所示的範例使用資料橫條以醒目提示較極端的正負值。您可以格式化資料橫條,讓資料橫條從儲存格中央開始,然後向左伸展為負值。

醒目提示正負值的資料橫條

提示: 如果範圍中的某個儲存格含有傳回錯誤的公式,設定格式化的條件就不會套用到這些儲存格。若要確保設定格式化的條件會套用至那些儲存格,請使用 ISIFERROR 函數來傳回值 (例如 0 或 "N/A"),才不會傳回錯誤值。

快速格式

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤上的 [樣式] 群組中,按一下 [設定格式化的條件] 旁邊的箭號,再按一下 [資料橫條],然後選取資料橫條圖示。

    設定格式化的條件

您可以使用 [套用格式化規則至] 選項按鈕,來變更樞紐分析表之 [值] 區域中欄位的範圍界定方法。

進階格式設定

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,然後按一下 [管理規則]。[設定格式化的條件規則管理員] 對話方塊會隨即顯示。

  3. 請執行下列其中一項操作:

    • 若要新增格式化條件,請按一下 [新增規則]。[新增格式化規則] 對話方塊會隨即顯示。

    • 若要變更格式化條件,請執行下列動作:

      1. 確定已在 [顯示格式化規則] 清單方塊中選取了適當的工作表、表格或樞紐分析表。

      2. 您也可以選擇變更儲存格範圍,方式如下:按一下 [套用到] 方塊中的 [摺疊對話方塊] 按鈕影像 暫時隱藏對話方塊,並在工作表或其他工作表上選取新的儲存格範圍,然後選取 [展開對話方塊] 按鈕影像

      3. 選取規則,然後按一下 [編輯規則]。[編輯格式化規則] 對話方塊會隨即顯示。

  4. 在 [套用規則至] 下方,依下列項目選擇性地變更樞紐分析表 [值] 區域之欄位的範圍:

    • 依選取範圍:   按一下 [僅這些儲存格]。

    • 依對應欄位:   按一下 [具有相同欄位的所有 <值欄位> 儲存格]。

    • 依值欄位:   按一下 [所有 <值欄位> 儲存格]。

  5. 按一下 [選取規則類型] 底下的 [根據其值格式化所有儲存格]。

  6. 在 [編輯規則說明] 底下,選取 [格式樣式] 清單方塊中的 [資料橫條]。

  7. 選取 [最小值] 及 [最大值] 的 [類型]。請執行下列其中一項操作:

    • 設定最低值及最高值的格式:   選取 [最低值] 及 [最高值]。

      在這種情況下,不要輸入 [最小值] 及 [最大值] 的值。

    • 設定數值、日期或時間值的格式:   選取 [數值],然後輸入 [最小值] 和 [最大值] 的 [值]。

    • 設定百分比的格式:   選取 [百分比],然後輸入 [最小值] 和 [最大值] 的值。

      有效的值是從 0 (零) 到 100。請不要輸入百分比 (%) 符號。

      因為數值是按比例分配,所以想要依比例來視覺化所有數值時,請使用百分比。

    • 設定百分位數的格式   選取 [百分位數],然後輸入 [最小值] 和 [最大值] 的值。

      有效的百分位數是從 0 (零) 到 100。

      如果想要使用某個資料橫條比例來視覺化一組最高值 (例如前 20個百分位數),並且依另一個資料橫條比例來視覺化一組最低值 (例如,後 20個百分位數),請使用百分位數,因為最高值及最低值都是代表極值,可能會影響資料視覺呈現的偏斜度。

    • 設定公式結果的格式   選取 [公式],然後輸入 [最小值] 和 [最大值] 的值。

      • 公式必須傳回數值、日期或時間值。

      • 公式是以等號 (=) 開頭。

      • 無效的公式不會套用任何格式。

      • 建議您先測試公式,確定不會傳回錯誤值。

    附註: 

    • 請確認 [最小值] 中的值小於 [最大值] 中的值。

    • 您可以選擇不同的 [最小值] 與 [最大值] 類型。例如,您可以選擇 [最小值] 是數值,而 [最大值] 是百分比。

  8. 若要選擇 [最小值] 和 [最大值] 色階,請按一下 [長條色彩]。

    若要選擇其他色彩或建立自訂色彩,請按一下 [其他色彩]。您所選取的長條色彩隨即會顯示在 [預覽] 方塊中。

  9. 若只要顯示資料橫條而非儲存格中的值,請選取 [僅顯示資料橫條]。

  10. 若要對資料橫條套用實心框線,請選取 [框線] 清單方塊中的 [實心框線],然後選擇框線的色彩。

  11. 若要選擇實心橫條或漸層橫條,請在 [填滿] 清單方塊中選擇 [實心填滿] 或 [漸層填滿]。

  12. 若要格式化負值橫條,請按一下 [負值和座標軸],然後在 [負值和座標軸設定] 對話方塊中,選擇負值橫條填滿與框線色彩的選項。您可以選擇座標軸的位置設定及色彩。選項選取完成之後,請按一下 [確定]。

  13. 您可以在 [橫條圖方向] 清單方塊中選擇設定,以變更橫條的方向。此設定預設為 [內容],不過您可以根據想要呈現資料的方式,選擇由左至右或由右至左的方向。

使用圖示集將資料加上附註,並將資料分類成三到五種由閾值分隔的類別。每個圖示分別代表一個值範圍。例如,在 [三箭號] 圖示集中,綠色向上箭號代表較高值,黃色橫向箭號代表中間值,紅色向下箭號代表較低值。

此處所示的範例使用多個圖示集設定格式化之條件的範例。

相同的資料搭配不同的圖示集

您可以選擇只針對符合條件的儲存格顯示圖示;例如,針對低於臨界值的儲存格顯示警告圖示,超過臨界值的儲存格則不顯示圖示。若要這麼做,當您設定條件時,請從圖示旁的圖示下拉式清單中選取 [無儲存格圖示],即可隱藏圖示。您也可以建立自己的圖示集組合;例如,綠色的「符號」核取記號、黃色的「交通號誌」和紅色的「標幟」。

提示: 如果範圍中的某個儲存格含有傳回錯誤的公式,設定格式化的條件就不會套用到這些儲存格。若要確保設定格式化的條件會套用至那些儲存格,請使用 ISIFERROR 函數來傳回值 (例如 0 或 "N/A"),才不會傳回錯誤值。

快速格式

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤上的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,再按一下 [圖示集],然後選取圖示集。

    設定格式化的條件

您可以使用 [套用格式化規則至] 選項按鈕,來變更樞紐分析表之 [值] 區域中欄位的範圍界定方法。

進階格式設定

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,然後按一下 [管理規則]。[設定格式化的條件規則管理員] 對話方塊會隨即顯示。

  3. 請執行下列其中一項操作:

    • 若要新增格式化條件,請按一下 [新增規則]。[新增格式化規則] 對話方塊會隨即顯示。

    • 若要變更格式化條件,請執行下列動作:

      1. 確定已在 [顯示格式化規則] 清單方塊中選取了適當的工作表、表格或樞紐分析表。

      2. 您也可以選擇變更儲存格範圍,方式如下:按一下 [套用到] 方塊中的 [摺疊對話方塊] 按鈕影像 暫時隱藏對話方塊,並在工作表或其他工作表上選取新的儲存格範圍,然後選取 [展開對話方塊] 按鈕影像

      3. 選取規則,然後按一下 [編輯規則]。[編輯格式化規則] 對話方塊會隨即顯示。

  4. 在 [套用規則至] 下方,依下列項目選擇性地變更樞紐分析表 [值] 區域之欄位的範圍:

    • 依選取範圍:   按一下 [僅這些儲存格]。

    • 依對應欄位:   按一下 [具有相同欄位的所有 <值欄位> 儲存格]。

    • 依值欄位:   按一下 [所有 <值欄位> 儲存格]。

  5. 按一下 [選取規則類型] 底下的 [根據其值格式化所有儲存格]。

  6. 在 [編輯規則說明] 底下,選取 [格式樣式] 清單方塊中的 [圖示集]。

    1. 選取一個圖示集。預設值為 [三交通號誌 (無框)]。圖示數量及每個圖示的預設比較運算子和閾值會因圖示集不同而有所差異。

    2. 您可以調整比較運算子及閾值。每個圖示的預設之值範圍的大小皆為相同,但您可以調整這些值,使它們符合特定的要求。請確認閾值由上到下的邏輯順序是最高值到最低值。

    3. 請執行下列其中一項操作:

      • 設定數值、日期或時間值的格式:   選取 [數值]。

      • 設定百分比的格式:   選取 [百分比]。

        有效的值是從 0 (零) 到 100。請不要輸入百分比 (%) 符號。

        因為數值是按比例分配,所以想要依比例來視覺化所有數值時,請使用百分比。

      • 設定百分位數格式:   選取 [百分位數]。有效的百分位數是從 0 (零) 到 100。

        如果想要使用某個圖示來視覺化一組最高值 (例如前 20個百分位數),並且依另一個圖示來視覺化一組最低值 (例如後 20個百分位數),請使用百分位數,因為最高值及最低值都是代表極端值,可能會影響資料視覺呈現的偏斜度。

      • 設定公式結果的格式:   選取 [公式],然後在每個 [值] 方塊中輸入公式中。

        • 公式必須傳回數值、日期或時間值。

        • 公式是以等號 (=) 開頭。

        • 無效的公式不會套用任何格式。

        • 建議您先測試公式,確定不會傳回錯誤值。

    4. 若要使第一個圖示表示較低值,最後一個圖示表示最高值,請選取 [反轉圖示順序]。

    5. 若只要顯示圖示而非儲存格中的值,請選取 [只顯示圖示]。

      附註: 

      1. 您可能需要調整欄寬,使其能容納圖示大小。

      2. 顯示的圖示大小取決於該儲存格中所使用的字型大小。如果放大字型的大小,則圖示大小也會依比例放大。

為了能更容易找到儲存格範圍內的特定儲存格,您可以根據比較運算子來格式化這些特定儲存格。例如,在依類別排序的庫存工作表中,您可以將少於 10 項的產品醒目提示為黃色。或者,在零售店摘要工作表中,您可以識別出收益大於 10%、銷售量少於 $100,000 且地區等於 "東南地區" 的所有商店。

此處所示的範例使用內建之設定格式化的條件準則的範例,例如「大於」及「前 %」。如此會將人口大於 2,000,000 之城市的格式設定為擁有綠色背景,並將平均高溫在前 30% 之城市的格式設定為擁有橘色背景。

格式設定顯示人口超過 2 百萬和高溫為前 30% 的城市

附註: 您無法依文字或日期來條件化設定樞紐分析表 [值] 區域中欄位的格式,只能依數值。

快速格式

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤上的 [樣式] 群組中,按一下 [設定格式化的條件] 旁邊的箭號,再按一下 [醒目提示儲存格規則]。

    設定格式化的條件

  3. 選取所要的命令,如 [介於]、[包含下列的文字] 或 [發生的日期]。

  4. 輸入您要使用的值,然後選取格式。

您可以使用 [套用格式化規則至] 選項按鈕,來變更樞紐分析表之 [值] 區域中欄位的範圍界定方法。

進階格式設定

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,然後按一下 [管理規則]。[設定格式化的條件規則管理員] 對話方塊會隨即顯示。

  3. 請執行下列其中一項操作:

    • 若要新增格式化條件,請按一下 [新增規則]。[新增格式化規則] 對話方塊會隨即顯示。

    • 若要變更格式化條件,請執行下列動作:

      1. 確定已在 [顯示格式化規則] 清單方塊中選取了適當的工作表、表格或樞紐分析表。

      2. 您也可以選擇變更儲存格範圍,方式如下:按一下 [套用到] 方塊中的 [摺疊對話方塊] 按鈕影像 暫時隱藏對話方塊,並在工作表或其他工作表上選取新的儲存格範圍,然後選取 [展開對話方塊] 按鈕影像

      3. 選取規則,然後按一下 [編輯規則]。[編輯格式化規則] 對話方塊會隨即顯示。

  4. 在 [套用規則至] 下方,依下列項目選擇性地變更樞紐分析表 [值] 區域之欄位的範圍:

    • 依選取範圍:   按一下 [僅這些儲存格]。

    • 依對應欄位:   按一下 [具有相同欄位的所有 <值欄位> 儲存格]。

    • 依值欄位:   按一下 [所有 <值欄位> 儲存格]。

  5. 按一下 [選取規則類型] 底下的 [只格式化包含下列的儲存格]。

  6. 在 [編輯規則說明] 底下的 [只格式化下列的儲存格] 清單方塊中,執行下列其中一項操作:

    • 依數值、日期或時間設定格式:   選取 [儲存格值],選取比較運算子,然後輸入數字、日期或時間。

      例如,選取 [介於],然後輸入 100200,或者選取 [等於],然後輸入 1/1/2009

      您也可以輸入會傳回數值、日期值或時間值的公式。

      • 如果輸入公式,請以等號 (=) 開頭。

      • 無效的公式不會套用任何格式

      • 建議您先測試公式,確定不會傳回錯誤值。

    • 設定文字格式:   選取 [特定文字]、選擇比較運算子,然後輸入文字。

      例如,選取 [包含] 然後輸入銀色,或者選取 [開頭以] 然後輸入

      在搜尋字串中加上引號,且可以使用萬用字元。字串最長可為 255 個字元。

      您也可以輸入會傳回文字的公式。

      • 如果輸入公式,請以等號 (=) 開頭。

      • 無效的公式不會套用任何格式。

      • 建議您先測試公式,確定不會傳回錯誤值。

    • 依日期設定格式:   選取 [發生日期],然後選取一個比較日期。

      例如,選取 [昨天] 或 [下週]。

    • 使用空白或無空白設定儲存格格式:   選取 [空白] 或 [無空白]。

      空白值就是不含資料的儲存格,這與含有一或多個空格 (空格一律視為文字) 的儲存格不同。

    • 使用錯誤值或無錯誤值設定儲存格格式:   選取 [錯誤] 或 [沒有錯誤]。

      錯誤值包括:#####、#VALUE!、#DIV/0!、#NAME?、#N/A、#REF!、#NUM! 及 #NULL!。

  7. 若要指定格式,請按一下 [格式]。[儲存格格式] 對話方塊即會出現。

  8. 選取當儲存格中值符合條件時,您想要套用的數值、字型、外框或填滿格式,然後按一下 [確定]。

    您可以選擇一種以上的格式。您所選取的格式會顯示在 [預覽] 方塊中。

您可以根據指定的截止值,找出儲存格範圍中的最高值及最低值。例如,您可以找出地區報表中前 5 名的暢銷產品、客戶調查中倒數 15% 的產品,或部門人員分析中前 25 名的薪資。

快速格式

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤上的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,再按一下 [頂端/底端項目規則]。

    設定格式化的條件

  3. 選取所要的命令,如 [前 10 個項目] 或 [最後 10 %]。

  4. 輸入您要使用的值,然後選取格式。

您可以使用 [套用格式化規則至] 選項按鈕,來變更樞紐分析表之 [值] 區域中欄位的範圍界定方法。

進階格式設定

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,然後按一下 [管理規則]。[設定格式化的條件規則管理員] 對話方塊會隨即顯示。

  3. 請執行下列其中一項操作:

    • 若要新增格式化條件,請按一下 [新增規則]。[新增格式化規則] 對話方塊會隨即顯示。

    • 若要變更格式化條件,請執行下列動作:

      1. 確定已在 [顯示格式化規則] 清單方塊中選取了適當的工作表、表格或樞紐分析表。

      2. 您也可以選擇變更儲存格範圍,方式如下:按一下 [套用到] 方塊中的 [摺疊對話方塊] 按鈕影像 暫時隱藏對話方塊,並在工作表或其他工作表上選取新的儲存格範圍,然後選取 [展開對話方塊] 按鈕影像

      3. 選取規則,然後按一下 [編輯規則]。[編輯格式化規則] 對話方塊會隨即顯示。

  4. 在 [套用規則至] 下方,依下列項目選擇性地變更樞紐分析表 [值] 區域之欄位的範圍:

    • 依選取範圍:   按一下 [僅這些儲存格]。

    • 依對應欄位:   按一下 [具有相同欄位的所有 <值欄位> 儲存格]。

    • 依值欄位:   按一下 [所有 <值欄位> 儲存格]。

  5. 按一下 [選取規則類型] 底下的 [只格式化排在最前面或最後面的值]。

  6. 在 [編輯規則說明] 底下,選取 [格式化下列範圍的值] 清單方塊中的 [前] 或 [後]。

  7. 請執行下列其中一項操作:

    • 若要指定最前面或最後面的數值,請輸入數值然後清除 [% 的選取範圍] 方塊。有效的值是從 1 到 1000。

    • 若要指定最前面或最後面的百分比,請輸入數字然後選取 [% 的選取範圍] 方塊。有效的值是從 1 到 100。

  8. (選擇性) 變更依對應欄位建立範圍的樞紐分析表中,[值] 區域之欄位格式套用的方式。

    根據預設,格式化條件會根據所有可見的值。不過,當您依對應欄位建立範圍,而非使用所有可見的值時,您可以選擇性地套用下列組合的格式化條件:

    • 欄及其父項欄位:選取 [每個欄群祖]。

    • 列及其父項欄位:選取 [每個列群組]。

  9. 若要指定格式,請按一下 [格式]。[儲存格格式] 對話方塊即會出現。

  10. 選取當儲存格中值符合條件時,您想要套用的數值、字型、外框或填滿格式,然後按一下 [確定]。

    您可以選擇一種以上的格式。您所選取的格式會顯示在 [預覽] 方塊中。

您可以找出儲存格範圍中高於或低於平均或標準差的值。例如,您可找出年度績效審查中高於平均的受評者,或品質評等中落在兩個標準差之下的製造材料。

快速格式

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤上的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,再按一下 [頂端/底端項目規則]。

    設定格式化的條件

  3. 選取所要的命令,如 [高於平均] 或 [低於平均]。

  4. 輸入您要使用的值,然後選取格式。

您可以使用 [套用格式化規則至] 選項按鈕,來變更樞紐分析表之 [值] 區域中欄位的範圍界定方法。

進階格式設定

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,然後按一下 [管理規則]。[設定格式化的條件規則管理員] 對話方塊會隨即顯示。

  3. 請執行下列其中一項操作:

    • 若要新增格式化條件,請按一下 [新增規則]。[新增格式化規則] 對話方塊會隨即顯示。

    • 若要變更格式化條件,請執行下列動作:

      1. 確定已在 [顯示格式化規則] 清單方塊中選取了適當的工作表、表格或樞紐分析表。

      2. 您也可以選擇變更儲存格範圍,方式如下:按一下 [套用到] 方塊中的 [摺疊對話方塊] 按鈕影像 暫時隱藏對話方塊,並在工作表或其他工作表上選取新的儲存格範圍,然後選取 [展開對話方塊] 按鈕影像

      3. 選取規則,然後按一下 [編輯規則]。[編輯格式化規則] 對話方塊會隨即顯示。

  4. 在 [套用規則至] 下方,依下列項目選擇性地變更樞紐分析表 [值] 區域之欄位的範圍:

    • 依選取範圍:   按一下 [僅這些儲存格]。

    • 依對應欄位:   按一下 [具有相同欄位的所有 <值欄位> 儲存格]。

    • 依值欄位:   按一下 [所有 <值欄位> 儲存格]。

  5. 按一下 [選取規則類型] 底下的 [只格式化高於或低於平均的值]。

  6. 在 [編輯規則說明] 底下的 [格式化下列的值] 清單方塊中,執行下列其中一項操作:

    • 若要格式化高於或低於範圍中所有儲存格平均值的儲存格,請選取 [高於] 或 [低於]。

    • 若要格式化範圍中所有儲存格高於或低於一個、兩個或三個標準差的儲存格,請選取標準差。

  7. (選擇性) 變更依對應欄位建立範圍的樞紐分析表中,[值] 區域之欄位格式套用的方式。

    依預設,條件化設定的格式是根據所有可見的值來執行。不過,當您依對應欄位建立範圍,而非使用所有可見的值時,您可以選擇性地套用下列組合的格式化條件:

    • 欄及其父項欄位:選取 [每個欄群祖]。

    • 列及其父項欄位:選取 [每個列群組]。

  8. 按一下 [格式] 以顯示 [儲存格格式] 對話方塊。

  9. 選取當儲存格中值符合條件時,您想要套用的數值、字型、外框或填滿格式,然後按一下 [確定]。

    您可以選擇一種以上的格式。您所選取的格式會顯示在 [預覽] 方塊中。

附註: 您不能依唯一值或重複值來條件化設定樞紐分析表 [值] 區域中欄位的格式。

此處所示的範例中,將設定格式化的條件用於「講師」欄以尋找授課班級超過一班的講師 (重複的講師名稱會以玫瑰色醒目提示)。只在「成績」欄中找到一次的成績值 (唯一值) 會以綠色醒目提示。

欄 C 中的重複值為玫瑰色,欄 D 中的唯一值為綠色。

快速格式

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤上的 [樣式] 群組中,按一下 [設定格式化的條件] 旁邊的箭號,再按一下 [醒目提示儲存格規則]。

    設定格式化的條件

  3. 選取 [重複的值]。

  4. 輸入您要使用的值,然後選取格式。

進階格式設定

  1. 選取範圍、表格或樞紐分析表中的一個或多個儲存格。

  2. 在 [常用] 索引標籤的 [樣式] 群組中,按一下 [設定格式化的條件] 旁的箭號,然後按一下 [管理規則]。[設定格式化的條件規則管理員] 對話方塊會隨即顯示。

  3. 請執行下列其中一項操作:

    • 若要新增格式化條件,請按一下 [新增規則]。[新增格式化規則] 對話方塊會隨即顯示。

    • 若要變更格式化條件,請執行下列動作:

      1. 確認 [顯示格式化規則] 清單方塊中已選取適當的工作表或表格。

      2. 您也可以選擇變更儲存格範圍,方式如下:按一下 [套用到] 方塊中的 [摺疊對話方塊] 按鈕影像 暫時隱藏對話方塊,並在工作表或其他工作表上選取新的儲存格範圍,然後選取 [展開對話方塊] 按鈕影像

      3. 選取規則,然後按一下 [編輯規則]。[編輯格式化規則] 對話方塊會隨即顯示。

  4. 按一下 [選取規則類型] 下的 [只格式化唯一或重複的值]。

  5. 在 [編輯規則說明] 下的 [格式化全部] 清單方塊中,選取 [唯一的] 或 [重複的]。

  6. 按一下 [格式] 以顯示 [儲存格格式] 對話方塊。

  7. 選取當儲存格中值符合條件時,您想要套用的數值、字型、外框或填滿格式,然後按一下 [確定]。

    您可以選擇一種以上的格式。您所選取的格式會顯示在 [預覽] 方塊中。

如果想要將現有的格式化樣式套用到您工作表上的新資料或其他資料,您可以使用 [複製格式] 以將格式化條件複製到該資料。

  1. 按一下具有要複製之格式化條件的儲存格。

  2. 按一下 [常用] > [複製格式]。

    常用索引標籤上的 [複製] 和 [貼上] 按鈕

    滑鼠指標隨即變成筆刷。

    提示: 如果您希望繼續使用筆刷圖示,將格式化條件貼到其他儲存格,可以按兩下 [複製格式]。

  3. 若要複製格式化條件,請在您想要設定格式的儲存格或儲存格範圍中拖曳筆刷圖示。

  4. 若要停止使用筆刷,請按下 Esc。

附註: 如果您已在套用設定格式化條件的規則中使用公式,可能必須在貼上格式化條件後,調整公式中的相對與絕對參照。如需詳細資訊,請參閱在相對、絕對與混合參照之間來回切換

清除工作表中設定格式化的條件   

  • 在 [常用] 索引標籤上,按一下 [設定格式化的條件] > [清除規則] > [清除整個工作表的規則]

清除範圍、表格或樞紐分析表中設定格式化的條件   

  1. 選取要清除條件化格式的儲存格、表格或樞紐分析表的範圍。

  2. 在 [常用] 索引標籤中,按一下 [設定格式化的條件] > [清除規則]。

  3. 根據所選的項目,按一下 [選取的儲存格]、[這個表格] 或 [此樞紐分析表]。

對特定函數有任何問題嗎?

在 Excel 社群論壇張貼問題

協助我們改進 Excel

您是否有任何關於下一版 Excel 的改善方式的建議?如果有的話,請參閱 Excel User Voice 中的主題。

請參閱

使用公式套用設定格式化的條件

管理設定格式化條件規則的優先順序

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×