Если вам нужно найти что-то в таблице или диапазоне по строкам, используйте В ПРОСМОТР. Например, можно найти цену автомобильной части по номеру части или имя сотрудника на основе его ИД.
Самая простая функция ВПР означает следующее:
=В.ПРОСМОТР(то, что вы хотите найти, где ее искать; номер столбца в диапазоне, содержащего возвращаемую величину, возвращает приблизительное или точное совпадение, обозначенные как 1/ИСТИНА или 0/ЛОЖЬ).
Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).
Используйте функцию ВПР для поиска значения в таблице.
Значение для поиска. Иного значения должно быть в первом столбце диапазона ячеек, который указан в table_array.
Например, если массив таблицы охватывает ячейки B2:D7, lookup_value должны быть в столбце B.
Искомое_значение может являться значением или ссылкой на ячейку.
Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Можно использовать именуемый диапазон или таблицу, а не ссылки на ячейки, а имена в аргументе.
Номер столбца (начиная с 1 в левом большинстве столбцов table_array),содержащий возвращаемую величину.
Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
Приблизительное совпадение: 1/ИСТИНА предполагает, что первый столбец таблицы отсортжен в алфавитном или числовом порядке, а затем будет выполнять поиск ближайшего значения. Это способ по умолчанию, если не указан другой. Например, =ВКП(90;A1:B100;2;ИСТИНА).
Точное совпадение: 0/ЛОЖЬ ищет точное значение в первом столбце. Например, =ВКП(«Кузнецов»;A1:B100;2;ЛОЖЬ).
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
Значение, которое вам нужно найти, то есть искомое значение.
Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона указать B2:D11, следует посчитать B первым столбцом, C — вторым и так далее.
При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
=ВLOOKUP(искомые значения, диапазон, содержащий искомые значения, номер столбца в диапазоне, содержащий возвращаемую величину, приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ)).
Примеры
Вот несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
Функцию ВЛОП можно использовать для объединения нескольких таблиц в одну, если одна из таблиц имеет поля, общие для всех остальных. Это особенно полезно, если вам нужно поделиться книгой с людьми, у которых есть более старые версии Excel, которые не поддерживают функции данных с несколькими таблицами в качестве источников данных, путем объединения источников в одну таблицу и изменения источника данных функции данных в новую таблицу, функцию данных можно использовать в более старых версиях Excel (при условии, что функция данных поддерживается более старой версией).
Здесь столбцы A–F и H имеют значения или формулы, которые используют только значения на этом сайте, а остальные столбцы используют В ПРОСМОТР и значения столбцов A (код клиента) и B (Доверенность) для получения данных из других таблиц.
Скопируйте таблицу с общими полями на новый и придать ей имя.
Чтобы открыть диалоговое окно Управление отношениями, > в > управления отношениями нажмите кнопку Data > Data Tools (Управление отношениями).
Для каждой из указанных связей обратите внимание на следующее:
Поле, которое связывает таблицы (в скобки в диалоговом окне). Это первый lookup_value для формулы ВЛКП.
Имя связанной таблицы подсмотра. Это первый table_array в формуле ВЛИО.
Поле (столбец) в связанной таблице подытовки с данными, которые должны быть в новом столбце. Эта информация не отображается в диалоговом оке Управление связями. Чтобы узнать, какое поле нужно извлечь, необходимо посмотреть в связанной таблице подыска. Обратите внимание на номер столбца (A=1) — это col_index_num формуле.
Чтобы добавить поле в новую таблицу, введите формулу в первом пустом столбце СРОТ, используя сведения, собранные на шаге 3.
В нашем примере в столбце G для получения данных «Ставка выставления счета» из четвертого столбца (col_index_num = 4) из таблицы «Доверенности» используется столбец «Доверенность» lookup_value(table_array) с формулой =ВЛП([@Attorney];tbl_Attorneys;4;ЛОЖЬ).
В формуле также можно использовать ссылку на ячейку и ссылку на диапазон. В нашем примере это будет =ВЛВП(A2;’Поверенные’! A:D,4;ЛОЖЬ).
Продолжайте добавлять поля, пока не будут добавлены все необходимые поля. Если вы пытаетесь подготовить книгу, содержащую функции данных, которые используют несколько таблиц, измените источник данных функции на новую таблицу.
Функция ВПР в Excel: пошаговая инструкция с 5 примерами
Давайте теперь рассмотрим, как сделать поиск с ВПР и как она работает. Рассмотрим приемы ее применения в формулах Excel.
Как сделать ВПР в Excel: понятная пошаговая инструкция.
Для начала на простом примере разберем, как работает функция ВПР в Excel. Предположим, у нас есть две таблицы. Первая – это прайс-лист с наименованиями и ценами. Вторая – это заказ на покупку некоторых из этих товаров. Искать в прайс листе нужный товар и руками вписывать в заказ его цену – занятие очень утомительное. Ведь прайс с ценами может насчитывать сотни строк. Нам необходимо сделать всё автоматически.
Нам необходимо обнаружить интересующее нас наименование в первом столбце и возвратить (то есть показать в ответ на наш запрос) содержимое из желаемого столбца той же строки, где находится наименование.
Наш прайс-лист расположен в столбцах А и В. Список покупок – в E-H. Допустим, первая позиция в списке покупок – бананы. Нам нужно в столбце A, где указаны все наименования, найти этот товар, затем его цену поместить в ячейку G2.
Для этого в G2 запишем следующую формулу:
А теперь разберем подробно, как сделать ВПР.
Получилось? Теперь просто скопируйте формулу из G2 в G3:G8.
Отчет о продажах готов.
Также чтобы понять, что такое точное совпадение, попробуйте в A5 или в E2 изменить наименование товара. К примеру, добавьте пробел в конце. Внешне ничего не изменилось, но вы сразу же получите ошибку #Н/Д. То есть, товар не был обнаружен. В то же время, таких случайных ошибок можно легко избежать, о чем мы поговорим отдельно.
Особо остановимся на четвертом параметре. Мы указали ноль (можно было написать ЛОЖЬ), что означает «точный поиск». А что, если забыть его указать и закончить номером столбца, из которого извлекаются нужные данные?
Давайте еще раз шаг за шагом разберем, что в этом случае будет происходить.
К сожалению, «бананы» были в нашем прайс-листе ниже, но до них просто «не дошел ход». И в список покупок теперь записана неправильная цена.
При помощи этой инструкции мы рассмотрели только основы. А как реально этим можно пользоваться?
Как работает функция ВПР в Excel: несколько примеров для «чайников».
Предположим, нам необходимо из списка сотрудников выбрать данные определенного человека. Посмотрим, какие здесь есть тонкости.
Во-первых, нужно сразу определиться: точный либо приблизительный поиск нам нужен. Ведь они предъявляют разные требования к подготовке исходных данных.
Использование точного и приблизительного поиска.
Посмотрите, какие результаты выборки цен мы получаем с использованием приблизительного поиска на неупорядоченном массиве данных.
Обратите внимание, что четвертый параметр равен 1.
Кое-что из результатов определено верно, но в большинстве случаев – ошибки. Функция продолжает просматривать данные столбца D с наименованиями товаров до тех пор, пока не встретит значение больше, чем заданное ей в качестве критерия поиска. Тогда она останавливается и возвращает цену.
Поиск цены на египетские бананы закончился на первой же позиции, так как во второй записаны сливы. А это слово по правилам алфавита стоит ниже, чем «Бананы Египет». Значит, дальше искать не нужно. Получили 145. И не важно, что это цена абрикосов. Поиск цены на сливы происходил до тех пор, пока в D15 не встретилось слово, которое по алфавиту стоит ниже: яблоки. Остановились и взяли цену из предыдущей строки.
А теперь взгляните, как должно было все происходить, если все делать верно. Всего лишь делаем сортировку, как указано стрелкой.
Вы спросите: «А зачем тогда этот неточный просмотр, если с ним столько проблем?»
Он отлично подходит для выбора значений из определенных интервалов.
Допустим, у нас установлена скидка для покупателей в зависимости от количества приобретенного товара. Нужно быстро рассчитать, сколько процентов положено на совершенную покупку.
Если у нас количество товара 11 единиц, то мы просматриваем столбец D до тех пор, пока не встретим число, большее 11. Это 20 и находится оно в 4-й строке. Останавливаемся здесь. Значит, наша скидка расположена в 3-й строке и равна 3%.
При работе с интервалами вида «от – до» такая методика вполне пригодна.
И еще один небольшой совет.
Применяйте именованный диапазон.
Для упрощения работы с формулами можно создать именованный диапазон и в дальнейшем ссылаться на него. В нашем случае назовем его «ДанныеСотрудников» (помните, что пробелы здесь недопустимы).
В ячейке B2 мы будем вводить нужную фамилию, а в ячейках С2:F2 запишем формулы:
Как видите, отличаются они только номером столбца, из которого будет извлечена нужная информация. Вместо ЛОЖЬ можно использовать 0.
Какие здесь преимущества?
Формула с именованным диапазоном выглядит намного более дружественно, наглядно и понятно. Вместо скучных и безликих координат вы видите идентификаторы, которые рождают у вас некоторые ассоциации. Согласитесь, “price” или «цена» – это наверняка информация о ценах.
Используя именованный диапазон, просто нажмите
Меню – Формула – Диспетчер имён.
Затем в списке диапазонов найдите нужный вам диапазон и откорректируйте его. Изменения автоматически будут применены во всех формулах.
Использование символов подстановки и другие тонкости критерия поиска.
Как и в предыдущих примерах, при вводе фамилии происходит точный поиск. Но есть несколько моментов, о которых мы ранее не упоминали.
Это целесообразно делать, если мы знаем только часть значения аргумента.
Но при этом будьте внимательны – снова будет найдено только первое подходящее совпадение, как это показано на скриншоте. Это очень важное ограничение, которое нужно обязательно учитывать.
Теперь давайте посмотрим, как можно работать с символами подстановки, если условия отбора не вводятся вручную, а берутся из таблицы Excel.
Формула в ячейке F2 выглядит следующим образом:
Здесь мы используем оператор «склеивания» строк &.
Конструкция «*»&D2&»*» означает, что к содержимому ячейки D2 добавляются с обоих сторон звездочки *. То есть, мы ищем любое вхождение этого слова – перед ним и после него могут быть любые другие слова и символы. Как, например, произошло с товаром «персики». Первый параметр будет в нашем случае выглядеть как «*персики*». При поиске такой конструкции приемлемым вариантом будут определены «Консервированные персики (Турция)».
Использование нескольких условий.
Предположим, у нас есть список имен и фамилий. Нам нужно найти нужного человека и вывести сумму его дохода.
В F2 используем следующую формулу:
Разберем пошагово, как в этом случае работает ВПР.
В начале мы формируем условие. Для этого при помощи оператора & «склеиваем» вместе имя и фамилию, а между ними вставляем пробел.
Не забываем при этом пробел заключить в кавычки, иначе Excel не воспримет его как текст.
Затем в таблице с доходами ищем ячейку с именем и фамилией, разделенными пробелом.
Дальше все происходит по уже отработанной схеме.
Можно попробовать подстраховаться на тот случай, если между именем и фамилией введено несколько пробелов. Знак пробела в формуле заменяем на знак подстановки «*».
Но при этом имейте в виду, что совпадение имени и фамилии уже будет не совсем точным. Подобный пример мы рассматривали чуть выше.
Более сложные и точные способы работы с несколькими условиями мы рассмотрим отдельно. Смотрите ссылки в конце.
«Умная» таблица.
И еще одна рекомендация: используйте «умную» таблицу.
Бывает очень удобно сначала преобразовать поисковую таблицу (прайс-лист) в «умную» с помощью команды Главная – Форматировать как таблицу (Home – Format as Table в английской версии Excel), и затем указать во втором аргументе использовать имя созданной таблицы. Кстати, оно ей будет присвоено автоматически.
В этом случае размеры списка товаров с ценами нас уже не будут беспокоить в будущем. При добавлении новых товаров в прайс-лист, либо их удалении, размеры «умной таблицы» сами подстроятся.
Специальные инструменты для ВПР в Excel.
Интерактивный мастер ВПР проведет вас через необходимые параметры конфигурации поиска, чтобы построить идеальную формулу для заданных вами критериев. В зависимости от вашей структуры данных он будет использовать стандартную функцию ВПР или формулу ИНДЕКС+ПОИСКПОЗ, если будет нужно извлечь значения слева от столбца поиска.
Вот что вам нужно сделать, чтобы получить формулу под вашу задачу:
Давайте посмотрим всё в действии.
Стандартный ВПР.
Запускаем мастер Vlookup Wizard. Указываем координаты основной таблицы и таблицы поиска, а также ключевой столбец (из которого будем брать значения для поиска), колонку поиска (в котором будем их искать) и колонку результата (из него в случае успеха берем соответствующее значение и вставляем в основную таблицу). Просто заполняем все нужные поля, как это показано на рисунке ниже. Руками прописываем (или обозначаем при помощи мышки) только диапазоны. Поля же просто выбираем из раскрывающегося списка.
Как и в предыдущих примерах, наша задача — подобрать цену для каждого товара, извлекая ее из прайс-листа. Ежели область поиска (Цена) является крайним левым столбцом в зоне поиска, то вставляется обычная формула ВПР для точного соответствия:
Ничего руками писать не нужно.
После нажатия кнопки Insert справа от колонки с наименованиями товаров будет вставлена дополнительная, которая будет озаглавлена так же, как и столбец результата. Сюда будут записаны все найденные значения цены, причем в виде формулы. При необходимости вы сможете ее подправить либо использовать в других таблицах.
«Левый» ВПР.
Когда колонка результата (Цена) находится слева от области поиска (Прайс), то мастер автоматически вставляет формулу ИНДЕКС+ПОИСКПОЗ:
Мы рассматривали левый ВПР в отдельной статье. Там же вы можете посмотреть и формулы для ручного ввода. Здесь же мы получаем их автоматически, не вникая в тонкости синтаксиса и правильности написания.
Дополнительный бонус! Благодаря грамотному использованию ссылок на ячейки, полученные формулы ВПР можно копировать или перемещать в любой столбец без необходимости обновлять ссылки.
Надеемся, что наши пошаговые инструкции по использованию функции ВПР в таблицах Excel были доступны и понятны даже «чайникам». Безусловно, эти самые несложные рекомендации можно использовать только в самых простых случаях. Все более сложное мы рассмотрим отдельно.
Функция ВПР() , английский вариант VLOOKUP(), ищет значение в первом (в самом левом) столбце таблицы и возвращает значение из той же строки, но другого столбца таблицы.
Функция ВПР() является одной из наиболее используемых в EXCEL, поэтому рассмотрим ее подробно.
В этой статье выбран нестандартный подход: акцент сделан не на саму функцию, а на те задачи, которые можно решить с ее помощью.
Параметр интервальный_просмотр может принимать 2 значения: ИСТИНА (ищется значение ближайшее к критерию или совпадающее с ним) и ЛОЖЬ (ищется значение в точности совпадающее с критерием). Значение ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по возрастанию. Это способ используется в функции по умолчанию, если не указан другой.
Задача1. Справочник товаров
Пусть дана исходная таблица (см. файл примера лист Справочник ).
Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или = ВПР($E9;$A$13:$C$19;2;ИСТИНА) или = ВПР($E9;$A$13:$C$19;2) (т.е. значение параметра Интервальный_просмотр можно задать ЛОЖЬ или ИСТИНА или вообще опустить). Значение параметра номер_столбца нужно задать =2, т.к. номер столбца Наименование равен 2 (Ключевой столбец всегда номер 1).
Для вывода Цены используйте аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра номер_столбца нужно задать =3).
Ключевой столбец в нашем случае содержит числа и должен гарантировано содержать искомое значение (условие задачи). Если первый столбец не содержит искомый артикул , то функция возвращает значение ошибки #Н/Д. Это может произойти, например, при опечатке при вводе артикула. Чтобы не ошибиться с вводом искомого артикула можно использовать Выпадающий список (см. ячейку Е9 ).
Понятно, что в нашей задаче ключевой столбец не должен содержать повторов (в этом смысл артикула, однозначно определяющего товар). В противном случае будет выведено самое верхнее значение.
При решении таких задач ключевой столбец лучше предварительно отсортировать (это также поможет сделать Выпадающий список нагляднее). Кроме того, в случае несортированного списка, ВПР() с параметром Интервальный_просмотр ИСТИНА (или опущен) работать не будет.
В файле примера лист Справочник показано, что формулы применимы и для ключевых столбцов содержащих текстовые значения, т.к. артикул часто бывает текстовым значением. Также задача решена для несортированного ключевого столбца.
Задача2. Поиск ближайшего числа
Предположим, что нужно найти товар, у которого цена равна или наиболее близка к искомой.
Чтобы использовать функцию ВПР() для решения этой задачи нужно выполнить несколько условий:
Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА)
Для вывода найденной цены (она не обязательно будет совпадать с заданной) используйте формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)
Как видно из картинки выше, ВПР() нашла наибольшую цену, которая меньше или равна заданной (см. файл примера лист «Поиск ближайшего числа» ). Это связано следует из того как функция производит поиск: если функция ВПР() находит значение, которое больше искомого, то она выводит значение, которое расположено на строку выше его. Как следствие, если искомое значение меньше минимального в ключевом столбце, то функцию вернет ошибку #Н/Д.
Найденное значение может быть далеко не самым ближайшим. Например, если попытаться найти ближайшую цену для 199, то функция вернет 150 (хотя ближайшее все же 200). Это опять следствие того, что функция находит наибольшее число, которое меньше или равно заданному.
Функция ВПР в Excel (на английском — VLOOKUP) по некоторому ключевому полю «подтягивает» данные из одного диапазона в другой. Ключевое поле должно присутствовать в обоих диапазонах данных (и там, куда «подтягиваем», и там, откуда берем данные).
Функция ВПР в Экселе: пошаговая инструкция
Представим, что перед нами стоит задача определить стоимость проданных товаров. Стоимость рассчитывается, как произведение количества и цены. Сделать это очень легко, если количество и цены находятся в соседних колонках. Однако данные могут быть представлены не в столь удобном виде. Исходная информация может находиться в совершенно разных таблицах и в другом порядке. В первой таблице указаны количества проданных товаров:
Если перечень товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.
Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.
Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.
Функция ВПР в Эксель легко справится с задачей.
Добавим вначале в первую таблицу новый столбец, куда будут подставляться цены из второй таблицы.
Для вызова функции с помощью Мастера нужно активировать ячейку, где будет прописана формула и нажать кнопку f(x) в самом начале строки формул. Появится диалоговое окно Мастера, где из списка всех функций нужно выбрать ВПР.
Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.
Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».
Следующее поле «Таблица». В нем нужно указать диапазон данных, где будет осуществляться поиск нужных значений. В нашем случае это вторая таблица с ценой. При этом крайний левый столбец выделяемого диапазона должен содержать те самые критерии, по которым осуществляется поиск (столбец с наименованиями товаров). Затем таблица выделяется вправо минимум до того столбца, где находятся искомые значения (цены). Можно и дальше вправо выделить, но это уже ни на что не влияет. Главное, чтобы выделенная таблица начиналась со столбца с критериями и захватывала нужный столбец с данными. Также следует обратить внимание на тип ссылок, они должны быть абсолютными, т.к. формула будет копироваться в другие ячейки.
Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов. Это не важно, Excel все сосчитает.
Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.
Нажимаем ОК. Если все правильно и значение критерия есть в обеих таблицах, то на месте только что введенной формулы появится некоторое значение. Остается только протянуть (или просто скопировать) формулу вниз до последней строки таблицы.
Теперь легко рассчитать стоимость простым умножением количества на цену.
Формулу ВПР можно прописать вручную, набирая аргументы по порядку, и разделяя точкой с запятой (см. видеоурок ниже).
Особенности использования формулы ВПР в Excel
Функция ВПР имеет свои особенности, о которых следует знать.
2. Номер столбца, указываемый в третьем поле «Номер_столбца» при использовании Мастера функций, должен отсчитываться, начиная с самого критерия.
3. Функция ВПР из диапазона с искомыми данными выдает первое сверху значение. Это значит, что, если во второй таблице, откуда мы пытаемся «подтянуть» некоторые данные, присутствуют несколько ячеек с одинаковым критерием, то в рамках выделенного диапазона ВПР захватит первое сверху значение. Об этом следует помнить. К примеру, если мы хотим к цене товара подтянуть количество из другой таблицы, а там этот товар встречается несколько раз (в нескольких строках), то к цене подтянется первое сверху количество.
4. Последний параметр формулы, который 0 (нуль), ставить нужно обязательно. Иначе формула может криво работать.
5. После использования ВПР саму формулу лучше сразу удалить, оставив только полученные значения. Делается это очень просто. Выделяем диапазон с полученными значениями, нажимаем «копировать» и на это же место с помощью специальной вставки вставляем значения. Если таблицы находятся в разных книгах Excel, то очень удобно разорвать внешние связи (оставив вместо них только значения) с помощью специальной команды, которая находится по пути Данные → Изменить связи.
После вызова функции разрывания внешних связей появится диалоговое окно, где нужно нажать кнопку «Разорвать связь» и затем «Закрыть».
Это позволит удалить сразу все внешние ссылки.
Примеры функции ВПР в Excel
Для следующих примеров использования функции ВПР возьмем немного другие данные.
Требуется цены из второй таблицы подтянуть в первую. В качестве критерия здесь используется код. Ниже показаны этапы вычисления ВПР.
Вторая таблица меньше первой, т.е. некоторые коды в ней отсутствуют. Для отсутствующих позиций ВПР выдает ошибку #Н/Д.
Появление таких ошибок, кстати, можно использовать для пользы дела, когда нужно найти отличия в таблицах. Но, скорее всего, ошибки помешают.
Конструкция с функцией ЕСЛИОШИБКА
Вместе с функцией ВПР часто используют функцию ЕСЛИОШИБКА, которая «заглушает» ошибки #Н/Д и вместо них возвращает некоторое значение. Обычно это 0 или пусто.
Как видно, ошибок больше нет, а вместо них пустые ячейки.
Разные форматы критерия в таблицах
Одна из распространенных причин появления ошибок заключается в несовпадении форматов критериев в двух таблицах. Текстовый и числовой форматы воспринимаются функцией ВПР как разные значения. Возможны два варианта.
Первый случай, когда критерии в первой таблице сохранены как числа, а критерии во второй таблице – как текст.
В ячейках с числами, сохраненными как текст, в левом верхнем углу появляется зелененький треугольник. Можно выделить все такие числа и в раскрывающемся списке выбрать Преобразовать в число.
Такое решение используется довольно часто. Но оно не всегда подходит. Например, когда данные из второй таблицы регулярно выгружаются из какой-нибудь базы данных типа 1С. В подобных файлах вообще все сохранено как текст. И если мы планируем постоянно использовать такие данные, вставляя их в заранее подготовленный диапазон, то лучше, чтобы формулы работали без дополнительного вмешательства.
Автоматически изменить формат критерия во второй таблице нельзя, т.к. ссылка ведет на целый диапазон. Придется вмешиваться в ссылку на критерий в первой таблице. Для этого потребуется дописать функцию ТЕКСТ, которая изменит числовой формат на текстовый. Синтаксис функции ТЕКСТ предполагает обязательное указание формата. Достаточно задать формат #. Ниже картинка с готовой формулой.
Две ошибки по-прежнему связаны с тем, что эти товары отсутствую во второй таблице. Чтобы их заглушить, можно вновь воспользоваться функцией ЕСЛИОШИБКА.
Вторая ситуация, заключается в том, что «текстом» являются критерий из первой таблицы. Форматы снова не совпадают.
Как и в прошлый раз, будем вносить коррективы в функцию ВПР. Преобразовать «текст» в «число» еще проще. Достаточно к ссылке на «текстовый» критерий добавить 0 или умножить на 1.
Бывает еще и третья, смешанная ситуация. Она встречается гораздо реже. Это когда в первой и второй таблице критерии сохранены и как число, и как текст, вперемешку. Здесь потребуется задействовать сразу все описанные выше функции: ЕСЛИОШИБКА, ТЕКСТ и +0. Вначале прописываем ЕСЛИОШИБКА и в качестве первого аргумента этой функции записываем ВПР с какой-либо конструкцией для изменения формата. Например, ВПР с формулой ТЕКСТ. В качестве второго аргумента (т.е. того, что должно быть в случае ошибки) записываем вторую конструкцию ВПР с +0. Таким образом, если ВПР с функцией ТЕКСТ не выдает ошибку, значит все ОК. Но если первая конструкция возвращает ошибку #Н/Д, то функция ЕСЛИОШИБКА подставляет вторую конструкцию – ВПР с +0. Другими словами, мы вначале принудительно делаем все критерии текстовыми, а затем, числовыми. Таким образом, ВПР проверяет оба формата. Один из них совпадет с форматом во второй таблице. Немного громоздко получается, но в целом все работает.
Отсутствующие критерии по-прежнему вызывают ошибку #Н/Д. В таком случае всю формулу можно еще раз «обернуть» в ЕСЛИОШИБКА.
Функция СЖПРОБЕЛЫ для чистки текстового критерия
В качестве критерия рекомендуется брать уникальный код, в котором опечатки, характерные для текста, маловероятны. Но иногда все-таки кода нет и критерием выступает текст (названия организаций, фамилии людей и т.д.). В этом случае возможны случайные ошибки в написании. Одна из распространенных ошибок – лишние пробелы. Проблема решается просто с помощью функции СЖПРОБЕЛЫ для всех критериев. Сделать это можно внутри формулы ВПР, а можно и предварительно пройтись по всем критериям в обеих таблицах. Кому как удобней.
Подсчет номера столбца в большой таблице
Если во второй таблице много столбцов, да еще часть из них скрыта или сгруппирована, то подсчитать напрямую количество столбцов между критерием и нужными данными, весьма непросто. Есть прием, который позволяет вообще не считать эти столбцы. Для этого во время выделения второй таблицы следует посмотреть в нижний правый угол выделяемого диапазона. Там появляется подсказка о количестве выделенных строк и столбцов. Запоминаем число столбцов и вносим в формулу ВПР.
Здорово экономит время.
Интервальный просмотр в функции ВПР
Наступило время обсудить последний аргумент функции ВПР. Как правило, указываю 0, чтобы функция искала точное совпадение критерия. Но есть вариант приблизительного поиска, это называется интервальный просмотр.
Рассмотрим алгоритм работы ВПР при выборе интервального просмотра. Прежде всего (это обязательно), столбец с критериями в таблице поиска должен быть отсортированы по возрастанию (если числа) или по алфавиту (если текст). ВПР просматривает список критериев сверху и ищет равный, а если его нет, то ближайший меньший к указанному критерию, т.е. на одну ячейку выше (поэтому и нужна предварительная сортировка. После нахождения подходящего критерия ВПР отсчитывает указанное количество столбцов вправо и забирает оттуда содержимое ячейки, что и является результатом работы формулы.
Проще понять на примере. По результатам выполнения плана продаж каждому торговому агенту нужно выдать заслуженную премию (в процентах от оклада). Если план выполнен менее, чем на 100%, премия не положена, если план выполнен от 100% до 110% (110% не входит) – премия 20%, от 110% до 120% (120% не входит) – 40%, 120% и более – премия 60%. Данные находятся в следующем виде.
Требуется подставить премию на основании выполнения планов продаж. Для решения задачи в первой ячейке пропишем следующую формулу:
На рисунке ниже изображена схема, как работает интервальный просмотр функции ВПР.
Джеки Чан выполнил план на 124%. Значит ВПР в качестве критерия ищет во второй таблице ближайшее меньшее значение. Это 120%. Затем отсчитывает 2 столбца и возвращает премию 60%. Брюс Ли план не выполнил, поэтому его ближайший меньший критерий – 0%.
Предлагаю посмотреть видеоурок о работе ВПР из курса «Основные функции Excel».
