엑셀 2차원 배열 정렬 :: Sort2DArray 함수 사용법 및 예제

엑셀 2차원 배열을 지정한 열(또는 행)에 맞춰 내림차순/오름차순으로 정렬하는 Sort2DArray 함수의 사용법 및 명령문과 실전 예제를 살펴봅니다.

작성자 :
오빠두엑셀
최종 수정일 : 2021. 12. 26. 10:00
URL 복사
메모 남기기 : (10)
by

엑셀 2차원 배열 정렬 :: Sort2DArray 명령문 사용법 총정리

엑셀 Sort2DArray 명령문 목차 바로가기
  1. 예제파일 다운로드
  2. 명령문 상세 설명
  3. Sort2DArray 명령문 전체 코드
  4. 명령문 실전 사용예제
요약

엑셀 Sort2DArray 함수는 2차원 배열을 지정한 열(또는 행)에 맞춰 내림차순/오름차순으로 정렬하는 VBA 사용자 지정 함수입니다.

명령문 구문
Array = Sort2DArray ( 배열, 순번, [정렬방향], [가로방향정렬], [정렬시작지점], [정렬종료지점], [정렬한계값] )
사용된 인수 및 변수 알아보기
인수 설명
배열
[Range]		 정렬할 2차원 배열입니다.
순번
[Long]		 배열을 정렬할 기준 열번호입니다. 만약 배열의 열번호가 0부터 시작할 경우에는 0부터 증가하는 순번을, 1부터 시작할 경우에는 1부터 증가하는 순번을 입력합니다.
정렬방향
[Boolean, 선택인수]		 1이면 오름차순 정렬합니다. 기본값은 -1 (=내림차순 정렬)입니다.
가로방향정렬
[Boolean, 선택인수]		 TRUE일 경우 가로방향으로 정렬합니다. 기본값은 FALSE 입니다.
정렬시작지점
[Long, 선택인수]		 정렬을 시작할 지점입니다. 기본값은 배열의 시작지점입니다.
정렬종료지점
[Long, 선택인수]		 정렬을 종료할 지점입니다. 기본값은 배열의 마지막 지점입니다.
정렬한계값
[Long, 선택인수]		 QuickSort와 InsertionSort를 구분할 한계점입니다. 기본값은 20입니다. (한계값은 데이터 구성에 따라 다르지만, 보통 10~20 사이의 정수가 적당합니다.)

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

상세 설명

엑셀 Sort2DArray 함수는 2차원 배열을 지정한 열이나 행 순번에 따라 내림차순/오름차순으로 정렬하는 엑셀 사용자지정 함수입니다. 만약 1차원 배열을 정렬해야 할 경우, SortArray 함수를 사용합니다.

엑셀 SortArray (1차원 배열 정렬) 함수 사용법 및 전체 명령문

Sort2DArray 함수는 정렬시 임시로 생성되는 더미 배열 개수에 따라 Quick Sort와 Insertion Sort를 자동으로 선택합니다. Quick Sort와 Insertion Sort를 구분하는 기본 한계값은 20이며, 이 값은 데이터 구성에 따라 10~20 사이의 정수가 보통 사용됩니다.

원본 배열이 잘 정렬되어 있을수록 작은 수의 한계값을 입력하면 Sort2DArray 함수가 더욱 빠르게 동작합니다. 동일한 원리로, 원본 배열이 무작위로 섞여있을 경우 큰 수의 한계값을 입력 할 수록 함수가 더욱 빠르게 동작합니다. 하지만 엑셀의 경우 입력가능한 데이터 개수가 100만개로 제한되어 있고, 100만개 데이터를 정렬시에는 한계값에 따라 처리속도에 그리 큰 차이가 나지 않으므로 대부분의 경우 기본값을 그대로 사용해도 무방합니다.

정렬의 종류와 각 정렬 방식 차이점에 대한 자세한 설명은 아래 엑셀 재고관리 프로그램 만들기 8시간 풀영상 강의 영상 중반부 내용을 참고하세요.

