[VBA] 매출을 정렬해보자(Feat. ArrayList, RegExp)

정렬문제는 솔직히 좀 짜증난다.. 굳이 매크로로 안해도 되기에 이게 과제로 나오면 그냥 몸이 꼬이고 그냥 건드리기가 싫어진다. 그래서 문제를 좀 더 꼬아서 만들어 보았다.

 

문제는 괄호속 매출 기준으로 내림차순 정렬을 하고 순번으로 오름차순 정렬을 하는것이다.

 

순번과 매출이 모두 숫자가 아니라 텍스트화 되어 있기 때문에 이 상태로 정렬을 하게되면 1, 10, 11, 2, 21, 29,3 .... 뭐 이런식으로 정렬이 될 것이다.

 

따라서 이러한 문제를 풀기 위해서는 매출을 뽑아내고 이것을 다시 숫자화 시켜서 정렬을 하여야 한다.

그런 다음 이 숫자화 되어 있는 매출을 순환 하면서 원본 데이터에 이 텍스트화 된 데이터에 매출이 있는지 확인 하기 위해서 다시 텍스트화 시켜서 비교하면서 배열에 담아야 한다.

풀이는 정규식과 배열 두가지로 풀어보았다.

정규식 패턴이 일치하면 Mat 값으로 들어오게되고 / Mat에서 세부 Submatches로 들어오게 된다.

정규식은 배열의 시작이 0부터 시작한다.

따라서

mat.submatches(0) = 1.

mat.submatches(1) = 매출

mat.submatches(2) = 90,000

 

또다른 방식인 배열방식을 보겠다.

배열에서  Vtemp 값의 변화를 보기 바란다.

 

나머지는 공통코드이다.

공통코드는 매출값과 원본 데이터를 가지고 있는 어레이리스트를 순환하면서 해당 매출이 포함되어 있으면 배열에 담고 어레이리스트에서 삭제하는 코드이다. 이때 삭제를 하게 되면 순환문의 숫자가 변경되었다고 에러메세지가 발생하기 때문에 On error reume next로 에러를 무시하는 코드를 동반하면 된다.

배열은 이해하기 쉬워도 정규식을 바로 이해하기는 어렵겠지만, 늘 말하듯 정규식은 많은 언어에서 사용되기 때문에 공부를 틈틈이 하는것이 중요하다.

Option Explicit

Sub 정렬_RegExp()

    Dim Al As Object: Set Al = CreateObject("system.collections.arraylist")             '= 어레이 리스트 선언( 매출 담기)
    Dim Al_sub As Object: Set Al_sub = CreateObject("system.collections.arraylist")     '= 어레이 리스트 선언( 원본 데이터 담기)
    Dim Reg As Object: Set Reg = CreateObject("vbscript.regexp")                        '= 매출 분리하기 위한 정규식 선언
    Dim Mat As Object                                                                   '= 정규식 일치하는 값 도출
    Dim rngAll As Range: Set rngAll = [b5:b34]
    Dim rngA As Range
    Dim al_key, al_s
    Dim V(), n&, cnt&, V_sub()
    
    ReDim V_sub(1 To rngAll.Rows.Count, 1 To 1)                                         '= 정렬된 값을 한방에 뿌리기 위해 담는 배열
    
    With Reg
        .Pattern = "(\d+.)(.+?)\(\s(.+?)\s\)"                                           '= 정규식( 순번 / 매출 / 매출값 )
        .Global = True
    End With
    
    For Each rngA In rngAll
         n = n + 1
        If Reg.test(rngA) Then
        
            Set Mat = Reg.Execute(rngA)
        
            For Each Mat In Mat
                With Mat
                    
                    Al.Add CLng(.submatches(2))                                        '= 매출값(문자)를 숫자로 변환하여 AL 에 추가해라
                    Al_sub.Add rngA.Value                                              '= 원본 데이터들을 모두 담아라
                    
                End With
            
            Next Mat
       
        End If
        
    Next rngA
  
    Al.Sort: Al.Reverse                                                                '= 매출을 오름차순 정렬후에 뒤집어라 / 내림차순 하는 방법
    
    For Each al_key In Al                                                              '= 매출들을 순환
        al_key = Format(al_key, "#,##0")                                               '= 매출을 다시 텍스트화 하여라
       
        On Error Resume Next
            For Each al_s In Al_sub                                                    '= 원본 데이터들을 순환
                
                If InStr(al_s, al_key) > 0 Then                                        '= 원본 데이터안에 매출이 있다면
                
                    cnt = cnt + 1                                                      '= 배열에 담기 위해 Cnt +1
                    V_sub(cnt, 1) = al_s                                               '= 출력할 배열에 원본데이터들을 담아라.
                    Al_sub.Remove al_s                                                 '= 배열에 담았으면 원본데이터를 삭제해라 / 삭제 안하면
                                                                                       '= 계속 상위에 있는 데이터가 배열에 담기게 된다.
                End If
            
            Next al_s
        On Error GoTo 0
        
    Next al_key
    
    [d5].Resize(rngAll.Rows.Count, 1) = V_sub                                          '= 배열을 출력해라

End Sub

Sub 정렬_Split()

    Dim Al As Object: Set Al = CreateObject("system.collections.arraylist")
    Dim Al_sub As Object: Set Al_sub = CreateObject("system.collections.arraylist")
    Dim rngAll As Range: Set rngAll = [b5:b34]
    Dim rngA As Range
    Dim al_key, al_s, Vtemp
    Dim cnt&, V_sub()
    
    ReDim V_sub(1 To rngAll.Rows.Count, 1 To 1)
    
    For Each rngA In rngAll
      
          Vtemp = Split(rngA, "( ")(1)                                             '= 배열에서 매출뽑아내기
          Vtemp = Split(Vtemp, " ")(0)
          Al.Add CLng(Vtemp)
          Al_sub.Add rngA.Value
        
    Next rngA
  
    Al.Sort: Al.Reverse
    
    For Each al_key In Al
    
        al_key = Format(al_key, "#,##0")
       
        On Error Resume Next
            For Each al_s In Al_sub
                
                If InStr(al_s, al_key) > 0 Then
                Stop
                    cnt = cnt + 1
                    V_sub(cnt, 1) = al_s
                    Al_sub.Remove al_s
                
                End If
            
            Next al_s
        On Error GoTo 0
        
    Next al_key
    
    [d5].Resize(rngAll.Rows.Count, 1) = V_sub

End Sub

정렬(최종).xlsm

0.02MB