Vlookup формула excel: Краткий справочник: функция ВПР — Служба поддержки Майкрософт

Как использовать функцию ВПР (VLOOKUP) в Excel

Наверняка многим активным пользователям табличного редактора Excel периодически приходилось сталкиваться с ситуациями, в которых возникала необходимость подставить значения из одной таблицы в другую. Вот представьте, на ваш склад зашёл некий товар. В нашем распоряжении имеется два файла: один с перечнем наименований полученного товара, второй — прайс-лист этого самого товара. Открыв прайс-лист, мы обнаруживаем, что позиций в нём больше и расположены они не в той последовательности, что в файле с перечнем наименований. Вряд ли кому-то из нас понравится идея сверить оба файла и перенести цены из одного документа в другой вручную. Разумеется, в случае, когда речь идёт о 5–10 позициях, механическое внесение данных вполне возможно, но что делать, если число наименований переваливает за 1000? В таком случае справиться с монотонной работой нам поможет Excel и его волшебная функция ВПР (или vlookup, если речь идёт об англоязычной версии программы).

Что такое ВПР и как ею пользоваться?

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

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

1. Для начала приведите таблицу Excel в необходимый вам вид. Добавьте к заготовленной матрице данных два столбца с названиями «Цена» и «Стоимость». Выберите для ячеек, находящихся в диапазоне новообразовавшихся столбцов, денежный формат.
2. Теперь активируйте первую ячейку в блоке «Цена» и вызовите «Мастер функций». Сделать это можно, нажав на кнопку «fx», расположенную перед строкой формул, или зажав комбинацию клавиш «Shift+F3». В открывшемся диалоговом окне отыщите категорию «Ссылки и массивы». Здесь нас не интересует ничего кроме функции ВПР. Выберите её и нажмите «ОК». Кстати, следует сказать, что функция VLOOKUP может быть вызвана через вкладку «Формулы», в выпадающем списке которой также находится категория «Ссылки и массивы».
3. После активации ВПР перед вами откроется окно с перечнем аргументов выбранной вами функции. В поле «Искомое значение» вам потребуется внести диапазон данных, содержащийся в первом столбце таблицы с перечнем поступивших товаров и их количеством. То есть вам нужно сказать Excel, что именно ему следует найти во второй таблице и перенести в первую.
4. После того как первый аргумент обозначен, можно переходить ко второму. В нашем случае в роли второго аргумента выступает таблица с прайсом. Установите курсор мыши в поле аргумента и переместитесь в лист с перечнем цен. Вручную выделите диапазон с ячейками, находящимися в области столбцов с наименованиями товарной продукции и их ценой. Укажите Excel, какие именно значения необходимо сопоставить функции VLOOKUP.
5. Для того чтобы Excel не путался и ссылался на нужные вам данные, важно зафиксировать заданную ему ссылку. Чтобы сделать это, выделите в поле «Таблица» требуемые значения и нажмите клавишу F4. Если всё выполнено верно, на экране должен появиться знак $.
6. Теперь мы переходим к полю аргумента «Номер страницы» и задаём ему значения «2». В этом блоке находятся все данные, которые требуется отправить в нашу рабочую таблицу, а потому важно присвоить «Интервальному просмотру» ложное значение (устанавливаем позицию «ЛОЖЬ»). Это необходимо для того, чтобы функция ВПР работала только с точными значениями и не округляла их.

Теперь, когда все необходимые действия выполнены, нам остаётся лишь подтвердить их нажатием кнопки «ОК». Как только в первой ячейке изменятся данные, нам нужно будет применить функцию ВПР ко всему Excel документу. Для этого достаточно размножить VLOOKUP по всему столбцу «Цена». Сделать это можно при помощи перетягивания правого нижнего уголка ячейки с изменённым значением до самого низа столбца. Если все получилось, и данные изменились так, как нам было необходимо, мы можем приступить к расчёту общей стоимости наших товаров. Для выполнения этого действия нам необходимо найти произведение двух столбцов — «Количества» и «Цены». Поскольку в Excel заложены все математические формулы, расчёт можно предоставить «Строке формул», воспользовавшись уже знакомым нам значком «fx».

Важный момент

