일전에 배열함수 안에서 와일드문자를 사용하는 방법에 대해 글을 작성한 적이 있다.
지난글에서 사용한 `iferror`와 `not` 을 `isnumber` 함수로 합치더라도, 아무래도 코드가 길어지기 마련인데, vba로 사용자 함수를 넣어 이 부분을 좀 간소화 시킬 수 있을 것이다.
아래와 같은 표가 있다고 하자. 조건영역1만 사용한다고 했을 때,
`사과`를 찾는다면 다음과 같은 수식을 사용할 것이다. 이 때는 뭐 별 문제가 없겠고, 구지 배열수식을 사용하지 않아도 된다.
(B열이 조건영역1, C열이 조건영역2, D열이 더할영역이다.)
{=SUM(IF(($B$3:$B$12="사과"),$D$3:$D$12))}
조건을 좀 변경해서, 조건영역1에 `사과` 아니면 `포도` 텍스트가 앞이든 가운데든 뒤든 포함되어있고, 조건영역2에 `서울`이라면 얘기가 좀 달라질 것이다. 사용해야하는 함수는 이 정도가 되겠고, 대상이 되는 값들도 꽤 많아질 것이다.
{=SUM(IF((ISNUMBER(FIND({"사과","포도"}, $B$3:$B$12)))*($C$3:$C$12="서울"),$D$3:$D$12))}
이 때, `사과` 아니면 `포도`가 포함되어있다 라는 조건을 좀 한개의 함수로 처리할 수 있다면 아무래도 좀 간소해 질 수 있을 것이다.
`사용자함수명(영역, or조건1, or조건2...)` 이런식이 될 것이다.
사용되는 함수를 먼저 보면 다음과 같은 형식이 될 것이고, 위의 코드와 정확하게 같은 기능을 한다.
{=SUM(IF((myIsIn($B$3:$B$12,"사과","포도"))*($C$3:$C$12="서울"),$D$3:$D$12))}
`사용자 정의 함수(UDF)`는 오류의 가능성이 단순 VBA 프로시저보다 훨씬 높아서, 기존에는 작성이 좀 꺼려졌는데, 이제 괜찮다. chatGPT를 통해 해당 기능을 하는 `UDF`를 생성하면 된다. 어떻게 작동하는지는 뭐 별로 중요하지 않다.
그리고 `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, t As Variant, cellVal As String
Dim i As Long, hit As Boolean
' --- 검색어 정규화(빈 값/공백 제외, Range나 배열도 허용) ---
termList = FlattenTerms(Terms)
If UBound(termList) < 0 Then
' 검색어가 없으면 모두 False
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
' --- 범위(2차원 배열) 처리 ---
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 termList(i) <> "" Then
If InStr(1, cellVal, termList(i), comp) > 0 Then
hit = True: Exit For
End If
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 termList(i) <> "" Then
If InStr(1, cellVal, termList(i), comp) > 0 Then
hit = True: Exit For
End If
End If
Next i
myIsIn = hit
End If
End If
End Function
' 내부 유틸: ParamArray 안의 값들을 문자열 배열로 평탄화
' - Range가 오면 각 셀을 순회하여 추가
' - 배열이 오면 각 요소를 추가
' - 공백 트림, 빈 문자열 제외
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
Else
' 기타 객체는 무시
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`를 사용하지 않는 기본 사용법도 알아는 두는게 나을듯하지만.. 그건 뭐 알아서..
끝.