Excel 엑셀에서 특정 텍스트가 포함된 셀 개수를 계산하는 방법 3가지(특정 문자열 포함 셀 개수)

By

엑셀 시트에서 특정 텍스트(문자열)가 포함된 셀의 개수를 구하고자 한다면 어떻게 하면 될까요?
특정 문자열의 텍스트 뿐만 아니라 특정 숫자나 기호가 포함된 셀의 개수를 계산해야 하는 경우도 종종 생길 수 있습니다.

이번 글에서는 Excel 엑셀에서 특정 텍스트가 포함된 셀 개수를 계산하는 방법 3가지(특정 문자열 포함 셀 개수)에 대해 알아보겠습니다.

엑셀에서 COUNTIF 함수를 사용하여 특정 문자열 포함 셀 개수 계산

COUNTIF 함수는 지정 범위 내에서 주어진 조건을 충족하는 셀의 개수를 계산하는 함수 입니다.

  • COUNTIF(range, criteria)
  • range : 조건에 맞는 셀의 수를 구하려는 셀 범위입니다.
  • criteria : 숫자, 식, 텍스트 형태의 조건입니다.

1. 아래 예제와 같이 A열의 셀 텍스트 값에서 “in”이라는 문자열이 포함된 셀의 개수를 구하는 수식에 대해 설명해 보겠습니다.

2. 적용한 수식은 =COUNTIF(A2:A6, “*in*”) 입니다.

  • 조건에 맞는 셀의 수를 구하려는 셀 범위는 A2에서 A6입니다.
  • 특정 텍스트 “in” 문자열을 포함하는 셀의 개수를 구하기 위해서 별표(*) 문자의 와일드카드를 사용하였습니다.
  • 와일드카드를 “in” 앞,뒤에 붙여줌으로 해서 “in”이라는 문자열이 셀의 어느 위치에 있든지 존재하기만 하면 Count 계산에 포함 됩니다.
Excel 엑셀에서 특정 텍스트가 포함된 셀 개수를 계산하는 방법 3가지(특정 문자열 포함 셀 개수) 1

3. A2, A3, A5, A6 이렇게 4개의 셀에 “in”이라는 특정 텍스트가 존재한다는 것을 확인할 수 있습니다.

엑셀에서 SUMPRODUCT, SEARCH 함수를 사용하여 특정 문자열 포함 셀 개수 계산

본격적인 설명에 앞서 사용되는 함수에 대해 간략히 설명하겠습니다.

  • SUMPRODUCT 함수는 배열 또는 범위의 대응되는 값끼리 곱해서 그 합을 구하는 함수입니다.
  • SEARCH 함수는 왼쪽에서 오른쪽으로 검색하여 지정한 문자 또는 텍스트 스트링이 처음 발견되는 곳에서 문자 개수를 구합니다. 이때 대/소문자를 구분하지 않습니다.
  • FIND 함수는 지정한 텍스트를 다른 텍스트 내에서 찾아 해당 문자의 시작 위치를 나타냅니다. 이때 대/소문자를 구분합니다.
  • ISNUMBER 함수는 값이 숫자이면 TRUE를, 그렇지 않을 경우 FALSE를 돌려줍니다.

1. 아래 화면은 SUMPRODUCT 함수를 사용하여 특정 텍스트가 포함된 셀의 수를 계산하는 수식입니다.

  • 수식 : =SUMPRODUCT(–ISNUMBER(SEARCH(“in”,A2:A6)))

2. 각 단계별로 설명해 보겠습니다.

  • SEARCH(“in”,A2:A6)
  • 셀 범위에서 “in”이라는 문자을 찾으면 처음 발견된 곳에서 문자 개수를 반환합니다.
  • 결과적을 {16, 14, #VALUE!, 25, 7}이 반환됩니다.
  • ISNUMBER(SEARCH(“in”,A2:A6))
  • ISNUMBER 결과로 {TRUE, TRUE, FALSE, TRUE, TRUE}가 반환됩니다.
  • TRUE와 FALSE 값을 각각 1과 0으로 변환하기 위해 이중 부정(–) 기호를 사용합니다.
  • –ISNUMBER(SEARCH(“in”,A2:A6)) 결과로 {1, 1, 0, 1, 1}로 변환됩니다.
  • SUMPRODUCT(–ISNUMBER(SEARCH(“in”,A2:A6)))는 결과적으로 SUMPRODUCT({1, 1, 0, 1, 1}) 배열을 합산하고 최종 결과로 4를 반환하게 됩니다.
Excel 엑셀에서 특정 텍스트가 포함된 셀 개수를 계산하는 방법 3가지(특정 문자열 포함 셀 개수) 2

3. 만약 조건 텍스트에 대해 대소문자를 구분하여 계산하려면 SEARCH 함수 대신 FIND 함수를 사용하면 됩니다.

  • 수식 : =SUMPRODUCT(–ISNUMBER(FIND(“in”,A2:A6)))
  • 단계별 설명은 SEARCH 함수 사용의 설명을 참고하시면 됩니다.
  • 특정 텍스트 “in”에 대해 대소문자를 구분하여 대문자가 포함된 “In”은 계산에서 제외됩니다.
Excel 엑셀에서 특정 텍스트가 포함된 셀 개수를 계산하는 방법 3가지(특정 문자열 포함 셀 개수) 3

마무리

이상으로 셀 값에서 특정 문자열 텍스트가 포함된 셀의 개수를 구하는 방법에 대해 알아보았습니다.

가장 간단하게 사용할 수 있는 방법은 COUNTIF 함수를 사용하는 방법입니다.
하지만 대소문자를 구분해야 하거나 숫자 안에 있는 숫자를 찾을 경우 SUMPRODUCT 함수와 부울 대수를 사용하는 방법으로 셀 개수를 계산해 주어야 합니다.

엑셀에서 COUNTIF 함수에 대한 보다 자세한 설명이나 수식에서 와일드카드 활용 방법에 대한 설명은 아래 다른 포스팅 글을 참고하시기 바랍니다.





♥ 이 글이 도움이 되었기를 바랍니다. ^-^
댓글로 흔적을 남겨 주세요.

답글 남기기