엑셀 고유값 추출 함수 - egsel goyugabs chuchul hamsu

인터넷을 좀 찾아보니… row 함수에, 나누기 곱하기 하는 복잡한 수식들이 나오던데… 그냥, index, match, countif 함수만 잘 활용하면 된다. 물론 배열 함수를 좀 이해 해야 겠지만…

첨부 파일을 보면…

B2:E10 표의 데이터에서 Department 는 G열에 Region은 2행에 중복 없이 고유값만 나열하여 새로운 표를 만들고자 했다.

G3의 수식은

{=IFERROR(INDEX($B$3:$B$10,MATCH(TRUE,COUNTIF($G$2:G2,$B$3:$B$10)=0,0)),"")}

IFERROR 는 셀에 Error 값이 나오지 않게 하는 것일 뿐이니 패스…

기본적으로 Index – 돌려 받을 값이 포함된 범위 지정 - , Match – 찾으려고 하는 값이 있는 위치/순서 지정- 함수 조합을 사용했다. 돌려 받을 값이 Department 이므로 $B$3:$B$10 지정

Match 함수에서 찾고자 하는 값 (lookup_value) 는 true

여기서 반드시 이해 해야 하는 것은 Match 함수의 lookup_array 부분COUNTIF($G$2:G2,$B$3:$B$10)=0 이다. 참고로, 배열 함수에서 IF, Countif 등 조건 함수들은 잘 사용하면 로직 값들 (True/ False) 의 배열/집합을 돌려주도록 할 수 있다.

수식에서 Countif 부분만 잘 선택해서 F9 을 눌러보자.

그럼, Countif 함수의 결과가 특정 값이 아니라 {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE} 배열/ 집합임을 확인할 수 있다. 즉 $G$2:G2 범위에서 $B$3:$B$10 (Department) 값이 있는지 하나 하나 확인하였는데, 하나도 없으니 다 0이고, 그러니 모두 True 값만 반환한 것이다.

즉, Match 함수는 이 {TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE} 집합에서, True 값을 찾으라고 명령한 것이다. 그런데 첫번째부터 True 가 있으니 1을 돌려주었고, 따라서 Index 함수의 Array 부분의 첫번째 위치한 SRH 값이 나타나게 된 것이다.

G4 수식의 Countif 부분을 선택해 F9 를 누르면 {FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE} 가 나오는 것을 보면 좀 더 쉽게 이해될지도?? 결국 Match 함수는 3을 반환하게 된다.

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

함수공식

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

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

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

= LOOKUP(2, 1/(COUNTIF($머릿글:머릿글, $참조범위)=0), $참조범위)

사용된 인수 설명

항목설명$참조범위중복값을 제거(또는 고유값만 추출)할 범위입니다.

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

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

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

공식 설명

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

엑셀 INDEX 함수
엑셀 MATCH 함수
엑셀 LOOKUP 함수
엑셀 COUNTIF 함수

참조범위에서 중복값을 제거한 뒤 고유값만 출력하는 공식이며 옵션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 함수를 사용하여 오류표시를 방지할 수 있습니다.