Как делать впр в excel

Как работать с функцией ВПР в Эксель: инструкция для новичков

С помощью функции ВПР (в переводе на английский VLOOKUP) пользователи программы Exсel имеют возможность переставлять данные из одной таблицы в другую со схожими параметрами. Эта услуга подойдёт для тех, кому приходится работать с большими списками. Ведь вписывать каждое значение отдельно может занять очень большое количество времени.

Использование ВПР в программе Excel

Для того, чтобы наглядно разобраться как работает функция ВПР в Excel: поможет пошаговая инструкция на конкретном примере.

Допустим, в магазин канцелярских товаров поступил новый привоз, к которому прилагается соответствующая документация. Администратору торгового зала необходимо рассчитать полную стоимость продукции, имея на руках файл Excel, который содержит две таблицы.

Первая – это список предметов, единицы их измерения и количество.

Вторая – содержит тот же список, но в ней ещё есть цена за 1 штуку.

Чтобы подсчитать сколько стоит продукция, следует информацию из второй вставить в первую, и с помощью простого умножения произвести расчёт.

Этапы работы (инструкция):

1. Для начала в первую Excel таблицу добавляются два столбца: «Цена за 1 шт.» и «Общая сумма».
   
2. Отметить верхнее поле в новом.
   
3. Выбрать раздел формулы, и нажать «Вставить функцию».
   
4. Из предложенных категорий Excel отметить «Ссылки и массивы».
   
5. Найти ВПР, и нажать «ОК».
   
6. Заполнить открывшееся окно «Аргументы».
   

– это товары из первой таблицы, которые необходимо будет определить во второй. Их значение выставляется таким образом: X: Y, где Х – это адрес первой ячейки столбика с товарами, а Y – последней. В рассматриваемой это А2 и А5.

– в этом поле будет стоимость из второго листа с данными. Чтобы её проставить следует кликнуть по строке, затем перейти на страницу с суммой, и выделить нужное (А2 – В5).

Важно! Эти показатели фиксируются, чтобы именно по ним производились расчёты программой Эксель.

Фиксирование информации производится путём нажатия горячей клавиши F4, на выделенной строке. Если всё сделано правильно там же появится значок $.

Номер — это строка в которой должна быть информация о том, что будет переноситься из другой таблицы. В рассматриваемом случае – это второй столбец (2).

Интервальный просмотр – логическое значение Excel, где точно это ЛОЖЬ, а приближённо – ИСТИНА. Если пользователю нужны точные, он должен написать «ЛОЖЬ».

В конечном итоге, окно «Аргументы» выглядит так:

Нужное значение появится в ячейке. Чтобы опция сработала на все товары, достаточно растянуть её.

Теперь, чтобы сосчитать общую стоимость предмета, достаточно вставить соответствующую формулу в ячейку Е2, и также растянуть её на все продукты. Конец инструкции.

Как сравнить две таблицы: пошаговая инструкция для «чайников»

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

Допустим, что к тому же администратору торгового центра снова привезли товар, но предупредили, что стоимость у некоторых предметов изменились. Как сравнить две таблицы функцией ВПР в Эксель?

Делается это в несколько шагов:

1. Открыть первую со старой информацией.
   
2. Добавить дополнительный столбик для новых данных «Новая стоимость».
   
3. Выделить первое пустое поле в созданном столбце (С2).
   
4. Выбрать раздел «ВПР Формулы» и «Вставить функцию».
   
5. Найти категорию Excel «Ссылки и массивы».
   
6. Выбрать ВПР.
7. Задать «Аргументы».

– то, что важно будет найти во второй таблице. Чтобы значение появилось в строке, нужно выделить первый столбик с наименованиями товаров (А2 – А5).

– с чем программа будет сравнивать. Для заполнения нужно перейти на вторую страницу и отметить два наименования – предметы и цена (А2 – В5). И зафиксировать результат кнопкой F4.

Номер столбца – второй, так как именно стоимость переносится в новую.

Интервальный просмотр – ЛОЖЬ.

Заполненное окно выглядит так:

