• 최초 작성일: 2023-03-17
  • 최종 수정일: 2023-03-17
  • 조회수: 2,312 회
  • 작성자: 권현욱 (엑셀러)
  • 강의 제목: VB0261 ㅡ 조건부 색상 차트 만들기

엑셀러 권현욱

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

1.
"이제 외국어 공부할 필요 없다. 너희들이 사회 진출할 무렵이면 전 세계 모든 언어를 자동으로 번역해 주는 통역기가 나온다. 그러니 외국어 공부할 시간에 수학을 해라."(중학교 때 수학 담당 이쌤, 1980년대 초)

일본어라고는 스미마셍, 사요나라 조차 모르는 딸이 얼마 전 일본으로 배낭 여행을 다녀 왔다. 숙소는 에어비앤비로, 언어는 스마트폰 번역 어플로 해결했단다. 거의 불편함이 없었다고 한다.

2.
"이제 코딩 배울 필요 없어요. 조만간 코딩을 자동으로 해주는 머신이 나올 겁니다. IBM인가 마이크로소프트에서 거의 다 개발했대요."(20년 쯤 전 ASP 배울 때 김 강사, 2000년대 초)

로딩 중...

(이미지 출처: unsplash.com)

최근 인공지능(AI)을 업무에 본격적으로 활용하고 있다. 엑셀의 기본 기능이나 함수는 물론이고 VBA 코딩도 한다. 그것도 잘! 앞으로 도움말이나 매크로 기록기를 쓸 일은 없겠구나 싶다.

업종 전환(?)을 해야 하나 잠시 고민했지만, 여전히 외국어로 밥벌이 하는 분들이 많은 현실을 자각하고는 마음을 바꿨다. 인공지능을 이용하여 좀 더 효율적으로 코딩하고 활용하는 방법을 고민해 보는 쪽으로. 우리에겐 200여 년 전 러다이트 운동Luddite Movement으로부터 체득한 지혜가 있지 않은가. 세상은 그렇게 진보해 왔으니까.



  • 이번 강의는 '엑셀러TV' 멤버십 회원을 대상으로 '우선 공개'하는 콘텐츠입니다. 전체 대상으로는 일주일 후 공개 예정입니다.
  • VBA 강의이고 난이도가 조금 있습니다. VBA에 익숙하지 않은 분은 코드를 복사/붙여넣기하며 따라해 보세요.

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

로딩 중...

조건부 색상 차트

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

완성 예

하지만 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 셀)을 기준으로 내림차순 정렬

해설

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

#엑셀차트 #조건부색상차트 #챗GPT #chatGPT #인공지능 #AI