엑셀 VBA 필수 기능 총정리 - 엑셀 재고관리 솔루션 프로그램 만들기 (8시간 풀영상)
실전 사용 예제
  1. 2차원 배열의 첫번째 열을 기준으로 내림차순 정렬 
    Sub Test1()
 
Dim DB As Variant
Dim i As Long: Dim j As Long
ReDim DB(0 To 5, 0 To 5)
 
For i = 0 To 5
For j = 0 To 5
DB(i, j) = Rnd() * 100
Next
Next
 
DB = Sort2DArray(DB, 0)
 
End Sub
  2. 2차원 배열의 첫번째 행을 기준으로 가로방향 오름차순 정렬 
    Sub Test2()
 
Dim DB As Variant
Dim i As Long: Dim j As Long
ReDim DB(0 To 5, 0 To 5)
 
For i = 0 To 5
For j = 0 To 5
DB(i, j) = Rnd() * 100
Next
Next
 
DB = Sort2DArray(DB, 0, 1, TRUE)
 
End Sub

엑셀 2차원 배열 정렬, Sort2DArray 명령문 전체 코드

Sort2DArray 명령문 전체 코드
Function Sort2DArray(DB, ByVal Index As Long, Optional ByVal Order As Integer = -1, Optional ByVal ByColumn As Boolean = False, Optional ByVal lngStart As Long = 0, Optional ByVal lngEnd As Long = 0, Optional THRESHOLD As Long = 20)
 
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'수정 및 배포 시 출처를 반드시 명시해야 합니다.
'
'■ Sort2DArray 명령문
'■ 2차원 배열을 오름차순/내림차순 또는 내림차순으로 정렬합니다. 한계점을 설정하여 QuickSort 또는 InsertionSort로 보다 빠르게 정렬할 수 있습니다. 기본 한계점은 20입니다.
'■ 인수 설명
'_____________DB                : 정렬할 배열입니다.
'_____________Index            : DB를 정렬할 기준 순번입니다.
'_____________Order           : [선택인수] 1 이면 오름차순 정렬합니다. 기본값은 -1 (=내림차순) 정렬입니다.
'_____________ByColumn    : [선택인수] True 면 열방향(가로방향) 정렬입니다. 기본값은 FALSE 입니다.
'_____________lngStart        : [선택인수] 정렬을 시작할 시작점입니다. 기본값은 배열의 시작지점입니다.
'_____________lngEnd         : [선택인수] 정렬을 종료할 마지막점입니다. 기본값은 배열의 마지막지점입니다.
'_____________Threshold    : [선택인수] QuickSort와 InsertionSort를 구분할 한계점입니다. 기본값은 20 입니다. Threshold 는 사용되는 데이터의 구성에 따라 다르지만 대부분 10-20 사이의 정수가 사용됩니다.
'■ 반환값
'_____________정렬된 배열을 반환합니다.
'본 명령문은 아래 링크를 참조하여 작성된 명령문입니다.
'https://www.vbforums.com/showthread.php?631366-RESOLVED-Quick-Sort-2D-Array
'###############################################################
 
Dim i As Long: Dim j As Long: Dim k As Long
Dim Pivot: Dim Temp
Dim Stack(1 To 64) As Long: Dim StackPtr As Long
 
If lngStart = 0 Then
    If ByColumn = False Then lngStart = LBound(DB, 1) Else lngStart = LBound(DB, 2)
End If
If lngEnd = 0 Then
    If ByColumn = False Then lngEnd = UBound(DB, 1) Else lngEnd = UBound(DB, 2)
End If
 
