엑셀에서 특정 문자가 포함된 개수 세는 방법

1. 개요

엑셀은 데이터를 정리하고 분석하기 위한 강력한 도구입니다. 특정 문자가 포함된 개수를 세는 것은 데이터 분석 과정에서 종종 필요한 작업입니다. 이 블로그 글에서는 엑셀에서 특정 문자가 포함된 개수를 세는 다양한 방법을 알아보겠습니다.

2. COUNTIF 함수

가장 간단하고 일반적인 방법은 COUNTIF 함수를 사용하는 것입니다. COUNTIF 함수는 특정 범위에서 특정 조건을 만족하는 셀의 개수를 세는 함수입니다. 아래의 예제를 통해 COUNTIF 함수를 사용하는 방법을 알아보겠습니다.

예제 1:

먼저, 아래와 같은 엑셀 시트를 생성합니다.

번호	이름	휴대폰 번호
1	홍길동	010-1234-5678
2	김철수	010-2345-6789
3	이영희	010-3456-7890

이제 휴대폰 번호 칼럼에서 "010"을 포함한 셀의 개수를 세고 싶다고 가정해보겠습니다. COUNTIF 함수를 사용하면 아래와 같이 셀에 공식을 입력할 수 있습니다.

=COUNTIF(C2:C4, "*010*")

위의 공식은 휴대폰 번호 칼럼(C2:C4)에서 "010"을 포함한 셀의 개수를 세는 것입니다. COUNTIF 함수의 두 번째 인수에는 검색 범위를 지정하고, 세 번째 인수에는 검색 조건을 입력합니다. 이 경우, "*010*"은 어떤 문자열 앞뒤에 "010"이 포함된 것을 의미합니다. 결과값은 3이 나와야 합니다.

위의 예제에서는 특정 문자열의 포함 여부를 검사했지만, COUNTIF 함수를 사용하면 다양한 조건에 따라 개수를 세는 것이 가능합니다. 예를 들어, 시작하는 문자열을 찾거나, 숫자 범위 내의 값을 찾을 수도 있습니다.

예제 2:

아래와 같은 엑셀 시트에서 숫자 칼럼에서 50 이상인 값을 세고 싶다고 가정해보겠습니다.

번호	이름	점수
1	홍길동	80
2	김철수	65
3	이영희	90

이 경우, 아래와 같이 COUNTIF 함수를 사용할 수 있습니다.

=COUNTIF(C2:C4, ">=50")

위의 공식은 점수 칼럼(C2:C4)에서 50 이상인 값을 세는 것입니다. 결과값은 2가 나와야 합니다.

3. LEN 함수와 SUBSTITUTE 함수 조합

COUNTIF 함수는 특정 문자열을 찾을 때 사용하기 좋습니다. 하지만, 특정 문자열이 아니라 어떤 문자열이든 포함된 개수를 세고 싶다면 다른 방법을 사용해야 합니다. 그럴 때는 LEN 함수와 SUBSTITUTE 함수를 조합해서 사용할 수 있습니다.

예제 3:

아래와 같은 엑셀 시트에서 "a" 문자가 포함된 셀의 개수를 세고 싶다고 가정해보겠습니다.

번호	이름	메모
1	홍길동	사과, 오렌지, 바나나
2	김철수	포도, 사과
3	이영희	바나나, 딸기

이 경우, 아래와 같이 도움말 칼럼을 추가하고, 아래의 공식을 입력합니다.

번호	이름	메모	도움말
1	홍길동	사과, 오렌지, 바나나	=LEN(B2)-LEN(SUBSTITUTE(B2, "a", ""))
2	김철수	포도, 사과	=LEN(B3)-LEN(SUBSTITUTE(B3, "a", ""))
3	이영희	바나나, 딸기	=LEN(B4)-LEN(SUBSTITUTE(B4, "a", ""))

