왕 초보용 엑셀 매크로(VBA) 배우기 - 13 (문자 + 숫자 정렬)

---

알면 너무 편리한 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원화하여 구분하는 방법이었으나, 이와 같은 방식으로 응용할 수 있는 분야는 무궁무진합니다.

​

한글과 기타 외국어(영어, 중국어, 일본어 등)를 구분할 수 있으며, 괄호 속에 들어있는 문자를 뽑아낼 수 있습니다.

만약에 이번에 배운 문자와 숫자를 구분하는 방법을 충분히 익힌 경우, 셀 속에 들어있는 다양한 데이터를 추출하고 합칠 수 있게 됩니다.

​

약간 어렵기는 하지만, 만약 이해만 할 수 있다면 그 가치는 매우 가치 있는 일입니다.

​

엑셀 매크로가 들어있는 파일 첨부합니다.

​

​

​숫자졍렬및필터문제점.xlsm

​