Excel V lookup 함수 제한 256자 극복 방법

Excel V lookup 함수 제한 256자 극복 방법

저는 여러 개의 값을 가진 엑셀 어레이를 가지고 있습니다.256자 미만인 경우도 있고 256자 이상인 경우도 있습니다.

샘플 문자열을 사용하여 V lookup을 하려고 했을 때 256자 이하의 행과 일치할 때 결과를 얻을 수 있습니다.256자보다 큰 행의 경우 '#N/A'를 반환합니다.

이 한계를 극복할 수 있는 V lookup이나 엑셀의 다른 내장 기능을 사용하는 방법이 있을까요?

VLOOKUP을 이렇게 사용하시는 분들은.

=VLOOKUP(A2,D2:Z10,3,FALSE)

즉, D2:D10에서 A2를 찾아보고 F2에서 결과를 반환하는 것입니다.F10 그럼 대신 이 공식을 사용해보세요.

=INDEX(F2:F10,MATCH(TRUE,INDEX(D2:D10=A2,0),0))

필요에 따라 범위 변경

편집:

A2의 값으로 샘플을 만들어 보았습니다.A10은 G2:G10과 같지만 순서가 다릅니다.각 값의 길이는 열 B에 표시되며, 열 C의 VLOOKUP은 col A 값 > 255자에서 실패하지만 col D의 INDEX/MATCH 공식은 모든 경우에 작동합니다.

https://www.dropbox.com/s/fe0sb6bkl3phqdr/vlookup.xls

저도 같은 문제가 있어서 이 커스텀 프리미티브 vlookup을 작성했습니다.그것은 세포의 가치관의 길이에 상관이 없습니다.

Function betterSearch(searchCell, A As Range, B As Range)
        For Each cell In A
            If cell.Value = searchCell.Value Then
                    betterSearch = B.Cells(cell.Row, 1)
                    Exit For
            End If
            betterSearch = "Not found"
        Next

End Function

PS 전문가가 작성한 VLOOKUP 원본이 이 10줄 기능보다 이 특정한 경우에 더 허술하게 구현되는 이유가 궁금할 수밖에 없습니다.

이는 에 대한 대체의 지연입니다.Match()그리고 또한 최적화된 vba 코드입니다.betterSearch위에.

Public Function Match2(search As String, lookupArray As Range, Optional match_type As Integer = 0) As Long
  Application.Volatile
  Dim vArray As Variant
  vArray = lookupArray.Value
  For i = 1 To UBound(vArray, 1)
    If match_type = 0 Then
      If search = vArray(i, 1) Then
        Match2 = i
        Exit Function
      End If
    Else
      If match_type = -1 Then
        If search <= vArray(i, 1) Then
          Match2 = i
          Exit Function
        End If
      Else
        If search >= vArray(i, 1) Then
          Match2 = i
          Exit Function
        End If
      End If
    End If
  Next
End Function

용도:

Index(rangeA, Match2(LookupValue, LookupRange, 0)

위의 앤스는 다음과 같이 말했습니다.

전문가가 작성한 원래의 VLOOKUP이 이 10줄 기능보다 이 특정한 경우에 더 허술하게 구현되는 이유가 궁금할 수밖에 없습니다.

최적화 및 성능.문자 수를 255자로 제한하는 경우 CPU에서 두 번의 작업만 수행하면 되지만 가변 길이 문자열을 비교하는 경우 255자 너비를 반복적으로 비교해야 하기 때문에 CPU에서 더 많은 단계를 수행해야 합니다.VBA와 같은 프로그래밍 언어는 모든 하위 작업이 사용자를 보호하기 때문에 이를 많이 모호하게 합니다.

예를 들어, 고정 길이 5의 두 문자열 "Hello"와 "abc"를 비교하면 CPU에서 다음과 같은 작업을 수행할 수 있습니다.

   0100100001100101011011000110110001101111 //Hello
-  0110000101100010011000110000000000000000 //abc
= -0000000000011000111111001111011010010100 //-419231380

이제 결과가 다음과 같은지 간단하게 질문할 수 있습니다.< 0,> 0,= 0또는 심지어 대략 0.이 작업은 2개의 CPU 작업으로 수행할 수 있습니다.셀의 길이가 가변적인 경우(공식도 가변적인 경우), 먼저 CPU를 사용하여 값의 끝을 0으로 패딩하여 문자열을 동일한 길이로 가져와야 작업을 수행할 수 있습니다.

XLookup은 더 이상 그러한 제한이 없습니다.그것으로 > 500자를 조회할 수 있었습니다.

언급URL : https://stackoverflow.com/questions/13202473/way-to-overcome-excel-vlookup-function-limit-of-256-characters