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

Заядлые эксельщики прекрасно освоили функцию ВПР. Действительно, трудно переоценить ее значение. ВПР умеет проделывать такие фокусы, которые кажутся просто чудесами для тех, кто мучается подстановкой данных из одной таблицы в другую. Однако ВПР довольно капризная функция и требует «нежного» обращения. Например, ВПР может выдавать ошибки #Н/Д, хотя уникальные критерии присутствуют в обоих таблицах.

В этой заметке разберем несколько приемчиков, которые позволят использовать ВПР на всю мощь, а также с легкостью выходить из затруднительных ситуаций.
Для начала напомню, как, собственно, работает ВПР. Представим, что есть две таблицы:

Две таблицы

Требуется цены из второй таблицы подтянуть в первую. В качестве критерия здесь используется код. Ниже показаны этапы вычисления ВПР.

Работа ВПР

Видно, что вторая таблица меньше первой, т.е. некоторые коды в ней отсутствуют. В этом случае ВПР для отсутствующих позиций выдает ошибку #Н/Д.

Результат работы ВПР

Появление таких ошибок, кстати, можно использовать для пользы дела (см. ролик в предыдущей статье о ВПР). В то же время наличие ошибок может отразиться на дальнейших расчетах. Поэтому вместе с функцией ВПР часто используют функцию ЕСЛИОШИБКА, которая «заглушает» ошибки #Н/Д, выдавая вместо них то, что мы сами захотим. Это могут быть какие-то пояснения типа «Данные отсутствуют» или еще что-то. Но чаще вместо ошибок уместнее выдавать 0 или пустую ячейку, чтобы эти значения могли восприниматься другими функциями в качестве обычных чисел (если нужно рассчитать сумму, среднюю, коэффициент вариации и т.д.).

Вот как выглядит подобная конструкция для нашего примера.

ЕСЛИОШИБКА и ВПР

Как видно, ошибок больше нет, а вместо них пустые ячейки.

Одна из распространенных причин появления ошибок заключается в несовпадении форматов критериев в двух таблицах. Известно, что текстовый и числовой форматы воспринимаются функцией ВПР как разные значения. Функция ЕСЛИОШИБКА здесь не уместна, т.к. нужные данные присутствуют, просто они не «подтягиваются». Здесь возможны два варианта.

Первый случай, когда критерии в первой таблице сохранены как числа, а критерии во второй таблице – как текст.

Различные форматы у критериев

В ячейках с числами, сохраненными как текст, в левом верхнем углу появляется зелененький треугольник. Да, можно поступить очень просто. Достаточно выделить все такие числа и в раскрывающемся списке выбрать «Преобразовать в число».

Преобразование текстового формата в числовой

И такое решение используется довольно часто. Но и оно не всегда подходит. Например, когда данные из второй таблицы регулярно выгружаются из какой-нибудь базы данных типа 1С. В подобных файлах вообще все сохранено как текст. И если мы планируем постоянно использовать такие данные, вставляя их в заранее подготовленный диапазон, то лучше, чтобы формулы работали без дополнительного вмешательства. Тем более, что в «тяжелых» файлах с большим количеством формул, Excel вообще может подвиснуть, т.к. после каждого изменения формата все формулы пересчитываются заново. В общем, бывают ситуации, когда нужно настроить работу ВПР, чтобы корректный расчет не зависел от формата данных.

Автоматически изменить формат критерия во второй таблице мы не можем, т.к. ссылка там ведет на целый диапазон. Придется вмешиваться в ссылку на критерий в первой таблице. Для этого потребуется дописать функцию ТЕКСТ, которая изменит числовой формат на текстовый. Синтаксис функции ТЕКСТ предполагает ссылку на изменяемую ячейку, а также необходимый формат текста. В нашем случае в качестве формата достаточно указать #. Теперь форматы критериев в обоих таблицах будут совпадать. Ниже картинка с готовой формулой.

Преобразование критерия в текст внутри ВПР

Две ошибки по-прежнему связаны с тем, что эти товары отсутствую во второй таблице. Чтобы их заглушить, можно вновь воспользоваться функцией ЕСЛИОШИБКА.

Вторая ситуация, заключается в том, что «текстом» являются критерий из первой таблицы. Форматы снова не совпадают.

Текстовый критерий в первой таблице

Как и в прошлый раз, будем вносить коррективы в функцию ВПР. Преобразовать «текст» в «число» еще проще. Достаточно к ссылке на «текстовый» критерий приплюсовать 0.

Преобразование текста в число внутри ВПР

Очень просто.

Бывает еще и третья, смешанная ситуация. Она встречается гораздо реже. Это когда в первой и второй таблице критерии сохранены и как число, и как текст, вперемешку. Здесь потребуется задействовать сразу все описанные выше функции: ЕСЛИОШИБКА, ТЕКСТ и +0. Вначале прописываем ЕСЛИОШИБКА и в качестве первого аргумента этой функции записываем ВПР с какой-либо конструкцией для изменения формата. Например, ВПР с формулой ТЕКСТ. В качестве второго аргумента (т.е. того, что должно быть в случае ошибки) записываем вторую конструкцию ВПР с +0. Таким образом, если ВПР с функцией ТЕКСТ не выдает ошибку, значит все ОК. Но если первая конструкция возвращает ошибку #Н/Д, то функция ЕСЛИОШИБКА подставляет вторую конструкцию – ВПР с +0. Другими словами, мы вначале принудительно делаем все критерии текстовыми, а затем, числовыми. Таким образом ВПР проверяет оба формата. Один из них совпадет с форматом во второй таблице. Немного громоздко получается, но в целом все работает.

Смешанный формат критериев

Отсутствующие критерии по-прежнему вызывают ошибку #Н/Д. В таком случае всю формулу можно еще раз «обернуть» в ЕСЛИОШИБКА.

В качестве критерия обычно рекомендуется брать некоторый уникальный код, в котором опечатки, характерные для текста, маловероятны. Но иногда все-таки кода нет и критерием выступает текст (названия организаций, фамилии людей и т.д.). В этом случае возможны случайные ошибки в написании. Одна из распространенных ошибок – это лишние пробелы. То есть наличие ненужных пробелов в начале текста, в конце или в середине, когда их более чем по одному между словами. Проблема решается аналогичным путем. Достаточно применить функцию СЖПРОБЕЛЫ для всех критериев. Сделать это можно внутри формулы ВПР, а можно и предварительно пройтись по всем критериям в обоих таблицах. Кому как удобней.

И последний на сегодня совет. Если во второй таблице много столбцов, да часть из них еще скрыта или сгруппирована, то подсчитать напрямую количество столбцов между критерием и нужными данными, весьма непросто. Есть прием, который позволяет вообще не считать эти столбцы. Для этого во время выделения второй таблицы следует посмотреть в нижний правый угол выделяемого диапазона. Там появляется подсказка о количестве выделенных строк и столбцов. Запоминаем число столбцов и вносим в формулу ВПР.

Быстрый подсчет столбцов для ВПР

Здорово экономит время.

На этом все. Применяйте правильные функции и работа в Excel заиграет новыми красками.

До свидания.

Поделиться в социальных сетях:
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •