엑셀 매크로 Addin 파일 배포

<Addin 파일 다운로드 : MiniTool.xlam>

이제까지 엑셀 관련 매크로들을 모아서 만든 엑셀 추가기능 파일입니다.

일반적으로 추가 기능 파일은 '엑셀 → 옵션  → 추가 기능'에서 추가하면 됩니다만, 위의 링크에 있는 파일은 추가 기능으로 추가하시면 오류가 날 수 있습니다.

위의 파일이 '해찾기 추가기능'을 참조로 하기 때문에 해찾기를 추가하지 않았거나, 해찾기를 추가했더라도 엑셀에서 로딩하는 순서가 달라지면 오류가 발생합니다. 따라서, 아래와 같은 방법으로 추가해주어야 오류없이 작동합니다.

또한, 매크로 파일이기 때문에 보안 센터 설정에서 매크로가 실행될 수 있도록 설정해주어야 합니다.

악성코드는 전혀 심어두지 않았으니 안심하셔도 됩니다만, 백신 프로그램에서 어떻게 반응할지는 모르겠네요~

<설치법>

1. 다운로드받은 파일을 임의의 폴더에 저장해둡니다. 이때, 해당 폴더에는 위의 파일을 제외한 다른 파일이 있으면 안됩니다.

2. 엑셀을 열어서 '엑셀 → 옵션 → 고급'을 열어서, 아래로 죽~ 스크롤하시면 '일반'이 나오며, '시작 시 다음 위치의 모든 파일 열기(L)'에 위의 추가 기능 파일이 저장된 폴더 경로를 지정해둡니다.

엑셀을 껐다가 다시 열면, 맨 우측에 아래 그림처럼 'MiniTool'이라는 리본 메뉴가 생성됩니다.

만약 설치 후 엑셀 실행시 오류가 발생하면, 엑셀을 다시 껐다가 켜면 자동으로 '해찾기 추가기능'이 등록되고 정상작동합니다만, 계속 오류가 뜬다면, 엑셀의 추가기능에서 수동으로 '해찾기 추가기능'을 추가해주시면 됩니다.

<주용도>

아스키형 텍스트 데이터 또는 엑셀 파일을 한꺼번에 열어서 데이터를 수합하고, 주기적으로 정렬된 데이터로부터 일괄적으로 차트를 그리며, 필요하면 smoothing/fitting 합니다. 또한, 만들어진 차트를 배열하고, 한꺼번에 서식을 정리하여 한눈에 경향성을 파악하는 것을 목적으로 합니다.

다만, 이 추가기능에서 계산되는 Smoothing, fitting 결과는 많은 데이터를 빠르게 차트로 그려보고 근사치를 추정하기 위해 개인적인 목적으로 만든 것이므로, 그 결과를 100% 신뢰하시면 안됩니다. 혹시 공식적으로 공개할 목적의 데이터라면 반드시 공학용 소프트웨어를 통해 데이터 검증을 다시 하시기 바랍니다.

차트 기능과 관련해서.. 이 기능들도 제가 자주 쓰는 데이터 형식을 자주 쓰는 차트 형식에 맞춰서 만들어둔 것들이라.. 기본적으로는 분산형 차트를 만들고, 필요하면 다른 형식으로 변환하도록 해두었습니다. 다만, 차트 관련 매크로가 매끄럽지 못할 수 있습니다. 어쨋거나, 엑셀이 그래프용 도구는 아니라서 정밀한 작업은 조금 어렵지만, 다량의 데이터를 한꺼번에 처리해서 경향성 파악하고 원하는 데이터만 추출하는 용도로 사용하는 것은 가능할 것입니다.

혹여, 엑셀에서 서식에 맞게 문서 편집을 목적으로 하신다면, 이러한 기능들은 거의 없습니다.

<사용법>

엑셀을 이용해서 수치 데이터 작업이나 그래프를 많이 그리시는 분들이라면 사용법을 쉽게 이해하실 것으로 생각됩니다. 사실, 데이터나 그래프 작업은 대부분 공학용 소프트웨어를 쓰시겠지만, 저처럼 사용하기 곤란한 환경이 아니라면 엑셀에서 이런 작업을 하실 일이 별로 없을 수도 있습니다.

대부분의 기능들은 각 단계마다 Msgbox나 Inputbox로 처리해 두었기 때문에 순서대로 클릭하며 따라가면 되고(오히려 익숙해지신 분들에겐 그게 더 귀찮은 일이 될 수도 있겠지만..) 그 용도가 뭔지 모르시는 분들은 사용할 필요가 없는 기능일 수 있으니 무시하시면 되겠습니다.

<주의점>

매크로가 실행된 후에는 Ctrl+Z로 실행취소가 되지 않습니다.

가급적 데이터가 손실되지 않도록 데이터가 있는 영역에 '덮어씌우기' 작업은 거의 없도록 했습니다만, 데이터 출력 범위를 선택하는 과정에서 데이터 덮어씌우기가 되면 실행취소가 되지 않으니 주의하시기 바랍니다.

사용하시다가 필요한 기능이 있거나 문제가 확인되시면 언제든지 댓글로 달아주시면 수정해보도록 하겠습니다. 다만, 제가 프로그래머가 아니다보니.. 얼마나 충실하게 답변을 드릴 수 있을지는 모르겠네요.

-----------------------------------------------

'22.3.14 수정 : Function Plot과 이름 관리 기능을 추가

-----------------------------------------------

'22.6.4 수정 : 차트 일괄 작업 기능 업데이트, 연속해찾기 등 일부 버그 수정

-----------------------------------------------

'22.7.28 수정 : 차트 그리기 추가(히스토그램, 박스차트 등), 다중 회귀 기능 추가

가로/세로 방향 데이터 정렬하기

엑셀에 기본적으로 제공되는 정렬기능은 열방향(세로방향)으로 정렬하도록 되어있습니다.

만약, 각 열의 데이터를 정렬하려고 한다면, 아래와 같이 옵션창을 열어 '위쪽에서 아래쪽' 대신 '왼쪽에서 오른쪽' 옵션을 선택하면 가로방향으로 정렬이 가능합니다.

가로방향으로 정렬이 가능하다는 것 자체를 모르는 분들도 많이 계시기도 하고, 정렬 기능을 자주 사용하는 경우에는 정렬기준을 찾아서 옵션을 선택하는 것이 생각보다 번거로운 작업이기도 합니다.

작업영역을 선택하고, 가로/세로 방향으로 정렬하는 기능을 조금더 직관적이고 간단하게 작업하기를 원한다면 아래와 같은 매크로를 사용해볼 수도 있습니다. 개인별 작업 방식에 따라 적당히 수정해서 사용하신다면 좋을 듯 하여 소개해드립니다.

---------------------------------------------------------------

'아래 함수는 사용자가 선택한 여러 개의 셀, range를 각각의 행 또는 열로 나눠주는 함수입니다.

'Ctrl, Shift 버튼과 마우스 버튼을 조합하여 선택된 여러 영역을 선택된 순서대로 행이나 열로 나눠주는 기능이며, 정렬에 반드시 필요한 함수는 아닙니다.

Function DivideLines(iRange As Range, iColumnDir As Boolean) As Range()
  On Error GoTo ErrorHandler
  Dim tLine() As Range, i As Long, j As Long, n As Long
  n = 0

  Select Case iColumnDir
    Case True
      For i = 1 To iRange.Areas.Count
        For j = 1 To iRange.Areas(i).Columns.Count
          n = n + 1
          ReDim Preserve tLine(1 To n)
          Set tLine(n) = iRange.Areas(i).Columns(j)
        Next
      Next
    Case False
      For i = 1 To iRange.Areas.Count
        For j = 1 To iRange.Areas(i).Rows.Count
          n = n + 1
          ReDim Preserve tLine(1 To n)
          Set tLine(n) = iRange.Areas(i).Rows(j)
        Next
      Next
  End Select
  DivideLines = tLine
ErrorHandler:
  Erase tLine
End Function

---------------------------------------------------------------

Sub SortSelectedRange()
  On Error GoTo ErrorHandler
  Dim tSh As Worksheet, tRange As Range, tSelKey() As Range, tKey() As Range, tOrder(), tOrientation, tMSG
  Dim i As Long, j As Long, n As Long
  Set tSh = ActiveSheet

  '매크로를 실행하기 직전에 선택된 영역이 단일 셀이라면, 정렬이 필요없습니다. 따라서, 선택된 셀이 위치한 전체 영역을 재선택해줍니다.
  If Selection.Rows.Count = 1 And Selection.Columns.Count = 1 Then Selection.CurrentRegion.Select

  '만약, 선택된 영역이 현재 시트의 전체 사용영역을 벗어난 경우라면, 현재 사용영역까지만 작업 영역으로 변경해줍니다.
  Set tRange = Application.Intersect(Selection, tSh.UsedRange)

  '정렬을 여러개의 영역에서 작업하는 경우는 거의 없기 때문에 1개의 영역으로만 선택하도록 설정해줍니다.
  If tRange.Areas.Count > 1 Then MsgBox "1개의 영역만 선택하세요.", vbInformation, "선택 오류": GoTo ErrorHandler

  '선택영역을 가로/세로 방향으로 정렬할지 확인합니다.

  ReDim tSelKey(0)
  n = -1
  If MsgBox("세로 방향으로 정렬하시겠습니까?" & vbCrLf & " -Yes : 세로방향 정렬(▼), -No : 가로방향 정렬(▶)", vbYesNo, "정렬 방향") = vbYes Then
    tOrientation = xlSortColumns

  '선택영역을 세로방향으로 정렬하고자 한다면, 정렬 기준이 되는 열을 Ctrl, Shift 조합하여 여러 셀을 입력받습니다.
    Set tSelKey(0) = Application.InputBox("정렬할 Key값이 포함된 열을 선택하세요.", "열 선택", tRange.Columns(1).Address, Type:=8)
    tSelKey = DivideLines(tSelKey(0), True)

  '정렬기준행이 현재 선택 영역 내에 있는지 확인하여, 제대로 선택된 정렬 기준을 key로 정의해줍니다.
    For i = LBound(tSelKey) To UBound(tSelKey)
      If HasIntersect(tSelKey(i).EntireColumn, tRange) Then
        n = n + 1
        ReDim Preserve tKey(n)
        Set tKey(n) = Application.Intersect(tSelKey(i).EntireColumn, tRange)
      End If
    Next
  Else

  '가로 방향으로 정렬한다면, 정렬 기준 행을 입력받을 수 있습니다.
    tOrientation = xlSortRows
    Set tSelKey(0) = Application.InputBox("정렬할 Key값이 포함된 행을 선택하세요.", "행 선택", tRange.Rows(1).Address, Type:=8)
    tSelKey = DivideLines(tSelKey(0), False)
    For i = LBound(tSelKey) To UBound(tSelKey)
      If HasIntersect(tSelKey(i).EntireRow, tRange) Then
        n = n + 1
        ReDim Preserve tKey(n)
        Set tKey(n) = Application.Intersect(tSelKey(i).EntireRow, tRange)
      End If
    Next
  End If

  '정렬 기준이 되는 행/열이 없다면, 작업 종료하고, 정렬 기준이 있다면, 각 정렬 기준을 오름차순, 내림차순으로 정렬할지 확인합니다.
  If n < 0 Then
    MsgBox "정렬할 Key값이 포함된 행/열은 선택 영역 내에 포함되어야 합니다.", vbInformation, "선택 오류"
    GoTo ErrorHandler
  Else
    ReDim Preserve tOrder(UBound(tKey))
    For i = 0 To UBound(tKey)
      tKey(i).Select
      tMSG = MsgBox(i + 1 & "번째 Key값으로 오름차순 정렬 하시겠습니까?" & vbCrLf & " Yes : 오름차순, No : 내림차순, Cancel : 작업취소", vbYesNoCancel, "정렬 방법")
      If tMSG = vbCancel Then GoTo ErrorHandler
      If tMSG = vbYes Then tOrder(i) = xlAscending Else tOrder(i) = xlDescending
    Next
  End If
  

  '현재 작업시트에서 대상 영역, 정렬 방법 등을 지정해주고 내장된 정렬 기능을 실행합니다.
  With tSh.Sort
    .SortFields.Clear
    For i = 0 To UBound(tKey)
      .SortFields.Add Key:=tKey(i), SortOn:=xlSortOnValues, Order:=tOrder(i), DataOption:=xlSortNormal
    Next
    .SetRange tRange
    .Header = xlNo
    .Orientation = tOrientation
    .Apply
  End With
