excel 在数组上使用过滤器时,有没有办法进行精确匹配?

fwzugrvs  于 2023-02-10  发布在  其他
关注(0)|答案(2)|浏览(172)

我是VBA新手,正在尝试在VBA中创建一个用户定义函数,以确定是否存在标题和性别不匹配。例如:如果头衔是“Mr”,性别是“F”,那么它将返回一个布尔值TRUE(不匹配)。
但是,当性别是“F”,标题是“MR”时,它会一直返回FALSE,因为MR是MRS的子字符串。有没有办法确保它完全匹配?
Function GenderTitleMismatch(title As Variant, gender As Variant) As Boolean
title = UCase(trim(title))
gender = UCase(trim(gender))
If gender = "M" And UBound(Filter(Array("MR", "DR"), title)) = -1 Then
GenderTitleMismatch = True
ElseIf gender = "F" and Ubound(Filter(Array("MRS","MS","DR","MISS"), title)) = -1 Then
GenderTitleMismatch = True
Else
GenderTitleMismatch = False
End If
End Function

vecaoik1

vecaoik11#

您仍然可以使用您的方法,并通过强制使用分隔符来防止子字符串匹配:

Function GenderTitleMismatch(title As Variant, gender As Variant) As Boolean

    title = UCase(Trim(title)) & "/"
    gender = UCase(Trim(gender))
    
    GenderTitleMismatch = False
    
    If gender = "M" And UBound(Filter(Array("MR/", "DR/"), title)) < 0 Then
        GenderTitleMismatch = True
    End If
    
    If gender = "F" And UBound(Filter(Array("MRS/", "MS/", "DR/", "MISS/"), title)) < 0 Then
        GenderTitleMismatch = True
    End If

End Function

但是你可以进一步简化它,像这样:

Function GenderTitleMismatch(title As Variant, gender As Variant) As Boolean

    Dim srch As String: srch = UCase(Trim(title)) & "/" & UCase(Trim(gender))
    
    Dim valid(): valid = Array("MR/M", "DR/M", "MRS/F", "MS/F", "DR/F", "MISS/F")

    GenderTitleMismatch = UBound(Filter(valid, srch)) < 0

End Function
rsl1atfo

rsl1atfo2#

标题性别不匹配

Function TitleGenderMismatch(ByVal Title, ByVal Gender) As Boolean
    
    Dim Titles(): Titles = Array("mrm", "drm", "mrsf", "msf", "missf", "drf")
    
    Dim tStr As String: tStr = Trim(CStr(Title))
    'tStr = Replace(tStr, ".", "") ' remove the dot: e.g. USA, Canada...
    ' Allow title with name e.g. 'Mr John Smith'.
    Dim tSpacePosition As Long: tSpacePosition = InStr(tStr, " ")
    If tSpacePosition > 0 Then tStr = Left(tStr, tSpacePosition - 1)
    
    Dim gStr As String: gStr = Trim(CStr(Gender))
    ' Allow words: e.g. 'male' and 'feemale' instead of 'm' and 'f'.
    gStr = Left(gStr, 1)
    
    Dim IsMisMatch As Boolean ' '.Match' is case-insensitive
    IsMisMatch = IsError(Application.Match(tStr & gStr, Titles, 0))
    
    TitleGenderMismatch = IsMisMatch

End Function

相关问题