Excel 條件格式自動醒目標示小計列/欄 + 定義名稱進階應用
報表中「小計」列/欄若能自動套用醒目格式(粗體、藍字),閱讀效率大增。結合條件格式與定義名稱,更能構建動態、可維護的試算表模型。
一、條件格式自動標示小計列與欄
假設資料區 A1:H11,第 1 列與 A 欄含「小計」文字標記。
- 選取整個資料區 A1:H11。
- 「開始」→「條件格式」→「新增規則」→ 「使用公式決定要格式化的儲存格」。
- 公式:
=($A1="小計")+(A$1="小計")>0
- 設定格式:字型→粗體、顏色→藍色、填滿→淺黃(依偏好)。
- 確定套用。
公式邏輯解析
- `$A1="小計"`:鎖定欄 A,相對列。判斷該列 A 欄是否為「小計」→ 標示整列。
- `A$1="小計"`:鎖定列 1,相對欄。判斷該欄第 1 列是否為「小計」→ 標示整欄。
- `+ 邏輯 OR`:任一為真(>0)即套用格式。等價 `=OR($A1="小計", A$1="小計")`。
二、定義名稱:不只是別名,是「有名字的公式」
「公式」→「定義名稱」中,「參考位置」永遠以 `=` 開頭,本質是命名公式。支援常數、函數、相對/絕對參考。
1. 常數名稱:簡化公式、集中維護
- 「公式」→「定義名稱」→ 名稱:`TaxRate` → 參考位置:`=5%`。
- 公式中直接用 `=TaxRate*B2` 計算稅額。
- 未來稅率變 3%,只需修改名稱定義,所有引用公式自動更新。
2. 文字常數名稱
名稱 `Company` 定義為 `="人民郵電出版社"`,公式用 `=Company` 直接輸出公司名。
3. 函數名稱:封裝複雜邏輯
定義名稱 `TotalScore` 參考位置:`=SUM(B2:G2)`(使用相對參考)。
- 在 H2 輸入 `=TotalScore` → 計算 B2:G2 總和。
- 複製到 H3 → 自動計算 B3:G3(相對參考隨作用儲存格移動)。
關鍵洞見:相對參考的動態威力
定義名稱用相對參考時,該名稱在不同儲存格被引用時,參考範圍會隨之相對移動。這讓「命名公式」成為可攜帶的計算邏輯模組,遠超單純的「儲存格別名」。
實務建議
- 命名規範:`範疇_用途`(如 `Tax_Rate`、`Report_Title`、`Calc_TotalRow`)。
- 管理名稱:「公式」→「名稱管理員」集中檢視、編輯、刪除、篩選。
- 文件化:在隱藏工作表建立「名稱說明表」記錄用途、公式、版本。
條件格式自動化呈現,定義名稱封裝邏輯,兩者結合讓試算表從「靜態資料表」進化為「動態資訊系統」。