ErrorHandler:
  Erase tKey, tSelKey, tOrder
End Sub

---------------------------------------------------------------

평소에 세로 방향 정렬 뿐만 아니라, 가로 방향 정렬을 수시로 사용해야하는 분이라면, 위의 매크로를 리본 메뉴 버튼으로 지정해두고 사용하면 엑셀 내장된 정렬기능보다는 설정이 조금은 간단해질 것 같습니다.

Module 9. FFT Filter (Fast Fourier Transform) - (5)Gaussian 필터시 가장자리 데이터의 처리

모든 데이터를 이용한 smoothing 기법에서 나타나는 보편적인 문제점 중 하나는 바로 데이터의 끝부분에 대한 처리 문제입니다.

일정 구간의 산술 평균값을 계산하거나, 다항식으로 fitting하여 근사값을 구한 방식, 혹은 가중치를 전/후 데이터에 가중치를 곱하여 평균을 구하는 방식(Convolution)은 특정 위치에서 노이즈를 제거하고, 참값을 예측하기 위해 측정된 위치의 주변 데이터를 이용하여 통계적인 값을 계산해냅니다.

데이터 집합의 중간 위치에 있는 데이터는 일정 갯수 이상의 전/후 데이터가 존재하기 때문에 무리없이 계산이 되지만, 가장자리부분, 즉 측정된 데이터의 첫번째 혹은 마지막 위치의 데이터는 그 주변 데이터가 없거나, 비대칭적으로 분포하게 됩니다. 따라서, 중간 위치에 있는 데이터의 예측방법과 가장자리에 있는 데이터의 예측방법이 동일하다면 문제가 발생할 수 있습니다.

아래의 그림은 0.01 주기로 측정된 데이터를 Cut-off를 1이 되도록 100개의 데이터 주기를 Cut-off로 입력한 후, Gaussian 필터로 노이즈를 제거한 결과입니다.

가장자리에서 충분히 먼 구간인 0.5<X<9.5 구간에는 필터링 결과(빨간색 실선)가 노이즈를 잘 제거한 것으로 보입니다만, X<0.5 구간과 X>9.5 구간(초록색 박스 표시)은 실측값에서 많이 벗어나는 것을 알 수 있습니다.

통상적으로 FFT 변환을 이용하여 Gaussian 필터를 적용하는 경우, cut-off의 0.5배에 해당하는 가장자리 구간은 예측값을 신뢰할 수 없으며, 이 구간의 예측값이 참값에 수렴할 수 있도록 여러가지 방법을 사용하기도 합니다.

가장자리 보정을 위해 사용되는 대표적인 방법은 다음과 같습니다.

1. 데이터 측정할 때, 측정 시작과 끝 구간에서 Gaussian 필터의 cut-off의 0.5배에 해당하는 구간을 더 측정하는 방법이 있습니다. 즉, 측정된 구간의 가장자리 부분을 절사하는 것이지요. 엄밀히 말해 신뢰할 수 있는 구간만 남기고 삭제하는 것이니, 보정이라고 하기는 어렵지만, 데이터를 측정하기 전에 미리 더 넓은 구간을 측정하도록 계획에 반영하는 것입니다.

2. 어떠한 데이터는 데이터 구간을 확장할 수 없는 경우도 있습니다. 예를 들어, 특정 시간에 변화를 주고, 이 시간 이후의 변화를 측정한다면, 변동이 발생된 시간의 이전 데이터는 변동 발생 후의 데이터와 연장선에 있지 않기 때문에 보정을 위한 데이터로 사용할 수 없습니다. 

이처럼 가장자리 구간의 데이터를 확장할 수 없다면, 가장자리 일정 구간의 데이터로부터 직선, 또는 다항식으로 외삽(extrapolation)하여 cut-off의 0.5배에 해당하는 구간의 가상 데이터를 생성하여 연장선을 그린 후, Gaussian 필터를 적용하는 것입니다. 그러나, 가장자리 데이터가 중간 영역의 데이터에 비해 급격한 기울기를 가지고 변동되는 경우에는 이러한 외삽법을 이용하기 어렵습니다.

3. 가장자리 부분의 데이터를 통상적인 방법으로 외삽이 불가능한 경우, 가장자리로 접근할수록 cut-off 값을 점진적으로 감소시켜서 원데이터에 최대한 근사한 결과를 얻을 수 있습니다. 그러나, 가장자리 부분의 예측값에는 노이즈가 더 많이 포함된다는 문제가 있습니다.

1번 방법을 제외한 나머지 보정 방법은 표준화된 방법은 아닙니다. 가장자리 데이터에 대한 보정은 데이터 형태를 확인하고 중간 부분의 예측값과 가장자리 부분의 예측값을 부드럽게 연결하기 위한 인위적인 보정일 뿐이기 때문에, 실측 결과와 비교하여 예측값이 적절한지 판단하면서 사용할 필요가 있습니다.

Module 9. FFT Filter (Fast Fourier Transform) - (4)Gaussian 필터

FFT 필터 관련 첫글에 첨부해두었던 mcrFFT.bas를 엑셀에서 열어보시면 몇 개의 함수로 구성되어있습니다. 함수명을 보시면 대략적인 기능을 알 수 있을 것입니다.

N개의 실수로 구성된 데이터를 Fourier 변환하기 위해서는 FFTR1D 함수를 사용하면 됩니다.

인덱스가 (0~N-1)인 데이터셋을 입력하여 FFT를 실행하게 되면, (0~N-1)의 인덱스를 갖는 복소수 배열이 출력됩니다.

동일한 방법으로 (0~N-1)의 인덱스를 갖는 복소수를 FFTR1DInv 함수로 역변환하게 되면, 실수 배열을 출력해줍니다. 따라서, 전체적인 계산 순서를 정리하면 아래와 같습니다.

실수 배열(입력) → (FFTR1D) → 복소수 배열 → (Filter 적용) → (FFTR1DInv) → 실수 배열 (출력)

FFTR1D 함수와 FFTR1DInv 함수는 위의 모듈에 이미 만들어져 있으므로, 데이터 형식에 맞게 Filter를 어떻게 만들 것인지만 고민하면 됩니다.

일반적으로 많이 사용되는 Gaussian 필터의 경우, 아래와 같이 작성할 수 있습니다.

---------------------------------------------------------------

'Highpass 또는 Low Pass 필터인 경우

Public Function FFT_GaussianFiltered(iData() As Double, iCutOff As Double, Optional iHighpass As Boolean = False) As Double()
  Dim tFFT() As typeXYPair, tFFTInv() As Double, tNC As Double, tVal As Double, n As Long, i As Long

  '입력받는 데이터는 0~n까지 index를 가짐
  n = UBound(iData)

  '실수값을 갖는 1차원 배열을 FFT 실행하여 복소수 형식의 데이터를 출력받음
  FFTR1D iData, n + 1, tFFT

  'CutOff값을 기준으로 공통으로 계산이 반복되는 부분만 미리 계산하여 변수이 저장해둠 (단순히 계산 횟수를 줄이기 위한 방법)
  tNC = iCutOff / n

  'High Pass인 경우, 높은 주파수(i가 증가하는 경우)에서는 투과율이 1에 수렴하고, Cutoff에서는 0.5, 낮은 주파수에서는 0에 수렴하는 filter값을 가지며, low pass인 경우에는 반대가 됨
  Select Case iHighpass
    Case True
      For i = 0 To n
        tVal = 1 - 2 ^ (-(i * tNC) ^ 2)
        tFFT(i).X = tFFT(i).X * tVal
        tFFT(i).Y = tFFT(i).Y * tVal
      Next
    Case False
      For i = 0 To n
        tVal = 2 ^ (-(i * tNC) ^ 2)
        tFFT(i).X = tFFT(i).X * tVal
        tFFT(i).Y = tFFT(i).Y * tVal
      Next
  End Select

  '필터를 곱한 결과를 FFT 역변환을 실시합니다.

  FFTR1DInv tFFT, n + 1, tFFTInv
  FFT_GaussianFiltered = FFTR1DInv
  Erase tFFT, tFFTInv
End Function

---------------------------------------------------------------

'Band pass 필터인 경우, 낮은 주파수의 cutoff값과 높은 주파수의 cutoff값을 입력받은 후, 두 cutoff 사이의 영역만 투과시키는 filter가 됨
Public Function FFT_GaussianBandFiltered(iData() As Double, iCutOff1 As Double, iCutOff2 As Double) As Double()
  Dim tFFT() As typeXYPair, tFFTInv() As Double, tNS As Double, tNC As Double, tVal As Double, n As Long, i As Long
  n = UBound(iData)
  FFTR1D iData, n + 1, tFFT
  tNS = iCutOff1 / n
  tNC = iCutOff2 / n
  For i = 0 To n
    tVal = 2 ^ (-(i * tNS) ^ 2) * (1 - 2 ^ (-(i * tNC) ^ 2))
    tFFT(i).X = tFFT(i).X * tVal
    tFFT(i).Y = tFFT(i).Y * tVal
  Next
  FFTR1DInv tFFT, n + 1, tFFTInv
  FFT_GaussianBandFiltered = FFTR1DInv
  Erase tFFT, tFFTInv
End Function

---------------------------------------------------------------

위의 계산에서 Cut-Off라는 용어를 계속 사용하였습니다. 여러가지 측정이나 계산목적으로 FFT 변환을 사용해보신 분들은 cut-off라는 용어가 익숙하실 수 있습니다만, 일반인들이라면 cut-off, 주파수... 등의 개념이 익숙하지 않을 수 있기에 약간의 부연 설명을 드립니다.

시간 함수를 기준으로 설명을 드리면, 예를 들어, 1초 간격으로 수집한 데이터가 있다고 하겠습니다. 이때, 데이터는 0초에 측정된 데이터, 1초에 측정된 데이터, ..., n초에 측정된 데이터로 구성됩니다. 만약, 이 데이터를 FFT 변환하여 Cos, Sin 함수로 분해해주었다고 하겠습니다.

