엑셀 시트 데이터 읽기/쓰기 (3)

본인에게 유용한 매크로를 만들기 위해서는 본인만의 고유한 함수들을 많이 작성해두는 것이 좋습니다. 사람들마다 이러한 함수를 짜는 알고리즘은 다양하며, 입력받는 값의 형식이나, 출력받는 형식이 달라서 다른 사람이 짜놓은 매크로를 받아 사용하려다보면 자기 스타일에 맞게 수정이 필요할 수 있습니다. 복잡한 수학적 계산이 필요한 알고리즘은 어쩔 수 없이 공개 소스를 이용하는 방법도 있겠지만, 간단한 기능을 갖는 프로시저를 Function으로 짜 놓으면, 필요할 때 마다 반복작업을 하지 않고도 원하는 프로시저를 간결하게 짤 수 있습니다.

매크로를 짜기 위해서는 본인만의 명확한 목적이 있는 것이 좋습니다. 필요성이 없다면 굳이 머리 복잡한 일을 할 필요는 없지요.

우선 목표를 하나 정해보겠습니다.

저는 분석 결과를 txt, dat, csv,.. 형식의 아스키 파일로 자주 전달받습니다. 상용소프트웨어에는  Import 기능으로 여러 개의 파일을 한꺼번에 열어서 데이터시트를 열 수 있습니다만, 사무실에는 그런 소프트웨어가 없습니다. 따라서, 일일이 파일을 열고, 워크시트를 새로 추가해서 붙여넣습니다. 일단 여기까지 지루하게 마쳤다 하더라도, 그 데이터의 특정 영역의 데이터만 추출해서 다시 그래프를 그리거나, 통계를 내거나... 이런 작업을 해야합니다. 

만약, 데이터 파일이 100개쯤 되고, 데이터마다 숫자로만 빼곡한 수십개의 열을 가진 것이라 한다면..? 아무리 꼼꼼한 사람이라도 파일을 중복해서 열거나, 빠뜨리거나, 데이터를 엉뚱한데서 추출하거나... 이런 실수를 하게 될 것이고, 당연히 매우 지루하고 오래 걸리겠지요.

파일을 열고, 닫는 것은 차후에 설명드리기로 하고, 일단 모든 파일을 열어서 각각의 워크시트에 열었다고 가정하겠습니다.

1단계 목표는 내가 원하는 영역을 선택하면, 그 영역의 데이터를 배열로 읽어오고, 읽어온 데이터를 다시 특정한 워크시트의 특정한 위치에 출력하고 싶습니다. 데이터를 어떻게 가공할 것이냐는 사람마다 다르지만, 원하는 위치를 읽어서 원하는 위치로 쓰는 것은 빈번하게 사용될 수 있습니다.

<원하는 위치의 데이터를 배열 변수로 읽어오기>

Function ReadRange(iRng As Range, Optional iReadValue2 As Boolean = False) As Variant()
  If iReadValue2 Then
    ReadRange = iRng.Value2
  Else
    ReadRange = iRng.Value
  End If
End Function

Value와 Value2에서 설명드렸다시피, Value2로 읽어오면, 날짜나 시간 값을 숫자형식으로 읽어게 됩니다. 만약, 측정 날짜, 시간 형식으로 되어 있다면, 이 값을 숫자로 변환해주어야하는 경우도 있으니, 위와 같이 데이터를 읽어올 때, Value2로 읽어올 것인가를 옵션으로 선택해줍니다. 옵션값을 넣지 않으면, 지정된 형식으로 그대로 읽어오는 것이지요.

읽어오는 것은 생각보다 복잡하지 않습니다만, 출력하는 것은 약간 까다롭습니다.

<배열 변수를 원하는 위치에 출력하기>

배열은 1차원일수도 2차원일수도, 혹은 그 이상 차원을 가질 수도 있습니다.

만약, 1차원 배열이라면, 가로 방향으로 출력을 하게 되고, 2차원 배열이라면, (행, 열) 위치에 출력합니다.

그러나, 1차원 배열은 가로 또는 세로로 출력하고, 2차원 배열은 그대로 출력하고 싶습니다.

