[기초방] VBA 100제 #90 [ 부분합 구하기 ]

 

부분합을 구하는 문제이다. 

 

https://1stminokingdom.tistory.com/310

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

몇일전에 이렇게 부분합을 구한적이 있었다.

 

365를 도입하면서 본인이 알고 있던 모든 로직들이 변화고 있다. 

물론 365가 없다고 하면 어쩔 수 없지만...그때는 만일 의뢰를 맡긴다면 돈을 많이 받을테야..ㅋㅋㅋㅋ

 

암튼 365를 하나씩 보면서 정말 엑셀이 막강해지고 있음을 느낀다.

 

365로 함수도 그렇고 본인 vba 코드도 그러고 방식은 같다.

 

1. 필터로 영역을 이름별로 추출하고 

2. 그 추출된 영역에서 갯수와 합을 구하면 된다.

 

그럼 여기서 vba에서 FILTER함수를 어떻게 적용하면 되는지에 대한 문제가 생긴다.

 

For Each va In .Sort(.Unique(rngall.Columns(1)))    
        
    vtemp = .Sort(.Filter(rngall, .IsNumber(.Search(va, rngall.Columns(1)))), 2, -1)  

    Cells(Rows.Count, "j").End(3)(2).Resize(UBound(vtemp, 1), 3) = vtemp               
    Cells(Rows.Count, "j").End(3)(2).Resize(1, 3) = Array("TOTAL", .CountA(.Index(vtemp, , 2)), .Sum(.Index(vtemp, , 3))) 
    Cells(Rows.Count, "j").End(3)(1, 2).NumberFormatLocal = "0"                        
Next va

 

 

1. 이름 영역에서 유티크한 값을 정렬해라

 
 For Each va In .Sort(.Unique(rngall.Columns(1)))

 

 

2. 각각의 이름을 순환하면서 필터의 이름항목에서 순환되는 이름과 같은 것만 필터링하고 그값을 날짜로 내림차순 정렬해서 VTEMP에 담아라

vtemp = .Sort(.Filter(rngall, .IsNumber(.Search(va, rngall.Columns(1)))), 2, -1)

 

 

3. 필터링된 목록을 출력해라

Cells(Rows.Count, "j").End(3)(2).Resize(UBound(vtemp, 1), 3) = vtemp

 

 

4. 마지막으로 부분합을 구해라

Cells(Rows.Count, "j").End(3)(2).Resize(1, 3) = Array("TOTAL", .CountA(.Index(vtemp, , 2)), .Sum(.Index(vtemp, , 3)))

 

 

이게 코드의 전부이다.

Option Explicit

Sub 기초방90()

    Dim rngall As Range: Set rngall = [b4:d103]
    Dim vtemp, va
    
    With Application
    
        [j4:l500].ClearContents                            

        For Each va In .Sort(.Unique(rngall.Columns(1)))   
        
            vtemp = .Sort(.Filter(rngall, .IsNumber(.Search(va, rngall.Columns(1)))), 2, -1) 
            
            Cells(Rows.Count, "j").End(3)(2).Resize(UBound(vtemp, 1), 3) = vtemp             
            Cells(Rows.Count, "j").End(3)(2).Resize(1, 3) = Array("TOTAL", .CountA(.Index(vtemp, , 2)), .Sum(.Index(vtemp, , 3))) 
            Cells(Rows.Count, "j").End(3)(1, 2).NumberFormatLocal = "0"                    
        Next va
    End With

End Sub

 

 

365 함수 역시도 값은 패턴으로 코드가 진행이 된다.

=DROP(
    LET(
        _t, B4:D103,
        _n, CHOOSECOLS(_t, 1),
        _d, CHOOSECOLS(_t, 2),
        _u, SORT(UNIQUE(_n)),

        →STEP1, 정렬된 테이블,
        _output, SORTBY(_t, _n, 1, _d, -1),
        
        →STEP2, 정렬된 유니크한 이름들은 순환하기 위해 REDUCE함수에 고유 이름들을 전달,
        REDUCE("",_u,            
            LAMBDA(a, c,
                LET(
                    →STEP2_1, 넘긴값으로 람다함수에서 필터링 진행,
                    _subS, FILTER(_output, (CHOOSECOLS(_output, 1)) = c),

                    →STEP2_2, 부분갯수,
                    _Sc, COUNTA(CHOOSECOLS(_subS, 3)),

                    →STEP2_3, 부분합,
                    _SS, SUM(CHOOSECOLS(_subS, 3)),
                    VSTACK(a, _subS, HSTACK("TOTAL", TEXT(_Sc, "0"), _SS))
                )
            )
        )
    ),
    1
)

 

 

부분합을 구하는 방법은 많으나, 365 함수로 다시 구현해보는 시간을 가졌다.

 

오늘도 맛있는 코드 냠냠

기초방90.xlsm

0.03MB