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

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

작성자 :
오빠두엑셀
최종 수정일 : 2021. 12. 10. 23:49
URL 복사
메모 남기기 : (52)

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

함수공식

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

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

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

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

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

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

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

공식 설명

엑셀 중복값 제거 공식은 배열수식으로 아래 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. 참조범위 각 항목의 고유값여부 확인
    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 함수를 사용하여 오류표시를 방지할 수 있습니다.
5 28 투표
게시글평점
52 댓글
Inline Feedbacks
모든 댓글 보기
52
0
여러분의 생각을 댓글로 남겨주세요.x