엑셀 중복값 제거 (또는 고유값 추출) 함수 공식 알아보기
함수공식
옵션1. INDEX/MATCH 배열수식 (정방향 출력)
옵션2. LOOKUP 간단공식 (역방향 출력)
사용된 인수 설명
| 항목 | 설명 |
| $참조범위 | 중복값을 제거(또는 고유값만 추출)할 범위입니다.
표 또는 동적범위로 지정하면 새로운 데이터를 자동으로 갱신하므로 더욱 효율적입니다. |
| $머릿글:머릿글 | 참조범위에서 중복값이 제거된 범위입니다. 이 범위에서 고유값이 출력됩니다.
확장범위에 대한 자세한 설명은 관련 포스트를 참고하세요. |
공식 설명
엑셀 중복값 제거 공식은 배열수식으로 아래 4개 함수가 사용됩니다.
참조범위에서 중복값을 제거한 뒤 고유값만 출력하는 공식이며 옵션1 (배열수식)은 정순(위->아래) 방향으로, 옵션2(LOOKUP함수)는 역순(아래->위) 방향으로 출력됩니다.
두번째 옵션인 LOOKUP 함수 공식이 더욱 빠르게 동작합니다. 따라서 역순출력이 상관 없다면, LOOKUP 함수 간단공식 사용을 권장합니다.
이 공식을 응용하면 VLOOKUP 여러값 중 고유값만 출력하는 공식을 작성할 수 있습니다. VLOOKUP 여러값 중 고유값만 출력하는 공식에 대한 자세한 설명은 아래 관련포스트를 참고하세요.
만약 엑셀 2016 이후 버전을 사용 중이라면, 파워쿼리를 사용해 쉽고 편리하게 고유값을 추출하고 이후 업무를 자동화할 수 있습니다. 파워쿼리를 사용한 고유값 추출에 대한 자세한 설명은 아래 영상 강의를 참고해주세요.
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀공식] 중복값 제거 자동화 공식 :: 목록에서 고유값만 나열하기예제파일
관련 기초 함수
중복값 제거 함수 공식 자세히 알아보기
본 예제에서는 두번째 옵션인 LOOKUP 함수 간단공식을 기준으로 설명합니다. INDEX/MATCH 함수 공식은 예제파일에 적어드렸습니다. INDEX/MATCH함수에 대한 자세한 설명은 관련 포스트를 참고하세요.
= LOOKUP(2,1/(COUNTIF($D$9:D9,$B$10:$B$17)=0),$B$10:$B$17)
LOOKUP 함수를 응용하면 손쉽게 중복값을 제거할 수 있습니다. 공식 원리 이해하기
- 참조범위 각 항목의 고유값여부 확인
1/(COUNTIF($D$9:D9,$B$10:$B$17)=0)
고유값이 출력될 범위($D$9:D9)는 동적확장범위로 한칸씩 증가합니다. 참조범위($B$10:$B$17)의 값은 아래와 같습니다.
= $B$10:$B$17
= {서울; 서울; 인천; 수원; 인천; 분당; 서울; 인천}D9셀에는 "품목" 이 입력되어 있습니다. 즉 $B$10:$B$17 '지역명' 범위안에 없는 값이므로 COUNTIF함수는 모두 0을 반환합니다.
= 1/(COUNTIF($D$9:D9,$B$10:$B$17)=0)
= 1/({0, 0, 0, 0, 0, 0, 0, 0}=0)
= 1/{1, 1, 1, 1, 1, 1, 1. 1}
= {1, 1, 1, 1, 1, 1, 1, 1}동적확장범위가 한칸 늘어나, $D$9:D10에서 COUNTIF를 적용한다고 가정하겠습니다.
D9:D10 셀에는 "품목, 인천"이 들어가있으므로 인천이 있는 항목은 COUNTIF함수의 결과값으로 1을 반환하게 됩니다. 따라서 최종 결과값은 아래와 같이 출력됩니다.
= 1/(COUNTIF($D$9:D10,$B$10:$B$17)=0)
= 1/({0, 0, 1, 0, 1, 0, 0, 1}=0)
= 1/{1, 1, 0, 1, 0, 1, 1, 0}
= {1, 1, #DIV/0, 1, #DIV/0, 1, 1, #DIV/0} - 참조범위의 아래에서부터 '1'이 출력된 곳의 값을 반환 (#DIV/0 오류는 무시)
= LOOKUP(2,1/(COUNTIF($D$9:D9,$B$10:$B$17)=0),$B$10:$B$17)
첫번째 고유값을 출력한다고 가정하겠습니다.($D$9:D9)
첫번째 고유값을 찾는 상황을 가정합니다. 그럴경우, COUNTIF 함수의 결과값이 LOOKUP 함수의 인수로 아래와 같이 입력됩니다.
= LOOKUP (2, {1, 1, 1, 1, 1, 1, 1, 1}, {서울, 서울, ... 인천} )LOOKUP 함수는 맨 밑에서부터 역순으로 같거나 작은값을 검색합니다. 따라서 2보다 작거나 같은 값을 마지막에서부터 찾아 올라옵니다. 결국 맨 마지막에 위치한 을 찾게 되고, 동일한 순서에 있는 "인천"을 반환합니다.
첫번째 고유값인 "인천"을 반환한 뒤, 두번째 고유값을 출력하는 상황을 가정합니다. ($D$9:D10)
두번째 고유값을 찾는 상황을 가정합니다. 그럴 경우, COUNTIF 함수의 결과값은 아래와 같이 입력됩니다.
= LOOKUP (2, {1, 1, #DIV/0, 1, #DIV/0, 1, 1, #DIV/0}, {서울, 서울, ..., 서울, 인천} )#DIV/0! 오류는 LOOKUP 함수에서 검색하지 않고 넘어갑니다.
따라서 마지막에서 두번째로 위치한 1을 찾게되고, 동일한 자리에 있는 "서울"이 LOOKUP 함수의 결과값으로 반환됩니다.>
- #N/A! 오류 처리고유값의 개수보다 넓은 범위에 공식을 적용할 시, 빈칸에 #N/A! 오류를 반환합니다. 그럴 경우 IFERROR 함수를 사용하여 오류표시를 방지할 수 있습니다.
- 참조범위 각 항목의 고유값여부 확인