FFT 변환을 하게 되면, 변환결과는 전체 함수의 평균값(주파수가 0), 주파수가 1인 Cos, Sin 함수, 주파수가 2인 Cos, Sin 함수, ...., 주파수가 n인 Cos, Sin 함수로 나눠지게 되며, 각 함수들의 진폭을 데이터 배열에 할당해줍니다. 따라서, index가 낮은 데이터는 낮은 주파수, index가 높은 데이터는 높은 주파수의 진폭값이 됩니다. (여기에서 주파수가 1, 2, 3,... 이라고 하는 의미는 측정된 전체 구간을 주기로 하는 함수, 1/2 구간을 주기로 하는 함수, 1/3 구간을 주기로 하는 함수.... 를 의미합니다.)

만약, 주파수가 10 이상이 되는 Cos, Sin 함수는 제거(filter=0)하고, 낮은 주파수의 함수만 다시 결합하게 되면, 전체적으로 노이즈가 제거된 형태의 함수를 얻게 되는데, 이러한 필터를 low pass filter라고 하고, 반대로 high pass filter의 경우에는 높은 주파수의 함수만으로 조합되므로 노이즈만 남고 background가 제거되는 효과가 있습니다.

비록 주파수는 시간의 역수 단위, 주기는 시간 단위를 갖게 되는 것입니다. 주파수가 비록 익숙한 용어이기는 하나, 1초 단위로 측정된 데이터를 주파수 0.1Hz를 기준으로 필터링한다고 하면, 쉽게 그 의미가 와닿지 않습니다. 이때, high pass / low pass filter의 기준값이 되는 주파수에 해당하는 주기를 cut-off라고 부릅니다. 1초 단위로 측정된 데이터를 10초의 주기를 갖는 cos, sin 함수를 기준으로 장주기 데이터만 걸러 낸다거나, 단주기 데이터만 걸러낸다고 하면 조금은 상상하기 쉽지요.

다시말해, high pass/low pass 함수를 작성할 때, 입력값을 주파수, 혹은 파수번호로 입력하지 않고 cut-off를 입력으로 해두는 이유는 데이터의 X변수와 필터링하려는 기준인 cut-off가 동일한 단위를 갖는 값이기 때문에 직관적으로 이해하기 편하기 때문입니다.

위의 Gaussian 필터 함수에서 데이터를 입력한 후, cut-off를 10으로 지정한다면, 10개의 데이터를 주기로 갖는 cos, sin 함수를 기준으로 장주기 혹은 단주기 영역을 제거해주는 역할을 하게됩니다.

Module 9. FFT Filter (Fast Fourier Transform) - (3)엑셀에서 Fourier 변환하기

엑셀에서도 Fourier 변환이 가능합니다. 엑셀에서 Fourier 변환을 하기 위해서는 아래와 같이 추가 기능에서 분석 도구팩을 설치한 후, 리본 메뉴의 데이터 오른쪽 끝에 생성된 '데이터 분석' 버튼을 눌러서 해당 기능을 사용할 수 있습니다.

엑셀 워크시트에서 위의 추가 기능 버튼을 누르면, 아래와 같이 여러가지 통계 분석 기능을 사용할 수 있고, '푸리에 분석' 기능을 사용할 수 있습니다.

엑셀의 추가 기능에 포함된 '푸리에 분석'은 비록 Fourier 변환과 역변환을 할 수 있지만, 데이터 갯수에 제한이 있습니다. 만약 데이터 갯수가 2^n 개에 해당하지 않는다면 아래와 같은 오류 메세지와 함께 변환이 되지 않습니다. 

2^n 개의 데이터가 아니라면, 부족한 셀 갯수만큼 '0'을 채워서 계산하는 꼼수를 쓸 수도 있겠습니다만, 이는 FFT 변환 후 frequency 혹은 파수값(wavenumber)와 연결짓기가 쉽지 않을 수 있으며, 데이터 갯수가 일정하지 않은 데이터들을 취급해야한다면, 매번 데이터 갯수를 맞춰주는 것도 많은 수작업이 필요한 일이 될 수도 있습니다.

다음에는 예전 글에서 올려드린 FFT 모듈(bas 파일)을 이용하여 임의의 데이터들에 대해 FFT 변환하는 매크로를 작성해보도록 하겠습니다.

Module 9. FFT Filter (Fast Fourier Transform) - (2)FFT를 이용한 filtering 개념

Fourier 변환이란, 임의의 함수를 주기가 다른 sin, cos 함수 혹은 복소 exp 함수의 합으로 분해하는 과정이라고 할 수 있습니다. 다시말해, 어떤 함수에 특정 주파수 혹은 주기를 갖는 sin, cos 함수가 얼마나 포함되어있는지를 각 주파수별로 분해하는 과정이며, Fourier 역변환은 분해된 sin, cos 함수를 합쳐서 원래의 함수로 되돌리는 과정이라고 할 수 있습니다.

만약, 노이즈가 포함된 어떤 함수 혹은 실측 데이터가 있다고 할 때, FFT 변환을 하여 주파수별 성분으로 나누었다고 하면, 노이즈는 매우 짧은 파장 혹은 높은 주파수를 갖는 함수로 나타나고, 함수의 개략적인 형상은 긴파장 혹은 낮은 주파수 영역의 함수로 나타나게 됩니다.

높은 주파수 성분은 가중치를 0에 근접한 값을 곱해주고, 낮은 주파수 성분의 성분은 1에 근접한 값을 곱해준 후, 이들을 다시 합쳐서 (Fourier 역변환) 함수를 만들어주게 되면, 노이즈가 제거된 함수를 얻을 수 있습니다.

예전에 소개시켜드린 바와 같이 Savitzky-Golay smoothing이나, 인접한 데이터의 평균값을 이용하는 여러가지 smoothing 기법과 유사하다고 볼 수 있습니다만, FFT 변환을 이용하면 보다 다양한 작업이 가능합니다.

위에서와 같이 고주파 영역을 제거함으로써 단순히 노이즈를 제거하는 방법도 가능하지만, 저주파 영역을 제거하게되면, 불필요한 형상을 제거하고 높은 주파수 영역의 성분들만 추출해 낼수도 있습니다. 전자의 경우는 고주파를 제거하고 저주파 영역만 투과시키는 개념이기 때문에 low pass filter라고 하고, 후자의 경우에는 저주파를 제거하고 고주파만 투과시키게 되므로 high pass filter라고 합니다. 만약, 중간 영역만 남겨두고 고주파와 저주파를 모두 제거한다면 band pass filter라고 합니다.

이때, filter의 형태는 특정 주파수 이상은 0, 특정 주파수 이하는 1로 계산해주는 계단 모양 필터도 가능하고, 주파수에 따라 특정한 함수를 적용해 줄 수도 있습니다.

특별한 목적이 아니라면 임계 주파수를 기준으로 Gaussian 함수를 적용한 Gaussian 필터를 사용하는 것이 보편적입니다.

그 이외에도 임의의 데이터에 convolution 계산이 필요하다면, 비록 FFT 또는 Inverse FFT가 이나라도 계산이 가능하지만, 매우 많은 양의 계산이 수반되어야하는 반면, FFT를 이용한다면 그 연산량이 크게 감소할 수 있기 때문에 엑셀에서 FFT를 수행할 수 있다면 매우 유용할 수 있을 것입니다.

Module 9. FFT Filter (Fast Fourier Transform) - (1)시작하며

푸리에 변환(Fourier Transform)이란, 해당 함수를 각각의 파장(혹은 주파수) 성분으로 분해하는 작업이고, 역변환은 모든 파장 또는 주파수 성분을 조합하여 실제 함수로 재생성하는 과정이라고 이해하시면 됩니다. 그러나, 이러한 변환을 위해서는 sin 및 cos 함수를 곱하여 함수 구간 전체에 대해 적분을 실시해야 합니다. 문제는 1개 파장 또는 주파수 성분에 대해 1번의 적분이 이루어져야하고, 모든 파장 또는 주파수에 대해 계산을 하기 위해서는 데이터수의 제곱에 비례하는 연산을 필요로 합니다.

연산 횟수를 줄이기 위해 다양한 알고리즘에 제안되었고, Cooley-Tukey 알고리즘이 대표적인 FFT 알고리즘입니다. Cooley-Tukey 알고리즘은 데이터수 N이라고 할 때, 연산 시간이 N log(N)으로 감소하기 때문에 FFT 필터에 많이 응용되고 있으며, 공개된 소스코드도 많이 있습니다. 만약, 1만개의 데이터가 포함되어있다면, 푸리에 변환을 위해 1억회의 연산이 필요하다면, FFT 변환은 약 4만회의 연산이면 끝나기 때문에 속도 측면에서 대략 2500배 차이가 나며, 데이터 갯수가 증가할수록 알고리즘에 따른 계산 속도 차이가 더욱 가중됩니다. (이전에 소개드린 정렬 알고리즘과 유사한...)

FFT(Fast Fourier Transform)은 과학/공학분야에서 매우 다양하게 활용되는 변환 중 하나입니다. 예를 들어, 전자 신호에서 불필요한 노이즈를 제거하거나, 사진 데이터를 흐리게 하거나 혹은 사진 내 경계선을 찾아내는 필터도 FFT 필터를 사용합니다. 재료학에서는 조도(거칠기, Roughness)를 계산하기 위해 형상 factor와 노이즈를 제거하는 용도로도 FFT 필터를 사용합니다. 뿐만 아니라, 앞서 설명드렸던 Savitzky-Golay filter와 같이 노이즈를 포함하는 데이터를 smoothing 하기 위한 filter로도 사용할 수 있습니다.

FFT filter가 이렇게 다양한 응용분야를 가졌음에도 불구하고, 데이터 수가 증가할수록 계산량이 급격하게 증가하기 때문에 프로그래밍에 능숙하지 않은 분들이 이러한 필터를 쉽게 구현하기가 쉽지 않습니다. 

그런데, 공개된 소스 코드들의 대부분은 데이터 갯수가 제한적인 문제가 있습니다. 즉 데이터 갯수가 2^n 개인 경우에 대해서만 계산이 가능하고, 임의의 데이터 갯수를 갖는 경우에 대해서는 코드를 구하기가 쉽지 않더군요. 아래 링크에 데이터 갯수에 상관없이 엑셀에서 FFT 계산이 가능한 소스 코드 하나를 찾아 올려두었습니다.

엑셀용 FFT 모듈 다운로드

사실 몇년 전에 구글 검색 중 찾았던 것인데.. 어디서 받았었는지 원본 링크를 잊어버렸네요. 어쨋거나, 위의 링크를 클릭하면 bas 파일을 받을 수 있습니다. 엑셀의 vb 에디터에서 파일 불러오기를 이용하여 다운로드받은 모듈을 삽입한 후, 매크로를 생성하면 됩니다.

위의 모듈에는 FFT를 위한 함수(function)만 포함되어 있습니다. 실행을 위한 Sub 프로시저는 포함되어있지 않으며, 매크로 실행 창을 열더라도 어떠한 실행 매크로도 생성되지 않습니다. 따라서, 필터링을 위한 매크로는 직접 생성해야합니다.

다음 글에서부터는 위의 모듈을 이용하여, FFT 변환/역변환 및 Gaussian 필터를 적용하는 매크로를 작성하도록 하겠습니다.

Module 8. 해찾기 기능 활용 - (5)연속 해찾기 응용

