들어가기 전에
Excel 피벗 테이블은 누적된 데이터 세트에서 여러 통찰력을 일반화할 수 있는 도구 중 하나입니다. 요구 사항에 따라 피벗 테이블의 필드 설정을 조작하는 것만으로도 수많은 분석을 수행할 수 있습니다. VBA를 사용하여 피벗 테이블을 자동으로 고치고 차트 색상 요소를 관리하는 방법을 소개합니다.
예제 파일과 소스 코드는 본문 맨 아래에 있습니다.
※ 이 글은 아래 기사 내용을 토대로 작성되었습니다만, 필자의 개인 의견이나 추가 자료들이 다수 포함되어 있습니다.
- 원문: Managing Pivot Table and Excel Charts with VBA
- URL: https://towardsdatascience.com/managing-pivot-table-and-excel-charts-with-vba-3dc5d672dff0
개요: 데이터 구조
샘플 데이터는 Excel-Easy 웹사이트에서 가져왔습니다. 이곳은 기본 Excel 및 VBA 기능에 대한 초보자 친화적인 자습서가 포함된 웹사이트입니다. 이 데이터 집합에는 2016년과 2017년 특정 국가의 과일 및 채소 판매 기록이 포함되어 있습니다. 데이터 집합에는 6개의 필드가 포함되어 있습니다: 주문 ID, 제품, 카테고리, 금액, 날짜, 국가입니다. 피벗 테이블을 만들기 위해 이 데이터 집합을 results1.csv와 results2.csv라는 2개의 csv 파일로 나누었습니다.
RawData 워크시트에서 이 데이터 집합을 가져왔습니다. 다음 수식을 사용하여 raw_data_source라는 동적 범위를 만듭니다.
=OFFSET(RawData!$A$1, 0, 0, COUNTA(RawData!$A:$A), COUNTA(RawData!$1:$1))
OFFSET 함수를 사용하여 동적 범위로 만들면 데이터 크기에 따라 소스 범위가 자동으로 변경되므로 편리합니다. '엑셀 표'를 이용해도 됩니다.
피벗 테이블 및 차트
RawData 시트의 raw_data_source 범위에 있는 데이터 집합을 데이터 원본으로 사용하여 두 개의 간단한 피벗 테이블과 차트를 만듭니다.
첫 번째는 다음과 같이 필드 설정을 통해 국가별로 필터링할 수 있는 개별 제품별 총 과일 및 채소 매출을 표시했습니다.
두 번째는 국가별 과일 및 채소 총 판매량을 나타냅니다.
1. 새 데이터 집합을 기반으로 피벗 테이블 새로 고침 자동화
대상 파일에서 데이터 집합을 새로 고치는 작업을 자동화합니다. 관리 시트에 플레이스홀더를 만들어 피벗 테이블을 가져와서 새로 고치려는 데이터 집합이 있는 CSV 데이터의 경로를 배치했습니다. 아래 그림과 같이 A2 셀의 이름을 파일 경로로 지정했습니다.
이 단계의 코드는 아래에 UpdateRawData 하위 루틴에 나와 있습니다. 각각 통합 문서의 이름, 관리 시트, RawData 시트 및 데이터 집합이 포함된 파일의 경로에 대해 wb, ws_admin, ws_rawdata 및 filepath 변수를 선언했습니다.
먼저 RawData 시트의 내용을 지웠습니다. 그런 다음 파일 이름을 기준으로 원본 통합 문서로 이동하여 해당 시트를 선택하고 내용을 복사한 다음, ws_rawdata 시트로 돌아와서 내용을 값으로 붙여 넣습니다.
마지막으로 피벗 테이블과 차트가 각각 있는 시트를 포함하여 전체 통합 문서를 새로 고치는 wb.RefreshAll 코드를 사용하여 통합 문서를 새로 고칩니다.
이상의 내용을 VBA로 코딩하면 다음과 같습니다.
Sub UpdateRawData()
Dim wb As Workbook
Dim ws_admin As Worksheet
Dim ws_rawdata As Worksheet
Dim filepath As String
Set wb = ThisWorkbook
Set ws_admin = wb.Worksheets(“Admin”)
Set ws_rawdata = wb.Worksheets(“RawData”)
'Clear Rawdata sheet
ws_rawdata.Activate
Cells.Clear
'get file path
filepath = ws_admin.Range(“filepath”)
Application.DisplayAlerts = False
'Open source file and select all contents
Dim src_wb As Workbook
Dim src_ws As Worksheet
Set src_wb = Workbooks.Open(filepath)
Set src_ws = src_wb.Sheets(1)
src_ws.UsedRange.Select
'Copy all
Selection.Copy
'Paste all
ws_rawdata.Range(“A1”).PasteSpecial xlPasteValues
'Close source file
src_wb.Close SaveChanges:=False
wb.RefreshAll
Application.DisplayAlerts = True
End Sub
이 코드를 파일 및 차트 새로 고침 버튼에 연결했습니다. 플레이스홀더에 파일 이름을 지정하고 버튼을 클릭하면 데이터 집합과 피벗 테이블이 자동으로 업데이트됩니다.
2. VBA를 사용하여 색상 표 만들기
관리 시트에 데이터 집합에서 사용할 수 있는 특정 과일 및 채소 목록이 포함된 테이블을 만들었습니다. F열에 각 과일 또는 채소의 색상에 대한 잠정적인 16진수 색상 코드를 지정했습니다. 이 색상을 사용하여 피벗 차트의 색상을 업데이트합니다. 먼저 각 셀에 지정한 색상으로 F열에 색상을 지정합니다.
16진수 색상 코드
각 색상에 대한 16진수 코드는 기본이 16인 6자리 16진수(0~9 또는 A~F)입니다. 16진수 색상 코드 RRGGBB에서 각 두 글자 쌍은 빨강, 초록, 파랑의 다양한 음영을 나타냅니다. RGB(Red Green Blue) 시스템에서 각 음영의 값은 0에서 255까지의 범위입니다.
예를 들어 16진수 색상 코드 ffab23의 경우 다음과 같이 계산하여 해당 RGB 코드를 계산했습니다. 16진수 시스템에서 ffab23은 RGB 시스템에서 각각 빨간색, 녹색 및 파란색 색상 구성 요소를 나타내는 (255, 171, 35)로 변환됩니다.
Excel에서 사용자 지정 색상 옵션으로 이동하여 시각화할 수도 있습니다.
Excel VBA에서 값과 함께 &H를 사용하면 해당 값이 16진수임을 의미하며 Val() 함수는 해당 10진수를 반환합니다. 아래 직접 실행 창에서 r, g 및 b는 각각 빨강, 초록 및 파랑의 각 음영에 해당하는 10진수 값을 나타냅니다.
아래 코드에서는 각 과일 또는 채소의 16진수 색상 코드가 포함된 테이블에 대해 명명된 범위 color_code_range를 만들었습니다. 선택 영역의 각 셀을 반복하여 빨간색, 녹색, 파란색 구성 요소를 10진수로 도출한 다음, 셀 내부를 동일한 RGB 색상 코드로 지정했습니다.
Sub refresh_color_table()
Dim wb As Workbook
Dim ws_admin As Worksheet
Set wb = ThisWorkbook
Set ws_admin = wb.Sheets(“Admin”)
ws_admin.Range("color_code_range").Select
Dim c As Range
Dim r, g, b As Long
Dim Hex As String
For Each c In Selection
Hex = c.Value
r = Val("&H" & Mid(Hex, 1, 2))
g = Val("&H" & Mid(Hex, 3, 2))
b = Val("&H" & Mid(Hex, 5, 2))
c.Interior.Color = RGB(r, g, b)
Next c
End Sub
위의 코드를 실행하면 그림과 같이 F 열이 색상 코드와 같은 색으로 지정됩니다.
색상 코드가 변경되고 코드를 다시 실행하면 표에 새로운 색상이 생성됩니다.
3. VBA에서 사전으로 작업하기
다음 단계에서는 위에서 선택한 사용자 지정 색상을 기반으로 피벗 차트에 색상을 할당합니다. 이를 위해 제품 이름을 키로, 해당 16진수 색상 코드를 값으로 포함하는 사전을 만들었습니다. VBA에서 사전 개체를 만들기 위한 전제 조건은 Microsoft 스크립팅 런타임을 미리 활성화하는 것입니다. 이를 위해 [도구] - [참조] - [Microsoft Scripting Runtime] 옆의 확인란을 선택하고 [확인]을 클릭합니다.
아래 코드에서는 colorMap이라는 사전 개체를 만들었습니다. 관리 시트에서 E2:F10 범위를 반복했습니다. E열의 내용을 키로, F 열의 내용을 해당 값으로 추가했습니다.
Sub create_dict()
Dim wb As Workbook
Dim ws_admin As Worksheet
Set wb = ThisWorkbook
Set ws_admin = wb.Sheets(“Admin”)
Dim colorMap As Dictionary
Set colorMap = New Dictionary
Dim i As Integer
For i = 2 To 10
If Not colorMap.Exists(Range("E" & i).Value) Then
colorMap.Add Range("E" & i).Value, Range("F" & i).Value
End If
Next i
For Each Key In colorMap.Keys()
Debug.Print Key & ": " & colorMap(Key)
Next Key
End Sub
위의 두 번째 for 루프에서는 컬러맵 사전의 각 키를 반복하여 아래와 같이 바로 창에 키와 값을 출력했습니다.
4. VBA를 사용하여 피벗 차트 요소 관리하기
이전 단계에서 배운 내용을 바탕으로 한 단계 더 나아가 VBA를 사용하여 피벗 차트 요소를 업데이트합니다. 이 경우 셀 값을 기반으로 차트 제목을 자동으로 설정하고 관리 시트에 정의된 특정 과일과 채소의 색상을 피벗 차트에 적용합니다.
이 단계에서는 Plot1 및 Plot2 시트를 sheetNames라는 배열로 할당했습니다. 차트 오브젝트를 차트 오브젝트로 선언했습니다. 각 시트 내에서 모든 차트 오브젝트 중 각 차트 오브젝트를 반복합니다.
[참고] 차트 개체는 워크시트에 내장된 차트의 크기와 모양을 제어하는 VBA에서 차트 개체를 위한 컨테이너 역할을 합니다. ChartObjects 컬렉션의 멤버입니다. VBA에서 이러한 각 개체의 메서드와 속성의 차이점을 이해하는 것이 중요합니다.
각 차트 오브젝트를 살펴본 후 E1 셀의 값을 기준으로 각 차트에 제목을 설정했습니다. 다음으로, 차트 개체의 전체 시리즈 컬렉션에서 각 시리즈를 반복해서 살펴봤습니다. 계열 이름(예: 과일 또는 채소 이름)을 itemName이라는 변수에 할당하고 colorMap 사전에서 해당 색상 코드를 가져왔습니다. 2단계와 마찬가지로 색상 코드의 빨간색, 녹색, 파란색 구성 요소를 10진수로 가져와 계열 막대를 RGB 색상으로 채웁니다.
Sub refresh_plots()
Dim wb As Workbook
Dim ws_admin As Worksheet
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws_admin = wb.Sheets("Admin")
Dim colorMap
Set colorMap = CreateObject("Scripting.Dictionary")
Dim i As Integer
For i = 2 To 10
If Not colorMap.Exists(Range("E" & i).Value) Then
colorMap.Add Range("E" & i).Value, Range("F" & i).Value
End If
Next i
Dim sheetNames As Variant
Dim sheetName As Variant
sheetNames = Array("Plot1", "Plot2")
Dim hex_color_code As String
Dim r, g, b As Integer
Dim chartObj As ChartObject
For Each sheetName In sheetNames
Set ws = wb.Sheets(sheetName)
For Each chartObj In ws.ChartObjects
chartObj.Chart.HasTitle = True
chartObj.Chart.ChartTitle.Text = ws.Range("E1").Value
For Each Series In chartObj.Chart.SeriesCollection
itemName = Series.Name
hex_color_code = colorMap(itemName)
r = Val("&H" & Mid(hex_color_code, 1, 2))
g = Val("&H" & Mid(hex_color_code, 3, 2))
b = Val("&H" & Mid(hex_color_code, 5, 2))
Series.Format.Fill.ForeColor.RGB = RGB(r, g, b)
Next Series
Next chartObj
Next sheetName
End Sub
이 코드의 사용 예시는 아래와 같습니다.
마치며
VBA를 사용하여 피벗 테이블과 차트 작업을 사용자 지정하고 자동화하는 방법에 대해 알아보았습니다. 새로운 데이터 집합으로 피벗 테이블 및 차트 새로 고침, 16진수 색상 코드를 기반으로 색상표 만들기, VBA에서 사전으로 작업하는 방법, VBA를 사용하여 피벗 차트 요소 관리 및 업데이트 등 네 가지 주요 작업의 자동화를 시연했습니다. 두 번째 단계에서는 Excel과 VBA를 모두 사용하여 16진수 색상 코드를 해당 RGB 색상 코드로 변환하는 방법을 자세히 설명했으며, 이후 단계에서도 이 기술을 사용했습니다.
이 글에서 사용된 코드와 Excel 파일은 [여기]에서 내려받을 수 있습니다.
'VBA' 카테고리의 다른 글
| 2024년에 매크로? 챗GPT에서도 여전히 유용한 VBA! (31) | 2024.12.17 |
|---|---|
| Excel 머리글 및 바닥글을 자동화하는 방법 (4) | 2024.09.11 |
| 파워포인트에 Excel 파일을 아이콘으로 삽입하는 2가지 방법 (6) | 2024.09.08 |
| 매크로를 사용하여 Excel을 자동화하는 방법 (1) | 2024.08.29 |
| Excel 매크로를 활성화 또는 비활성화하는 방법 (2) | 2024.06.15 |
