엑셀에서 여러 값과 같지 않은 셀의 개수 세기

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

• 공식1을 사용하여 여러 값과 같지 않은 셀의 개수 세기
• 공식2를 사용하여 여러 값과 같지 않은 셀의 개수 세기

공식1을 사용하여 여러 값과 같지 않은 셀의 개수 세기

여러 지정된 값과 같지 않은 셀을 세기 위해 MATCH, ISNA 및 SUMPRODUCT 함수의 조합을 사용할 수 있으며 일반적인 문법은 다음과 같습니다:

• 범위: 세고자 하는 셀 목록;
• 제외값: 범위 목록에서 무시할 값들.

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를 사용하여 여러 값과 같지 않은 셀의 개수 세기

엑셀에서 이 작업을 해결하기 위해 COUNTA, SUMPRODUCT 및 COUNTIF 함수의 조합을 적용할 수도 있습니다. 일반적인 문법은 다음과 같습니다:

• 범위: 세고자 하는 셀 목록;
• 제외값: 범위 목록에서 무시할 값들.

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)에서 뺀 값이 필요한 최종 결과입니다.

참고: 위의 두 공식 모두 대소문자를 구분하지 않습니다.

관련 함수:

추가 자료: