지정한 행마다 합계 구하기

Step 1: 이번 시간의 질문

'지정 행마다 합계 구하기"라고?? 그것은 예전에 Excel 강좌에서 배열 수식을 사용하면 된다고 해놓고선…' 그 동안 강좌를 열심히 공부하신 분 중에는 이렇게 생각을 하시는 분이 계실 듯 합니다. 건성건성으로 따라오신 분이라면 먼 소리를 하는 줄도 모르실 것이고… ^^

이런 질문에 대해, "부분합을 사용" 하시라는 좀 무성의한(^^;) 답변을 드린 것으로 기억합니다. 그런데 가만히 보보니까 Range 오브젝트에 대해 많은 것을 생각해 보게 하는 아주 재미있는 예제가 될 것 같아서 VBA 강좌시간에 다루어 보도록 합니다.

아래의 버튼을 눌러서 오늘의 예제 파일을 실행시켜 보고 오시기 바랍니다.

잘 되지요? 코드를 볼까요? 먼저, 예제 시트를 만드는 코드입니다.

Step 2: 예제 시트 만들기 (MakeTestSheet)

Sub MakeTestSheet()
    Dim r As Integer
    Dim rngCell As Range
    Worksheets.Add after:=Sheets("Preface")
    '''Preface 시트 뒤에 새로운 워크시트를 한 장 삽입합니다.

    Set rngCell = [A1].Offset(0, 5)

    For r = 1 To 257
        Cells(r, 2) = Int(Rnd * 1000)
        '''순환문을 돌면서 임의의 숫자를 B열에 뿌려 줍니다. 여기서 257이라는 숫자는
        '''어떤 의미가 있을까요?... 아무 뜻도 없습니다. ^^ 그저 1과 자기 자신 이외의
        '''어떤 수로도 나누어지지 않는 수, 즉 임의의 솟수일 따름입니다.

    Next r
    With rngCell
        With ActiveSheet.Buttons.Add(.Left, .Top, .Width * 2, .Height)
            .OnAction = "MakeConditionalSum"
            .Caption = "Click Me!!!"
            '''버튼을 하나 만들고 프로시저를 연결합니다.

        End With
    End With
End Sub

Step 3: 지정한 행마다 합계 구하기 (MakeConditionalSum)

버튼에 연결된 MakeConditionalSum 프로시저는 다음과 같습니다.

Sub MakeConditionalSum()
    Dim shtSheet As Worksheet
    Dim lngSubTotal As Long, lngTotal As Long
    Dim i As Integer, intOffset As Integer, intNew As Integer
    Dim r As Integer, intStart As Integer
    Dim Msg As String
    Dim blnOK As Boolean
    Application.ScreenUpdating = False
    Set shtSheet = ActiveSheet
    '''필요한 변수와 타입을 정의해 준 다음, ScreenUpdating 속성을 False로 지정하여
    '''프로그램의 수행 속도가 조금이라도 빠르도록 설정합니다.

    If TypeName(Selection) <> "Range" Then
        MsgBox "유효하지 않은 범위입니다//By Exceller"
        Exit Sub
    End If
    '''셀이 아닌 다른 오브젝트 등이 선택되어 있을 경우, 에러 메시지를 나타내고…

    Do
        Msg = Space(0)
        Msg = Msg & " 몇 행마다 합계를 구할까요?" & vbCr & vbCr
        Msg = Msg & "0을 입력하면 합계 부분을 제거합니다"
        i = Val(InputBox(Msg, "2~10 사이의 숫자를 입력하세요!//By Exceller"))
        If i = 0 Then: DelConSum: Exit Sub
        '''몇 행마다 합산을 할 것인 지 기준을 사용자로부터 입력받습니다.
        '''이 때 2~10 이내의 숫자만 입력이 가능하도록 Do~Loop 문을 사용하였습니다.
        '''만약 사용자가 0을 입력할 경우, 기존의 소계 부분을 제거합니다.

    Loop While i < 2 Or i > 10

    DelConSum
    intOffset = i + 1
    r = intOffset
    With shtSheet
        Do
            If IsEmpty(.Cells(r, 2)) Then blnOK = True
            '''blnOK 변수가 True 값을 가질 때까지 Do~Loop 문을 계속 실행합니다.
            '''그러면 blnOK 변수는 언제 True 값을 가지는가? B열의 값이 공란이 나올 때까지
            '''반복문을 계속 실행하게 됩니다.
            '''만약 이 문장이 없으면 언제까지나 계속 실행되는 무한 루프문이 됩니다.

            If r = intOffset Then intStart = 1
            intNew = Application.CountA(.Range(.Cells(intStart, 2), .Cells(r - 1, 2)))
            If intNew < intOffset - 1 Then r = intStart + intNew
            '''여기서부터는 종이를 한 장 펴놓고 각 변수에 직접 숫자를 대입해 가면서
            '''살펴보시면 이해가 빠릅니다. 말로 간단하게 설명을 드리자면, 사용자가
            '''지정한 수(변수 i에 담겨져 있는 값)만큼 셀의 값을 intNew 변수에 담아둡니다.
            '''그 아래에 있는 코드는 데이터의 맨 마지막 부분에 남게 되는 값들의 처리를
            '''위한 것입니다. 즉 데이터 수가 257개인데 10개씩 Grouping을 하게되면
            '''마지막에 7개의 데이터가 남지요? 이 부분의 처리를 위한 것입니다.

            '''나머지 아래의 코드는 별로 어려운 부분이 없을 것입니다. 사용자가 지정한
            '''간격마다 빈 행을 하나씩 삽입하고 부분 합계를 넣어주는 것입니다.

            .Cells(r, 1).EntireRow.Insert
            With .Cells(r, 1)
                .Value = "구간 합계"
                .Font.ColorIndex = 3
            End With

            lngSubTotal = Application.Sum(.Range(.Cells(intStart, 2), .Cells(r - 1, 2)))
            lngTotal = lngTotal + lngSubTotal
            .Cells(r, 2) = lngSubTotal
            intStart = r + 1
            r = r + intOffset
        Loop Until blnOK

        With .Cells(Application.CountA(.Columns(2)) + 1, 2)
            .Value = lngTotal
            .Offset(0, -1) = "총 합계"
        End With
    End With
    Application.ScreenUpdating = True
    MsgBox "이제 지정하신 " & i & " 행씩 합계를 구하였습니다", , "작업 완료//By Exceller"
End Sub

필요한 변수와 타입을 정의해 준 다음, ScreenUpdating 속성을 False로 지정하여 프로그램의 수행 속도가 조금이라도 빠르도록 설정합니다. 셀이 아닌 다른 오브젝트 등이 선택되어 있을 경우에는 에러 메시지를 나타냅니다. 몇 행마다 합산을 할 것인지 기준은 사용자로부터 입력받는데, 이때 2~10 이내의 숫자만 입력이 가능하도록 Do~Loop 문을 사용하였고, 사용자가 0을 입력할 경우 기존의 소계 부분을 제거합니다.

blnOK 변수가 True 값을 가질 때까지 Do~Loop 문을 계속 실행합니다. 그러면 blnOK 변수는 언제 True 값을 가지는가? B열의 값이 공란이 나올 때까지 반복문을 계속 실행하게 됩니다. 만약 이 문장이 없으면 언제까지나 계속 실행되는 무한 루프문이 됩니다. 여기서부터는 종이를 한 장 펴놓고 각 변수에 직접 숫자를 대입해 가면서 살펴보시면 이해가 빠릅니다. 말로 간단하게 설명을 드리자면, 사용자가 지정한 수(변수 i에 담겨져 있는 값)만큼 셀의 값을 intNew 변수에 담아둡니다. 그 아래에 있는 코드는 데이터의 맨 마지막 부분에 남게 되는 값들의 처리를 위한 것입니다. 즉 데이터 수가 257개인데 10개씩 Grouping을 하게되면 마지막에 7개의 데이터가 남지요? 이 부분의 처리를 위한 것입니다. 나머지 아래의 코드는 별로 어려운 부분이 없을 것입니다. 사용자가 지정한 간격마다 빈 행을 하나씩 삽입하고 부분 합계를 넣어주는 것입니다.

Step 4: 구간 합계 제거하기 (DelConSum)

마지막으로, 이미 존재하는 구간 합계 부분을 제거하는 코드를 보도록 합니다. 몇 강좌 전에 살펴본 Find와 FindNext 메서드를 사용하였지요? Find 메서드가 무엇인지, 어떤 곳에 사용하는 것인지 헷갈리는 분은 VB0125 강좌를 다시 살펴보고 오시기 바랍니다.

Sub DelConSum()
    Dim shtSheet As Worksheet
    Dim rngFindData As Range
    Set shtSheet = ActiveSheet

    With shtSheet.Columns(1)
        Set rngFindData = .Find("합계", LookIn:=xlValues, LookAt:=xlPart)
        If Not rngFindData Is Nothing Then
            Do
                rngFindData.EntireRow.Delete
                Set rngFindData = .FindNext
            Loop While Not rngFindData Is Nothing
        End If
    End With
End Sub

오늘은 여기까지…

정리 — 구간 합계 삽입 핵심

자주 묻는 질문 (FAQ)

마치며

부분합으로도 할 수 있는 작업이지만 Range 오브젝트를 다양하게 다뤄 볼 수 있는 재미있는 예제입니다.