엑셀, 선물 명단에서 중복 정리하기

명절이면 지인분들에게 선물을 하게 됩니다. 개인적으로는 자기 것만 정리하면 되는 것인데, 회사에서는 이사람 저사람 모두 취합해야 하기 때문에 이것도 일이 됩니다. 이렇게 여러사람에게서 취합된 선물 명단에서 중복을 가려내기 위해 눈으로 일일이 찾아 내려면 눈이 어느새 침침해 집니다. 한번쯤 경험해 보셨을 거에요.. ^^> 이거 어느 세월에 중복을 가리고 퇴근하나..! 이곳에서 설명한 것을 눈여겨 보았다가 실제로 적용해 보면, 귀가하는 발걸음이 가벼워집니다.

먼저 아래 그림을 한번 보세요. 몇 군데 표시를 해 두었는데, 보안이 필요한 부분은 흰색으로 칠해 두었습니다.
워크시트에 'A님' 부터 'E님' 까지는 각각의 분들에게서 받은 선물 명단이라고 합니다. 처음에 엑셀 문서를 작성한 분이 'A님' 부터 'E님' 까지 각 워크시트에 대해서 제목들을 동일한 구성으로 정리를 해 두었더군요. 각 워크시트 안에서는 중복된 내용이 없다고 합니다. 그리고 이곳에서는 'MS 엑셀 2003' 을 기준으로 설명합니다.

(아래 그림에서 글자가 깨져 보인다면 그림을 클릭해 보세요.)

중복을 가려내기 위해서 vlookup 이라는 함수를 이용합니다. 중복을 체크하는 방법은, 새로운 워크시트를 하나 만들어서 (편의상 워크시트 이름을 '전체' 라고 합니다) 첫번째 'A님' 워크시트의 내용을 담고, '전체' 워크시트를 기준으로 'B님' 워크시트에 vlookup 함수를 이용하여 동일 여부를 가려낸 다음, 'B님' 워크시트에서 중복되지 않은 내용을 '전체' 워크시트에 복사해 넣고 하는 식으로 'E님' 워크시트 까지 작업하면 됩니다. 그리고, 중복 여부는 이름으로 먼저 비교하고, 동일 이름이 나오면 핸드폰 번호와 주소를 기준으로 중복을 체크합니다.

모니터가 아무리 크고 길어도 해상도가 아무리 좋아도 각 항목이 한눈에 들어오게 하는데 제약사항이 많습니다. 그렇다면 눈에 확 들어오도록 각 셀들의 크기를 동일하게 하는 것이 좋습니다. 인쇄하려고 하는 작업이 아니라 중복을 가려내는 작업을 하려고 폭을 줄이는 것이니까, 인쇄할 때는 폭을 다시 원래대로 해 놓으면 됩니다.

아래 그림과 같이 폭을 동일하게 맞추려는 칼럼들을 선택하세요. 알파벳으로 되어 있는 칼럼을 클릭한 상태에서 필요한 만큼 마우스를 오른쪽으로 주욱 드래그 하면 선택이 됩니다. 선택된 칼럼에서 마우스 오른쪽 버튼을 클릭하면 아래 그림과 같이 메뉴가 나오고 여기서 '열 너비(C)..' 를 클릭합니다.

아래 그림과 같이, 열 너비에 알맞은 수치를 입력하고 '확인' 버튼을 클릭합니다. 각 칼럼들이 한눈에 보이도록 하면 됩니다.

폭이 줄어들어서 데이타들이 한눈에 들어오네요. 역시 여백이 어느정도 있어야 마음도 넉넉해 지고 작업도 수월합니다. 그런데 문제가 하나 있네요. 셀에 입력된 내용이 많아서 두 줄에 걸쳐서 내용이 보이네요. 이럴 때는 '셀에 맞춤' 기능을 이용합니다.

해당 셀들을 선택해서 조정하려는 셀에 마우스 커서를 올려 놓고 오른쪽 마우스 버튼을 클릭합니다. 메뉴에서 '셀 서식(F)..' 를 클릭합니다.

