萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 辦公室軟體 >  >> Excel 教學 >> Excel 綜合

Excel 條件格式結合公式實戰四例:驗證身分證、前三名、週末標示、間隔列著色

條件格式搭配公式,能實現一般規則無法達成的精準邏輯判斷。本文精選四大高頻實戰場景,手把手教學公式撰寫關鍵:以左上角儲存格為基準撰寫相對參考公式,讓你靈活套用至任何區域。

案例一:身分證字號長度驗證(綠色=正確長度)

需求:A2:A52 輸入身分證字號(15 或 18 碼),長度正確者儲存格填滿綠色。

  1. 選取 A2:A52 → 設定儲存格格式為「文字」(防止科學記號)。
  2. 「條件格式」→「新增規則」→「使用公式決定要格式化的儲存格」。
  3. 公式:=OR(LEN(A2)=15, LEN(A2)=18)
  4. 「格式」→「填滿」選綠色 → 確定層層關閉。

原理:公式以作用區域左上角 A2 為基準。Excel 內部自動將公式調整為 A3→LEN(A3)、A4→LEN(A4)... 實現逐列判斷。

新增規則對話框輸入公式

設定綠色填滿

完成規則設定

測試效果:15/18碼變綠,其餘不變

刪除條件格式步驟

案例二:找出銷售額前三名(藍字標示)

需求:B2:B12 銷售額,前三名藍字顯示,保持原排序不變。

  • 選取 B2:B12 → 新增規則 → 公式:=B2>LARGE($B$2:$B$12,3) (或 =B2>=LARGE($B$2:$B$12,3) 含並列)。
  • 格式設定字型顏色藍色、粗體。

優勢:不改變資料排序,原始時間/區域順序完整保留。

前三名藍字效果

案例三:週末日期自動陰影(六/日灰底)

需求:A2:A12 日期欄,週六、週日灰底提醒。

  • 選取 A2:A12 → 新增規則 → 公式:=OR(WEEKDAY(A2,2)=6, WEEKDAY(A2,2)=7)
  • 格式設定填滿灰色(或淺藍)。

解析:WEEKDAY(日期, 2) 回傳 1(一)~7(日),故 6=六、7=日。

案例四:間隔列著色(每隔 N 列一色)

需求:大量資料表,隔列灰底輔助閱讀。

  • 選取整個資料區域(含標題列建議排除)。
  • 新增規則 → 公式:=MOD(ROW(),2)=0 (偶數列著色)。
  • 隔兩列:=MOD(ROW(),3)=0,以此類推。

解析:ROW() 取得儲存格列號,MOD(列號, 間隔數)=0 判斷是否為間隔列。若資料不從第 1 列開始,需調整:=MOD(ROW()-起始列號, 間隔數)=0。

間隔列著色效果

公式撰寫黃金法則

  1. 選取區域左上角儲存格為「主動儲存格」。
  2. 公式中所有參考均以該儲存格為基準(相對參考)。
  3. 需固定參考的區域(如查找表、常數)加 $ 鎖定。
  4. 完成後,可檢查區域內其他儲存格的條件格式公式,確認自動調整正確。
copyright © 萬盛學電腦網 all rights reserved