차트의 레이블을 일일이 마우스 클릭하여 입력하고, 색상까지 바꿔주는게 귀찮아서 자동으로 완성해줄 수 있는 VBA가 있는지 찾아봤다.

그리고 그 코드를 약간 수정해서 아래의 결과물을 보여질 수 있는 코드를 만들었다.

결과

사용방법

차트를 클릭한 뒤 메크로로 아래의 코드를 넣은 모듈을 실행시키면 된다.

Sub LastPointLabel()
  Dim mySrs As Series
  Dim iPts As Long
  Dim vYVals As Variant
  Dim vXVals As Variant

  If ActiveChart Is Nothing Then
    MsgBox "Select a chart and try again.", vbExclamation
  Else
    Application.ScreenUpdating = False
    For Each mySrs In ActiveChart.SeriesCollection
      With mySrs
        vYVals = .Values
        vXVals = .XValues
        ' clear existing labels
        .HasDataLabels = False
        
        For iPts = .Points.Count To 1 Step -1
          If Not IsEmpty(vYVals(iPts)) And Not IsError(vYVals(iPts)) _
              And Not IsEmpty(vXVals(iPts)) And Not IsError(vXVals(iPts)) Then
            ' add label
            Set pt = mySrs.Points(iPts)
            pt.ApplyDataLabels _
                ShowSeriesName:=True, _
                ShowCategoryName:=False, ShowValue:=False, _
                AutoText:=True, LegendKey:=False
            Set dl = pt.DataLabel
            With dl
                'text color match to the it's line of the chart
                .Font.Color = mySrs.Format.Line.ForeColor
                .Top = pt.Top - 10
                .Left = pt.Left + 20
                .Font.Size = 12
                .Font.Bold = True
            End With
          End If
        Exit For
        Next
        ' Change the font size to 12
        
      End With
    Next
    ' legend is now unnecessary
    ActiveChart.HasLegend = False
    Application.ScreenUpdating = True
  End If
End Sub

참조

https://peltiertech.com/label-last-point-for-excel-2007/

https://stackoverflow.com/questions/59274616/custom-chart-point-datalabel-position

얼마전 부터 기존에 하고있던 관리 업무에서 소싱영업까지 업무가 추가가 되었다. 영업에 대해는 잘 모르지만 영업관련 부서를 바로 옆에서 지켜본 결과 프로그램 없이 엑셀로 트랙킹을 하는 것은 쉽지 않았다. 

또 나 역시 제품 클레임 제품들을 트래킹해야 했는데 트래킹 프로그래밍을 만드는건 귀찮음에 미루고 미뤘다.

일단 결과 화면부터 보자

결과화면은 위와 같이 업체별로 진행상황이 나오도록 할 것이다.

일단 아주 원시적이지만 직관적으로 트래킹을 하는 경우를 보자

이방법은 쉽게 느껴질 수 있지만 이건 정말 비효율 적이다. 엑셀을 처음 하는 사람들은 아마 이렇게 할지도 모른다. (내가 그랬다..)

처음엔 쉽게 느껴질 수 있지만 나중가면 업체를 찾는거 조차 어렵고 정보가 좌우로 적혀 있어 보기가 많이 불편하다. 또한 상세정보 들이 길어지면 가독성은 더 떨어지게 된다.

그러면 이것을 어떻게 해결해야 하는가?

1. DB 형식으로 엑셀을 만든다.

날짜, 업체명, 진행상황, 상세로 칼럼을 나누었고 새로운 상황이 생길때 마다 새로운 열을 추가 시켰다.

이거만 보면 "에이 뭐야 이거 보기 더 어려워 졌잖아." 생각할 지도 모른다. 더 많은 정보가 들어갔고 입력하기 편하지만 시간상으로만 정렬이 되어있을 뿐 업체들이 섞여 있어서 한눈에 파악하기 어렵다. 

물론 이때 정렬 기능을 사용하여 이 데이타를 업체별로 정렬하여 보여지게 할 수도 있지만 그렇게하면 입력 할때마다 다시 날짜별로 정렬을 바꾸고 상황을 보고 싶을 또 정렬로 업체별로 수정해야 하는 번거로움이 있다.

2. 이 데이터를 바탕으로 피벗테이블을 만든다.

해결책은 피벗테이블을 만드는 것이다. 

1) 데이터가 지속적으로 입력되기 때문에 테이블로 만든다.

이렇게 테이블을 만들면 새로운 열을 입력할 때 마다. 자동으로 테이블의 범위가 변경된다.

2) 피벗테이블의 범위를 테이블로 선택한다.

3) 보여질 정보들을 row에다 넣는다.

 업체명 별로 분류가 되고 내부에 날짜가 있고 그 날짜에 대한 진행상황과 상세내용이 나와있지만 밑으로 주주룩 나와서 보기가 어렵다.