해찾기의 응용 중 하나로, 복잡한 방정식의 해를 수치적으로 구하는 것입니다. 그런데, 1개의 해가 아니라 궤적을 구하려고 한다면, 매개 변수의 값을 변경하면서 반복해서 수치해를 구해야합니다.

예를 들어, 인산(H3PO4)의 중화곡선을 구한다고 해보겠습니다. 인산은 pH에 따라 H+ 이온이 해리되지 않은 상태에서부터 3개의 H+ 이온이 모두 해리된 상태가 가능합니다. 황산, 구연산 등의 다가산의 경우에는 모두 마찬가지입니다. 염산과 같이 1가의 강산인 경우, HCl의 투입량에서 NaOH나 KOH의 염기 물질의 투입량을 빼면 바로 H+ 이온 농도를 구할 수 있으므로, 복잡한 과정이 필요없습니다. 그러나, 인산과 같이 pH에 따라 해리되는 수소의 갯수가 다른 경우, 아래와 같은 산해리 평형상수(pKa)로부터 pH에 따라 각 성분의 해리 수준을 계산해 주어야 합니다.

여기에서 계산해보려는 한가지 예로, 0.5mol의 인산이 포함된 용액에 NaOH를 투입할 때, pH 변화(적정곡선)를 계산하고, pH에 따라 어떠한 이온 상태가 가장 안정한 상태인지 확인하는 것입니다.

인산을 투입하게 되면, pH가 증가할수록 H3PO4 → H2PO4^- → HPO4^2- → PO4^3- 와 같이 수소 이온이 분리됩니다. 이 때, 각각의 평형 상수를 Ka1, Ka2, Ka3라고 하고, 이 값의 로그값에 음수를 취해서 pKa(=-log Ka)값이라고 하여 화학 데이터로 제공하게 됩니다. 구글 검색을 해보니, 이 값이 각각 2.14, 7.20, 12.37이라고 합니다. 평형상수로 환산해보면, 10^-2.14~10^-12.37이 된다는 뜻입니다. 농도 계산을 하기 위해 위의 농도 평형식이 3개이고, 여기에 포함된 숫자의 크기가 10^10 배가 차이가 나기 때문에 일반적인 연립방정식을 풀어서는 결과를 얻기가 쉽지 않습니다.

이를 계산하기 위해 아래와 같이 엑셀 시트를 작성합니다.

우선 인터넷으로부터 얻은 pKa와 물의 해리상수를 왼쪽편에 정리해둡니다. 오른쪽에는 구하고자하는 pH 범위를 우선 지정해줍니다. (pH 1~14까지 0.1 간격으로 연속데이터를 만들어줍니다.)

G열에는 pH의 정의를 이용하여 H+ 농도([H+]=10^-pH)를 입력해줍니다.

I~L열에는 인산의 농도를 넣어 줍니다. 3개의 농도 평형식이 있기 때문에 4개 중 1개의 화학종 농도가 결정되면, 나머지는 자동으로 계산됩니다. 이들 농도의 총합(I~L)이 투입한 초기농도와 같아야 하므로, 이들의 합과 초기 투입량의 차이를 N열에 넣어두고, 이 값이 0이 되도록 I~L 열 중 1개를 변경해주면 됩니다. 1개의 목표셀(N열)의 값이 0이 되도록 I~L 열 중 1개의 변경셀을 바꾸는 해찾기 문제가 되는 것입니다.

수치 계산을 염두에 둔다면, 2번째 또는 3번째의 크기를 변경셀로 사용하는 것이 좋습니다. 만약 1번째 열([H3PO4] 농도)을 변경셀로 둔다면, 4번째 열을 계산할 때에는 Ka1*Ka2*Ka3를 곱한 숫자가 계산에 반영되기 때문에 제대로 계산이 되지 않을 수 있습니다.

끝으로, OH- 농도는 pH로부터 구한 H+ 이온 농도와 물의 해리상수를 이용하여 구할 수 있고, H+, OH- 및 인산 농도계산이 완료되면, 전기적 중성을 유지하기 위해 필요한 Na+ 또는 Cl- 농도를 계산하여, 염산 또는 수산화나트륨 투입량을 계산해줄 수 있습니다.

1개의 pH에 대해서 해찾기 식을 만들었으므로, pH 값을 계속 변경하여 해찾기를 하면, 중화곡선을 얻을 수 있습니다.

연속해찾기를 실행한 후, 목표셀을 N열의 전체 구간에 대해 지정해주고, 변경셀은 목표셀 중 1번째 셀에 대해서만 지정해줍니다. 목표셀의 목표값은 0이 되는 것이므로 지정값, 0이 되도록 입력하고, 체크박스 '연속 해찾기'를 체크한 상태에서 '해찾기 시작'을 클릭합니다.

계산이 완료되고 나면, 목표셀 값(N열)이 지정값(0)과 잘 일치하는지 확인합니다. 지정값을 0으로 하는 경우, 계산이 이상없이 잘 되었더라도 0은 아니지만, 0에 근접한 매우 작은 숫자로 마치는 경우가 대부분입니다.

연속해찾기를 이용하여, 위 그림과 같이 중화곡선과 pH에 따른 주요 인산 이온의 농도에 대한 그래프를 얻을 수 있습니다.

Module 8. 해찾기 기능 활용 - (4)연속 해찾기 매크로

연속해찾기 : 엑셀 파일 다운로드

엑셀의 해찾기가 공학적/수학적 계산용으로 사용하기엔 손색이 없는 기능임은 분명하지만, 1번에 1개 셀에 대해서만 실행한다는 것과, 특정값에 수렴하지 않더라도 반복횟수 제한에 걸려 계산이 완료된다는 점에서 반복 작업이 필요한 사람에게는 꽤나 불편한 일입니다.

여러 개의 셀에 대해 일괄적으로 해찾기가 가능하다면 반복작업을 엑셀에 맡겨두고 사용자는 그만큼 여유시간이 생길 수 있겠지요.

상단에 연속해찾기 매크로 파일(xltm 확장자)을 구글드라이브에 업로드해두었습니다. 파일을 열고, 보기 또는 개발도구 리본메뉴에서 '매크로'를 실행해보면, 새로운 '해찾기' 창이 생성됩니다. 

각 항목은 엑셀의 '해찾기' 창에 입력하는 항목을 그대로 가져온 것이고, 맨 아래 '연속 해찾기'와 '제한조건 위치 고정'이 추가되었다는 점과, 목표셀에 여러개의 셀을 한꺼번에 지정한다는 점이 차이가 있습니다.

목표셀은 해를 구하려는 대상 셀을 모두 지정해두고, 변경셀, 제한조건 등에 입력되는 모든 셀 주소는 1번째 목표셀에 대한 상대위치로 주소를 입력해줍니다. (그냥 1번 목표셀에 대한 변경셀, 제한조건을 지정해주면 됩니다.)

2번, 3번..목표셀에 대하여 해찾기를 실행할 때에는 변경셀과 제한조건을 다시 상대위치로 옮겨서 해찾기를 실행합니다. 예를 들어, 목표셀을 A1:C1, 변경셀을 A2:A3로 지정했다면, 목표셀 A1, 변경셀 A2:A3를 계산한 후, 목표셀 B1, 변경셀 B2:B3를 계산하고, 마지막으로 목표셀 C1, 변경셀 C2:C3로하여 총 3번의 해찾기를 수행합니다.

제한조건에 입력되는 주소 역시, 목표셀의 상대위치로 자동변경되지만, 필요에 의해 모든 해찾기 계산에서 제한조건을 목표셀에 무관하게 고정하겠다고 한다면, 창 맨 아래의 '제한조건 위치 고정'을 체크하시면 목표셀에 대한 상대주소로 변경되지 않습니다.

해찾기 옵션은 엑셀의 기본 해찾기 메뉴 항목과 동일합니다만, 최대계산횟수에 100x5와 같은 형식으로 되어있습니다. 즉 한꺼번에 100번의 반복계산을 수행하고, 결과를 확인했을 때 완전히 수렴되지 않았다면 다시 100번을 계산하고, 수렴이 되지 않았다면 다시 계산하는 방식으로 최대 5회 수행하라는 의미입니다. 물론 값이 수렴되었다면, 중간에 작업을 마칩니다. 정밀한 계산을 원한다면 반복계산 횟수와 정밀도를 늘려주면 되나, 완전히 수렴하지 않는 경우라면 시간이 오래걸릴 수 있습니다.

매크로의 코드는 암호화하지 않았으니, 필요하신 분은 수정해서 사용하셔도 됩니다.

다음글에서는 연속해찾기를 활용하는 예를 올려드리도록 하겠습니다.

Module 8. 해찾기 기능 활용 - (3)비선형 함수 Fitting

해찾기를 이용해 단순한 수학문제 풀기 이외에도 목표값만 정확하게 설정하면, 함수 fitting도 가능해집니다.

예전 글에서는 선형 또는 다항식 fitting 문제에서는 데이터의 거듭제곱값들을 구하고, 행렬을 만든 후 다항식의 계수를 계산해냈습니다. 다항식의 경우에는 1개의 유일한 fitting 결과가 가능하지만, 비선형 함수는 함수들마다 fitting하는 알고리즘이 달라집니다. 그럼에도 불구하고, 오차 제곱합을 최소화하는 것은 동일합니다.

비선형 함수의 대표적인 사례로 Peak fitting을 예로 들어보겠습니다. 사실 이 부분을 이해하신다면, 직선이나 다항식, 기타 함수로의 fitting은 쉽게 해결할 수 있습니다. Peak fitting으로 사용되는 함수 중 Gaussian 함수가 있습니다. Gaussian 함수는 A*exp(-((x-μ)/σ)^2) 형태입니다. 

아래 그림과 같이 2개의 Gaussian peak과 상수값의 합으로 되어있는 (x, y) 데이터가 있다고 할 때, 상수, y1_fit, y2_fit으로 나눠서 함수를 만들어 줄 수 있고, 각각의 상수들은 별도의 목록으로 오른쪽 셀에 나타내었습니다. y1_fit 함수는 아래 그림처럼 입력해두고, y2_fit과 상수항도 동일하게 둘 수 있습니다.

마지막으로 상수와 2개의 Gaussian 함수의 합을 y_sum열에 입력해둡니다.

Fitting을 위해서는 실측 데이터 y와 계산된 데이터 y_sum의 차가 최소가 되도록 해야하므로, 상수 목록 맨 하단에 Err 항을 만들고, '=sumxmy2(y 범위, y_sum 범위)'를 입력해줍니다. sumxmy2 함수는 해찾기로 함수 fitting 할 때 많이 사용하게 될 함수이며, 두 범위의 값에 대하여 각 셀값의 차이의 제곱합을 구하라는 의미입니다. (=∑(x-y)^2)

끝으로 각 상수들의 초기값을 적절하게 입력하여 peak의 모양을 대략적으로 잡아줍니다.

Err 값을 최소화하기 위해 아래와 같이 오차 제곱합을 목표셀로 지정하고, 변경할 상수 목록을 변경 셀로 지정한 후 해찾기를 실행합니다.

해찾기를 완료하면 아래 그림과 같이 측정 데이터와 fitting된 함수가 거의 일치하게 되며, 2개의 Gaussian으로 peak가 분리되었습니다.

