Excel : 특정 텍스트를 포함하는 셀 수 (정확한 부분 및 부분 일치)

자습서는 Excel에 특정 텍스트를 가진 셀 수를 세는 방법을 보여줍니다. 정확한 일치, 부분 일치 및 필터링 된 셀에 대한 공식 예제를 찾을 수 있습니다.

지난주에 우리는 텍스트가 탁월한 세포를 셀을 세는 방법을 살펴 보았습니다. 많은 정보 덩어리를 분석 할 때 특정 텍스트가 포함 된 셀의 수를 알고 싶을 수도 있습니다. 이 튜토리얼은 간단한 방식으로 수행하는 방법을 설명합니다.

• 특정 텍스트를 가진 셀 수를 세십시오
• 특정 텍스트를 포함하는 셀 수 (부분 일치)
• 주어진 텍스트로 세포를 계산하기위한 사례에 민감한 공식
• 특정 텍스트를 포함하는 필터링 된 셀 수

Excel에 특정 텍스트가있는 셀을 계산하는 방법

Microsoft Excel은 Countif 함수 인 셀을 조건부로 계산하는 특수 기능을 가지고 있습니다. 기준 인수에 대상 텍스트 문자열을 제공하는 것만 큼해야합니다.

다음은 특정 텍스트를 포함하는 셀 수를 계산하는 일반적인 Excel 공식입니다.

countif (범위, " 텍스트 ")

다음 예제는이를 실제로 보여줍니다. 가정, A2 : A10에는 항목 ID 목록이 있으며 특정 ID 인 "AA-01"으로 셀 수를 계산하려고합니다. 두 번째 인수 에이 문자열을 입력하면이 간단한 공식을 얻을 수 있습니다.

=COUNTIF(A2:A10, "AA-01")

사용자가 공식을 수정할 필요없이 주어진 텍스트로 셀을 계산하고, 사전 정의 된 셀에 텍스트를 입력하고, D1에 입력하고 셀 참조를 제공 할 수 있습니다.

=COUNTIF(A2:A10, D1)

메모. Excel Countif 함수는 사례에 민감하지 않으므로 문자 케이스를 구별하지 않습니다. 대문자와 소문자를 다르게 처리하려면이 사례에 민감한 공식을 사용하십시오.

특정 텍스트로 셀을 계산하는 방법 (부분 일치)

이전 예제에서 논의 된 공식은 기준과 정확히 일치합니다. 셀에 적어도 하나의 다른 문자가있는 경우, 예를 들어, 결국 추가 공간이있는 경우, 정확히 일치하지 않으며 그러한 셀은 계산되지 않습니다.

내용의 일부로 특정 텍스트를 포함하는 셀의 수를 찾으려면 기준에 와일드 카드 문자, 즉 시퀀스 또는 문자를 나타내는 별표 (*)를 사용하십시오. 목표에 따라 공식은 다음 중 하나처럼 보일 수 있습니다.

처음 에 특정 텍스트를 포함하는 셀을 계산하십시오.

countif (범위, " 텍스트 *")

어떤 위치 에서 특정 텍스트를 포함하는 셀을 계산하십시오.

countif (범위, " * text *")

예를 들어, A2 : A10 범위의 셀 수를 "AA"로 시작하려면이 공식을 사용하십시오.

=COUNTIF(A2:A10, "AA*")

어떤 위치에서 "AA"를 포함하는 세포의 수를 얻으려면 다음을 사용하십시오.

=COUNTIF(A2:A10, "*AA*")

공식을보다 역동적으로 만들려면 하드 코딩 된 문자열을 셀 참조로 바꾸십시오.

특정 텍스트로 시작하는 셀을 계산합니다.

=COUNTIF(A2:A10, D1&"*")

어디서나 특정 텍스트가있는 셀을 계산합니다.

=COUNTIF(A2:A10, "*"&D1&"*")

아래 스크린 샷은 결과를 보여줍니다.

특정 텍스트를 포함하는 셀 수 (사례에 민감성)

대문자와 소문자를 구별 해야하는 상황에서는 Countif 기능이 작동하지 않습니다. 정확한 또는 부분 일치를 찾고 있는지 여부에 따라 다른 공식을 구축해야합니다.

특정 텍스트를 가진 셀을 계산하기위한 사례에 민감한 공식 (정확히 일치)

텍스트 케이스를 인식하는 특정 텍스트가있는 셀 수를 계산하려면 Sumproduct와 정확한 기능의 조합을 사용합니다.

SumProduct (-정확한 ( " 텍스트 ", 범위 ))

이 공식의 작동 방식 :

• 정확히 샘플 텍스트와 범위의 각 셀을 비교하고 정확한 일치 및 다른 모든 셀을 나타내는 True 및 False 값 배열을 반환합니다. 이중 하이픈 ( 이중 단지 라고 함)은 진실과 거짓을 1과 0으로 강요합니다.
• SumProduct는 배열의 모든 요소를 ​​합산합니다. 이 합은 1의 수입니다.

예를 들어, D1의 텍스트를 포함하고 대문자와 소문자를 다른 문자로 처리하는 A2 : A10의 셀 수를 얻으려면이 공식을 사용하십시오.

=SUMPRODUCT(--EXACT(D1, A2:A10))

특정 텍스트를 가진 셀을 계산하기위한 사례에 민감한 공식 (부분 일치)

셀의 어느 곳에서나 관심있는 텍스트 문자열을 찾을 수있는 대소 문자열 공식을 구축하려면 3 가지 기능을 사용하고 있습니다.

sumProduct (-(isnumber (find ( " text ", range ))))))

