[筆記] Excel VBA-1:如何使用Excel巨集執行多項格式化條件篩選

又是久違的一個月更新,在連續多篇的心靈雞湯後終於回歸到我想要寫的筆記系列。


之所以會想著手這篇文章,是因為這算是我寫VBA macro的初體驗。過去雖然有使用過Excel VBA的經驗,但基本上都是透過錄製來完成巨集的編輯,而這次則是我自己以撰寫程式的方式來完成Macro。姑且不論寫的codes真的非常醜,但只要能跑的Macro就是好Macro(笑)。

在開始說明我做了什麼前,我想先介紹這專案的背景:

(使用者故事卡片-A)

我作為一個產品營運專員,我希望可以更快速地讓我能整理客戶報表上的資料,讓其一目瞭然。

(使用者故事卡片-B, 完整版)

作為一個產品營運專員,我時常需要檢查客戶報表是否有錯(錯誤的值、錯誤的格式etc),然而報表上的大量資料讓我總得花費許多時間在一一檢查表格,我希望能找到更有效率的方式來整理數據。

在撰寫這VBA前,我的作法是每次檢視客戶的檔案時用Excel的格式化條件、Sort/Filter來整理資料,但實際上我要看的項目是固定的,而每次都要重新設定格式化條件實在有些浪費時間。另個困難是我要檢查多項條件,我想要同時檢查該格數字不等於A, 不等於B且不等於C — — 基本的格式化條件卻無法以聯集方式篩選A且B且C。

格式化條件聯集缺陷其實是Excel格式化條件中相當有趣的設定。

在Excel的格式化條件設定中包含了「優先順序」設定。舉例而言,在A行中,若我的第一個格式化設定條件為大於1的欄位必須標為紅色,而第二個格式化條件則是大於2的欄位必須標為綠色。那麼這兩個條件在Excel系統內會判定為衝突,因而只會應用優先順序較高的條件。

以下兩張截圖應該就可以很清楚看出差別為何。在截圖1中,因為大於1設定為紅色條件的優先順序為首位,因而條件2就自動被捨棄了。而在截圖2中,我將條件2的順序設定為首位,所以他會先將大於2的欄位標為綠色後再檢查條件1。

截圖1:當>1的條件設為優先
截圖2:當>2的條件設為首位

因為在多項條件下需要設定優先順序,與我本身想要達成的效果有些微出入,因而最終我決定以VBA的方式來完成目標。


請參考下方的截圖,示範用的Spreadsheet提供於文章最末。

我希望達成的目標是:水果欄位底下,除了蘋果與橘子外的所有東西都應該標示為「錯誤」(紅色)。

讓我們先從一個條件設定開始:

蘋果之外的任何表格都應標示為紅色。

說明版

再繼續往下之前,先提供大家一個Hack Tip, 若你像我一樣需要跑很多次VBA測試的話,你可能也對每次要手動清掉格式化的格子感到厭煩。你可以在 Range(“B2”).Select 與 Range(Selection, Selection.End(xlDown)).Select 中間加上一條清掉格式的Code:

Range(Selection, Selection.End(xlDown)).FormatConditions.Delete

這樣你就不用再手動清除格式化的格子了,因為每跑一次新的VBA, 它都會先清除完格子後再幫你檢查下面的條件內容。

進階到兩個條件:蘋果與橘子之外的任何表格都應該標示為紅色。

從一個條件到兩個條件的階段難度上升許多,這大概也是為什麼Stack Overflow 上大多數人都跟我一樣瘋狂搜尋”How to set up multiple conditions in conditional formatting using VBA” 當然,若你有更簡單的寫法,非常歡迎你在文章之下留言讓我知道!分享讓學習變得更有意義!

  • 當進階到兩個條件時,Operator在當中所扮演的角色會對VBA的邏輯有很大的影響。在此我嘗試使用 Or / And 的公式來連結兩項條件。