비선형 함수에 대해 해찾기를 실행하게 되면, 반복계산 횟수를 초과하더라도 결과가 완전히 수렴하지 않을 수 있습니다. 목표 셀의 숫자가 더이상 변하지 않을 때까지 해찾기를 반복해서 실행하면 다른 공학 소프트웨어로 계산한 결과와 동일한 결과를 얻을 수 있습니다.

해찾기 결과가 완전히 수렴하도록 하려면, 해찾기 옵션에서 반복 횟수를 충분히 크게 해줄 수도 있습니다. 반복횟수가 100번이 기본값이지만, 만약 1000번으로 바꿨다해도 중간에 값이 수렴하면 엑셀이 반복 계산을 중단하기 때문에 시간 낭비는 발생하지 않습니다.

엑셀에 이렇게 훌륭한 계산 툴이 있음에도 불구하고, 해찾기의 단점은.. 목표셀, 변경셀, 조건식 등을 일일이 수동으로 지정해야합니다. 만약 위와 같은 fitting을 수십개의 데이터에 대해 작업하고자 한다면, 매우 귀찮은 일일 수 있습니다. 만약 데이터 배열이 반복되어 있고, 동일한 포맷으로 해찾기를 반복할 수 있다면, 공학적 계산용으로 활용도가 충분히 높아질 수 있을 것입니다.

반복 해찾기가 가능하다면 할 수 있는 일에 대해서는 다음 글에 소개하도록 하겠습니다.

Module 8. 해찾기 기능 활용 - (2)수학 문제 풀기

해찾기를 이용하여 수학 문제를 푸는 방법에 대해 설명드리려고 합니다.

해석해가 있는 경우이든, 해석해가 없는 경우이든 상관없습니다. 지난 글에서 보여드린 예와 같이 z=exp(x^2)/(x^2+y^2) 함수가 있고, y=1이고, z=10이 되도록 하는 x를 구하고자 한다면, 아래 그림과 같이, x, y, z에 해당하는 셀을 설정하고, z에 해당하는 셀의 함수를 아래와 같이 입력해줍니다. x, y 값이 변경되면 자동을 z값(C2 셀)이 변경될 것입니다.

해찾기를 실행한 후, 목표 셀을 z값에 해당하는 C2로 지정하고, 변경할 셀을 x값에 해당하는 A2 셀로 지정합니다. 값이 음수일수도 있고, 양수일 수도 있기 때문에 '제한되지 않은 변수를 음이 아닌 수로 설정'의 체크를 해제합니다. 변동 범위가 양의 값으로 제한하고 싶다면 그냥 두셔도 됩니다만, 간혹 해찾기가 오류나는 경우에 이 체크 박스를 반드시 확인하셔야 합니다.

해찾기를 눌러주면, x에 해당하는 A2 셀의 값을 변동시켜 해가 찾아지면, 해찾기 결과를 출력해주고 결과를 유지할 것인지 물어봅니다. z값이 10으로 변했고, x값은 1.972237로 구해집니다.

해찾기의 기본 원리는 변경 셀(x)의 값을 일정량 변동시킨 후, 목표 셀(z) 값의 변화를 확인하여 변경 셀값을 증가시킬지, 감소시킬지를 결장합니다. 이때, x값을 얼마나 변경시킬지 결정해야하는데, 미분값(Δz/Δx)을 사용하게 됩니다. 따라서, 변경 셀의 값을 매우 조금만 변동하더라도 목표셀이 민감하게 반응을 하게 되면, 아무리 많이 반복계산을 하더라도 수렴하지 못하는 경우가 있는데, 해찾기 창의 '해법 선택' 옵션을 눌러 해찾기 방법이나, 최대 반복 횟수, 정밀도 등을 지정해 줄 수 있습니다.

단순 함수값을 구해주는 경우 이외에도 다음과 같이 연립방정식을 풀 수도 있습니다. 예를 들어, y=x^2-5*x+2, y = 3*x+2의 교차점을 구한다고 하겠습니다. 직선과 포물선이 만나는 점이 2개 있으므로, 2개의 해가 있을 것입니다. 그림을 그려보면, 대략 0과 10 사이에 2개의 교차점이 생깁니다. 아래 그림과 같이,

A2=-5, A3=10으로 x의 초기값을 주고, B2=A2^2-5*A2+2, C2=3*A2+2로 입력하고, B2, C2를 복사하여 B3, C2에 붙여넣으면 함수가 복사됩니다. 해를 찾기 위해 목표값을 (y2-y1)의 제곱합을 구할 수 있도록 D2에 '=SUMXMY2(B2:B3,C2:C3)'을 넣어줍니다. D2가 0이 되는 방법은 y1=y2인 경우 밖에 없을 것입니다. 혹은 D2가 최소값이 되도록 해를 찾으라고도 할 수 있습니다.

해찾기에서는 초기값이 매우 중요합니다. 초기값이 설정되지 않으면, x=0에서부터 값을 찾아나가기 시작하는데, x=0일 때, 이미 교차점이 되기 때문에 나머지 x값을 찾지 못합니다. 따라서, 2개의 해를 찾기 위해, 하나의 x는 음수에서 시작하고, 나머지 x를 10에서 시작하도록 초기값을 설정해준 후 해찾기를 실행시킵니다.

이와 같이 해찾기를 실행시킨 결과, 아래와 같이 0과 8이 아닌 근사치로만 계산이 됩니다. 해석적인 방법이 아니라 수치를 대입하는 방법이다보니 계산의 정밀도에 따라, 혹은 지정해둔 반복 계산 횟수에 도달하게 되면 계산을 마쳐버리는 것이지요. 필요하면 해찾기를 다시한번 실행시켜주면 참값에 더욱 근접해가기도 하지만, 정확한 값이 나오지 않을 수도 있습니다. 그러나, 그 오차가 참값과 비교하여 큰 차이가 나지 않으므로, 충분히 유의미한 결과가 되겠지요.

기본적인 해찾기 기능에 대해 설명드렸고, 다음 글에서는 이산 데이터로부터 Fitting하는 방법에 대해 설명드리도록 하겠습니다.

Module 8. 해찾기 기능 활용 - (1)해찾기 기능이란?

엑셀에는 기본 메뉴나 함수 이외에도 복잡한 함수나 기능을 갖도록 프로그래밍한 Addin을 추가할 수 있습니다. 엑셀에서 기본적으로 제공하는 추가 기능은 통화(Currency) 관련 기능, 통계 분석을 위한 분석도구와 해찾기 기능이 있습니다.

재무나 통계를 많이 사용하시는 분들은 통화 및 분석 도구를 사용하실 수 있겠지만, 저는 그쪽을 별로 쓸 일이 없더군요. 대신 해찾기 기능을 많이 사용합니다.

'해찾기'란 여러 셀을 참조하는 특정 셀(목표셀) 값이 최소값, 최대값, 혹은 지정된 값이 될 때까지 참조하는 다른 셀의 값을 수치적으로 변경하는 기능입니다.

예를 들어, y=2*x1+3*x2라고 할 때, y=10, x1=2라고 하면, x2=(y-2*x1)/3=2 와 같이 구할 수 있습니다. 이러한 방식은 방정식을 정확하게 풀 수 있는 경우에만 가능합니다. 만약, z=exp(x^2)/(x^2+y^2) 이라고 하고, z=10, y=1 일 때, x값을 구해야하는 상황이라면 x를 어떻게 구해야할까요? 전문적인 수학용 소프트웨어라면 쉽게 해결이 가능할 것 같습니다만, x=f(y,z)와 같이 해를 구할 수 없다면 값을 구하긴 쉽지 않습니다.

이러한 경우, z는 exp(x^2)/(x^2+y^2) 함수를 입력해두고, y=1로 고정한 상태에서, z=10이 될 때까지 x값을 반복해서 변경시켜주고, 허용 오차 범위안에서 해가 찾아지면 계산을 종료합니다. 해석적인 방법과 달리 수치를 지속적으로 반복하는 방식이라 오류가 발생할 수는 있지만, 꽤 쓸만한 기능입니다.

이러한 해찾기 기능은 단순히 함수값에 대한 해를 계산하는 것 뿐만 아니라, 앞서 만들었던 fitting 기능에도 활용할 수 있습니다. 이런 방법은 뒤에 조금더 자세히 설명드리도록 하겠습니다.

우선 해찾기 기능을 추가하는 방법에 대하여 설명드리면,

리본메뉴에 '개발도구'가 표시되어 있다면, 개발도구 메뉴에서 톱니바퀴 모양의 '추가기능' 버튼을 클릭하거나, '파일'→'옵션'→'추가 기능'→맨 하단의 'Excel 추가 기능'→'이동' 버튼을 눌러서 추가 기능 창이 뜨면, '해 찾기 추가 기능'을 체크하시면 됩니다.

만약, 제대로 설치되었다면, '데이터' 메뉴의 맨 우측에 '해 찾기' 버튼이 생성됩니다.

해찾기 버튼을 누르게 되면, 아래와 같이 해 찾기 창이 뜨게 됩니다.

다음 글에서는 해찾기 기능 활용방법에 대해서는 조금더 자세히 설명드린 후, 조금더 편리하게 사용하도록 매크로를 만들어볼까 합니다.

Module 7. 여러 차트를 일괄 변경하기 - (4)차트 크기 동일하게 맞추기

엑셀의 워크시트에 삽입된 차트는 그 속성을 보더라도 차트 크기를 지정하는 메뉴를 찾기가 쉽지 않습니다. 1개 정도는 대충 마우스로 크기를 맞춘다하더라도 여러 개의 차트 크기를 맞추려면 비슷하게는 맞출 수 있지만, 정확하게 맞추기 어렵습니다.

만약, 차트 속성을 고정으로 하지 않은 상태에서 사용자가 어쩌다 차트가 걸쳐있는 행이나 열을 삭제/삽입하거나 폭이나 높이를 바꾸게 되면 차트 크기도 함께 변하게 되는데, 크기가 달라진 차트를 동일하게 맞추려해도 크기 지정하는 옵션을 찾지 못해서 크기가 제멋대로 변해버린 상태가 되는 경우가 발생합니다. 여러 개의 차트를 만들고, 그림으로 변환하거나 문서 작성을 위해 복사하려는데 차트 크기가 서로 다르면 보기가 좋지 않겠지요.

이번에 소개할 내용은 차트 크기를 일괄적으로 변경하는 매크로입니다.

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

