작성일자 글쓴이 thkmon

<엑셀VBA 입문 9강> 특정 단어의 개수를 세는 방법

<엑셀VBA 입문 9강> 특정 단어의 개수를 세는 방법

9강입니다. 연재가 늦어져서 죄송합니다.

오늘은 특정 단어를 세는 함수를 만들어볼까 합니다.

오늘 내용은 어렵지만 가치가 있는 내용입니다.

오히려, 다소 어렵다면 잘 따라오고 있다고 생각하면 됩니다. 차분히 읽으면 이해됩니다.

1. include 함수

먼저 특정 단어가 존재하는지 검사하는 함수를 만들어봅시다.

이름은 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 = 1
    Else
        include = 0
    End If

End Function

8강까지의 내용을 잘 따라오셨다면 큰 어려움은 없습니다. 그래도 헷갈릴지 몰라 InStr 함수에 대해 간단히 소개합니다.

InStr 함수는 특정 텍스트(찾을값)이 대상 텍스트(대상값)의 몇 번째 글자수에 위치해있는지 알아보는 함수입니다.

InStr(시작인덱스, 대상값, 찾을값, vbTextCompare) 이렇게 사용합니다.

예를 들면 InStr(1, “이 문장에서 사과라는 단어를 찾습니다”, “사과”, vbTextCompare) 와 같이 사용합니다.

시작인덱스가 1이면 첫번째 글자부터 찾습니다.

그 결과 리턴값이 0 이면 대상값 중에 찾을값이 존재하지 않는 상태입니다.

리턴값이 0 보다 크면 존재하는 겁니다. 예를 들어 리턴값이 n이면 대상값의 n번째 자리수에 해당 텍스트가 존재하는 겁니다.

2. 개선된 include 함수 (includeMulti 함수)

이번엔 include 함수를 개선해보겠습니다. 개선된 include함수라고 해서, 이름은 includeMulti[인크루드 멀티]로 붙여보겠습니다.

아래와 같이 사용합니다.

 

보다시피 여러 범위에서 특정 단어가 존재하는지를 찾아내는 함수입니다.

이 함수를 만들기 위해서는 지난 시간(8강)에서 만든 getContent 함수가 필요합니다.

기억나실지 모르지만, getContent 함수는 내용이 제법 길고 까다로웠습니다.

이런 함수는 만들 때 한 번 이해해두고, 다음에는 그냥 잘 갖다 쓰면 됩니다.

그대로 가져와보겠습니다.

Function getContent(cell As Range)
    Dim resultVal
    resultVal = “”
   
    ‘addr 변수에 문자열을 저장한다. $가 포함되어 있다면 제거한다.
    ‘ex) cell.Address = “$A$1:$D$10” 이라면 addr = “A1:D10” 이 된다.
    ‘ex) cell.Address = “$A$1” 이라면 addr = “A1” 이 된다.
    Dim addr
    addr = cell.Address & “”
    addr = Replace(addr, “$”, “”)
   
    Dim colonIdx
    colonIdx = InStr(addr, “:”)
    If colonIdx > 0 Then
        ‘콜론이 존재하는 경우(다중범위)
        Dim leftAddr, leftColNum, leftRowNum
        leftAddr = Mid(addr, 1, colonIdx – 1)
        leftColNum = Range(leftAddr).Column
        leftRowNum = Range(leftAddr).Row
       
        Dim rightAddr, rightColNum, rightRowNum
        rightAddr = Mid(addr, colonIdx + 1, Len(addr) – colonIdx)
        rightColNum = Range(rightAddr).Column
        rightRowNum = Range(rightAddr).Row
       
        For r = leftRowNum To rightRowNum
            For c = leftColNum To rightColNum
                resultVal = resultVal & Cells(r, c).Value
                ‘MsgBox (“(” & r & “,” & c & “) : ” & Cells(r, c).Value)
            Next c
        Next r
       
        getContent = resultVal
    Else
        ‘콜론이 존재하지 않는 경우(단일범위)
        resultVal = Range(cell, cell).Value
        getContent = resultVal
    End If

End Function

한 번 잘 만들어둔 함수는 다음에 그냥 갖다 쓰면 됩니다.

내용을 다시 볼 필요가 없기도 합니다.

이렇게 안쪽 내용을 보지 않고 그냥 갖다쓸 수 있는 함수의 특성을 “블랙박스”(안쪽이 보이지 않는 검은색 상자)라고 합니다.

눈치채셨겠지만, 함수는 블랙박스에 가까울수록 좋아요.

안쪽 내용을 열심히 들여다보지 않아도, 쉽게 갖다쓸 수 있어야 좋다는 뜻입니다. 그래서 함수명을 잘 붙이는게 중요합니다.

여튼 includeMulti 함수는 아래와 같이 만듭니다.

Function includeMulti(cell As Range, textToFind)

    ‘대상값을 가져옴 (getContent 함수 사용)
    Dim val
    val = getContent(cell)
   
    ‘대상값 1번째 글자부터 textToFind 값 찾기
    Dim idx
    idx = InStr(1, val, textToFind, vbTextCompare)

    ‘인덱스 값을 리턴
    If idx > 0 Then
        includeMulti = 1
    Else
        includeMulti = 0
    End If