위의 공식은 메모 칼럼에서 "a"를 제외한 문자열의 길이를 세는 것입니다. SUBSTITUTE 함수는 지정한 문자를 다른 문자로 대체하는 함수입니다. 여기서는 "a"를 공백으로 대체했습니다. 따라서, SUBSTITUTE 함수의 결과로 나오는 문자열의 길이를 LEN 함수를 사용하여 구할 수 있습니다. 마지막으로, 그 길이에서 원래 문자열의 길이를 빼면 "a"를 제외한 문자열의 길이가 나옵니다.

위의 예제는 "a"를 예시로 들었지만, 어떤 문자든지 SUBSTITUTE 함수를 사용하여 대체한 다음, 길이를 구하면 특정 문자가 포함된 개수를 세는 것이 가능합니다.

4. VBA 코드

마지막으로, VBA 코드를 사용하여 특정 문자가 포함된 개수를 세는 것도 가능합니다. VBA(Visual Basic for Applications)는 엑셀의 매크로 기능을 활용하여 프로그래밍을 할 수 있게 해주는 언어입니다. 아래의 예제를 통해 VBA 코드를 사용하는 방법을 알아보겠습니다.

예제 4:

먼저, 엑셀의 개발 도구 탭에서 Visual Basic을 클릭하여 VBA 편집기를 엽니다. 그리고 아래의 코드를 입력합니다.

Function CountStringsWithinRange(rng As Range, substring As String) As Integer     Dim cell As Range     Dim count As Integer          count = 0          For Each cell In rng         If InStr(1, cell, substring) > 0 Then             count = count + 1         End If     Next cell          CountStringsWithinRange = count End Function 

위의 코드는 CountStringsWithinRange라는 사용자 정의 함수를 정의한 것입니다. 이 함수는 특정 범위에서 특정 문자열을 포함하는 셀의 개수를 세는 역할을 합니다.

이제 엑셀 시트로 돌아와서 아래와 같은 공식을 입력할 수 있습니다.

=CountStringsWithinRange(C2:C4, "010")

이 공식은 휴대폰 번호 칼럼(C2:C4)에서 "010"을 포함한 셀의 개수를 세는 것입니다. 결과값은 3이 나와야 합니다.

VBA 코드를 사용하여 특정 문자가 포함된 개수를 세려면, 엑셀 시트의 매크로 기능을 사용해야 합니다. 따라서, 초보자에게는 조금 어려울 수 있습니다. 하지만, VBA는 강력한 기능을 가지고 있기 때문에 데이터 분석에 더욱 효과적으로 활용할 수 있습니다.

5. 결론

위에서는 엑셀에서 특정 문자가 포함된 개수를 세는 다양한 방법을 알아보았습니다. COUNTIF 함수를 사용하면 가장 간단하게 특정 문자열을 검색하여 개수를 세는 것이 가능합니다. 또한, LEN 함수와 SUBSTITUTE 함수를 조합하여 어떤 문자든지 포함된 개수를 세는 방법도 있습니다. 마지막으로, VBA 코드를 사용하여 더욱 복잡한 조건에 따라 개수를 세는 것이 가능합니다.

2023.10.08 - [Future Items] - 엑셀 콤마 제외한 숫자 개수 세는 방법

엑셀 콤마 제외한 숫자 개수 세는 방법

2023.10.08 - [Future Items] - 엑셀에서 텍스트, 문자 및 단어의 개수를 세는 수식 및 함수

엑셀에서 텍스트, 문자 및 단어의 개수를 세는 수식 및 함수

각 방법마다 장단점이 있으니 상황에 맞게 적절하게 선택하여 사용하시면 됩니다. 데이터 분석에 있어서 특정 문자가 포함된 개수를 세는 것은 중요한 작업 중 하나입니다. 이 블로그 글을 통해 다양한 방법을 익히고 응용하여 더 나은 데이터 분석을 할 수 있기를 바랍니다.