После нажатия кнопки «ОК» новые значения появятся в таблице. Чтобы ценовая информация появилась у всех предметов нужно растянуть ячейку.

Теперь администратор может работать с данными стандартными функциями Excel, благодаря инструкции.

Поиск с помощью ВПР по нескольким условиям

Если пользователю программы Excel необходимо из большого каталога найти необходимые данные, он может воспользоваться данным способом для чайников (инструкция).

Итак, имеется документ, в котором обозначены: компании, товары и цены.

Нужно найти цену на конкретный товар – гелевая ручка. Но так как каталог может быть огромным, а гелевые ручки быть не у одной компании, поиск стоимости в Эксель лучше проводить через ВПР с несколькими условиями: название компании и предмета.

Чтобы осуществить поиск следует:

1. Создать слева новый столбец с объединёнными данными (название компании и товара).

Делается это просто:

• выделить крайнюю левую ячейку (А1);
• щёлкнуть ПКМ и выбрать «Вставить»;
• отметить добавление столбца и нажать «ОК».

1. Внести данные в новый столбец. Для этого нужно нажать на пустое поле А2, ввести формулу объединения (=B2&C2) и нажать кнопку Enter. Чтобы продлить список достаточно растянуть ячейку.
   
   
2. Нажать на любое свободное место и самостоятельно ввести, что нужно найти (ЛасточкаГелевая ручка).
   
3. Выбрать ячейку где будет отображен результат и заполнить Аргументы функции.

– что нужно найти (щёлкнуть по введенной — ЛасточкаГелевая ручка – А8).

– где искать нужное значение (выделить ячейки от первой до последней — А2 – D5).

Номер столбца – из какого столбца вывести результат (4).

Интервальный просмотр – ЛОЖЬ.

После нажатия команды «ОК», программа отобразит результат.

Как сделать выпадающий список через функцию ВПР

Чтобы сделать выпадающий список из существующего нужно следовать инструкции:

1. Выбрать поле, в котором будет сформированы показатели. Например, Е2.
   
2. Зайти в раздел «Данные», и выбрать «Проверка данных».
   
3. Установить тип данных, как список.

1. В появившуюся строку «Источник» ввести информацию (выделить с первой до последней ячейки – А2:А5).
   

Выпадающий список готов.

Теперь с помощью функции ВПР нужно добавить возможность просмотра цены, при выборе товара. Как это работает в Эксель? (Инструкция).

1. Создать новое поле с названием «Цена».
   
2. Вставить аргументы.

– ячейка Excel, в которой находится выпадающий список (Е2).

– выделенный фрагмент с предметами и ценами (А2-В5).

Номер столбца – 2 (в нём находятся цены).

Интервальный просмотр – ЛОЖЬ.

После подтверждения команды можно пользоваться списком и просмотром цены.

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

Функция ВПР в Excel: пошаговая инструкция с 5 примерами

ВПР — это функция Excel для поиска и извлечения данных из определенного столбца в таблице. Она поддерживает приблизительное и точное сопоставление, а также подстановочные знаки (* и ?). Значения поиска должны отображаться в первом столбце таблицы, а столбцы поиска находятся правее.

Давайте теперь рассмотрим, как сделать поиск с ВПР и как она работает. Рассмотрим приемы ее применения в формулах Excel.

Как сделать ВПР в Excel: понятная пошаговая инструкция.

Для начала на простом примере разберем, как работает функция ВПР в Excel. Предположим, у нас есть две таблицы. Первая – это прайс-лист с наименованиями и ценами. Вторая – это заказ на покупку некоторых из этих товаров. Искать в прайс листе нужный товар и руками вписывать в заказ его цену – занятие очень утомительное. Ведь прайс с ценами может насчитывать сотни строк. Нам необходимо сделать всё автоматически.

Нам необходимо обнаружить интересующее нас наименование в первом столбце и возвратить (то есть показать в ответ на наш запрос) содержимое из желаемого столбца той же строки, где находится наименование.

Наш прайс-лист расположен в столбцах А и В. Список покупок – в E-H. Допустим, первая позиция в списке покупок – бананы. Нам нужно в столбце A, где указаны все наименования, найти этот товар, затем его цену поместить в ячейку G2.

