<엑셀VBA 입문 5강> 특정 단어를 포함하는지 확인하는 함수

<엑셀VBA 입문 5강> 특정 단어를 포함하는지 확인하는 함수

엑셀에서 가장 많이 사용하는 함수가 SUM[썸], COUNT[카운트]라면, 그 다음으로 많이 사용하는 함수는 SUMIF[썸이프], COUNTIF[카운트이프]가 될 것이다.

어째서일까? IF는 분류를 위한 함수다. 어떤 업무를 하든지 분류 작업이 필수다. 우리에게는 데이터를 분류하고 분류별로 계산하고자 하는 니즈가 있다.

쇼핑몰을 하고 있다면 와이셔츠의 수량, 청바지의 수량을 세어보게 되어있다. 설문조사를 하고 있다면 지역별 분류가 필요하다. 고객 문의를 접수하고 있다면 단순 상담인지 신규 가입인지, 탈퇴 요청인지 클레임인지 구분해야 한다.

또 하나 엑셀에 유명한 함수가 있다면 VLOOKUP[브이룩업]을 들 수 있다. 일반적으로 SUM, SUMIF, MID/LEFT/RIGHT, VLOOKUP이면 웬만한 엑셀 업무는 처리할 수 있다. VLOOKUP 역시 분류에 따른 특정값의 반환이 목표다. (참고로 VLOOKUP 함수를 몰라도 이 글을 읽는데는 지장이 없다)

하지만 SUMIF, COUNTIF, VLOOKUP에는 치명적 약점이 있다. 정확한 값을 요구한다는 점이다. 청바지의 수량만 더하고자할 때 COUNTIF는 특정값(ex: “청바지”)을 찾는다. “청바지”라는 단어를 포함하는 경우는 찾지 않는다.

다음은 서울이라는 단어가 포함될 경우 O를 기입, 포함되지 않을 경우 X를 기입하는 예제다.

서울이라는 단어가 포함되어 있을 경우 O를 기입, 그렇지 않으면 X를 기입했다. 여기서는 함수를 쓰지 않고 직접 입력했다. 항목 4개 정도는 사람이 기입할 수 있다. 하지만 4만 개라면 어떨까?

다행히 특정 단어를 포함하는지 확인하는 함수는 기존 엑셀에 존재한다. SEARCH 함수다. SEARCH(찾을문자, 대상텍스트) 식으로 사용한다.

=SEARCH(찾을문자, 대상텍스트)

ex) =SEARCH(“서울”, A1)

보다시피 특정 단어가 n번째 글자에 위치할 경우 n을 반환하고 있다. 다만 SEARCH 함수의 특징은 특정 단어를 찾아내지 못했을 경우 #VALUE! 에러를 뱉어낸다는 점이다. 그렇기에 SEARCH 함수는 항상 IF함수와 ISERROR 함수를 동원해 아래와 같이 사용하곤 한다.

=IF(ISERROR(SEARCH(찾을문자, 대상텍스트)), 0, SEARCH(찾을문자, 대상텍스트))

ex) =IF(ISERROR(SEARCH(“서울”, A1)), 0, SEARCH(“서울”, A1))

다소 복잡해보인다. 원래 남이 짠 코드는 복잡해보이는게 정상이니 인내심을 갖자. IF함수를 동원해서 SEARCH가 에러를 뱉을 경우 0 을 표현하고, 그렇지 않을 경우 SEARCH 의 반환값을 보여주는 명령어다. 즉, 결과값이 0 이면 특정 단어가 미포함 상태이고 결과값이 1 보다 크거나 같으면 포함 상태이다.

만약 VBA를 이용해 이러한 함수를 직접 만들어보면 어떨까? 방법은 아래와 같다.

1. 엑셀을 켠다.

2. Alt + F11 키를 눌러 VBA를 켠다.

3. 상단 메뉴의 [삽입] – [모듈] 클릭한다. 모듈 창이 뜬다.

4. 아래와 같이 코드를 입력한다.

Function indexOf(cell, textToFind)

    ‘cell의 값을 가져옴
    Dim val
    val = Range(cell, cell).Value

    ‘cell의 값 1번째 글자부터 textToFind 값 찾기
    Dim idx
    idx = InStr(1, val, textToFind, vbTextCompare)

    ‘인덱스 값을 리턴
    indexOf = idx

End Function

5, 엑셀 시트에서 indexOf(대상텍스트, 찾을문자) 로 함수를 사용해보자.

