중복값 제거 및 고유값 추출
지원 버전 자세히 보기
범위의 중복값을 제거해 고유값만 추출하는 공식입니다.
인수 설명
01INDEX/MATCH 정방향 추출
{=INDEX ( 참조범위, MATCH ( 0, COUNTIF ( 확장범위, 참조범위 ), 0 ) )}인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
동작 원리
COUNTIF 함수가 이미 출력된 값을 표시하고 MATCH 함수와 INDEX 함수가 첫 미출력값을 정방향으로 반환합니다.
INDEX/MATCH 정방향 추출 시트에서 C2 셀이 서울을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
COUNTIF 함수가 출력 여부를 확인합니다
첫 수식의 확장범위 C1에는 머리글만 있으므로 A2:A8의 일곱 값은 모두 아직 출력되지 않은 상태입니다. COUNTIF 함수는 각 값에 대응하는 0을 반환합니다.
=COUNTIF ( $C$1:C1, $A$2:$A$8 )
C1에는 원본 목록과 같은 값이 없습니다. = {0;0;0;0;0;0;0}
A2:A8의 각 행에 대응하는 출력 횟수입니다. MATCH 함수가 첫 번째 0의 위치를 찾습니다
MATCH 함수는 COUNTIF 함수가 만든 배열에서 첫 번째 0을 찾아 원본 목록의 첫 행인 1을 반환합니다.
=MATCH ( 0, COUNTIF ( $C$1:C1, $A$2:$A$8 ), 0 )
첫 번째 미출력값의 상대 위치를 찾습니다. = 1
A2:A8에서 첫 번째 값의 위치입니다. INDEX 함수가 첫 고유값을 반환합니다
INDEX 함수는 A2:A8의 첫 번째 값인 서울을 반환합니다. 수식을 아래로 복사하면 확장범위에 앞선 결과가 누적되어 다음 고유값을 차례로 찾습니다.
=INDEX ( $A$2:$A$8, MATCH ( 0, COUNTIF ( $C$1:C1, $A$2:$A$8 ), 0 ) )
MATCH 함수가 반환한 1을 행 번호로 사용합니다. = "서울"
원본 목록에서 처음 등장한 고유값입니다. 02LOOKUP 역방향 추출
=LOOKUP ( 2, 1 / ( COUNTIF ( 확장범위, 참조범위 ) = 0 ), 참조범위 )인수 설명 자세히 보기
동작 원리
COUNTIF 함수가 이미 출력된 값을 제외할 배열을 만들고 LOOKUP 함수가 마지막 미출력값을 역방향으로 반환합니다.
LOOKUP 역방향 추출 시트에서 D3 셀이 분당을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
COUNTIF 함수가 앞선 결과를 표시합니다
D1:D2에는 머리글과 첫 결과인 서울이 들어 있습니다. COUNTIF 함수는 원본 목록의 서울에 1을, 아직 출력되지 않은 인천·수원·분당에 0을 반환합니다.
=COUNTIF ( $D$1:D2, $A$2:$A$8 )
원본 목록의 각 행이 앞서 출력됐는지 확인합니다. = {1;1;0;0;0;0;1}
서울 세 행만 이미 출력된 값으로 표시됩니다. 나눗셈이 미출력값만 1로 남깁니다
COUNTIF 함수의 결과가 0인 위치는 1을 반환하고, 이미 출력된 서울의 위치는 0으로 나누어 #DIV/0! 오류를 반환합니다. LOOKUP 함수는 이 오류를 건너뜁니다.
=1 / ( COUNTIF ( $D$1:D2, $A$2:$A$8 ) = 0 )
아직 출력되지 않은 위치만 숫자 1로 남깁니다. = {#DIV/0!;#DIV/0!;1;1;1;1;#DIV/0!}
인천·수원·분당 위치만 1로 남습니다. LOOKUP 함수가 마지막 1의 값을 반환합니다
LOOKUP 함수는 숫자 2 이하의 마지막 숫자인 1을 찾습니다. 마지막 1과 같은 위치의 원본값은 분당이므로 D3 셀에 분당을 반환합니다.
=LOOKUP ( 2, 1 / ( COUNTIF ( $D$1:D2, $A$2:$A$8 ) = 0 ), $A$2:$A$8 )
오류를 제외한 마지막 1의 원본값을 찾습니다. = "분당"
아래에서 두 번째로 추출되는 고유값입니다. 03UNIQUE 동적 배열 추출 (Excel 2021 이상)
=UNIQUE ( 참조범위 )
해당 공식은 일반적인 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/