엑셀에서 구글스프레드시트의 UNIQUE 함수 구현하기
블로그에 어떤 분께서 엑셀에서 구글 스프레드시트의 Unique() 함수를 사용 해보고 싶다고 하신 분이 있어서
한번 해보았습니다. VBA를 가지고 함수로 구현 하려면 버튼을 누르거나 해서 매크로를 실행 시켜야 합니다.
구글의 UNIQUE() 같이 쉬트 상에서 아무런 액션 없이 그 자리에서 구현 하는 방법은 배열 수식 밖에 없을 것 같아 배열 수식으로 하는 방법을 알아 냈습니다.
D3셀에 있는 입력된 수식을 보시면
{=INDEX($B$3:$B$20,MATCH(0,COUNTIF($D$2:D2,$B$3:$B$20),0))}배열 수식이므로 D3셀에 수식을 입력 하고 control + shift + enter 를 동시에 누르셔야 합니다.
$B$3:$B$20 은 중복된 항목이 있는 목록입니다. 엑셀로 영역에 "목록"이란 이름을 부여 하였습니다.
{=INDEX(목록,MATCH(0,COUNTIF($D$2:D2,목록),0))} 이런 식으로 입력 하면 되겠습니다.
편의 상 설명을 위하여 A1 형식의 영역을 표시 하였습니다.
D3셀 부터 아래로는 중복된 글자가 표시될 영역입니다.
D3셀에 수식을 입력 하고 D4셀 부터는 D3셀을 복사하여 붙여 넣기를 합니다.
그러면 옆에 같은 모양으로 $D$2는 고정이 되고 D2 부분에서 행에 맞게 숫자가 하나 씩 늘어 납니다.
팁으로, D3셀의 하단 모서리에 마우스를 갖다 대면 십자 모양으로 변하는 데 클릭한 후 아래로 Drap and Drop 하셔도 됩니다.
원리 및 사용법을 설명 드리면
- INDEX 함수의 사용법은 INDEX(목록, 순번) 로 사용하고 목록에서 순번에 해당하는 항목을 표시합니다.
=INDEX({Richard, Jonathan, Juliet} , 3) 이렇게 하면 Juliet 이 표시 됩니다. - MATCH 의 사용법은 MATCH( 찾을문자, 영역, 0) 으로 영역에서 찾을 문자를 찾아서 순번을 표시합니다.
=MATCH( 0 , {1,2,0,3},0) 이렇게 하면 3 이 튀어나옵니다.
- COUNTIF의 사용법은 COUNTIF(영역,문자) 이렇게 사용하고
ex) =COUNTIF({0,0,1,2},"0") 이러면 2개 이므로 2가 표시 됩니다.
윗 수식에서는 배열수식 안이기 때문에
D7셀 기준으로 COUNTIF함수는 다음과 같이 치환 됩니다.
{
COUNTIF (
{"UNIQUE";"Richard";"TJ";"Jonathan";"Juliet"},
{"Richard";"TJ";"Jonathan";"Juliet";"HK";"Richard";"HK";"Jonathan";"HK";"Jonathan"}
)} = {1;1;1;1;0;1;0;1;0;1}
--> COUNTIF 함수가 배열 수식 내부에 있는 경우 뒷 인자를 앞 인자와 비교하여 배열의 수 만큼 반복 하여 배열에 넣게 됩니다.
D3 셀을 기준으로한 수식에서는 $D$2:D2 이 기준이 되기 때문에 UNIQUE이라는 단어가 두번째 배열 속에 없으므로 모두 0
=INDEX($B$3:$B$20,MATCH(0, {0;0;0;0;0;0;0;0;0;0} ,0))
MATCH 함수에서는 첫번째 0을 찾게 되어 1 을 리턴하게 되며 1은 "목록" 에서 첫 번째 인 Richard 가 됩니다.
D7셀을 기준으로한 수식에서는 $D$2:D6 이 기준이 되기 때문에
=INDEX($B$3:$B$20,MATCH(0,{1;1;1;1;0;1;0;1;0;1},0))
{"UNIQUE";"Richard";"TJ";"Jonathan";"Juliet"} 을 모두 뒤져서 숫자를 세게 됩니다.
위에서 언급한대로 {1;1;1;1;0;1;0;1;0;1} 첫번째 0이 나오는 순번은 5 입니다. 그래서 HK를 가리키게 됩니다.
D4인 다음 셀의 경우 {"UNIQUE";"Richard"}
D5는 {"UNIQUE";"Richard";"TJ"} 행이 늘어 날 때 마다 하나씩 추가되어 각 행 별로 이미 표시한 것은 제외하고 다른 것만 남게 됩니다.
이런 식으로 첫번째 D3셀로 부터 교차 하여 검색 해가면서 INDEX 와 MATCH 함수를 사용해서 중복된 목륵을 제외하고 목록에 표시 할 수 있습니다.
엑셀에서는 함수로는 제공하지 않지만 데이터 메뉴에 중복된 항목 제거 기능을 사용 하여 하는 편이 빠릅니다.
차세대 엑셀에서는 파이썬을 지원 한다고 하니 구글 앱 스크립트 같이 강력한 함수와 기능을 기대 해 봅니다.
엑셀 파일은 네이버 블로그에서 공개 해드리겠습니다.