VBA自定义性别头衔校验函数中，Filter数组如何实现精确匹配？

你的代码中使用Filter函数导致无法实现精确匹配，因为Filter是子串匹配逻辑——比如当头衔为"MR"时，它会在女性允许的头衔"MRS"中识别到匹配的子串，错误地判定为有效头衔，进而返回False。

以下是两种实现精确匹配的修正方案：

方案一：遍历数组做精确匹配 #

适合后续需要扩展头衔列表的场景：

Function GenderTitleMismatch(title As Variant, gender As Variant) As Boolean
    Dim cleanedTitle As String
    Dim cleanedGender As String
    Dim allowedTitles As Variant
    Dim i As Integer
    Dim isMatch As Boolean
    
    ' 统一清理输入：转大写并去除首尾空格
    cleanedTitle = UCase(Trim(title))
    cleanedGender = UCase(Trim(gender))
    
    isMatch = False
    
    ' 根据性别获取对应允许的头衔数组
    Select Case cleanedGender
        Case "M"
            allowedTitles = Array("MR", "DR")
        Case "F"
            allowedTitles = Array("MRS", "MS", "DR", "MISS")
        Case Else
            ' 未知性别默认判定为不匹配（可根据需求调整）
            GenderTitleMismatch = True
            Exit Function
    End Select
    
    ' 遍历数组执行精确匹配
    For i = LBound(allowedTitles) To UBound(allowedTitles)
        If cleanedTitle = allowedTitles(i) Then
            isMatch = True
            Exit For
        End If
    Next i
    
    ' 未匹配到允许头衔则返回True（存在不匹配）
    GenderTitleMismatch = Not isMatch
End Function

方案二：用Select Case直接判断 #

适合头衔数量较少、逻辑更直观的场景：

Function GenderTitleMismatch(title As Variant, gender As Variant) As Boolean
    Dim cleanedTitle As String
    Dim cleanedGender As String
    
    cleanedTitle = UCase(Trim(title))
    cleanedGender = UCase(Trim(gender))
    
    Select Case cleanedGender
        Case "M"
            ' 男性有效头衔：MR、DR
            GenderTitleMismatch = Not (cleanedTitle = "MR" Or cleanedTitle = "DR")
        Case "F"
            ' 女性有效头衔：MRS、MS、DR、MISS
            GenderTitleMismatch = Not (cleanedTitle = "MRS" Or cleanedTitle = "MS" Or cleanedTitle = "DR" Or cleanedTitle = "MISS")
        Case Else
            ' 未知性别判定为不匹配（可按需修改）
            GenderTitleMismatch = True
    End Select
End Function

核心改进点 #

• 采用精确相等判断，彻底避免子串匹配的误判问题；
• 统一处理输入格式，消除大小写、首尾空格对判断结果的影响；
• 两种方案可根据实际需求选择，扩展性和可读性兼顾。

内容的提问来源于stack exchange，提问作者Emmanuel Natera