OR的功能如下:
Function (OR) =OR(Logical_test1, Logical_test2)
只要Logical_test1為真 Or Logical_test2為真,則顯示為TRUE
反之,若Logical_test1 AND Logical_test2 皆不為真,則顯示為FALSE
AND的功能如下:
Function (AND) =AND(Logical_test1, Logical_test2)
只要Logical_test1為真 AND Logical_test2為真,則顯示為TRUE
反之,若 Logical_test1 OR Logical_test2 (有一項)不為真,則顯示為FALSE
  • 若你想使用AND(且)的公式,則不符合(格子=蘋果 and 格子=橘子)的條件符合就會被標為紅色,但很明顯的不會有任何一個格子同時等於蘋果又等於橘子。所以AND的用法在此不能使用。換個角度想,那使用OR會有什麼結果呢?公式改為不符合(格子=蘋果 or 格子=橘子)的條件符合就會被標為紅色,確實是我們要的結果,但此時要設想另一個OR公式上的限制:

我們的條件格式化拿xCellValue(格子內的值)以 NotEqual (不等同於)來檢驗是否符合Formula的結果,但OR公式所吐出來的值是TRUE/ FALSE,所以除非格子內的值本身就是TRUE / FALSE,不然永遠都不會達成這項條件。白話文說明是,拿APPEL這格子來比較OR功能跑出來後的TRUE/FALSE結果永遠都不符合。

於是我需要再多包一個功能讓它能把TRUE的值轉換為原本的APPLE或是ORANGE, 這就用最簡單的功能IF來完成。

Function (IF) =IF(Logical_test,Value_if_true, Value_if_false)
若Logical_test為真,則顯示Value_if_true的值,若為假,則顯示Value_if_false的值

於是我將公式改寫為 IF(OR(B2=""APPLE"",B2=""ORANGE""), B2,FALSE)

  1. 先跑OR的功能:若 B2=APPLE 或 B2=ORANGE 任何一項條件完成則顯示TRUE
  2. 若(1)顯示為TRUE的話,那麼保留原本那一格的內容B2
  3. 若(1)顯示為FALSE的話,那麼顯示為FALSE

因此,條件式格式化所比較的就會是xlCellValue(被選擇的任一格)與原本格子內容(如果符合等於蘋果或是等於橘子的條件)或FALSE這個值(如果不符合條件)是否相同。所以若原本格子就是ORANGE或是APPLE的話就會符合這條件。但別忘了,我們所設定的目標是「排除」(NotEqual)- 因而最終呈現結果會是「不等於橘子與不等於蘋果的水果被標示出來」。

同理,三個條件的話就繼續往後加內容了。
例如: =IF(OR(B2=""APPLE"",B2=""ORANGE"",B2=""BANANA""), B2,FALSE)

說明版
完成版截圖
在此提供大家另外一種寫法,也這是我原本最開始的寫法。實際上條件式格式化的功能等同於最基本的IF功能,所以我們可以將條件格式化理解為IF, ELSE, THEN的段落。
思考脈絡為:
IF B2 不等於 蘋果
OR B2 不等於 橘子
THEN Nothing
ELSE 把格子變為紅色
==實際寫法(不額外說明,因為該寫法較好理解)==
Sub find_No_Apple_orange()
Range("B2:B").Select
Range(Selection, Selection.End(xlDown)).Select
If B2 = "APPLE" Or B2 = "ORANGE" Then
Else
Selection.Interior.Color = RGB(255, 199, 206) '只是改成用RGB色碼標示顏色而已
End If
End Sub
這與Conditional formatting的寫法有何差別呢?主要在於Excel本身功能上的差異:
  • Conditioanl Formatting是一個被內嵌在格子內的公式,隨著你條件符合或不符合,它會自動檢查更改顏色。
  • 而IF ElSE寫法則是直接選取符合條件的格子,將格子上色。但即便你之後改變了格子的內容,上色的格子會持續維持上色的狀態。

另外一個差異是,若你是用VBA來跑條件格式化的話,所有你的條件會顯示在「條件式格式化」功能下「管理條件」中。你可以改變不同條件想要顯示的顏色,並且快速設定。綜觀以上的比較,我最終選擇以Conditional_formatting的寫法來完成我這次的專案。

下一篇文章會繼續精煉這個VBA,將它改寫為「依照條件可自動搜尋格式化應用範圍」的功能。
Special thanks to Luis, and huge credit to my roommate Huli for helping me to finalize the VBA codes.
Like what you read? Give Aki a round of applause.

From a quick cheer to a standing ovation, clap to show how much you enjoyed this story.