Function WriteVec(iVec(), iSh As Worksheet, iRange As Range, Optional iWriteCol As Boolean = True) As Range
  Dim tVec(), i As Long, tR As Long, tC As Long
  tR = iRange.Row
  tC = iRange.Column
  If iWriteCol Then
    ReDim tVec(LBound(iVec) To UBound(iVec), 1 To 1)
    For i = LBound(iVec) To UBound(iVec)
      tVec(i, 1) = iVec(i)
    Next
    iSh.Range(iSh.Cells(tR, tC), iSh.Cells(tR + UBound(tVec, 1) - LBound(tVec, 1), tC)).Value = tVec
    Set WriteVec= iSh.Range(iSh.Cells(tR, tC), iSh.Cells(tR + UBound(tVec, 1) - LBound(tVec, 1), tC))
  Else
    tVec = iVec
    iSh.Range(iSh.Cells(tR, tC), iSh.Cells(tR, tC + UBound(tVec, 1) - LBound(tVec, 1))).Value = tVec
    Set WriteVec= iSh.Range(iSh.Cells(tR, tC), iSh.Cells(tR, tC + UBound(tVec, 1) - LBound(tVec, 1)))
  End If
End Function

위의 함수는 1차원 배열, 출력하는 시트 및 범위를 지정하고, Column (세로) 방향으로 쓸지, Row (가로) 방향으로 쓸지 입력할 수 있도록 하였습니다. 만약, 방향 설정이 없으면 자동을 세로방향으로 쓰게 되는 것이지요.

조금 복잡해보일 수 있습니다만, 간단하게 설명드리면, 입력받은 1차원 배열을 그냥 쓰면 가로 방향으로 쓰게 됩니다. 그러나, 저는 세로 방향으로 쓰기를 선호하기 때문에 세로 방향으로 1열을 갖는 2차원 배열로 바꾼 후 시트에 쓰게 하는 것입니다. 이때, 출력 범위는 출력하고자하는 배열의 크기에 맞게 자동으로 계산합니다. 출력하는 범위는 시작위치만 지정하면 되지, 그 크기까지 지정하면서 코딩하기는 귀찮은 일이기 때문이지요.

그런데, 위 코드를 보시면, 세로로 쓰기 위해 2차원 배열을 만들어서 값을 일일이 재입력한 후 다시 워크시트에 값을 쓰도록 해두었습니다. 그냥 워크시트에 바로 쓰면 될 것을 왜 메모리낭비에 시간낭비하면서 이렇게 해두었을까요?

엑셀은 셀 하나하나에 써 나가는 것보다, 배열 변수에서 미리 쓰고, 배열값을 한꺼번에 워크시트에 덮어쓰는 것이 더 빠르기 때문입니다. 몇백개 정도에서는 차이를 못느끼지만, 몇만개~몇십만개 데이터를 쓸 때에는 크게 차이가 납니다.

Function WriteMat(iMat(), iSh As Worksheet, iRange As Range, Optional iInsert As Boolean = False, Optional iShift As Variant = xlToRight) As Range

  Dim tR As Long, tC As Long
  tR = iRange.Row
  tC = iRange.Column
  If iInsert Then iSh.Range(iSh.Cells(tR, tC), iSh.Cells(tR + UBound(iMat, 1) - LBound(iMat, 1), tC + UBound(iMat, 2) - LBound(iMat, 2))).Insert iShift
  iSh.Range(iSh.Cells(tR, tC), iSh.Cells(tR + UBound(iMat, 1) - LBound(iMat, 1), tC + UBound(iMat, 2) - LBound(iMat, 2))).Value = iMat
  Set WriteMat = iSh.Range(iSh.Cells(tR, tC), iSh.Cells(tR + UBound(iMat, 1) - LBound(iMat, 1), tC + UBound(iMat, 2) - LBound(iMat, 2)))
End Function

유사한 방법으로 2차원 배열을 출력하는 것도 위와 같이 만들어둡니다.

다만, 2차원 배열에서는 약간 더 응용해서, 기존의 데이터를 덮어씌우지 않고, 삽입할 것이냐, 아니면 그냥 덮어쓸 것이냐를 선택하게 해두는 것이지요.

Vec, Mat으로 구분한 것은 1차원 배열을 Vector, 2차원 배열을 Matrix로 표현한 것입니다. 개인 취향대로 변경하시면 됩니다.

또한, 본인의 성향에 따라, 몇가지 옵션을 더 넣어서, 읽고 쓰기 함수를 만들어두면, 코드 한줄로 원하는 위치에서 데이터를 읽고, 원하는 위치에 원하는 방식대로 쓸 수 있습니다. 이제 이 데이터를 가지고 어떤 작업을 할 것이냐가 중요하겠지요.