VBA강좌

미국 클린턴 정부 시절에 노동부 장관을 역임한 로버트 라이시(무섭게 생긴 콘돌리자 라이스 말고... 저는 이 두 분의 이름이 늘 헷갈리더군요. 외모상으로는 혼돈할래야 할 수가 없는 캐릭터인데 말입니다. ^^;)가 쓴 <부유한 노예>라는 책에 이런 내용이 있었습니다.

... 우리 이웃집에는 위성 안테나가 설치되어 있는데, 이 안테나를 통해서 1500개의 TV 채널을 볼 수 있다고 한다. 어떤 채널이 있는지 알아보는 데에만 며칠이 걸릴 것이고, 실제 TV 시청 시간보다는 방송 시간표 보는 시간이 분명 많을테니 말이다. 그래도 안테나를 설치했다는 사실만으로도 기뻐하고 좋아한다...

아주 오래 전에 본 4컷짜리 만화가 오버래핑 되어 떠올랐습니다(한 20년쯤 전 한겨레신문이었던 것으로 기억합니다만 상황은 오늘날에도 여전히 유효한 것 같습니다). 만화를 그릴 수는 없고 상황을 대충 설명하자면...

#장면1 : 원시인 K
   1일 노동시간 : 4시간
   1일 수면시간 : 18시간
   주거면적 : 측정불가
   배우자 : 12명
   취미 : 사냥
   두려운 것 : 천둥
   치명적 질환 : 골절상
행복지수 : 100점

#장면2 : 조선시대 K초시
   1일 노동시간 : 7시간
   1일 수면시간 : 10시간
   주거면적 : 100평
   배우자 : 4명
   취미 : 독서
   두려운 것 : 조상님
   치명적 질환 : 종기
행복지수 : 85점

#장면3 : 현대인 K
   1일 노동시간 : 12시간
   1일 수면시간 : 6시간
   주거면적 : 25평
   배우자 : 1명
   취미 : TV시청
   두려운 것 : 아내
   치명적 질환 : 암, 에이즈
행복지수 : 30점

마지막 장면에서 행복지수가 30점 밖에 안되는 현대인K씨는 이렇게 생각합니다.

'옛날엔 TV가 없어서 무척 심심했을거야. 상상만해도 끔찍하군! 어떻게 살았나 몰라~~'

성공, 그리고 행복의 진정한 의미와 척도는 무엇일까요.
로버트 라이시, 그의 용기가 새삼 부럽습니다.

예제 파일 내려받기

지난 X0461 강좌에서 분산형 차트에 레이블을 표시하는 방법 한 가지를 소개해 드렸습니다. 이것과 관련하여 몇 건의 질문 메일이 답지한 것으로 보아, 아마도 여러 가지 경우에 두루 적용될 수 있나 봅니다. 해서... 분산형 차트에 레이블을 표시하는 방법들에 대해 정리를 해 보고자 합니다.

1. 노동집약적(?)인 방법

이 방법은 지난 X0461 강좌에서 소개해 드린 방법입니다. 자세한 사항은 해당 강좌를 참고하시기 바라며, 여기서는 전반적인 순서에 대해서만 간단하게 설명 드립니다.

매출액과 경상이익 데이터에서 매출액 데이터에 사용자 지정 서식을 지정하여 차트에 표시할 문자열 형태가 되도록 합니다.
분산형 차트(표식만 있는 분산형)를 작성한 다음, 차트의 X축 서식을 변경합니다.
'닫기' 버튼을 클릭합니다. 차트 계열을 마우스 오른쪽 버튼으로 클릭하고 '데이터 레이블 추가' 메뉴를 선택하여 경상이익 금액이 표시되도록 합니다.
데이터 레이블을 마우스 오른쪽 버튼으로 클릭하고 '데이터 레이블 서식' 메뉴를 선택합니다. 대화상자가 나타나면 '레이블 옵션-X 값' 항목을 클릭하여 체크 표시가 나타나도록 하고 '닫기' 버튼을 누릅니다.

기억이 새록새록 나시지요?(아침에 무슨 반찬이 나왔는지도 가물가물한데 장난치지 말라구요?... 알겠나이다! ^^;) 기억이 안나는 분은 X0461 강좌를 내려받아 살펴보세요.

http://www.iExceller.com/MyXls/Lectures/X0461.zip

2. 여전히 노동집약적이나 계열수가 적을 때 쓸만한 방법

앞의 방법보다 조금 간단해 보이는 방법입니다. 차트의 계열이 적은 경우(15~20개 정도...?)이면서 And VBA를 모를 경우 호구지책... 아니지... 궁여지책으로 그런대로 쓸만한 방법인 듯 합니다.

