Excel VBA에서 데이터 정렬하기

Excel VBA로 데이터 정렬하기

Excel에는 리본 메뉴를 사용하여 다양한 표 형식의 데이터를 정렬할 수 있는 기능이 있습니다. 이 기능은 VBA 코드 내에서 구현 가능하며. 이 작업은 매우 간단합니다.

리본의 ‘데이터’ 탭에 있는 ‘정렬 및 필터’ 그룹에 있는 ‘정렬’ 아이콘을 클릭하면 아래와 같은 대화 상자가 표출됩니다. 먼저 대화상자에서 정렬할 데이터 범위를 선택해야 합니다.

단축키 Alt-A-S-S를 사용하여 사용자 지정 정렬을 위한 대화 상자를 표시할 수도 있습니다.

최신 버전의 Excel에서는 정렬 방법이 크게 개선되었습니다. 이전에는 정렬이 3단계로 제한되었지만 이제 필요한 만큼 많은 단계를 입력할 수 있으며, 이는 VBA 내에서도 동일하게 적용됩니다.

Excel 정렬 대화 상자에서 제공되는 모든 정렬 기능을 VBA 코드에서 사용할 수 있습니다. Excel의 정렬 함수는 VBA에서 직접 작성할 수 있는 그 어떤 것보다 간단합니다. 이 기능을 활용해 보세요.

VBA에서 정렬을 수행할 때 정렬 매개변수는 정렬 대화 상자에서도 동일하게 유지됩니다. 또한 통합 문서를 저장할 때 정렬 순서도 저장됩니다.

사용자가 동일한 범위의 표 형식 데이터를 선택하고 정렬 아이콘을 클릭하면 VBA 코드에서 입력한 모든 정렬 기준이 대화상자에 표시됩니다. 사용자가 직접 정렬을 수행하려면 기존에 작성된 모든 정렬 기준을 삭제해야 하므로 번거로울 수 있습니다.

또한 코드 내에서 정렬 기준을 변경하지 않고 기본값에 의존하는 경우 사용자가 변경한 내용이 VBA 정렬에 반영되어 예기치 않은 결과가 나올 수 있으며, 이는 디버깅하기 매우 어려울 수 있습니다.

다행히도 VBA에는 모든 정렬 기준을 다시 설정하여 사용자에게 깨끗한 정렬 대화 상자를 표시하는 Clear 메서드가 있습니다.

Worksheets("Sheet1").Sort.SortFields.Clear

정렬이 완료되기 전과 후에 VBA에서 정렬 매개 변수를 지우는 것이 좋습니다.

VBA에서 Sort 메서드 실제 사용하기

표 형식의 데이터를 Excel로 가져올 때 임의의 순서로 가져오는 경우가 많습니다. CSV(쉼표로 구분된 값) 파일에서 가져올 수도 있고 데이터베이스나 웹 페이지 링크에서 가져올 수도 있습니다. 데이터를 가져올 때마다 정해진 순서로 가져오지 않는 경우가 많습니다.

워크시트 내에서 이러한 데이터를 사용자에게 표시하는 경우, 사용자는 정렬되지 않은 방대한 양의 데이터를 보고 이해하기 어려울 수 있습니다. 사용자는 이 데이터를 정렬하여 데이터를 그룹화하거나 특정 섹션을 잘라내어 다른 애플리케이션에 붙여넣기를 원할 수 있습니다.

예를 들어, 가장 높은 급여를 받는 직원이나 가장 오래 근무한 직원을 보고 싶을 수도 있습니다.

VBA의 정렬 메서드를 사용하면 쉽게 정렬하기 위해 여러가지 옵션을 사용할 수 있습니다.

VBA를 사용한 Excel 정렬 데모를 위한 샘플 데이터

먼저 VBA 내에서 사용 가능한 모든 기능을 보여드리기 위해 워크시트에 입력할 몇 가지 샘플 데이터가 필요합니다.