아래 오른쪽과 같이 '셀에 맞춤(K)' 를 선택하고 '확인' 버튼을 클릭합니다. 간혹 아래 왼쪽과 같이 '셀에 맞춤(K)' 이 활성화 되지 않는 경우가 있는데 이런 경우에는 바로 위에 있는 '텍스트 줄 바꿈(W)' 이 체크되지 않은 상태로 있어야 합니다. 해당 항목에 '■' 체크가 되어 있는 것은 선택된 셀들중 일부는 선택 '√' 상태, 일부는 해제 상태로 서로 섞여 있는 것을 의미합니다.

아래 그림과 같이 셀 폭에 맞게 글자들이 줄어듭니다. 셀의 폭을 넓히면 다시 글자들이 보이게 됩니다.

이제는 vlookup 함수를 사용할때 어느 워크시트에서 가져온 것인지 구분하는 방법을 설명합니다.
먼저 'A님' 워크시트를 선택합니다. 아래 그림과 같이 맨 오른쪽 칼럼에 특정 문자열을 입력합니다. 여기에서는 'A' 문자를 사용합니다. 어느 워크시트에서 가져온 데이타인지 확인하려는 것이므로 작업하는 분이 구분하기 쉬운 문자를 입력합니다.
동일한 문자를 한번에 입력하는 방법은 맨 위의 셀에 문자를 입력하고 늘이기 컨트롤을 더블클릭 하거나, 해당 범위를 선택한 상태에서 문자를 입력하고 <Ctrl> 키와 <Enter> 키를 동시에 누르면 됩니다.
이러한 방식으로 'A님' 부터 'F님' 워크시트 까지 동일하게 적용합니다.

이제는 위에서 설명한 '전체' 워크시트를 생성합니다. 새로운 워크시트를 삽입할 수도 있겠지만 이곳에서는 복사 기능을 이용하여 'A님' 워크시트를 복재합니다. 'A님' 워크시트의 '탭' 부분을 마우스 오른쪽 버튼을 클릭하고, 메뉴에서 '이동/복사(M)..' 을 클릭합니다.

'이동/복사' 창에서 '복사본 만들기(C)' 를 체크 상태로, '다음 시트의 앞에(B)' 부분을 선택하고 '확인' 버튼을 클릭합니다.

새로 생성된 워크시트의 '탭' 이름을 아래와 같이 '이름 바꾸기(R)..' 메뉴를 이용하거나, 아니면 해당 탭을 마우스 왼쪽 버튼으로 더블클릭하여 '탭' 이름 바꾸기 상태로 만들어서 이름을 '전체' 로 변경합니다.

여기까지가 사전 작업입니다. 반 정도 왔습니다. 얼마 남지 않았습니다. 힘 내세요.. ^^> 이제 vlookup 을 해 보겠습니다.
아래 그림과 같이 'B님' 워크시트 중에서 vlookup 을 위해서 미리 입력한 문자 'B' 옆에 vlookup 을 적용해 보겠습니다.
참고로 'A님' 워크시트에는 'A' 문자를, 'B님' 워크시트에는 'B' 문자를 입력하는 식으로 'E님' 워크시트에는 'E' 문자 사용.
함수 이름과 파라미터를 알고 계신 분은 직접 입력하셔도 무방합니다.
처음 설명하는 것이므로 그림으로 설명합니다. 다음에는 수식으로 설명합니다. ^^>
입력하려는 셀에서 'fx' 를 클릭하면 '함수 마법사' 창이 열립니다. 범주 선택을 '찾기/참조 영역' 으로 하고,
함수 선택에서 'vlookup' 을 찾아 클릭한 다음 '확인' 버튼을 클릭합니다.

Lookup_value 는 '성명' 에 해당하는 첫 셀을 선택합니다.

