엑셀 매크로를 배우면 퇴근이 빨라질까요?
엑셀 매크로를 알게 되었지만, 결코 퇴근이 앞당겨지지는 않았습니다. 그러나, 다음 2가지가 저를 위로합니다.
1. 엑셀 매크로(VBA)를 만들고 사용할 때, 머리를 사용하고 있다는 느낌이 강하게 듭니다. 그래서, 나이 먹고 치매에 걸리지 않을 거라는 확신이 들곤 합니다.
2. 엑셀 매크로(VBA)는 보통 사용하지 않는 기능이기에, 나는 남들이 하지 못하는 것을 한다는 자부심이 조금 있습니다. 이 정도 자부심도 기죽어 살아야 하는 월급쟁이 생활에 큰 위안이 됩니다.
지난번에는 text와 number를 엑셀 매크로(VBA)로 구분하는 방법을 익혔습니다.
(우리나라 말 문자와 숫자라는 용어는 약간 불확실한 점이 있어 영어로 사용하고자 합니다.)
2가지를 비교하기 위해서는 동일한 조건이어야 합니다. 때문에 2019년 쉬트에 있는 "핸드폰번호"와 2020년 시트네 있는 "핸드폰번호" 양자를 모두 한 가지 조건으로 변경해야 합니다.
"핸드폰번호"가 text가 아니고 number인 셀은 text로 먼저 변경합니다.
매크로(VBA) 스크립트는 아주 간단합니다.
Cells(i, 3).Font.Color = vbRed (number인 셀을 찾아서, 빨간색 글씨로 바꿔라)를
Cells(i, 3) = "'0" & Cells(i, 3) (문자표시 캐릭터 '와 0을 설 앞에 써라)로 바꾸면 됩니다.
그리고 매크로를 실행하면, 아래와 같이 "C5"셀이 1012345678인 number에서 '01012345678(text)로 변경됩니다.
number 셀 앞에 0을 붙이는 것은 의미가 없습니다. 오직 text 셀에서만 설 앞에 0이 의미를 갖게 됩니다.
이제 "숫자_문자_구분하기한뒤에_문자로변경하기" 매크로(VBA)로 핸드폰번호를 모두 text로 변경했습니다.
number를 text로 변경하기에 성공했습니다.
그런데 문제가 있습니다.
일반적으로 핸드폰번호를 기록하는 방법은 000-0000-0000이기에 단순히 text 앞에 0를 붙여 00000000000으로 표시하게 되면, 타 핸드폰번호화 비교할 수 없기 때문입니다.
즉, 00000000000인 text를 000-0000-0000으로 바꿔줘야 합니다.
위 그림과 같이 매크로 코드를 변경해 줍니다.
Cells(i, 3) = "0" & Left(Cells(i, 3), 2) & "-" & Mid(Cells(i, 3), 3, 4) & "-" & Right(Cells(i, 3), 4)
number나 text 모두 자리수 개념이 있습니다. 일반적인 핸드폰번호 자리수와 우리가 바꾸고 싶어 하는 1번 회원의 자리수는 아래 그림처럼 표시합니다.
위 도표와 매크로 코드를 보면서 설명합니다.
1번회원은 맨 앞에 숫자 0이 필요하고, 중간에 2개의 -(빼기 기호)가 필요합니다.
그래서
셀(i행, 3열) = "0" & 셀(i행, 3열)의 왼쪽에서 2글자 & "-" & 셀(i행, 3열)의 중간 3번째 부터 4글자 & "-" & 셀(i행, 3열)의 오른쪽에서 4글자를 가져오게 합니다.
약간은 복잡해 보기기는 하나, 살펴보면 단순한 로직입니다. 눈에 익혀서 사용해야 합니다.
셀에서 필요한 문자만 골라서 사용하는 방법입니다. 왼쪽 문자에서, 중간 문자에서, 오른쪽 문자에서 필요한 만큼 가져와서 사용합니다.
즉, 단순히 number을 text로 바꿔서는 핸드폰번호를 상호비교 할 수 없기에 위와 같은 방법으로 핸드폰번호를 변경해 줍니다.
그러고 나서 문자 중 특수문자를 바꿔주면 "핸드폰번호" 데이터 클리닝이 완성됩니다.
2번 회원은 "-"(빼기 기호) 대신에 "/"(나누기 기호)를 사용하고 있으며
4번 회원은 "-"(빼기 기호) 대신에 " "(공백)을 사용하고 있습니다.
이런 것들을 한 번에 변경해 주는 방법은 아주 간단합니다
Columns(3).Replace "/", "-", xlPart 명령입니다. (3번째 열. 바꿔라 "/"를 "-"로, 셀의 일부)
너무 간단합니다.
(xlPart는 셀의 일부 중 문자를 찾아 바꾸는 명령이며, xlWhole은 셀 전체가 같은 경우 바꾸는 명령입니다)
(xl은 excel의 약어로 보입니다,
참고로 빨간색으로 바꾸는 명령 vbRed에서 vb 접두어는 VBA에서 공통으로 사용하는 명령어이고, xl 접두어는 엑셀에서 사용하는 명령어 일 것으로 추정됩니다.)
매크로 "숫자_문자_구분하기한뒤에_문자로변경하기" 와 "문자중에_특수문자변환하기"를 실행하면 다음과 같이 깔끔한 핸드폰번호가 만들어집니다.
이렇게 해서, 핸드폰번호 클니링을 마칩니다.
이번에 배운 것 중 셀 속에 있는 문자 위치를 가지고, 왼쪽, 중간, 오른쪽에서 필요한 개수만큼 불러오는 명령어 left, mid, right를 익혀 사용한다면 문자는 완전정복에 가깝습니다.
왕초보용_매크로(VBA)_5.xlsm 파일 다운로드
다음번에도 네이버 지식인에서 자주 나오는 사례로 설명드리겠습니다.
안녕하세요
vba전혀모르는 1인입니다~
위에 파일 내용을 보면 20년동호회 전화번호 와 19년 시트 동호회 전화번호가 있듯이
중복을 찾아야하는 시트가 5개가 있습니다.
동시에 중복값 찾는 VBA수식이 어떻게 될까요?