Казалось бы, всё готово и с нашей задачей VLOOKUP справилась, но не тут-то было. Дело в том, что в столбце «Цена» по-прежнему остаётся активной функция ВПР, свидетельством этого факта является отображение последней в строке формул.  То есть обе наши таблицы остаются связанными одна с другой. Такой тандем может привести к тому, что при изменении данных в таблице с прайсом, изменится и информация, содержащаяся в нашем рабочем файле с перечнем товаров.

Подобной ситуации лучше избежать посредством разделения двух таблиц. Чтобы это сделать, нам необходимо выделить ячейки, находящиеся в диапазоне столбца «Цена», и щёлкнуть по нему правой кнопкой мыши. В открывшемся окошке выберите и активируйте опцию «Копировать». После этого, не снимая выделения с выбранной области ячеек, вновь нажмите правую кнопку мыши и выберите опцию «Специальная вставка».

Активация этой опции приведёт к открытию на вашем экране диалогового окна, в котором вам нужно будет поставить флажок рядом с категорией «Значение». Подтвердите совершённое вами действия, кликнув на кнопку «ОК».

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

Как сравнить две таблицы в Excel?

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

Возможность работы с несколькими условиями

Ещё одним несомненным достоинством функции VLOOKUP является его способность работать с несколькими параметрами, присущими вашему товару. Чтобы найти товар по двум или более характеристикам, необходимо:

1. Создать два (или, при необходимости, более) условий для поиска.
2. Добавить новый столбец, в который в процессе работы функции добавятся все прочие столбцы, по которым происходит поиск товара.
3. В полученном столбце, по вышеописанному алгоритму, вводим уже знакомую нам формулу функции VLOOKUP.

В заключение стоит сказать, что поддержание Excel такой функции, как ВПР, значительно упрощает работу с табличной информацией. Не бойтесь использовать VLOOKUP в работе с огромным количеством данных, ведь как бы они не были оформлены, принцип работы функции всегда один и тот же. Всё, что вам необходимо сделать — правильно определить её аргументы.

Гид по функции ВПР (VLOOKUP) в Google Таблицах и Excel

Инструкции

Что это за функция и как с ней работать

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

Мы в Telegram

В канале «Маркетинговые щи» только самое полезное: подборки, инструкции, кейсы.

Не всегда на серьёзных щах — шуточки тоже шутим =)

Подписаться

Станьте email-рокером 🤘

Пройдите бесплатный курс и запустите свою первую рассылку

Подробнее

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

Чтобы было быстрее и проще работать, в Google Таблицах и Excel есть множество функций. Одна из таких — ВПР (VLOOKUP). Она мгновенно и точно находит нужные данные в указанном диапазоне, позволяет автоматически переносить их с одного листа на другой (или с одной таблицы на другую, если использовать вместе с функцией IMPORTRANGE).

Чтобы самостоятельно поработать с шаблоном и примерами из статьи, можно открыть эту таблицу, выбрать «Файл → Создать копию».

Как работает ВПР

ВПР (VLOOKUP) — функция поиска и извлечения данных, которая:

• принимает определенный набор символов в качестве запроса;
• ищет совпадение с этим запросом в крайнем левом столбце заданного диапазона;
• копирует значения из ячейки, которая находится в соседнем столбце, но на этой же строке.

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

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

Запрос показывает, что мы ищем в таблице. Например, наименование товара.

Диапазон отражает, где мы ищем запрос. Например, в диапазоне B2:C20. И если будет совпадение с ячейкой B1, функция ничего не вернет, так как эта ячейка не входит в указанный диапазон.

Индекс — номер столбца, который определяет, из какого столбца возвращать значение. Например, если в качестве диапазона указать B2:D11, то столбец C будет вторым, а D — третьим.

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

Сортировка говорит, отсортированы значения в таблице или нет. 1 или ИСТИНА (TRUE) — да, 0 или ЛОЖЬ (FALSE) — нет.

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

К тому же неточный поиск работает только в отношении чисел. Если ищем по словам, нужно обязательно передавать последним параметром 0 (ЛОЖЬ, FALSE).

А что если будет несколько ячеек, которые соответствуют искомому запросу? Тогда функция все равно выдаст только один результат — завершит работу, как только наткнется на первое совпадение.

Как пользоваться функцией ВПР

Разберемся на примере. Допустим, магазин электроники постоянно торгует разными видами товаров и в очередной раз закупил новую партию. 

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

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

Шаг 1: Выбираем функцию и запрос

Для этого в ячейке «стоимость покупки» набираем равно «=» и пишем ВПР. После этого нажимаем на ячейку с запросом в столбце «название товара». Либо прописываем в скобках координаты ячейки. В нашем случае это «B4».