이 공식의 작동 방식 :

• 사례에 민감한 찾기 기능은 범위의 각 셀에서 대상 텍스트를 검색합니다. 성공하면 함수는 첫 번째 문자의 위치를 ​​반환합니다. 그렇지 않으면 #Value! 오류. 명확성을 위해, 우리는 정확한 위치를 알 필요가 없으며, (오류와 반대되는 숫자)는 셀에 대상 텍스트가 포함되어 있음을 의미합니다.
• IsNumber 함수는 찾기에 의해 반환 된 숫자 및 오류 배열을 처리하고 숫자를 true로, 다른 것을 false로 변환합니다. 이중 단지 (-)는 논리적 값을 하나와 0으로 강요합니다.
• SumProduct는 1과 0의 배열을 합계하고 내용의 일부로 지정된 텍스트를 포함하는 셀 수를 반환합니다.

실제 데이터에 대한 공식을 테스트하려면 A2 : A10의 셀이 D1에 기판 입력을 포함하는 수를 찾으십시오.

=SUMPRODUCT(--(ISNUMBER(FIND(D1, A2:A10))))

그리고 이것은 3의 카운트를 반환합니다 (셀 A2, A3 및 A6) :

특정 텍스트로 필터링 된 셀을 계산하는 방법

필터링 된 목록에 가시 항목을 계산하려면 정확한 또는 부분 일치를 원하는지 여부에 따라 4 개 이상의 기능을 조합해야합니다. 예제를보다 쉽게 ​​따르기 위해 소스 데이터를 먼저 살펴 보겠습니다.

가정 B 열에 주문 ID가 있는 테이블이 있고 아래 이미지에 표시된 C 열의 수량이 있다고 가정합니다. 현재, 당신은 1보다 큰 수량에만 관심이 있고 그에 따라 테이블을 필터링했습니다. 문제는 - 특정 ID로 필터링 된 셀을 어떻게 계산합니까?

특정 텍스트가있는 필터링 된 셀을 계산하는 공식 (정확히 일치)

내용이 샘플 텍스트 문자열과 정확히 일치하는 필터링 된 셀을 계산하려면 다음 공식 중 하나를 사용하십시오.

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(B2:B10=F1))

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), --(B2:B10=F1))

여기서 F1은 샘플 텍스트이고 B2 : B10은 계산할 셀입니다.

이러한 공식의 작동 방식 :

두 공식의 핵심에서는 2 개의 점검을 수행합니다.

1. 가시적이고 숨겨진 행을 식별하십시오. 이를 위해, 당신은 function_num 인수가 103으로 설정된 하위 운동 함수를 사용합니다. 모든 개별 셀 참조를 하위 운동에 제공하고, 간접 (첫 번째 공식) 또는 오프셋, 행 및 최소 (두 번째 공식)의 조합을 사용합니다. 우리는 가시적이고 숨겨진 행을 찾는 것을 목표로하기 때문에 참조 할 열 (A 예에서 A)은 중요하지 않습니다. 이 작업의 결과는 1과 0의 배열로, 하나는 가시 행과 0- 숨겨진 행을 나타냅니다.
2. 주어진 텍스트를 포함하는 셀을 찾습니다. 이를 위해 샘플 텍스트 (F1)를 셀 범위 (B2 : B10)와 비교하십시오. 이 작업의 결과는 이중 단지 연산자의 도움으로 1과 0으로 강요됩니다.

마지막으로, SumProduct 함수는 동일한 위치에 두 배열의 요소를 곱한 다음 결과 배열을 합산합니다. 곱하기는 0을 곱하기 때문에 두 배열에 1이 1을 갖는 셀 만 최종 배열에서 1을 갖습니다. 1의 합은 지정된 텍스트를 포함하는 필터링 된 셀의 수입니다.

특정 텍스트가있는 필터링 된 셀을 계산하는 공식 (부분 일치)

세포 내용물의 일부로 특정 텍스트를 함유 한 필터링 된 셀을 계산하려면 다음과 같은 방식으로 위의 공식을 수정하십시오. 샘플 텍스트를 셀 범위와 비교하는 대신 iSNumber를 사용하여 대상 텍스트를 검색하고 이전 예 중 하나에 설명 된대로 찾으십시오.

=SUMPRODUCT(SUBTOTAL(103, INDIRECT("A"&ROW(A2:A10))), --(ISNUMBER(FIND(F1, B2:B10))))

=SUMPRODUCT(SUBTOTAL(103, OFFSET(A2:A10, ROW(A2:A10) - MIN(ROW(A2:A10)),,1)), --(ISNUMBER(FIND(F1, B2:B10))))

결과적으로, 공식은 셀의 어느 위치에서나 주어진 텍스트 문자열을 찾습니다.

메모. function_num 인수에서 103을 갖는 소계 함수는 모든 숨겨진 셀을 식별하고, 필터링되고 수동으로 숨겨져 있습니다. 결과적으로, 상기 공식은 보이지 않는 세포가 숨겨져있는 방법에 관계없이 가시 세포 만 수를 계산합니다. 필터링 된 셀만 제외하지만 수동으로 숨겨진 셀을 포함하려면 function_num 에 3을 사용하십시오.

이것이 엑셀에 특정 텍스트가있는 셀 수를 계산하는 방법입니다. 읽어 주셔서 감사합니다. 다음 주에 우리 블로그에서 뵙기를 바랍니다!

사용 가능한 다운로드

Excel 공식은 특정 텍스트로 세포를 계산합니다

위 내용은 Excel : 특정 텍스트를 포함하는 셀 수 (정확한 부분 및 부분 일치)의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!