Как пользоваться VLOOKUP Excel? Функция ВПР в Excel для "чайников" и не только

Любой человек, имеющий дело с цифрами, - для профессионального ли интереса либо просто из любви к искусству, - конечно, первым делом освоит калькулятор. Что дальше? На очереди гениальная по своей сути программа для работы с числами - Excel. Знать эту программу в совершенстве, равно как и знать в совершенстве математику, наверное, невозможно. Однако существуют базовые возможности в программе, функции "Эксель", зная которые с уверенностью на 90 % можно говорить об умении работать с данными на уровне уверенного пользователя. Одна из таких наиважнейших функций в Excel - VLOOKUP, она очень многогранна. Как пользоваться VLOOKUP в Excel (для чайников) рассматривается в статье. Конечно, восприятие теории не принесет полного понимания процесса использования функции. Необходима практика сначала на небольших массивах данных, затем на таблицах любых размеров.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Сначала необходимо правильно прописать формулу.

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

Это пример того, как работает VLOOKUP в Excel.

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

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

• "Н/Д" - самый распространенный тип ошибки. Может возникнуть по нескольким причинам.

1. Столбец, по которому функция ищет совпадение, неправильно расположен (он должен быть крайним левым). Если возникла ситуация, при которой искомое значение левее зоны поиска совпадения, таблицу необходимо преобразовать. Например, скопировать нужный столбец и вставить его правее зоны поиска.
2. Ошибка "Н/Д" может возвращаться, если не закреплен диапазон поиска, при протягивании формулы ВПР.
3. Если с помощью аргумента "Интервальный просмотр" задан точный поиск (проставлена цифра 0), ошибка "Н/Д" возвращается, если в двух таблицах точного совпадения нет.
4. Аргумент "Интервальный просмотр" задан ближайшим значением (проставлена цифра 1, либо поле не заполнено), а диапазон, по которому проходит поиск, не отсортирован. При неточном поиске обязательно нужно сортировать крайний левый столбец диапазона поиска.
5. Сравниваемые данные имеют лишние пробелы (для того чтобы их убрать, можно воспользоваться функцией "СЖПРОБЕЛЫ", применив ее к таблице и к искомому значению), разный формат, лишние кавычки. Для одинакового написания значений в обеих таблицах имеет смысл воспользоваться выпадающим списком.

• "ССЫЛКА" - данная ошибка часто возникает при неправильном указании номера столбца, если столько столбцов нет в выбранном диапазоне. В таких случаях необходимо помнить, что номер проставляют, считая с левого столбца выделенного диапазона, а не таблицы в целом.
• "ИМЯ" - ошибка возвращается часто при неправильном занесении текста в "Искомое значение". Текст необходимо прописывать в кавычках.

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