ChatGPT로 만드는 엑셀 대시보드 실전 가이드 | 모든 버전 가능

Sub CreateDashboardLayout()
 
    Dim ws As Worksheet
    Dim shp As Shape
    Dim i As Integer
    Dim chartBoxNames(1 To 5) As String
    Dim chartTop As Double
 
    ' 시트 확인 또는 새로 생성
    On Error Resume Next
    Set ws = ThisWorkbook.Worksheets("대시보드")
    If ws Is Nothing Then
        Set ws = ThisWorkbook.Worksheets.Add
        ws.Name = "대시보드"
    End If
    On Error GoTo 0
 
    ws.Activate
    ws.Cells.Clear
 
    ' 배경색 설정
    ws.Cells.Interior.Color = RGB(&HF2, &HED, &HF3)
 
    ' 타이틀 바 추가
    Set shp = ws.Shapes.AddShape(msoShapeRectangle, 0, 0, ws.UsedRange.Width * 10 + 800, 80)
    With shp
        .Name = "TitleBar"
        .Fill.ForeColor.RGB = RGB(&H1D, &H24, &H33)
        .Line.Visible = msoFalse
        .TextFrame2.TextRange.Text = "글로벌 게임 TOP 1000 판매 현황 대시보드"
        With .TextFrame2.TextRange.Font
            .Name = "맑은 고딕"
            .Size = 20
            .Bold = msoTrue
            .Fill.ForeColor.RGB = RGB(&HFF, &HFF, &HFF)
        End With
        .Top = 0
        .Left = 20
        .Height = 80
        .Width = 900
    End With
 
    ' 차트 박스 색상 및 스타일 설정
    Dim chartFillColor As Long: chartFillColor = RGB(&HFF, &HFF, &HFF)
    Dim shadowColor As Long: shadowColor = RGB(&H1D, &H24, &H33)
 
    chartBoxNames(1) = "ChartBox1"
    chartBoxNames(2) = "ChartBox2"
    chartBoxNames(3) = "ChartBox3"
    chartBoxNames(4) = "ChartBox4"
    chartBoxNames(5) = "ChartBox5"
 
    ' 상단 2개 박스
    chartTop = 100
    For i = 1 To 2
        Set shp = ws.Shapes.AddShape(msoShapeRoundedRectangle, 20 + (i - 1) * 460, chartTop, 420, 250)
        With shp
            .Name = chartBoxNames(i)
            .Fill.ForeColor.RGB = chartFillColor
            .Fill.Transparency = 0.15
            .Line.Visible = msoFalse
            .Shadow.Type = msoShadow6
            .Shadow.Style = msoShadowStyleOuterShadow
            .Shadow.ForeColor.RGB = shadowColor
            .Shadow.Transparency = 0.85
            .Shadow.Blur = 30
            .Shadow.OffsetX = 10
            .Shadow.OffsetY = 10
        End With
    Next i
 
    ' 하단 3개 박스
    chartTop = 380
    For i = 3 To 5
        Set shp = ws.Shapes.AddShape(msoShapeRoundedRectangle, 20 + (i - 3) * 300, chartTop, 280, 220)
        With shp
            .Name = chartBoxNames(i)
            .Fill.ForeColor.RGB = chartFillColor
            .Fill.Transparency = 0.15
            .Line.Visible = msoFalse
            .Shadow.Type = msoShadow6
            .Shadow.Style = msoShadowStyleOuterShadow
            .Shadow.ForeColor.RGB = shadowColor
            .Shadow.Transparency = 0.85
            .Shadow.Blur = 30
            .Shadow.OffsetX = 10
            .Shadow.OffsetY = 10
        End With
    Next i
 
    MsgBox "대시보드 레이아웃이 성공적으로 생성되었습니다!", vbInformation
 
End Sub

