전체 Excel 통합 문서에서 특정 텍스트의 발생 횟수 세기

COUNTIF 함수를 사용하면 셀 범위에서 특정 텍스트나 숫자 값이 몇 번 나타나는지 쉽게 계산할 수 있습니다. 그러나 전체 통합 문서에서 특정 텍스트의 발생 횟수를 세려면 더 복잡한 공식이 필요합니다. 이 기사는 SUMPRODUCT, COUNTIF 및 INDIRECT 함수를 기반으로 하는 공식을 통해 이 문제를 해결하는 방법을 설명합니다.

전체 통합 문서에서 발생 횟수를 어떻게 계산합니까?

통합 문서에 4개의 워크시트(Sheet2, Sheet3, Sheet4 및 Sheet5)가 있다고 가정하고, 특정 텍스트 “Laptop”이 이러한 4개의 워크시트에서 몇 번 나타나는지 세고 싶다면 다음과 같이 하여 결과를 얻을 수 있습니다.

일반 공식

=SUMPRODUCT(COUNTIF(INDIRECT(“‘”&range1&”‘!”&range2),criteria))

인수

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

1. 먼저 새 시트를 만들고 셀 범위에 모든 시트 이름을 나열해야 합니다. 이 경우 B7:B10 범위에 모든 시트 이름을 나열했습니다.

2. 결과를 출력할 빈 셀을 선택합니다.

3. 아래 공식 중 하나를 입력하고 Enter 키를 눌러 결과를 얻습니다.

특정 텍스트를 이미 셀에 입력했다면 (예: B3), 다음처럼 공식에서 해당 셀을 참조할 수 있습니다.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3))

또는 공식에 직접 특정 텍스트를 큰따옴표로 묶어 입력할 수도 있습니다.

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),"Laptop"))

참고: 여기서 A1:Z10000 범위는 임의의 범위이며, 데이터에 맞게 변경할 수 있습니다.

이 공식은 어떻게 작동합니까?

=SUMPRODUCT(COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3))

• "'"&B7:B10&"'!A1:Z10000": 배열의 각 시트 이름이 연결(concatenation)을 통해 범위 A1:Z10000과 결합되고, {"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000";"'Sheet4'!A1:Z10000";"'Sheet5'!A1:Z10000"}와 같은 배열을 얻게 됩니다.
• INDIRECT({"'Sheet2'!A1:Z10000";"'Sheet3'!A1:Z10000";"'Sheet4'!A1:Z10000";"'Sheet5'!A1:Z10000"}): INDIRECT 함수는 네 개의 시트에 있는 범위를 참조합니다.
• COUNTIF(INDIRECT("'"&B7:B10&"'!A1:Z10000"),B3): COUNTIF 함수는 각 시트에서 특정 값이 나타나는 횟수를 세고 {13;13;13;13}과 같은 배열을 반환합니다. 즉, 특정 텍스트가 각 시트에서 13번 나타난다는 것을 의미합니다.
• SUMPRODUCT({13;13;13;13}): SUMPRODUCT 함수는 배열의 모든 숫자를 합산하고 최종 결과로 52를 반환합니다.

관련 함수

관련 공식