'가로방향 정렬
  If ByColumn Then
    ReDim Temp(LBound(DB, 1) To UBound(DB, 1))
    Stack(StackPtr + 1) = lngStart
    Stack(StackPtr + 2) = lngEnd
    StackPtr = StackPtr + 2
    Do
      StackPtr = StackPtr - 2
      lngStart = Stack(StackPtr + 1)
      lngEnd = Stack(StackPtr + 2)
      If lngEnd - lngStart < THRESHOLD Then
        ' 비교 대상의 첫번째 값과 마지막값 차이가 20 미만일 경우 Insertion Sort
        For j = lngStart + 1 To lngEnd
          For k = LBound(DB, 1) To UBound(DB, 1)
            Temp(k) = DB(k, j)
          Next
          Pivot = DB(Index, j)
          For i = j - 1 To lngStart Step -1
            If Order >= 0 Then
              If DB(Index, i) <= Pivot Then Exit For
            Else
              If DB(Index, i) >= Pivot Then Exit For
            End If
            For k = LBound(DB) To UBound(DB)
              DB(k, i + 1) = DB(k, i)
            Next
          Next
          For k = LBound(DB) To UBound(DB)
            DB(k, i + 1) = Temp(k)
          Next
        Next
      Else
        ' 비교 대상의 첫번째 값과 마지막값 차이가 20 이상일 경우 Quick Sort
        i = lngStart: j = lngEnd
        Pivot = DB(Index, (lngStart + lngEnd) \ 2)
        Do
          If Order >= 0 Then
            Do While (DB(Index, i) < Pivot): i = i + 1: Loop
            Do While (DB(Index, j) > Pivot): j = j - 1: Loop
          Else
            Do While (DB(Index, i) > Pivot): i = i + 1: Loop
            Do While (DB(Index, j) < Pivot): j = j - 1: Loop
          End If
          If i <= j Then
            If i < j Then
              For k = LBound(DB) To UBound(DB)
                Temp(k) = DB(k, i)
                DB(k, i) = DB(k, j)
                DB(k, j) = Temp(k)
              Next
            End If
            i = i + 1: j = j - 1
          End If
        Loop Until i > j
        If (lngStart < j) Then
          Stack(StackPtr + 1) = lngStart
          Stack(StackPtr + 2) = j
          StackPtr = StackPtr + 2
        End If
        If (i < lngEnd) Then
          Stack(StackPtr + 1) = i
          Stack(StackPtr + 2) = lngEnd
          StackPtr = StackPtr + 2
        End If
      End If
    Loop Until StackPtr = 0
'세로방향 정렬
Else
    ReDim Temp(LBound(DB, 2) To UBound(DB, 2))
        ' Stack 설정
        Stack(StackPtr + 1) = lngStart
        Stack(StackPtr + 2) = lngEnd
        StackPtr = StackPtr + 2
            Do
                StackPtr = StackPtr - 2
                lngStart = Stack(StackPtr + 1)
                lngEnd = Stack(StackPtr + 2)
                    ' 비교 대상의 첫번째 값과 마지막값 차이가 20 미만일 경우 Insertion Sort
                    If lngEnd - lngStart < THRESHOLD Then
                          For j = lngStart + 1 To lngEnd
                            For k = LBound(DB, 2) To UBound(DB, 2)
                              Temp(k) = DB(j, k)
                            Next
                            Pivot = DB(j, Index)
                            For i = j - 1 To lngStart Step -1
                              If Order >= 0 Then
                                If DB(i, Index) <= Pivot Then Exit For
                              Else
                                If DB(i, Index) >= Pivot Then Exit For
                              End If
                              For k = LBound(DB, 2) To UBound(DB, 2)
                                DB(i + 1, k) = DB(i, k)
                              Next
                            Next
                            For k = LBound(DB, 2) To UBound(DB, 2)
                              DB(i + 1, k) = Temp(k)
                            Next
                          Next
                Else
                    ' 비교 대상의 첫번째 값과 마지막값 차이가 20 이상일 경우 Quick Sort
                    i = lngStart: j = lngEnd
                    Pivot = DB((lngStart + lngEnd) \ 2, Index)
                        Do
                            If Order >= 0 Then
                                Do While (DB(i, Index) < Pivot): i = i + 1: Loop
                                Do While (DB(j, Index) > Pivot): j = j - 1: Loop
                            Else
                                Do While (DB(i, Index) > Pivot): i = i + 1: Loop
                                Do While (DB(j, Index) < Pivot): j = j - 1: Loop
                            End If
                            If i <= j Then
                                  If i < j Then
                                        For k = LBound(DB, 2) To UBound(DB, 2)
                                            Temp(k) = DB(i, k)
                                            DB(i, k) = DB(j, k)
                                            DB(j, k) = Temp(k)
                                        Next
                                  End If
                                    i = i + 1: j = j - 1
                            End If
                        Loop Until i > j
                            If (lngStart < j) Then
                                  Stack(StackPtr + 1) = lngStart
                                  Stack(StackPtr + 2) = j
                                  StackPtr = StackPtr + 2
                            End If
                            If (i < lngEnd) Then
                                  Stack(StackPtr + 1) = i
                                  Stack(StackPtr + 2) = lngEnd
                                  StackPtr = StackPtr + 2
                            End If
                End If
            Loop Until StackPtr = 0
