Excel: использование функции ВПР

Общее

В Excel есть полезная функция – ВПР. Позволяет на основе списка неких идентификаторов извлечь из таблицы значения атрибутов этих идентификаторов.

Сравнение ВПР с другими вариантами решения задачи:

• ВПР – подходит для небольших объемов данных. По опыту попытка получения данных по 100к значениям в таблице из 300к строк требовала неимоверное время для расчета и в результате пришлось решать задачу по-другому. Большей частью влияет таблица, в которой осуществляется поиск (в примере таблица с 300к строк). Hint: если нужно данные из одной книги скопировать без формул (в данном случае ВПР) в другую – можно использовать сохранение в CSV или промежуточное копирование в блокнот.
• скрипт bash/ruby/etc – т.к. большую часть времени работаю с консолью, этот вариант зачастую самый для меня простой. По сути подходит для любого объема данных, но большие сводки могут потребовать многопоточность (см. xargs, split) и решения сортировки данных после отработки разных потоков, что может потребовать больше времени, чем вариант с БД.
• БД (join) – создать две таблицы, сделать join на основе необходимого поля. Крайне рекомендую использовать как замену Excel в случае большого объема данных (больше 100к строк).

Пример

=ВПР(G2;table;2;ЛОЖЬ)

• G2 – столбец для поиска
• table – таблица в которой делается поиск
• 2 – столбец этой таблицы
• ЛОЖЬ – искать ли приближенное значение. Лучше не надо.

Особенности

В ВПР если не получается (отсутствует поле “присвоить имя” при ПКМ) присвоить имя таблице: Excel -> конструктор -> имя таблицы.

Особенности таблицы для поиска (в примере это table):

• проще всего задать имя таблицы если она большая (выделяем ячейки ctrl+a, ПКМ, “Присвоить имя”)
• задать таблицу через указание стобцов, а не значений таблицы, не получится т.к нужна именно таблица
• значения, по которым делается поиск, должно быть в первом столбце
• таблицу можно создать на отдельном листе
• как избавится от НД при впр (мешает сортировке по числам):

=ЕСЛИ(ЕОШИБКА(ВПР(A3;testtb;10;ЛОЖЬ));"";ВПР(A3;testtb;10;ЛОЖЬ))

=ЕСЛИ(ЕОШИБКА(ВПР(A3;testtb;10;ЛОЖЬ));0;ВПР(A3;testtb;10;ЛОЖЬ))

или даже на другой ВПР

=ЕСЛИ(ЕОШИБКА(ВПР(B2;test;4;ЛОЖЬ));ВПР(C2;testb;2;ЛОЖЬ);ВПР(B2;test;4;ЛОЖЬ))