Excel 에서 여러 값과 일치하지 않는 셀의 개수 계산하기

Excel 에서는 COUNTIF 함수를 사용하여 특정 값과 일치하지 않는 셀의 개수를 쉽게 구할 수 있지만, 여러 값과 일치하지 않는 셀의 개수를 세어본 적이 있습니까? 예를 들어, 아래 스크린샷과 같이 A 열의 제품 총 개수를 구하되 C4:C6 에 있는 특정 항목은 제외하고자 합니다. 본 문서에서는 Excel 에서 이 작업을 해결하기 위한 몇 가지 수식을 소개합니다。

• 수식1 을 사용하여 여러 값과 일치하지 않는 셀의 개수 계산하기
• 수식2 를 사용하여 여러 값과 일치하지 않는 셀의 개수 계산하기

수식1 을 사용하여 여러 값과 일치하지 않는 셀의 개수 계산하기

여러 지정된 값과 일치하지 않는 셀을 세려면 MATCH， ISNA 및 SUMPRODUCT 함수를 조합할 수 있습니다。 일반적인 구문은 다음과 같습니다：

• range： 개수를 세려는 셀 목록입니다。
• exclude_values： 범위 목록에서 무시할 값입니다。

1。 결과를 출력할 빈 셀에 아래 수식을 입력하거나 복사하세요：

2。 그런 다음Enter키를 눌러 올바른 결과를 얻으세요。 스크린샷 참조：

수식 설명：

• MATCH(A2:A13,C2:C4,0): 이 MATCH 함수는 각각의 일치하는 값의 위치를 숫자로 반환하고, 찾을 수 없는 경우에는 #N/A 오류 값을 반환합니다. 즉, A2:A13 의 값이 C2:C4 의 값과 같으면 숫자를 반환하고， 그렇지 않으면 #N/A 오류를 반환합니다。 여기서 결과는 다음과 같습니다： {1；#N/A；#N/A;1;3;2；#N/A； #N/A;3；#N/A； #N/A； #N/A；}。
• ISNA(MATCH(A2:A13,C2:C4,0))= ISNA({1;#N/A;#N/A;1;3;2;#N/A; #N/A;3;#N/A; #N/A; #N/A;}): 이 ISNA 함수는 오류 값을 TRUE 로, 숫자를 FALSE 로 변환하므로 다음과 같은 결과를 얻게 됩니다： {FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}。
• --(ISNA(MATCH(A2:A13,C2:C4,0))))=--({FALSE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE;TRUE;TRUE}): 이중 부정 연산자(-- )는 모든 TRUE 를 1 로, FALSE 를 0 으로 변환합니다。 따라서 다음과 같이 표현할 수 있습니다： SUMPRODUCT(--(ISNA(MATCH(A2:A13,C2:C4,0))))=SUMPRODUCT({0;1;1;0;0;0;1;1;0;1;1;1；})。
• 그런 다음 SUMPRODUCT 함수를 사용하여 배열 내 숫자의 합계를 구하면 다음과 같은 결과를 얻습니다： 7。

수식2 를 사용하여 여러 값과 일치하지 않는 셀의 개수 계산하기

Excel 에서 이 작업을 해결하려면 COUNTA， SUMPRODUCT 및 COUNTIF 함수를 조합할 수도 있습니다。 일반적인 구문은 다음과 같습니다：

• range： 개수를 세려는 셀 목록입니다。
• exclude_values： 범위 목록에서 무시할 값입니다。

1。 결과를 표시할 빈 셀에 아래 수식을 입력하거나 복사하세요：

2。 그런 다음Enter키를 눌러 아래 스크린샷과 같은 결과를 얻으세요：

수식 설명：

• COUNTA(A2:A13)： 이 COUNTA 함수는 A2:A13 범위 내의 모든 비빈 셀 셀 수를 반환합니다。 결과는 다음과 같습니다： 12。
• COUNTIF(A2:A13,C2:C4): 이 COUNTIF 함수는 A2:A13 범위에서 C2:C4 의 기준과 일치하는 셀을 세므로 다음과 같은 결과를 얻게 됩니다: {2;1;2}. 이는 Apple 이 두 번, Grape 가 한 번, Lemon 이 두 번 나타남을 의미합니다。
• SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=SUMPRODUCT({2;1;2})： 이 SUMPRODUCT 함수는 배열 내 모든 항목의 합계를 구하여 최종 결과를 반환합니다： 5。
• COUNTA(A2:A13)-SUMPRODUCT(COUNTIF(A2:A13,C2:C4))=12-5： SUMPRODUCT 함수의 결과(5)를 전체 비빈 셀 셀 수(12)에서 빼면 필요한 최종 결과를 얻을 수 있습니다。

참고： 위의 두 수식 모두 대소문자 구분 아닙니다。

관련 함수：

관련 문서 더 보기：