У меня есть массив excel с несколькими значениями. Некоторые содержат менее 256 символов, а некоторые имеют длину более 256.
Когда я попытался выполнить VLookup, используя образец строки, я могу получить результаты, когда он соответствует строкам с менее чем 256 символами. Для строк, содержащих более 256 символов, возвращается «#Н/Д».
Есть ли способ использовать Vlookup или использовать какую-либо другую встроенную функцию в Excel, чтобы преодолеть это ограничение?
Если вы используете функцию ВПР, как это
=VLOOKUP(A2,D2:Z10,3,FALSE)
то есть поиск A2 в D2: D10 и возврат результата из F2: F10, а затем попробуйте эту формулу вместо этого
=INDEX(F2:F10,MATCH(TRUE,INDEX(D2:D10=A2,0),0))
изменить диапазоны по мере необходимости
Изменить:
Я смоделировал образец здесь — значения в A2: A10 такие же, как в G2: G10, но в другом порядке. Длина каждого из этих значений показана в столбце B, ВПР в столбце C не выполняется для значений столбца A > 255 символов, но формула ИНДЕКС/ПОИСКПОЗ в столбце D работает во всех случаях.
https://www.dropbox.com/s/fe0sb6bkl3phqdr/vlookup.xls
У меня была та же проблема, и я написал этот пользовательский примитивный поиск. Его не волнует длина значений ваших ячеек.
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 Невольно возникает вопрос, почему оригинальная ВПР, написанная профессионалами, реализована в данном конкретном случае хуже, чем эта 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)
Выше Анс сказал:
Невольно удивляешься, почему оригинальная ВПР, написанная профессионалами, реализована в данном конкретном случае хуже, чем эта 10-строчная функция?
Оптимизация и производительность. Если вы ограничиваете количество символов до 255, это требует только 2 операций на ЦП, тогда как сравнение строк переменной длины требует гораздо больше шагов на ЦП, потому что вам приходится многократно сравнивать ширину 255 символов. Языки программирования, такие как VBA, сильно скрывают это, потому что все подоперации выполняются за вас.
Например, чтобы сравнить 2 строки «Hello» и «abc» фиксированной длины 5, мы просто выполняем следующую операцию на процессоре:
0100100001100101011011000110110001101111 //Hello
- 0110000101100010011000110000000000000000 //abc
= -0000000000011000111111001111011010010100 //-419231380
Теперь вы можете просто спросить, является ли результат < 0, > 0, = 0 или даже приблизительно равным 0. Это можно сделать за 2 операции процессора. Если ячейки имеют переменную длину (а также формулы), то сначала вам придется использовать ЦП, чтобы дополнить конец значения нулями, чтобы получить строки одинаковой длины, прежде чем вы сможете выполнять операции.
