在管理學生名單或任何需要根據特定條件標示資料的場景中，Excel 的「設定格式化條件」功能都非常實用。今天我們要學習如何利用這個功能，結合 COUNTIF 函數，來自動標示學生繳交學生會費的狀況。我們的目標是：
已繳費的學生學號，其儲存格底色變為綠色。 繳費資料異常（例如重複繳費）的學生學號，其儲存格底色變為紅色。
若出現 1 次：代表正常繳費。 若出現 0 次：代表尚未繳費 (在此範例中我們不特別標色，但邏輯上已區分)。 若出現 大於 1 次：代表重複繳費，視為異常。
A 欄：完整的學生學號清單 (例如 A2 開始往下)。 E 欄：已繳交學生會費的學號清單 (例如 E2 開始往下，此清單可能會有重複或錯誤)。
選取目標範圍： 首先，選取 A 欄中所有學生學號的儲存格 (例如 A2:A109，根據您的實際資料範圍調整)。 開啟格式化條件管理員： 點選「常用」索引標籤 -> 「設定格式化條件」 -> 「管理規則」。 新增「異常繳費 (紅色)」規則： 在「設定格式化條件規則管理員」視窗中，點選「新增規則」。 選擇規則類型：「使用公式來決定要格式化哪些儲存格」。 在「格式化在此公式為 True 的值」欄位中，輸入以下公式 (假設您選取範圍的第一個儲存格是 A2，而已繳費名單範圍是 $E$2:$E$109)： 說明：$E$2:$E$109 使用了絕對參照 (錢字號 $），這樣當 Excel 套用此規則到 A 欄的其他儲存格時，參照的繳費名單範圍不會改變。A2 則是相對參照，它會隨著判斷的儲存格而改變 (例如判斷 A3 時，公式中的 A2 會自動變成 A3)。此公式的意義是：如果 A2 學號在 E2:E109 範圍內出現的次數大於 1 次，則條件成立。
點選下方的「格式...」按鈕。 在「設定儲存格格式」視窗中，切換到「填滿」頁籤，選擇紅色作為背景色，然後按「確定」。 回到「新增格式化規則」視窗，按「確定」。
新增「已繳費 (綠色)」規則： 再次點選「新增規則」。 選擇規則類型：「使用公式來決定要格式化哪些儲存格」。 輸入以下公式： 說明：此公式的意義是：如果 A2 學號在 E2:E109 範圍內出現的次數等於 1 次，則條件成立。
點選「格式...」按鈕。 在「設定儲存格格式」視窗中，切換到「填滿」頁籤，選擇綠色作為背景色，然後按「確定」。 回到「新增格式化規則」視窗，按「確定」。
調整規則順序與套用： 在「設定格式化條件規則管理員」視窗中，您會看到剛才建立的兩條規則。請確保「紅色 (異常)」的規則在「綠色 (已繳)」的規則之上。如果順序不對，可以選取規則後使用右側的上下箭頭調整。這是因為如果一個學號繳了兩次，它同時滿足 ">1" 和 "=1" (至少一次) 的條件，我們希望優先標示為異常。 確認「適用於」欄位顯示的是您當初選取的正確範圍 (例如 =$A$2:$A$109)。 點選「確定」或「套用」。
排序：選取包含標題的資料範圍，點選「資料」索引標籤 -> 「排序」。在「排序」對話框中，選擇「學號」欄，「排序方式」選擇「儲存格色彩」，然後選擇您想置於頂端或底端的顏色 (例如將紅色或綠色置於頂端)。 篩選：選取包含標題的資料範圍，點選「資料」索引標籤 -> 「篩選」。點擊「學號」欄標題旁的下拉箭頭，選擇「依色彩篩選」，然後選擇您想看到的顏色。例如，您可以只篩選出紅色的異常繳費資料進行核對。
沒有留言:
張貼留言
注意：只有此網誌的成員可以留言。