End Function

보시다시피 대상값 val을 가져올 때 Range(cell, cell).Value 이 아닌, 이미 만들어둔 getContent 함수를 사용했습니다. 이렇게 함수 안에서 다른 함수를 부르고, 작은 함수를 여러개 모아서 큰 함수를 만드는걸 잘해야 됩니다. 이게 코딩의 기본이고 VBA의 기본입니다.

아시다시피 getContnet 함수는 여러 셀 대상으로 가용하지만 1개 셀 대상으로도 가용합니다.

따라서 includeMulti 함수도 여러 셀 대상으로 가용하고 1개 셀 대상으로도 가용합니다.

3. getCount 함수

이어서 getCount 함수를 만들어봅니다. 양이 많지만 힘을 내서 만들어봅니다.

getCount 함수는 아래와 같이 사용합니다.

include 함수는 단순히 “사과”라는 단어가 존재하는지, 존재하지 않는지에 따라 1 과 0 으로 표시합니다.

이에 대비해 getCount 함수는 “사과”라는 단어를 세고 있습니다.

아래와 같이 코딩합니다.

Function getCount(cell, textToFind)

    ‘예외처리: 찾을값이 없으면 0 리턴
    If Len(textToFind) = 0 Then
        getCount = 0
        Exit Function
    End If

    ‘대상값을 가져옴
    Dim val
    val = Range(cell, cell).Value
   
    ‘예외처리: 대상값이 없으면 0 리턴
    If Len(val) = 0 Then
        getCount = 0
        Exit Function
    End If
   
    ‘textToFind 의 글자수
    Dim textLen
    textLen = Len(textToFind)
   
    ‘결과값 변수
    Dim resultCount
    resultCount = 0
   
    ‘현재 인덱스
    Dim curIdx
    curIdx = 0
   
    ‘기준 인덱스
    Dim axisIdx
    axisIdx = 1

    Do
        curIdx = InStr(axisIdx, val, textToFind, vbTextCompare)
        If curIdx > 0 Then
            resultCount = resultCount + 1
            axisIdx = curIdx + textLen
        Else
            Exit Do
        End If
    Loop

    getCount = resultCount

End Function

이번에 만든 getCount 함수 역시 InStr 함수를 기본으로 하고 있습니다.

Do Loop [두 루프] 문을 만들었으니, 무한루프에 걸리지 않게 조심해야 합니다. 참고로 무한루프에 걸렸다 싶으면 Ctrl + Break 키를 누르면 풀려납니다(함수 강제중지). Break 키는 키보드 최상단 우측부에 있습니다. 키를 찾기 힘드므로 (1) 저장을 자주 하시고 (2) 함수를 잘 짜서 무한루프 늪에 빠지지 않도록 합시다.

axisIdx는 axis index를 의미하는 변수입니다. 기준이 되는 인덱스라는 뜻이죠. curIdx 는 current index를 의미하는 변수입니다. 현재 인덱스를 담아두는 변수입니다. (참고로 axis[엑시스]는 “축”이라는 뜻입니다. 이를테면 “악의 축”은 the axis of evil [디 엑시스 오브 이블]이라고 합니다.)

처음에 axisIdx 의 초기값이 1입니다. 첫번째 글자로부터 찾을값(textToFind)을 검색합니다.

찾으면 curIdx의 값이 0 초과의 값을 갖게 되고, resultCount 값을 1 플러스 한다음 루프문 안에서 계속 검색합니다.

찾아내지 못하면 0 미만의 값을 가지므로 Exit Do 로 루프문을 나가게 됩니다.

여기서 약간 헷갈릴 수 있는 부분이 axisIdx = curIdx + textLen 라는 부분입니다.

axisIdx, 즉 기준이 되는 인덱스 구할 때, 찾은 인덱스(curIdx)에 찾을 글자수(textLen)를 더하고 있습니다.

사실 axisIdx = curIdx + 1 이렇게만 해도 됩니다.

만약 axisIdx = curIdx, 이렇게 할 경우 axisIdx 값은 변함이 없으므로 무한루프에 걸릴 가능성이 있습니다.

그런데 axisIdx = curIdx + 1 이렇게만 해두면 바로 다음 글자 인덱스부터 찾으므로 글자를 겹쳐 찾습니다.

무슨 말인가 하면,

“상상하다”이라는 문장이 있을 때, “상상”의 개수를 세어보면 1개입니다.

그럼, “상상상하다”라는 문장이 있을 때, “상상”의 개수를 세어보면  몇 개 일까요?

누군가에게는 1개이고, 누군가에게는 2개입니다.

겹치는 부분이 있기 때문입니다.

axisIdx = curIdx + 1 로 로직을 짜면 2개가 되고,

axisIdx = curIdx + textLen 로 로직을 짜면 1개가 됩니다.