Sub CreateDashboardPivots()
 
    Dim wsData As Worksheet, wsPivot As Worksheet
    Dim tbl As ListObject
    Dim pc As PivotCache
    Dim pt As PivotTable
    Dim lastCol As Long
    Dim ptRange As Range
    Dim startCol As Long
    Dim i As Integer
 
    Dim ptInfo As Variant
    Dim valueFields As Variant, fld As Variant
 
    ' 원본 데이터 및 피벗 시트 설정
    Set wsData = ThisWorkbook.Sheets("Data")
    Set tbl = wsData.ListObjects("GameData")
 
    ' 피벗 시트 준비
    On Error Resume Next
    Set wsPivot = ThisWorkbook.Sheets("피벗")
    If wsPivot Is Nothing Then
        Set wsPivot = ThisWorkbook.Sheets.Add(After:=wsData)
        wsPivot.Name = "피벗"
    Else
        wsPivot.Cells.Clear
    End If
    On Error GoTo 0
 
    ' 피벗캐시 생성
    Set pc = ThisWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=tbl.Range.Address(, , xlA1, True))
 
    ' 피벗테이블 구성 정보 배열 (이름, 행필드, 값필드 배열, 정렬방법)
    ptInfo = Array( _
        Array("TrendByYear", "발행일", Array("글로벌 판매량"), "발행일 ASC"), _
        Array("GlobalByPlatform", "플랫폼", Array("글로벌 판매량"), "글로벌 판매량 DESC"), _
        Array("RegionByPlatform", "플랫폼", Array("북미 판매량", "유럽 판매량", "일본 판매량", "기타 판매량"), "글로벌 판매량 DESC"), _
        Array("GlobalByGenre", "장르", Array("글로벌 판매량"), "글로벌 판매량 DESC"), _
        Array("Top10Publisher", "퍼블리셔", Array("글로벌 판매량"), "TOP10 DESC") _
    )
 
    startCol = 1
 
    For i = LBound(ptInfo) To UBound(ptInfo)
        Dim ptName As String: ptName = ptInfo(i)(0)
        Dim rowField As String: rowField = ptInfo(i)(1)
        valueFields = ptInfo(i)(2)
        Dim sortType As String: sortType = ptInfo(i)(3)
 
        Set ptRange = wsPivot.Cells(1, startCol)
        Set pt = wsPivot.PivotTables.Add(PivotCache:=pc, TableDestination:=ptRange, TableName:=ptName)
 
        With pt
            ' 행 필드 추가
            .PivotFields(rowField).Orientation = xlRowField
            .PivotFields(rowField).Position = 1
 
            ' 값 필드 추가
            For Each fld In valueFields
                .AddDataField .PivotFields(fld), fld & " 합계", xlSum
            Next fld
 
            ' 정렬 처리
            If sortType Like "*ASC" Then
                .PivotFields(rowField).AutoSort xlAscending, rowField
            ElseIf sortType Like "*DESC" Then
                .PivotFields(rowField).AutoSort xlDescending, Replace(sortType, " DESC", "")
            ElseIf sortType Like "TOP10*" Then
                .PivotFields(rowField).AutoSort xlDescending, Replace(sortType, "TOP10 DESC", "")
                .PivotFields(rowField).PivotFilters.Add2 _
                    Type:=xlTopCount, _
                    DataField:=.DataFields(1), _
                    Value1:=10
            End If
        End With
 
        ' 다음 피벗의 시작 위치 계산 (겹치지 않게 3열 띄우기)
        startCol = pt.TableRange2.Columns(pt.TableRange2.Columns.Count).Column + 3
    Next i
 
    MsgBox "모든 피벗테이블이 성공적으로 생성되었습니다!", vbInformation
 
End Sub

Sub CreateDashboardCharts()
 
    Dim wsDashboard As Worksheet, wsPivot As Worksheet
    Dim shp As Shape, chtObj As ChartObject
    Dim pt As PivotTable
    Dim chartTop As Double, chartLeft As Double
    Dim chartWidth As Double, chartHeight As Double
    Dim chartNames As Variant, chartTypes As Variant, ptNames As Variant
    Dim i As Integer
 
    ' 시트 설정
    Set wsDashboard = ThisWorkbook.Sheets("대시보드")
    Set wsPivot = ThisWorkbook.Sheets("피벗")
 
    ' 차트명, 종류, 피벗테이블명 배열 설정
    chartNames = Array( _
        "연도별 판매 트렌드", _
        "플랫폼별 글로벌 판매량", _
        "지역별 판매량 비교", _
        "장르별 게임 판매량", _
        "TOP 10 퍼블리셔 판매량 순위" _
    )
 
    chartTypes = Array( _
        xlLine, xlPie, xlColumnClustered, xlBarClustered, xlBarClustered _
    )
 
    ptNames = Array( _
        "TrendByYear", _
        "GlobalByPlatform", _
        "RegionByPlatform", _
        "GlobalByGenre", _
        "Top10Publisher" _
    )
 
    ' 차트 생성 반복
    For i = 0 To UBound(chartNames)
        On Error Resume Next
        Set shp = wsDashboard.Shapes("ChartBox" & (i + 1))
        On Error GoTo 0
 
        If shp Is Nothing Then
            MsgBox "ChartBox" & (i + 1) & " 도형을 찾을 수 없습니다.", vbExclamation
            Exit Sub
        End If
 
        ' 도형 위치 및 크기 계산 (도형 기준 5% 여백, 90% 크기)
        chartLeft = shp.Left + shp.Width * 0.05
        chartTop = shp.Top + shp.Height * 0.05
        chartWidth = shp.Width * 0.9
        chartHeight = shp.Height * 0.9
 
        ' 피벗테이블 참조
        Set pt = wsPivot.PivotTables(ptNames(i))
 
        ' 기존 차트 제거 (같은 위치에 다시 만들 경우)
        For Each chtObj In wsDashboard.ChartObjects
            If Not Intersect(wsDashboard.Cells(chartTop, chartLeft), chtObj.TopLeftCell) Is Nothing Then
                chtObj.Delete
            End If
        Next chtObj
 
        ' 차트 객체 생성
        Set chtObj = wsDashboard.ChartObjects.Add(Left:=chartLeft, Top:=chartTop, Width:=chartWidth, Height:=chartHeight)
        With chtObj
            .Chart.SetSourceData Source:=pt.TableRange1
            .Chart.ChartType = chartTypes(i)
            .Chart.HasTitle = True
            .Chart.ChartTitle.Text = chartNames(i)
            .Chart.ChartStyle = 2
 
            ' 공통 속성: 눈금선, 버튼, 배경 제거
            On Error Resume Next
            .Chart.Axes(xlValue).MajorGridlines.Delete
            .Chart.Axes(xlCategory).MajorGridlines.Delete
            On Error GoTo 0
            .Chart.ShowAllFieldButtons = False
            .Chart.ChartArea.Format.Fill.Visible = msoFalse
            .Chart.ChartArea.Format.Line.Visible = msoFalse
 
            ' 범례가 하나뿐이면 제거
            If .Chart.Legend Is Nothing = False Then
                If .Chart.Legend.LegendEntries.Count = 1 Then
                    .Chart.HasLegend = False
                End If
            End If
 
            ' 가로 막대형 차트일 경우 항목 역순
            If chartTypes(i) = xlBarClustered Then
                On Error Resume Next
                .Chart.Axes(xlCategory).ReversePlotOrder = True
                On Error GoTo 0
            End If
        End With
    Next i
 
    MsgBox "모든 차트가 대시보드에 성공적으로 생성되었습니다!", vbInformation
 