C4:D11 영역의 데이터를 선택하고 '표식만 있는 분산형' 차트를 작성합니다. 그런 다음 차트의 각종 서식을 적당히 손질(?)하여 그림과 같은 차트 형태를 만듭니다.

임의의 계열을 하나 선택하고 마우스 오른쪽 버튼을 클릭한 다음 '데이터 레이블 추가' 메뉴를 선택하면 각 계열의 레이블이 나타납니다
레이블 하나를 클릭해 보면 전체 레이블이 선택됩니다. 이 상태에서 임의의 레이블을 사알~짝 눌러보면 계열 중 하나만 선택됩니다(어째... 표현이 어설픈데... 하여튼 잘 선택하시리라 믿고...).
아래 그림처럼 '350'을 선택하고 수식 입력줄에 등호(=)를 입력합니다. 이것은 강동 지점의 경상이익 금액이므로 B4 셀을 클릭합니다.

<Enter> 키를 누르면 '350'이라는 값 대신 '강동'이라는 지점명이 표시됩니다. 마찬가지 방법으로 나머지 값들을 지점으로 대체해 줍니다.

3. VBA를 활용하는 방법

데이터의 계열이 많거나 이런 류의 작업을 빈번히 해야 하는 경우라면 VBA를 사용하여 자동화 할 수 있습니다.

표 내부의 셀을 하나 선택하고 '삽입' 탭의 '표' 그룹에 있는 '표' 컨트롤을 클릭합니다.
'표 만들기' 대화상자가 나타납니다. 범위(B2:D11)가 제대로 지정되었는지 본 다음 '확인' 버튼을 클릭합니다.

엑셀 2002 버전에서는 '데이터-목록-목록 만들기' 메뉴를 사용하면 됩니다.

B4:B11 영역을 선택하고 '지점명'이라는 이름을 정의합니다. 이름 정의를 어떻게 하냐고요??(허허... 큰일인데... ^^;) 워크시트 좌측 상단의 '이름 상자'에 '지점명'이라고 입력하고 엔터키를 누르세요). 표를 만든 다음 이름 정의를 하면 데이터를 추가하거나 삭제할 경우, 범위도 자동으로 변하므로 아주 편리합니다. 먼 소리냐구요? 광주 지점 밑에 제주 지점을 추가한 다음 '이름 상자'에서 '지점명'을 선택해 보세요!

엑셀 2002 버전 이하인 경우 데이터 추가/삭제에 따라 범위를 유동적으로 변하게 하려면 '동적 이름 정의(Dynamic Naming)'를 이용해야 합니다.

'수식' 탭의 '정의된 이름' 그룹에서 '이름 관리자'를 클릭합니다.
'이름 관리자' 대화상자에서 '새로 만들기' 버튼을 클릭하고 다음과 같이 이름 정의 합니다.

이름 : 지점명

참조 대상 : =OFFSET(Sheet1!$B$4,0,Sheet1!$B$4:$D$110,COUNTA(Sheet1!$B:$B)-1)

VB Editor 상태에서 모듈 시트를 하나 삽입하고 다음과 같은 코드를 작성합니다(예제 파일에도 복사해 두었습니다. 친절하게도... ^^).

Sub DisplayLabel()

    Dim rngBranch As Range
    Dim chtMyChart As Chart
    Dim i As Integer
    Dim intBranch As Integer

    Set chtMyChart = ActiveSheet.ChartObjects(1).Chart
    On Error Resume Next
    Set rngBranch = Range("지점명")

    chtMyChart.SeriesCollection(1).ApplyDataLabels Type:=xlDataLabelsShowValue, _
        AutoText:=True
    intBranch = chtMyChart.SeriesCollection(1).Points.Count

    For i = 1 To intBranch
        With chtMyChart.SeriesCollection(1).Points(i)
            .HasDataLabel = True
            With .DataLabel
                .Text = rngBranch.Cells(i)
                .Font.Size = 10
                .Font.Name = "굴림"
            End With
        End With
    Next i

End Sub

참고로, 표시된 레이블을 지우는 코드는 다음과 같습니다.

Sub DelDataLabel()
    Dim chtMyChart As Chart
    Set chtMyChart = ActiveSheet.ChartObjects(2).Chart

    chtMyChart.SeriesCollection(1).HasDataLabels = False
End Sub

이런 정도의 방법이 떠오릅니다. 이것 이외의 또 다른 좋은 방법을 알고 계신 분은 알려주세요.