ex) =indexOf(A1, “서울”)

 

​

이런식으로 하면 =IF(ISERROR(SEARCH(찾을문자, 대상텍스트)), 0, SEARCH(찾을문자, 대상텍스트)) 라는 함수를 indexOf라는 한 단어의 함수로 줄여 사용할 수 있다.

물론 VBA 코드를 이해하는게 처음에는 쉽지 않을 것이다. 가급적 “쉬운 것인데 왜 이해를 못하지”라고 생각하지 말고, “정말 어렵고 가치있는 것이므로 이해하지 못하는게 당연하다”고 받아들이길 바란다.

먼저 VBA에는 내장함수로 InStr이라는 함수가 있다. “InStr(1, val, textToFind, vbTextCompare)” 이라는 명령어는 “문자찾기(1번째글자부터, 대상텍스트, 찾을문자열, 텍스트비교)”라는 의미로 받아들이면 된다. InStr의 리턴값은 찾아낸 글자위치이다. 예를 들어 찾는 값을 대상 텍스트의 첫 번째 글자에서 찾았다면 숫자 1을 리턴하고, 문자를 찾지 못하면 0 을 리턴한다.

함수 내용을 풀어보자면 먼저 첫번째인자로 넘어온 셀의 값(cell)을 변수 val에 담고, VBA 내장함수인 InStr을 이용해서 cell의 값 내부에서 두번째 인자(textToFind)의 위치를 찾는다. 이후 결과값(위치값)을 변수 idx에 담고, 그대로 리턴한다.

만약 포함하는지를 O, X로 표현하고 싶다면 include 라는 함수로 변형해서 사용할 수도 있다. 다음은 그 예다.

Function include(cell, textToFind)

    ‘cell의 값을 가져옴
    Dim val
    val = Range(cell, cell).Value

    ‘cell의 값 1번째 글자부터 textToFind 값 찾기
    Dim idx
    idx = InStr(1, val, textToFind, vbTextCompare)

    ‘인덱스 값을 리턴
    If idx > 0 Then
        include = “O”
    Else
        include = “X”
    End If

End Function

 

이렇게 이번 강의에서는 include라는 이름의 특정 단어를 포함하는지 확인하는 함수를 만들어보았다. 내용상으로는 앞서 만든 indexOf에 비해 큰 차이가 없다. 내용 중에 익숙한 함수명이 보일텐데, 그 이름도 유명한 IF[이프]다. VBA에서 IF문은 다음과 같이 사용한다.

1. IF문 첫 번째

If 조건 Then

    ‘조건 충족시 실행할 내용

End If

2. IF문 두 번째

If 조건 Then

    ‘조건 충족시 실행할 내용

Else

    ‘조건미충족시 실행할 내용

End If

3. IF문 세 번째

If 조건1 Then

    ‘조건1 충족시 실행할 내용

Else If 조건2 Then

    ‘조건2 충족시 실행할 내용

End If

4. IF문 네 번째

If 조건1 Then

    ‘조건1 충족시 실행할 내용

Else If 조건2 Then

    ‘조건2 충족시 실행할 내용

Else If 조건3 Then

    ‘조건3 충족시 실행할 내용

Else

    ‘모든 조건 미충족시 실행할 내용

End If

간단하게나마 VBA 코드를 다루고자 한다면 간단한 내장함수들은 사용법을 익혀둬야 한다. 함수 사용법을 일부러 외울 필요는 없고 나중에라도 인터넷 검색을 통해 어떻게 사용했는지 떠올려볼 수 있으면 된다. 아니면 적당히 복사-붙여넣기를 통해 코드를 짜도 된다. 시험 공부는 컨닝이 허용되지 않지만 업무는 아무래도 상관없다. 일만 잘하면 장땡이다.

기회가 닿는대로 VBA 함수를 다룰 예정이지만 함수 하나하나를 자세히 설명하기는 곤란하다. 자꾸 보면 자연스럽게 이해가 되는 날이 올 것이다. 대표적인 VBA 내장함수는 If, MsgBox, For, Do 문 등이 있다. 차차 배울 수 있을 것이다.

이어지는 글 <엑셀VBA 입문 6강> VBA 저장하는 법 : https://blog.naver.com/bb_/221253136556

이어지는 글 <엑셀VBA 입문 7강> VBA 기초 명령어 : https://blog.naver.com/bb_/221253767009
이어지는 글 <엑셀VBA 입문 8강> 셀 내용 가져오기 : https://blog.naver.com/bb_/221260814900