組篩選進(jìn)階：超越原生Filter，實(shí)現(xiàn)多條件、多模式與多維數(shù)組處理)
1. 項(xiàng)目概述從“能用”到“好用”的VBA數(shù)組篩選之路在VBA的日常開發(fā)里處理數(shù)組篩選是家常便飯。系統(tǒng)自帶的Filter函數(shù)用起來確實(shí)順手一句Filter(源數(shù)組, 匹配字符串, 包含, 比較模式)就能快速返回一個(gè)包含子串的結(jié)果數(shù)組。但用久了尤其是在處理稍微復(fù)雜點(diǎn)的數(shù)據(jù)清洗、報(bào)表生成或者自動(dòng)化核對任務(wù)時(shí)它的“脾氣”和“局限”就暴露無遺了。最讓人頭疼的莫過于它只能進(jìn)行基于字符串的“模糊包含”匹配你想按精確相等、數(shù)值范圍、多條件“與/或”邏輯來篩對不起Filter函數(shù)攤攤手表示辦不到。另一個(gè)坑是它的返回結(jié)果永遠(yuǎn)是一維的字符串?dāng)?shù)組如果你的源數(shù)據(jù)是二維的或者里面混著數(shù)字、日期Filter要么報(bào)錯(cuò)要么給你轉(zhuǎn)成字符串后續(xù)處理起來非常別扭。所以當(dāng)項(xiàng)目里又一次因?yàn)镕ilter無法實(shí)現(xiàn)“篩選出金額大于1000且部門為‘銷售部’的記錄”而不得不寫一長串循環(huán)時(shí)我決定不再將就。這次的目標(biāo)很明確自己動(dòng)手寫一個(gè)能完全模仿Filter基礎(chǔ)功能同時(shí)又能突破其核心局限的自定義函數(shù)。它不僅要能處理Filter能做的字符串包含篩選更要搞定Filter做不了的精確匹配、多條件、非字符串?dāng)?shù)據(jù)類型、多維數(shù)組篩選。這不僅僅是再造一個(gè)輪子而是打造一個(gè)更適應(yīng)復(fù)雜現(xiàn)實(shí)場景的“瑞士軍刀”。2. 核心需求與設(shè)計(jì)思路拆解2.1 深入剖析原生Filter函數(shù)的“阿喀琉斯之踵”在動(dòng)手之前必須先把標(biāo)準(zhǔn)Filter函數(shù)的局限性摸透這樣才能有的放矢。它的局限主要體現(xiàn)在四個(gè)方面匹配模式單一且不可控這是最根本的局限。Filter函數(shù)本質(zhì)上是一個(gè)“字符串包含”篩選器。它的Match參數(shù)必須是一個(gè)字符串函數(shù)的行為就是在源數(shù)組的每個(gè)元素中查找是否包含這個(gè)字符串。你無法將其改為“等于”、“以...開頭”、“以...結(jié)尾”或“正則表達(dá)式匹配”。例如你想從數(shù)組[“蘋果”, “香蕉”, “葡萄”]中精確找出“蘋果”用Filter(arr, “蘋果”, True, vbTextCompare)會(huì)把包含“蘋果”字的都找出來如果存在“紅蘋果”也會(huì)被選中而這往往不是我們想要的。數(shù)據(jù)類型強(qiáng)制轉(zhuǎn)換與丟失Filter的輸入數(shù)組和輸出數(shù)組都被強(qiáng)制限定為字符串類型Variant/String。如果你傳入一個(gè)包含數(shù)字、日期、布爾值的數(shù)組VBA會(huì)隱式地將它們?nèi)哭D(zhuǎn)換為字符串再進(jìn)行匹配。這會(huì)導(dǎo)致兩個(gè)問題一是精度可能丟失例如數(shù)字123.456可能變成“123.456”二是匹配邏輯變得詭異。比如你想從[123, 234, 345]中篩選出包含“23”的元素Filter會(huì)返回[123, 234]但這本質(zhì)是在字符串“123”和“234”中查找“23”而非數(shù)值比較。更麻煩的是輸出結(jié)果是一個(gè)一維字符串?dāng)?shù)組原始的數(shù)據(jù)類型信息徹底丟失給后續(xù)計(jì)算帶來隱患。無法處理多維數(shù)組Filter函數(shù)只接受一維數(shù)組作為輸入。在實(shí)際業(yè)務(wù)中我們經(jīng)常從Excel的Range.Value屬性直接得到一個(gè)二維數(shù)組或者自己構(gòu)建多維數(shù)組來存儲(chǔ)表格數(shù)據(jù)。面對這樣的數(shù)據(jù)源Filter函數(shù)會(huì)直接拋出“類型不匹配”或“下標(biāo)越界”的錯(cuò)誤迫使開發(fā)者必須先通過循環(huán)將其展平為一維篩選后再重組過程繁瑣且低效。多條件篩選能力缺失現(xiàn)實(shí)場景中的篩選很少是單條件的。Filter函數(shù)一次只能接受一個(gè)匹配字符串和一個(gè)是否包含的邏輯。要實(shí)現(xiàn)“條件A與條件B”或者“條件A或條件B”必須多次調(diào)用Filter并進(jìn)行數(shù)組間的交集或并集操作代碼會(huì)變得冗長且難以維護(hù)。2.2 自定義函數(shù)SuperFilter的頂層設(shè)計(jì)基于以上痛點(diǎn)我設(shè)計(jì)的自定義函數(shù)SuperFilter需要實(shí)現(xiàn)以下核心目標(biāo)功能目標(biāo)兼容模式完全復(fù)刻原生Filter函數(shù)的所有參數(shù)和功能作為保底選項(xiàng)。擴(kuò)展模式提供多種匹配模式精確、開頭、結(jié)尾、包含、正則支持?jǐn)?shù)值/日期等非字符串類型的直接比較。多條件支持允許傳入一個(gè)條件數(shù)組并指定條件間的邏輯關(guān)系A(chǔ)ND 或 OR。多維數(shù)組支持能夠直接處理二維數(shù)組并可以指定按行篩選還是按列篩選返回一個(gè)結(jié)構(gòu)保持的二維子數(shù)組。數(shù)據(jù)類型保持輸出數(shù)組的元素?cái)?shù)據(jù)類型盡量與輸入數(shù)組對應(yīng)元素保持一致。接口設(shè)計(jì)Function SuperFilter(SourceArray As Variant, _ Match As Variant, _ Optional Include As Boolean True, _ Optional Compare As VbCompareMethod vbBinaryCompare, _ Optional MatchMode As String “contains”, _ Optional ConditionLogic As String “AND”, _ Optional Dimension As Long 1) As VariantSourceArray: 源數(shù)組接受一維或二維Variant數(shù)組。Match: 匹配條件。可以是單個(gè)值字符串、數(shù)字、日期等也可以是一個(gè)一維條件數(shù)組。Include: 是否包含。True返回匹配項(xiàng)False返回不匹配項(xiàng)與Filter一致。Compare: 字符串比較模式vbBinaryCompare區(qū)分大小寫或vbTextCompare不區(qū)分。MatchMode: 匹配模式?？蛇x“exact”精確、“startswith”開頭、“endswith”結(jié)尾、“contains”包含默認(rèn)、“regex”正則表達(dá)式。ConditionLogic: 當(dāng)Match為數(shù)組時(shí)條件間的邏輯。“AND”所有條件都滿足或“OR”任一條件滿足。Dimension: 對二維數(shù)組的篩選維度。1表示按行篩選返回符合條件的整行2表示按列篩選。內(nèi)部邏輯流程輸入驗(yàn)證與初始化檢查SourceArray是否是數(shù)組判斷其維數(shù)。根據(jù)維數(shù)和Dimension參數(shù)確定循環(huán)遍歷的方式是遍歷行還是列。條件預(yù)處理將Match參數(shù)統(tǒng)一處理為一個(gè)條件數(shù)組condArr()并確定匹配模式MatchMode。如果是多條件需明確ConditionLogic。核心遍歷與匹配判斷遍歷源數(shù)組的每一個(gè)目標(biāo)元素可能是一行、一列或一個(gè)單元素。對每個(gè)元素根據(jù)MatchMode調(diào)用對應(yīng)的匹配判斷函數(shù)并結(jié)合ConditionLogic計(jì)算最終是否匹配。結(jié)果收集與數(shù)組構(gòu)建動(dòng)態(tài)收集所有匹配項(xiàng)的索引。遍歷結(jié)束后根據(jù)收集的索引從源數(shù)組中提取對應(yīng)元素/行/列構(gòu)建一個(gè)新的、結(jié)構(gòu)正確的Variant數(shù)組作為結(jié)果返回。錯(cuò)誤處理在整個(gè)過程中加入必要的錯(cuò)誤捕獲例如無效的匹配模式、維度參數(shù)錯(cuò)誤、正則表達(dá)式編譯失敗等并返回一個(gè)可識(shí)別的錯(cuò)誤值如Empty數(shù)組或拋出描述性錯(cuò)誤。3. 核心細(xì)節(jié)解析與關(guān)鍵技術(shù)實(shí)現(xiàn)3.1 多模式匹配引擎的實(shí)現(xiàn)這是SuperFilter函數(shù)的核心也是與原生Filter最大的區(qū)別。我們需要一個(gè)統(tǒng)一的判斷函數(shù)它能根據(jù)MatchMode參數(shù)采用不同的邏輯進(jìn)行比較。首先定義一個(gè)內(nèi)部幫助函數(shù)IsMatchPrivate Function IsMatch(ByVal SourceValue As Variant, _ ByVal ConditionValue As Variant, _ ByVal MatchMode As String, _ ByVal CompareMethod As VbCompareMethod) As Boolean ‘ 處理空值情況 If IsEmpty(SourceValue) Or IsEmpty(ConditionValue) Then IsMatch False Exit Function End If Dim srcStr As String, condStr As String Dim regex As Object On Error GoTo ErrorHandler Select Case LCase(Trim(MatchMode)) Case “exact” ‘精確匹配 If VarType(SourceValue) vbString And VarType(ConditionValue) vbString Then IsMatch (StrComp(SourceValue, ConditionValue, CompareMethod) 0) Else ‘ 非字符串類型使用等號(hào)直接比較注意Variant的比較方式 IsMatch (SourceValue ConditionValue) End If Case “startswith” ‘開頭匹配 srcStr CStr(SourceValue) condStr CStr(ConditionValue) If CompareMethod vbTextCompare Then IsMatch (InStr(1, srcStr, condStr, vbTextCompare) 1) Else IsMatch (Left(srcStr, Len(condStr)) condStr) End If Case “endswith” ‘結(jié)尾匹配 srcStr CStr(SourceValue) condStr CStr(ConditionValue) If CompareMethod vbTextCompare Then ‘ 通過StrComp比較末尾部分稍微復(fù)雜一點(diǎn) IsMatch (StrComp(Right(srcStr, Len(condStr)), condStr, CompareMethod) 0) Else IsMatch (Right(srcStr, Len(condStr)) condStr) End If Case “contains” ‘包含匹配 (模擬原生Filter) srcStr CStr(SourceValue) condStr CStr(ConditionValue) IsMatch (InStr(1, srcStr, condStr, CompareMethod) 0) Case “regex” ‘正則表達(dá)式匹配 Set regex CreateObject(“VBScript.RegExp”) regex.Pattern CStr(ConditionValue) regex.IgnoreCase (CompareMethod vbTextCompare) regex.Global False IsMatch regex.Test(CStr(SourceValue)) Set regex Nothing Case Else ‘ 默認(rèn)使用包含匹配 srcStr CStr(SourceValue) condStr CStr(ConditionValue) IsMatch (InStr(1, srcStr, condStr, CompareMethod) 0) End Select Exit Function ErrorHandler: ‘ 例如正則表達(dá)式模式無效 IsMatch False End Function關(guān)鍵點(diǎn)與避坑指南數(shù)據(jù)類型處理在“exact”模式下我們優(yōu)先判斷是否為字符串是則用StrComp函數(shù)以支持大小寫敏感控制否則直接用等號(hào)比較。這比原生Filter全部轉(zhuǎn)字符串更合理。性能考量正則表達(dá)式對象的創(chuàng)建和銷毀CreateObject和Set regex Nothing有一定開銷。如果在一個(gè)大數(shù)據(jù)集的循環(huán)中頻繁使用“regex”模式會(huì)成為性能瓶頸?？梢钥紤]將正則對象創(chuàng)建移到主函數(shù)中作為可選參數(shù)傳入但會(huì)增大接口復(fù)雜度。對于一般應(yīng)用在IsMatch內(nèi)部創(chuàng)建是清晰簡單的做法?？罩堤幚鞩sEmpty判斷至關(guān)重要。VBA數(shù)組中經(jīng)常存在Empty值直接對其進(jìn)行字符串轉(zhuǎn)換或比較可能導(dǎo)致類型不匹配錯(cuò)誤。這里選擇將任何一方為Empty的匹配直接判定為False邏輯上更安全。3.2 動(dòng)態(tài)數(shù)組構(gòu)建與結(jié)果收集策略原生Filter函數(shù)內(nèi)部知道結(jié)果數(shù)量可以直接分配一個(gè)大小合適的數(shù)組。我們的自定義函數(shù)在遍歷完成前無法預(yù)知有多少項(xiàng)符合條件。因此需要一種動(dòng)態(tài)收集匹配項(xiàng)索引或引用最后再構(gòu)建結(jié)果數(shù)組的策略。我采用了Collection對象作為中間容器來收集匹配行的索引對于一維數(shù)組或匹配行的索引數(shù)組對于二維數(shù)組按行篩選。Collection在VBA中對于動(dòng)態(tài)增刪非常高效?！?假設(shè)我們已經(jīng)確定是二維數(shù)組按行篩選 (Dimension 1) Dim matchedRows As New Collection Dim i As Long, j As Long Dim rowMatchesAllConditions As Boolean Dim conditionArr As Variant ‘ 假設(shè)這是處理好的條件數(shù)組 Dim matchModeStr As String ‘ 假設(shè)這是匹配模式 For i LBound(SourceArray, 1) To UBound(SourceArray, 1) ‘ 遍歷每一行 rowMatchesAllConditions True ‘ 假設(shè)初始匹配用于AND邏輯 ‘ 對于當(dāng)前行檢查所有條件 For j LBound(conditionArr) To UBound(conditionArr) ‘ 這里需要根據(jù)條件與行的對應(yīng)關(guān)系進(jìn)行比較可能只比較特定列 ‘ 假設(shè)條件數(shù)組的每個(gè)元素是一個(gè)結(jié)構(gòu)體或數(shù)組包含“比較值”和“比較列索引” ‘ 這里簡化為直接比較當(dāng)前行的第一列與條件值 If Not IsMatch(SourceArray(i, 1), conditionArr(j), matchModeStr, Compare) Then If ConditionLogic “AND” Then rowMatchesAllConditions False Exit For ‘ AND邏輯下一個(gè)條件不滿足整行就不滿足 End If Else If ConditionLogic “OR” Then rowMatchesAllConditions True Exit For ‘ OR邏輯下一個(gè)條件滿足整行就滿足 End If End If Next j ‘ 根據(jù)Include參數(shù)和匹配結(jié)果決定是否收集 If (rowMatchesAllConditions And Include) Or (Not rowMatchesAllConditions And Not Include) Then matchedRows.Add i ‘ 收集行號(hào) End If Next i遍歷結(jié)束后matchedRows這個(gè)Collection里就存放了所有符合條件的行號(hào)。接下來根據(jù)這個(gè)行號(hào)集合來構(gòu)建結(jié)果數(shù)組If matchedRows.Count 0 Then ‘ 沒有匹配項(xiàng)返回一個(gè)空的數(shù)組 SuperFilter Array() ‘ 返回一個(gè)包含0個(gè)元素的數(shù)組 Else ‘ 根據(jù)源數(shù)組的維度和篩選維度構(gòu)建結(jié)果數(shù)組 Dim resultArr() As Variant Dim targetRowIndex As Long, srcRowIndex As Long Dim colCount As Long ‘ 計(jì)算列數(shù)對于按行篩選 colCount UBound(SourceArray, 2) - LBound(SourceArray, 2) 1 ‘ 重新定義結(jié)果數(shù)組的大小 ReDim resultArr(1 To matchedRows.Count, 1 To colCount) targetRowIndex 1 For Each srcRowIndex In matchedRows ‘ 遍歷收集到的行號(hào) For j 1 To colCount ‘ 復(fù)制數(shù)據(jù)保持原數(shù)據(jù)類型 resultArr(targetRowIndex, j) SourceArray(srcRowIndex, j) Next j targetRowIndex targetRowIndex 1 Next srcRowIndex SuperFilter resultArr End If實(shí)操心得使用Collection而非ReDim Preserve在循環(huán)內(nèi)部頻繁使用ReDim Preserve來擴(kuò)大數(shù)組是非常低效的因?yàn)槊看蜳reserveVBA都需要在內(nèi)存中創(chuàng)建一個(gè)新的更大數(shù)組并復(fù)制所有舊數(shù)據(jù)。Collection的.Add方法在動(dòng)態(tài)增長方面性能好得多。明確數(shù)組下界VBA數(shù)組默認(rèn)下界可能是0也可能是1取決于如何聲明和賦值。使用LBound和UBound函數(shù)始終是最安全的做法能確保代碼的健壯性。返回空數(shù)組的處理當(dāng)沒有匹配項(xiàng)時(shí)返回Array()一個(gè)長度為0的數(shù)組比返回Null或Empty更友好。調(diào)用方可以用If UBound(SuperFilter(...)) LBound(SuperFilter(...)) Then來判斷是否有結(jié)果這與處理原生Filter的結(jié)果習(xí)慣一致。4. 完整實(shí)現(xiàn)與代碼剖析下面是將上述思路整合后的SuperFilter函數(shù)的一個(gè)相對完整的簡化版本重點(diǎn)展示架構(gòu)和邏輯。實(shí)際版本可能包含更多錯(cuò)誤處理和邊界情況判斷。Option Explicit ‘ 定義匹配模式枚舉使代碼更易讀實(shí)際使用字符串也可 ‘ Private Enum MatchModes: mmExact: mmStartsWith: mmEndsWith: mmContains: mmRegex: End Enum Function SuperFilter(SourceArray As Variant, _ Match As Variant, _ Optional Include As Boolean True, _ Optional Compare As VbCompareMethod vbBinaryCompare, _ Optional MatchMode As String “contains”, _ Optional ConditionLogic As String “AND”, _ Optional Dimension As Long 1) As Variant ‘ —————————— 1. 參數(shù)驗(yàn)證與初始化 —————————— If Not IsArray(SourceArray) Then Err.Raise 13, “SuperFilter”, “SourceArray 必須是一個(gè)數(shù)組?！?End If Dim numDims As Long numDims GetArrayDimensions(SourceArray) ‘ 自定義函數(shù)獲取數(shù)組維數(shù) If numDims 2 Then Err.Raise 5, “SuperFilter”, “暫不支持二維以上的數(shù)組?！?End If ‘ 統(tǒng)一將Match轉(zhuǎn)換為數(shù)組方便后續(xù)處理 Dim conditionArr() As Variant If IsArray(Match) Then conditionArr Match Else ReDim conditionArr(0 To 0) conditionArr(0) Match End If ‘ 驗(yàn)證MatchMode MatchMode LCase(Trim(MatchMode)) If Not (MatchMode “exact” Or MatchMode “startswith” Or _ MatchMode “endswith” Or MatchMode “contains” Or MatchMode “regex”) Then MatchMode “contains” ‘ 默認(rèn)回退到包含模式 End If ‘ —————————— 2. 分派到不同的處理流程 —————————— Dim result As Variant If numDims 1 Then result SuperFilter1D(SourceArray, conditionArr, Include, Compare, MatchMode, ConditionLogic) Else ‘ numDims 2 If Dimension 1 And Dimension 2 Then Dimension 1 result SuperFilter2D(SourceArray, conditionArr, Include, Compare, MatchMode, ConditionLogic, Dimension) End If SuperFilter result End Function ‘ 處理一維數(shù)組的核心私有函數(shù) Private Function SuperFilter1D(SourceArr As Variant, _ ConditionArr As Variant, _ Include As Boolean, _ Compare As VbCompareMethod, _ MatchMode As String, _ ConditionLogic As String) As Variant Dim matchedIndices As New Collection Dim i As Long, c As Long Dim elem As Variant Dim conditionMet As Boolean For i LBound(SourceArr) To UBound(SourceArr) elem SourceArr(i) conditionMet EvaluateConditions(elem, ConditionArr, MatchMode, Compare, ConditionLogic) If (conditionMet And Include) Or (Not conditionMet And Not Include) Then matchedIndices.Add i End If Next i ‘ 構(gòu)建結(jié)果數(shù)組 If matchedIndices.Count 0 Then SuperFilter1D Array() Else Dim result() As Variant ReDim result(LBound(SourceArr) To LBound(SourceArr) matchedIndices.Count - 1) ‘ 注意這里簡化了實(shí)際應(yīng)處理下界可能為0的情況 Dim idx As Long, pos As Long pos LBound(result) For Each idx In matchedIndices result(pos) SourceArr(idx) pos pos 1 Next idx SuperFilter1D result End If End Function ‘ 處理二維數(shù)組的核心私有函數(shù) (按行篩選示例) Private Function SuperFilter2D(SourceArr As Variant, _ ConditionArr As Variant, _ Include As Boolean, _ Compare As VbCompareMethod, _ MatchMode As String, _ ConditionLogic As String, _ ByVal Dimension As Long) As Variant ‘ 實(shí)現(xiàn)邏輯與上文“動(dòng)態(tài)數(shù)組構(gòu)建”示例類似遍歷行/列使用EvaluateConditions判斷整行/列是否滿足條件 ‘ 此處省略詳細(xì)代碼結(jié)構(gòu)同SuperFilter1D但內(nèi)部循環(huán)和結(jié)果數(shù)組構(gòu)建是二維的。 ‘ ... ‘ 返回一個(gè)二維Variant數(shù)組或空數(shù)組 End Function ‘ 評估單個(gè)源值 against 一組條件的私有函數(shù) Private Function EvaluateConditions(SourceValue As Variant, _ ConditionArr As Variant, _ MatchMode As String, _ Compare As VbCompareMethod, _ ConditionLogic As String) As Boolean Dim j As Long Dim condMet As Boolean Dim overallResult As Boolean If ConditionLogic “AND” Then overallResult True ‘ AND邏輯初始為真遇到一個(gè)假則變假 For j LBound(ConditionArr) To UBound(ConditionArr) If Not IsMatch(SourceValue, ConditionArr(j), MatchMode, Compare) Then overallResult False Exit For End If Next j Else ‘ “OR” 邏輯 overallResult False ‘ OR邏輯初始為假遇到一個(gè)真則變真 For j LBound(ConditionArr) To UBound(ConditionArr) If IsMatch(SourceValue, ConditionArr(j), MatchMode, Compare) Then overallResult True Exit For End If Next j End If EvaluateConditions overallResult End Function ‘ 獲取數(shù)組維數(shù)的幫助函數(shù) Private Function GetArrayDimensions(arr As Variant) As Long On Error Resume Next Dim dims As Long dims 0 Do dims dims 1 Err.Clear Dim lb As Long lb LBound(arr, dims) If Err.Number 0 Then Exit Do Loop GetArrayDimensions dims - 1 End Function代碼設(shè)計(jì)要點(diǎn)模塊化將一維和二維數(shù)組的處理拆分成獨(dú)立的私有函數(shù)SuperFilter1D和SuperFilter2D主函數(shù)只負(fù)責(zé)路由和基礎(chǔ)驗(yàn)證。這使得代碼結(jié)構(gòu)清晰易于維護(hù)和調(diào)試。條件評估分離EvaluateConditions函數(shù)專門負(fù)責(zé)根據(jù)ConditionLogicAND/OR來綜合多個(gè)條件的判斷結(jié)果。IsMatch函數(shù)前面已定義則負(fù)責(zé)最底層的單個(gè)值比較。這種分層設(shè)計(jì)符合單一職責(zé)原則。健壯性開頭進(jìn)行了必要的參數(shù)驗(yàn)證并使用Err.Raise拋出清晰的錯(cuò)誤方便調(diào)用者調(diào)試。GetArrayDimensions函數(shù)優(yōu)雅地處理了獲取未知數(shù)組維數(shù)的問題。5. 實(shí)戰(zhàn)應(yīng)用與性能優(yōu)化探討5.1 典型使用場景示例假設(shè)我們有一個(gè)從Excel表格讀取的二維數(shù)組data結(jié)構(gòu)如下姓名部門銷售額日期張三銷售部15002023/10/1李四技術(shù)部8002023/10/2王五銷售部22002023/10/1趙六市場部12002023/10/3‘ 場景1精確篩選部門為“銷售部”的所有行原生Filter做不到 Dim result1 As Variant result1 SuperFilter(data, “銷售部”, True, vbTextCompare, “exact”, , 1) ‘ 結(jié)果將是一個(gè)包含張三和王五兩行的二維數(shù)組。 ‘ 場景2多條件AND篩選銷售部且銷售額大于1000 ‘ 假設(shè)我們構(gòu)建一個(gè)條件數(shù)組每個(gè)條件是一個(gè)包含“比較值”和“比較列索引”的數(shù)組。 ‘ 這里簡化演示假設(shè)我們分別對第2列部門和第3列銷售額進(jìn)行篩選。 ‘ 注意對于數(shù)值比較MatchMode用”exact”并不合適實(shí)際需要擴(kuò)展MatchMode支持”greaterthan”等。 ‘ 一種變通方法是使用正則表達(dá)式或直接在EvaluateConditions中寫特殊邏輯。 ‘ 更優(yōu)雅的設(shè)計(jì)是允許Match為對象數(shù)組或自定義類型數(shù)組包含比較運(yùn)算符。 ‘ 本例展示思路 Dim conditions(1) As Variant conditions(0) Array(“銷售部”, 2) ‘ 第2列等于“銷售部” conditions(1) Array(“^([1-9]\d{3,}|[2-9]\d\d\d)$”, 3) ‘ 第3列匹配正則大于等于1000的數(shù)字字符串形式 ‘ 這里需要自定義IsMatch函數(shù)能識(shí)別這種結(jié)構(gòu)化的條件并解析。 ‘ 這展示了SuperFilter框架的擴(kuò)展性。 ‘ 場景3篩選日期為2023/10/1的記錄原生Filter會(huì)將其轉(zhuǎn)為字符串再匹配不可靠 Dim result3 As Variant result3 SuperFilter(data, #10/1/2023#, True, vbBinaryCompare, “exact”, , 1) ‘ 直接使用日期類型進(jìn)行精確匹配可靠。 ‘ 場景4反向篩選Include:False排除所有部門名稱中包含“部”字的行模擬原生Filter Dim result4 As Variant result4 SuperFilter(data, “部”, False, vbTextCompare, “contains”, , 1) ‘ 返回部門名不包含“部”的行可能為空。5.2 性能瓶頸分析與優(yōu)化建議自定義函數(shù)在帶來靈活性的同時(shí)必然會(huì)在性能上有所犧牲尤其是與高度優(yōu)化的原生函數(shù)相比。SuperFilter的主要性能開銷在雙層循環(huán)對于二維數(shù)組的多條件篩選是O(m*n)的復(fù)雜度m行*n條件。當(dāng)數(shù)據(jù)量巨大數(shù)萬行時(shí)循環(huán)本身會(huì)成為瓶頸。頻繁的類型判斷與函數(shù)調(diào)用在循環(huán)內(nèi)部每次比較都要調(diào)用IsMatch內(nèi)部還有Select Case、StrComp、InStr甚至CreateObject正則等操作。Variant類型操作Variant是VBA中開銷最大的數(shù)據(jù)類型頻繁的讀取、賦值和比較會(huì)比明確類型的變量慢。優(yōu)化策略提前進(jìn)行類型判斷和轉(zhuǎn)換如果事先知道某列全是數(shù)字或日期可以在主循環(huán)外進(jìn)行類型判斷并在IsMatch函數(shù)中為這種類型實(shí)現(xiàn)快速路徑避免每次都進(jìn)行VarType判斷和CStr轉(zhuǎn)換。使用靜態(tài)正則對象如果匹配模式是“regex”且模式不變可以在模塊級(jí)別聲明一個(gè)靜態(tài)的RegExp對象在函數(shù)中重復(fù)使用避免反復(fù)創(chuàng)建銷毀。針對純字符串一維數(shù)組的快速路徑如果檢測到輸入是一維字符串?dāng)?shù)組且匹配模式是“contains”可以退化到使用原生Filter函數(shù)進(jìn)行第一步快速篩選然后再對結(jié)果應(yīng)用其他條件或處理。這相當(dāng)于用原生函數(shù)做了一次預(yù)過濾。限制條件數(shù)量在業(yè)務(wù)邏輯允許的情況下盡量減少單次篩選的條件數(shù)量。將最嚴(yán)格、最能過濾掉數(shù)據(jù)的條件放在前面判斷可以提前退出循環(huán)對于AND邏輯。數(shù)組切片與內(nèi)存操作對于超大規(guī)模數(shù)據(jù)可以考慮借助Windows API調(diào)用或編寫C DLL來實(shí)現(xiàn)內(nèi)存塊的高效比較但這大大增加了復(fù)雜度僅適用于極端性能場景。注意對于絕大多數(shù)Office VBA應(yīng)用場景數(shù)據(jù)處理量在幾千到幾萬行上述SuperFilter函數(shù)的性能是完全可接受的。優(yōu)化時(shí)應(yīng)遵循“先測量后優(yōu)化”的原則用Timer函數(shù)定位真正的熱點(diǎn)避免過度設(shè)計(jì)。6. 常見問題與排查技巧實(shí)錄在實(shí)際使用和調(diào)試SuperFilter函數(shù)的過程中我遇到了不少典型問題。這里記錄一下方便大家避坑。問題1函數(shù)返回#VALUE!錯(cuò)誤或運(yùn)行時(shí)錯(cuò)誤‘13’類型不匹配。排查思路檢查源數(shù)組確保傳入的SourceArray確實(shí)是一個(gè)數(shù)組。有時(shí)從某個(gè)可能返回單值的函數(shù)獲取數(shù)據(jù)需要先判斷IsArray()。檢查條件值類型當(dāng)使用“exact”模式比較數(shù)字和字符串時(shí)“123”和123是不相等的。確保你的Match參數(shù)類型與數(shù)組中對應(yīng)元素的類型一致或理解這種差異。二維數(shù)組的維度參數(shù)確保對二維數(shù)組調(diào)用時(shí)Dimension參數(shù)只傳了1或2。傳了0或3會(huì)導(dǎo)致后續(xù)計(jì)算索引時(shí)出錯(cuò)。正則表達(dá)式模式無效當(dāng)MatchMode為“regex”時(shí)如果傳入的匹配字符串不是有效的正則表達(dá)式regex.Test會(huì)出錯(cuò)。確保在IsMatch函數(shù)中做好了錯(cuò)誤捕獲。問題2篩選結(jié)果為空但明明覺得應(yīng)該有數(shù)據(jù)。排查思路字符串比較模式Compare參數(shù)這是最常見的原因。vbBinaryCompare區(qū)分大小寫”Apple”和”apple”不匹配。vbTextCompare不區(qū)分。檢查你的數(shù)據(jù)大小寫和參數(shù)設(shè)置。匹配模式MatchMode理解錯(cuò)誤想要精確匹配卻用了默認(rèn)的“contains”。仔細(xì)核對MatchMode參數(shù)。前后空格數(shù)據(jù)中可能存在肉眼不易察覺的首尾空格。使用Trim函數(shù)清洗源數(shù)據(jù)或條件值。可以在IsMatch函數(shù)內(nèi)部對字符串比較加入自動(dòng)Trim的選項(xiàng)但需注意對于精確匹配有時(shí)空格是有意義的。多條件邏輯ConditionLogic錯(cuò)誤誤將“OR”邏輯當(dāng)作“AND”使用或者反之。理清業(yè)務(wù)邏輯。Include參數(shù)理解反了Include:False是返回不匹配的項(xiàng)。確認(rèn)你是否想要的是排除。問題3處理大量數(shù)據(jù)時(shí)速度非常慢。排查思路啟用屏幕更新和計(jì)算關(guān)閉如果是在Excel中循環(huán)調(diào)用該函數(shù)處理大量單元格確保在代碼開頭設(shè)置了Application.ScreenUpdating False和Application.Calculation xlCalculationManual處理完再恢復(fù)。檢查是否在循環(huán)中重復(fù)調(diào)用函數(shù)避免在For Each循環(huán)中針對每一行數(shù)據(jù)都調(diào)用一次SuperFilter。應(yīng)該一次性將整個(gè)數(shù)據(jù)區(qū)域裝入數(shù)組用SuperFilter處理這個(gè)數(shù)組最后將結(jié)果一次性寫回工作表。這是VBA操作數(shù)據(jù)的黃金準(zhǔn)則盡量減少VBA與工作表之間的交互。審視匹配模式是否不必要地使用了開銷巨大的“regex”模式如果只是簡單匹配換成“exact”或“contains”會(huì)快很多。數(shù)據(jù)量是否真的超出了VBA處理范圍如果數(shù)組有幾十萬行VBA循環(huán)本身就會(huì)很慢?？紤]是否能在數(shù)據(jù)庫層面如Access、SQL Server進(jìn)行篩選或者使用Power Query進(jìn)行處理。問題4如何篩選“大于某個(gè)數(shù)值”或“介于某兩個(gè)日期之間”這類范圍條件解決方案當(dāng)前的SuperFilter框架主要通過MatchMode支持文本匹配模式。為了支持?jǐn)?shù)值/日期比較最整潔的擴(kuò)展方式是修改Match參數(shù)和IsMatch函數(shù)的設(shè)計(jì)。方案A快速但不優(yōu)雅將MatchMode擴(kuò)展增加“gt”大于、“l(fā)t”小于、“gte”大于等于、“l(fā)te”小于等于、“between”介于等模式。此時(shí)Match參數(shù)可能需要是一個(gè)兩元素?cái)?shù)組來表示范圍。這需要修改IsMatch函數(shù)來支持這些新的比較運(yùn)算符。方案B更靈活允許Match參數(shù)傳入一個(gè)自定義的回調(diào)函數(shù)函數(shù)指針。在IsMatch中如果發(fā)現(xiàn)Match是一個(gè)函數(shù)則調(diào)用這個(gè)函數(shù)來進(jìn)行判斷。這樣用戶可以將任何復(fù)雜的比較邏輯寫成一個(gè)獨(dú)立的函數(shù)傳入。這是最強(qiáng)大但也對使用者要求最高的方式。方案C實(shí)用折中對于簡單的范圍篩選可以結(jié)合使用Include:False。例如要篩選銷售額大于1000的可以先篩選出小于等于1000的然后取反。但這對于“介于兩者之間”這樣的條件就有點(diǎn)繞。最終我選擇了在內(nèi)部預(yù)留了擴(kuò)展接口但當(dāng)前版本主要優(yōu)化了字符串和精確匹配的通用場景。對于復(fù)雜的數(shù)值比較我通常建議使用方案C的變通方法或者直接在調(diào)用SuperFilter之前用簡單的數(shù)組循環(huán)預(yù)處理出一個(gè)標(biāo)志列然后再用SuperFilter進(jìn)行篩選。工具不是萬能的在復(fù)雜場景下組合使用多種方法往往是最佳實(shí)踐。 VBA數(shù)組篩選進(jìn)階：超越原生Filter，實(shí)現(xiàn)多條件、多模式與多維數(shù)組處理
1. 項(xiàng)目概述從“能用”到“好用”的VBA數(shù)組篩選之路在VBA的日常開發(fā)里處理數(shù)組篩選是家常便飯。系統(tǒng)自帶的Filter函數(shù)用起來確實(shí)順手一句Filter(源數(shù)組, 匹配字符串, 包含, 比較模式)就能快速返回一個(gè)包含子串的結(jié)果數(shù)組。但用久了尤其是在處理稍微復(fù)雜點(diǎn)的數(shù)據(jù)清洗、報(bào)表生成或者自動(dòng)化核對任務(wù)時(shí)它的“脾氣”和“局限”就暴露無遺了。最讓人頭疼的莫過于它只能進(jìn)行基于字符串的“模糊包含”匹配你想按精確相等、數(shù)值范圍、多條件“與/或”邏輯來篩對不起Filter函數(shù)攤攤手表示辦不到。另一個(gè)坑是它的返回結(jié)果永遠(yuǎn)是一維的字符串?dāng)?shù)組如果你的源數(shù)據(jù)是二維的或者里面混著數(shù)字、日期Filter要么報(bào)錯(cuò)要么給你轉(zhuǎn)成字符串后續(xù)處理起來非常別扭。所以當(dāng)項(xiàng)目里又一次因?yàn)镕ilter無法實(shí)現(xiàn)“篩選出金額大于1000且部門為‘銷售部’的記錄”而不得不寫一長串循環(huán)時(shí)我決定不再將就。這次的目標(biāo)很明確自己動(dòng)手寫一個(gè)能完全模仿Filter基礎(chǔ)功能同時(shí)又能突破其核心局限的自定義函數(shù)。它不僅要能處理Filter能做的字符串包含篩選更要搞定Filter做不了的精確匹配、多條件、非字符串?dāng)?shù)據(jù)類型、多維數(shù)組篩選。這不僅僅是再造一個(gè)輪子而是打造一個(gè)更適應(yīng)復(fù)雜現(xiàn)實(shí)場景的“瑞士軍刀”。2. 核心需求與設(shè)計(jì)思路拆解2.1 深入剖析原生Filter函數(shù)的“阿喀琉斯之踵”在動(dòng)手之前必須先把標(biāo)準(zhǔn)Filter函數(shù)的局限性摸透這樣才能有的放矢。它的局限主要體現(xiàn)在四個(gè)方面匹配模式單一且不可控這是最根本的局限。Filter函數(shù)本質(zhì)上是一個(gè)“字符串包含”篩選器。它的Match參數(shù)必須是一個(gè)字符串函數(shù)的行為就是在源數(shù)組的每個(gè)元素中查找是否包含這個(gè)字符串。你無法將其改為“等于”、“以...開頭”、“以...結(jié)尾”或“正則表達(dá)式匹配”。例如你想從數(shù)組[“蘋果”, “香蕉”, “葡萄”]中精確找出“蘋果”用Filter(arr, “蘋果”, True, vbTextCompare)會(huì)把包含“蘋果”字的都找出來如果存在“紅蘋果”也會(huì)被選中而這往往不是我們想要的。數(shù)據(jù)類型強(qiáng)制轉(zhuǎn)換與丟失Filter的輸入數(shù)組和輸出數(shù)組都被強(qiáng)制限定為字符串類型Variant/String。如果你傳入一個(gè)包含數(shù)字、日期、布爾值的數(shù)組VBA會(huì)隱式地將它們?nèi)哭D(zhuǎn)換為字符串再進(jìn)行匹配。這會(huì)導(dǎo)致兩個(gè)問題一是精度可能丟失例如數(shù)字123.456可能變成“123.456”二是匹配邏輯變得詭異。比如你想從[123, 234, 345]中篩選出包含“23”的元素Filter會(huì)返回[123, 234]但這本質(zhì)是在字符串“123”和“234”中查找“23”而非數(shù)值比較。更麻煩的是輸出結(jié)果是一個(gè)一維字符串?dāng)?shù)組原始的數(shù)據(jù)類型信息徹底丟失給后續(xù)計(jì)算帶來隱患。無法處理多維數(shù)組Filter函數(shù)只接受一維數(shù)組作為輸入。在實(shí)際業(yè)務(wù)中我們經(jīng)常從Excel的Range.Value屬性直接得到一個(gè)二維數(shù)組或者自己構(gòu)建多維數(shù)組來存儲(chǔ)表格數(shù)據(jù)。面對這樣的數(shù)據(jù)源Filter函數(shù)會(huì)直接拋出“類型不匹配”或“下標(biāo)越界”的錯(cuò)誤迫使開發(fā)者必須先通過循環(huán)將其展平為一維篩選后再重組過程繁瑣且低效。多條件篩選能力缺失現(xiàn)實(shí)場景中的篩選很少是單條件的。Filter函數(shù)一次只能接受一個(gè)匹配字符串和一個(gè)是否包含的邏輯。要實(shí)現(xiàn)“條件A與條件B”或者“條件A或條件B”必須多次調(diào)用Filter并進(jìn)行數(shù)組間的交集或并集操作代碼會(huì)變得冗長且難以維護(hù)。2.2 自定義函數(shù)SuperFilter的頂層設(shè)計(jì)基于以上痛點(diǎn)我設(shè)計(jì)的自定義函數(shù)SuperFilter需要實(shí)現(xiàn)以下核心目標(biāo)功能目標(biāo)兼容模式完全復(fù)刻原生Filter函數(shù)的所有參數(shù)和功能作為保底選項(xiàng)。擴(kuò)展模式提供多種匹配模式精確、開頭、結(jié)尾、包含、正則支持?jǐn)?shù)值/日期等非字符串類型的直接比較。多條件支持允許傳入一個(gè)條件數(shù)組并指定條件間的邏輯關(guān)系A(chǔ)ND 或 OR。多維數(shù)組支持能夠直接處理二維數(shù)組并可以指定按行篩選還是按列篩選返回一個(gè)結(jié)構(gòu)保持的二維子數(shù)組。數(shù)據(jù)類型保持輸出數(shù)組的元素?cái)?shù)據(jù)類型盡量與輸入數(shù)組對應(yīng)元素保持一致。接口設(shè)計(jì)Function SuperFilter(SourceArray As Variant, _ Match As Variant, _ Optional Include As Boolean True, _ Optional Compare As VbCompareMethod vbBinaryCompare, _ Optional MatchMode As String “contains”, _ Optional ConditionLogic As String “AND”, _ Optional Dimension As Long 1) As VariantSourceArray: 源數(shù)組接受一維或二維Variant數(shù)組。Match: 匹配條件。可以是單個(gè)值字符串、數(shù)字、日期等也可以是一個(gè)一維條件數(shù)組。Include: 是否包含。True返回匹配項(xiàng)False返回不匹配項(xiàng)與Filter一致。Compare: 字符串比較模式vbBinaryCompare區(qū)分大小寫或vbTextCompare不區(qū)分。MatchMode: 匹配模式?？蛇x“exact”精確、“startswith”開頭、“endswith”結(jié)尾、“contains”包含默認(rèn)、“regex”正則表達(dá)式。ConditionLogic: 當(dāng)Match為數(shù)組時(shí)條件間的邏輯。“AND”所有條件都滿足或“OR”任一條件滿足。Dimension: 對二維數(shù)組的篩選維度。1表示按行篩選返回符合條件的整行2表示按列篩選。內(nèi)部邏輯流程輸入驗(yàn)證與初始化檢查SourceArray是否是數(shù)組判斷其維數(shù)。根據(jù)維數(shù)和Dimension參數(shù)確定循環(huán)遍歷的方式是遍歷行還是列。條件預(yù)處理將Match參數(shù)統(tǒng)一處理為一個(gè)條件數(shù)組condArr()并確定匹配模式MatchMode。如果是多條件需明確ConditionLogic。核心遍歷與匹配判斷遍歷源數(shù)組的每一個(gè)目標(biāo)元素可能是一行、一列或一個(gè)單元素。對每個(gè)元素根據(jù)MatchMode調(diào)用對應(yīng)的匹配判斷函數(shù)并結(jié)合ConditionLogic計(jì)算最終是否匹配。結(jié)果收集與數(shù)組構(gòu)建動(dòng)態(tài)收集所有匹配項(xiàng)的索引。遍歷結(jié)束后根據(jù)收集的索引從源數(shù)組中提取對應(yīng)元素/行/列構(gòu)建一個(gè)新的、結(jié)構(gòu)正確的Variant數(shù)組作為結(jié)果返回。錯(cuò)誤處理在整個(gè)過程中加入必要的錯(cuò)誤捕獲例如無效的匹配模式、維度參數(shù)錯(cuò)誤、正則表達(dá)式編譯失敗等并返回一個(gè)可識(shí)別的錯(cuò)誤值如Empty數(shù)組或拋出描述性錯(cuò)誤。3. 核心細(xì)節(jié)解析與關(guān)鍵技術(shù)實(shí)現(xiàn)3.1 多模式匹配引擎的實(shí)現(xiàn)這是SuperFilter函數(shù)的核心也是與原生Filter最大的區(qū)別。我們需要一個(gè)統(tǒng)一的判斷函數(shù)它能根據(jù)MatchMode參數(shù)采用不同的邏輯進(jìn)行比較。首先定義一個(gè)內(nèi)部幫助函數(shù)IsMatchPrivate Function IsMatch(ByVal SourceValue As Variant, _ ByVal ConditionValue As Variant, _ ByVal MatchMode As String, _ ByVal CompareMethod As VbCompareMethod) As Boolean ‘ 處理空值情況 If IsEmpty(SourceValue) Or IsEmpty(ConditionValue) Then IsMatch False Exit Function End If Dim srcStr As String, condStr As String Dim regex As Object On Error GoTo ErrorHandler Select Case LCase(Trim(MatchMode)) Case “exact” ‘精確匹配 If VarType(SourceValue) vbString And VarType(ConditionValue) vbString Then IsMatch (StrComp(SourceValue, ConditionValue, CompareMethod) 0) Else ‘ 非字符串類型使用等號(hào)直接比較注意Variant的比較方式 IsMatch (SourceValue ConditionValue) End If Case “startswith” ‘開頭匹配 srcStr CStr(SourceValue) condStr CStr(ConditionValue) If CompareMethod vbTextCompare Then IsMatch (InStr(1, srcStr, condStr, vbTextCompare) 1) Else IsMatch (Left(srcStr, Len(condStr)) condStr) End If Case “endswith” ‘結(jié)尾匹配 srcStr CStr(SourceValue) condStr CStr(ConditionValue) If CompareMethod vbTextCompare Then ‘ 通過StrComp比較末尾部分稍微復(fù)雜一點(diǎn) IsMatch (StrComp(Right(srcStr, Len(condStr)), condStr, CompareMethod) 0) Else IsMatch (Right(srcStr, Len(condStr)) condStr) End If Case “contains” ‘包含匹配 (模擬原生Filter) srcStr CStr(SourceValue) condStr CStr(ConditionValue) IsMatch (InStr(1, srcStr, condStr, CompareMethod) 0) Case “regex” ‘正則表達(dá)式匹配 Set regex CreateObject(“VBScript.RegExp”) regex.Pattern CStr(ConditionValue) regex.IgnoreCase (CompareMethod vbTextCompare) regex.Global False IsMatch regex.Test(CStr(SourceValue)) Set regex Nothing Case Else ‘ 默認(rèn)使用包含匹配 srcStr CStr(SourceValue) condStr CStr(ConditionValue) IsMatch (InStr(1, srcStr, condStr, CompareMethod) 0) End Select Exit Function ErrorHandler: ‘ 例如正則表達(dá)式模式無效 IsMatch False End Function關(guān)鍵點(diǎn)與避坑指南數(shù)據(jù)類型處理在“exact”模式下我們優(yōu)先判斷是否為字符串是則用StrComp函數(shù)以支持大小寫敏感控制否則直接用等號(hào)比較。這比原生Filter全部轉(zhuǎn)字符串更合理。性能考量正則表達(dá)式對象的創(chuàng)建和銷毀CreateObject和Set regex Nothing有一定開銷。如果在一個(gè)大數(shù)據(jù)集的循環(huán)中頻繁使用“regex”模式會(huì)成為性能瓶頸?？梢钥紤]將正則對象創(chuàng)建移到主函數(shù)中作為可選參數(shù)傳入但會(huì)增大接口復(fù)雜度。對于一般應(yīng)用在IsMatch內(nèi)部創(chuàng)建是清晰簡單的做法?？罩堤幚鞩sEmpty判斷至關(guān)重要。VBA數(shù)組中經(jīng)常存在Empty值直接對其進(jìn)行字符串轉(zhuǎn)換或比較可能導(dǎo)致類型不匹配錯(cuò)誤。這里選擇將任何一方為Empty的匹配直接判定為False邏輯上更安全。3.2 動(dòng)態(tài)數(shù)組構(gòu)建與結(jié)果收集策略原生Filter函數(shù)內(nèi)部知道結(jié)果數(shù)量可以直接分配一個(gè)大小合適的數(shù)組。我們的自定義函數(shù)在遍歷完成前無法預(yù)知有多少項(xiàng)符合條件。因此需要一種動(dòng)態(tài)收集匹配項(xiàng)索引或引用最后再構(gòu)建結(jié)果數(shù)組的策略。我采用了Collection對象作為中間容器來收集匹配行的索引對于一維數(shù)組或匹配行的索引數(shù)組對于二維數(shù)組按行篩選。Collection在VBA中對于動(dòng)態(tài)增刪非常高效?！?假設(shè)我們已經(jīng)確定是二維數(shù)組按行篩選 (Dimension 1) Dim matchedRows As New Collection Dim i As Long, j As Long Dim rowMatchesAllConditions As Boolean Dim conditionArr As Variant ‘ 假設(shè)這是處理好的條件數(shù)組 Dim matchModeStr As String ‘ 假設(shè)這是匹配模式 For i LBound(SourceArray, 1) To UBound(SourceArray, 1) ‘ 遍歷每一行 rowMatchesAllConditions True ‘ 假設(shè)初始匹配用于AND邏輯 ‘ 對于當(dāng)前行檢查所有條件 For j LBound(conditionArr) To UBound(conditionArr) ‘ 這里需要根據(jù)條件與行的對應(yīng)關(guān)系進(jìn)行比較可能只比較特定列 ‘ 假設(shè)條件數(shù)組的每個(gè)元素是一個(gè)結(jié)構(gòu)體或數(shù)組包含“比較值”和“比較列索引” ‘ 這里簡化為直接比較當(dāng)前行的第一列與條件值 If Not IsMatch(SourceArray(i, 1), conditionArr(j), matchModeStr, Compare) Then If ConditionLogic “AND” Then rowMatchesAllConditions False Exit For ‘ AND邏輯下一個(gè)條件不滿足整行就不滿足 End If Else If ConditionLogic “OR” Then rowMatchesAllConditions True Exit For ‘ OR邏輯下一個(gè)條件滿足整行就滿足 End If End If Next j ‘ 根據(jù)Include參數(shù)和匹配結(jié)果決定是否收集 If (rowMatchesAllConditions And Include) Or (Not rowMatchesAllConditions And Not Include) Then matchedRows.Add i ‘ 收集行號(hào) End If Next i遍歷結(jié)束后matchedRows這個(gè)Collection里就存放了所有符合條件的行號(hào)。接下來根據(jù)這個(gè)行號(hào)集合來構(gòu)建結(jié)果數(shù)組If matchedRows.Count 0 Then ‘ 沒有匹配項(xiàng)返回一個(gè)空的數(shù)組 SuperFilter Array() ‘ 返回一個(gè)包含0個(gè)元素的數(shù)組 Else ‘ 根據(jù)源數(shù)組的維度和篩選維度構(gòu)建結(jié)果數(shù)組 Dim resultArr() As Variant Dim targetRowIndex As Long, srcRowIndex As Long Dim colCount As Long ‘ 計(jì)算列數(shù)對于按行篩選 colCount UBound(SourceArray, 2) - LBound(SourceArray, 2) 1 ‘ 重新定義結(jié)果數(shù)組的大小 ReDim resultArr(1 To matchedRows.Count, 1 To colCount) targetRowIndex 1 For Each srcRowIndex In matchedRows ‘ 遍歷收集到的行號(hào) For j 1 To colCount ‘ 復(fù)制數(shù)據(jù)保持原數(shù)據(jù)類型 resultArr(targetRowIndex, j) SourceArray(srcRowIndex, j) Next j targetRowIndex targetRowIndex 1 Next srcRowIndex SuperFilter resultArr End If實(shí)操心得使用Collection而非ReDim Preserve在循環(huán)內(nèi)部頻繁使用ReDim Preserve來擴(kuò)大數(shù)組是非常低效的因?yàn)槊看蜳reserveVBA都需要在內(nèi)存中創(chuàng)建一個(gè)新的更大數(shù)組并復(fù)制所有舊數(shù)據(jù)。Collection的.Add方法在動(dòng)態(tài)增長方面性能好得多。明確數(shù)組下界VBA數(shù)組默認(rèn)下界可能是0也可能是1取決于如何聲明和賦值。使用LBound和UBound函數(shù)始終是最安全的做法能確保代碼的健壯性。返回空數(shù)組的處理當(dāng)沒有匹配項(xiàng)時(shí)返回Array()一個(gè)長度為0的數(shù)組比返回Null或Empty更友好。調(diào)用方可以用If UBound(SuperFilter(...)) LBound(SuperFilter(...)) Then來判斷是否有結(jié)果這與處理原生Filter的結(jié)果習(xí)慣一致。4. 完整實(shí)現(xiàn)與代碼剖析下面是將上述思路整合后的SuperFilter函數(shù)的一個(gè)相對完整的簡化版本重點(diǎn)展示架構(gòu)和邏輯。實(shí)際版本可能包含更多錯(cuò)誤處理和邊界情況判斷。Option Explicit ‘ 定義匹配模式枚舉使代碼更易讀實(shí)際使用字符串也可 ‘ Private Enum MatchModes: mmExact: mmStartsWith: mmEndsWith: mmContains: mmRegex: End Enum Function SuperFilter(SourceArray As Variant, _ Match As Variant, _ Optional Include As Boolean True, _ Optional Compare As VbCompareMethod vbBinaryCompare, _ Optional MatchMode As String “contains”, _ Optional ConditionLogic As String “AND”, _ Optional Dimension As Long 1) As Variant ‘ —————————— 1. 參數(shù)驗(yàn)證與初始化 —————————— If Not IsArray(SourceArray) Then Err.Raise 13, “SuperFilter”, “SourceArray 必須是一個(gè)數(shù)組?！?End If Dim numDims As Long numDims GetArrayDimensions(SourceArray) ‘ 自定義函數(shù)獲取數(shù)組維數(shù) If numDims 2 Then Err.Raise 5, “SuperFilter”, “暫不支持二維以上的數(shù)組?！?End If ‘ 統(tǒng)一將Match轉(zhuǎn)換為數(shù)組方便后續(xù)處理 Dim conditionArr() As Variant If IsArray(Match) Then conditionArr Match Else ReDim conditionArr(0 To 0) conditionArr(0) Match End If ‘ 驗(yàn)證MatchMode MatchMode LCase(Trim(MatchMode)) If Not (MatchMode “exact” Or MatchMode “startswith” Or _ MatchMode “endswith” Or MatchMode “contains” Or MatchMode “regex”) Then MatchMode “contains” ‘ 默認(rèn)回退到包含模式 End If ‘ —————————— 2. 分派到不同的處理流程 —————————— Dim result As Variant If numDims 1 Then result SuperFilter1D(SourceArray, conditionArr, Include, Compare, MatchMode, ConditionLogic) Else ‘ numDims 2 If Dimension 1 And Dimension 2 Then Dimension 1 result SuperFilter2D(SourceArray, conditionArr, Include, Compare, MatchMode, ConditionLogic, Dimension) End If SuperFilter result End Function ‘ 處理一維數(shù)組的核心私有函數(shù) Private Function SuperFilter1D(SourceArr As Variant, _ ConditionArr As Variant, _ Include As Boolean, _ Compare As VbCompareMethod, _ MatchMode As String, _ ConditionLogic As String) As Variant Dim matchedIndices As New Collection Dim i As Long, c As Long Dim elem As Variant Dim conditionMet As Boolean For i LBound(SourceArr) To UBound(SourceArr) elem SourceArr(i) conditionMet EvaluateConditions(elem, ConditionArr, MatchMode, Compare, ConditionLogic) If (conditionMet And Include) Or (Not conditionMet And Not Include) Then matchedIndices.Add i End If Next i ‘ 構(gòu)建結(jié)果數(shù)組 If matchedIndices.Count 0 Then SuperFilter1D Array() Else Dim result() As Variant ReDim result(LBound(SourceArr) To LBound(SourceArr) matchedIndices.Count - 1) ‘ 注意這里簡化了實(shí)際應(yīng)處理下界可能為0的情況 Dim idx As Long, pos As Long pos LBound(result) For Each idx In matchedIndices result(pos) SourceArr(idx) pos pos 1 Next idx SuperFilter1D result End If End Function ‘ 處理二維數(shù)組的核心私有函數(shù) (按行篩選示例) Private Function SuperFilter2D(SourceArr As Variant, _ ConditionArr As Variant, _ Include As Boolean, _ Compare As VbCompareMethod, _ MatchMode As String, _ ConditionLogic As String, _ ByVal Dimension As Long) As Variant ‘ 實(shí)現(xiàn)邏輯與上文“動(dòng)態(tài)數(shù)組構(gòu)建”示例類似遍歷行/列使用EvaluateConditions判斷整行/列是否滿足條件 ‘ 此處省略詳細(xì)代碼結(jié)構(gòu)同SuperFilter1D但內(nèi)部循環(huán)和結(jié)果數(shù)組構(gòu)建是二維的。 ‘ ... ‘ 返回一個(gè)二維Variant數(shù)組或空數(shù)組 End Function ‘ 評估單個(gè)源值 against 一組條件的私有函數(shù) Private Function EvaluateConditions(SourceValue As Variant, _ ConditionArr As Variant, _ MatchMode As String, _ Compare As VbCompareMethod, _ ConditionLogic As String) As Boolean Dim j As Long Dim condMet As Boolean Dim overallResult As Boolean If ConditionLogic “AND” Then overallResult True ‘ AND邏輯初始為真遇到一個(gè)假則變假 For j LBound(ConditionArr) To UBound(ConditionArr) If Not IsMatch(SourceValue, ConditionArr(j), MatchMode, Compare) Then overallResult False Exit For End If Next j Else ‘ “OR” 邏輯 overallResult False ‘ OR邏輯初始為假遇到一個(gè)真則變真 For j LBound(ConditionArr) To UBound(ConditionArr) If IsMatch(SourceValue, ConditionArr(j), MatchMode, Compare) Then overallResult True Exit For End If Next j End If EvaluateConditions overallResult End Function ‘ 獲取數(shù)組維數(shù)的幫助函數(shù) Private Function GetArrayDimensions(arr As Variant) As Long On Error Resume Next Dim dims As Long dims 0 Do dims dims 1 Err.Clear Dim lb As Long lb LBound(arr, dims) If Err.Number 0 Then Exit Do Loop GetArrayDimensions dims - 1 End Function代碼設(shè)計(jì)要點(diǎn)模塊化將一維和二維數(shù)組的處理拆分成獨(dú)立的私有函數(shù)SuperFilter1D和SuperFilter2D主函數(shù)只負(fù)責(zé)路由和基礎(chǔ)驗(yàn)證。這使得代碼結(jié)構(gòu)清晰易于維護(hù)和調(diào)試。條件評估分離EvaluateConditions函數(shù)專門負(fù)責(zé)根據(jù)ConditionLogicAND/OR來綜合多個(gè)條件的判斷結(jié)果。IsMatch函數(shù)前面已定義則負(fù)責(zé)最底層的單個(gè)值比較。這種分層設(shè)計(jì)符合單一職責(zé)原則。健壯性開頭進(jìn)行了必要的參數(shù)驗(yàn)證并使用Err.Raise拋出清晰的錯(cuò)誤方便調(diào)用者調(diào)試。GetArrayDimensions函數(shù)優(yōu)雅地處理了獲取未知數(shù)組維數(shù)的問題。5. 實(shí)戰(zhàn)應(yīng)用與性能優(yōu)化探討5.1 典型使用場景示例假設(shè)我們有一個(gè)從Excel表格讀取的二維數(shù)組data結(jié)構(gòu)如下姓名部門銷售額日期張三銷售部15002023/10/1李四技術(shù)部8002023/10/2王五銷售部22002023/10/1趙六市場部12002023/10/3‘ 場景1精確篩選部門為“銷售部”的所有行原生Filter做不到 Dim result1 As Variant result1 SuperFilter(data, “銷售部”, True, vbTextCompare, “exact”, , 1) ‘ 結(jié)果將是一個(gè)包含張三和王五兩行的二維數(shù)組。 ‘ 場景2多條件AND篩選銷售部且銷售額大于1000 ‘ 假設(shè)我們構(gòu)建一個(gè)條件數(shù)組每個(gè)條件是一個(gè)包含“比較值”和“比較列索引”的數(shù)組。 ‘ 這里簡化演示假設(shè)我們分別對第2列部門和第3列銷售額進(jìn)行篩選。 ‘ 注意對于數(shù)值比較MatchMode用”exact”并不合適實(shí)際需要擴(kuò)展MatchMode支持”greaterthan”等。 ‘ 一種變通方法是使用正則表達(dá)式或直接在EvaluateConditions中寫特殊邏輯。 ‘ 更優(yōu)雅的設(shè)計(jì)是允許Match為對象數(shù)組或自定義類型數(shù)組包含比較運(yùn)算符。 ‘ 本例展示思路 Dim conditions(1) As Variant conditions(0) Array(“銷售部”, 2) ‘ 第2列等于“銷售部” conditions(1) Array(“^([1-9]\d{3,}|[2-9]\d\d\d)$”, 3) ‘ 第3列匹配正則大于等于1000的數(shù)字字符串形式 ‘ 這里需要自定義IsMatch函數(shù)能識(shí)別這種結(jié)構(gòu)化的條件并解析。 ‘ 這展示了SuperFilter框架的擴(kuò)展性。 ‘ 場景3篩選日期為2023/10/1的記錄原生Filter會(huì)將其轉(zhuǎn)為字符串再匹配不可靠 Dim result3 As Variant result3 SuperFilter(data, #10/1/2023#, True, vbBinaryCompare, “exact”, , 1) ‘ 直接使用日期類型進(jìn)行精確匹配可靠。 ‘ 場景4反向篩選Include:False排除所有部門名稱中包含“部”字的行模擬原生Filter Dim result4 As Variant result4 SuperFilter(data, “部”, False, vbTextCompare, “contains”, , 1) ‘ 返回部門名不包含“部”的行可能為空。5.2 性能瓶頸分析與優(yōu)化建議自定義函數(shù)在帶來靈活性的同時(shí)必然會(huì)在性能上有所犧牲尤其是與高度優(yōu)化的原生函數(shù)相比。SuperFilter的主要性能開銷在雙層循環(huán)對于二維數(shù)組的多條件篩選是O(m*n)的復(fù)雜度m行*n條件。當(dāng)數(shù)據(jù)量巨大數(shù)萬行時(shí)循環(huán)本身會(huì)成為瓶頸。頻繁的類型判斷與函數(shù)調(diào)用在循環(huán)內(nèi)部每次比較都要調(diào)用IsMatch內(nèi)部還有Select Case、StrComp、InStr甚至CreateObject正則等操作。Variant類型操作Variant是VBA中開銷最大的數(shù)據(jù)類型頻繁的讀取、賦值和比較會(huì)比明確類型的變量慢。優(yōu)化策略提前進(jìn)行類型判斷和轉(zhuǎn)換如果事先知道某列全是數(shù)字或日期可以在主循環(huán)外進(jìn)行類型判斷并在IsMatch函數(shù)中為這種類型實(shí)現(xiàn)快速路徑避免每次都進(jìn)行VarType判斷和CStr轉(zhuǎn)換。使用靜態(tài)正則對象如果匹配模式是“regex”且模式不變可以在模塊級(jí)別聲明一個(gè)靜態(tài)的RegExp對象在函數(shù)中重復(fù)使用避免反復(fù)創(chuàng)建銷毀。針對純字符串一維數(shù)組的快速路徑如果檢測到輸入是一維字符串?dāng)?shù)組且匹配模式是“contains”可以退化到使用原生Filter函數(shù)進(jìn)行第一步快速篩選然后再對結(jié)果應(yīng)用其他條件或處理。這相當(dāng)于用原生函數(shù)做了一次預(yù)過濾。限制條件數(shù)量在業(yè)務(wù)邏輯允許的情況下盡量減少單次篩選的條件數(shù)量。將最嚴(yán)格、最能過濾掉數(shù)據(jù)的條件放在前面判斷可以提前退出循環(huán)對于AND邏輯。數(shù)組切片與內(nèi)存操作對于超大規(guī)模數(shù)據(jù)可以考慮借助Windows API調(diào)用或編寫C DLL來實(shí)現(xiàn)內(nèi)存塊的高效比較但這大大增加了復(fù)雜度僅適用于極端性能場景。注意對于絕大多數(shù)Office VBA應(yīng)用場景數(shù)據(jù)處理量在幾千到幾萬行上述SuperFilter函數(shù)的性能是完全可接受的。優(yōu)化時(shí)應(yīng)遵循“先測量后優(yōu)化”的原則用Timer函數(shù)定位真正的熱點(diǎn)避免過度設(shè)計(jì)。6. 常見問題與排查技巧實(shí)錄在實(shí)際使用和調(diào)試SuperFilter函數(shù)的過程中我遇到了不少典型問題。這里記錄一下方便大家避坑。問題1函數(shù)返回#VALUE!錯(cuò)誤或運(yùn)行時(shí)錯(cuò)誤‘13’類型不匹配。排查思路檢查源數(shù)組確保傳入的SourceArray確實(shí)是一個(gè)數(shù)組。有時(shí)從某個(gè)可能返回單值的函數(shù)獲取數(shù)據(jù)需要先判斷IsArray()。檢查條件值類型當(dāng)使用“exact”模式比較數(shù)字和字符串時(shí)“123”和123是不相等的。確保你的Match參數(shù)類型與數(shù)組中對應(yīng)元素的類型一致或理解這種差異。二維數(shù)組的維度參數(shù)確保對二維數(shù)組調(diào)用時(shí)Dimension參數(shù)只傳了1或2。傳了0或3會(huì)導(dǎo)致后續(xù)計(jì)算索引時(shí)出錯(cuò)。正則表達(dá)式模式無效當(dāng)MatchMode為“regex”時(shí)如果傳入的匹配字符串不是有效的正則表達(dá)式regex.Test會(huì)出錯(cuò)。確保在IsMatch函數(shù)中做好了錯(cuò)誤捕獲。問題2篩選結(jié)果為空但明明覺得應(yīng)該有數(shù)據(jù)。排查思路字符串比較模式Compare參數(shù)這是最常見的原因。vbBinaryCompare區(qū)分大小寫”Apple”和”apple”不匹配。vbTextCompare不區(qū)分。檢查你的數(shù)據(jù)大小寫和參數(shù)設(shè)置。匹配模式MatchMode理解錯(cuò)誤想要精確匹配卻用了默認(rèn)的“contains”。仔細(xì)核對MatchMode參數(shù)。前后空格數(shù)據(jù)中可能存在肉眼不易察覺的首尾空格。使用Trim函數(shù)清洗源數(shù)據(jù)或條件值。可以在IsMatch函數(shù)內(nèi)部對字符串比較加入自動(dòng)Trim的選項(xiàng)但需注意對于精確匹配有時(shí)空格是有意義的。多條件邏輯ConditionLogic錯(cuò)誤誤將“OR”邏輯當(dāng)作“AND”使用或者反之。理清業(yè)務(wù)邏輯。Include參數(shù)理解反了Include:False是返回不匹配的項(xiàng)。確認(rèn)你是否想要的是排除。問題3處理大量數(shù)據(jù)時(shí)速度非常慢。排查思路啟用屏幕更新和計(jì)算關(guān)閉如果是在Excel中循環(huán)調(diào)用該函數(shù)處理大量單元格確保在代碼開頭設(shè)置了Application.ScreenUpdating False和Application.Calculation xlCalculationManual處理完再恢復(fù)。檢查是否在循環(huán)中重復(fù)調(diào)用函數(shù)避免在For Each循環(huán)中針對每一行數(shù)據(jù)都調(diào)用一次SuperFilter。應(yīng)該一次性將整個(gè)數(shù)據(jù)區(qū)域裝入數(shù)組用SuperFilter處理這個(gè)數(shù)組最后將結(jié)果一次性寫回工作表。這是VBA操作數(shù)據(jù)的黃金準(zhǔn)則盡量減少VBA與工作表之間的交互。審視匹配模式是否不必要地使用了開銷巨大的“regex”模式如果只是簡單匹配換成“exact”或“contains”會(huì)快很多。數(shù)據(jù)量是否真的超出了VBA處理范圍如果數(shù)組有幾十萬行VBA循環(huán)本身就會(huì)很慢?？紤]是否能在數(shù)據(jù)庫層面如Access、SQL Server進(jìn)行篩選或者使用Power Query進(jìn)行處理。問題4如何篩選“大于某個(gè)數(shù)值”或“介于某兩個(gè)日期之間”這類范圍條件解決方案當(dāng)前的SuperFilter框架主要通過MatchMode支持文本匹配模式。為了支持?jǐn)?shù)值/日期比較最整潔的擴(kuò)展方式是修改Match參數(shù)和IsMatch函數(shù)的設(shè)計(jì)。方案A快速但不優(yōu)雅將MatchMode擴(kuò)展增加“gt”大于、“l(fā)t”小于、“gte”大于等于、“l(fā)te”小于等于、“between”介于等模式。此時(shí)Match參數(shù)可能需要是一個(gè)兩元素?cái)?shù)組來表示范圍。這需要修改IsMatch函數(shù)來支持這些新的比較運(yùn)算符。方案B更靈活允許Match參數(shù)傳入一個(gè)自定義的回調(diào)函數(shù)函數(shù)指針。在IsMatch中如果發(fā)現(xiàn)Match是一個(gè)函數(shù)則調(diào)用這個(gè)函數(shù)來進(jìn)行判斷。這樣用戶可以將任何復(fù)雜的比較邏輯寫成一個(gè)獨(dú)立的函數(shù)傳入。這是最強(qiáng)大但也對使用者要求最高的方式。方案C實(shí)用折中對于簡單的范圍篩選可以結(jié)合使用Include:False。例如要篩選銷售額大于1000的可以先篩選出小于等于1000的然后取反。但這對于“介于兩者之間”這樣的條件就有點(diǎn)繞。最終我選擇了在內(nèi)部預(yù)留了擴(kuò)展接口但當(dāng)前版本主要優(yōu)化了字符串和精確匹配的通用場景。對于復(fù)雜的數(shù)值比較我通常建議使用方案C的變通方法或者直接在調(diào)用SuperFilter之前用簡單的數(shù)組循環(huán)預(yù)處理出一個(gè)標(biāo)志列然后再用SuperFilter進(jìn)行篩選。工具不是萬能的在復(fù)雜場景下組合使用多種方法往往是最佳實(shí)踐。