이 데이터를 그림과 똑같이 워크시트(‘Sheet1’)에 복사합니다.

정렬 기준으로 사용할 수 있도록 각 데이터에 다른 셀 배경색과 글꼴 색을 사용하였습니다. 셀 및 글꼴 색상을 사용한 정렬은 이 튜토리얼의 뒷부분에서 설명합니다. 또한 E3셀에서는 부서 이름이 모두 소문자로 되어 있습니다.

셀 및 글꼴 색상으로 정렬하는 기능을 사용하지 않을 예정이시라면 위의 예시처럼 셀 색상과 글꼴 색상의 변경은 필요하지 않습니다.

VBA 정렬을 위한 매크로 기록하기

정렬을 위한 VBA 코드는 매우 복잡해 보일 수 있기 때문에 Excel에서 수동으로 정렬을 수행하고 코드가 어떻게 작동하는지 보여주기 위해 매크로를 기록해 보는것도 좋습니다.

매크로 기록 기능을 사용하면 대다수의 정렬 기준이 기본값을 사용함에도 불구하고 사용 가능한 거의 모든 매개변수를 코드로 기록하기 때문에 엄청난 양의 코드를 생성할 수 있습니다.

이러한 단점에도 불구하고 VBA Sort 코드를 작성하는 데 무엇이 필요한지 간편하게 알 수 있으며, 기록된 코드는 항상 잘 작동한다는 장점이 있습니다. 직접 작성한 코드가 제대로 작동하려면 많은 테스트와 디버깅이 필요할 수 있습니다.

VBA에서 수행한 작업의 경우 실행 취소 기능이 없으므로 정렬 코드 작성을 시작하기 전에 다른 워크시트에 데이터의 사본을 만드는 것이 좋습니다.

예를 들어, 위의 샘플 데이터에서 Employee를 기준으로 간단한 정렬을 수행하면 매크로 기록 기능을 통해 다음과 같은 코드가 생성됩니다:

Sub Macro1()

Range("A1:E6").Select

ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:E6")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

이 코드는 상당히 큰 코드 덩어리이며, 기본 값으로 설정된 매개변수들도 코드로 생성되기 때문에 많은 부분이 불필요합니다. 하지만 빠르고 간편하게 기능을 구현할 수 있는 코드가 필요한 경우 매크로 기록 기능을 통해 생성된 코드를 자신의 VBA 코드에 복사하여 사용할 수 있습니다.

코드를 더 이해하기 쉽고 우아하게 만들고 싶다면 다른 옵션을 사용할 수 있습니다.

단일 레벨 정렬을 수행하는 VBA 코드

위 예제에서 매크로를 기록할 때 수행했던 Employee만을 기준으로 샘플 데이터를 정렬하려면 코드가 매우 간단합니다:

Sub SingleLevelSort()

Worksheets("Sheet1").Sort.SortFields.Clear

Range("A1:E6").Sort Key1:=Range("A1"), Header:=xlYes

End Sub

이 코드는 오름차순 정렬과 같은 기본값을 생략하므로 매크로 기록기능을 통해 생성된 코드보다 훨씬 이해하기 쉽습니다. 이 예에서는 ‘Clear’ 문을 앞쪽에서 사용합니다.

‘Clear’ 메서드는 해당 워크시트의 모든 정렬 기준을 기본값으로 다시 설정되도록 하는 데 사용됩니다. 사용자가 이전에 정렬기준을 다른 값으로 설정했거나 이전에 VBA에서 정렬을 수행하여 정렬기준이 변경되었을 수 있습니다. 정렬할 때 기본 위치에서 시작하는 것이 중요합니다. 그렇지 않으면 잘못된 결과가 나올 수 있습니다.

Clear 메서드는 Header 매개 변수를 재설정하지 않으므로 코드에 이 매개 변수를 포함하는 것이 좋습니다. 그렇지 않으면 Excel에서 헤더 행이 있는지 여부를 추측해야합니다.

