엑셀_자동(수식)으로 중복없이 고유값만 추출하기_배열함수원리이해

중복없이고유값추출하기.xlsx

0.01MB

 

해당 포스트는 엑셀에 아직 Unique 함수가 없었을 때 작성한 것입니다. :)

배열함수의 작동원리를 이해할 수 있겠네요! 

 

데이터에서 중복된 값을 제외하고 고유값만 나열하는 방법 중 가장 쉬운 것은

전체 데이터를 복사 후, 데이터 탭에 “중복된 항목 제거” 기능을 사용하는 것이다.

 

다만, 위의 방법은 데이터가 업데이트 될 때마다 반복해주어야 한다는 단점이 있다. 즉, 수동이라는 것…

 

수식을 잘 활용하면 이 작업을 자동화할 수 있다.

인터넷을 좀 찾아보니… 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을 반환하게 된다.

Region 의 고유 값을 행으로 나열한 것은 G3 의 함수를 복사해서 참조 범위만 바꿔 준 것이니, 위에 내용이 잘 이해되었다면 쉽게 적용할 수 있을 것이다.