여러 값을 포함하지 않는 셀의 개수 세기

일반적으로 COUNTIF 함수를 사용하면 특정 값 하나를 포함하지 않는 셀의 개수를 쉽게 셀 수 있습니다. 이 게시물은 Excel 에서 제한된 범위 내 여러 값을 포함하지 않는 셀의 개수를 세는 방법을 단계별로 설명합니다。

여러 값을 포함하지 않는 셀을 어떻게 세나요？

다음 스크린샷과 같이 D3:D4 에 나열된 값을 포함하지 않는 B3:B11 범위의 셀 개수를 세려면 다음 단계를 따르세요。

일반 공식

{=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(criteria_range),range))),ROW(criteria_range)^0)>0))}

인수

참고：이 공식은 배열 공식으로 입력되어야 합니다。 공식을 적용한 후 중괄호({})가 공식을 감싸고 있다면 배열 공식이 성공적으로 생성된 것입니다。

이 공식을 어떻게 사용하나요？

1. 결과를 출력할 빈 셀을 선택하세요。

2. 아래 공식을 입력한 후Ctrl+Shift+Enter키를 동시에 눌러 결과를 얻으세요。

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

이 공식들은 어떻게 작동하나요？

=SUM(1-(MMULT(--(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))),ROW(D3:D4)^0)>0))

1) --(ISNUMBER(SEARCH(TRANSPOSE(D3:D4),B3:B11))):

• TRANSPOSE(D3:D4):TRANSPOSE 함수는 D3:D4 의 방향을 회전시켜{“count”,”blank”}를 반환합니다。
• SEARCH({“count”,”blank”},B3:B11):여기에서 SEARCH 함수는 B3:B11 범위에서 부분 문자열 “count”와 “blank”의 위치를 찾아 다음과 같은 배열을 반환합니다。{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,
  #VALUE!;1,7}.
• 이 경우, 셀 개수를 셀 때 제외하고자 하는 값이 두 개 있으므로 B3:B11 의 각 셀이 두 번 검색되어 배열에 18 개의 값이 생성됩니다. 배열의 각 숫자는 B3:B11 의 각 셀에서 “count” 또는 “blank”의 첫 번째 문자 위치를 나타냅니다。
• ISNUMBER{#VALUE!,#VALUE!;#VALUE!,#VALUE!;1,#VALUE!;#VALUE!,8;1,#VALUE!;#VALUE!,#VALUE!;#VALUE!,
  #VALUE!;1,#VALUE!;1,7}:ISNUMBER 함수는 배열에서 숫자를 만나면 TRUE 를, 오류를 만나면 FALSE 를 반환합니다。 여기서 반환되는 결과는 다음과 같습니다。{FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;
  TRUE,TRUE}.
• --({FALSE,FALSE;FALSE,FALSE;TRUE,FALSE;FALSE,TRUE;TRUE,FALSE;FALSE,FALSE;FALSE,FALSE;TRUE,)
  FALSE;TRUE,TRUE}):두 개의 마이너스 부호는 “TRUE”를 1 으로, “FALSE”를 0 로 변환합니다。 이때 다음과 같은 새 배열을 얻게 됩니다。{0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1}.

2)ROW(D3:D4)^0:ROW 함수는 셀 참조의 행 수 설정을 반환합니다: {3;4}. 그런 다음 거듭제곱 연산자(^)는 숫자 3 과 4 을 0 제곱하여 다음과 같은 결과를 반환합니다。{1;1}.

3) MMULT({0,0;0,0;1,0;0,1;1,0;0,0;0,0;1,0;1,1},{1;1}):MMULT 함수는 다음 두 배열의 행렬 곱을 반환합니다: {0;0;1;1;1;0;0;1;2}. 이는 원본 데이터와 일치합니다. 배열의 0 이 아닌 숫자는 제외된 문자열 중 하나 이상이 발견되었음을 나타내며, 0 은 제외된 문자열이 전혀 발견되지 않았음을 의미합니다。

4) SUM(1-{0;0;1;1;1;0;0;1;2}>0):

• {0;0;1;1;1;0;0;1;2}>0:여기서 배열의 각 숫자가 0 보다 큰지 확인합니다. 숫자가 0 보다 크면 TRUE 를, 그렇지 않으면 FALSE 를 반환합니다。 그러면 다음과 같은 새 배열을 얻게 됩니다。{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}.
• 1-{FALSE;FALSE;TRUE;TRUE;TRUE;FALSE,FALSE,TRUE;TRUE}:지정된 값을 포함하지 않는 셀만 세야 하므로 배열의 값을 1 에서 빼서 반전시켜야 합니다. 이 수학 연산자는 TRUE 와 FALSE 값을 자동으로 1 과 0 으로 변환하며， 최종적으로 다음과 같은 결과를 반환합니다。{1;1;0;0;0;1;1;0;0}.
• SUM{1;1;0;0;0;1;1;0;0}:SUM 함수는 배열의 모든 숫자를 합산하여 최종 결과 4 를 반환합니다。

관련 함수

관련 공식