[VBA] 대량 데이터에서 부분합구하기

부분합에 대한 고민이다.

23000건 정도의 데이터이니 이걸 우리가 아는 부분합으로 구한다면 셀 사이사이를 부분합이 들어가야 하기 때문에 분명히 에러가 발생할 것이다. 질문자의 고민도 그런 고민일것이다.

 

이러한 문제의 해법은 기존 시트에 Insert를 하기보단 부분합전까지 배열이나 셀에 담고 부분합을 구한 후 다시 리스트를 쌓고 다시 부분합을 구하는 방식으로 해야 엑셀에 무리가 가지 않는다.

 

부분합전까지 리스트의 갯수를 구하기 위해서는 

1. 고유값을 찾아내야 한다.

 

uni = Application.Unique(rngAll.Columns(14))

 

이 코드는 전체 영역중 14번째 열의 모든값을 배열로 담아서 고유값을 구한 후 uni 배열에 담는 구문이다.

* 유니크 함수는 버전별로 작동이 안될 수도 있으니 이런분들은 365를 구입하시거나, 아님 중복값제거를 한 후 그 값을 uni배열에 담으면 된다.

 

2. 고유값의 개수와 부분합 구하기

이렇게 구한 고유값을 순환하면서 Countif함수로 고유값의 갯수를 구하고 / 고유값들의 재고 수량을 Sumif로 구한다.

 

For Each ua In uni
    i = i + 1
    Vall(i, 1) = ua                                                 
    Vall(i, 2) = Application.CountIf(rngAll.Columns(14), ua)        
    Vall(i, 3) = Application.SumIf(rngAll.Columns(14), ua, rngAll.Columns(11))

Next ua

 

 

3. 리스트값 담기 + 부분합 담기

rngAll(rowNum,  각 열의 번호)는 각각의 아이템들을 result 배열에는 리스트 + 부분합까지 담게되는데, 리스트에는 부분합이 없기 때문에 rowNum과 coNum을 달리 카운트 하여야 한다. 

For Each Va In Vall                                              
    i = i + 1                                                       
    For j = 1 To Vall(i, 2)                                          

        rowNum = rowNum + 1: coNum = coNum + 1                       

        result(coNum, 1) = rngAll(rowNum, 1)                         
        result(coNum, 2) = rngAll(rowNum, 2)                       
        result(coNum, 3) = rngAll(rowNum, 10)
        result(coNum, 4) = rngAll(rowNum, 11)
        result(coNum, 5) = rngAll(rowNum, 14)                      

    Next j

        coNum = coNum + 1
        result(coNum, 4) = Vall(i, 3)                             
        result(coNum, 5) = result(coNum - 1, 5) & " 요약"          

If Va = "" Then Exit For                                            
                                                                  
Next Va

 

 

 

4. 출력하기

.[a2].Resize(coNum - 1, 5) = result   /  coNum -1 을 해준이유는 마지막에 요약테이블이 하나더 필요없는것이 들어오기 때문에 -1 을 하야 손절하는 것이다.

 

With Sheets("test")
                
    .Activate                                                  
    .[a1].Resize(1, 6) = Array("순서", "로케이션", "상품명", "현수량(낱개)", "부분합", "비고") 
    .[a2].Resize(coNum - 1, 5) = result                        

    Set rngX = Sheets("test").[a1].CurrentRegion              

    rngX.Borders.LineStyle = 1
    rngX.HorizontalAlignment = xlCenter

End With

 

Option Explicit
Sub 부분합()

    Dim rngAll As Range, rngA As Range
    Dim i&, j&, rowNum&, coNum&
    Dim uni, ua, Vall(1 To 65536, 1 To 3), Va
    Dim result(1 To 65536, 1 To 5)
    Dim rngX As Range
    
    Sheets("test").Cells.ClearContents                                     '= 초기화
    
    Set rngAll = Range([a2], [n2].End(4))                                  '= 영역설정
    
        uni = Application.Unique(rngAll.Columns(14))                                   '= 고유값 설정
        
        For Each ua In uni
            i = i + 1
            Vall(i, 1) = ua                                                 '= 고유값 리스트
            Vall(i, 2) = Application.CountIf(rngAll.Columns(14), ua)                   '= 고유값의 갯수
            Vall(i, 3) = Application.SumIf(rngAll.Columns(14), ua, rngAll.Columns(11)) '= 고유값의 부분합
        
        Next ua
        i = 0
        For Each Va In Vall                                                  '= 구해진 고유값 순환
            i = i + 1                                                        '= 1부터 하나씩 +1
            For j = 1 To Vall(i, 2)                                          '= 고유값의 갯수만큼 부분 반복
            
                rowNum = rowNum + 1: coNum = coNum + 1                       '= 리스트 +1 / 부분합 포함한 행의크기 +1
                
                result(coNum, 1) = rngAll(rowNum, 1)                         '= 결과값은 부분합 때문에 리스트와는 다르게
                result(coNum, 2) = rngAll(rowNum, 2)                         '= 카운팅을 해줘야 함
                result(coNum, 3) = rngAll(rowNum, 10)
                result(coNum, 4) = rngAll(rowNum, 11)
                result(coNum, 5) = rngAll(rowNum, 14)                        '= list 시트에서 [ 순서 + 로케이션 + 상품명 + 현수량 + 부분합 ]
                                                        
            Next j
        
                coNum = coNum + 1
                result(coNum, 4) = Vall(i, 3)                                '= 부분합 출력
                result(coNum, 5) = result(coNum - 1, 5) & " 요약"            '=  요약 테이블
                
        If Va = "" Then Exit For                                             '= 배열의 범위를 임으로 잡았기 때문에 이 구문이 없으면 계속 순환한다.
                                                                             '= 순환되는 배열의 값의 빈값이면 반복문을 탈출해라
        Next Va
        
            With Sheets("test")
                
                .Activate                                                    '= 테스트 시트 활성화
                .[a1].Resize(1, 6) = Array("순서", "로케이션", "상품명", "현수량(낱개)", "부분합", "비고")  '= 제목행
                .[a2].Resize(coNum - 1, 5) = result                          '= Result 배열 출력
                
                Set rngX = Sheets("test").[a1].CurrentRegion                 '= 테두리 + 가운데 정렬
               
                rngX.Borders.LineStyle = 1
                rngX.HorizontalAlignment = xlCenter
            
            End With
 
    MsgBox "부분합을 모두 완료 했습니다."
    
End Sub

 

 

그럼 다음 질문을....

대량부분합.xlsm

2.78MB