Для этого в G2 запишем следующую формулу:

А теперь разберем подробно, как сделать ВПР.

1. Мы берем значение из E2.
2. Ищем точное совпадение (поскольку четвертым параметром указан 0) в диапазоне $A$2:$B$7 в первой его колонке (крайней левой). Обратите внимание, что лучше сразу же использовать абсолютные ссылки на прайс-лист, чтобы при копировании этой формулы ссылка не «соскользнула».
3. Если товар будет найден, то нужно перейти во второй столбец диапазона (на это указывает третий параметр = 2).
4. Взять из него цену и вставить ее в нашу ячейку G2.

Получилось? Теперь просто скопируйте формулу из G2 в G3:G8.

Отчет о продажах готов.

Также чтобы понять, что такое точное совпадение, попробуйте в A5 или в E2 изменить наименование товара. К примеру, добавьте пробел в конце. Внешне ничего не изменилось, но вы сразу же получите ошибку #Н/Д. То есть, товар не был обнаружен. В то же время, таких случайных ошибок можно легко избежать, о чем мы поговорим отдельно.

Особо остановимся на четвертом параметре. Мы указали ноль (можно было написать ЛОЖЬ), что означает «точный поиск». А что, если забыть его указать и закончить номером столбца, из которого извлекаются нужные данные?

Давайте еще раз шаг за шагом разберем, что в этом случае будет происходить.

1. Берем значение из F2.
2. Начинаем его искать в крайней левой колонке диапазона $A$2:$B$7, то есть в столбце A. Поскольку в A2 совпадение не найдено, смотрим дальше: что находится ниже.
3. Там обнаруживаем товар «Сливы». При этом предполагается, что наш список отсортирован по алфавиту. Ведь именно это – главное условие поиска приблизительного совпадения.
4. Поскольку в сортированном списке «сливы» находятся ниже, чем «бананы», то функция принимает решение, что дальше искать слово, начинающееся на «Б» нет смысла. Процесс можно остановить. И остаться на букве «А». То есть, там и находится наиболее близкое значение.
5. Поскольку поиск завершен, переходим из A2 во второй столбец, то есть в B. Вставляем данные из B2 в G2 как результат вычислений.

К сожалению, «бананы» были в нашем прайс-листе ниже, но до них просто «не дошел ход». И в список покупок теперь записана неправильная цена.

При помощи этой инструкции мы рассмотрели только основы. А как реально этим можно пользоваться?

Как работает функция ВПР в Excel: несколько примеров для «чайников».

Предположим, нам необходимо из списка сотрудников выбрать данные определенного человека. Посмотрим, какие здесь есть тонкости.

Во-первых, нужно сразу определиться: точный либо приблизительный поиск нам нужен. Ведь они предъявляют разные требования к подготовке исходных данных.

Использование точного и приблизительного поиска.

Посмотрите, какие результаты выборки цен мы получаем с использованием приблизительного поиска на неупорядоченном массиве данных.

Обратите внимание, что четвертый параметр равен 1.

Кое-что из результатов определено верно, но в большинстве случаев – ошибки. Функция продолжает просматривать данные столбца D с наименованиями товаров до тех пор, пока не встретит значение больше, чем заданное ей в качестве критерия поиска. Тогда она останавливается и возвращает цену.

Поиск цены на египетские бананы закончился на первой же позиции, так как во второй записаны сливы. А это слово по правилам алфавита стоит ниже, чем «Бананы Египет». Значит, дальше искать не нужно. Получили 145. И не важно, что это цена абрикосов. Поиск цены на сливы происходил до тех пор, пока в D15 не встретилось слово, которое по алфавиту стоит ниже: яблоки. Остановились и взяли цену из предыдущей строки.

А теперь взгляните, как должно было все происходить, если все делать верно. Всего лишь делаем сортировку, как указано стрелкой.

Вы спросите: «А зачем тогда этот неточный просмотр, если с ним столько проблем?»

Он отлично подходит для выбора значений из определенных интервалов.

