Module 4. 다항식으로 일괄 Fitting (Regression) 하기 - (5) (X,Y) 데이터쌍으로 지정하기

이전 글에서, 여러 파일을 열어 하나의 워크시트로 통합하는 매크로를 소개드린 적이 있습니다. (바로가기 : Module 3)

이렇게 통합해둔 데이터는 기본적으로 동일한 행 갯수를 갖는 데이터는 아래와 같은 구조를 가지게 됩니다. 각 주기마다 동일한 데이터 배열을 가지고, header 갯수도 동일하게 됩니다. 또한, fitting을 하기위한 X 변수가 반드시 1번째 열에 있지 않을 수도 있습니다.

어쨋거나, 이렇게 데이터들이 배열이 되어있다면, 엑셀에서 일일이 X열과 Y1열을 선택해서 산점도를 그리고, 다시 regression하는 것이 매우 번거로운 일이 됩니다. 엑셀에서 산점도는 앞의 열을 X로, 뒤의 열을 Y로 취급하기 때문에, 차트를 그린 후, 수작업으로 X, Y열을 바꿔줘야합니다.

만약, 데이터가 수십개의 파일로부터 합쳐진 데이터라면, X, Y열을 일일이 마우스로 클릭하는 것조차 번거롭고, 실수할 가능성이 높아집니다.

위와 같이 반복되는 구조를 가진 데이터에서, 주기적으로 X, Y열을 지정하고, 그 데이터들을 일괄적으로 fitting할 수 있다면, 분석작업이 매우 빨라질 수 있습니다. Fitting 작업 뿐만 아니라, 주기적 배열 데이터를 X, Y열로 간단하게 지정할 수 있다면, 자동으로 차트를 그릴 때에도 매우 편리할 수 있습니다. 

아래의 매크로 예제는 일정한 주기를 갖고 반복된 구조의 데이터에서 간단히 X, Y쌍으로 지정하는 함수이며, 이로부터 반환된 결과는 다양하게 응용하실 수 있습니다. 또한, 개인적으로 많이 사용하는 패턴이 있다면, 본인의 용도에 맞게 적당히 수정하여 사용하실 수도 있을 것입니다.

사용방법은 다음과 같습니다.

우선 X, Y 데이터 전체의 영역을 선택합니다

주기적으로 반복된 데이터인지 체크하여, 반복된 데이터라면 '주기'를 먼저 선택하고, 해당 주기 데이터에서 X열과 Y열로 나눠줍니다. 이때 X는 1개의 열만 선택하도록 하고, Y열은 여러개의 열도 선택할수 있게 해줍니다.

X와 Y열이 쌍을 이룰 수 있도록 X열과 Y열을 배열에 순서대로 할당해서 반환해주며, 헤더 정보가 있는 데이터라면 헤더의 행 수를 입력받도록 합니다.

인터페이스가 그다지 고급스럽지는 않더라도, 그래도 마우스 클릭 몇번만으로 (X, Y)쌍으로 할당해줄 수 있기 때문에 한번 만들어두면 반복작업에서 시간을 크게 단축시킬 수 있을 것입니다.

그리고, 아래 함수 중 UnionRange, HasIntersect 함수는 이전 글에서 작성했던 함수들이며, 엑셀에서 제공하는 Application.Union, Application.Intersect의 변형 함수입니다.

다음에는 이렇게 (X, Y) 쌍으로 지정된 정보를 이용하여, 일괄 fitting을 수행하도록 하겠습니다.

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

