[vba] 범위 대 범위 한번에 비교

지식인에서 답변을 달기 위해 코딩을 하다가 문득 생긴 의문이다.
범위대 범위를 한번에 비교할 수 있는 방법은 없을까?

그래서 지식인에 질문을 했다.

A1:C1 범위와 A2:C2 범위의 값을 각각 비교하고 싶어서

(즉, A1과 A2를 비교, B1과 B2를 비교, C1과 C2를 비교)

if  range("A1").resize(,3) = range("A2").resize(,3) then

이렇게 했더니 오류 메시지가 뜨네요.

If [a1] = [a2] and [b1] = [b2] and [c1] = [c2] then

이렇게 하나씩 비교하는 수 밖에 없나요?

범위대 범위로 한번에 비교하는 방법이 있는지 궁금합니다.

두 개의 답변이 달렸다. 그것도 지식인 랭킹 1,2위를 하시는 분들께서!

한분의 답은 For ~ Next 순환문을 이용하는 방법이다.

다음은

1행의 1열~5000열까지의 값과

3행 1열: 5000행 5000열 범위의 값을 비교하여

1열부터 5000열까지 모두 같으면 해당 행 끝에 "O"표시를, 하나라도 다르면 "X"표시를 하는 코드이다.

Sub 일치여부2()
For Each r In [a3:a5000]
    For i = 1 To 5000
        If Cells(r.Row, i) <> Cells(1, i) Then
        Cells(r.Row, 5001) = "X"
        flg = 1
        Exit For
        End If
    Next i
      If flg = 0 Then Cells(r.Row, 5001) = "O"
      flg = 0
 Next r
End Sub

하지만 이 방법은 내가 예상할 수 있는 방법이어서, 뭔가 색다른 방법이 없을까 생각하고 있는데,

다른 한 분의 답변이 올라왔다.

이 분의 답변은 Transpose와 Join을 이용하는 방법이다.

Sub 일치여부()
    Dim wf As WorksheetFunction
    Set wf = WorksheetFunction
  
    Dim v$
    v = Join(wf.Transpose(wf.Transpose(Range("a1").Resize(, 5000))))
  
    Dim a As Range
    For Each a In [a3:a5000]
        If Join(wf.Transpose(wf.Transpose(a.Resize(, 5000)))) = v Then
            a(1, 5001).Value = "O"
        Else
            a(1, 5001).Value = "X"
        End If
    Next
End Sub

가운데 보면 Transpose를 이중으로 사용한 곳이 있다.

wf.Transpose(wf.Transpose......

이 부분에 대한 이유를 묻자, 다음과 같은 답변을 얻었다.

세로범위는 1번을 쓰면 1차원배열
가로범위는 2번을 써야 1차원배열이 됩니다.
1차원 배열을 Join()함수로 문자화 하기 위해...

범위는 가로든 세로든 기본적으론 2차원 배열입니다.
배열은 가로배열이 기본이라고 생각하시면 되구요.
세로 범위는 1변 변환하면 가로 1차원으로 바뀌므로 1번에끝
가로범위는
- 1차변환 = 세로 1차원
- 2차변환 = 가로 1차원
이렇게 2번 해야 가로 1차원으로 쓸수있어요.

좋은 걸 배웠다.

그럼, 이 두 방법의 속도 차이는 어떨까?

일단 100개 정도의 데이터에서는 별 차이가 없었다.

1,000개 정도의 데이터가 되자, Transpose를 쓰는 방법이 1초 정도 빨랐다.

그럼 10,000개는?

10,000 x 10,000 의 데이터를 입력하자, 엑셀이 뻗어버렸다.

5,000 x 5,000 으로 낮추었더니 겨우 돌아간다.

이 상태에서 돌려보았더니, For ~ Next 문을 쓰는 쪽이 훨씬 빨랐다. (3초 대 1분 3초)

그러고보니, Transpose를 쓰는 쪽은 1열부터 5000열까지의 데이터를 하나로 묶어서 비교하는 것이니까 당연히 메모리도 많이 잡아먹고 오래걸리겠지.... 하고 정리를 하던차에,

1행하고 같은 데이터가 몇 개 있는지 세보려했다. 그런데, 1행과 일치하는 행이 하나도 없다!

데이터를 수작업으로 넣을 수 없어서 Randbetween(1,2) 를 5,000x 5,000 범위에 모두 넣고 서로 비교하려했는데, 그 많은 데이터 중에서 1행과 같은게 하나도 없다니!

(근데 또 생각해보면 당연한게, 1행에서 1과 2의 조합만으로 5000열까지 배열하면 나올 수 있는 경우의 수는 2의 5,000제곱이다! 5,000행 중에서 일치하는 게 나올 확률이 희박한 건 당연한 듯.)

아무튼, 그러면 For - Next 구문에서는 1열부터 5000열까지 비교하다가 중간에 하나라도 틀리면 순환문을 빠져나오도록 만들었으니 계산을 끝까지 해야하는 Transpose보다 빠른 게 당연하겠다고 생각하고, 실험을 좀 바꾸어보았다.

1000행부터 5000행까지는 1행의 데이터를 복사해서 그대로 붙였다. 즉, 이대로라면 "O"표시가 나오는 행은 4000개나 된다. 즉, For ~ Next 구문도 여기서는 1열부터 5000열까지 꼼짝없이 빠져나가지 못하고 비교작업을 해야한다는 뜻이다. 여기서도 For ~ Next 구문이 더 빠를까?

실험 결과는, Transpose는 이전과 마찬가지로 1분 3초 걸린 반면, For ~ Next 구문은 하세월이었다. '응답없음' 비슷한 현상을 보이면 꼼짝을 않기에 강제로 매크로를 중단시켰는데, 그때까지 1500행 정도에서 계산을 하던 중이었다.

결론은, 데이터가 소량일 때는 둘 다 가능하고, 순환문 도중에 결과가 나와 끝까지 비교하지 않아도 되는 일이 많을 때는 For ~ Next 구문이 빠르고, 안정적으로 꾸준한 성능을 보이는 것은 Transpose 구문인 것 같다.