vlookup 함수의 인수(파라미터)는 다음과 같이 설정합니다.
첫째, Lookup_value 에 선택한 셀이 이름이면 Table_array 에서 선택하는 범위의 맨 왼쪽이 '이름' 을 포함해야 한다는 것.
둘째, Lookup_value 는 비교하는 기준이기 때문에 Lookup_value 가 변하더라도 동일한 기준을 적용하려면, Table_array 를 절대좌표로 만들어야 한다는 것입니다. 키보드의 <F4> 를 눌러 보면 'E3:J76' → '$E$3:$J$76' → 'E$3:J$76' → '$E3:$J76' →'E3:J76' 순으로 계속 변하는데 여기서 필요한 절대좌표는 '$E$3:$J$76' 입니다.
셋째, Col_index_num 은 기준 칼럼을 1, 그 오른쪽 칼럼을 2, 이런식으로 표기합니다. 'A' 문자가 입력된 J 칼럼은 '이름' 이 들어간 E 칼럼을 포함해서 6번째 칼럼이므로 Col_index_num 에는 '6' 를 입력합니다.
넷째, Range_lookup 은 true 와 false 가 있는데, 동일한 것을 찾으려면 false 를 입력합니다. true 는 범위 개념이 들어 갑니다. 이를테면 크거나 같다와 같은 의미 입니다. 더 자세한 내용은 이곳에선 생략. 필요한 부분을 댓글로 다시면 추가 설명 ^^>

아래와 같이 한 다음 '확인' 버튼을 클릭합니다. 그리고 채우기 핸들을 더블클릭 하면 수식이 주루룩 반영됩니다.

그런데 왜 이러죠..? 'K5' 셀만 빼고는 '#N/A' 라고 나오네요. 이것은 해당 값을 찾아오지 못해서 발생하는 겁니다.
이것을 없애려면 수식을 조금 바꾸어야 합니다. 'if' 명령과 'iserror' 함수를 사용합니다.

'E3' 셀을 편집해서 아래와 같이 바꾸어 줍니다. 그리고 다시 채우기 핸들을 마우스 오른쪽 버튼을 더블 클릭합니다.
if 사용법은 if( "조건", "조건이 참인 경우의 값", "조건이 거짓인 경우의 값" ) 입니다.
iserror 사용법은 iserror( "값1" ) 입니다. 만약 '값1' 에 오류가 있으면 true, 오류가 없으면 false 를 리턴합니다.
=IF(ISERROR(VLOOKUP(E3,전체!$E3:$J76,6,FALSE)),"",VLOOKUP(E3,전체!$E3:$J76,6,FALSE))

화면에 담지는 않았지만 위 그림에 나오던 '#N/A' 가 없어졌습니다.
그런데 중복되는 항목이 체크된 것은 좋은데, 전화번호나 주소를 확인해 봐야 하잖아요.
이것을 반영하려면 L3, M3, N3 각각의 셀에 아래아 같은 수식을 입력합니다.
L3 에 넣을 수식, =IF(LEN(K5)=0,"",VLOOKUP(E5,전체!$E$3:$J$76,2,FALSE))
M3 에 넣을 수식, =IF(LEN(K5)=0,"",VLOOKUP(E5,전체!$E$3:$J$76,3,FALSE))
N3 에 넣을 수식, =IF(LEN(K5)=0,"",VLOOKUP(E5,전체!$E$3:$J$76,4,FALSE))
이곳에 len 함수를 사용했는데, 셀에 들어가 있는 값의 문자열 길이를 구합니다.
참고로 lenb 함수는 바이트 단위로 계산. N 바이트 문자의 경우 이를테면 '한글' 이면 len 에서는 2, lenb 에서는 4가 나옵니다.
isblank를 사용하면 될 것같지만 소용없습니다. isblank 함수는 수식이 들어 있어도 false 가 리턴됩니다.
istext 함수는 수식이 들어 있어도 true 를 리턴합니다.
수식이 아니라 결과값에서 뭔가를 하는 함수를 이용하려면 역시 'len' 함수를 사용하면 됩니다.
L3 에는 '주소' 가 나오도록 하기 때문에 vlookup 의 Col_index_num 에 '2' 를 입력하고,
M3 에는 '전화1' 이 나오도록 하기 때문에 '3', N3 에는 '핸드폰1' 이 나오도록 하므로 '4' 를 입력합니다.

