엑셀(EXCEL) 똑같은 항목의 합계 요약 목록 테이블 만들기. 함수 이용하는 (UNIQUE+SUMIF) 것과 VBA 코드로 만드는 방법 소스(예제파일 포함)

목차

1. 함수 사용하기 (UNIQUE + SUMIF)

2. VBA로 목록 만들기

 

 

여러가지 분류로 표를 만들고 요약을 할 경우가 많습니다.

요약을 할때는 부분합을 구하는 방식으로 구하는 경우가 많은 오늘 그런 경우를 해결해주는 방법을 알아 보겠습니다.

아래 예시에 분류별로, 혹은 치수별로 총 수량을 구하는 방법 2가지를 소개하겠습니다.

 

오늘의 예시입니다.

 

 

 

1. 함수 사용하기 (UNIQUE + SUMIF)

 

마이크로 오피스 365 이상의 버전에서는 배열을 처리하는 기능이 강화되었습니다.

그중 하나가 UNIQUE 함수합니다. 이 함수는 목록을 입력받아서 중복된 값을 지우고 고유값 목록을 만들어 줍니다.

이걸 SUMIF와 결합하면 쉽고 간단하게 목록을 만들 수 있습니다.

 

 

UNIQUE(범위) : 범위내에서 목록을 반환합니다. 오피스365 버전 이후에는 바로 목록을 배열하는 기능이 지원됩니다.

SUMIF(범위,조건,더할 값) : "범위"가 "조건"하고 맞을 때 "더할 값"에 값들을 찾아서 더합니다.

 

먼저 UNIQUE 함수가 동작하는 방식을 알아보겠습니다.

백문이 불여일견이니 아래를 보겠습니다. UNIQUE 함수를 사용하자마자 목록이 생겼습니다.

 

UNIQUE 함수 사용하기

 

 

이제 여기에 SUMIF만 적용하면 됩니다.

함수내용은 아래와 같습니다.

=SUMIF($A$2:$A$55,H2,$D$2:$D$55)

• $A$2:$A$55 : 범위가 됩니다.
• H2 : 조건이 됩니다. 분류에서 같은 값을 찾은 조건입니다.
• $D$2:$D$55 : 더할 값들입니다. 스마하트하게 범위에서 데이터를 찾으면 같은 열이나 행에서 찾아줍니다.

 

SUMIF 적용하기

 

 

 

2. VBA로 목록 만들기

 

위에 함수들은 일정이상 엑셀에 숙련된 분이라면 빠르게 적용이 가능할 것입니다.

하지만 UNIQUE 함수가 오피스 365 이상에만 적용되는 점 때문에 사용이 어려울 수도 있을 것입니다.

그리고 VBA로 짠 문서가 사용이 편할 수도 있을 것 같아서 코드를 소개합니다.

우선 파일을 바로 공유합니다. 제가 쓰던 코드라서 가볍게 사용 가능 하셨으면 좋겠습니다.

 

VBA 예제.xlsm

0.63MB

 

 

소스코드 및 사용법은 아래와 같습니다.

 

Sub SummarizeData()
    Dim wsSource As Worksheet
    Dim wsTarget As Worksheet
    Dim criteriaCol As Long
    Dim valueCol As Long
    Dim startCell As Range
    Dim lastRow As Long
    Dim dict As Object
    Dim i As Long
    Dim category As Variant
    Dim value As Variant
    
    ' ① 워크시 기본 정보 입력하기
    Set wsTarget = ThisWorkbook.Sheets("실행")
    On Error Resume Next
    Set wsSource = ThisWorkbook.Sheets(wsTarget.Range("D3").value)
    On Error GoTo 0
    Set startCell = wsTarget.Range("F8")
    
    ' ② 먼저 입력 했던 값 선택하고 지우기
    Worksheets("실행").Range(startCell.Address).CurrentRegion.Select
    Selection.ClearContents
    
    ' 시트가 없으면 없다고 알려주고 끝내기
    If wsSource Is Nothing Then
        MsgBox "대상 시트가 존재하지 않습니다.", vbExclamation
        Exit Sub
    End If
    
    ' ③ 입력한 열의 이름을 열 번호로 변경하기
    criteriaCol = wsSource.Range(wsTarget.Range("D4").value & "1").Column
    valueCol = wsSource.Range(wsTarget.Range("D5").value & "1").Column

    
    ' ④ "기준"이 되는 열에서 마지막 칸 입력하기
    lastRow = wsSource.Cells(wsSource.Rows.Count, criteriaCol).End(xlUp).Row
    
    ' ⑤ 폴더를 초기화합니다. 참조에서 Microsoft Scripting Runtime를 활성화 시킵니다.
    Set dict = CreateObject("Scripting.Dictionary")
    
    ' ⑥ dict에 값이 없으면 폴더를 추가하고, 있으면 숫자의 값을 불러와서 더합니다.
    For i = 1 To lastRow
        category = Trim(wsSource.Cells(i, criteriaCol).value)
        value = wsSource.Cells(i, valueCol).value
        
        If category <> "" And IsNumeric(value) Then
            If dict.exists(category) Then
                dict(category) = dict(category) + CDbl(value)
            Else
                dict.Add category, CDbl(value)
            End If
        End If
    Next i
    
    ' ⑦ 결과 출력를 출력합니다. 참조가 추가되지 않으면 여기서 에러가 납니다.
    i = 0
    For Each category In dict.Keys
        startCell.Offset(i, 0).value = category ' 분류
        startCell.Offset(i, 1).value = dict(category) ' 합계
        i = i + 1
    Next category
    
    MsgBox "데이터를 정리하였습니다" & Chr(10) & "https://toast-story.tistory.com", vbInformation
    
End Sub

 

 

실행하는 방법

 

우선 실행하는 방법은 쉽습니다.

이 워크시트에 정리할 시트를 복사해서 붙여 넣고, 데이터만 있는 상태로 만듭니다.

그리고 시트이름 / 정리할 열 / 숫자가 들어있는 열을 각각 입력하고 옆에 회색의 실행버튼을 누릅니다.

그럼 아래와 결과가 정리됩니다.

결과를 복사해서 필요한 곳에 가져다 쓰시면 됩니다.