Допустим, у нас установлена скидка для покупателей в зависимости от количества приобретенного товара. Нужно быстро рассчитать, сколько процентов положено на совершенную покупку.

Если у нас количество товара 11 единиц, то мы просматриваем столбец D до тех пор, пока не встретим число, большее 11. Это 20 и находится оно в 4-й строке. Останавливаемся здесь. Значит, наша скидка расположена в 3-й строке и равна 3%.

При работе с интервалами вида «от – до» такая методика вполне пригодна.

И еще один небольшой совет.

Применяйте именованный диапазон.

Для упрощения работы с формулами можно создать именованный диапазон и в дальнейшем ссылаться на него. В нашем случае назовем его «ДанныеСотрудников» (помните, что пробелы здесь недопустимы).

В ячейке B2 мы будем вводить нужную фамилию, а в ячейках С2:F2 запишем формулы:

Как видите, отличаются они только номером столбца, из которого будет извлечена нужная информация. Вместо ЛОЖЬ можно использовать 0.

Какие здесь преимущества?

1. У вас не рябит в глазах от букв, цифр и знаков доллара в обычных адресах диапазонов?

Формула с именованным диапазоном выглядит намного более дружественно, наглядно и понятно. Вместо скучных и безликих координат вы видите идентификаторы, которые рождают у вас некоторые ассоциации. Согласитесь, “price” или «цена» – это наверняка информация о ценах.

1. Если по каким-то причинам вам необходимо будет изменить координаты диапазона поиска, который вы использовали в большом количестве формул – вам нужно корректировать каждую формулу или пользоваться функцией “Найти и заменить”? Согласитесь, это очень долго, трудоемко, возможны ошибки.

Используя именованный диапазон, просто нажмите

Меню – Формула – Диспетчер имён.

Затем в списке диапазонов найдите нужный вам диапазон и откорректируйте его. Изменения автоматически будут применены во всех формулах.

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

Использование символов подстановки и другие тонкости критерия поиска.

Как и в предыдущих примерах, при вводе фамилии происходит точный поиск. Но есть несколько моментов, о которых мы ранее не упоминали.

1. Регистр символов не влияет на результат. Можно все вводить прописными буквами – ничего не изменится. Пример вы видите чуть ниже.

1. Если в списке есть люди с одинаковыми фамилиями, то найден будет только первый из них. Как и мы говорили ранее, как только найдено что-то подходящее, процесс останавливается.
2. Можно использовать символы подстановки * и ?. Напомню, что вопросительный знак заменяет собой любой символ, а звездочка – любое количество символов (в том числе и ноль). О них мы упоминали в начале.

Это целесообразно делать, если мы знаем только часть значения аргумента.

Но при этом будьте внимательны – снова будет найдено только первое подходящее совпадение, как это показано на скриншоте. Это очень важное ограничение, которое нужно обязательно учитывать.

Теперь давайте посмотрим, как можно работать с символами подстановки, если условия отбора не вводятся вручную, а берутся из таблицы Excel.

Формула в ячейке F2 выглядит следующим образом:

Здесь мы используем оператор «склеивания» строк &.

Конструкция «*»&D2&»*» означает, что к содержимому ячейки D2 добавляются с обоих сторон звездочки *. То есть, мы ищем любое вхождение этого слова – перед ним и после него могут быть любые другие слова и символы. Как, например, произошло с товаром «персики». Первый параметр будет в нашем случае выглядеть как «*персики*». При поиске такой конструкции приемлемым вариантом будут определены «Консервированные персики (Турция)».

Использование нескольких условий.

Еще один простой пример для «чайников» — как использовать при выборе нужного значения несколько условий?

Предположим, у нас есть список имен и фамилий. Нам нужно найти нужного человека и вывести сумму его дохода.

В F2 используем следующую формулу:

Разберем пошагово, как в этом случае работает ВПР.

В начале мы формируем условие. Для этого при помощи оператора & «склеиваем» вместе имя и фамилию, а между ними вставляем пробел.

Не забываем при этом пробел заключить в кавычки, иначе Excel не воспримет его как текст.

