Module 6. 엑셀 차트 일괄적으로 그리기 - (2) 차트 수식 분해하기(Series 함수)

사용자가 사용하는 데이터와 목적에 따라 차트는 여러 종류가 될 수 있습니다. 누군가는 바 차트를, 누군가는 파이 차트를 주로 취급할 수도 있습니다만, 저는 개인적으로 분산차트를 많이 그립니다. X, Y 데이터가 동시에 있고, X값의 연속적인 변화에 따른 Y 결과를 확인하는 것이지요.

또한 여러 조건에 대한 X, Y 데이터들을 서로 비교하기 위해 1개의 차트를 중첩시켜서 경향을 비교하기도 합니다.

사용자들마다 활용분야가 다르다보니 모든 종류의 데이터에 대해 모든 종류의 차트를 생성하는 매크로는 구현하기 쉽지 않습니다. 여기에 예시로 보여드리는 매크로를 개인에 맞게 적당히 수정하시면, 본인의 취향대로 수정이 가능할 것입니다.

엑셀의 차트에 나타내는 차트의 식(formula)은 다음과 같은 형태를 가집니다. 엑셀에서 임의의 차트를 그리고 나서, 차트를 선택하고 수식란을 확인해보시면 됩니다.

=SERIES(Sheet1!$D$1,Sheet1!$A$2:$A$1002,Sheet1!$D$2:$D$1002,1)

SERIES( ) 함수에 (데이터명, X값, Y값, 해당 그래프의 순서)를 입력하게 됩니다.

대부분 형태의 차트에서 위의 4가지 항목만 포함되며, Bubble형 차트에서는 각 점의 크기를 나타내는 배열값을 포함하여 총 5가지 항목이 입력되어야 합니다. 따라서, 버블형을 많이 쓰시는 분들이라면, 일반적인 분산 차트보다 입력값이 많아지기 때문에 여기에서 소개해드리는 매크로를 일부 수정해야합니다.

위의 Series() 함수에 입력되는 값은 일반적으로는 엑셀의 range 형태로 지정합니다만, 반드시 range만 입력하는 것은 아닙니다. 데이터명은 문자열, 배열, range 주소가 모두 지정될 수 있고, X, Y값은 배열 데이터와 range 주소를 사용할 수 있습니다. 만약 range 주소를 입력해주면, 해당 주소의 값을 배열로 변환하여 차트를 그려주는 것입니다.

X, Y값에 range 주소를 입력할 때, 시트명이 함께 붙어있는 것을 알 수 있습니다. 그래서 차트를 다른 시트로 복사해가더라도 차트가 그래도 유지되는 것입니다. 그런데, 매크로를 이용해 차트를 그릴때, X, Y열 값에 range 주소만 입력하게 되면, 시트명이 포함되지 않아 오류가 발생하게 되는데, 시트명이 공백이 없는 문자열인 경우에는 시트명+!+Range 주소 형태이지만, 공백, 한글, 영문자 등이 조합된 시트명이라면 시트명 앞, 뒤에 작은 따옴표( ' )를 붙여주지 않으면 오류가 발생합니다. 시트명을 해석해서 어떠한 경우에 따옴표를 붙여줄지 판단해야하는데, 이게 생각보다 경우의 수가 많고, 불필요한 작업이 되는 경우가 있습니다.

아래와 같은 function을 사용하여, 차트의 Series 함수로부터 시트명을 찾아주는 함수를 하나 만들어두었습니다. 물론 시트명만 확인하는 것이 아니라, 만약 현재 차트가 있다면, 차트 수식을 분해하여, 시트명, 데이터명, X, Y 데이터 range로 분리하는 함수입니다.

'-------------------------------------------------

Function SplitChartSeriesFormula(iFormula As String, Optional iDelSheetName As Boolean = False) As String()
  On Error GoTo ErrorHandler
  Dim tStr() As String, tSplit, i As Long

  '차트의 한개 데이터시리즈의 수식이 입력되면, 함수 내의 입력값만 추출해서, 쉼표 ","로 나누어줍니다. 버블 차트인 경우에는 5개, 나머지는 4개의 데이터로 나누어집니다.
  tSplit = Mid(iFormula, Len("=SERIES(") + 1, Len(iFormula) - Len("=SERIES(") - 1)
  tSplit = Split(tSplit, ",")
  For i = 0 To UBound(tSplit)
    ReDim Preserve tStr(i + 1)
    tStr(i + 1) = Trim(tSplit(i)) 'i=0 : 데이터명, 1: X값, 2: Y값, 3: Index, 4: Bubble size
  Next

  '차트를 그리기 위해서는 반드시 Y값 영역이 입력되어야 하므로, Y 영역의 주소를 "!"로 분리하여 시트명과 주소 영역으로 다시 나누어주며, 앞 부분만 시트명으로 반환해줍니다.

  '따라서, tStr은 0: 시트명, 1: 데이터명, 2: X값, 3: Y값, 4: Index, 5: Bubble size의 배열이 됩니다.

  tSplit = Split(tSplit(2), "!")
  tStr(0) = tSplit(0)

  '만약, 시트명을 생략한 주소만 반환받고 싶다면, 시트명을 삭제하도록 합니다. 현재 작성하려는 매크로에서는 그다지 필요없는 기능입니다만, 향후 다른 목적으로 사용하기 위해 이러한 옵션을 미리 추가해둡니다.

  If iDelSheetName Then
    For i = 1 To UBound(tStr)
      tStr(i) = Replace(tStr(i), tStr(0) & "!", "")
    Next
  End If
ErrorHandler:
  SplitChartSeriesFormula = tStr
  Erase tStr
End Function

'-------------------------------------------------

차트 내 SeriesCollection의 Property 중 Name, XValues, Values를 사용하면, 차트 그리기에서 데이더명과 X, Y값을 지정해줄 수는 있습니다만, 반대로 이미 그려진 차트에서 X, Y 영역을 알아내는 것이 어렵습니다. 그 외에도 사소한 이유들로 인해, 위와 같이 차트 수식을 분리해내면, 이후 차트를 합치거나, 분리하거나, 데이터 영역을 이동하는 등.. 여러가지 작업을 하는데 유용하게 사용할 수 있을 것입니다.