Пишем свой VLOOKUP для того, чтобы не зависеть от стандартного (Excel functions)

[Dormidont]

Амбула Фабула
Заранее простите за занудство :)

[lnikitin]

Что-то мне подсказывает, что vba — куда старше, чем T-SQL. Так что это еще вопрос, что из них олдскульнее…

[OKComputer]

Дату рождения VBA отыскать не удалось. SQL аж с 1979 года ведет историю (с Вики). Но T-SQL конечно много позже появился. (Опять же дат в Вики нет)

[lnikitin]

VBA как известно появился в Excel 5.0, так что с 1993 года. T-SQL — я даже не догадываюсь когда появился, но в 1993 году насколько мне известно появился и MSSQL for WinNT. Вот только я не знаю, был ли в нем Transact-SQL… А сравнивал я с T-SQL лишь из-за его упоминания в статье.

[playground]

На самом деле к ВПР(), а так же практически ко всему что в Экселе связано с поиском или выборками есть масса претензий :)
Я в свое время написал аналог ВПР() за исключением:
— не нужно чтобы значения в столбце поиска были отсортированы.
— ищет только точное совпадение
— возвращает не значение, а номер строки (соответствующая строчка закомментирована)
— возвращает номер строки ПОСЛЕДНЕГО найденного совпадения.

Function VPRL(SearchValue As Variant, Table As Range, SearchColumnNum As Integer, ResultColumnNum As Integer)
Dim i As Integer
For i = 1 To Table.Rows.Count
If Table.Cells(i, SearchColumnNum).Value = SearchValue Then
' VPRL = Table.Cells(i, ResultColumnNum)
VPRL = i
End If
Next i
End Function

[OKComputer]

"— ищет только точное совпадение"
Чтобы искало совпадения независимо от регистра я и поставил UCASE.

Также известный баг — если ищешь среди цифровых значений а в одной из таблиц они представлены как «число в виде текста» — зеленый треугольничек — то ВПР ничего не найдет. Опять же лечится написанием данной функции, при сравнении использовал «If CStr(Table.Cells(i, SearchColumnNum)) = SearchValue Then» =>
Заработало

[lnikitin]

ПОИСКПОЗ она же МАТCH?

[playground]

Да нет, искать надо было в другом столбце, все эти истории с сортировкой для ПОИСКПОЗ тоже актуальны, на сколько я помню (у меня был неотсортированные данные), ну и ПОИСКПОЗ вроде бы тоже находит первый элемент, а мне надо было последний.

[Ngels]

Да ну что вы такое новорите про сортировку… В функции ВПР есть последний параметр, который отвечает за «точность», при его значении=0 сортировка не нужна… Хотя стоит утрчнить версию Excel…

[playground]

Да, интервальный_просмотр. Но как вы верно заметили он отвечает не за сортировку, там это просто побочный эффект.

[Monca]

>знает как убрать дубликаты из списка

Я не знаю, научите.
Стыдно сказать, но без сортировки и ручной правки не умею…

[OKComputer]

На этом ПК у меня 2007 офис, в нем есть просто кнопка, а в 2003 вроде как при специальной вставке есть опция? До понедельника проверить не смогу…

[xen32]

Data -> Remove duplicates
Либо пихнуть всё в сводную таблицу. Второй способ более кошерен, так как не страдают данные.

[sargsyan]

index + match и никаких проблем с сортировками или порядком столбцов
index + match + match для совсем продвинутых -> получаеться резиновый поиск по x,y

[BiBo]

Как вариант, для работы с массивами данных, на мой субъективный взгляд, очень удобно пользовать Google Refine, очень мощно, удобно и свободно.

[unkinddragon]

На тему «фишек» — недавно пришлось сравнивать много-много строк в разных таблицах по группам столбцов — пригодились хэшироваие, создавал в нужных таблицах столбец, куда записывал md5.

Исходный код модуля pastebin.com/AA7d7ewL
Там просто функция MD5(st as String) as String.

[OKComputer]

Дано не заходил сюда. Классная «фишка».

[OKComputer]

Также хочу здесь оставить код аналогичный стандартному ВПР

Function VLOOKUP2(SearchValue As Variant, Table As Range, ResultColumnNum As Integer)
Dim i As Long
SearchColumnNum = 1
Rem MsgBox (SearchValue)
For i = 1 To Table.Rows.Count
Rem MsgBox (Table.Cells(i, SearchColumnNum))
If UCase(RTrim(Table.Cells(i, SearchColumnNum))) = RTrim(UCase(SearchValue)) Then
VLOOKUP2 = Table.Cells(i, ResultColumnNum)
Exit For
End If
Next i
End Function

[OKComputer]

Также бывает полезен следующий несложный макрос, он обеспечивает защиту от зависаний при пересчете больших сегментов

Sub SelectionCalculate()
Set ss = Selection
For Each cl In Selection.Cells
cl.Select
cl.Calculate
Next cl
ss.Select
End Sub