Затем в таблице с доходами ищем ячейку с именем и фамилией, разделенными пробелом.

Дальше все происходит по уже отработанной схеме.

Можно попробовать подстраховаться на тот случай, если между именем и фамилией введено несколько пробелов. Знак пробела в формуле заменяем на знак подстановки «*».

Приметно так — D2&»*»&E2

Но при этом имейте в виду, что совпадение имени и фамилии уже будет не совсем точным. Подобный пример мы рассматривали чуть выше.

Более сложные и точные способы работы с несколькими условиями мы рассмотрим отдельно. Смотрите ссылки в конце.

«Умная» таблица.

И еще одна рекомендация: используйте «умную» таблицу.

Бывает очень удобно сначала преобразовать поисковую таблицу (прайс-лист) в «умную» с помощью команды Главная – Форматировать как таблицу (Home – Format as Table в английской версии Excel), и затем указать во втором аргументе использовать имя созданной таблицы. Кстати, оно ей будет присвоено автоматически.

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

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

Функция ВПР в Excel

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

Синтаксис и описание функции ВПР в Excel

Итак, так как второе название этой статьи «Функция ВПР в Excel для чайников», начнем с того что узнаем, что же такое функция ВПР и что она делает? Функция ВПР на английском VLOOKUP, ищет указанное значение и возвращает соответствующее значение из другого столбца.

Как работает функция ВПР? Функция ВПР в Excel выполняет поиск по вашим спискам данных на основе уникального идентификатора и предоставляет вам часть информации, связанную с этим уникальным идентификатором.

Буква «В» в ВПР означает «вертикальный». Она используется для дифференциации функции ВПР и ГПР, которая ищет значение в верхней строке массива («Г» обозначает «горизонтальный»).

Функция ВПР доступна во всех версиях Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003.

Синтаксис функции ВПР выглядит следующим образом:

Как видите, функция ВПР имеет 4 параметра или аргумента. Первые три параметра обязательные, последний — необязательный.

1. искомое_значение – это значение для поиска.

Это может быть либо значение (число, дата или текст), либо ссылка на ячейку (ссылка на ячейку, содержащую значение поиска), или значение, возвращаемое некоторой другой функцией Excel. Например:

• Поиск числа : =ВПР(40; A2:B15; 2) — формула будет искать число 40.
• Поиск текста : =ВПР(«яблоки»; A2:B15; 2) — формула будет искать текст «яблоки». Обратите внимание, что вы всегда включаете текстовые значения в «двойные кавычки».
• Поиск значения из другой ячейки : =ВПР(C2; A2:B15; 2) — формула будет искать значение в ячейке C2.

1. таблица — это два или более столбца данных.

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

Итак, наша формула =ВПР(40; A2:B15; 2) будет искать «40» в ячейках от A2 до A15, потому что A — это первый столбец таблицы A2: B15.

1. номер_столбца — номер столбца в таблице, из которой должно быть возвращено значение в соответствующей строке.

Самый левый столбец в указанной таблице равен 1, второй столбец — 2, третий — 3 и т. д.

Итак, теперь вы можете прочитать всю формулу =ВПР(40; A2:B15; 2). Формула ищет «40» в ячейках от A2 до A15 и возвращает соответствующее значение из столбца B (потому что B является вторым столбцом в указанной таблице A2:B15).

4. интервальный_просмотр определяет, ищете ли вы точное соответствие (ЛОЖЬ) или приблизительное соответствие (ИСТИНА или опущено). Этот последний параметр является необязательным, но очень важным.

Функция ВПР в Excel примеры

Теперь давайте рассмотрим несколько примеров использования функции ВПР для реальных данных.

Функция ВПР на разных листах

На практике формулы ВПР редко используются для поиска данных на одном листе. Чаще всего вам придется искать и вытаскивать соответствующие данные с другого листа.

Чтобы использовать функцию ВПР с другого листа Excel, вы должны ввести имя рабочего листа и восклицательный знак в аргументе таблица перед диапазоном ячеек, например, =ВПР(40;Лист2!A2:B15;2). Формула указывает, что диапазон поиска A2:B15 находится в Лист2.

