인터넷을 좀 찾아보니… 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)
- 참조범위 각 항목의 고유값여부 확인
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 함수를 사용하여 오류표시를 방지할 수 있습니다.
5 20 투표
게시글평점