Module 6. 엑셀 차트 일괄적으로 그리기 - (3) 차트를 그릴 데이터 영역 다듬기

차트를 그리기 위해 X, Y 데이터 영역을 선택할 때, 정확하게 차트를 그릴 영역을 선택하면 그 영역에 대해서만 차트를 그리게 됩니다만, 아래 그림과 같이, A열과 B열 전체를 선택하고 차트를 그리게 되면, 엑셀에서는 현재 시트의 사용 영역(UsedRange)과 데이터 형식을 해석하여 가장 왼쪽의 열을 X, 나머지 오른쪽 열을 Y열로 지정하고, 맨 위쪽의 데이터 형식이 문자열이거나, 공란이면 이 부분을 header(데이터명)로 분리해서 차트를 그려줍니다.

1~2개 차트를 그릴 때는 매우 유용한 기능이긴 합니다만, 원하는 형태로 차트가 그려진 것이 아니라면, 다시 수작업으로 수정하는 작업이 필요하며, 생각보다 불편한 경우가 많습니다.

특히 아래와 같이 데이터가 없는 영역까지도 차트에 포함되는 경우가 있는데, X, Y열이 모두 숫자인 분산 차트에서는 상관없습니다만, 이 차트를 Bar 차트나 꺾은 선형 차트와 같이 X값을 범주형으로 취급하는 차트형식으로 변환하게 되면, 아래 2번째 그림과 같이 공란 부분도 데이터 갯수에 포함되기 때문에 오른쪽 부분이 비어 있는 형태가 됩니다.

X, Y 데이터가 비어있는 영역을 차트에는 포함시키지 않도록 하기 위해서 아래와 같은 function을 정의해주게 되면, 사용자가 열 전체를 대충 선택하더라도 데이터만 포함된 부분만 취해서 차트를 그릴 수 있습니다.

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

Function TrimXYRange(iXCol As Range, iYCol As Range, Optional iHeader As Integer = 0) As Range()

  Dim tSh As Worksheet, tXY(2) As Range, tHN As Integer, i As Long, tX(), tY(), tSN As Long, tFN As Long

  'X열과 Y열, Header 갯수에 대한 정보를 입력해줍니다. 입력 방식은 PeriodicSelection 또는 SelectedColIntoXYPair 함수를 이용하여 선택된 X, Y열을 입력받아 사용하도록 만들었으니 링크를 참고하시면 되겠습니다. 혹은 사용자가 직접 X, Y열을 지정하도록 할 수도 있겠습니다.

  '선택 영역이 포함된 시트를 우선 정의해둔 후, 선택 영역이 사용영역을 초과한 부분과 Header 영역을 제외한 나머지 영역을 데이터 영역으로 지정합니다.
  Set tSh = iYCol.Worksheet
  Set tXY(1) = Application.Intersect(iXCol, tSh.UsedRange)
  Set tXY(1) = Application.Intersect(tXY(1), tXY(1).Offset(tHN, 0))
  Set tXY(2) = Application.Intersect(iYCol, tSh.UsedRange)
  Set tXY(2) = Application.Intersect(tXY(2), tXY(2).Offset(tHN, 0))
  

  'X, Y 영역의 데이터를 임시로 배열에 저장해 둔 후, 데이터가 빈값인 셀은 데이터 영역에서 제외시킵니다.
  tX = tXY(1).Value
  tY = tXY(2).Value
  For i = 1 To UBound(tY, 1)
    If Not (TypeName(tX(i, 1)) = "Empty" And TypeName(tY(i, 1)) = "Empty") Then
      tSN = i
      Exit For
    End If
  Next
  For i = UBound(tY, 1) To 1 Step -1
    If Not (TypeName(tX(i, 1)) = "Empty" And TypeName(tY(i, 1)) = "Empty") Then
      tFN = i
      Exit For
    End If
  Next

  'Header가 없다면, 데이터명 range는 지정하지 않으며, 데이터만 포함된 X와 Y 영역을 반환해줍니다. 따라서, Range 배열의 0번은 데이터명, 1번은 X열, 2번은 Y열 range를 반환하게 됩니다.

  tHN = iHeader: If tHN < 0 Then tHN = 0
  If tHN > 0 Then Set tXY(0) = iYCol.Range(Cells(1, 1), Cells(tHN, 1))

  Set tXY(1) = tXY(1).Range(Cells(tSN, 1), Cells(tFN, 1))
  Set tXY(2) = tXY(2).Range(Cells(tSN, 1), Cells(tFN, 1))
  TrimXYRange = tXY
  Erase tX, tY
End Function

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

위의 함수는 데이터가 있는 영역만 잘라서 데이터명과 X, Y열에 대한 range 정보를 반환해줍니다. X, Y열에 대한 정보는 필수로 입/출력되는데, 경우에 따라서는 X가 없는 차트를 그려야할 때도 있습니다. X가 없는 차트를 그려야할 일이 많으신 분들은, X열을 선택적으로 입력받을 수 있도록 약간만 수정하시면 되겠습니다.