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

Общее

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

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

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

Особенности таблицы для поиска (в примере это 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;ЛОЖЬ))

Leave a Reply