Шаг 2: Настраиваем диапазон запроса

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

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

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

Шаг 3: Выбираем номер столбца

Индекс (номер столбца) передаем следующим после диапазона.

Важно: столбцы считаются внутри выбранного диапазона.

Так, в нашем примере нужно взять и перенести информацию о стоимости электроники, которая находится в столбце D. Если смотреть на весь лист, то D — это четвертый по счету столбец. А вот в диапазоне C:D столбец D — это именно второй по счету.

Шаг 4: Выбираем параметр «отсортировано» или «не отсортировано»

На этом этапе функция определяет, что искать: точное или неточное (меньшее или равное) совпадение с запросом. Напомню, что здесь есть два варианта:

0 (ЛОЖЬ, FALSE). ВПР выбирает точь-в-точь подходящий вариант. Как правило, используют именно такой режим поиска.

1 (ИСТИНА, TRUE). Функция выбирает примерно подходящий вариант, меньший или равный, но не больший. Это нужно гораздо реже, и имеет смысл, только если значения в диапазоне отсортированы.

Допустим, нам нужно сопоставить размер скидки для клиента с количеством покупок. Для этого создаем отдельную таблицу с диапазоном скидок. Важно, чтобы диапазон был возрастающий. Например, 5, 7, 9, 12, 15. Иначе функция работать не будет.

Теперь прописываем функцию. Ячейка запроса — количество покупок, диапазон — вторая таблица с количеством покупок и размером скидки, номер столбца — второй, сортировка — «ИСТИНА» или «1».

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

Шаг 5: Настраиваем функцию под всю таблицу

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

Но перед этим важно зафиксировать значения диапазона. Для этого нужно поставить знаки доллара как минимум после названий столбцов («G» и «H» в нашем случае), а лучше и перед тоже. Это можно сделать вручную или выделить диапазон и нажать «F4» на клавиатуре. 

В противном случае при протягивании формула будет меняться, например, так: G4:h26 → G5:h27 → G6:h28 и так далее. А нам важно искать все значения в определенном диапазоне.

Как сравнить таблицы с помощью ВПР

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

Для начала подтягиваем в таблицу старые цены. Формула будет выглядеть так: 

=ВПР(A4;’Таблица со старыми ценами’!$B$4:$C$16;2;0)

После этого добавляем данные из таблицы с новыми ценами. Формула будет такой:

=ВПР(A4;$G$4:$H$16;2;0)

Теперь можно сравнить цены. Чтобы не делать это вручную, прописывайте формулу: новая цена – старая цена / новая цена. А в формате чисел выберите процент.

Как работать с ВПР, если искомое значение — слева, а не справа

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

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

Поэтому самый простой способ — скопировать столбец «Цена» и перенести его в правый, после «Код товара». После этого внести новый столбец в диапазон и работать с ВПР как и прежде.

Если такой вариант не подходит, есть более сложный — через массивы.

Массив в Excel и Google Таблицах — это определенный набор данных, можно сказать, та же таблица, только «виртуальная». 

Массивы могут быть одномерными, то есть состоять только из строк вроде {1\2} или столбцов вида {1;2}. Или же многомерными — включать и столбцы, и строки.

Для поиска цены создадим отдельные поля «Код товара» и «Цена».

И прописываем ВПР для ячейки «Цена». Функция будет вида

=ВПР(H6;{‘Лист6’!D:D \ ‘Лист6’!C:C};2;0), где:

• H6 — номер ячейки с кодом товара.
• Лист6 — название листа, в котором находится наша таблица с ценами, кодами и количеством товаров.
• D:D — диапазон столбца с кодами товаров.
• C:C — диапазон столбца с ценами товаров.
• 2 — номер столбца из диапазона.
• 0 — точное соответствие.

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

В итоге, когда введем в ячейку «Код товара» соответствующие данные, получим информацию о стоимости устройств.

Есть и другие способы работать с ВПР, если искомое значение слева. Например, с помощью функции СУММЕСЛИ, ВЫБОР, ИНДЕКС и ПОСКПОЗ — о таком варианте рассказывали на сайте «Планета Excel». Другую полезную инструкцию по работе с массивами выкладывали в телеграм-канале «Google Таблицы».

Как использовать символьные шаблоны ВПР