저는 후자가 맞다고 보기 때문에(이미 검사한 부분은 지나가야 한다고 생각하기 때문에) textLen 을 더해주었습니다.

4. 개선된 getCount 함수 (getCountMulti 함수)


이어서 getCountMulti 함수를 만들어봅니다. 거의 다 왔습니다.

아래와 같이 사용합니다.

어떻게 만드는 걸까요?

include 함수를 개선한 방식과 똑같습니다.

val = Range(cell, cell).Value 부분을 val = getContent(cell) 로 교체해주고,

getCount 라는 단어를 getCountMulti 로 다 바꿔주면 끝입니다.

앞서와 마찬가지로, getCountMulti 함수는 셀 n개 대상으로도 사용 가능하지만, 셀 1개 대상으로도 사용 가능합니다. getContent 함수가 그랬기 때문이죠.

코드를 남겨둡니다.

Function getCountMulti(cell As Range, textToFind)

    ‘예외처리: 찾을값이 없으면 0 리턴
    If Len(textToFind) = 0 Then
        getCountMulti = 0
        Exit Function
    End If

    ‘대상값을 가져옴
    Dim val
    val = getContent(cell)
   
    ‘예외처리: 대상값이 없으면 0 리턴
    If Len(val) = 0 Then
        getCountMulti = 0
        Exit Function
    End If
   
    ‘textToFind 의 글자수
    Dim textLen
    textLen = Len(textToFind)
   
    ‘결과값 변수
    Dim resultCount
    resultCount = 0
   
    ‘현재 인덱스
    Dim curIdx
    curIdx = 0
   
    ‘기준 인덱스
    Dim axisIdx
    axisIdx = 1

    Do
        curIdx = InStr(axisIdx, val, textToFind, vbTextCompare)
        If curIdx > 0 Then
            resultCount = resultCount + 1
            axisIdx = curIdx + textLen
        Else
            Exit Do
        End If
    Loop

    getCountMulti = resultCount

End Function

2020.04.19(화) 내용추가

현재 getCountMulti 함수는 1개의 셀에 단어가 2번 포함되어 있을 경우 2개로 계산합니다.

ex) 1개의 셀 내의 값이 “사과사과”일 경우 사과를 2개로 계산

1개의 셀 내에 단어가 2번 들어가더라도 1개로 계산되게 하려면 아래 getCountMulti2 함수를 사용하기 바랍니다.

=getCountMulti2(A1:C3,”사과”) 이런식으로 사용 가능합니다.

Function getCountMulti2(cell As Range, textToFind)
    Dim resultCount
    resultCount = 0
   
    Dim idx
    idx = 0
   
    Dim resultVal
    resultVal = “”
   
    ‘addr 변수에 문자열을 저장한다. $가 포함되어 있다면 제거한다.
    ‘ex) cell.Address = “$A$1:$D$10” 이라면 addr = “A1:D10” 이 된다.
    ‘ex) cell.Address = “$A$1” 이라면 addr = “A1” 이 된다.
    Dim addr
    addr = cell.Address & “”
    addr = Replace(addr, “$”, “”)
   
    Dim colonIdx
    colonIdx = InStr(addr, “:”)
    If colonIdx > 0 Then
        ‘콜론이 존재하는 경우(다중범위)
        Dim leftAddr, leftColNum, leftRowNum
        leftAddr = Mid(addr, 1, colonIdx – 1)
        leftColNum = Range(leftAddr).Column
        leftRowNum = Range(leftAddr).Row
       
        Dim rightAddr, rightColNum, rightRowNum
        rightAddr = Mid(addr, colonIdx + 1, Len(addr) – colonIdx)
        rightColNum = Range(rightAddr).Column
        rightRowNum = Range(rightAddr).Row
       
        For r = leftRowNum To rightRowNum
            For c = leftColNum To rightColNum
                resultVal = Cells(r, c).Value
                ‘MsgBox (“(” & r & “,” & c & “) : ” & Cells(r, c).Value)
               
                ‘대상값 1번째 글자부터 textToFind 값 찾기
                idx = InStr(1, resultVal, textToFind, vbTextCompare)
                If idx > 0 Then
                    resultCount = resultCount + 1
                End If
            Next c
        Next r
       
        getCountMulti2 = resultCount
    Else
        ‘콜론이 존재하지 않는 경우(단일범위)
        resultVal = Range(cell, cell).Value
       
        ‘대상값 1번째 글자부터 textToFind 값 찾기
        idx = InStr(1, resultVal, textToFind, vbTextCompare)
        If idx > 0 Then
            resultCount = resultCount + 1
        End If
               
        getCountMulti2 = resultCount
    End If

End Function

이어지는 글 <엑셀VBA 입문 10강> VBA로 웹파싱하기 (HTML소스 가져오기)  : https://blog.naver.com/bb_/221267721181

이어지는 글 <엑셀VBA 입문 11-2강> 배열(Array) : https://blog.naver.com/bb_/221313490342

이어지는 글 <엑셀VBA 입문 12강> 특정파일 특정시트의 특정셀을 가져오는 매크로 : https://blog.naver.com/bb_/221288948784