End Sub

Sub AddDashboardSlicers_Safe()
 
    Dim wsDashboard As Worksheet, wsPivot As Worksheet
    Dim shpSlicerBox As Shape
    Dim ptBase As PivotTable
    Dim slicerCache As SlicerCache
    Dim slicerNames As Variant, pivotFields As Variant
    Dim i As Integer
    Dim leftPos As Double, topPos As Double, slicerWidth As Double, slicerHeight As Double
    Dim sc As SlicerCache
    Dim slicerExists As Boolean
 
    ' 시트 참조
    Set wsDashboard = ThisWorkbook.Sheets("대시보드")
    Set wsPivot = ThisWorkbook.Sheets("피벗")
 
    ' SlicerBox 도형 확인
    On Error Resume Next
    Set shpSlicerBox = wsDashboard.Shapes("SlicerBox")
    On Error GoTo 0
    If shpSlicerBox Is Nothing Then
        MsgBox "'SlicerBox' 도형이 없습니다. 슬라이서를 추가할 수 없습니다.", vbExclamation
        Exit Sub
    End If
 
    ' 기준 피벗테이블
    Set ptBase = wsPivot.PivotTables(1)
 
    ' 필드와 슬라이서 이름 설정
    pivotFields = Array("플랫폼", "장르")
    slicerNames = Array("Slicer_Platform", "Slicer_Genre")
 
    ' 슬라이서 박스 기준 위치
    slicerWidth = shpSlicerBox.Width / 2 - 10
    slicerHeight = shpSlicerBox.Height - 20
    topPos = shpSlicerBox.Top + 10
 
    For i = 0 To UBound(pivotFields)
        leftPos = shpSlicerBox.Left + 10 + (i * (slicerWidth + 10))
 
        slicerExists = False
 
        ' 기존 슬라이서 캐시 존재 여부 확인
        For Each sc In ThisWorkbook.SlicerCaches
            If sc.SourceName = pivotFields(i) Then
                Set slicerCache = sc
                slicerExists = True
                Exit For
            End If
        Next sc
 
        ' 슬라이서 캐시가 없으면 생성
        If Not slicerExists Then
            Set slicerCache = ThisWorkbook.SlicerCaches.Add2(ptBase, pivotFields(i))
        End If
 
        ' 기준 슬라이서 객체가 있다면 삭제
        On Error Resume Next
        wsDashboard.Shapes(slicerNames(i)).Delete
        On Error GoTo 0
 
        ' 피벗테이블 연결
        Dim pt As PivotTable
        For Each pt In wsPivot.PivotTables
            On Error Resume Next
            slicerCache.PivotTables.AddPivotTable pt
            On Error GoTo 0
        Next pt
 
        ' 슬라이서 추가
        With slicerCache.Slicers.Add(wsDashboard, , pivotFields(i), pivotFields(i), leftPos, topPos, slicerWidth, slicerHeight)
            .Name = slicerNames(i)
        End With
    Next i
 
    MsgBox "슬라이서가 오류 없이 성공적으로 추가되었습니다!", vbInformation
 
End Sub