엑셀 중복값 제거 및 고유값 추출 하기 :: 엑셀 함수 공식

선택된 범위에서 중복값 제거 후 고유값만 출력하는 엑셀 함수 공식의 동작원리 및 실전 예제를 알아봅니다.

홈페이지 » 엑셀 중복값 제거 및 고유값 추출 하기

엑셀 중복값 제거 (또는 고유값 추출) 함수 공식 알아보기 

함수공식

옵션1. INDEX/MATCH 배열수식 (정방향 출력)

{ = INDEX($참조범위, MATCH (0, COUNTIF($머릿글:머릿글, $참조범위), 0)) }

옵션2. LOOKUP 간단공식 (역방향 출력)

= LOOKUP(2, 1/(COUNTIF($머릿글:머릿글, $참조범위)=0), $참조범위)
사용된 인수 설명
항목 설명
$참조범위 중복값을 제거(또는 고유값만 추출)할 범위입니다.

표 또는 동적범위로 지정하면 새로운 데이터를 자동으로 갱신하므로 더욱 효율적입니다.

$머릿글:머릿글 참조범위에서 중복값이 제거된 범위입니다. 이 범위에서 고유값이 출력됩니다.

확장범위에 대한 자세한 설명은 관련 포스트를 참고하세요.

공식 설명

엑셀 중복값 제거 공식은 배열수식으로 아래 4개 함수가 사용됩니다.

참조범위에서 중복값을 제거한 뒤 고유값만 출력하는 공식이며 옵션1 (배열수식)은 정순(위->아래) 방향으로, 옵션2(LOOKUP함수)는 역순(아래->위) 방향으로 출력됩니다.

두번째 옵션인 LOOKUP 함수 공식이 더욱 빠르게 동작합니다. 따라서 역순출력이 상관 없다면, LOOKUP 함수 간단공식 사용을 권장합니다.

이 공식을 응용하면 VLOOKUP 여러값 중 고유값만 출력하는 공식을 작성할 수 있습니다. VLOOKUP 여러값 중 고유값만 출력하는 공식에 대한 자세한 설명은 아래 관련포스트를 참고하세요.

예제파일 다운로드
관련 기초 함수
선택된 범위에서 하나의 조건을 만족하는 셀의 개수를 계산합니다.
선택한 범위에서 원하는 위치의 값을 반환합니다.

중복값 제거 함수 공식 자세히 알아보기

본 예제에서는 두번째 옵션인 LOOKUP 함수 간단공식을 기준으로 설명합니다. INDEX/MATCH 함수 공식은 예제파일에 적어드렸습니다. INDEX/MATCH함수에 대한 자세한 설명은 관련 포스트를 참고하세요.

= LOOKUP(2,1/(COUNTIF($D$9:D9,$B$10:$B$17)=0),$B$10:$B$17)

엑셀 중복값 제거 목록 나열
LOOKUP 함수를 응용하면 손쉽게 중복값을 제거할 수 있습니다.
공식 원리 이해하기
  1. 참조범위 각 항목의 고유값여부 확인
    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}
  2. 참조범위의 아래에서부터 '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 함수의 결과값으로 반환됩니다.>

  3. #N/A! 오류 처리고유값의 개수보다 넓은 범위에 공식을 적용할 시, 빈칸에 #N/A! 오류를 반환합니다. 그럴 경우 IFERROR 함수를 사용하여 오류표시를 방지할 수 있습니다.

INDEX / MATCH 함수가 사용된 다른 공식

지정한 범위에서 찾는값과 일치하는 그림을 출력합니다.
범위에서 값을 찾아 같은 행의 다른 값을 반환합니다.
INDEX / MATCH 함수의 다양한 실전 응용방법을 알아봅니다.
여러 조건을 만족하는 VLOOKUP 검색 공식입니다.
2중 조건을 만족하는 데이터유효성 목록상자를 생성합니다.
VLOOKUP 함수 결과값으로 여러개의 값을 세로/가로 방향으로 출력합니다.
Loading...
4.9 14 투표
게시글평점
guest
28 댓글
Inline Feedbacks
모든 댓글 보기
Ido
Ido
2020년 2월 25일 2:10 오후
게시글평점 :
     

D9:D10 셀에는 "품목, 인천"이 들어가있으므로 인천이 있는 항목은 COUNTIF함수의 결과값으로 1을 반환하게 됩니다. 따라서 최종 결과값은 아래와 같이 출력됩니다. 를 유추해보면 찾는 값을 품목 또는 인천으로 계산해서 하나만 같다면 1을… 더보기 »

Ido
Ido
2020년 2월 25일 2:41 오후
답글 남기기  Ido

countif(범위, 찾을값)이던데 여기선 countif(머릿글:머릿글, 참조범위)라서 범위와 찾을값을 뒤바뀐 느낌도 나고 해서 모르겠습니다.

이상원
이상원
2021년 7월 8일 4:11 오후
답글 남기기  Ido

처음엔 저도 그런 느낌을 받았습니다만, 근본적으로 원리가 바뀐 건 없고 우리의 편견이 작용한 결과입니다. 상식적인 생각으론 님이 생각하시는 것처럼 '범위'가 항상 '찾을값' 보다 커야 할 거 같지만 '찾을값'이 배열로 설정되면… 더보기 »

이상원
이상원
2021년 7월 8일 4:14 오후
답글 남기기  Ido

아... 그리고 이 곳에서 다루고 있는 구조의 핵심은 "한번도 나타나지 않은 값들의 배열을 만들어 그 중 가장 아래에 위치한 값을 표기하는 방식"입니다.