샘플 데이터에 대해 이 코드를 실행하면 다음과 같이 표시됩니다:

다단계 정렬을 수행하는 VBA 코드

코드 내에서 필요한 만큼 정렬 수준을 추가할 수 있습니다. 먼저 부서별로 정렬한 다음 시작 날짜별로 정렬하되 부서는 오름차순으로, 시작 날짜는 내림차순으로 정렬하고 싶다고 가정해 보겠습니다:

Sub MultiLevelSort()

Worksheets("Sheet1").Sort.SortFields.Clear

Range("A1:E6").Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlDescending

End Sub

정렬 문에 두 개의 키가 있습니다(Key1 및 Key2). Key1(Department, E열)이 먼저 정렬된 다음 첫 번째 정렬을 기준으로 Key2(Start Date, C열)가 정렬됩니다.

또한 두 개의 순서 매개 변수가 있습니다. Order1은 Key1(Department)과 연결되고 Order2는 Key2(Start Date)와 연결됩니다. Key와 Order가 서로 일치하도록 확인하는 것이 중요합니다.

샘플 데이터에 대해 이 코드를 실행하면 다음과 같이 표시됩니다:

Department열(E)은 오름차순으로, Start Date 열(C)은 내림차순으로 정렬되어 있습니다.

이 정렬의 효과는 Jane Halfacre(3행)와 John Sutherland(4행)를 볼 때 가장 두드러집니다. 둘 다 Finance(재무)에 속하지만 Jane Halfacre가 John Sutherland보다 Start Date 늦습니다. Start Date는 내림차순으로 표시됩니다.

데이터의 범위를 특정할 수 없는 경우 UsedRange 객체를 사용하여 정렬 범위를 정의할 수 있습니다. 이 방법은 워크시트에 표 형식 데이터만 있는 경우에만 작동하며, 데이터 외부의 값은 행 및 열에 대해 잘못된 결과를 제공할 수 있으므로 주의해야 합니다.

Sub MultiLevelSort()

Worksheets("Sheet1").Sort.SortFields.Clear
Worksheets("Sheet1").UsedRange.Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlDescending

End Sub

이렇게 하면 정렬 범위를 정의하는 데 있어서  ‘End(xlDown)’ 메서드를 사용하는 경우 발생할 수 있는 문제를 방지할 수 있습니다. ‘End(xlDown)’ 메서드를 사용하면 데이터 중간에 빈 셀이 있는 경우 빈 셀 이후의 데이터는 포함되지 않지만, UsedRange는 워크시트에서 마지막 활성 셀까지 내려갑니다.

셀 색상으로 정렬하기

Excel 2007부터 셀의 색상을 기준으로 정렬할 수 있으므로 VBA에서 정렬 코드를 사용할 때 자유도 높게 사용할 수 있습니다.

Sub SingleLevelSortByCellColor()

Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _
    SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A2:E6")
    .Apply
End With

End Sub

이 코드는 셀 색상을 기준으로 샘플 데이터 범위(A2:A6)를 정렬합니다. ‘xlSortOnCellColor’로 설정된  ‘SortOn’이라는 추가 매개 변수가 있다는 점에 주목하세요.

‘SortOn’ 매개 변수는 워크시트 객체에서만 사용할 수 있으며 Range 객체에서는 사용할 수 없습니다.

이 때문에 셀 값을 기준으로 사용하는 정렬보다 코드가 더 복잡합니다.

이 코드는 전체 데이터 범위를 포괄하는 정렬에 대한 키 값을 사용합니다. 셀 색상 정렬 기준의 키로 개별 열을 지정하고 앞서 설명드렸던 것처럼 여러 단계를 사용할 수도 있습니다.

코드를 실행한 결과는 다음과 같습니다:

글꼴 색상별로 정렬하기

Excel VBA의 정렬 기능을 사용하면 글꼴 색상별로 정렬할 수 있습니다:

