엑셀 중복값 제거 (또는 고유값 추출) 함수 공식 알아보기
함수공식
옵션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 함수를 사용하여 오류표시를 방지할 수 있습니다.
- 참조범위 각 항목의 고유값여부 확인
해당 공식은 일반적인 COUNTIF 함수의 동작과는 약간 다르게 배열수식으로 사용됩니다.
따라서 '참조범위'에서 각 값을 하나씩 돌아가며 '머릿글:머릿글' 범위에 해당 값이 포함되어있는지 여부를 계산한 뒤, 배열로 반환하는 수식으로 이해하시면 되겠습니다.
제 답변이 도움이 되셨길 바랍니다.^^
감사합니다.
상식적인 생각으론 님이 생각하시는 것처럼 '범위'가 항상 '찾을값' 보다 커야 할 거 같지만 '찾을값'이 배열로 설정되면 범위보다 찾을값 배열이 더 커보이는(넓어보이는?) 착시를 느끼게 됩니다. 하지만 찾을값 배열의 각요소 하나에 대해서 '범위'의 각각의 값에 대응해 결과를 나타낸다면 countif 함수의 기본 원칙에 위배되지 않는다는 걸 알수 있습니다.
저도 처음엔 님처럼 생각한 결과 무지하게 혼란스러웠는데 이 곳 저 곳을 돌아다닌 결과 이해가 되어 올립니다. 님은 이미 이해하셨더라도 아직 이해 중인 분들을 위해서라도....
배열식은... MS에서도 그리 자세하게 설명하진 않는 내용이라.. 처음엔 이해하기가 좀 어렵긴 하네요.
LOOKUP 함수는 맨 마지막부터 값을 조회합니다. 따라서 뒤에서 3번째 위치한 '수원' 반환됩니다.
답변이 도움이 되셨길 바랍니다.
감사합니다.
강의 항상 잘 듣고 보고 있습니다
첨부화일에서 중간 데이타 값중 일부를 삭제하면 결과값 중간에 "0" 이나오는데 제가 중복값 골라서 데이터 추출하는 작업을 많이하다 보니 약간의 트릭을 통해 해당값만 나오게 사용하고 있습니다.
- { = INDEX($참조범위, MATCH (0, COUNTIF($머릿글:머릿글, $참조범위), 0)) }
정방향 출력이 필요해서 위 방법으로 했는데 뒤죽박죽으로 나오네요.중복값 걸러서 잘 나오기도 하고 어떤건 단독항목인데 중복으로 나오기도 하고 랜덤입니다. 왜 그럴까요 ㅡㅡ;
{=INDEX($C$5:$C$107,MATCH(0,COUNTIF($C$4:C4,$C$5:$C$107),0))}
분명 배열수식으로 했는데 말이죠.
2.머리글 위치가 참조범위의 머리글인 거 맞죠?
3.그리고 위 공식에 대한 설명은 없는 거죠? 찾아도 안 보이는 거 같아서요.ㅠ
4.ps. 선생님! 이번에 자세히 보다 보니 요약공식 처럼 적어 놓은 부위가 헷갈릴 수가 있게 돼 있는 거 같아요.
$참조범위
이렇게 표시된 참조범위가 실제 수식을 보면 절대참조인 (예: $A1:A3$) 달러 표시로 돼 있는 경우가 있어서 간편하게 보다보면 헷갈리고 실제 수식을 다시 확인해야 하는 경우가 생기네요. 분명 간단히 알려주고자함에 만들어 주셨겠지만요.( 특히 초입문자에게는 더 그러하지 않을까요.) 지금 막히는 부분이 생겨 자세히 보니 전부가 그렇게 표시된 것인지는 모르겠으나 지금 학습하는 여러 글에서 수식과 똑같게 표시돼 있지 않네요.ㅠ
이미 스크랩 많이 했는데;;
익숙하지 않고 낯선 함수 수식에서는 요약공식만 보고 헷갈릴 수 있을 거 같아서요. 새로운 글에서는 절대참조 사용이면 절대참조 표시로 실제 수식과 같이 표시해 주면 더 좋을 거 같습니다.
머리글은 입력될곳의 위치네요.
본문에서는 댓글 수정이 안 되나 봐요,,,
(lookup 사용한 중복제거)
https://www.oppadu.com/%EC%97%91%EC%85%80-%ED%95%A8%EC%88%98-%EB%AC%B4%EB%A3%8C-%EA%B0%95%EC%9D%98-%EB%8F%99%EC%A0%81%EB%B2%94%EC%9C%84-%EC%9C%A0%EB%8F%99%EB%B2%94%EC%9C%84-%EC%9E%90%EB%8F%99-%EB%AA%A9%EB%A1%9D/