엑셀 함수로 중복 데이터 정리 및 분류하는 실제 사례

필자의 지인이 주말에 전화를 걸어 와 엑셀로 데이터 정리하는 일을 도와 줄 수 없느냐고 묻는다. 자료량이 많지 않은데 중복된 것을 제거하는 반복 작업이니 분명 쉽게 할 수 있는 방법이 있을 것이라는 설명이었다.

실제로 엑셀이나 워드, 아래아한글 등에서 중복된 자료를 정리하는 단순 반복 작업은 사람을 지치고 짜증나게 만든다. 따라서 지인의 입장을 고려해 연휴 기간에 시간이 나기에 자료를 보내라고 얘기했다. 누군가의 스트레스 해소를 도와 줄 수 있다면 좋은 일일 것이기 때문이다.

지인이 보내 온 자료는 국가별 전화코드였다. 국제전화를 걸 때 각 국을 인식하는 고유코드로 대한민국의 경우 82이다.

그런데 아래 박스에 있는 것처럼 번호 옆에 있는 국가명이 두번씩 반복되어 있다. 이것을 하나만 나오게 일괄 작업 해 달라는 요청이었다.  그런데 자료를 보니 꽤 복잡하다. 각 라인미다 문자열의 길이도 다르고 한 단어가 아닌 두개의 단어가 중복된 경우도 있기 때문이다. 아주 단순한 변환 작업만은 아님을 의미한다.

+1     미국 미국
+1 340     미국령 버진아일랜드 미국령 버진아일랜드
+1 670     북마리아나 제도 북마리아나 제도
+1 671     괌 괌
+1 684     아메리칸사모아 아메리칸사모아
+1 787, +1 939 푸에르토리코 푸에르토리코
+1     캐나다 캐나다
+1 441     버뮤다 버뮤다

 

텍스트 화일로 되어 있는 이 자료의 원본 파일은 다음과 같다.

국가별 코드.txt

우선 엑셀 프로그램을 띄운 후 위의 텍스트 화일을 불러 들였다. 각 항목이 공백으로 분리되어 있기에 자동적으로 각 입력 항목이 분류된다.

 

[텍스트 화일 불러 들이기]

 

[텍스트 마법사로 항목별로 분류하여 읽어 들임]

이제 엑셀로 데이터가 불러 들여졌으니 본격적으로 데이터 분류 작업을 해야 한다.

아래 사진에서 C10에 들어간 함수는 C2에 표기했다. D10은 D3, E10은 E4, F10은 F5, G10은 G6에 각각 기입했다.

다음은 각 행에 들어 있는 함수식(Excel Function)이 어떤 것인지에 대한 설명이다.

C10의  함수 식은 IF(ISERR(FIND(" ",B5,1)), B5,FIND(" ",B5,1))이다. 우선 국가명이 들어 있는 B5에서 첫번째 공백(Space)이 어디에 있는지 B5 문자열의 첫번째부터 탐색한다. 만약 아무런 공백이 없어 에러가 발생 된다면 C10에 B5를 표시한다. 그렇지 않다면 첫번째 공백의 위치를 C10에 표시하는 함수 식이다.

D10의 함수는 LEFT(B10,C10)이다. 이는 국가명이 들어 있는 B10에서 공백이 발견된 위치까지의 문자를 추출하는 함수 식이다. 따라서 D10에 '미국'이라는 값이 나타난다.

E10의 함수는 IF(ISERR(FIND(" ";B10;C10+1));"에러";FIND(" ";B10;C10+1))이다. 여기에서는 국가명이 2개의 단어로 되어 있는 경우를 분류하기 위한 것이다. "미국 미국"의 경우 첫번째 공백만 제거하면 되나 11번째 줄처럼 "미국령 버진아일랜드 미국령 버진아일랜드"의 경우 두번째 공백을 찾아 분리해야 하기 때문이다.

따라서 국가명이 들어 있는 B10에서 첫번째 공백 이후부터 2번째 공백을 찾아 발견되면 이것의 위치를 표시하고 없으면 '에러'라 표기하는 함수식이다. 따라서 두번째 공백이 없는 10번째 줄에는 '에러"가 두번째 공백이 있는 11번째 줄에는 두번째 공백 문자의 위치인 '11'이 나타난다.

F10에는 MID(B10,C10+1,E10-C10)가 들어 있다. 이것은 문자열의 중간에서 특정 갯수만큼의 문자열을 뽑아내는 함수이다. 국가명이 있는 B10에서 첫번쨰 공백 이후 부터 첫번째 공백과 두번째 공백 사이만큼의 문자열을 추출한다. 그렇게 되면 두번째 문자열만 나타난다.  예를 들면 11번째 줄에서는 '버진아일랜드'가 표시된다.

 

마지막으로 G10의 함수식은 IF(E10="에러",D10,CONCATENATE(D10," ",F10)이다. 이는 두번째 공백이 있으면 D10과 F10의 문자열을 합하고 그렇지 않으면 D10만을 표시하는 함수 식이다. 따라서 G행에는 중복 명칭이 제거된 최종적인 국가명이 나타난다.

10번째 줄에 적용 되었던 함수들을 다른 줄로 모두 복사하면 변환 작업은 마무리 된다.

이후에는 이렇게 추출된 국가코드와 국가명을 복사하여 다른 시트에 아래 사진처럼 값 복사를 하면 작업이 완료된다. 이를 텍스트 화일로 추출 할 수도 있을 것이다.

 

 

다음은 실제로 작업에 이용된 엑셀 화일 및 추출된 텍스트 화일을 첨부한다.

 

국가별 코드 변환 후.txt

 

국가별_코드.xlsx

중복 데이터의 자동 분류는 데이터량이 많을 때 유용하다. 위의 작업 대상 라인 수가 몇만, 몇십만 라인이더라도 이렇게 함수 식으로 자동 분류하면 수 분내에 작업을 완료 할 수 있다. 일일이 작업 하려면 여러 사람이 투입 되어야 하고 작업 과정에서 에러가 발생될 수도 있으니 분명 효율적인 방식이다.

따라서 유사한 고민을 갖고 있다면 위의 방식을 참조해 해결 할 수 있다.

직접 시도 하면서 잘 되지 않거나 의문 사항이 있는 경우 문의 주시면 가능한 범위 내에서 지원토록 하겠습니다. 단순 반복 작업에 의한 스트레스에서 해소 될 수 있기를 기대합니다.