Sub SingleLevelSortByFontColor()

Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add(Range("A2:A6"), _
    xlSortOnFontColor, xlAscending, xlSortNormal).SortOnValue.Color = RGB(0, 0, 0)

With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange Range("A1:E6")
    .Header = xlYes
    .Orientation = xlTopToBottom
    .Apply
End With

End Sub

글꼴 색상을 기준으로 정렬하는 코드는 셀색상을 기준으로 정렬하는 코드보다 훨씬 더 복잡합니다. 이제 ‘SortOn’ 매개변수에 ‘xlSortOnFontColor’ 값이 들어갑니다.

방향을 ‘xlTopToBottom’으로 지정해야 하며 정렬 기준 색상을 지정해야 합니다. 색상은 RGB(빨강, 초록, 검정)로 지정하며 값은 0에서 255까지입니다.

코드를 실행한 결과는 아래와 같습니다.

VBA에서 색상을 사용하여 정렬하는 것은 다단계 정렬보다 훨씬 복잡하지만, 정렬 코드가 작동하지 않는 경우(매개 변수가 누락되었거나 코드를 잘못 입력한 경우 발생할 수 있음) 매크로 기록 기능을 사용하여 기록된 코드를 기존 코드에 활용하는 방법으로 돌아갈 수 있습니다.

VBA를 활용한 정렬에서 매개변수 사용하기

VBA 코드에서 정렬을 사용자 지정하는 데 사용할 수 있는 여러 가지 매개변수(선택적으로 사용할 수 있는)가 있습니다.

SortOn

SortOn은 정렬에 셀 값, 셀 배경색 또는 셀 글꼴 색상을 사용할지 여부를 선택합니다. 기본 설정은 셀 값입니다.

SortOn = xlSortOnValues

Order

순서는 정렬을 오름차순 또는 내림차순으로 수행할지 여부를 선택합니다. 기본값은 오름차순입니다.

Order = xlAscending

DataOption

데이터 옵션은 텍스트와 숫자를 정렬하는 방법을 선택합니다. xlSortNormal 매개 변수는 숫자 데이터와 텍스트 데이터를 개별적으로 정렬합니다. xlSortTextAsNumbers 매개 변수는 정렬을 위해 텍스트를 숫자 데이터로 처리합니다. 기본값은 xlSortNormal입니다.

DataOption = xlSortNormal

Header

Header는 표 형식 데이터 범위에 헤더 행이 있는지 여부를 선택합니다. 헤더 행이 있으면 정렬에 포함하지 않습니다.

매개변수 값은 xlYes, xlNo 및 xlYesNoGuess입니다. xlYesNoGuess는 머리글 행이 있는지 여부를 Excel에 맡기므로 일관되지 않은 결과가 쉽게 발생할 수 있습니다. 이 값은 사용하지 않는 것이 좋습니다.

기본값은 XNo(데이터 내에 머리글 행이 없음)입니다. 가져온 데이터에는 일반적으로 헤더 행이 있으므로 이 매개 변수를 xlYes로 설정해야 합니다.

Header = xlYes

MatchCase

이 매개 변수는 정렬이 대/소문자를 구분할지 여부를 결정합니다. 옵션 값은 True 또는 False입니다. 값이 False인 경우 소문자 값은 대문자 값과 동일하게 간주됩니다. 값이 True이면 정렬 내에서 대문자 값과 소문자 값의 차이를 정렬에 표시합니다. 기본값은 False입니다.

MatchCase = False

Orientation

이 매개 변수는 정렬을 행을 통해 아래쪽으로 수행할지, 아니면 모든 열을 가로질러 수행할지를 결정합니다. 기본값은 xlTopToBottom(행을 통해 정렬)입니다. 가로로 정렬하려면 xlLeftToRight를 사용할 수 있습니다. 이 매개변수에는 xlRows 및 xlColumn과 같은 값은 사용할 수 없습니다.