4) 피벗테이블 디자인 바꾸기

레포트 레이아웃에 들어가서 show in tabular form 을 클릭한다. 

단지 저 버튼을 클릭했을 뿐인데

위와 같이 보기 더욱 쉽게 보여진다.

이건 계산 관련 피봇이 아니기 때문에 grand totals 버튼을 클릭해서 안보이게 바꿀 수 있다.

5)피봇 스타일 바꾸기

이렇게 하면 업체별로 눈에 더 쉽게 들어오게 바꿀 수 있다.

3. 데이터 추가하기

이후 정보를 더 추가하게 되면

피봇테이블에 아직 데이터가 변경된게 적용 안됬는데

데이터를 리프레쉬하면

아래와 같이 추가가 된것을 볼 수 있다.

위의 방식대로 하면 영업뿐만이 아니라 재품별 클레임 상황도 쉽게 관리가 가능하다. 

+ 슬라이더 추가

아래와 쉽게 같이 업체 A, B만 선택하여 볼 수 있다.

https://post.naver.com/viewer/postView.nhn?volumeNo=9080703&memberNo=25379965&navigationType=push

이미 엑셀로 잘 사용하고 있는데 프로그래밍적인 기능이 필요한 경우, 아마 세가지 중 하나를 선택해야만 할 것이다. 

1. 그냥 프로그래밍적인 기능을 포기하고 엑셀에서 기본적으로 제공되는 포뮬러만 사용한다.

2. 파이썬으로 옮기고 모든 기능들을 구현한다. 혹은 추가로 필요한 기능만 하는 파이썬 스크립트를 만든다.

3. 지금까지 잘 쓰던건 나두고 필요한 프로그래밍적인 기능만 VBA로 추가한다. 

파이썬으로 다 바꾸면 나중을 위해서는 좋을것이다. 실행도 빠르고 간편하고 코드도 간단하다.

필요한 기능만 파이썬으로 만들면 엑셀과 파이썬을 둘다 관리해야하는 번거로움이 생긴다.

그래서 생각한게 간단한건 VBA로 처리하는 것이다. 인터넷을 찾아보니 회사에 파이썬이 보안에 막혀서 VBA를 쓰는 경우도 있다고 한다.

(개인적으로, 만일 복잡한 코드를 만들어야한다면 그냥 파이썬을 사용하는게 더 효율적이고 빠르고 구조적으로 설계하는게 쉬울것 같다.)

1. 에디터 실행하기

ALT + F11 를 눌러 visual basic for applications를 실행한다.

2. 구조는 다음과 같다.

VBAProject
- Microsoft Excel Objects
--sheet1
--thisWorkBook

오른쪽 화면에는 VBAProject(파일명) 하위 요소는 Microsoft Excel Objects폴더, 그 안에 sheet1와 thisWorkBook이 있다.

또한, 오른쪽 마우스를 클릭하여 UserForm, module, class module 을 추가 할 수 있다.

3. 코드 작성하기

모듈을 만든 뒤 코드를 아래와 같이 작성해 본다.

Sub Cyan()
Cells.Interior.ColorIndex = 28
End Sub

4. 간단히 메크로를 통해 실행해보기

Alt + F8를 누루거나 저 Macros 아이콘을 클릭한다.

실행을 누르면 만들어 놓은 기능이 실행이 된다. 

참고로 모듈이 아니라 sheet object에 코드를 넣어도 되는데 이 경우 아래와 같이 이름이 나온다.

5. module과 sheet object 용도의 차이

아래의 코드를 보듯이 공용으로 사용될 수 있는 기능과 세부적인 로직은 모듈로 만들어 저장하고,

sheet object는 그 sheet에만 사용하는 코드와 모듈함수, 서브실행을 한다.

Module: DataAccess:

Function GetData(strTableName As String, strCondition1 As String) As Recordset
    'Code Related to getting data from the database'
End Function

Module: PopulateSheet:

Sub PopulateASheet(wsSheet As Worksheet, rs As Recordset)
    'Code to populate a worksheet '
End Function

Sheet: Sheet1 Code:

Sub GetDataAndPopulate()
    'Sample Code'
     Dim rs As New Recordset
     Dim ws As Worksheet
     Dim strParam As String
     Set ws = ActiveSheet
     strParam = ws.Range("A1").Value

     Set rs = GetData("Orders",strParam)

     PopulateASheet ws, rs
End Sub

Sub Button1_Click()
    Call GetDataAndPopulate
End Sub

6. function과 sub의 차이.

Sub로 만든 코드는 메크로를 통해 실행할 수 있다.

function으로 만든 코드는 셀에서 실행 할 수 있으며 반환값을 가져야 한다.

7. 저장하기

그냥 엑셀파일로 저장하게되면 만들어 놓은 메크로(기능)이 사라지게 된다. 

그러니 저장할땐 *. xlsm 로 저장해야한다.