검색 수가 많을 때 vlookup을 최적화하는 방법(VLOOKUP 대체 방법)

검색 수가 많을 때 vlookup을 최적화하는 방법(VLOOKUP 대체 방법)

vlookup을 대체할 수 있는 기능을 찾고 있습니다.또한 관심사 내에서 퍼포먼스가 향상되고 있습니다.

콘텍스트는 다음과 같습니다.

• 큰 {key;data} 데이터 세트가 있습니다(약 100,000 레코드).
• 데이터 세트에 대해 많은 VLOOKUP 작업을 수행하고 싶다(일반적으로 데이터 세트 전체의 순서를 변경하는 것이 목적)
• 데이터 세트에 중복된 키가 없습니다.
• 정확한 일치만을 찾고 있습니다(마지막 인수).VLOOKUP이FALSE)

설명하는 스키마:

레퍼런스 시트: ("sheet1")

        A           B
     1
     2  key1        data1
     3  key2        data2
     4  key3        data3
   ...  ...         ...
 99999  key99998    data99998
100000  key99999    data99999
100001  key100000   data100000
100002

룩업 시트:

        A           B
     1
     2  key51359    =VLOOKUP(A2;sheet1!$A$2:$B$100001;2;FALSE)
     3  key41232    =VLOOKUP(A3;sheet1!$A$2:$B$100001;2;FALSE)
     4  key10102    =VLOOKUP(A3;sheet1!$A$2:$B$100001;2;FALSE)
   ...  ...         ...
 99999  key4153     =VLOOKUP(A99999;sheet1!$A$2:$B$100001;2;FALSE)
100000  key12818    =VLOOKUP(A100000;sheet1!$A$2:$B$100001;2;FALSE)
100001  key35032    =VLOOKUP(A100001;sheet1!$A$2:$B$100001;2;FALSE)
100002

Core i7 M620 @2.67 GHz에서는 최대 10분 만에 계산됩니다.

이러한 맥락에서 더 나은 성능을 제공하는 VLOOKUP의 대체 방법이 있습니까?

나는 다음과 같은 대안을 고려했다.

• VLOOKUP 어레이 공식
• 일치/인덱스
• VBA(사전 사용)

비교한 퍼포먼스는 다음과 같습니다.

• VLOOKUP 단순식 : 10분
• VLOOKUP 어레이 공식 : 최대 10분 (1:1 퍼포먼스 지수)
• MATCH / INDEX : 최대 2분 (5:1 퍼포먼스 지수)
• VBA(사전 사용): 최대 6초(100:1 퍼포먼스 인덱스)

동일한 참조 시트 사용

1) 룩업 시트: (vlookup array 수식 버전)

         A          B
     1
     2   key51359    {=VLOOKUP(A2:A10001;sheet1!$A$2:$B$100001;2;FALSE)}
     3   key41232    formula in B2
     4   key10102    ... extends to
   ...   ...         ... 
 99999   key4153     ... cell B100001
100000   key12818    ... (select whole range, and press
100001   key35032    ... CTRL+SHIFT+ENTER to make it an array formula)
100002

2) Lookup Sheet : (일치+인덱스 버전)

         A           B                                       C
      1
      2  key51359    =MATCH(A2;sheet1!$A$2:$A$100001;)       =INDEX(sheet1!$B$2:$B$100001;B2)
      3  key41232    =MATCH(A3;sheet1!$A$2:$A$100001;)       =INDEX(sheet1!$B$2:$B$100001;B3)
      4  key10102    =MATCH(A4;sheet1!$A$2:$A$100001;)       =INDEX(sheet1!$B$2:$B$100001;B4)
    ...  ...         ...                                     ...
  99999  key4153     =MATCH(A99999;sheet1!$A$2:$A$100001;)   =INDEX(sheet1!$B$2:$B$100001;B99999)
 100000  key12818    =MATCH(A100000;sheet1!$A$2:$A$100001;)  =INDEX(sheet1!$B$2:$B$100001;B100000)
 100001  key35032    =MATCH(A100001;sheet1!$A$2:$A$100001;)  =INDEX(sheet1!$B$2:$B$100001;B100001)
 100002