Sub ChangeChartSize()
  On Error Resume Next
  Dim i As Long, tChart() As Chart, tShape As ShapeRange, tAR As Single, tStr As String, tSplit, tVal
  '일단 여러개의 차트를 선택한 상태에서 차트만 ShapeRange로 변수에 할당하여 목록을 만들어둡니다.

  If SelectionToChartArray(tChart, True) = False Then GoTo ErrorHandler
  Set tShape = ChartArrayToShapeRange(tChart)

  '차트 비율을 4/3, 16/9와 같이 일정한 비율로 바꾸고 싶다면, 차트 비율을 입력받습니다. 분수 형태가 아닌 가로:세로 비율을 숫자로 입력받을 수도 있으며, 만약 입력 취소하거나 0 또는 숫자가 아닌 값이 입력되면 비율 변경을 하지 않도록 합니다.
  tStr = InputBox("차트 크기 비율을 재설정하시겠습니까?" & vbCrLf & "차트의 '가로/세로' 비율을 입력하세요.", "차트 가로/세로 비율", "4/3")
  tAR = 0
  If StrPtr(tStr) <> 0 Then
    tSplit = Split(tStr, "/")
    If UBound(tSplit) = 0 Then
      If Val(tSplit(0)) <> 0 Then tAR = Abs(Val(tSplit(0)))
    Else
      If Not (Val(tSplit(0)) = 0 And Val(tSplit(1)) = 0) Then tAR = Abs(Val(tSplit(0)) / Val(tSplit(1)))
    End If
  End If
  '차트 가로/세로 비율이 입력되었다면, 1번 차트의 비율을 변경합니다. 현재 매크로는 세로 길이를 고정하고, 가로 길이를 변경하는 방법으로 비율을 조정합니다. 만약 차트 속성에서 가로/세로 비율을 고정되어있다면 가로 길이를 변경하면 세로가 함께 변경되므로, 속성을 일단 변경해준 후, 크기 변경하고 다시 속성값을 되돌려줍니다.
  If tAR <> 0 Then
    With tShape(i)
      tVal = .LockAspectRatio
      .LockAspectRatio = msoFalse
      .Width = .Height * tAR
      .LockAspectRatio = tVal
    End With
  End If
  '2번째 차트부터는 1번 차트의 크기 속성을 그대로 복사해줍니다.
  For i = 2 To tShape.Count
    With tShape(i)
      tVal = .LockAspectRatio
      .LockAspectRatio = msoFalse
      .Width = tShape(1).Width
      .Height = tShape(1).Height
      .LockAspectRatio = tVal
    End With
  Next
ErrorHandler:
  Erase tChart
End Sub

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

이상과 같이, 차트의 크기를 변경하기 위해서는 해당 차트를 포함한 Shape의 크기를 변경하여 차트 트기를 변경합니다.

만약, 차트의 각 구성 요소를 변경하고 싶다면, 아래와 같이 차트의 각 요소에 해당하는 object를 확인하시고, 각각에 대한 속성을 변경하게 되면 차트를 자유롭게 설정할 수 있습니다. 아래에 나타낸 것 이외에도 다양한 속성들이 있으나, 직관적으로 보이는 부분만 나타내었으며, https://docs.microsoft.com/en-us/office/vba/api/excel.chart(object)를 참고하셔서 본인에게 필요한 기능을 포함하는 다양한 매크로를 작성하여 활용할 수 있을 것입니다. 

Module 7. 여러 차트를 일괄 변경하기 - (3)여러 차트에 차트 서식을 일괄 복사하기

차트의 서식을 다른 차트에 복사하기 위해서는 원본 차트에서 Ctrl+C 후, 홈 탭에서 '선택하여 붙여넣기'를 클릭하면, 아래와 같이 선택 창이 뜨게 되고, 서식만 붙여넣게 할 수 있습니다. 그러나, 이 방법은 여러 개의 차트를 선택한 상태에서는적용할 수 없으며, 붙여넣을 차트를 1개씩만 선택해서 반복 작업을 해야하고, 차트 갯수가 많아지면 번거로운 수작업이 많아진다는 문제가 있습니다.

여러개의 차트를 선택한 후, 1번째 차트의 서식을 나머지 차트에 붙여넣는 매크로를 만들어보려고 합니다. 차트가 몇개 안된다면, 1번 차트를 제일 먼저 선택하고, 나머지 차트를 Ctrl 키를 누른 상태에서 클릭하면 1번 차트를 지정해줄 수 있습니다. 그런데, 작업하려는 차트가 수십개라면, 차트들을 일일이 클릭하기도 귀찮을 수 있습니다. 이럴 땐, 차트 1~2개를 선택한 상태에서 Ctrl+A를 누르게 되면, 해당 시트에 있는 모든 차트를 선택할 수 있습니다.

그런데, Ctrl+A로 모든 차트를 선택하게 되면, ZOrder에 따라 차트 순서가 정해지는데, 차트의 ZOrder를 모르기 때문에 어느 차트가 1번 차트인지 모르게 됩니다. 물론 제일 처음 생성된 차트가 1번이겠지만, 차트를 생성한 순서를 다 기억할 수는 없으니.. 이럴 땐 기준차트를 선택해서 마우스 오른쪽 버튼으로 맨 앞으로, 혹은 맨 뒤로 보내기..를 클릭하게 되면 ZOrder가 바뀌게 되니 미리 ZOrder를 변경한 후 작업할 차트를 Ctrl+A로 여러 차트를 선택하는 방법이 있습니다. 혹은 기준 차트를 잘라낸 후 (Ctrl+X), 붙여넣기(Ctrl+V)를 하게 되면, 이 차트의 ZOrder가 맨 마지막으로 가게 됩니다.

다른 방법으로는 대상 차트를 다 선택한 후, 마지막에 Ctrl을 누른 상태에서 기준 차트를 한번 클릭하면 그 차트는 선택에서 제외되고, 다시 클릭하게 되면 기준 차트는 맨 마지막에 선택된 차트가 됩니다. 따라서, 차트 배열을 만들 때, 맨 마지막 차트를 기준 차트로 선택할 수 있게 해두면, 사용자 입장에서는 차트 선택이 비교적 간단해질 수 있습니다.

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

Sub CopyChartFormat()
  On Error Resume Next
  Dim i As Long, tSel As ShapeRange, tMode As Long, tChart() As Chart

  '워크시트에서 원하는 여러 개체를 선택한 상태에서 차트들만 추출하여 배열에 할당해줍니다. 만약 차트가 선택되지 않았거나, 차트가 1개 밖에 없다면 매크로를 중단시킵니다.
  If SelectionToChartArray(tChart, True) = False Then GoTo ErrorHandler
  If UBound(tChart) < 1 Then GoTo ErrorHandler
  '선택된 개체 중 차트들만 ShapeRange로 변환해줍니다.

  Set tSel = ChartArrayToShapeRange(tChart)
  '복사 방식이 전체, 서식, 수식으로 선택할 수 있도록 하고, 기본값을 서식만 복사하도록 2로 설정해둡니다.

  tMode = Val(InputBox("복사 모드를 선택하세요." & vbCrLf & "1 : 전체 복사,  2 : 서식만 복사,  3 : 수식만 복사", "복사 모드 선택", 2))
  If Not (tMode = 1 Or tMode = 2 Or tMode = 3) Then GoTo ErrorHandler
  '기준 차트는 Chart(0) 또는 tSel(1).Chart 이며, Chart.Copy가 아니라 Chart.ChartArea.Copy를 사용해야합니다. 나머지 차트를 순서대로 선택한 후, 붙여넣기를 실행합니다. 작업을 마치고 나면, 차트들만 선택된 상태로 매크로를 종료합니다.

  tSel(1).Chart.ChartArea.Copy
  For i = 2 To tSel.Count
    tSel(i).Select
    ActiveSheet.PasteSpecial Format:=tMode
  Next

  tSel.Select
ErrorHandler:
  Erase tChart
End Sub

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

위의 매크로는 실행하더라도, 차트의 크기나, 데이터 범위 등이 변하지 않을 수 있습니다. Shape이 가진 모든 속성을 다 복사하는 것이 아니라, ChartArea 내 속성만 복사하기 때문에, 차트를 포함한 Shape의 크기가 변하지 않습니다. 또한, X, Y값의 범위가 '자동'으로 설정되어 있으면, '자동'이라는 속성을 복사하게 되므로, 인위적으로 X, Y값의 범위를 동일하게 만들기 위해서는 데이터 상/하한값을 수동으로 지정하여야 X, Y값을 동일하게 만들 수 있습니다.

여러 데이터셋으로부터 차트를 그린 후, 이러한 속성을 강제로 맞춰주기 위해서는 별도의 매크로가 필요합니다. 다음 글에서는 차트 Scale과 크기까지 복사하는 매크로를 작성해보겠습니다.

Module 7. 여러 차트를 일괄 변경하기 - (2)차트배열을 ShapeRange로 변환하기

여러 개의 차트에 작업을 하고, 작업이 완료된 여러 개의 차트를 선택된 상태로 매크로를 마치게 되면, 그 다음 수작업하는데 도움이 될 수 있습니다.

예를 들어, 5개의 차트를 선택해서, 원본 차트를 남겨두기 위해 복사를 한 후 크기를 변경하거나, 서식을 변경했다고 하겠습니다. 그 다음 작업은 처음에 선택한 차트가 아니라, 작업이 완료된 차트 복사본이 선택되어 있다면, 수작업으로 차트를 다른 시트로 옮기거나, 그 이후 작업을 추가로 진행하는데 유리할 수 있습니다.

다른 예로, 10개의 차트를 선택한 상태에서 2개씩 병합된 차트를 5개 만들었다고 하면, 매크로가 완료되었을 때, 처음 10개의 차트를 선택한 상태로 마치는 것보다, 새로 만들어진 차트 5개를 선택한 상태로 매크로가 끝난다면, 새 차트를 일일이 선택하는 번거로움이 줄어들 수 있을 것입니다.

1개의 차트인 경우, Chart.Select를 이용해서 차트가 선택된 상태로 끝낼 수 있습니다만, 차트 배열에서 여러 개의 차트가 할당되어 있다면, Select method가 작동되지 않습니다.

여러 개의 차트를 동시에 선택된 상태를 만들기 위해서는 차트배열을 ShapeRange로 변환해준 후, ShapeRange.Select를 사용해야합니다.

이전 글에서 Shape 아래 Chart가 포함된다고 했는데, Chart.Parent를 사용하면, 상위 개체인 Shape이 반환될 것 같지만, 차트를 포함한 Shape이 반환되지 않습니다. 따라서, 번거롭지만, 아래와 같이 해당 시트 내에 있는 모든 Shape에 대해 선택하려는 Chart명을 일일이 확인한 후, 해당 Shape의 Index 목록을 만들어 ShapeRange로 만들어주는 방법을 쓸 수 있습니다.

엑셀 작업 시트에 수천개의 Shape이 포함된 것이 아니라면, 이렇게 ShapeRange를 만들더라도 그다지 시간이 오래 걸리진 않습니다.

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

Function ChartArrayToShapeRange(iChart() As Chart) As ShapeRange
  On Error GoTo ErrorHandler
  Dim i As Long, j As Long, n As Long, tSh As Worksheet, tShRange As ShapeRange, tIndex() As Long

  '우선 차트가 포함된 시트를 확인합니다.
  Set tSh = iChart(LBound(iChart)).Parent.Parent
  n = LBound(iChart) - 1
  ReDim tIndex(LBound(iChart) To UBound(iChart))
  '해당 시트 내 모든 Shape 개체에 대하여, Chart가 포함되어있는지 확인하고, 차트명을 입력된 차트명과 비교하여 같다고 하면, 해당 Shape의 Index 목록을 만들어줍니다.

  For i = LBound(iChart) To UBound(iChart)
    For j = 1 To tSh.Shapes.Count

      If tSh.Shapes(j).HasChart Then
        If tSh.Shapes(j).Chart.Name = iChart(i).Name Then
          n = n + 1
          tIndex(n) = j
          Exit For
        End If
      End If
    Next
  Next

  'Index 배열을 이용하여, ShapeRange를 만들어 반환해줍니다. ShapeRange는 일반적인 배열과 달리, 아이템들을 추가하거나 빼는 등의 작업이 되지 않기 때문에 이러한 과정을 거쳐서 원하는 ShapeRange를 만들 수 있습니다.

  Set ChartArrayToShapeRange = tSh.Shapes.Range(tIndex)
