아이엑셀러 닷컴

얼마 전 유튜브 강의에서 '조건부 색상 차트'를 만든 적이 있습니다.

조건에 따라 엑셀 차트 색상이 변한다 ㅡ Excel 조건부 색상 차트 만들기

조건부 색상 차트

점수 구간 별로 별도의 컬럼을 추가하였고(위 그림에서 빨간색 점선 부분), 이것을 토대로 차트를 작성했습니다. 차트는 데이터를 가지고 만드는 것이다 보니 그런 방식으로 진행되었습니다.

완성 예

하지만 VBA를 이용하면 그런 번거로움이 사라집니다. [완성 예]에서 볼 수 있듯 이름과 점수 열만 있을 뿐, 다른 데이터는 없음에도 점수 구간별로 색상이 구분된 차트를 만들 수 있습니다.

완성 예

뿐만 아니라 점수를 입력하는 순간 테이블이 내림차순으로 자동 정렬되고 차트에도 반영됩니다. 이벤트 프로시저를 이용하면 가능하며, 이번 강의에서는 Worksheet 개체의 Change 이벤트를 사용합니다.

이벤트 프로시저 생성

이벤트 프로시저를 작성할 시트(여기서는 '연습' 시트)의 탭을 마우스 오른쪽 버튼으로 클릭하고 [코드 보기] 메뉴를 선택합니다.

'개체' 드롭다운 버튼을 클릭하고 'Worksheet'를 선택합니다. 그 오른쪽에 있는 '프로시저' 드롭다운 버튼을 클릭하고 'Change' 이벤트를 선택하면 Worksheet_Change 이벤트 프로시저가 만들어집니다. 이곳에 코드를 작성합니다. Worksheet_SelectionChange 프로시저는 삭제해도 됩니다.

전체 코드 둘러보기

전체 코드 내용입니다. 코드에 대한 해설은 아래에서 설명하므로 전반적인 구조 중심으로 훑어보시기 바랍니다.

Private Sub Worksheet_Change(ByVal Target As Range)
    ''' ---------- (1단계) ----------
    Dim rChtSrc As Range
    Dim rChtStart As Range
    Dim shpCht As Shape
    Dim shpX As Shape
    Dim oCht As Chart
    Dim oSeries As Series
    Dim oPoint As Point
    Dim sCaption As String
    Dim vX As Variant
    Dim iX As Integer

    On Error Resume Next
    Set rChtSrc = Me.Range("B3").CurrentRegion
    Set rChtSrc = rChtSrc.Offset(1).Resize(rChtSrc.Rows.Count - 1)
    If Intersect(Target, rChtSrc) Is Nothing Then Exit Sub
    Set rChtStart = rChtSrc.Cells(rChtSrc.Columns.Count).Offset(-1, 2)
    sCaption = Me.Range("B1")

    ''' ---------- (2단계) ----------
    For Each shpX In Me.Shapes
        shpX.Delete
    Next

    Set shpCht = Me.Shapes.AddChart2
    With shpCht
        .Name = "myChart"
        .Left = rChtStart.Left
        .Top = rChtStart.Top
        .Width = rChtStart.Width * 7
        .Height = rChtStart.Height * 11
    End With

    Set oCht = shpCht.Chart
    With oCht
        .SetSourceData rChtSrc
        .HasLegend = False
        .HasTitle = True
        .ChartTitle.Caption = sCaption
        .ApplyDataLabels
        .Axes(xlValue).Delete
        .Axes(xlValue).MajorGridlines.Delete
        .ChartGroups(1).Overlap = 100
        .ChartGroups(1).GapWidth = 100
    End With

    ''' ---------- (3단계) ----------
    Set oSeries = oCht.SeriesCollection(1)
    vX = oSeries.Values

    For iX = 1 To oSeries.Points.Count
        Set oPoint = oSeries.Points(iX)
        Select Case vX(iX)
            Case Is < 60
                oPoint.Format.Fill.ForeColor.RGB = RGB(200, 0, 0)
            Case Is < 81
                oPoint.Format.Fill.ForeColor.RGB = RGB(0, 200, 0)
            Case Else
                oPoint.Format.Fill.ForeColor.RGB = RGB(0, 0, 200)
        End Select
    Next
    rChtSrc.Sort key1:=rChtSrc.Cells(2), order1:=xlDescending, Header:=xlNo
End Sub

CODE

코드 해설

1. 먼저, 등장 인물을 지정하고 배역을 맡깁니다. 전문 용어로는 '변수를 선언하고 값이나 영역을 할당^assign한다'고 합니다.

Dim rChtSrc As Range          ''' 차트 데이터 영역에 접근할 변수
Dim rChtStart As Range        ''' 차트를 작성할 시작 위치
Dim shpCht As Shapes          ''' 차트(Shape) 개체에 접근할 변수
Dim shpX As Shape             ''' 시트 내의 모든 Shape 개체에 접근할 순환 변수
Dim oCht As Chart             ''' 차트 개체에 접근할 변수
Dim oSeries As Series         ''' 차트 계열에 접근할 변수
Dim oPoint As Point           ''' 차트 계열 중 각 포인트 개체에 접근할 변수
Dim sCaption As String        ''' 차트 제목을 저장할 변수
Dim vX As Variant             ''' 차트 계열(Series) 값을 저장할 변수
Dim iX As Integer             ''' 차트 계열의 포인트에 접근할 순환 변수

On Error Resume Next          ''' 오류가 발생하더라도 일단 다음 작업 진행
Set rChtSrc = Me.Range("B3").CurrentRegion
                              ''' B3 셀 인접 영역을 rChtSrc 변수에 할당
Set rChtSrc = rChtSrc.Offset(1).Resize(rChtSrc.Rows.Count - 1)    
                              ''' 제목을 제외한 영역을 rChtSrc 변수에 다시 할당 
