[엑셀] Excel VBA 코드 작성 - 엑셀 문자열 찾는 ZLOOKUP - 사용자 정의 엑셀 함수 만들기
[엑셀] Excel VBA 코드 작성 - 엑셀 문자열 찾는 ZLOOKUP
- 사용자 정의 엑셀 함수 만들기
#엑셀 LOOKUP함수 #엑셀_사용자정의_함수
ZLOOKUP 함수는 Excel VBA를 사용하여 맞춤형 검색 기능을 구현한 것입니다.
이 함수는 지정된 범위 내에서 문자열을 검색하고, 해당 문자열이 포함된 행에서 특정 컬럼의 값을 반환합니다
엑셀 2010 이후 , XLOOKUP 함수가 있으면 된다는데 엑셀 2010 이전 버전이라 작동이 안되어서 직접 작성해 보았습니다.
엑셀에서 원하는 영역에서 특정 문자열이 있는 항목을 찾고 싶은데 VLOOKUP, HLOOKUP 같은 문자열을 찾는 함수들을 이용하는 것은 했지만 원하는 결과를 얻기 어려웠습니다. 그래서 여러 기능들을 찾아 보다가 직접 만들어 보기로 했습니다.최근 엑셀에서는 XLOOKUP으로 가능하다는데 제가 사용하는 버전에서는 없는 함수 였습니다.
XLOOKUP은 Excel 2016 Excel 2019에서는 사용할 수 없습니다. 그러나 최신 버전의 Excel을 사용하여 다른 사용자가 만든 XLOOKUP 함수와 함께 Excel 2016 또는 Excel 2019의 통합 문서를 사용하는 상황이 발생할 수 있습니다.
- https://support.microsoft.com/ko-kr/office/xlookup-%ED%95%A8%EC%88%98-b7fd680e-6d10-43e6-84f9-88eae8bf5929
1. VBA로 사용자 함수 코드 작성
- 엑셀 시트에서 오른쪽 마우드 클릭으로 아래 "코드보기" 속성을 클릭합니다.
2. 엑셀 VBA에서 모듈(Modult) 추가
코드 작성 에디터 상단 삽입 > 모듈을 선택하여 추가 합니다. 소스를 에디터 창에 복사하고 저장하면 됩니다. 저장하면 자동으로 컴파일이되어 엑셀에 추가되며, 엑셀 시트에 가서 사용자 함수를 이용할 수 있습니다.
3. VBA ZLOOKUP 함수 작성하기
ZLOOKUP 함수는 선택된 검색 영역에서 검색 문(searchString) 을 포함하는 문자열이 있는 경우 원하는 컬럼열의 값을 출력합니다.
ZLOOKUP (searchRange, searchString , searchColumn , returnColumn , isFirst )
ZLOOKUP (찾을영역, 검색어 , 찾는 컬럼 순번 , 찾는시트에서 반환 컬럼 순번 , '첫번째 결과값' )
- searchRange : 검색을 수행할 Excel 범위입니다.
- searchString : 검색할 문자열입니다. 이 문자열을 포함하는 셀을 찾습니다.
- searchColumn : 검색할 컬럼열의 순번 (1부터) : (중요 : 검색 영역의 컬럼 상대위치 순번임 )
- returnColumn : 검색 결과로 해당 시트의 절대 컬럼 열 순번
ex) 1 : A열 2: B열 , 3: C열 ... (중요: 선택영역의 컬럼열 순번 아님에 주의)
- isFirst : TRUE이면 첫번째 결과을 FALSE이면 마지막 결과값 을 출력합니다.
4. ZLOOKUP 함수 소스
Function ZLOOKUP(searchRange As Range, searchString As String, searchColumn As Long, returnColumn As Long, isFirst As Variant) As Variant
' ZLOOKUP : 검색 영역에서 검색 문을 포함하는 문자열이 있는 경우 원하는 컬럼의 값을 출력합니다.
' searchRange : 검색 영역
' searchString : 검색할 문자열 (검색어가 포함된 문자열을 찾는다.)
' searchColumn : 검색 컬럼열 (1부터)
' returnColumn : 결과 컬럼열 (1부터)
' isFirst : 첫번째, 마지막값 여부
Dim cell As Range
Dim found As Boolean
Dim result As Variant
Dim foundRow As Range
Dim foundIndex As Long
Dim tempIndex As Long
found = False ' 초기 찾기 상태 설정
result = "" ' 기본값을 빈 문자열로 설정, 찾지 못했을 때 반환됨
tempIndex = 0
For Each cell In searchRange.Columns(searchColumn).Cells
' cell.Value의 길이가 searchString의 길이와 같은지 확인하여 일치하는지 비교합니다.
' InStr([start], string1, string2, [compare])
' vbTextCompare : 대소문자를 구분하지 않고 문자열 비교
If InStr(1, cell.Value, searchString, vbTextCompare) > 0 Then
' 찾은 경우, 해당 행을 foundRow에 복사합니다.
Set foundRow = cell.EntireRow
found = True
If isFirst Then
Exit For
End If
End If
Next cell
If found Then
' 찾은 행의 여섯 번째 항목의 값 출력
result = foundRow.Cells(1, returnColumn).Value
Else
' result = tempIndex & searchString & "을(를) 찾을 수 없습니다."
result = ""
End If
ZLOOKUP = result ' 결과 반환
End Function
함수 로직
1. 초기 found 상태는 False로 설정되고, result는 빈 문자열로 초기화됩니다.
2. searchRange의 searchColumn 컬럼을 순회하면서 각 셀에 searchString이 포함되어 있는지 확인합니다.
3. 이때 InStr 함수를 사용하여 대소문자를 구분하지 않는 문자열 검색을 수행합니다.
4.만약 searchString이 포함된 셀을 찾으면, 해당 셀의 전체 행을 foundRow로 설정하고, found를 True로 변경합니다.
5. isFirst가 True로 설정되어 있다면, 첫 번째 일치 항목을 찾은 후 즉시 반복문을 종료합니다. 이는 첫 번째 일치하는 값만 반환하고자 할 때 사용됩니다.
6. 반복문이 종료된 후, 일치하는 항목을 찾았다면 (found = True), foundRow에서 returnColumn의 값을 result에 저장합니다.
7. 만약 일치하는 항목을 찾지 못했다면, result는 사용자에게 어떤 문자열이 찾을 수 없었는지의 메시지를 반환하도록 설정됩니다.
8. 최종적으로 result 값을 함수의 반환 값으로 지정합니다.
5. ZLOOKUP 함수 사용법
* 아주 중요한 것
= ZLOOKUP ( 검색할 영역, 검색어, 검색영역에서 검색할 컬럼열, 해당 시트에서 리턴할 컬럼열 , TRUE)
= ZLOOKUP ( $B$7:$D$10, F15, 2, 4 , TRUE)
- 검색할 영역에서 2번째 열을 검색하여 운영비, 핵생회비, 개발 연구비 등을 찾는 예시입니다.
- 검색할 영역 : $B$7:$D$10 ( B7:D10 으로 복사를 해도 유지하기 위해 앞에 '$' 를 붙입니다.)
- F15 : F 15열의 검색어에 해당하는 값으로 "운영비"
- 2 : 검색할 영역 ( 검색 영역에서 2번째 열임 , C열임)
- 3 : 검색 결과로 반환할 컬럼 (4 = D열) , returnColumn 은 주의 바랍니다.
[ 검색할 데이터 시트 ]
[ 검색 하는 예시 ]
- 사용예시 - 운영비 - 항목
ZLOOKUP( B7:D10, "운영비", 2, 3, TRUE) : 현재 시트의 B7~D:10에서 2번째 열에서 검색하는 경우
ZLOOKUP('검색할 시트명'!$B7:$D$10, F5, 2, 3, TRUE) : 특정 시트의 값을 지정하는 경우 시트명을 추가 합니다.
. searchColumn : 2 , 검색 영역의 상대 컬럼 2번째 컬럼 " 세부항목(검색할곳)
. returnColumn : 3 , 찾는 시트의 절대 컬럼 위치 3번째열 (C열)
. isFirst : TRUE
사용예시 - 운영비 - 금액: 처음 검색된 값
ZLOOKUP('검색할 시트명'!$B7:$D$10, F5, 2, 4, TRUE) : 특정 시트의 값을 지정하는 경우 시트명을 추가 합니다.
. searchColumn : 2
. returnColumn : 4 : 찾는 시트의 절대 컬럼 위치 4번째열 (D열)
. isFirst : TRUE
사용예시 - 운영비 - 금액 - 마지막값:
ZLOOKUP('검색할 시트명'!$B7:$D$10, F5, 2, 4, FALSE) : 검색 결과의 마지막 값을 리턴 합니다.
. searchColumn : 2
. returnColumn : 4 : 찾는 시트의 절대 컬럼 위치 4번째열 (D열)
. isFirst : FALSE , 검색 결과의 마지막 값을 리턴
[ 개발 후기 ]
작성하고 사용하다보니 returnColumn 이 헷갈릴듯 합니다.
처음에는 선택된 행렬의 순서 번호를 넣으려고 했는데 .. 상대적인 위치를 잡다보니 헷갈려서 간편하게 작성하고자 정리 했습니다. 상대적인 위치를 선택하는 것은 수정하면 되는데 간혹 해당 셀의 값을 복사하다 보니 순서가 이동되고 앞뒤로 세기도 어려워서 해당 시트의 컬럼 순서로 정했습니다.
- 해당 시트에서 리턴할 컬럼열 : 해당 검색 결과 값을 출력할 열을 해당 시트에서 위치하는 열로 정한 이유는 많지만
검색어 왼쪽 또는 오른쪽으로 정하기 어렵다는 문제도 있고 실제 검색 위치를 기준으로 상대적인 위치는 더 헷갈리는 문제가 발생하여 해당 시트의 컬럼 열로 했다. 이럴 거면 "시트명" 검색어 , 검색할 컬럼, 반환할 컬럼으로 할까도 생각해봅니다. ㅠㅠ
엑셀 VBA 모듈함수 작성 예시
다음과 같이 VBA로 필요한 함수를 만들어 엑셀에서 사용 가능합니다. 각 시트별로 소스를 넣으면 해당 시스만 사용 가능하고 , 프로그램 전체에서 이용 가능하도록 추가도 가능합니다.
- 엑셀 시트에서 "소스보기"를 하면 아래 창이 뜹니다. 코드를 복사해서 붙이고 저장하면 됩니다.
- 오류가 나는 경우 F8을 이용해서 오류가 나는 행에 대한 내용을 수정하여야 하는데 그때는 개발 내공이 필요 합니다.
기타 사용자 함수 작성 예시
Function FindAndDisplayLike(searchRange As Range, searchString As String, searchColumn As Long, returnColumn As Long, isFirst As Variant) As Variant
' FindAndDisplayLike : 검색 영역에서 왼쪽에서 검색 문장이 일치하는 문자열이 있는 경우 원하는 컬럼의 값을 출력합니다.
' searchRange : 검색 영역
' searchString : 검색 문자열 (첫글자부터 매칭이 되는 경우)
' searchColumn : 검색 컬럼열 (1부터)
' returnColumn : 결과 컬럼열 (1부터)
' isFirst : 첫번째, 마지막값 여부
' Editor : couplewith.tistory.com
Dim cell As Range
Dim found As Boolean
Dim result As Variant
Dim foundRow As Range
Dim foundIndex As Long
Dim tempIndex As Long
found = False ' 초기 찾기 상태 설정
result = "" ' 기본값을 빈 문자열로 설정, 찾지 못했을 때 반환됨
tempIndex = 0
For Each cell In searchRange.Columns(searchColumn).Cells
' cell.Value의 길이가 searchString의 길이와 같은지 확인하여 일치하는지 비교합니다.
If Len(cell.Value) >= Len(searchString) Then
If Left(cell.Value, Len(searchString)) = searchString Then
' 찾은 경우, 해당 행을 foundRow에 복사합니다.
Set foundRow = cell.EntireRow
found = True
If isFirst Then
Exit For
End If
End If
End If
Next cell
If found Then
' 찾은 행의 여섯 번째 항목의 값 출력
result = foundRow.Cells(1, returnColumn).Value
Else
' result = tempIndex & searchString & "을(를) 찾을 수 없습니다."
result = ""
End If
FindAndDisplayLike = result ' 결과 반환
End Function
참고
* 사용자 정의 엑셀 함수 작성 : https://couplewith.tistory.com/591
* XLOOKUP : 엑셀 최신 버전에서 제공하는 XLOOKUP (어렵다 ㅜㅜ)