Как пользоваться VLOOKUP Excel

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

Сущность и назначение функции ВПР в Excel

Это вас заинтересует: Война в Майнкрафте или как разнообразить симулятор строительства

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

Это вас заинтересует:BYD S6: фото, характеристики, характеристики автомобиля и отзывы владельцев

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

Аргументы функции

Это может вас заинтересовать: Chevrolet Equinox: обзор, характеристики, отзывы

Для применения функции необходимо остановиться на полученной ячейке, выбрать на ленте вкладку «Формулы» — «Ссылки и массивы» — «ВПР». В ячейке появилась надпись «=ВПР(» . Теперь нужно правильно ввести аргументы функции. Сделать это можно через точку с запятой прямо в строке формул. Однако начинающему пользователю удобнее сделать это вручную через диалог аргументов функции.

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

желаемое значение — на что обратить внимание;
таблица — где искать;
номер столбца — в каком столбце искать;
интервальный вид — отсортированный.

Аргумент «Искомое значение»

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

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

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

Преобразование в числовой формат простое: нужно умножить массив на единицу.

Аргумент «Таблица»

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

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

Аргумент «Номер столбца»

Здесь необходимо записать цифру, в каком столбце, с крайнего левого, необходимо принять значение подстановки в результате расчета. В примере выше это вторая колонка, в строке аргумента следует ввести цифру 2. Если бы между колонками «Код» и «Цена» была еще одна колонка, то нужно было бы ввести цифру 3, и так на.

Аргумент «Интервальный просмотр»

Это поле является необязательным, но может быть очень важным. Это может быть одно из двух значений: 1 (истина) или 0 (ложь). Большинство пользователей предполагают, что функция этого аргумента заключается в определении точного совпадения искомых значений. Это не совсем правильно. Если указать единицу измерения, то при наличии дубликатов в диапазоне таблицы функция вернет последнее найденное значение. В этом случае функция ВПР будет рассматривать все значения, меньшие или равные значению, введенному в поле «Искомое значение». Если функция находит большее значение, но не находит меньшее или равное значение, она вернет ошибку N/A.

Видно, что в поле «Значение» введено число 3187849425, в искомом диапазоне такого значения нет, и программа, найдя все значения меньше или равные искомому, вернула значение, соответствующее до последнего подходящего кода в списке: 3187848593, цена которого 2479,46 руб. Если поле «Интервальный поиск» оставить пустым, функция будет работать так же, как и со значением единицы измерения.

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

Особенности использования ВПР по нескольким условиям

Использование функции ВПР часто представляет собой творческий процесс, требующий от пользователя математического мышления. Часто требуется найти совпадение не в одном столбце, а в двух и даже больше. Используя дополнительные действия, вы также можете применить функцию ВПР. Вам необходимо создать дополнительные столбцы в обеих таблицах, где вы объединяете данные из рассматриваемых столбцов. Это можно сделать с помощью функции «СЦЕПИТЬ» или значка «&».

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

Как использовать ВПР в Excel на разных листах, в разных файлах? Пользователи часто создают каталоги для сравнения и сопоставления. Отличие работы в таких условиях заключается в несколько иной форме поля «Таблица» в окне аргументов функции. Перед вводом диапазона необходимо ввести номер листа (если данные находятся в одной книге) или имя файла (если данные находятся в разных книгах). Если в примере выше цену скопировать в отдельный файл, назвать его «Цена» и с помощью функции ВПР искать там цены, то это будет выглядеть так:

Все вышеперечисленные действия можно выполнить по ссылке. Вам нужно поставить курсор в поле «Таблица», перейти к нужному файлу и выделить диапазон мышкой. Иногда бывает так, что таблица не вставляется как ссылка в окно. Затем нужно сделать следующее: открыть исходную таблицу, в которой нужно сделать расчеты, затем найти вторую таблицу через меню «Файл» — «Открыть». Файлы, открытые таким образом, отлично взаимодействуют.

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

Выпадающий список для облегчения работы с ВПР

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

Пример использования функции VLOOKUP в Excel

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

Сначала нужно правильно написать формулу.

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

Это пример того, как функция ВПР работает в Excel.

Ошибки при использовании функции ВПР

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

«Н/Д» — наиболее распространенный тип ошибки. Это может произойти по разным причинам.

Столбец, в котором функция ищет совпадения, расположен неправильно (он должен быть крайним слева). Если возникает ситуация, когда искомое значение находится слева от зоны соответствия, таблицу необходимо преобразовать. Например, скопируйте нужный столбец и вставьте его справа от области поиска.
Может быть возвращена ошибка «Н/Д», если диапазон поиска не привязан при перетаскивании формулы ВПР.
Если в аргументе Ranged Lookup указан точный поиск (введено число 0), возвращается ошибка «Н/Д», если нет точного совпадения между двумя таблицами.
Аргумент «Диапазон поиска» устанавливается на ближайшее значение (введена цифра 1 или поле пусто) и диапазон поиска не сортируется. Для нечетких поисков следует отсортировать крайний левый столбец диапазона поиска.
Сравниваемые данные имеют лишние пробелы (для их удаления можно использовать функцию «ОТРЕЗАТЬ», применяя ее к таблице и к нужному значению), разное форматирование, лишние кавычки. Для одинакового написания значений в обеих таблицах есть смысл использовать выпадающий список.

«ССЫЛКА» — эта ошибка часто возникает, когда номер столбца указан неправильно, если столько столбцов не входит в выбранный диапазон. В таких случаях следует помнить, что число пишется, считая от левого столбца выбранного диапазона, а не таблицы в целом.
«ИМЯ» — часто возвращается ошибка, когда текст введен неправильно в «Значение поиска». Текст должен быть заключен в кавычки.

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