엑셀 여러개 조건 만족하지 않는 개수 구하는 방법 :: 엑셀 함수 공식

여러개 조건을 범위로 입력하여 만족하지 않는 개수를 쉽게 구하는 공식의 사용법과 동작원리를 알아봅니다.

홈페이지 » 엑셀 여러 조건 만족하지 않는 개수 구하기 :: 함수 공식

여러개 조건 만족하지 않는 개수 세기 공식

여러개 조건 만족하지 않는 개수 세기 목차 바로가기
함수 공식
=SUMPRODUCT(--ISNA(MATCH(데이터범위,제외대상,0)))
인수 설명
인수설명
데이터범위일치하지 않는 값을 찾을 데이터 범위입니다.
제외대상제외할 값이 입력된 범위 또는 배열 {값1, 값2, 값3...} 형태로도 입력 가능합니다.

예제파일 다운로드

호환성
운영체제호환성
Windows 버전모든 엑셀 버전에서 사용 가능합니다.
Mac 버전모든 엑셀 버전에서 사용 가능합니다.
사용된 기초 함수
선택된 범위에서 여러개의 조건을 만족하는 셀의 개수를 구합니다.
범위 내 찾는값의 위치(순번)을 반환합니다.

여러개 조건 만족하지 않는 개수 세기 공식 알아보기

공식 설명

일반적으로 여러개의 조건이 일치하거나 또는 일치하지 않을 경우의 개수를 셀 때에는 COUNTIFS 함수를 사용합니다.

= COUNITFS ( 범위, "<>조건1", 범위, "<>조건2", ... )

하지만 조건이 여러개일 경우에는 어떻게 할까요? 조건이 2~3개라면 간단하겠지만, 조건으로 10개, 20개가 넘어갈 경우 그 많은 조건을 한 수식에 모두 입력하기에는 어려울 것입니다. 따라서 위 공식을 사용하면 여러개의 조건을 하나의 범위로 참조해서 수식을 보다 간단하게 작성할 수 있습니다.

공식의 동작원리
' 데이터범위 : {"사과", "귤", "귤", "참외", "포도", "사과", "배추", "상추", "배추", "피망"}
' 제외범위 : {"귤", "포도", "배추"}
  1. MATCH 함수는 각 데이터범위의 값을 제외대상에서 찾아 제외대상범위에서 몇번째에 위치하는지 순번을 반환합니다.
    =MATCH(데이터범위, 제외대상, 0)
    =MATCH({"사과", "귤", "귤", "참외", "포도", "사과", "배추", "상추", "배추", "피망"}, {"귤", "포도", "배추"}, 0)
    ={#N/A, 1, 1, #N/A, 2, #N/A, 3, #N/A, 3, #N/A}
  2. ISNA 함수는 #N/A 오류일 경우 TRUE를 반환합니다.
    =ISNA(MATCH(데이터범위,제외대상,0))
    =ISNA({#N/A, 1, 1, #N/A, 2, #N/A, 3, #N/A, 3, #N/A})
    ={TRUE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE}
  3. -- 기호는 논리값을 숫자로 강제 변환합니다.
    =--{TRUE, FALSE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE, FALSE, TRUE}
    ={1, 0, 0, 1, 0, 1, 0, 1, 0, 1}
  4. SUMPRODUCT 함수는 배열안 숫자의 합계를 계산합니다.
    =SUMPRODUCT({1, 0, 0, 1, 0, 1, 0, 1, 0, 1})
    =1+0+0+1+0+1+0+1+0+1d
    =5  '제외범위 안 값을 제외한 데이터 개수는 5개입니다.

자주묻는질문

자주묻는질문1. 여러개의 다른 조건을 만족하는 개수는 어떻게 구하나요?

여러개 다른 조건을 만족하는 개수를 구하는 방법은 아주 간단합니다. 위 적어드린 공식에 NOT 함수 하나만 더해주면 되는데요. 공식은 아래와 같습니다.

=SUMPRODUCT(--NOT(ISNA(MATCH(데이터범위,제외대상,0))))
현재 페이지 댓글알림 신청
알림 설정
guest
0 Comments
Inline Feedbacks
View all comments
0
여러분의 생각을 댓글로 남겨주세요.x