ErrorHandler:
  Erase tIndex
End Function

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

Module 7. 여러 차트를 일괄 변경하기 - (1)선택된 차트를 배열에 할당하기

이제까지 주기적인 데이터를 선택하거나 fitting한 후, 차트를 작성하였습니다. 데이터를 일괄적으로 작업을 하고 차트를 작성하다보면, 차트 역시 데이터 갯수만큼 생성될 수 있습니다.

기본 형식으로 작성된 차트는 데이터의 분포를 파악하는 데에는 유용하지만, 별도의 자료를 작성하기 위해서는 일일이 차트들의 속성을 변경하는 작업이 필요하게 됩니다. 예를 들면, X, Y축의 범위라든지, 범례 위치라든지, 색상이나 크기를 맞추는 등...

만약 주기적인 데이터를 가진 자료로부터 차트를 만들었다면, 차트 종류나 데이터수와 같은 기본적인 차트 형식은 거의 동일할 것입니다. 따라서, 1개의 대표 차트에 기본적인 서식을 만들어 둔 후, 다른 차트에 서식을 복사할 수 있다면 필요한 수작업 양이 크게 줄어들 수 있을 것을 것입니다. 특히 수십개의 차트를 동시에 처리해야할 때 유용하겠지요.

이러한 작업을 위해, 우선 사용자가 선택한 차트를 배열에 할당할 필요가 있습니다.

그런데, 엑셀에서 차트는 복잡한 구조를 갖는 object입니다. 만약 사용자가 여러개의 object를 선택했다면, 이 중 차트만 배열에 할당해주면 됩니다만, 만약 1개의 차트의 구성 요소를 선택한 상태였다면 구성 요소의 Parent를 선택차트로 지정해주어야 합니다. 따라서, 차트를 선택했을 때에만 매크로가 작동할 수 있도록 사용자가 어떠한 개체를 선택했는지 확인하고, 차트를 선택했다면 선택된 차트를 배열로 지정해서 이후 차트와 관련된 일괄 작업을 할 수 있습니다.

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

Function SelectionToChartArray(oChart() As Chart, Optional iCheckFirst As Boolean = False) As Boolean

  '차트가 포함된 개체를 선택했다면, oChart()에 선택된 차트 목록을 출력하고, 함수의 반환값은 True가 되고, 차트가 포함된 개체를 하나도 선택하지 않았다면, 함수의 반환값을 False로 반환해줍니다. Type을 지정해서 차트 선택 여부와 차트 목록을 동시에 반환할 수도 있지만, Type 선언을 최소화하기 위해서 이런 방식을 사용했습니다.

  On Error GoTo ErrorHandler

  Dim tChart() As Chart, tChart2() As Chart, tSel, i As Long, n As Long

  '현재 선택된 것이 어떤 개체인지 확인합니다.

  Select Case TypeName(Selection)

  '여러 개의 그림과 도형 등을 선택했을 때에는 TypeName이 DrawingObjects가 됩니다.

  'Selection.ShapeRange에 목록이 있으며 ShapeRange는 여러 개의 Shape으로 구성되고, Shape 아래 Chart가 포함됩니다. 차트를 포함한 Shape에서 차트만 배열에 할당해줍니다.

    Case "DrawingObjects"

      n = -1

      Set tSel = Selection.ShapeRange

      For i = 1 To tSel.Count

        If tSel(i).HasChart Then

          n = n + 1

          ReDim Preserve tChart(n)

          Set tChart(n) = tSel(i).Chart

        End If

      Next

  '여러 개의 차트를 일괄적으로 작업하기 위해, 1개의 차트에 서식이나, X, Y값의 범위, 크기 등을 지정해두고, 나머지 차트는 그 서식을 복사하는 방식으로 일괄 작업을 할 예정입니다. 따라서, 기준 차트를 첫번째로 선택한 차트로 설정할지 마지막에 선택한 차트로 설정할지를 확인한 후, 기준차트를 배열의 0번에 할당해줍니다. 시트에서 차트를 여러개 선택할 때, ZOrder를 바로 알 수 없기 때문에 기준차트를 마지막에 선택하는 것이 작업이 편할 수 있습니다.

      If n > 0 And iCheckFirst Then

        If MsgBox("첫번째 선택된 차트를 기준차트로 설정하시겠습니까?" & vbCrLf & " -Yes : 첫번째 차트를 기준차트로 설정" & vbCrLf & " -No : 마지막 차트를 기준차트로 설정", vbYesNo, "기준차트 설정") = vbNo Then

          tChart2 = tChart

          Set tChart(0) = tChart2(n)

          For i = 0 To n - 1

            Set tChart(i + 1) = tChart2(i)

          Next

        End If

      End If

  '1개 차트만 선택되었을 때에는 TypeName이 Chart로 주어집니다.

    Case "Chart"

      ReDim tChart(0)

      Set tChart(0) = Selection

  '차트가 선택되지 않은 상태에서 시트의 일부 구간을 선택했다면, Range를 선택한 것이며, 이때에는 차트와 과련된 작업을 할 필요가 없습니다.

    Case "Range"

      GoTo ErrorHandler

  '1개 차트 중 일부 요소를 선택했을 때에는 3번 정도 Parent 개체를 확인해보면, Chart를 선택했는지 확인할 수 있습니다. 그러나, 어떠한 요소는 이렇게 해도 현재 대상차트가 선택되지 않는 경우도 있습니다. 모든 구성요소를 다 확인해보진 않았으나, 차트 요소의 대부분은 Parent 개체를 확인하면 차트가 나오더군요.

    Case Else

      n = -1

      ReDim tChart(0)

      Set tSel = Selection

      For i = 1 To 3

        Set tSel = tSel.Parent

        If TypeName(tSel) = "Chart" Then

          Set tChart(0) = tSel

          n = n + 1

          Exit For

        End If

      Next

      If n < 0 Then GoTo ErrorHandler

  End Select

  '만약, 정상적으로 이 단계까지 왔다면, 1개 이상을 포함하는 차트 목록이 만들어졌음을 의미하며, 결과값을 True로 반환하고 함수를 종료합니다. 반면, 어떠한 이유에서든 에러가 발생했다면, 함수 결과를 False로 반환하고, 함수 목록은 반환해줄 필요가 없습니다.

  oChart = tChart

  SelectionToChartArray = True

  Erase tChart, tChart2

  Exit Function

ErrorHandler:

  SelectionToChartArray = False

  Erase tChart, tChart2

End Function

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

Veritcal / Perpendicular Offset에 따른 일괄 선형회귀 / Linear fitting 매크로 (2)

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