Function SelectedColIntoXYPair(oXCol() As Range, oYCol() As Range, oHeader As Integer) As Long
  On Error GoTo ErrorHandler
  Dim tRange As Range, tSelRange As Range, tUsedRange As Range, tPeriod As Range, tXCol As Range, tYCol As Range, tYCols() As Range
  Dim tSelCol As Range, tCheck As Boolean
  Dim i As Long, j As Long, n As Long
  Dim tStr As String, tMSG
  

  '활성화된 시트에서 데이터를 선택하면, 선택 영역에 대한 정보를 저장해둡니다.

  '선택영역이 데이터가 없는 영역까지 선택되었다면, 사용된 영역에 한해서만 작업할 수 있도록 현재 시트의 사용 영역도 저장해둡니다.

  Set tSelRange = Selection
  Set tUsedRange = tSelRange.Worksheet.UsedRange

  '만약 사용자가 영역을 선택하지 않았다면, 현재 셀이 포함된 영역으로 확장하여 선택 영역으로 지정합니다. 만약, 현재 셀이 사용영역을 벗어나있다면, 사용 영역을 모두 선택 영역으로 자동 설정해줍니다.

  If tSelRange.Columns.Count = 1 And tSelRange.Rows.Count = 1 Then
    If HasIntersect(tSelRange, tUsedRange) Then
      Set tSelRange = tSelRange.CurrentRegion
    Else
      Set tSelRange = tUsedRange
    End If
  End If
  

  '선택된 영역(Area)이 1개라면 주기적으로 반복된 데이터라고 인식하여 X, Y열을 지정하도록 합니다. 만약, 선택 영역이 여러 개라면, 사용자가 임의로 X, Y열을 지정한 것으로 판단하여 다른 옵션을 적용합니다.

  If tSelRange.Areas.Count = 1 Then
    If tSelRange.Columns.Count > 1 Then

  'X, Y 열을 지정해야하므로, 선택 영역의 열 수가 1개 이하라면 작업을 종료하고, 1개 이상이라면 데이터 반복 주기를 지정하도록 합니다. 반복 주기는 1개 영역만 선택해야하며, 여러개 열을 선택하거나, 선택 영역을 벗어난 위치에서 주기를 선택하는 경우 재선택할 수 있도록 반복합니다.
      Do
        tSelRange.Select
        tSelRange.Cells(1, 1).Activate
        Set tPeriod = Application.InputBox("단위 주기를 선택하세요." & vbCrLf & "(XYY..XYY..와 같이 주기적으로 배열된 데이터인 경우)", "주기 선택", tSelRange.Address, Type:=8)
        Set tPeriod = Application.Intersect(tSelRange, tPeriod.EntireColumn)
        If tPeriod.Areas.Count <> 1 Then If MsgBox("단위 주기는 1개의 영역으로 선택하세요.", vbOKCancel, "선택 오류") = vbCancel Then GoTo ErrorHandler
      Loop Until tPeriod.Areas.Count = 1
      tPeriod.Select
      tPN = tPeriod.Columns.Count
      

  'X열은 1개의 반복주기 안에서 1개의 열을 선택하도록 합니다.
      Do
        tPeriod.Select
        Set tXCol = Application.InputBox("X열을 선택하세요.", "X열 선택", tPeriod.Columns(1).Address, Type:=8)
        If HasIntersect(tXCol.EntireColumn, tPeriod) Then
          Set tXCol = Application.Intersect(tPeriod, tXCol.EntireColumn)
          tCheck = (tXCol.Areas.Count = 1 And tXCol.Columns.Count = 1)
          If Not tCheck Then MsgBox "X열은 1개만 선택하세요.", vbInformation, "선택 오류"
        Else
          tCheck = False
          MsgBox "선택영역 내에서 X열을 선택하세요.", vbInformation, "선택 오류"
        End If
      Loop Until tCheck
      

  'Y열은 X열을 제외한 나머지 열로 자동 지정합니다. 그러나, 사용자가 X열을 제외한 나머지 열 중에서 일부만을 선택할 수 있으므로, Y열을 지정하도록 합니다. Y열 역시, 1개 주기영역 내에서만 지정하도록 합니다.
      Set tRange = Nothing
      For i = 1 To tPeriod.Columns.Count
        If tPeriod.Columns(i).Column <> tXCol.Column Then Set tRange = UnionRange(tRange, tPeriod.Columns(i))
      Next
      
      Do
        tPeriod.Select
        Set tYCol = Application.InputBox("Y열을 선택하세요.", "Y열 선택", tRange.Address, Type:=8)
        If HasIntersect(tYCol.EntireColumn, tPeriod) Then
          tCheck = True
          Set tYCol = Application.Intersect(tPeriod, tYCol.EntireColumn)
        Else
          tCheck = False
          MsgBox "선택영역 내에서 Y열을 선택하세요.", vbInformation, "선택 오류"
        End If
      Loop Until tCheck
      

  '선택된 Y열을 각각의 열로 나눠서 배열에 할당해둡니다. 이때 X열과 중복된 열이 있다면 제외시킵니다.
      n = 0
      For i = 1 To tYCol.Areas.Count
        For j = 1 To tYCol.Areas(i).Columns.Count
          If tYCol.Areas(i).Columns(j).Column <> tXCol.Column Then
            n = n + 1
            ReDim Preserve tYCols(1 To n)
            Set tYCols(n) = tYCol.Areas(i).Columns(j)
          End If
        Next
      Next

  '만약, X와 중복된 열 이외에 Y열이 없다면, 작업을 진행할 수 없으므로, 메세지를 남기고 종료합니다. 그렇지 않다면, (X, Y)열 정보를 반복해서 배열에 지정해주고, 이 배열값을 반환해줍니다. 또한, (X, Y)가 몇쌍인지 함수의 결과값으로 반환해줍니다.
      If n = 0 Then MsgBox "X, Y열은 서로 다른 열을 선택하세요.", vbInformation, "선택 오류": GoTo ErrorHandler
      n = 0
      For i = 1 To tSelRange.Columns.Count Step tPN
        For j = 1 To UBound(tYCols)
          If tYCols(j).Column < tSelRange.Column + tSelRange.Columns.Count Then
            n = n + 1
            ReDim Preserve oXCol(1 To n)
            ReDim Preserve oYCol(1 To n)
            Set oXCol(n) = tXCol
            Set oYCol(n) = tYCols(j)
            Set tYCols(j) = tYCols(j).Offset(0, tPN)
          End If
        Next
        Set tXCol = tXCol.Offset(0, tPN)
      Next
      SelectedColIntoXYPair = n
    Else
      MsgBox "2개 열 이상을 선택하세요.", vbInformation, "선택 오류"
      GoTo ErrorHandler
    End If
  Else

  '만약, 사용자가 여러 영역을 선택했다면, 사용자가 임의로 X, Y열을 선택했다고 간주하고, 이를 XYXY 배열로 처리할지, YXYX 배열로 처리할지 물어보고, (X,Y)쌍으로 만들어 반환합니다.

  '우선, 선택영역 중 사용영역을 벗어난 영역은 제외시킨 후, 각각의 열로 나눠줍니다.

    Set tSelRange = Application.Intersect(tSelRange, tUsedRange)
    n = 0
    For i = 1 To tSelRange.Areas.Count
      For j = 1 To tSelRange.Areas(i).Columns.Count
        n = n + 1
        ReDim Preserve tYCols(1 To n)
        Set tYCols(n) = tSelRange.Areas(i).Columns(j)
      Next
    Next

  '배열의 형태를 입력받도록 합니다. 단순 XY배열로 간주하였으나, XYY, XYYY.. 등으로도 배열되어있다면, 사용자에 따라 별도로 지정하는 옵션을 추가해줄 수 있습니다.
    tMSG = MsgBox("XY,XY,…로 배열되어 있습니까?" & vbCrLf & "예 : XY,XY,… , 아니오 : YX,YX,… , 취소 : 작업 취소", vbYesNoCancel, "입력 선택")
    n = 0
    If tMSG = vbYes Then
      For i = 2 To UBound(tYCols) Step 2
        n = n + 1
        ReDim Preserve oXCol(1 To n)
        ReDim Preserve oYCol(1 To n)
        Set oXCol(n) = tYCols(i - 1)
        Set oYCol(n) = tYCols(i)
      Next
    ElseIf tMSG = vbNo Then
      For i = 2 To UBound(tYCols) Step 2
        n = n + 1
        ReDim Preserve oXCol(1 To n)
        ReDim Preserve oYCol(1 To n)
        Set oYCol(n) = tYCols(i - 1)
        Set oXCol(n) = tYCols(i)
      Next
    Else
      GoTo ErrorHandler
    End If
    SelectedColIntoXYPair = n
  End If
  

  '헤더 갯수를 입력받도록 하고, 헤더가 없다면 0으로 처리합니다. 헤더에 해당하는 행은 숫자가 입력되어 있더라도 fitting할 때 제외시키도록 합니다.
  tStr = InputBox("Header로 사용할 행의 갯수를 입력하세요.", "Header 수", 0)
  If StrPtr(tStr) = 0 Then GoTo ErrorHandler
  oHeader = Int(Val(tStr))
  
  Exit Function
ErrorHandler:

  '오류가 발생된 경우에는 데이터쌍이 0개로 반환됩니다.
  SelectedColIntoXYPair = 0
End Function

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