[原創] 淺談條件格式化

Hsieh

近日發現一個現象，使用者在設定格式化條件時，對於規則設定似乎有所盲點。
在此做些簡單的說明條件規則公式的技巧。
格式化條件的公式，最重要是格式化公式的參照必須正確
一、判斷儲存格的值為條件
如圖有一個資料表，以1~100的亂數為資料，我們要找出其中偶數的儲存格，將其儲存格填滿黃色
操作步驟:
1. 選取A2:D21儲存格範圍，此時作用儲存格為A2
2. 使用公式來決定要格式化哪些儲存格
3. 公式輸入=MOD(A2,2)=0
思考邏輯:
因為我們要找出所有偶數儲存格，所以格式化公式是針對每個儲存格本身的值做判斷
所以，公式是以欄列參照均為相對參照
作用中儲存格是A2時，公式中要判斷的位址是A2
作用中儲存格是A3時，公式中要判斷的位址是A3
作用中儲存格是B2時，公式中要判斷的位址是B2
所以格式化條件會隨著作用中儲存格，改變判斷儲存格

下載 (50.42 KB)

2010-11-29 20:34

二、判斷同列某欄位的值為條件
假設表格有4欄位(L2:O18)，要依據最左邊的日期欄位做判斷，判斷該日期為周六日則將整列4個儲存格均以紅色填滿
操作步驟:
1.選取L2:O18，此時作用儲存格為L2
2. 使用公式來決定要格式化哪些儲存格
3. 公式輸入=WEEKDAY($L2,2)>=6
思考邏輯:
因為要做判斷依據的儲存格，不論在同列中哪一個欄位，都必須參照到同一個日期欄位
所以當作用儲存格為L2，要判斷的日期就是L2
當作用儲存格為M2，要判斷的日期還是L2
當作用儲存格為N2，要判斷的日期還是L2
當作用儲存格為O2，要判斷的日期還是L2
當作用儲存格為L3，要判斷的日期還是L3
當作用儲存格為M3，要判斷的日期還是L3
由此可知，公式要判斷的儲存格，會隨列位不同而改變，但對欄位而言是始終都是L欄
所以公式中欄位參照採用絕對參照，列為採相對參照

下載 (49.21 KB)

2010-11-29 22:01

若以上例表格轉置成4列的資料
相同邏輯，只不過是將原本以欄為絕對參照，列為相對參照
改變成已列為絕對參照，欄改為相對參照。如圖

下載 (45.11 KB)

2010-11-29 22:24

今天就先以最簡單的判斷說明，有時間再繼續 格式條件化.zip (119.6 KB)

下載次數: 33

2010-11-29 22:28

fr447446

設定格式化條件算是實務上常用的功能之ㄧ，
小弟剛接觸EXCEL時也常因搞不清楚何時用絕對/相對 參照，
而造成許多的困擾，此篇對剛接觸EXCEL的新手有相當的幫助。
感謝 Hsieh 版大分享~

takachang

學習了感謝分享這麼精闢的教學

fantersy

感謝分享~小弟常在輸入公式的地方犯錯
在條件格式化內容輸入時
不像在儲存格輸入會自動幫你校正括號
所以有點麻煩...>.<