Sub LinearFit()
  On Error Resume Next
  Dim tSh As Worksheet, tRange As Range, tN As Long, tSh2 As Worksheet, tStr As String, tMode As Integer, tCN As Integer
  Dim tXCol() As Range, tYCol() As Range, tHN As Integer
  Dim tXY() As Range, tX As Range, tY As Range, tStrX As String, tStrY As String, tValX(), tValY()
  Dim tA As Range, tB As Range, tRSq As Range, tStrA As String, tSign As String, tValA, tValB, i As Long
  Dim tXFit As Range, tYFit As Range, tRes As Range, tValRes(), tStrRes As String
  Dim tChart As Chart
  

  Set tSh = ActiveSheet
  '반복된 X, Y열 데이터가 있다고 하면, X, Y쌍을 지정해줍니다.

  If SelectedColIntoXYPair(tXCol, tYCol, tHN) < 1 Then GoTo ErrorHandler
  

  'Offset 종류와 fitting 식을 선택합니다. 여기에서는 총 4가지 종류로 fitting하도록 합니다.
  tStr = InputBox("Fitting mode를 선택하세요." & vbCrLf & "1 : Vertical Offset / Y=aX+b" & vbCrLf & "2 : Vertical Offset / Y=aX" & vbCrLf & "3 : Perpedicular Offset / Y=aX+b" & vbCrLf & "4 : Perpedicular Offset / Y=aX", "Fitting Mode 선택", 1)
  tMode = Int(Val(tStr))
  If StrPtr(tStr) = 0 Or (tMode < 1 Or tMode > 4) Then Exit Sub

  '원데이터를 복사하고 fitting 결과를 출력할 시트를 만들어준 후 시트명을 적당하게 지정해줍니다. 또한, fitting 종류를 표기해주고, 기울기, 절편, R-Sq를 출력할 위치를 지정합니다. 또한, 차트에 fitting 결과를 그리기 위한 2개의 점을 출력할 위치도 지정해둡니다.

  Set tSh2 = ActiveWorkbook.Sheets.Add(After:=tSh)
  With tSh2
    .Name = NewSheetName(tSh.Name & "_LF", tSh2.Index)
    .Tab.ColorIndex = 8
    .Activate
    .Cells(1, 1).Value = "Fitting Mode: "
    Select Case tMode
      Case 1
        .Cells(1, 2).Value = "Vertical Offset / Y=aX+b"
      Case 2
        .Cells(1, 2).Value = "Vertical Offset / Y=aX"
      Case 3
        .Cells(1, 2).Value = "Perpedicular Offset / Y=aX+b"
      Case 4
        .Cells(1, 2).Value = "Perpedicular Offset / Y=aX"
    End Select
    .Cells(2, 1).Value = "a="
    .Cells(3, 1).Value = "b="
    .Cells(4, 1).Value = "R_Sq="
    .Cells(6, 1).Value = "(X1,Y1) :"
    .Cells(7, 1).Value = "(X2,Y2) :"
  End With

  Call CalcModeOn(True)

  For i = 1 To UBound(tYCol)

  'Y 데이터 갯수에 해당하는 만큼 반복하되, X, Y 데이터와 잔차를 함께 출력하도록 3개 열마다 반복해서 출력합니다.

  '기울기를 출력하는 위치(tA)를 지정한 후, 나머지 출력위치는 모두 .Offset으로 지정해줍니다. 이렇개 해두면, tA 위치를 바꾸면 나머지 위치들이 모두 상대위치로 지정되기 때문에 변경할 필요가 없습니다.

    Set tA = tSh2.Cells(2, 3 * (i - 1) + 2)
    Set tB = tA.Offset(1, 0)
    Set tRSq = tB.Offset(1, 0)
    Set tXFit = tSh2.Range(tRSq.Offset(2, 0), tRSq.Offset(3, 0))
    Set tYFit = tXFit.Offset(0, 1)
    

  'X, Y 데이터를 미리 만들어둔 함수를 이용해서 header 부분을 제외한 데이터 영역 중 X, Y 데이터가 모두 있는 영역만 복사해옵니다. header가 있다면 header도 가져와서 출력하도록 합니다.
    tXY = TrimXYRange(tXCol(i), tYCol(i), tHN, True)
    Set tRange = tSh2.Range(tSh2.Cells(9, 3 * (i - 1) + 2), tSh2.Cells(9, 3 * (i - 1) + 4))
    tRange.Value = Array("X" & i, "Y" & i, "Residue" & i)
    If tHN > 0 Then
      CopyValues tSh.Range(tXCol(i).Cells(1, 1), tXCol(i).Cells(tHN, 1)), tRange.Cells(1, 1).Offset(1, 0)
      CopyValues tSh.Range(tYCol(i).Cells(1, 1), tYCol(i).Cells(tHN, 1)), tRange.Cells(1, 2).Offset(1, 0)
    End If
    Set tX = CopyValues(tXY(1), tRange.Cells(1, 1).Offset(1, 0).Offset(tHN, 0))
    Set tY = CopyValues(tXY(2), tRange.Cells(1, 2).Offset(1, 0).Offset(tHN, 0))
    Set tRes = tY.Offset(0, 1)
    

  'X, Y, A, B가 기록될 주소를 변수에 지정해둡니다. "$"를 삭제하는 것은 상대주소로 변환하기 위한 것으로 결과에는 영향을 미치지 않습니다만, 작업이 완료된 후 엑셀 시트에서 셀 함수 복사하기를 이용하여 다른 영역의 fitting이 가능하도록 상대 주소가 입력해줍니다.

    tStrX = Replace(tX.Address, "$", "")
    tStrY = Replace(tY.Address, "$", "")
    tStrA = Replace(tA.Address, "$", "")
    tStrB = Replace(tB.Address, "$", "")

  'Fitting mode에 따라 기울기 A, 절편 B 셀에 아래와 같이 함수가 입력되도록 합니다. 엑셀의 내장함수를 그대로 활용하는 것으로 문자열을 조합하여 해당 셀의 수식(formula)에 대입해줍니다. 각 식은 이전 글들에서 다 계산해두었기 때문에 그대로 만들어주기만 하면 됩니다. 평균(Average), 제곱합(SumSq), 분산(Var.P), 공분산(Covar), 곱 합치기(SumProduct) 등... 엑셀의 수식 입력란에 사용할 수 있는 함수를 그대로 사용합니다.

    Select Case tMode
      Case 1
        tA.Formula = "=COVAR(" & tStrX & "," & tStrY & ")/VAR.P(" & tStrX & ")"
        tB.Formula = "=AVERAGE(" & tStrY & ")-" & tStrA & "*AVERAGE(" & tStrX & ")"
      Case 2
        tA.Formula = "=SUMPRODUCT(" & tStrX & "," & tStrY & ")/SUMSQ(" & tStrX & ")"
        tB.Value = 0
      Case 3
        If Application.WorksheetFunction.Covar(tX, tY) >= 0 Then tSign = "+" Else tSign = "-"
        tA.Formula = "=((VAR.P(" & tStrY & ")-VAR.P(" & tStrX & "))" & tSign & "SQRT((VAR.P(" & tStrY & ")-VAR.P(" & tStrX & "))^2+4*COVARIANCE.P(" & tStrX & "," & tStrY & ")^2))/(2*COVARIANCE.P(" & tStrX & "," & tStrY & "))"
        tB.Formula = "=AVERAGE(" & tStrY & ")-" & tStrA & "*AVERAGE(" & tStrX & ")"
      Case 4
        If Application.WorksheetFunction.SumProduct(tX, tY) >= 0 Then tSign = "+" Else tSign = "-"
        tA.Formula = "=((SUMSQ(" & tStrY & ")-SUMSQ(" & tStrX & "))" & tSign & "SQRT((SUMSQ(" & tStrY & ")-SUMSQ(" & tStrX & "))^2+4*SUMPRODUCT(" & tStrX & "," & tStrY & ")^2))/(2*SUMPRODUCT(" & tStrX & "," & tStrY & "))"
        tB.Value = 0
    End Select
    

  'A, B 수식으로부터 구한 값을 이용하여 잔차(Residue)와 함께 R-Sq값을 구해줍니다.

    tValA = tA.Value
    tValB = tB.Value
    tValX = tX.Value
    tValY = tY.Value
    tValRes = tRes.Value
    tStrRes = Replace(tRes.Address, "$", "")
    Select Case tMode
      Case 1, 2
        For j = 1 To tRes.Rows.Count
          tValRes(j, 1) = tValY(j, 1) - (tValA * tValX(j, 1) + tValB)
        Next
        tRes.Value = tValRes
        tRSq.Formula = "=1-SUMSQ(" & tStrRes & ")/(COUNT(" & tStrRes & ")*VAR.P(" & tStrY & "))"
      Case 3, 4
        For j = 1 To tRes.Rows.Count
          tValRes(j, 1) = (tValA * tValX(j, 1) + tValB - tValY(j, 1)) / Sqr(tValA * tValA + 1)
        Next
        tRes.Value = tValRes
        tRSq.Formula = "=1-2*SUMSQ(" & tStrRes & ")/(COUNT(" & tStrRes & ")*(VAR.P(" & tStrX & ")+VAR.P(" & tStrY & ")))"
    End Select

  'Fitting한 결과를 직선으로 그리기 위해, X의 최소, 최대값에서 Y값을 구해서 fitting 결과 출력 셀 아래에 입력해줍니다.

    tXFit.Cells(1, 1).Formula = "=MIN(" & tStrX & ")"
    tXFit.Cells(2, 1).Formula = "=MAX(" & tStrX & ")"
    tYFit.Cells(1, 1).Formula = "=" & tStrA & "*" & tXFit.Cells(1, 1).Address & "+" & tStrB
    tYFit.Cells(2, 1).Formula = "=" & tStrA & "*" & tXFit.Cells(2, 1).Address & "+" & tStrB
    

  'X, Y 원데이터를 점 분산차트를 그리고, fitting 결과를 선 차트로 추가하여 그려준 후, 해당 Y열 위치로 옮겨줍니다.
    Set tChart = ScatterChart_New(tX, tY, 0, xlXYScatter)
    Set tChart = ScatterChart_Add(tChart, tXFit, tYFit, 0, xlXYScatterLinesNoMarkers)
    tChart.SeriesCollection(2).Format.Line.Weight = 2
    With tChart.ChartArea
      .Top = tY.Cells(5, 1).Top
      .Left = tY.Cells(5, 1).Left
    End With
  Next
ErrorHandler:
  Call CalcModeOn(False)
  Erase tXCol, tYCol, tXY, tValX, tValY, tValRes
End Sub

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

위의 매크로를 이용하여 일괄적으로 linear fitting한 결과는 아래 그림과 같습니다. 

일일이 차트를 그리고, 직선으로 fitting하는 작업을 반복하는 것보다는 빠르게 진행될 수 있으며, 차트를 확인하여 상관관계가 있는 데이터를 빠르게 찾아낼 수 있으며, 적당히 수정하면 통계 소프트웨어에서처럼 MatrixPlot도 가능할 것입니다.

Veritcal / Perpendicular Offset에 따른 일괄 선형회귀 / Linear fitting 매크로 (1)

Offset 종류에 따른 1차식 fitting 매크로를 작성하고자 합니다.

다항식 fitting에서는 모든 계산식을 함수로 만들었습니다만, 1차식 fitting은 역행렬을 구할 필요없이 분산, 공분산, 평균 등과 같이 엑셀의 내장함수만 잘 조합하더라도 쉽게 구할 수 있기 때문에 별도의 함수는 가급적 만들지 않고 작성해보력 합니다. 또한, fitting 결과를 차트를 통해 쉽게 확인할 수 있다면 시각적으로 빠른 판단이 가능할 것 같습니다.

반복되는 여러개의 데이터가 있다고 하면, 이전에 만들어둔 SelectedColIntoXYPair 함수를 이용해서, 여러개의 X, Y쌍을 선택합니다. 원데이터를 손상시키지 않도록 선택된 열을 별로의 시트를 생성하여 복사한 다음, X, Y열의 데이터 갯수를 동일하게 만들어주고, 엑셀 내장함수를 이용하여 기울기, 절편과 결정 계수 R-Sq값을 구해줍니다. 마지막으로 원 데이터를 점으로, fitting된 결과를 직선으로 차트를 그려주는 것까지 만들어보도록 하겠습니다.

X, Y 쌍을 선택하는 것은 기존의 함수를 그대로 사용하도록 하겠습니다만, X, Y 데이터 갯수를 맞춰주는 함수는 TrimXYRange를 사용하려고 합니다. 다만, 이전에 만들어둔 함수는 단순히 X, Y열 중 빈셀만 제거해주는 것이었다면, X, Y fitting을 위해서는 X와 Y가 모두 채워져있는 셀만 선택해야하므로, 약간의 수정을 하도록 하겠습니다. 다만, 기존에 작성한 함수를 변경하지 않고 사용할 수 있어야겠지요.

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

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

  '기존의 함수와 동일합니다만, Option으로 데이터 갯수를 맞출 것인지를 입력할 수 있도록 합니다. 기존 함수를 다른 매크로에 활용했더라도, 기본값이 설정되어있으니 코드 수정은 불필요합니다.

  Dim tSh As Worksheet, tXY(2) As Range, tHN As Integer, i As Long, tX(), tY(), tSN As Long, tFN As Long
  Set tSh = iYCol.Worksheet
  tHN = iHeader: If tHN < 0 Then tHN = 0
  If tHN > 0 Then Set tXY(0) = tSh.Range(iYCol.Cells(1, 1), iYCol.Cells(tHN, 1))
  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))
  
  tX = tXY(1).Value
  tY = tXY(2).Value

  '데이터 갯수를 맞춰주는 것으로 입력하면(iMatchNum=True), X와 Y 중 1개라도 빈칸인 경우 데이터를 제외시키도록 하며, 데이터 갯수를 맞춰줄 필요가 없다면(iMatchNum=False) X, Y가 모두 공란으로 되어 있는 셀만 제외하라는 것입니다.
  If iMatchNum Then
    For i = 1 To UBound(tY, 1)
      If Not (TypeName(tX(i, 1)) = "Empty" Or 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" Or TypeName(tY(i, 1)) = "Empty") Then
        tFN = i
        Exit For
      End If
    Next
  Else
    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
  End If
  Set tXY(1) = tSh.Range(tXY(1).Cells(tSN, 1), tXY(1).Cells(tFN, 1))
  Set tXY(2) = tSh.Range(tXY(2).Cells(tSN, 1), tXY(2).Cells(tFN, 1))
  TrimXYRange = tXY
  Erase tX, tY
End Function

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

두번째 함수는 시트의 일정한 영역 데이터를 복사하는 것으로, 엑셀에 내장된 Range.Copy method보다 빠르게 실행하도록 아래와 같이 작성해둡니다. .Copy, .Paste를 사용할 수 있으나, 값이나 셀의 서식을 함께 복사되다보니 반복작업수가 많아질수록 속도가 느려집니다. 아래와 같이 값만 복사하는 함수를 사용하게 되면, 조금더 빠르게 작업이 가능합니다.

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

Function CopyValues(iRange1 As Range, iRange2 As Range) As Range
  Dim tRange As Range

  '2번 range의 시작셀에서 1번 range의 행, 열 갯수만큼 range만큼 확장하여 값만 대입해줍니다. 복사가 완료되면 대상 영역을 결과값으로 반환해줍니다.
  Set tRange = iRange2.Worksheet.Range(iRange2.Cells(1, 1), iRange2.Cells(1, 1).Offset(iRange1.Rows.Count - 1, iRange1.Columns.Count - 1))
  tRange.Value = iRange1.Value
  Set CopyValues = tRange
End Function

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

다음 글에서는 일괄적으로 fitting하는 매크로를 작성하도록 하겠습니다.