Как пользоваться функцией ВПР в Excel
Функция ВПР в Excel помогает находить данные по строкам в таблице или диапазоне, а также переносить значения из одной таблицы в другую, если вы работаете с большими массивами данных. Её использование в разы ускоряет процесс выполнения задач, что на руку бухгалтерам, аналитикам и другим специалистам. В статье рассмотрим особенности применения функции ВПР в Excel с примерами: как она работает и зачем нужна, как запустить функцию и правильно использовать.
Зачем функция ВПР есть в Excel
ВПР или Vlookup расшифровывается как вертикальный просмотр. Функция имеет следующий синтаксис:
=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)
С помощью неё удобно сравнивать многосоставные данные, находящиеся в одном файле. Например, у вас есть информация о производителях телефона и их стоимости, а также контактные данные покупателей. Всё это находится в разных таблицах, а вам нужно позвонить потенциальным клиентам, которые оставили заявки, и сообщить подробности. Для этого как раз и удобно использовать описанную выше функцию — не придётся искать вручную цену смартфона в столбце, а затем переносить её. Функция поможет автоматически скопировать цены из списка с товарами в список с номерами. Теперь напротив каждого покупателя будет стоять не только производитель, но и цена, которую он должен заплатить. Такой подход помогает избежать большого количества ошибок и используется в проектах для решения многих задач.
Важно, что ВПР работает только в том случае, когда данные находятся в одном файле, на разных листах.
Активно используете Excel в работе, но никак не можете разобраться с полным функционалом программы? Обратите внимание на онлайн-курсы, которые можно проходить в удобном для вас темпе прямо из дома:
- «Excel и Google-таблицы для ростабизнеса» от Eduson Academy
- «Power BI и Excel для продвинутых» от Нетологии
Получите промокод 10 000 ₽ на обучение и курсы стоимостью 20 000 ₽ в подарок!
Нажимая на кнопку «Получить», я соглашаюсь на обработку моих персональных данных.
Спасибо! Мы получили вашу заявку. Скоро с вами свяжемся.
Запуск функции ВПР
Принцип работы Vlookup довольно простой: ВПР проводит анализ выбранного диапазона, передвигается сверху вниз, ищет заданное значение. Когда функция его находит, то выбирает информацию напротив него из нужного столбца и переносит во вторую таблицу. Excel — универсальная программа не только для сотрудников крупных компаний и стартапов. Её активно используют в бизнесе в различных случаях. Там никуда и без аналитики — на курсе от Moscow Business Academy подробно расскажут про инструменты капитализации, веб-аналитику и правильную постройку модели бюджета компании.
Рассмотрим запуск на примере списков с ценой телефона и данными клиентов. Первым делом необходимо построить и найти функцию. Для этого выделите ячейку, в которую формула перенесёт выбранные данные. Во второй таблице создайте столбец и напишите название «Цена», а затем выделите ячейку напротив первого покупателя.
Далее откройте окно, чтобы построить функцию. Существует два способа: вам необходимо перейти во вкладку «Формулы» и нажать на «Вставить функцию». Или же нажать на «fx» в строке ссылок. Перед вами возникнет окно «Построитель формул». Вам через поиск надо найти ВПР и выбрать «Вставить функцию». Должно появиться окно для ввода аргументов функции. Давайте вместе заполним его.
Маркетолог — востребованная профессия с широким спектром задач. На курсе «Интернет-маркетолог» покажут, как работать с аудиторией и настраивать рекламу. Один из блоков состоит из изучения специализированных программ, погружения в специфику их функционирования.
Аргументы функции ВПР
В Excel есть несколько видов аргументов: искомое значение, таблица, номер столбца, интервальный просмотр. В статье остановимся на каждом из них.
Искомое значение — это наименование ячейки с одинаковыми данными, на основе которых функция и будет осуществлять поиск нужной информации в этом случае. В примере это тип смартфона. ВПР сможет найти подходящую в таблице с каталогом из всех моделей, затем вытащит из столбца точную стоимость и скопирует её в таблицу с покупателями. Чтобы установить указанный параметр и количество, выставите курсор в окне «Искомое значение». Затем выберите ячейку А2 — это первое значение столбца «Тип». Указанное нами значение аргумента в этом случае кроме построителя формул отразится в формуле строки ссылок. Выглядит это так — fx = ВПР (А2) .
Стратегический менеджмент нужен тем, кто хочет научиться управлять финансами и выстраивать чёткие бизнес-процессы. Eduson Academy готовят специалистов по этому профилю и выдают диплом, подтверждающий квалификацию.
Таблица — аргумент-диапазон ячеек, в котором будет выполняться поиск и использование искомого и возвращаемого значения. Он должен состоять из столбцов с искомым значением и с теми данными, которые надо скопировать в первую таблицу. Мы переносим цену техники, это может быть и любой другой параметр. Диапазон для поиска обязательно должен включать столбцы «Тип» (искомое) и «Цена, руб.» (данные, которые переносим). Обратите внимание, что для правильного функционирования искомое значение должно быть в первом столбце по возрастанию. В нашем примере оно в ячейке A2, диапазон базы должен начинаться именно с A.
Действуйте так: установите курсор в поле «Таблица» в построителе формул, а затем вернитесь к таблице с марками смартфонов. Выберите диапазон с колонками «Модель» и «Цена». В примере это будет выбор — А2:Е19. Следующий шаг — необходимо сделать закрепление. Если работаете на Windows, выберите значение диапазона в строке ссылок и нажмите F4. MacOS требует другого подхода — сначала надо выбрать данные диапазона в строке ссылок, а затем нажать сочетание клавиш Cmd + T. Закрепление необходимо для того, чтобы при протягивании функции вниз, она нормально сработала во всех строках по количеству чисел. В формуле строки ссылок появится такое возвращаемое значение — fx=ВПР(A2; товары1′!$A$2:$E$7) .
Следующий пункт — номер столбца. Это столбец, из которого нужно взять данные о количестве или цене, например. Из него мы и «подтягиваем» результат. Чтобы найти нужные цифры, считаем слева направо — цена телефона находится в пятом столбце слева. Важно установить курсор в окно «Номер столбца» и ввести число. У нас эти данные соответствуют 5 — указываем это число. Такой текст программа возвращает в формуле в строке ссылок: fx=ВПР(A2;’ товары1!$A$2:$E$7;5) .
Последний аргумент — интервальный просмотр. Он поможет настроить точность работы функции. Аргумент принимает два значения для символов в каждом случае: «истина» или «ложь». При точном (истина) совпадении при поиске ВПР надо ввести 0, второй вариант: для приближенного значения (ложь) ставим 1. В примере важно точное значение — поэтому выбираем первый вариант. Значение 0 нужно ввести в окно «Интервальный просмотр». Оно тут же появится в формуле строки ссылок: fx=ВПР(A2;’ товары1′!$A$2:$E$7;5;0) .
Другие инструменты пакета MS Office также важны для работы. На курсе «MS Office:Excel, Word и PowerPoint» вы научитесь эффективно работать с текстовыми документами и писать электронные материалы, анализировать большое количество данных с помощью таблиц и делать запоминающиеся презентации.
Результат использования ВПР в Excel
Для получения результата нажимаем кнопку «Готово» в построителе формул. В выбранной ячейке увидите необходимое значение. У нас это цена первой модели смартфона. Пока что функция сработала для одной строки — протяните это значение вертикально вниз, чтобы она сработала на всех строках. Специально для этого нужно закрепление интервала.
В итоге получаем отсортированную таблицу с контактами клиентов и данными о смартфонах, который каждый из них хотел бы приобрести. Вы можете начинать звонить людям и задавать нужные вопросы, не боясь допустить ошибку. Перед использованием приложения рекомендуем изучить политику конфиденциальности и правила программы.
В своей работе программой Excel пользуются и менеджеры маркетплейсов — для них особенно важно знание функции ВПР, когда товаров очень много. «Менеджер по маркетплейсам» — курс, благодаря которому вы начнёте активно работать на крупных маркетплейсах уже через 3,5 месяца обучения.
Поиск по нескольким критериям
Мы рассмотрели сравнение по одному критерию — марке телефона. Но часто при работе может потребоваться сравнение двух или более критерий. Например, у одной модели может быть 128 и 256 Гб памяти — так таблица содержит разные товары. Или же смартфоны разных оттенков. В Екселе можно использовать и представить эту функцию с несколькими условиями.
Если мы применим описанный выше путь ВПР, выйдет такой вариант функции: fx=ВПР(A2;’ товар’!$A$2:$E$7;5;0) . Если оставить её в таком формате, то ВПР при подтягивании стоимости будет выбирать первый попавшийся вид без учёта цвета. Теперь надо осуществлять поиск стоимости и сортировку сразу по двум критериям — тип и цвет.
Для этого добавьте в общую формулу — ЕСЛИ(‘товар’!$B$2:$D$7=B2) :
‘товар’!$B$2:$D$7 — так мы выделяем диапазон цвета,
B2 — это искомое значение цвета, первая ячейка в столбце с цветом.
В итоге получилась такая функция: fx=ВПР(A2;ЕСЛИ(‘Товары’!$B$2:$D$7=B2;’Товары’!$A$2:$E$7);5;0) .
«Инструменты бухгалтера: 1C: Бухгалтерия 8.3 и Excel» — обучение для бухгалтеров, которые хотят научиться работать с большими массивами данных. Вы также без труда сможете автоматизировать процессы заполнения документов на сайтах и регистрации хозяйственных операций.
Сравнение нескольких таблиц с помощью функции ВПР
Пользоваться этой функцией удобно, когда необходимо сравнить данные в двух или более таблицах. Допустим, у вас изменились цены и есть задача: сравнить ту стоимость товара, что была раньше, с новой.
- Откройте таблицу со старыми ценами и добавьте колонку «Новая цена».
- Наведите курсор на первую ячейку и через «Мастер функций» выберите ВПР. Задайте параметры и получите: =ВПР($A$2:$A$9;’новые цены’!$A$2:$B$9;2;ЛОЖЬ) .
- Что получилось? Мы выделили диапазон А2:А9 в старой таблице и сравнили его с новыми ценами. Теперь мы убрали новые значения в ячейку С2 нового столбца в старой таблице — это помогло вернуть знак соответствия и поставить значения в нужно порядке.
Функцией ВПР в Excel можно пользоваться и для решения других главных или полезных задач. Она позволяет за короткое время справляться с большим массивом сводных данных, поиском исходных и возвращаемых значений, сводит самые распространённые ошибки к минимуму, если сравнивать с классическими расчётами. Также функцию довольно просто изучать и применять к различным кейсам.
Анна Уженкова
Копирайтер. Увлекается литературой и любит разбираться в сложных вещах. В работе ценит сам процесс — тексты, это не просто тексты, а возможность на что-то повлиять. Среди тем, которые нравятся, — образование, психология, IT.
Редакция блога
Блог для тех, кто работает или хочет работать в сфере IT. Рассказываем о профессиях, обучении и полезных навыках.
Аргумент интервальный просмотр в функции ВПР
ВПР является одной из самых востребованных функций в Excel. Эта функция позволяет подтянуть значения из одной таблицы в другую по указанному критерию. Одной из важных составляющих является аргумент “интервальный просмотр” в функции ВПР.
Синтаксис функции ВПР:
= ВПР (Искомое_значение; таблица; номер_столбца; интервальный просмотр)
В данной статье подробно остановимся на последнем аргументе — интервальном просмотре в функции ВПР.
Аргумент “интервальный просмотр” в функции ВПР означает точность поиска совпадений в таблице.
0 — поиск точного совпадения
1 — поиск приблизительного совпадения
- ВПР с интервальным просмотром 0
- ВПР с интервальным просмотром 1
- Аргумент интервальный просмотр 1 в функции ВПР для расчета скидки
- Что будет, если забыть указать аргумент интервальный просмотр?
ВПР с интервальным просмотром 0
В абсолютном большинстве случаев в качестве интервального просмотра 0 (или ЛОЖЬ) — точный поиск. Это означает, что функция ВПР будет искать точное совпадение искомого значения со значениями в таблице. Настолько точное, что даже незаметный пробел в конце слова может вызвать ошибку #Н/Д (нет данных)
Рассмотрим на примере. В таблице показан поиск при помощи функции ВПР с интервальным просмотром 0. Как видите, excel точно нашел совпадение.
Теперь добавим пробел внутри ячейки с искомым значением.
Появилась ошибка #Н/Д (нет данных), хотя визуально ничего не изменилось.
В этом некоторое коварство интервального просмотра 0, которое обязательно нужно учитывать.
При этом, несмотря на то, что ВПР с интервальным поиском 0 ищет точное совпадение, функция игнорирует прописные и строчные буквы. Если в нашем примере мы напишем фамилию со строчной буквы, результат будет таким же.
ВПР с интервальным просмотром 1
Если аргумент интервальный просмотр в функции ВПР указать 1 (или ИСТИНА), то функция будет искать приблизительное совпадение в таблице. Причем, в случае с текстовыми данными, как в нашем примере, результат совершенно непредсказуем.
Убедимся в этом на примере. Для той же фамилии Казаков укажем аргумент интервальный просмотр 1. Как ни странно, ничего не изменилось.
Теперь укажем другую фамилию в качестве критерия. Результат получился совершенно другой.
И даже если указать искомое значение, которого вообще нет в левом столбце данной таблицы, ошибки #Н/Д не появится.
Как видите, результат таких вычислений довольно сложно использовать.
Однако, для чего же предусмотрена возможность приблизительного поиска в функции ВПР? Конечно же, для работы с числовыми значениями.
Аргумент интервальный просмотр 1 в функции ВПР для расчета скидки
Сейчас я докажу на примере, что приблизительный поиск в ВПР очень может пригодиться.
Часто в торговле используется ступенчатая система скидок. При превышении определенной стоимости покупки скидка возрастает. В нашем примере две таблицы: в первой перечислены чеки с суммами, во второй — условия скидки.
Теперь в ячейку D2 напишем следующую формулу.
В качестве искомого значения укажем сумму чека.
Скопируем формулу во все ячейки столбца Скидка, не забыв *закрепить диапазон абсолютными ссылками*
Завершим пример указанием процентного формата для ячеек со скидкой.
А теперь сравните получившиеся значения с правой таблицей. Скидка определилась правильно. Несмотря на то, что в качестве аргумента была задана сумма, и точного совпадения нет в таблице со скидками.
Возможности интервального просмотра 1 в функции ВПР можно использовать не только для определения величины скидки, но и для любых других числовых интервалов. К примеру, для определения возрастной группы, интервала заработных плат и т.д.
Только обратите внимание, что в таблице процент скидки тяготеет к нижнему порогу интервала. Например, для интервала от 2000 до 5000 процент скидки указан возле нижнего порога 2000.
Что будет, если забыть указать аргумент интервальный просмотр?
Аргумент интервальный просмотр в функции ВПР является необязательным. Заметьте, что он указан в квадратных скобках, что является признаком необязательного аргумента в формуле.
Поэтому, если забыть его указать, то ошибки не возникнет. Но и результат вычисления будет не точным.
Вернемся к нашему предыдущему примеру и сотрем аргумент “интервальный просмотр”.
Как видите, ошибки не возникло и какое-то значение определилось. Даже несмотря на то, что искомого значения нет в таблице.
Это произошло потому, что по умолчанию ВПР считает интервальный просмотр равным ИСТИНЕ или 1. И осуществляет приблизительный поиск в случае интервального просмотра.
Потому так важно правильно указывать аргумент интервальный просмотр в функции ВПР правильно. И в случае необходимости точного поиска обязательно указывать 0.
Функция ВПР в Excel
В табличном редакторе Microsoft Excel множество различных формул и функций. Они позволяют сэкономить время и избежать ошибок – достаточно правильно написать формулу и подставить нужные значения.
В этой статье мы рассмотрим функцию ВПР (или VLOOKUP, что означает «вертикальный просмотр»). Функция ВПР помогает работать с данными из двух таблиц и подтягивать значения из одной в другую. Использовать ее удобно, когда нужно посчитать выручку или прикинуть бюджет, если в одной таблице указан прайс-лист, а в другой количество проданного товара.
Допустим, есть таблица с количеством проданного товара и таблица с ценами на эти товары
Необходимо к каждому товару из таблицы слева добавить цену из прайса справа.
Как создать функцию ВПР в Excel
Необходимая последовательность значений в функции называется синтаксис. Обычно функция начинается с символа равенства «=», затем идет название функции и аргументы в скобках.
Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:
- Выделить ячейку и вписать функцию.
- Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».
После этого открывается окно, где можно заполнить ячейки аргументов формулы.
Синтаксис функции ВПР выглядит так:
=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)
В нашем случае получится такая формула:
=ВПР(A2;$G$2:$H$11;2;0)
Аргументы функции ВПР
Сейчас разберемся что и куда писать.
Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.
Искомое значение – это название ячейки, из которой мы будем «подтягивать» данные. Формула ВПР ищет полное или частичное совпадение в другой таблице, из которой берет информацию.
В нашем случае выбираем ячейку «A2», в ней находится наименование товара. ВПР возьмет это название и будет искать аналогичную ячейку во второй таблице с прайсом.
=ВПР(A2;
Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании.
В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.
=ВПР(A2;$G$2:$H$11
Номер столбца – это столбец таблицы, из которой нужно взять данные. Именно из него мы будем «подтягивать» результат.
- Формула сканирует таблицу по вертикали.
- Находит в самом левом столбце совпадение с искомым значением.
- Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.
- Передает данные в ячейку с формулой.
В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.
=ВПР(A2;$G$2:$H$11;2
Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.
=ВПР(A2;$G$2:$H$11;2;0)
Комьюнити теперь в Телеграм
Подпишитесь и будьте в курсе последних IT-новостей
Автозаполнение
В конце протягиваем формулу вниз до конца, в результате чего происходит автозаполнение.
Чтобы функция ВПР правильно сработала во время автозаполнения, искомое значение должно быть относительной ссылкой, а таблица – абсолютной.
- В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.
- Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу.
ВПР и приблизительный интервальный просмотр
В предыдущем примере мы «подтягивали» значения из таблицы, используя точный интервальный просмотр. Он подходит для работы с наименованиями. Теперь разберем ситуацию, когда может понадобиться приблизительный интервальный просмотр.
Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.
Товары такие же, как и в первом примере, но задача изменилась: нужно привязать формулу не к наименованию, а к количеству
Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.
Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:
Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение.
В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает.
Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает
Итоги
- Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз.
- Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).
- Функцию можно вписать вручную или в специальном окне (Shift + F3).
- Искомое значение – относительная ссылка, а таблица – абсолютная.
- Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением.
- Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.
- Порядок работы с функцией подходит для Гугл-таблиц.
Функция ВПР
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Совет: Попробуйте использовать новую функцию ПРОСМОТРX, улучшенную версию функции ВПР, которая работает в любом направлении и по умолчанию возвращает точные совпадения, что делает ее проще и удобнее в использовании, чем предшественницу.
Находите данные по строкам в таблице или диапазоне с помощью функции ВПР. Например, можно найти цену автомобильной детали по ее номеру или найти имя сотрудника по его идентификатору.
Самая простая функция ВПР означает следующее:
=ВПР(искомое значение; место для его поиска; номер столбца в диапазоне с возвращаемым значением; возврат приблизительного или точного совпадения — указывается как 1/ИСТИНА или 0/ЛОЖЬ).
Совет: Секрет функции ВПР состоит в организации данных таким образом, чтобы искомое значение (Фрукт) отображалось слева от возвращаемого значения, которое нужно найти (Количество).
Технические подробности
Используйте функцию ВПР для поиска значения в таблице.
ВПР(искомое_значение, таблица, номер_столбца, [интервальный_просмотр])
- =ВПР(A2;A10:C20;2;ИСТИНА)
- =ВПР(«Иванов»;B2:E7;2;ЛОЖЬ)
- =ВПР(A2;’Сведения о клиенте’!A:F;3;ЛОЖЬ)
Имя аргумента
искомое_значение (обязательный)
Значение для поиска. Искомое значение должно находиться в первом столбце диапазона ячеек, указанного в аргументе таблица.
Например, если таблица охватывает диапазон ячеек B2:D7, искомое_значение должно находиться в столбце B.
Искомое_значение может являться значением или ссылкой на ячейку.
таблица (обязательный)
Диапазон ячеек, в котором будет выполнен поиск искомого_значения и возвращаемого значения с помощью функции ВПР. Вы можете использовать именованный диапазон или таблицу, а также имена в аргументе вместо ссылок на ячейки.
Первый столбец в диапазоне ячеек должен содержать искомое_значение. Диапазон ячеек также должен содержать возвращаемое значение, которое нужно найти.
номер_столбца (обязательный)
Номер столбца (начиная с 1 для крайнего левого столбца таблицы), содержащий возвращаемое значение.
интервальный_просмотр (необязательный)
Логическое значение, определяющее, какое совпадение должна найти функция ВПР, — приблизительное или точное.
- Вариант Приблизительное совпадение — 1/ИСТИНА предполагает, что первый столбец в таблице отсортирован в алфавитном порядке или по номерам, а затем выполняет поиск ближайшего значения. Это способ по умолчанию, если не указан другой. Например, =ВПР(90;A1:B100;2;ЛОЖЬ).
- Вариант Точное совпадение — 0/ЛОЖЬ осуществляет поиск точного значения в первом столбце. Например, =ВПР(«Иванов»;A1:B100;2;ЛОЖЬ).
Начало работы
Для построения синтаксиса функции ВПР вам потребуется следующая информация:
- Значение, которое вам нужно найти, то есть искомое значение.
- Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.
- Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона вы указываете B2:D11, следует считать B первым столбцом, C — вторым и т. д.
- При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.
Теперь объедините все перечисленное выше аргументы следующим образом:
=ВПР(искомое значение; диапазон с искомым значением; номер столбца в диапазоне с возвращаемым значением; приблизительное совпадение (ИСТИНА) или точное совпадение (ЛОЖЬ)).
Примеры
Вот несколько примеров использования функции ВПР.
Пример 1
Пример 2
Пример 3
Пример 4
Пример 5
Объединение данных из нескольких таблиц на одном листе с помощью функции ВПР
С помощью функции ВПР вы можете объединить несколько таблиц в одну, если одна из таблиц содержит поля, общие для всех остальных. Это может быть особенно удобно, если вам нужно поделиться книгой с пользователями более старых версий Excel, которые не поддерживают функции данных с несколькими таблицами в качестве источников данных. Благодаря объединению источников в одну таблицу и изменению источника функции данных на новую таблицу, функцию данных можно использовать в более старых версиях Excel (при условии, что функция данных поддерживается в более старой версии).
Здесь столбцы A–F и H содержат значения или формулы, которые используют значения только на этом листе, а в остальных столбцах используется функция ВПР и значения столбца А (код клиента) и столбца B (адвокат) для получения данных из других таблиц.
Распространенные неполадки
Возможная причина
Неправильное возвращаемое значение
Если аргумент интервальный_просмотр имеет значение ИСТИНА или не указан, первый столбец должны быть отсортирован по алфавиту или по номерам. Если первый столбец не отсортирован, возвращаемое значение может быть непредвиденным. Отсортируйте первый столбец или используйте значение ЛОЖЬ для точного соответствия.
- Если аргумент интервальный_просмотр имеет значение ИСТИНА, а значение аргумента искомое_значение меньше, чем наименьшее значение в первом столбце таблицы, будет возвращено значение ошибки #Н/Д.
- Если аргумент интервальный_просмотр имеет значение ЛОЖЬ, значение ошибки #Н/Д означает, что найти точное число не удалось.
Дополнительные сведения об устранении ошибок #Н/Д в функции ВПР см. в статье Исправление ошибки #Н/Д в функции ВПР.
#ССЫЛКА! в ячейке
Если значение аргумента номер_столбца больше, чем число столбцов в таблице, появится значение ошибки #ССЫЛКА!.
Дополнительные сведения об устранении ошибок #ССЫЛКА! в функции ВПР см. в статье Исправление ошибки #ССЫЛКА!.
Если значение аргумента таблица меньше 1, появится значение ошибки #ЗНАЧ!.
Дополнительные сведения об устранении ошибок #ЗНАЧ! в функции ВПР см. в статье Исправление ошибки #ЗНАЧ! в функции ВПР.
Значение ошибки #ИМЯ? чаще всего появляется, если в формуле пропущены кавычки. Во время поиска имени сотрудника убедитесь, что имя в формуле взято в кавычки. Например, в функции =ВПР(«Иванов»;B2:E7;2;ЛОЖЬ) имя необходимо указать в формате «Иванов» и никак иначе.
Дополнительные сведения см. в статье Исправление ошибки #ИМЯ?.
Ошибки #ПЕРЕНОС! в ячейке
Эта конкретная ошибка #ПЕРЕНОС! обычно означает, что формула использует неявное пересечение для искомого значения и применяет весь столбец в качестве ссылки. Например, =ВПР(A:A;A:C;2;ЛОЖЬ). Вы можете устранить эту проблему, привязав ссылку подстановки с помощью оператора @, например: =ВПР(@A:A;A:C;2;ЛОЖЬ). Кроме того, вы можете использовать традиционный метод ВПР и ссылаться на одну ячейку вместо целого столбца: =ВПР(A2;A:C;2;ЛОЖЬ).
Рекомендации
Используйте абсолютные ссылки в аргументе интервальный_просмотр
Использование абсолютных ссылок позволяет заполнить формулу так, чтобы она всегда отображала один и тот же диапазон точных подстановок.
Не сохраняйте числовые значения или значения дат как текст.
При поиске числовых значений или значений дат убедитесь, что данные в первом столбце аргумента таблица не являются текстовыми значениями. Иначе функция ВПР может вернуть неправильное или непредвиденное значение.
Сортируйте первый столбец
Если для аргумента интервальный_просмотр указано значение ИСТИНА, прежде чем использовать функцию ВПР, отсортируйте первый столбец таблицы.
Используйте подстановочные знаки
Если значение аргумента интервальный_просмотр — ЛОЖЬ, а аргумент искомое_значение представляет собой текст, то в аргументе искомое_значение допускается использование подстановочных знаков: вопросительного знака (?) и звездочки (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом.
Например, с помощью функции =ВПР(«Ивано?»;B2:E7;2;ЛОЖЬ) будет выполнен поиск всех случаев употребления Иванов с последней буквой, которая может меняться.
Убедитесь, что данные не содержат ошибочных символов.
При поиске текстовых значений в первом столбце убедитесь, что данные в нем не содержат начальных или конечных пробелов, недопустимых прямых (‘ или «) и изогнутых (‘ или “) кавычек либо непечатаемых символов. В этих случаях функция ВПР может возвращать непредвиденное значение.
Для получения точных результатов попробуйте воспользоваться функциями ПЕЧСИМВ или СЖПРОБЕЛЫ.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.