배열수식에서 와일드카드 문자 사용

배열수식 안에서 와일드카드 문자를 사용하는 방법은 여러 가지가 있다.
IFERROR 와 NOT 를 사용하는 방법도 있고, ISNUMBER 함수를 사용하는 방법도 있다.

이 방법들 모두 무난하지만 수식이 길어지기 마련인데, VBA로 사용자 정의 함수를 만들어 두면 이 부분을 조금 더 간소화할 수 있다.

아래와 같은 예가 있다고 가정하자.

조건영역 1만 사용한다고 가정하고, 사과를 찾는 경우 다음과 같은 수식을 사용하면 된다.
이 경우에는 별다른 문제가 없고 굳이 배열수식을 사용하지 않아도 된다. (B열이 조건범위1, C열이 조건범위2, D열이 합산범위이다.)

{ =SUM( IF( ($B$3:$B$12="사과"), $D$3:$D$12 )) }

img



조건을 조금 변경해
조건영역1에 사과 또는 포도가 포함되어 있고, 조건영역2가 겨울이라면 이야기가 달라진다.
이 경우에는 수식도 길어지고 논리식에 포함되는 값들도 꽤 많아진다.

{ =SUM(IF( (ISNUMBER(FIND({"사과","포도"}, $B$3:$B$12))) * ($C$3:$C$12="겨울"),$D$3:$D$12)) }

img



즉, 사과 또는 포도가 포함되어 있다는 조건을 조금 더 깔끔한 함수로 처리할 수 있다면 수식도 훨씬 간단해질 것이다.

사용자정의함수(범위, or조건1, or조건2…) 이런 형태가 가능하다면 말이다.
사용법은 함수 이름만 봐도 의미가 직관적이고, 기존 수식과 기능은 동일하지만 가독성은 훨씬 좋아진다.

{ =SUM(IF( (myIsIn($B$3:$B$12,"사과","사도")) * ($C$3:$C$12="겨울"),$D$3:$D$12)) }


사용자 정의 함수(UDF)는 과거에는 접근성이 다소 떨어졌지만 요즘은 ChatGPT 등을 활용해 필요한 기능을 빠르게 구현할 수 있다. 동작 원리를 완벽히 이해하지 않아도 실무에서 사용하는 데에는 큰 문제가 없다.

또한 xla 파일로 만들어 두면 필요할 때마다 불러와 재사용할 수도 있다.

아래는 실제로 사용한 코드이다.
myIsIn이라는 이름의 함수로 정의했다. 기능 대비 코드가 조금 길지만, 재사용성은 충분하다.

' myIsIn(Target, term1, term2, term3, ...)
' - Target: 범위 또는 단일 값
' - termN : 포함(contains)으로 검색할 키워드들 (가변 개수, OR 조건)
' 반환: Target과 동일한 크기의 TRUE/FALSE 배열 (단일 값이면 단일 TRUE/FALSE)
' 비교: 기본은 대소문자 구분 없음(vbTextCompare)

Function myIsIn(Target As Variant, ParamArray Terms() As Variant) As Variant

    Dim comp As VbCompareMethod: comp = vbTextCompare
    Dim arr, outArr(), r As Long, c As Long
    Dim termList() As String, cellVal As String
    Dim i As Long, hit As Boolean

    ' 검색어 정규화
    termList = FlattenTerms(Terms)

    If UBound(termList) < 0 Then
        If TypeName(Target) = "Range" Then
            arr = Target.Value
            ReDim outArr(1 To UBound(arr, 1), 1 To UBound(arr, 2))
            For r = 1 To UBound(arr, 1)
                For c = 1 To UBound(arr, 2)
                    outArr(r, c) = False
                Next c
            Next r
            myIsIn = outArr
        Else
            myIsIn = False
        End If
        Exit Function
    End If

    If TypeName(Target) = "Range" Then
        arr = Target.Value
    Else
        arr = Target
    End If

    If IsArray(arr) Then
        ReDim outArr(1 To UBound(arr, 1), 1 To UBound(arr, 2))
        For r = 1 To UBound(arr, 1)
            For c = 1 To UBound(arr, 2)
                If IsError(arr(r, c)) Or IsEmpty(arr(r, c)) Then
                    outArr(r, c) = False
                Else
                    cellVal = CStr(arr(r, c))
                    hit = False
                    For i = LBound(termList) To UBound(termList)
                        If InStr(1, cellVal, termList(i), comp) > 0 Then
                            hit = True: Exit For
                        End If
                    Next i
                    outArr(r, c) = hit
                End If
            Next c
        Next r
        myIsIn = outArr
    Else
        If IsError(arr) Or IsEmpty(arr) Then
            myIsIn = False
        Else
            cellVal = CStr(arr)
            hit = False
            For i = LBound(termList) To UBound(termList)
                If InStr(1, cellVal, termList(i), comp) > 0 Then
                    hit = True: Exit For
                End If
            Next i
            myIsIn = hit
        End If
    End If
End Function

Private Function FlattenTerms(ByVal Terms As Variant) As String()
    Dim buf() As String: ReDim buf(-1 To -1)
    Dim t As Variant, e As Variant, cell As Variant
    Dim s As String

    For Each t In Terms
        If IsObject(t) Then
            If TypeName(t) = "Range" Then
                For Each cell In t.Cells
                    If Not IsError(cell.Value) And Not IsEmpty(cell.Value) Then
                        s = Trim$(CStr(cell.Value))
                        If Len(s) > 0 Then PushString buf, s
                    End If
                Next cell
            End If
        ElseIf IsArray(t) Then
            For Each e In t
                If Not IsError(e) And Not IsEmpty(e) Then
                    s = Trim$(CStr(e))
                    If Len(s) > 0 Then PushString buf, s
                End If
            Next e
        Else
            If Not IsError(t) And Not IsEmpty(t) Then
                s = Trim$(CStr(t))
                If Len(s) > 0 Then PushString buf, s
            End If
        End If
    Next t

    FlattenTerms = buf
End Function

Private Sub PushString(ByRef arr() As String, ByVal s As String)
    Dim n As Long
    On Error GoTo init
    n = UBound(arr) + 1
    ReDim Preserve arr(0 To n)
    arr(n) = s
    Exit Sub
init:
    ReDim arr(0 To 0)
    arr(0) = s
End Sub


설명이 길어질 수 있으니 UDF를 사용하지 않는 기본 방식도 함께 익혀두는 것을 추천한다.
상황에 따라 더 단순한 방법이 정답

끝.