Конечно, вам не нужно вводить имя листа вручную. Просто начните вводить формулу, и когда дело дойдет до аргумента таблица, переключитесь на рабочий лист поиска и выберите диапазон с помощью мыши.

Формула, которую вы видите на изображении ниже, ищет текст в ячейке А2 (« Продукт 3 ») в столбце A (1-й столбец диапазона поиска A2:B9) на листе « Цены »:

Функция ВПР в Excel – Функция ВПР на разных листах

Обратите внимание, что рекомендуется использовать абсолютные ссылки на ячейки (со знаком $) в аргументе таблица. Это предотвратит изменение диапазона поиска при копировании формулы ВПР в другие ячейки.

Как использовать именованный диапазон или таблицу в формулах ВПР

Если вы собираетесь использовать один и тот же диапазон поиска в нескольких формулах ВПР, вы можете создать именованный диапазон для него и ввести имя непосредственно в аргументе таблица вашей формулы ВПР.

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

Функция ВПР в Excel – Присвоение имени диапазону

Теперь вы можете написать следующую формулу ВПР, чтобы получить цену Продукта 1:

Функция ВПР в Excel – Пример функции ВПР с именем диапазона

Большинство имен диапазонов в Excel применяются ко всей книге, поэтому вам не нужно указывать имя рабочего листа, даже если ваш диапазон поиска находится на другом листе. Такие формулы гораздо более понятны. Кроме того, использование именованных диапазонов может быть хорошей альтернативой абсолютным ссылкам на ячейки. Поскольку именованный диапазон не изменяется, когда формула копируется в другие ячейки, и вы можете быть уверены, что ваш диапазон поиска всегда останется верным.

Если вы преобразовали диапазон ячеек в полнофункциональную таблицу Excel (вкладка «Вставка» —> «Таблица»), вы можете выбрать диапазон поиска с помощью мыши, а Microsoft Excel автоматически добавит имена колонок или имя таблицы в формулу:

Функция ВПР в Excel – Пример функции ВПР с именем таблицы

Полная формула может выглядеть примерно так:

или даже =ВПР(«Продукт 1»;Таблица6;2).

Как и именованные диапазоны, имена столбцов являются постоянными, а ссылки на ячейки не изменятся независимо от того, где копируется формула ВПР.

Функция ВПР с несколькими условиями

Рассмотрим пример функции ВПР с несколькими условиями. У нас есть следующие исходные данные:

Функция ВПР в Excel – Таблица исходных данных

Пусть нам необходимо использовать функцию ВПР с несколькими условиями. Например, для поиска цены товара по двумя критериями: названию продукта и его типу.

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

Итак на листе « Цены » вставляем столбец и в ячейке А2 вводим следующую формулу:

При помощи этой формулы мы сцепляем значение столбца « Продукт » и « Тип ». Заполняем все ячейки.

Теперь таблица для поиска выглядит следующим образом:

Функция ВПР в Excel – Добавление вспомогательного столбца

1. Теперь в ячейке С2 на листе « Продажи » напишем следующую формулу ВПР:

Заполняем для остальных ячеек и в результате получаем цены для каждого продукта в соответствии с типом:

Функция ВПР в Excel – Пример ВПР с несколькими условиями

Теперь разберем ошибки функции ВПР.

Почему не работает функция ВПР

В этой части статьи мы рассмотрим почему не работает функция ВПР и возможные ошибки функции ВПР.

Использование функции ВПР (VLOOKUP) для подстановки значений

Кому лень или нет времени читать — смотрим видео. Подробности и нюансы — в тексте ниже.

Постановка задачи

Итак, имеем две таблицы — таблицу заказов и прайс-лист:

Задача — подставить цены из прайс-листа в таблицу заказов автоматически, ориентируясь на название товара с тем, чтобы потом можно было посчитать стоимость.

Решение

В наборе функций Excel, в категории Ссылки и массивы (Lookup and reference) имеется функция ВПР (VLOOKUP) . Эта функция ищет заданное значение (в нашем примере это слово «Яблоки») в крайнем левом столбце указанной таблицы (прайс-листа) двигаясь сверху-вниз и, найдя его, выдает содержимое соседней ячейки (23 руб.) Схематически работу этой функции можно представить так:

