알면 너무 편리한 1줄짜리 매크로(VBA)
웹페이지를 엑셀에 복사하면, 엄청난 하이퍼링크가 생성됩니다.
이 경우에 엑셀 쉬트에 귀찮은 하이퍼링크가 여기저기 걸려있는데 전부 없애고 싶다면, 단 1줄이면 됩니다.
Cells.Hyperlinks.Delete
이처럼 간단한 명령어로 엑셀 쉬트에 있는 모든 하이퍼링크를 순간에 없애줍니다. 만약 매크로를 사용하지 않는다면 하이퍼링크가 있는 셀을 조심스럽게(왜냐하면, 잘못하면 하이퍼링크가 열립니다) 클릭해서, 마우스 우클릭 - 하이퍼링크 삭제라는 멍청한 행동을 반복해야 합니다.
문자 + 숫자를 정렬할 경우 숫자 순서로 정렬하고 싶지만, 엑셀에서는 문자+숫자 또는 숫자+문자로 된 셀은 전체를 문자로 인식합니다.
그래서, 위 그림 좌측과 같이 "문자1" 다음에 "문자2"가 안 오고, "문자10"이 정렬됩니다. 이유는 "문자10"이 "문자2"보다 앞선 "문자열"이기 때문입니다.
우리가 원하는 정렬 순서는 위 그림 우측과 같이 숫자순으로 정렬되게끔 하고 싶습니다. 만약 "문자+숫자" 데이터 수가 마우스로 클릭해서 우측과 같이 숫자 자리수를 동일하게 바꾼다면, 정렬은 우리가 원하는 숫자순으로 바뀌게 됩니다.
그런데, "문자+숫자" 데이터가 너무 많아서 일일이 마우스로 수정할 수 없는 경우에는 엑셀 매크로(VBA)로 간단하게 우측과 같이 "숫자" 자리수를 동일하게 만들어 줍니다.
만약 위의 그림과 같이 단순한 구조로 된 "문자+숫자"는 아주 간단한 방법이 있습니다.
① 컨트롤 + H 그리고, 모두 바꾸기(문자를 9999999999로 바꾸기)
② 정렬
③ 컨트롤 + H 그리고, 모두 바꾸기(9999999999 숫자를 원래 문자로 다시 바꾸기)
즉, 문자를 전부 특이한 숫자(9999999999)로 바꿔서, 전체를 숫자로 만들어 준 후에, 정렬을 합니다. 이때, 셀속에 있는 문자는 전부 숫자이기 때문에 정렬이 숫자순으로 됩니다. 그리고 9999999999 숫자를 문자로 바꾸는 방법입니다.
위 방법이 가장 간단하고, 효과적입니다.
하지만 엑셀 매크로 학습을 위해서 문자와 숫자를 분리하고, 숫자를 기준으로 정렬하는 법을 알아보도록 하겠습니다.
즉, 한 개의 셀에 들어있는 문자와 숫자를 구분하고, 숫자에는 동일한 자리수를 채워 넣어서, 정렬 시 숫자 순으로 정렬되게끔 하는 방법입니다.
약 16줄로 만든 엑셀 매크로(VBA)입니다. 왕초보용 엑셀 매크로(VBA)는 약간 어려워 보입니다.
매크로 설명
위 매크로는 2개의 For문으로 구성되어 있습니다. i 루핑과 j 루핑입니다.
i 루핑은 2부터 15까지이며, 15 대신에 셀 마지막을 뜻하는 명령어 MaxRow를 사용할 수 있습니다.
(만약 MaxRow를 사용하려면, MaxRow = Cells.Find("*", , , , xlByRows, xlPrevious).Row를 추가하고
15대신에 MaxRow를 써넣으면 됩니다.)
i 루핑은 줄(행)을 루핑 합니다. j 루핑은 i루핑 속에서, 셀속 문자 갯수를 세는 루핑입니다.
즉, for j = 1 to i_length 입니다. 즉, 셀속에 있는 문자를 처음(1)부터 끝(i_length)까지 루핑하라는 명령입니다. 셀속에 있는 문자 갯수를 한개 한개씩 세어 나가는 이유는 셀속에 있는 글자 속에 들어있는 숫자를 찾아내기 위해서입니다.
① i_number_ok = False
(i_number_ok라는 임의의 변수를 설정하고, 이 값은 거짓이라고 정의합니다)
② IsNumeric(Mid(Cells(i, i_column), j, 1)) = True
(만약 셀 속에 있는 글자를 앞에서부터 세어가면서 숫자가 나오면, i_number_ok를 참으로 바꿔줍니다.)
즉, 우리는 셀속에 있는 숫자가 나오는 지점을 찾아냅니다. 예를 들면 첫 번째 레코드 "문자1"의 i_length = 3입니다. 그중에 숫자가 나오는 지점, i_number_ok가 참으로 바뀌는 j = 3입니다.
③ 만약 i_number_ok = True 라면, 왼쪽문자(i_left)와 오른쪽문자(i_right)를 만들고,
오른쪽 문자의 포맷은 "00000" 형식으로 꾸며줍니다. Format(i_right, "00000")
위 매크로를 실행하면 아래와 같은 E열(5번열)과 같은 결과를 만들어 주며, 이때 정렬을 하게 되면,
C열(3번열)에 나오는 이상한 정렬이 아닌, 우리가 의도한 숫자순 정렬이 됩니다.
그런데, 우리가 원하는 셀속의 데이터가 지저분해 보여, 최초 오리지널 형식으로 다시 바꿔줘야 합니다.
즉, G열(7번째 칼럼)처럼 되돌리는 매크로를 한 개 더 만들어 줍니다.
아래 매크로(VBA)는 문자_숫자_정렬하기() 매크로와 90% 똑같습니다.
빨간색 박스로 되어있는 오른쪽(i_right)만 달라졌습니다.
CInt 명령은 Convert Integer(바꾸다, 정수)입니다. 즉 i_right를 정수 형태로 바꾸라는 명령입니다.
그 결과 G칼럼(7열)은 오리지널 데이터와 같은 형태를 가지고 있으며, 정렬 순서 또한 숫자순입니다.
이번에 배운 문자+숫자 정렬 매크로(VBA)의 목적은 정렬보다는 한 셀속에 있는 글자를 한개씩 세어나가는 방법이 중요합니다.
즉, for j 속에 들어있는 Mid(Cells(i, i_column), j, 1)이 핵심입니다.
Mid(Cells(i, i_column), j, 1)를 다시 한번 자세히 설명하자면, mid(셀(i행, i_column열), j, 1개)를 의미합니다.
만약 셀(i행, i_column열)의 값이 "문자1"이라면 j는 3번 루핑하며, j = 1은 "문", j = 2는 "자" , j = 3은 "1"입니다. 비로서 j = 3은 "1"이 되면 1은 바로 우리가 원하는 숫자값(isnumeric)이 되며 i_number_ok = true로 바뀌는 구조입니다.
이번에 배운 엑셀 매크로는 약간 어려운 수준입니다.
만약에 이 정도 수준을 이해하기만 한다면, 엑셀로 매크로(VBA)를 이용해서 못할 것이 없습니다.
이번 사례는 문자와 숫자를 단순히 2원화하여 구분하는 방법이었으나, 이와 같은 방식으로 응용할 수 있는 분야는 무궁무진합니다.
한글과 기타 외국어(영어, 중국어, 일본어 등)를 구분할 수 있으며, 괄호 속에 들어있는 문자를 뽑아낼 수 있습니다.
만약에 이번에 배운 문자와 숫자를 구분하는 방법을 충분히 익힌 경우, 셀 속에 들어있는 다양한 데이터를 추출하고 합칠 수 있게 됩니다.
약간 어렵기는 하지만, 만약 이해만 할 수 있다면 그 가치는 매우 가치 있는 일입니다.
엑셀 매크로가 들어있는 파일 첨부합니다.
올려주신 13개의 포스트,,
유용하고 보면서 연습 좀 해보겠습니다.
이와는 별개로 혹시 다음 포스트는 이제 더이상 안올리시는걸까요?
설명에 들어가기 앞서
쓰시는 서론 글이 잘 읽히고 재밌습니다..
엑셀을 공부하시면서 느꼈던 점이나 예전 시대 업무 모습도 살짝 엿볼수 있어서 좋네요...
포스팅이 기대되는데 무려 2년 전 포스팅이라 다음 포스팅은 안나올 가능성이 높지만....