Мы рассказывали о том, что в ВПР есть неточный поиск, который работает только с цифрами. Но для неточного поиска по словам тоже кое-что есть.

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

Для этого прописываем формулу:

=ВПР(«*Iphone*»;диапазон;1;0) 

Звездочки означают любое количество любых символов (в том числе их отсутствие). То есть условию будут соответствовать и «Apple Iphone», и «Iphone 12», и «Iphone».

А чтобы найти ячейку со словом Iphone с определенным количеством знаков после него, нужно составить формулу вида:

=ВПР(«*Iphone ?? ???»;диапазон;1;0)

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

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

Поделиться

СВЕЖИЕ СТАТЬИ

Другие материалы из этой рубрики

Не пропускайте новые статьи

Подписывайтесь на соцсети

Делимся новостями и свежими статьями, рассказываем о новинках сервиса

«Честно» — авторская рассылка от редакции Unisender

Искренние письма о работе и жизни. Свежие статьи из блога. Эксклюзивные кейсы и интервью с экспертами диджитала.

Оставляя свой email, я принимаю Политику конфиденциальности

Наш юрист будет ругаться, если вы не примете 🙁

Как запустить email-маркетинг с нуля?

В бесплатном курсе «Rock-email» мы за 15 писем расскажем, как настроить email-маркетинг в компании. В конце каждого письма даем отбитые татуировки об email ⚡️

*Вместе с курсом вы будете получать рассылку блога Unisender

Оставляя свой email, я принимаю Политику конфиденциальности

Наш юрист будет ругаться, если вы не примете 🙁

Функция ВПР Excel

❮ Назад
Далее ❯

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

Он имеет тип = ВПР и состоит из следующих частей:

= ВПР( искомое_значение , табличный_массив ,
col_index_num , [ range_lookup ])

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

Примечание: Различные части функции разделены символом, например запятой , или точкой с запятой ;

Символ зависит от ваших языковых настроек.

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

Table_array: Диапазон таблицы, включая все ячейки таблицы.

Col_index_num: Данные, которые просматриваются. Вход
— номер столбца, считая слева:

Range_lookup: ИСТИНА, если числа (1), или ЛОЖЬ, если текст (0).

Примечание: В Range_lookup можно использовать как 1/0, так и True/False.

Как пользоваться функцией ВПР .

1. Выберите ячейку ( h5 )
2. Тип = ВПР
3. Дважды щелкните команду ВПР
4. Выберите ячейку, в которую будет введено значение поиска ( h4 )
5. Тип (, )
6. Диапазон таблицы меток ( A2:E21 )
7. Тип (, )
8. Введите номер столбца, считая слева ( 2 )
9. Тип True (1) или False (0) ( 1 )
10. Нажмите Enter
11. Введите значение в ячейку, выбранную для Lookup_value h4(7)

Давайте посмотрим на пример!

Используйте функцию VLOOKUP , чтобы найти имена покемонов на основе их ID# :

h5 — здесь отображается результат поиска. В этом случае имена покемонов основаны на их ID#.

h4 выбрано как lookup_value . Это ячейка, в которую вводится поисковый запрос. В данном случае Pokemons ID# .

Диапазон таблицы отмечен как table_array , в этом примере A2:E21 .

Число 2 вводится как col_index_number . Это второй столбец слева, и это данные, которые просматриваются.

Иллюстрация выбора col_index_number 2 .

Итак, далее — 1 (Истина) вводится как range_lookup . Это потому, что в самом левом столбце есть только числа. Если это был текст,
0 (False) .

Хорошая работа! Функция возвращает значение #Н/Д . Это связано с тем, что не было введено никакого значения для идентификатора поиска № h4 .

Давайте зададим ему значение, введите h4(7) :

Взгляните на это! Функция VLOOKUP успешно нашла покемона Squirtle с ID# 7 .

Еще раз, введите (h4)4 :

Он все еще работает! Функция вернула имя Charmanders, которое имеет 4 в качестве ID#. Отлично .

❮ Предыдущий
Следующий ❯

ВЫБОР ЦВЕТА

Лучшие учебники

Учебник HTML
Учебник CSS
Учебник JavaScript
Учебник How To
Учебник SQL
Учебник Python
Учебник W3.CSS
Учебник Bootstrap
Учебник PHP
Учебник Java
Учебник C++
Учебник по jQuery

Основные ссылки