Для простоты дальнейшего использования функции сразу сделайте одну вещь — дайте диапазону ячеек прайс-листа собственное имя. Для этого выделите все ячейки прайс-листа кроме «шапки» (G3:H19), выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define) или нажмите CTRL+F3 и введите любое имя (без пробелов), например Прайс. Теперь в дальнейшем можно будет использовать это имя для ссылки на прайс-лист.

Теперь используем функцию ВПР. Выделите ячейку, куда она будет введена (D3) и откройте вкладку Формулы — Вставка функции (Formulas — Insert Function) . В категории Ссылки и массивы (Lookup and Reference) найдите функцию ВПР (VLOOKUP) и нажмите ОК. Появится окно ввода аргументов для функции:

Заполняем их по очереди:

• Искомое значение (Lookup Value) — то наименование товара, которое функция должна найти в крайнем левом столбце прайс-листа. В нашем случае — слово «Яблоки» из ячейки B3.
• Таблица (Table Array) — таблица из которой берутся искомые значения, то есть наш прайс-лист. Для ссылки используем собственное имя «Прайс» данное ранее. Если вы не давали имя, то можно просто выделить таблицу, но не забудьте нажать потом клавишу F4 , чтобы закрепить ссылку знаками доллара , т.к. в противном случае она будет соскальзывать при копировании нашей формулы вниз, на остальные ячейки столбца D3:D30.
• Номер_столбца (Column index number) — порядковый номер (не буква!) столбца в прайс-листе из которого будем брать значения цены. Первый столбец прайс-листа с названиями имеет номер 1, следовательно нам нужна цена из столбца с номером 2.
• Интервальный_просмотр (Range Lookup) — в это поле можно вводить только два значения: ЛОЖЬ или ИСТИНА:
  • Если введено значение или ЛОЖЬ (FALSE) , то фактически это означает, что разрешен поиск только точного соответствия, т.е. если функция не найдет в прайс-листе укзанного в таблице заказов нестандартного товара (если будет введено, например, «Кокос»), то она выдаст ошибку #Н/Д (нет данных).
  • Если введено значение 1 или ИСТИНА (TRUE) , то это значит, что Вы разрешаете поиск не точного, а приблизительного соответствия, т.е. в случае с «кокосом» функция попытается найти товар с наименованием, которое максимально похоже на «кокос» и выдаст цену для этого наименования. В большинстве случаев такая приблизительная подстановка может сыграть с пользователем злую шутку, подставив значение не того товара, который был на самом деле! Так что для большинства реальных бизнес-задач приблизительный поиск лучше не разрешать. Исключением является случай, когда мы ищем числа, а не текст — например, при расчете Ступенчатых скидок.

Все! Осталось нажать ОК и скопировать введенную функцию на весь столбец.

Ошибки #Н/Д и их подавление

Функция ВПР (VLOOKUP) возвращает ошибку #Н/Д (#N/A) если:

• Включен точный поиск (аргумент Интервальный просмотр=0) и искомого наименования нет в Таблице.
• Включен приблизительный поиск (Интервальный просмотр=1), но Таблица, в которой происходит поиск не отсортирована по возрастанию наименований.
• Формат ячейки, откуда берется искомое значение наименования (например B3 в нашем случае) и формат ячеек первого столбца (F3:F19) таблицы отличаются (например, числовой и текстовый). Этот случай особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, идентификаторы, даты и т.п.) В этом случае можно использовать функции Ч и ТЕКСТ для преобразования форматов данных. Выглядеть это будет примерно так:
  =ВПР(ТЕКСТ(B3);прайс;0)
• Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.). В этом случае можно использовать текстовые функции СЖПРОБЕЛЫ (TRIM) и ПЕЧСИМВ (CLEAN) для их удаления:
  =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(B3));прайс;0)
  =VLOOKUP(TRIM(CLEAN(B3));прайс;0)

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

