nØthing special

엑셀2003버전까지는 행의 갯수가 65536인데, 일상적인 작업에는 그리 작지 않는 크기이다. 그러나 일상적인 업무에서 종종 65536개를 넘어서는 데이터를 다루는 경우도 생긴다. 가령 수년간의 일별 주식종가를 다룰 경우 등등.

어제 어느 게시판에서 다음과 같은 질문이 있었는데,

2개의 엑셀시트를 한개로 어떻게 연결해야 되나요?
처음 시트는 65021 개 데이타와 와 다른 시트는 54158개의 데이타가 있어요ㅠ
카피.페이스트 하려니까 65531이상의 데이타는 불가능 하다고 두개의 시트를 못 합치네요...
어떻게 할 방법 없을까요?
꼭 해야 되서요.ㅜ
두개의 데이타 합쳐서 피벗 테이블을 만들어야되요ㅠㅠ

사실 답변을 달아봐야 '엑셀2007버전을 사용하세요' 라는 돈 들어 가는 소리밖에 할 얘기가 없다. 데이터가 여러 시트로 찢어서 이산가족이 되면 VLOOKUP()과 같은 함수 사용도 당근 불편해진다. 그래서 생각난 것이 엑셀 수식을 가지고 각각의 데이터를 하나로 모아 사용할 수 없을까 ...구글검색에 들어 갔지만 맘에 드는 해답이 없다. 그러면 VBA로 함수를 하나 만들어 보자. 그래서 만들어진 것이 아래와 같은 함수이다.

Public Function MERGE(rng1 As Range, rng2 As Range)
    Dim r1 As Range, r2 As Range
    Dim v1, v2
    Dim ru
    Dim col As Long
    Dim row As Long
    Dim r As Long, c As Long
    
    Set r1 = rng1
    Set r2 = rng2
    
    v1 = r1.Value
    v2 = r2.Value
    
    col = WorksheetFunction.Max(UBound(v1, 2), UBound(v2, 2))
    row = UBound(v1, 1) + UBound(v2, 1)
    
    ReDim ru(1 To row, 1 To col)
    
    row = 1
    For r = 1 To UBound(v1, 1)
        col = 1
        For c = 1 To UBound(v1, 2)
            ru(row, col) = v1(r, c)
            col = col + 1
        Next
        row = row + 1
    Next
    
    For r = 1 To UBound(v2, 1)
        col = 1
        For c = 1 To UBound(v2, 2)
            ru(row, col) = v2(r, c)
            col = col + 1
        Next
        row = row + 1
    Next

    MERGE = ru
End Function

위의 머지함수는 두 개의 셀영역을 입력받아 하나의 데이터로 만들어 준다. 그래서 VLOOKUP()을 사용하여 데이터를 찾을 수 있다. 가령 다음과 같이 사용할 수 있다.

위의 수식에서 MERGE()함수는 현재의 워크시트내 A11:Q20와 Sheet3의 A1:Q10 셀영역을 하나의 메모리 공간으로 만들어 VLOOKUP의 Table_array매개변수로 넘겨준다. 즉 두개의 데이터 크기가 다르고 다른 워크시트에 있더라도 작동한다. 물론 VLOOKUP외에 기타 INDEX, MATCH와 같은 함수에서도 사용할 수 있을 것이다.

그러나 MERGE()함수의 한계는 두 개의 셀 영역만 다룬다는 점이다. 만일 갯수에 상관없이 매개변수를 받아 처리하려면 ParamArray형 매개변수를 이용하여 MERGE()함수를 뜯어 고쳐햐 하는데...요건 다음 기회에...