Orientation = xlTopToBottom

SortMethod

이 매개 변수는 중국어 정렬에만 사용됩니다. xlPinYin과 xlStroke의 두 가지 값이 있으며, 기본값은 xlPinYin입니다.

xlPinYin은 문자에 대한 중국어 표음 정렬 순서를 사용하여 정렬합니다. xlStroke는 각 문자의 획 수에 따라 정렬합니다.

정렬 매크로를 기록하면 이 매개변수가 항상 코드에 포함되므로 이 매개변수가 무슨 의미인지 궁금했을 것입니다. 그러나 중국어로 된 데이터를 다루지 않는 한 거의 필요 없습니다.

SortMethod = xlPinYin

더블 클릭 이벤트를 사용하여 표 형식 데이터 정렬하기

Microsoft가 제공한 VBA의 Sort 메서드의 모든 기능에는 열 머리글을 두 번 클릭하여 특정 열을 기준으로 데이터를 정렬하는 간단한 수단이 포함되어 있지 않았습니다.
In all the functionality that Microsoft included in the sort methods for VBA, it did not include a simple means to double-click on a column header and sort the whole of the tabular data based on that particular column.

이 기능은 매우 유용한 기능이며, 이를 위한 코드를 작성하는 것도 쉽습니다.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'데이터가 A1 셀에서 시작한다고 가정합니다.

'선택한 열과 데이터의 마지막 열 과 마지막 행을 저장하기 위해 세 개의 변수를 만듭니다.
Dim Col As Integer, RCol As Long, RRow As Long

'사용자가 헤더 행(1행)을 두 번 클릭했는지 확인합니다. 그렇지 않으면 프로시저를 종료합니다.
If Target.Row <> 1 Then Exit Sub

'UsedRange' 객체를 사용하여 데이터 범위의 마지막 행을 저장합니다.
RCol = ActiveSheet.UsedRange.Columns.Count

'UsedRange' 객체를 사용하여 데이터 범위의 마지막 열을 저장합니다.
RRow = ActiveSheet.UsedRange.Rows.Count

'사용자가 데이터 범위를 벗어난 열을 두 번 클릭하지 않았는지 확인합니다.
If Target.Column > RCol Then Exit Sub

'사용자가 두 번 클릭한 열을 저장합니다.
Col = Target.Column

'이전 정렬 매개 변수를 삭제합니다.
ActiveSheet.Sort.SortFields.Clear

'UsedRange' 객체의 최대 행과 열로 정의된 범위를 정렬합니다.
'사용자가 두 번 클릭한 열을 정렬 기준의 키로 사용하여 데이터를 정렬합니다.
ActiveSheet.Range(Cells(1, 1), Cells(RRow, RCol)).Sort Key1:=Cells(1, Col), Header:=xlYes

'A1셀 선택 - 정렬이 완료된 후 사용자가 편집 모드에 남아 있지 않도록 하기 위한 것입니다.
ActiveSheet.Range("A1").Select

End Sub

이 코드는 표 형식 데이터가 포함된 시트의 더블 클릭 이벤트에 배치해야 합니다. 프로젝트 탐색기 창(VBE 화면의 왼쪽 상단 모서리)에서 워크시트 이름을 클릭한 다음 코드 창의 첫 번째 드롭다운에서 ‘워크시트’를 선택하면 됩니다. 두 번째 드롭다운에서 ‘BeforeDoubleClick’을 선택한 후 생성되는 프로시저내에 코드를 입력하면 됩니다.

코드 끝에 있는 A1 셀로 커서를 이동하는 것을 제외하고는 이름, 범위 또는 셀 참조가 하드코딩되어 있지 않습니다. 이 코드는 사용자가 두 번 클릭한 셀 좌표와 표 형식 데이터 범위의 크기에서 필요한 모든 정보를 가져오도록 설계되었습니다.

