Excel에서 범위 내 특정 단어 세기

이 문서에서는 Excel의 셀 범위에서 특정 단어가 나타나는 횟수를 계산하는 수식을 설명합니다.

Excel에서 셀 범위 내 특정 단어를 어떻게 세나요?

일반적인 공식

=SUMPRODUCT((LEN(rng)-LEN(SUBSTITUTE(rng,txt,"")))/LEN(txt))

인수

이 수식은 어떻게 사용하나요?

범위 B5:B8에서 “tool”이라는 단어를 세고 싶다고 가정하면, 아래와 같이 진행할 수 있습니다.

빈 셀을 선택하고, 아래 수식을 입력한 후 Enter 키를 누릅니다.

팁: 수식을 직접 복사하여 선택한 셀에 붙여넣을 수 있습니다.

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"))

참고:

• 1. 범위 B5:B8과 단어 “tool”을 필요에 따라 변경하세요;
• 2. 단어를 찾지 못하면 결과는 0이 됩니다;
• 3. 이 수식은 대소문자를 구분합니다;
• 4. 범위 내 특정 단어를 세면서 대소문자를 무시하려면, 아래 수식처럼 SUBSTITUTE 안에 UPPER 함수를 사용하세요:
• =SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(UPPER(B5:B8),UPPER("tool"),"")))/LEN("tool"))
• 

이 수식은 어떻게 작동하나요?

=SUMPRODUCT((LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"))

• 1. LEN(B5:B8): LEN 함수는 범위 B5:B8의 각 셀에 대해 전체 문자열 길이를 계산하며, 배열 숫자 {47;23;23;15}로 반환됩니다;
• 2. LEN(SUBSTITUTE(B5:B8,"tool","")): 여기서 SUBSTITUTE 함수는 범위 B5:B8의 각 셀에서 특정 단어 “tool”을 제거하고, 그 다음 LEN 함수는 특정 단어 “tool”을 제외한 각 셀의 길이를 계산합니다. 결과는 배열 숫자 {35;19;19;15}로 반환됩니다;
• 3. (LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")): 이제 이 수식 부분은 {47;23;23;15}-{35;19;19;15}로 변환됩니다. 뺄셈 후 결과는 {12;4;4;0}입니다;
• 4. LEN("tool"): 여기서 LEN 함수는 “tool”의 길이를 계산하며, 결과는 4입니다;
• 5. LEN(B5:B8)-LEN(SUBSTITUTE(B5:B8,"tool","")))/LEN("tool"): 위에서 언급했듯이, 이 부분은 {12;4;4;0}/4로 표시됩니다. 배열의 각 숫자를 4로 나누면 결과는 {3;1;1;0}입니다;
• 6. SUMPRODUCT({3;1;1;0}): SUMPRODUCT 함수는 배열의 숫자를 합산하며, 범위 B5:B8에서 “tool”의 총 개수를 반환합니다.

관련 함수

관련 수식