3) 룩업 시트: (vbalookup 버전)

       A          B
     1
     2  key51359    {=vbalookup(A2:A50001;sheet1!$A$2:$B$100001;2)}
     3  key41232    formula in B2
     4  key10102    ... extends to
   ...  ...         ...
 50000  key91021    ... 
 50001  key42       ... cell B50001
 50002  key21873    {=vbalookup(A50002:A100001;sheet1!$A$2:$B$100001;2)}
 50003  key31415    formula in B50001 extends to
   ...  ...         ...
 99999  key4153     ... cell B100001
100000  key12818    ... (select whole range, and press
100001  key35032    ... CTRL+SHIFT+ENTER to make it an array formula)
100002

NB : 어떠한 이유로(외부 내부) vbalookup이 한번에 65536개 이상의 데이터를 반환하지 못합니다.그래서 저는 배열 공식을 둘로 나누어야 했습니다.

및 관련 VBA 코드:

Function vbalookup(lookupRange As Range, refRange As Range, dataCol As Long) As Variant
  Dim dict As New Scripting.Dictionary
  Dim myRow As Range
  Dim I As Long, J As Long
  Dim vResults() As Variant

  ' 1. Build a dictionnary
  For Each myRow In refRange.Columns(1).Cells
    ' Append A : B to dictionnary
    dict.Add myRow.Value, myRow.Offset(0, dataCol - 1).Value
  Next myRow

  ' 2. Use it over all lookup data
  ReDim vResults(1 To lookupRange.Rows.Count, 1 To lookupRange.Columns.Count) As Variant
  For I = 1 To lookupRange.Rows.Count
    For J = 1 To lookupRange.Columns.Count
      If dict.Exists(lookupRange.Cells(I, J).Value) Then
        vResults(I, J) = dict(lookupRange.Cells(I, J).Value)
      End If
    Next J
  Next I

  vbalookup = vResults
End Function

주의:Scripting.Dictionary하기 위해 참조인이 필요하다Microsoft Scripting Runtime수동으로 추가해야 합니다(Excel VBA 창의 [Tools]-> [ References ]메뉴).

결론:

이 경우 사전을 사용하는 VBA는 VLOOKUP을 사용하는 경우보다 100배, MATCH/INDEX를 사용하는 경우보다 20배 빠릅니다.

또한 다른 곳에서 볼 수 있는 내 아이디어가 아닌 "double Vlookup" 방법을 사용하는 것도 고려해 보는 것이 좋습니다.당신이 1장에 기술한 것과 동일한 데이터 세트를 사용하여 2장의 10만 개의 룩업 값(랜덤 정렬)을 테스트하여 4초 미만으로 시간을 쟀습니다.코드도 조금 더 간단합니다.

Sub FastestVlookup()

    With Sheet2.Range("B1:B100000")
        .FormulaR1C1 = _
        "=IF(VLOOKUP(RC1,Sheet1!R1C1:R100000C1,1)=RC1,VLOOKUP(RC1,Sheet1!R1C1:R100000C2,2),""N/A"")"
        .Value = .Value
    End With

End Sub

Excel 2013으로 전환하여 Data Model을 사용합니다.여기서 양쪽 테이블에서 원하는 ID 키를 사용하여 열을 정의하고 피벗 테이블에서 이러한 두 테이블을 관계로 바인딩할 수 있습니다.꼭 필요한 경우에는 Getpivotdata()를 사용하여 첫 번째 테이블을 채울 수 있습니다.약 25만 행 테이블에서 vlookup을 수행했습니다.1시간 후 Excel 계산 중지.데이터 모델에서는 10초도 걸리지 않았습니다.

값 고정: 사전을 빌드할 때 빈 셀을 확인합니다.셀이 공백일 경우 를 종료합니다.

언급URL : https://stackoverflow.com/questions/18656808/how-to-optimize-vlookup-for-high-search-count-alternatives-to-vlookup