데이터 범위의 크기는 중요하지 않습니다. 코드는 필요한 모든 정보를 가져오며, 값을 하드코딩할 필요 없이 통합 문서 내의 모든데이터를 사용할 수 있습니다.

데이터에 헤더 행이 있고 데이터 범위가 A1 셀에서 시작한다는 규칙만 지키면 됩니다. 변경이 필요하다면 데이터 범위의 시작 위치는 코드 내에서 쉽게 변경할 수 있습니다.

모든 사용자들이 이 새로운 정렬 기능에 만족할 것입니다!

VBA를 사용하여 정렬 기능 확장하기

Microsoft는 다양한 매개 변수를 사용하여 정렬에 다양한 유연성을 부여했습니다. VBA에서는 이 기능을 더욱 확장할 수 있습니다.

데이터 상단에 굵은 글꼴로 값을 정렬하고 싶다고 가정해 보겠습니다. Excel에서는 이 작업을 수행할 수 있는 방법이 없지만 VBA 코드를 작성하여 수행할 수 있습니다:

Sub SortByBold()

'데이터의 행과 열 수를 저장할 변수를 만듭니다.
Dim RRow As Long, RCol As Long, N As Long

'사용자가 무슨 일이 일어나고 있는지 볼 수 없도록 화면 업데이트를 끕니다.
'값이 변경되는 것을 보고 불필요한 오해를 갖게 할 수 있기 때문입니다.
Application.ScreenUpdating = False

'데이터 범위의 마지막 열 번호를 저장합니다.
RCol = ActiveSheet.UsedRange.Columns.Count

'데이터 범위의 마지막 행 번호를 저장합니다.
RRow = ActiveSheet.UsedRange.Rows.Count

'헤더 행(첫번째 행)을 제외하고 데이터 범위의 모든 행을 반복합니다.
For N = 2 To RRow
    '셀에 굵은 글꼴이 있는 경우 셀 값의 앞부분에 0을 입력합니다.
    If ActiveSheet.Cells(N, 1).Font.Bold = True Then
        ActiveSheet.Cells(N, 1).Value = "0" & ActiveSheet.Cells(N, 1).Value
    End If
Next N

'이전 정렬 매개 변수 지우기
ActiveSheet.Sort.SortFields.Clear

'데이터 범위를 정렬합니다. 선행 값이 0인 모든 값이 맨 위로 이동합니다.
ActiveSheet.Range(Cells(1, 1), Cells(RRow, RCol)).Sort Key1:=Cells(1, 1), Header:=xlYes

'헤더 행(첫번째 행)을 제외하고 데이터 범위의 모든 행을 반복합니다.
For N = 2 To RRow
    '셀에 굵은 글꼴이 있는 경우 셀 값의 앞부분에 0을 제거하여 원래 값으로 복원합니다.
    If ActiveSheet.Cells(N, 1).Font.Bold = True Then
        ActiveSheet.Cells(N, 1).Value = Mid(ActiveSheet.Cells(N, 1).Value, 2)
    End If
Next N

'화면 업데이트 다시 켜기
Application.ScreenUpdating = True

End Sub

이 코드는 ‘UsedRange’ 객체를 사용하여 데이터 범위의 크기를 계산한 다음 그 안의 모든 행을 반복합니다. 굵은 글꼴이 발견되면 셀 값 앞에 0을 입력합니다.

그런 다음 정렬이 수행됩니다. 정렬은 오름차순이므로 앞에 0이 있는 모든 항목이 목록의 맨 위로 이동합니다.

그런 다음 코드가 모든 행을 반복하여 셀 값의 앞부분에 0을 제거하여 데이터를 원래 값으로 복원합니다.

이 코드는 굵은 글꼴을 기준으로 정렬하지만 이탤릭체 글꼴, 텍스트의 포인트 크기, 밑줄 글꼴, 글꼴 이름 등 다른 셀 특성도 같은 방식으로 쉽게 활용할 수 있습니다.