자, 아래와 같이 L 칼럼에는 주소, M 칼럼에는 전화1, N 칼럼에는 핸드폰1 이 표시됩니다.
그런데 중간중간에 중복된 것이 나오기 때문에 정렬을 해 주어야 합니다.

아래 그림과 같이 명단이 들어 있는 모든 로우를 선택합니다.
왼쪽에 표시된 숫자 '3' 을 클릭하고 <Ctrl> 키와 <Shift> 키를 누른 상태에서 아래 화살표키 ↓ 를 누르면 됩니다.
선택된 내용을 확인한 다음에 '데이터(D)', '정렬(S)' 를 차례로 클릭합니다.

중복된 내용이 들어있는 '열 K' 를 '오름차순' 으로 체크하고 '확인' 버튼을 클릭합니다.

아래 그림과 같이 중복이 발생한 부분이 맨 아래쪽으로 이동합니다.
이름으로는 중복이지만 전화번호나 주소를 보면 중복이 아닌 경우도 있습니다.
이럴때는 해당 로우의 셀들을 '채우기 색' 기능을 이용하여 다른 색으로 배경을 칠합니다.
이제 중복되지 않은 부분들만 복사해서 '전체' 워크시트 하단 부분에 추가합니다.
물론 이름은 동일하지만 전화번호나 주소가 다른 명단도 포함하여 '전체' 워크시트에 추가합니다.

'전체' 워크시트에 아래 그림과 같이 J 칼럼에 'A' 가 들어간 내용과 'B' 가 들어간 내용들이 존재하고,
이들 명단은 'A님' 워크시트와 'B님' 워크시트 모두에서 중복을 체크한 내용이 됩니다.
이제 'C님', 'D님', 'E님' 들에 대해서도 위와 같은 방식으로 적용을 합니다.
다만 'C님' 을 대상으로 할 때는 '전체' 워크시트의 J 칼럼에 'A' 와 'B' 가 모두 표기된 부분을,
vlookup 함수의 Table_array 에 반영해야 합니다.
동일한 방식으로 'E님' 워크시트는 'A', 'B', 'C', 'D' 등이 모두 들어가야 하겠지요.

동일한 부분이 중복되도록 설명이 되는 부분은 화면캡처를 하지 않았습니다.
본문 분량만 많아지기 때문에 생략한 점 양해 바랍니다.

보너스로 각각의 건수를 체크해 보겠습니다.
중복 체크 설명을 쉽게 이해하신 분은 조금더 진도를 나가 보겠습니다.
아래 그림에서 '수량' 부분을 채우는 문제입니다.
여기에서는 sum 함수와 배열을 사용해 보겠습니다.

먼저 작업을 쉽게 하기 위해서 아래와 같이 '전체' 워크시트의 J 칼럼에 대하여 이름을 '_data' 라고 지정합니다.
이름을 사용하면 수식에서 사용하기 쉽고 절대주소이기 때문에 값이 틀리게 나오는 오류도 막을 수 있습니다.

자, 이제 수식을 입력합니다. D4 셀에는 =sum((_data="A") * 1) 수식을 입력하고, <Ctrl> 키와 <Shift>키를 누른상태에서 <Enter> 키를 누릅니다. D4 셀을 클릭해 보면 { =sum((_data="A") * 1) } 처럼 "{" 와 "}" 로 둘러 싸인 것이 보입니다.
data 이름의 범위에 대하여 문자 "A" 와 동일하면 true 또는 숫자 '1' 로 해석하고, data 범위에 대하여 일일이 숫자 1과 곱한 다음 모두 더한다는 내용입니다. sumif 등의 함수를 사용해도 되겠지만, 배열수식을 이용하면 여러가지 조건에 대하여도 계산해 내기 때문에, 일단 배열수식에 맛들이면 다른 함수를 이용할 것도 배열수식만 고집하게 됩니다. ^^>

그러면 D5 셀과 같이 3가지에 대하여 배열수식을 사용하려면 어떻게 해야 할까요..?
아래와 같이 하면 됩니다. 개개의 배열수식을 모두 더하면 되는 것입니다.
{=SUM((_data="B")*1) + SUM((_data="C")*1) + SUM((_data="D")*1)}

이상입니다.