Справочник по HTML
Справочник по CSS
Справочник по JavaScript
Справочник по SQL
Справочник по Python
Справочник по W3.CSS
Справочник по Bootstrap
Справочник по PHP
Цвета HTML
Справочник по Java
Справочник по Angular
Справочник по jQuery

90901
Примеры HTML
Примеры CSS
Примеры JavaScript
Примеры How To
Примеры SQL
Примеры Python
Примеры W3. CSS
Примеры Bootstrap
Примеры PHP
Примеры Java
Примеры XML
Примеры jQuery

FORUM |
О

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

Copyright 1999-2023 Refsnes Data. Все права защищены.
W3Schools работает на основе W3.CSS.

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

Урок 19. Как использовать функцию ВПР Excel

/en/excel-tips/a-trick-for-finding-inconsistent-data/content/

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

Многие из наших учащихся говорили нам, что хотят научиться использовать функцию ВПР Excel. ВПР — чрезвычайно полезный инструмент, и научиться им пользоваться проще, чем вы думаете!

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

Что такое ВПР?

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

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

Мы добавим нашу формулу в ячейку F2 , но вы можете добавить его в любую пустую ячейку. Как и в любой формуле, вы начнете со знака равенства (=). Затем введите имя формулы. Наши аргументы должны быть заключены в круглые скобки, поэтому введите открывающую круглую скобку. Пока это должно выглядеть так:

=ВПР(

Добавление аргументов

Теперь мы добавим наши аргументы. Аргументы сообщат ВПР, что искать и где искать.

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

=VLOOKUP(«Фоторамка»

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

=VLOOKUP(«Фоторамка», A2:B16

Важно знать, что функция VLOOKUP всегда будет найдите в этом диапазоне первый столбец . В этом примере он будет искать столбец A по запросу «Фоторамка». Значение, которое он возвращает (в данном случае цена), всегда должно быть справа от этого столбца.

Третий аргумент — это порядковый номер столбца . Это проще, чем кажется: первый столбец диапазона равен 1, второй столбец — 2 и т. д. В данном случае мы пытаемся найти цену товара , а цены содержатся во втором столбце . . Это означает, что наш третий аргумент будет 2 :

=ВПР(«Фоторамка», A2:B16, 2

Четвертый аргумент сообщает ВПР, нужно ли искать примерно соответствует и может быть либо ИСТИНА, либо ЛОЖЬ. Если это TRUE, он будет искать приблизительные совпадения. Как правило, это полезно только в том случае, если в первом столбце есть числовые значения, которые были отсортированы. Поскольку мы ищем только точные совпадения, четвертый аргумент должен быть FALSE . Это наш последний аргумент, поэтому закройте скобки:

=ВПР(«Фоторамка», A2:B16, 2, ЛОЖЬ)

Вот и все! Когда вы нажмете Enter, он должен дать вам ответ, который равен 9.0006 9,99 .

Как это работает

Давайте посмотрим, как работает эта формула. Сначала ищет по вертикали вниз по первому столбцу (VLOOKUP — это сокращение от по вертикали ). Когда он находит «Фоторамку», он перемещается во второй столбец , чтобы найти цену.

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

Если мы хотим найти цену другого товара, мы можем просто изменить первый аргумент:

=ВПР(«Футболка», A2:B16, 2, ЛОЖЬ)

или:

=ВПР(«Подарочная корзина», A2:B16, 2, ЛОЖЬ)

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

Другой пример

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

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

Мы также добавили третий столбец с категорией для каждого элемента. Это даст нам возможность найти цену или категорию. Вот как выглядит таблица на данный момент:

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

= ВПР(E2, A2:B16, 2, ЛОЖЬ)

Чтобы найти категорию, нам понадобится изменить второй и третий аргументы . Во-первых, мы изменим диапазон на A2:C16 , чтобы он включал третий столбец. Далее мы изменим порядковый номер столбца на 9.0006 3 потому что наши категории находятся в третьем столбце:

=VLOOKUP(E2, A2:C16, 3, FALSE)

Когда вы нажмете Enter, вы увидите, что подарочная корзина находится в Gifts категория.

Если мы хотим найти категорию другого элемента, мы можем просто изменить имя элемента в ячейке E2 :

Попробуйте это!

Если вы хотите больше практики, посмотрите, можете ли вы найти следующее:

• Цена кофейной кружки
• Категория пейзажной живописи
• Цена сервировочной чаши
• Категория платка

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