Объяснение функции Excel VLOOKUP

Функция VLOOKUP значительно облегчает работу с Excel. Избавьте себя от необходимости вручную перебирать большой массив данных, а вместо этого используйте формулу VLOOKUP для мгновенного вывода результатов в другую ячейку. Чтобы вы могли в полной мере воспользоваться практической пользой этой функции Excel, мы объясним вам шаг за шагом, как сделать VLOOKUP на простом примере, и предложим идеи для различных вариантов ее использования.

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

Название формулы состоит из «V» для вертикали и функции «lookup», хотя это лишь частично отражает ее практическую область применения. Тем не менее, из названия можно извлечь два важных момента: V указывает направление поиска, поскольку функция начинается с верхней строки и оттуда идет вертикально вниз. Схожей с VLOOKUP является формула HLOOKUP, которая осуществляет поиск по строкам в горизонтальном направлении. Компонент ссылки основан на ссылающемся символе: функция ссылается только на значение другой ячейки. Следовательно, формула VLOOKUP связана с менее обширной функцией REFERENCE.

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

Параметры объясняются более подробно:

• Значение поиска: это значение, которое вы ищете. Вы можете ввести критерий непосредственно в формулу словами, используя кавычки, или ввести конкретную ячейку, содержащую данные.
• Расположение в таблице: здесь указывается область, в которой находится искомое значение. В функциях Excel для разделения символов от до всегда используется двоеточие.
• Номер индекса столбца: обозначает расположение столбца в указанном месте таблицы в числовом значении. Примечание: столбец D не имеет автоматически индексный номер 4. Если начать область данных со столбца B, то D будет иметь индекс столбца под номером 3.
• Совпадение: должны ли результаты быть точными или просто приблизительно равными? При значении «0» или «FALSE» будет показано только точное совпадение. «1» или «TRUE» позволяет ввести другие значения, а затем возвращает следующее наименьшее значение. Ввод этого параметра необязателен: если вы оставите эту часть пустой, то функция «1» будет восприниматься как стандартная.

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

Или

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

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

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

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

Мы хотели бы выяснить, какой альбом находится в инвентарном месте под номером 10. Для этого мы можем использовать функцию VLOOKUP:

Ячейка, в которую мы ввели функцию, теперь должна выдать «Параллельные линии».

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

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

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

Кроме того, для достижения этого результата VLOOKUP можно использовать другую функцию Excel: «ВЫБОР». Это позволяет продемонстрировать, что столбцы VLOOKUP расположены не там, где они находятся на самом деле, позволяя, по сути, изменить расположение таблицы. В функции CHOOSE можно указать различные области и порядок, в котором они будут интерпретироваться:

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

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

Однако эта формула работает, только если вы вводите альбом в качестве значения поиска; для исполнителей или инвентарных номеров формулу придется корректировать заново. Приложив немного усилий, вы также можете создать выпадающее меню, выбрав значение поиска. Затем используйте функцию IF для корректировки расположения таблицы в функции VLOOKUP в зависимости от выбора:

До сих пор мы всегда работали с точным совпадением и поэтому всегда устанавливали параметр «диапазон совпадения» в значение «FALSE». Это объясняется простой причиной: пример работает в основном с текстом. Параметр неточного совпадения работает в основном со значениями в отсортированном списке. Если само значение не представлено, функция использует следующее наименьшее. А у нас есть такой упорядоченный список значений в столбце А. Поэтому если вы используете VLOOKUP для поиска определенного инвентарного номера, который еще не присвоен, функция будет использовать следующий наименьший — в нашем случае число 16.

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

Подробнее об этой формуле можно прочитать в этой статье блога Udemy о множественных значениях с помощью VLOOKUP. Формула является так называемой формулой массива, то есть вы не просто активируете ее клавишей Enter, а сочетанием [Ctrl] + [Shift] + [Enter]. Это также создает фигурные скобки, характерные для формул массива, вокруг вводимых данных.