Как сделать ВПР в Excel понятная инструкция
Чтобы понять, как пользоваться функцией ВПР в Excel, разберем ее синтаксис:
=ВПР(искомое значение; таблица; номер столбца; )
Искомое значение — значение или ссылка на ячейку, по которой будем искать значения в другой таблице. Это критерий поиска.
Таблица — ссылка на таблицу, в которой будет искать.
Номер столбца — порядковый номер столбца относительно самого левого столбца из выделенного диапазона поиска.Интервальный просмотр — указывается 0 (точный поиск) или 1 (приблизительный поиск)
Рассмотрим использование ВПР на примере:
Есть таблица с количеством гостей в отеле, необходимо понять, есть ли зависимость количества проживающих от того, насколько тепло в этом городе. Для этого нужно подтянуть значения температуры воздуха из таблицы справочника.
В качестве критерия будет использоваться название месяца.
Добавим в первую таблицу столбец Температура и напишем следующую формулу:
В примере формула ВПР ищет значение Январь из первой таблице в крайнем левом столбце второй таблицы. И подтягивает совпадающее значение из указанного столбца (в данном случае, из второго столбца).
Аргумент Интервальный просмотр задаем , чтобы функция искала точное совпадение.
Запомним, что в абсолютном большинстве случаем Интервальный просмотр ставим = 0.
Кстати, если не указать интервальный просмотр совсем, то формула не выдаст ошибку, т.к. этот аргумент необязательный. Но результат будет неожиданным, поскольку в этом случае ВПР считает не указанный Интервальный просмотр равным 1 (а это приблизительный поиск).
Не забываем добавить абсолютные ссылки (значки доллара $) к диапазону, иначе диапазон “съедет” при копировании формулы.
Итак, мы получили в нашу таблицу значение из таблицы-справочника — подтянули по критерию названия месяца. Осталось только протянуть или скопировать формулу во все ячейки этого столбца.
Функция ВПР в Экселе: пошаговая инструкция
Представим, что перед нами стоит задача определить стоимость проданных товаров. Стоимость рассчитывается, как произведение количества и цены. Сделать это очень легко, если количество и цены находятся в соседних колонках. Однако данные могут быть представлены не в столь удобном виде. Исходная информация может находиться в совершенно разных таблицах и в другом порядке. В первой таблице указаны количества проданных товаров:
Во второй – цены:
Если перечень товаров в обеих таблицах совпадает, то, зная магическое сочетание Ctrl+C и Ctrl+V, данные о ценах можно легко подставить к данным о количестве. Однако очередность позиций в обеих таблицах не совпадает. Тупо скопировать цены и подставить к количеству не получится.
Поэтому мы не можем прописать формулу умножения и «протянуть» вниз на все позиции.
Что делать? Надо как-то цены из второй таблицы подставить к соответствующему количеству в первой, т.е. цену товара А к количеству товара А, цену Б к количеству Б и т.д.
Вот так.
Функция ВПР в Эксель легко справится с задачей.
Добавим вначале в первую таблицу новый столбец, куда будут подставляться цены из второй таблицы.
Для вызова функции с помощью Мастера нужно активировать ячейку, где будет прописана формула и нажать кнопку f(x) в самом начале строки формул. Появится диалоговое окно Мастера, где из списка всех функций нужно выбрать ВПР.
Кликаем по надписи «ВПР». Открывается следующее диалоговое окно.
Теперь нужно заполнить предлагаемые поля. В первом окошке «Искомое_значение» нужно указать критерий для ячейки, в которую мы вписываем формулу. В нашем случае это ячейка с наименованием товара «А».
Следующее поле «Таблица». В нем нужно указать диапазон данных, где будет осуществляться поиск нужных значений. В нашем случае это вторая таблица с ценой. При этом крайний левый столбец выделяемого диапазона должен содержать те самые критерии, по которым осуществляется поиск (столбец с наименованиями товаров). Затем таблица выделяется вправо минимум до того столбца, где находятся искомые значения (цены). Можно и дальше вправо выделить, но это уже ни на что не влияет. Главное, чтобы выделенная таблица начиналась со столбца с критериями и захватывала нужный столбец с данными
Также следует обратить внимание на тип ссылок, они должны быть абсолютными, т.к. формула будет копироваться в другие ячейки
Следующее поле «Номер_столбца» — это число, на которое столбец с искомыми данными (ценами) отстоит от столбца с критерием (наименованием товара) включительно. То есть отсчет идет, начиная с самого столбца с критерием. Если у нас во второй таблице оба столбца находятся рядом, то нужно указать число 2 (первый – критерий, второй — цены). Часто бывает, что данные отстоят от критерия на 10 или 20 столбцов
Это не важно, Excel все сосчитает
Последнее поле «Интервальный_просмотр», где указывается тип поиска: точное (0) или приблизительное (1) совпадение критерия. Пока ставим 0 (или ЛОЖЬ). Второй вариант рассмотрен ниже.
Нажимаем ОК. Если все правильно и значение критерия есть в обеих таблицах, то на месте только что введенной формулы появится некоторое значение. Остается только протянуть (или просто скопировать) формулу вниз до последней строки таблицы.
Теперь легко рассчитать стоимость простым умножением количества на цену.
Формулу ВПР можно прописать вручную, набирая аргументы по порядку, и разделяя точкой с запятой (см. видеоурок ниже).
Зачем нужна функция ВПР и когда её используют
Представьте, что вы продаёте автомобили. У вас есть каталог с характеристиками авто и их стоимостью. Также у вас есть таблица с данными клиентов, которые забронировали эти автомобили.
Это каталог автомобилей с ценами
Это список клиентов: указаны забронированные авто, но нет цен
Вам нужно сообщить покупателям, сколько стоят их авто. Перед тем как обзванивать клиентов, нужно объединить данные: добавить во вторую таблицу колонку с ценами из первой.
Просто скопировать и вставить эту колонку не получится. Искать каждое авто вручную и переносить цены — долго.
ВПР автоматически сопоставит названия автомобилей в двух таблицах. Функция скопирует цены из каталога в список забронированных машин. Так напротив каждого клиента будет стоять не только марка автомобиля, но и цена.
Ниже пошагово и со скриншотами разберёмся, как сделать ВПР для этих двух таблиц с данными.
Функция ВПР в Экселе – сравнение двух таблиц
Итак, у вас есть «Заказ поставщику» (1) и ответ поставщика в виде «Счёта на оплату» (2).
Для удобства восприятия я разместил их на одном листе:
Ваша задача сверить количество позиций и их цены.
Для начала проверим все ли позиции и по правильной ли цене указал в счёте поставщик.
Для этого нужно из Счёта перетянуть данные в Заказ при помощи функции ВПР.
Перед «перетяжкой», в таблицу «Заказ поставщику» нужно добавить два «сравнительных» столбца:
«Кол/во в счёте» и «Цена в счёте»
После добавления столбцов, нужно перетянуть соответствующие данные при помощи ВПР:
Обратите внимание, я закрепил диапазоны ячеек. Теперь когда данные перенесены, нужно их сравнить, для это необходимо добавить еще два столбца (Разница 1 и Разница 2):
Теперь когда данные перенесены, нужно их сравнить, для это необходимо добавить еще два столбца (Разница 1 и Разница 2):
В столбце «Разница 1» нужно вычесть от исходного количества (D4) количество в счёте (E4).
В столбце «Разница 2» нужно вычесть от исходной цены (G4) цену в счёте (H4).
Таким образом мы сможем увидеть разницу и в количестве и в цене.
Если значение «0», то значит всё хорошо и данные одинаковые.
Если значение плюсовое (например «+3»), то это значит что в счёте не хватает 3 штук.
Если значение отрицательное, это значит, что нам пытаются «впихнуть» лишнее.
Если значение #Н/Д — это значит, что в счёте вообще нет такой позиции.
Тоже самое и с ценами, если значения в плюсе, значит нам сделали скидку, а если значения в минусе — значит нам пытаются продать по завышенной цене.
Таким образом нужно просканировать всю таблицу и выяснить о причинах расхождения у поставщика:
Друзья, вот так мы проверили насколько соответствует Заказ, полученному Счёту и казалось бы что это всё что необходимо для счастливой жизни. Однако это не совсем так.
Нужно еще проверить соответствие Счёта, отправленному заказу, на предмет лишних позиций.
Вдруг хитрый поставщик, среди сотни позиций решил нам скрытно что-то «допродать».
Для этого в «Счёт на оплату» нужно добавить столбец «Кол/во в заказе» и «отвепээрить» туда значения из столбца «Количество» Заказа поставщику.
И если в столбце «Количество в заказе» мы вдруг увидим значения #Н/Д это значит, что позиции с таким наименование не было в нашем заказе поставщику.
Это не всегда означает, что поставщик хочет нас «нагреть», чаще всего просто может быть ошибка в названии товара. Стоит например поставить лишний пробел, как Эксель не задумываясь выдаст #Н/Д. Для него это разные названия.
Теперь всё тоже самое продемонстрирую в небольшом видео.
ВПР имеет два режима поиска.
Четвертый параметр «интервальный просмотр» позволяет задать режим сравнения, в котором нужно работать – точный или приблизительный. Это логический параметр, то есть для него возможно 2 варианта:
- или ЛОЖЬ – интервальный просмотр выключен (ищем точное соответствие)
- 1 или ИСТИНА – интервальный просмотр включен (достаточно приблизительного)
Если этот параметр — ЛОЖЬ, то ищется точное равенство в первом столбце с аргументом «искомое». Если же такого нет, возвращается ошибка #Н/Д. Точный режим нужен, когда вы хотите найти информацию, основанную на каком-то уникальном ключе. Скажем, информацию о товаре — по коду товара, или данные фильма по его названию, человека — по фамилии.
Если он равен ИСТИНА или вовсе пропущен, то допускается неточное совпадение в данных отсортированной таблицы с аргументом «искомое». То есть нам нужно «лучшее соответствие», «как можно самое близкое». Возможно, вы просматриваете почтовые тарифы по весу посылки, ищете налоговую ставку на основе дохода, или определяете скидку на основе ежемесячного объема продаж. В этих случаях вы, скорее всего, не найдете именно ваш вес либо точную цифру вашего дохода. А если точного нет, то принимается ближайшее подходящее значение.
Обратите внимание, что при этом предполагается, что наш перечень отсортирован по столбцу поиска
Задача2. Поиск ближайшего числа
Предположим, что нужно найти товар, у которого цена равна или наиболее близка к искомой.
Чтобы использовать функцию ВПР() для решения этой задачи нужно выполнить несколько условий:
- Ключевой столбец, по которому должен производиться поиск, должен быть самым левым в таблице;
- Ключевой столбец должен быть обязательно отсортирован по возрастанию;
- Значение параметра Интервальный_просмотр нужно задать ИСТИНА или вообще опустить.
Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА)
Для вывода найденной цены (она не обязательно будет совпадать с заданной) используйте формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)
Как видно из картинки выше, ВПР() нашла наибольшую цену, которая меньше или равна заданной (см. файл примера лист «Поиск ближайшего числа» ). Это связано следует из того как функция производит поиск: если функция ВПР() находит значение, которое больше искомого, то она выводит значение, которое расположено на строку выше его. Как следствие, если искомое значение меньше минимального в ключевом столбце, то функцию вернет ошибку #Н/Д.
Найденное значение может быть далеко не самым ближайшим. Например, если попытаться найти ближайшую цену для 199, то функция вернет 150 (хотя ближайшее все же 200). Это опять следствие того, что функция находит наибольшее число, которое меньше или равно заданному.
Если нужно найти по настоящему ближайшее к искомому значению, то ВПР() тут не поможет. Такого рода задачи решены в разделе Ближайшее ЧИСЛО. Там же можно найти решение задачи о поиске ближайшего при несортированном ключевом столбце.
Примечание. Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием. Это можно сделать с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10) .
Примечание: Если в ключевом столбце имеется значение совпадающее с искомым, то функция с параметром Интервальный_просмотр =ЛОЖЬ вернет первое найденное значение, равное искомому, а с параметром =ИСТИНА — последнее (см. картинку ниже).
Если столбец, по которому производится поиск не самый левый, то ВПР() не поможет. В этом случае нужно использовать функции ПОИСКПОЗ() + ИНДЕКС() или ПРОСМОТР() .
Что такое ВПР в Excel?
ВПР расшифровывается как «вертикальный просмотр». В английском интерфейсе для её обозначения используется термин VLOOKUP, означающий то же самое, или VPR, являющийся калькой русской аббревиатуры.
Назначение функции ВПР — найти и вернуть в ячейку любое значение из исходной таблицы на основании указанных данных. Особенно полезна эта опция Excel, когда необходимо извлечь информацию из огромной сводной таблицы, сопоставить несколько источников данных или осуществить быстрый поиск по нескольким параметрам — примеры решения каждой из перечисленных задач будут приведены ниже.
Поиск в нескольких столбцах или базе Excel с INDIRECT (ДВССЫЛ)
В примере (снимок № 6) имеется 3 таблицы. Диапазоны данных в каждой из них имеют названия «Финансы», «Производство» и «Продажи».
В этом примере вам нужно найти сотрудника из отдела продаж с идентификатором 3. Название диапазона вводится в C19, идентификатор – в E19. Ячейка B22 связана с C19, и B22 (идентификатор) – это Lookup_value. Выражение с ДВССЫЛ находится в таблице – INDIRECT воспринимает содержимое C19 как имя диапазона.
Далее указываете номер колонки в матрице, откуда функция должна извлекать сведения и отображать их в ячейке при помощи VLOOKUP. В Range_lookup введите 0, потому что здесь требуется только точное пересечение.
ВПР – одно из самых полезных и важных средств поиска в Microsoft Excel. Он обычно используется для выполнения запросов к огромным листам с кучей информации, когда ручная работа может занять слишком много времени. Буква «V» в VLOOKUP означает «вертикальный», поэтому ее также иногда называют формулой вертикального отбора.
Особенности функции вертикального поиска в Excel
- Регистр в запросах не учитывается.
- В строке «Порядок сортировки» значение по умолчанию равно 1 (TRUE-ПРАВДА). Поэтому лучше не пропускать этот аргумент, если вам нужны только точные совпадения.
- В первом поле (там, где вы пишете запрос) допускается использование подстановочных знаков.
Выше были рассмотрены лишь некоторые способы применения с виду обыкновенной функции поиска VLOOKUP. Все эти примеры ВПР в Excel наглядно демонстрируют, насколько она полезна при работе с базами данных и упрощает анализ больших электронных таблиц.
Синтаксис функции ВПР
Функция имеет четыре аргумента. Рассмотрим их на примере простейшей телефонной книги.
Итак, у нас есть небольшая таблица в четыре столбца. В первом расположены порядковые номера записей, во втором перечислены имена друзей, в третьем адреса и в последнем телефоны.
В этом массиве данных нужно найти телефон Ивана.
Воспользуемся функцией ВПР.
В подсказке через точку с запятой указаны четыре аргумента.
Первый — искомое_значение. Это то, ЧТО мы должны искать в массиве данных, то есть в нашем случае имя «Иван
Второй аргумент — таблица, но фактически здесь мы должны указать диапазон значений в котором будет производиться поиск, а не таблицу целиком. То есть это то, ГДЕ будет производиться поиск.
Если, как в нашем случае, в первой строке размещены заголовки столбцов, то в них поиск осуществляться не должен, поэтому мы их выделять не будем. При этом функция ВПР производит поиск ТОЛЬКО в крайнем левом столбце выделенного диапазона, а значит первый столбец таблицы нас тоже не интересует. Указываем диапазон с именами, адресами и телефонами.
Но почему мы выбираем не только столбец с именами, в котором ищем нужный критерий?
А дело в том, что указанный нами критерий будет действительно искаться только в первом столбце выделенного диапазона, но в итоге функция должна вернуть не его, а значение, находящееся с ним в одной строке таблицы — в нашем примере телефон Ивана. Поэтому мы расширяем диапазон на все столбцы до нужного нам и в него попадает столбец с адресами.
Но как указать функции, какой именно столбец нас интересует? То есть, что нам нужен телефон, а не адрес?
В этом поможет третий аргумент функции — номер_столбца.
И тут важным является то, что имеется в виду номер столбца в выделенном диапазоне, а не номер столбца на листе Excel.
В рассматриваемом примере был выделен диапазон, состоящий из трех столбцов, поэтому здесь первым будет столбец с именами, второй с адресами, а третий с телефонами.
Еще раз подчеркну, что отсчет ведется от первого столбца выделенного диапазона, а не от столбца А листа Excel.
Так как нам нужно, чтобы функция ВПР вернула номер телефона, который находится в третьем столбце выделенного диапазона, то вводим 3.
И последний самый загадочный аргумент — интервальный_просмотр.
Этот аргумент логический и может принимать только два значения — ИСТИНА или ЛОЖЬ. Для простоты эти значения можно записывать цифрами, соответственно, 1 и 0.
Данный аргумент необязательный и если его не указать, то по умолчанию Excel будет его принимать за ИСТИНУ.
Из описания следует, что значение ИСТИНА соответствует приблизительному совпадению, а значение ЛОЖЬ — точному.
С точным совпадением вроде бы все интуитивно понятно — найденное значение должно в точности соответствовать искомому. Так как мы ищем имя и нас интересует именно точное совпадение, то сейчас используем его. О приблизительном совпадении поговорим чуть позже (в одной из следующих заметок) и разберем его на другом примере.
Нажимаем Enter и получаем результат.
Давайте проанализируем, как функция ВПР работает.
В качестве искомого значения мы задали имя Иван. Напомню, что функция ВПР всегда производит поиск искомого значения в крайнем левом столбце выделенного диапазона (B2:D7), поэтому перемещаясь сверху вниз по столбцу B она сравнивает заданное нами искомое значение со значениями, которые в нем находятся.
Как только она находит нужное значение, то поиск по вертикали заканчивается и функция смещает свое внимание на ячейку, которая задается третьим аргументом. Мы указали третий столбец и в итоге получили значение из его ячейки (D5)
Итак, с синтаксисом разобрались и разберем пример использования функции ВПР.
Аргументы функции
- lookup_value (искомое_значение) – это величина для поиска, из крайнего левого столбца таблицы. Это может быть значение, ссылка на ячейку или текстовая строка. В примере со студентами это их фамилии;
- table_array (таблица) – это диапазон данных, в котором будет осуществлен поиск. Это может быть ссылка на диапазон ячеек или именованный диапазон. В примере с таблицей со студентами, это будет вся таблица, которая содержит оценку и фамилии студентов;
- col_index (номер_столбца) – это порядковый номер столбца в диапазоне с данными, из которого будет получена искомая величина;
- () – этот аргумент указывает на точность совпадения данных при поиске. Укажите “0” – если точное, “1” – если приблизительное совпадение.
Функция ВПР в Excel для чайников
На примере ниже, в таблице справа, мы хотим подставить данные результатов экзамена по математике для студента Сидорова. Нам известны данные по всем студентам из таблицы слева. Используя формулу, мы можем подставить необходимые для нас данные.
С одним условием
Рассмотрим функцию на простом примере поиска сотрудника по присвоенному ему коду. Таблицу нужно отсортировать в порядке возрастания.
- Кликнуть по ячейке справа от таблицы или под ней и вписать искомый номер.
- В ячейке рядом ввести функцию ВПР через знак «=», либо же использовать опцию «Вставить функцию» в разделе «Формулы». Проще использовать именно опцию – там есть подсказки, какие именно данные нужны.
- Задать параметры поиска, заполнив соответствующие поля. Интервальный просмотр в данном случае пропустим.
- Нажать «Enter» – и появится результат поиска.Когда таблица не отсортирована и данные введены в хаотичном порядке, результат будет неправильный – программа найдет ближайшее соответствие («ИСТИНА»). Но можно не сортировать таблицу, а указать интервальный просмотр «ЛОЖЬ».Чтобы найти другой элемент, следует просто изменить первый аргумент, и результат автоматически преобразится.
С несколькими условиями
Часто на практике требуется сравнить данные нескольких диапазонов и выбрать значение с учетом 2-х и более критериев. Здесь задействована также функция ЕСЛИ, которая отвечает как раз за условия.
Из таблицы нужно найти показатель выручки по конкретному менеджеру в определенный день:
- В первую выделенную ячейку вписать дату, во вторую – фамилию менеджера. В третьей ячейке будет происходить поиск.
- Кликнуть по пустой ячейке и ввести формулу =ВПР(G1;ЕСЛИ(C2:C12=G2;A2:D12;»»);4;0).
- Для подтверждения действия зажать комбинацию клавиш Ctrl+Shift+Enter. Нажатие на «Enter» не сработает в этом случае, потому что формула должна быть выполнена в массиве (об этом свидетельствуют скобки «{}», в которые взята вся формула).
Как получить N-е слово из текста.
Этот пример демонстрирует оригинальное использование сложной формулы ПСТР в Excel, которое включает 5 различных составных частей:
- ДЛСТР — чтобы получить общую длину.
- ПОВТОР — повторение определенного знака заданное количество раз.
- ПОДСТАВИТЬ — заменить один символ другим.
- ПСТР — извлечь подстроку.
- СЖПРОБЕЛЫ — удалить лишние интервалы между словами.
Общая формула выглядит следующим образом:
Где:
- Строка — это исходный текст, из которого вы хотите извлечь желаемое слово.
- N – порядковый номер слова, которое нужно получить.
Например, чтобы вытащить второе слово из A2, используйте это выражение:
Или вы можете ввести порядковый номер слова, которое нужно извлечь (N) в какую-либо ячейку, и указать эту ячейку в формуле, как показано на скриншоте ниже:
Как работает эта формула?
По сути, Excel «оборачивает» каждое слово исходного текста множеством пробелов, находит нужный блок «пробелы-слово-пробелы», извлекает его, а затем удаляет лишние интервалы. Чтобы быть более конкретным, это работает по следующей логике:
ПОДСТАВИТЬ и ПОВТОР заменяют каждый пробел в тексте несколькими. Количество этих дополнительных вставок равно общей длине исходной строки: ПОДСТАВИТЬ($A$2;» «;ПОВТОР(» «;ДЛСТР($A$2)))
Вы можете представить себе промежуточный результат как «астероиды» слов, дрейфующих в пространстве, например: слово1-пробелы-слово2-пробелы-слово3-… Эта длинная строка передается в текстовый аргумент ПСТР.
- Затем вы определяете начальную позицию для извлечения (первый аргумент), используя следующее уравнение: (N-1) * ДЛСТР(A1) +1. Это вычисление возвращает либо позицию первого знака первого слова, либо, чаще, позицию в N-й группе пробелов.
- Количество букв и цифр для извлечения (второй аргумент) — самая простая часть — вы просто берете общую первоначальную длину: ДЛСТР(A2).
- Наконец, СЖПРОБЕЛЫ избавляется от начальных и конечных интервалов в извлечённом тексте.
Приведенная выше формула отлично работает в большинстве ситуаций. Однако, если между словами окажется 2 или более пробелов подряд, это даст неверные результаты (1). Чтобы исправить это, вложите еще одну функцию СЖПРОБЕЛЫ в ПОДСТАВИТЬ, чтобы удалить лишние пропуски между словами, оставив только один, например:
Следующий рисунок демонстрирует улучшенный вариант (2) в действии:
Если ваш исходный текст содержит несколько пробелов между словами, а также очень большие или очень короткие слова, дополнительно вставьте СЖПРОБЕЛЫ в каждое ДЛСТР, чтобы вы были застрахованы от ошибки:
Я согласен с тем, что это выглядит немного громоздко, но зато безупречно обрабатывает все возможные варианты.
Два ВПР быстрее, чем один.
Это может показаться совершенно невероятным, но когда у вас большой набор данных и вам необходимо найти точное совпадение, вы можете значительно ускорить вычисления, добавив еще один ВПР в формулу!
Предыстория: представьте, что у вас много данных о заказах, скажем, более 10000 записей, и ВПР нужна для поиска общей суммы заказа на основе идентификатора заказа. Итак, вы используете что-то вроде этого:
То есть, мы ищем точное совпадение. Если номер заказа не будет найден, то получим ошибку #Н/Д.
Проблема в том, что точные совпадения действительно ищутся медленно, потому что Excel должен перебирать последовательно через все значения, пока не найдет искомое (или нет).
И наоборот, приблизительные совпадения молниеносны, потому что Excel может выполнять то, что называется бинарным поиском.
Однако проблема с этим двоичным поиском состоит в том, что Excel может возвращать неправильный результат, когда номер заказа не найден. Он просто возьмет наиболее похожий и близкий, что вряд ли вас устроит. Хуже того, ответ может выглядеть совершенно нормально, поэтому ошибку очень сложно обнаружить.
Решение состоит в том, чтобы использовать ВПР дважды, оба раза в режиме приблизительного соответствия. Первый раз просто проверяем, что значение действительно существует. Если это так, второй ВПР запускается (опять же, в режиме приблизительного соответствия), чтобы получить нужные данные. Если нет, вы можете вернуть любую фразу, которой вы хотите указать, что результат не был найден.
Окончательное выражение выглядит так:
То есть, мы сначала ищем и извлекаем номер заказа. И если результат этого поиска точно совпадает с критерием, повторяем еще раз, но теперь уже извлекаем именно сумму заказа.
Примечание: ваши данные должны быть отсортированы, чтобы использовать этот способ.
Функция ВПР с несколькими условиями
Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:
Функция ВПР в Excel – Таблица исходных данных
Пусть нам необходимо использовать функцию ВПР с несколькими условиями. Например, для поиска цены товара по двумя критериями: названию продукта и его типу.
- Для того чтобы использовать функцию ВПР с несколькими условиями необходимо вставить в начало дополнительный столбец, который будет хранить информацию с названием и типом товара.
Итак на листе « Цены » вставляем столбец и в ячейке А2 вводим следующую формулу:
При помощи этой формулы мы сцепляем значение столбца « Продукт » и « Тип ». Заполняем все ячейки.
Теперь таблица для поиска выглядит следующим образом:
Функция ВПР в Excel – Добавление вспомогательного столбца
- Теперь в ячейке С2 на листе « Продажи » напишем следующую формулу ВПР:
Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:
Функция ВПР в Excel – Пример ВПР с несколькими условиями
Теперь разберем ошибки функции ВПР.