If Intersect(Target, rChtSrc) Is Nothing Then Exit Sub
                              ''' 선택한 셀이 rChtSrc 영역 밖이라면 프로시저 종료
Set rChtStart = rChtSrc.Cells(rChtSrc.Columns.Count).Offset(-1, 2)
                              ''' 차트를 작성할 기준 셀 지정
sCaption = Me.Range("B1")     ''' B1 셀 내용을 차트 제목으로 사용하기 위해 저장

해설

2. 이미 만들어진 차트가 있으면 지우고 새로운 차트를 생성합니다.

For Each shpX In Me.Shapes    ''' Me는 이 코드가 들어있는 현재 시트
    shpX.Delete            
    ''' 보다 정확하게 하려면 Chart 개체의 이름을 확인하고 삭제해야 하지만 
    ''' 편의상 현재 시트 내의 Shape 개체를 모두 삭제
Next

Set shpCht = Me.Shapes.AddChart2    ''' 차트 개체를 생성하여 shpCht 개체에 할당

''' 생성된 개체의 이름, 위치, 크기 지정
With shpCht
    .Name = "myChart"
    .Left = rChtStart.Left
    .Top = rChtStart.Top
    .Width = rChtStart.Width * 7
    .Height = rChtStart.Height * 11
End With

Set oCht = shpCht.Chart

''' 차트 개체의 각종 속성 지정
With oCht
    .SetSourceData rChtSrc    ''' 차트의 데이터 범위 지정
    .HasLegend = False        ''' 범례 숨김
    .HasTitle = True          ''' 차트 제목 표시
    .ChartTitle.Caption = sCaption
    .ApplyDataLabels          ''' 데이터 레이블 표시
    .Axes(xlValue).Delete     ''' 세로 축 제거
    .Axes(xlValue).MajorGridlines.Delete    ''' 주 눈금선 제거
    .ChartGroups(1).Overlap = 100    ''' 계열 겹치기 지정
    .ChartGroups(1).GapWidth = 100   ''' 간격 너비 지정
End With

해설

3. 차트 계열의 각 포인트^Point에 접근하여 값을 알아낸 다음, 반복문과 조건 분기를 통해 색상을 지정합니다. 여기가 이번 코드에서 가장 중요한 부분입니다.

Set oSeries = oCht.SeriesCollection(1)
''' 차트의 첫 번째 계열을 oSeries 변수에 할당
vX = oSeries.Values                   ''' oSeries 값을 vX 변수에 할당

For iX = 1 To oSeries.Points.Count    ''' 계열의 포인트 수만큼 반복 작업
    Set oPoint = oSeries.Points(iX)   ''' n번째 포인틀를 oPoint 변수에 할당
    Select Case vX(iX)                ''' vX(n) 변수에 담긴 값에 따라 조건 분기 처리
        Case Is < 60
        ''' vX(n) 값이 60 미만이면 해당 포인트의 색상을 붉은색으로 지정
            oPoint.Format.Fill.ForeColor.RGB = RGB(200, 0, 0)
        Case Is < 81
        ''' vX(n) 값이 60 ~ 81 미만이면 해당 포인트의 색상을 그린색으로 지정
            oPoint.Format.Fill.ForeColor.RGB = RGB(0, 200, 0)
        Case Else
        ''' 그 외의 값이면 해당 포인트의 색상을 파란색으로 지정
            oPoint.Format.Fill.ForeColor.RGB = RGB(0, 0, 200)
    End Select
Next
rChtSrc.Sort key1:=rChtSrc.Cells(2), order1:=xlDescending, Header:=xlNo
''' rChtScr 영역의 두 번째 셀(여기서는 C4 셀)을 기준으로 내림차순 정렬

해설

차트 개체는 휘하에 똘마니(속성이나 메서드)를 많이 거느리고 있습니다. 매크로 기록기를 이용하면 많은 도움을 받을 수 있습니다.

들어가기 전에 ㅡ 도저히 실현되기 어려울 것 같던 2가지 기술

완성 예

이벤트 프로시저 생성

전체 코드 둘러보기

코드 해설