End If
 
Sort2DArray = DB
 
End Function
 
Sub ArrayToRng(startRng As Range, Arr As Variant, Optional ColumnNo As String = "")
 
'###############################################################
'오빠두엑셀 VBA 사용자지정함수 (https://www.oppadu.com)
'▶ ArrayToRng 함수
'▶ 배열을 범위 위로 반환합니다.
'▶ 인수 설명
'_____________startRng      : 배열을 반환할 기준 범위(셀) 입니다.
'_____________Arr               : 반환할 배열입니다.
'_____________ColumnNo   : [선택인수] 배열의 특정 열을 선택하여 범위로 반환합니다. 여러개 열을 반환할 경우 열 번호를 쉼표로 구분하여 입력합니다.
'                                               값으로 공란을 입력하면 열을 건너뜁니다.
'▶ 사용 예제
'Dim v As Variant
'ReDim v(0 to 1)
''v(0) = "a" : v(1) = "b"
'ArrayToRng Sheet1.Range("A1"), v
'▶ 사용된 보조 명령문
'Extract_Column 함수
'##############################################################
 
On Error GoTo SingleDimension:
 
Dim Cols As Variant: Dim Col As Variant
Dim X As Long: X = 1
If ColumnNo = "" Then
    startRng.Cells(1, 1).Resize(UBound(Arr, 1) - LBound(Arr, 1) + 1, UBound(Arr, 2) - LBound(Arr, 2) + 1) = Arr
Else
    Cols = Split(ColumnNo, ",")
    For Each Col In Cols
        If Trim(Col) <> "" Then
            startRng.Cells(1, X).Resize(UBound(Arr, 1) - LBound(Arr, 1) + 1) = Extract_Column(Arr, CLng(Trim(Col)))
        End If
        X = X + 1
    Next
End If
Exit Sub
 
SingleDimension:
Dim tempArr As Variant: Dim i As Long
ReDim tempArr(LBound(Arr, 1) To UBound(Arr, 1), 1 To 1)
For i = LBound(Arr, 1) To UBound(Arr, 1)
    tempArr(i, 1) = Arr(i)
Next
startRng.Cells(1, 1).Resize(UBound(Arr, 1) - LBound(Arr, 1) + 1, 1) = tempArr
 
End Sub
 
'########################
' 배열에서 특정 열 데이터만 추출합니다.
' Array = Extract_Column(Array, 1)
'########################
 
Function Extract_Column(DB As Variant, Col As Long) As Variant
 
Dim i As Long
Dim vArr As Variant
 
ReDim vArr(LBound(DB) To UBound(DB), 1 To 1)
For i = LBound(DB) To UBound(DB)
        vArr(i, 1) = DB(i, Col)
Next
 
Extract_Column = vArr
 
End Function
5 4 투표
게시글평점
10 댓글
Inline Feedbacks
모든 댓글 보기
10
0
여러분의 생각을 댓글로 남겨주세요.x