Если нужно извлечь не одно значение а сразу весь набор (если их встречается несколько разных), то придется шаманить с формулой массива.

Функция ВПР() в EXCEL

Функция ВПР() , английский вариант VLOOKUP(), ищет значение в первом (в самом левом) столбце таблицы и возвращает значение из той же строки, но другого столбца таблицы.

Функция ВПР() является одной из наиболее используемых в EXCEL, поэтому рассмотрим ее подробно.

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

Синтаксис функции

ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)

Искомое_значение — это значение, которое Вы пытаетесь найти в столбце с данными. Искомое_значение может быть числом или текстом, но чаще всего ищут именно число. Искомое значение должно находиться в первом (самом левом) столбце диапазона ячеек, указанного в таблице .

Таблица — ссылка на диапазон ячеек. В левом столбце таблицы ищется Искомое_значение , а из столбцов расположенных правее, выводится соответствующий результат (хотя, в принципе, можно вывести можно вывести значение из левого столбца (в этом случае это будет само искомое_значение )). Часто левый столбец называется ключевым . Если первый столбец не содержит искомое_значение , то функция возвращает значение ошибки #Н/Д.

Номер_столбца — номер столбца Таблицы , из которого нужно выводить результат. Самый левый столбец (ключевой) имеет номер 1 (по нему производится поиск).

Параметр интервальный_просмотр может принимать 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 ).

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

При решении таких задач ключевой столбец лучше предварительно отсортировать (это также поможет сделать Выпадающий список нагляднее). Кроме того, в случае несортированного списка, ВПР() с параметром Интервальный_просмотр ИСТИНА (или опущен) работать не будет.

В файле примера лист Справочник также рассмотрены альтернативные формулы (получим тот же результат) с использованием функций ИНДЕКС() , ПОИСКПОЗ() и ПРОСМОТР() . Если ключевой столбец (столбец с артикулами) не является самым левым в таблице, то функция ВПР() не применима. В этом случае нужно использовать альтернативные формулы. Связка функций ИНДЕКС() , ПОИСКПОЗ() образуют так называемый «правый ВПР»: =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)

В файле примера лист Справочник показано, что формулы применимы и для ключевых столбцов содержащих текстовые значения, т.к. артикул часто бывает текстовым значением. Также задача решена для несортированного ключевого столбца.

Примечание . Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием . (см. статью Выделение строк таблицы в MS EXCEL в зависимости от условия в ячейке ).

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

Задача2. Поиск ближайшего числа

Предположим, что нужно найти товар, у которого цена равна или наиболее близка к искомой.

Чтобы использовать функцию ВПР() для решения этой задачи нужно выполнить несколько условий:

1. Ключевой столбец, по которому должен производиться поиск, должен быть самым левым в таблице;
2. Ключевой столбец должен быть обязательно отсортирован по возрастанию;
3. Значение параметра Интервальный_просмотр нужно задать ИСТИНА или вообще опустить.

Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА)

Для вывода найденной цены (она не обязательно будет совпадать с заданной) используйте формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)

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

Найденное значение может быть далеко не самым ближайшим. Например, если попытаться найти ближайшую цену для 199, то функция вернет 150 (хотя ближайшее все же 200). Это опять следствие того, что функция находит наибольшее число, которое меньше или равно заданному.

Если нужно найти по настоящему ближайшее к искомому значению, то ВПР() тут не поможет. Такого рода задачи решены в разделе Ближайшее ЧИСЛО . Там же можно найти решение задачи о поиске ближайшего при несортированном ключевом столбце.

Примечание . Для удобства, строка таблицы, содержащая найденное решение, выделена Условным форматированием . Это можно сделать с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10) .

Примечание : Если в ключевом столбце имеется значение совпадающее с искомым, то функция с параметром Интервальный_просмотр =ЛОЖЬ вернет первое найденное значение, равное искомому, а с параметром =ИСТИНА — последнее (см. картинку ниже).

Если столбец, по которому производится поиск не самый левый, то ВПР() не поможет. В этом случае нужно использовать функции ПОИСКПОЗ() + ИНДЕКС() или ПРОСМОТР() .