꼼짝마라
꼼짝마라
2020년 3월 27일 11:08 오후
게시글평점 :
     

언제가 필요한 강의 감사드립니다.

장공
장공
2020년 3월 30일 10:40 오전
게시글평점 :
     

벽에 막히는게 없이 설명을 잘해주세요~

토토가
토토가
2020년 4월 11일 8:13 오전
게시글평점 :
     

익숙해질때까지 반복할께요 감사합니다^^

김종호
김종호
2020년 4월 23일 3:45 오후
게시글평점 :
     

세번째 값을 검색할때에는 { #DIV/0, #DIV/0, 1 ,1 ,1 ,1 ,#DIV/0, 1} 인데 그럼 뒤에서 1을 검색하면 인천이 나와야되는데 어떠케 분당이 나오나요?

hm****
hm****
2020년 6월 19일 5:12 오후
게시글평점 :
     

안녕하세요 강의 항상 잘 듣고 보고 있습니다 첨부화일에서 중간 데이타 값중 일부를 삭제하면 결과값 중간에 "0" 이나오는데 제가 중복값 골라서 데이터 추출하는 작업을 많이하다 보니 약간의 트릭을 통해 해당값만 나오게… 더보기 »

20200619_151207.png
굴레악
굴레악
2020년 9월 21일 11:14 오후
게시글평점 :
     

{ = INDEX($참조범위, MATCH (0, COUNTIF($머릿글:머릿글, $참조범위), 0)) } 정방향 출력이 필요해서 위 방법으로 했는데 뒤죽박죽으로 나오네요. 중복값 걸러서 잘 나오기도 하고 어떤건 단독항목인데 중복으로 나오기도 하고 랜덤입니다. 왜 그럴까요 ㅡㅡ; {=INDEX($C$5:$C$107,MATCH(0,COUNTIF($C$4:C4,$C$5:$C$107),0))} 분명 배열수식으로 했는데 말이죠.… 더보기 »

Last edited 11 월 전 by 굴레악
굴레악
굴레악
2020년 9월 22일 12:40 오전
답글 남기기  굴레악

파일 받아서 따라 하니 또 되는건 뭘까요 ㅡㅡ;

머리글은 입력될곳의 위치네요.
본문에서는 댓글 수정이 안 되나 봐요,,,

wsh6995
wsh6995
2020년 10월 26일 1:46 오후
게시글평점 :
     

공백은 고유값에서 제외하려면 어떻게 해야할까요 ?
(lookup 사용한 중복제거)

Last edited 10 월 전 by wsh6995
딱대
딱대
2020년 11월 23일 11:52 오후
게시글평점 :
     

하 겨우 이해했다 countif(품목,인천)범위에서 각각 서울,서울,.인천,수원...을 찿아서 (갯수를) 배열로 놓는다{0,0,1,0,....) countif 부터 잘못이해하고 있어서 헤맸네요. 쉬운걸 괜히 하

이형철
이형철
2021년 2월 2일 11:00 오전
게시글평점 :
     

중복값 데이터 불러오기 함수 중(INDEX/MATCH) 범위가(B10:B17) 유동적일때어떻게 해야하나요?

dlft****
dlft****
2021년 4월 19일 11:24 오후
게시글평점 :
     

안녕하세요. 올려주신 중복값에서 고유값 찾기로 공부 잘했습니다. 이와 앞에 INDEX, MATCH 를 같이 활용해서 날짜와 거래처 라는 2조건이 있고, 중복적으로 여러 품목이 있으면 같은 날짜와 같은 거래처 2조건을 만족하는 중복된… 더보기 »

이승현
이승현
2021년 6월 1일 10:23 오전
게시글평점 :
     

혹시 중복값 제거하여 결과값이 세로가 아닌 가로로 배열되려면 어떻게 해야되나요?

주은과선율
주은과선율
2021년 6월 11일 6:33 오후
게시글평점 :
     

예제를 보면서 엑셀을 배우니깐 도움이 많이 됩니다.
혹시 B열과 D열 두개 열의 중복값을 제거한후 고유값을 추출하고 싶으면 어떻게 해야하는 알고 싶습니다.
배열수식으로 알고 싶습니다.

Last edited 3 월 전 by 주은과선율
이상원
이상원
2021년 7월 7일 5:06 오후
게시글평점 :
     

countif() 의 정의가 countif(범위, 조건)이라고 정의되어 있는데 여기서는 countif(조건, 범위)로 사용되는 거 같은데 이게 어떻게 가능한건지 이해가 되질 않습니다. 일단 작동은 하는거 같은데 개념을 잡을수가 없네요. 설사 배열식으로 설명이 된다고… 더보기 »

Last edited 2 월 전 by 성현아빠
이상원
이상원
2021년 7월 8일 3:59 오후
게시글평점 :
     

countif 함수 배열식과 작동하는 원리를 이해하지 못해 한참이나 인터넷과 유투브를 뒤진 끝에 올려주신 내용을 완벽하게 이해했습니다. 지금까진 주로 VBA로 많은 부분을 해결했는데 배열식과 함수의 관계를 다시 보게 되는 계기가 되었습니다.… 더보기 »

IDFK
IDFK
2021년 7월 15일 12:31 오후
게시글평점 :
     

안녕하세요. 혹시, 지역명에서 왼쪽에 추가 지정값을 놓을떄 추출은 어떻게 하는지요.. 예를 들어 지역명 왼쪽에 6월 서울 이면 6월에 해당하는 서울 값을 중복 값제외하고 뽑을수 있는지 문의 드립니다.

28
0
여러분의 생각을 댓글로 남겨주세요.x