Функция VLOOKUP Excel используется для поиска и возврата информации из таблицы данных на основе выбранного вами значения поиска.
Как правило, VLOOKUP требует, чтобы значение поиска находилось в крайнем левом столбце таблицы данных, а функция возвращает другое поле данных, расположенное в той же строке справа от этого значения.
Однако, комбинируя VLOOKUP с функцией CHOOSE, можно создать формулу поиска слева, которая:
- Разрешает значение поиска из любого столбца в таблице данных
- Возвращает информацию, расположенную в любом столбце слева от значения поиска
Заметка: Эти инструкции относятся к версиям Excel 2019, 2016, 2013, 2010 и Excel для Office 365.
01 из 05Учебное пособие: использование функций VLOOKUP и CHOOSE в левой форме поиска
Чтобы создать формулу поиска слева, показанную в примере изображения, используйте формулу:
= ВПР ($ D $ 2, ВЫБРАТЬ ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)
В этом примере формула позволяет найти часть, предоставленную различными компаниями, перечисленными в столбце 3 таблицы данных.
Работа функции CHOOSE в формуле заключается в том, чтобы обмануть VLOOKUP, полагая, что столбец 3 является столбцом 1. В результате имя компании можно использовать в качестве значения поиска, чтобы найти название части, предоставленной каждой компанией.
Введите данные учебника
- Введите заголовок поставщик в клетку D1.
- Введите заголовок Часть в ячейке E1.
- Введите таблицу данных, отображаемую на изображении выше, в ячейки От D4 до F9
- Ряды 2 и 3 остаются пустыми для соответствия критериям поиска и формулой поиска слева, созданной во время этого урока
Откройте диалоговое окно VLOOKUP
Хотя можно ввести формулу непосредственно в ячейку F1 на листе, многие люди испытывают трудности с синтаксисом формулы.
В этом случае безопаснее использовать диалоговое окно VLOOKUP. Почти все функции Excel имеют диалоговое окно, которое позволяет вам вводить каждый аргумент функции в отдельной строке.
- Нажмите на ячейку E2 рабочего листа. E2 - это место, где будут отображаться результаты левой формулы поиска.
- Нажми на Формулы вкладку ленты.
- Нажми на Поиск и ссылки в ленте, чтобы открыть раскрывающийся список функции.
- Нажмите на ВПР в списке, чтобы открыть диалоговое окно функции.
Ввод аргументов в диалоговое окно VLOOKUP
Аргументы функции - это значения, используемые функцией для вычисления результата.
В диалоговом окне функции имя каждого аргумента находится в отдельной строке, за которой следует поле ввода значения.
Введите следующие значения для каждого из аргументов VLOOKUP в правильной строке диалогового окна, как показано на сопроводительном изображении.
Значение поиска
Значение поиска - это поле информации, которое используется для поиска в массиве таблиц. VLOOKUP возвращает другое поле данных из той же строки, что и значение поиска.
В этом примере используется ссылка ячейки на место, где название компании будет введено на рабочий лист. Преимущество этого заключается в том, что он упрощает изменение названия компании без редактирования формулы.
- Нажми на искомое_значение в диалоговом окне.
- Нажмите на ячейку D2 добавить эту ссылку на ячейку искомое_значение линия.
- нажмите F4 ключ на клавиатуре, чтобы сделать ссылку на ячейку абсолютной - $ D $ 2.
Абсолютные ссылки на ячейки
Абсолютные ссылки на ячейки используются для аргументов lookup value и table array для предотвращения ошибок, если формула поиска копируется в другие ячейки на листе.
Ввод функции CHOOSE
Аргумент массива таблицы - это блок смежных данных, из которых извлекается конкретная информация.
Как правило, VLOOKUP смотрит только справа от аргумента value для поиска данных в массиве таблиц. Чтобы заставить его смотреть влево, VLOOKUP нужно обмануть, переставив столбцы в массиве таблиц, используя функцию CHOOSE.
В этой формуле функция CHOOSE выполняет две задачи:
- Он создает массив таблиц шириной всего два (столбцы D и F).
- Он изменяет порядок столбцов в массиве таблицы справа налево, чтобы столбец F был первым, а столбец D второй.
Ввод функций
При вводе функций вручную каждый из аргументов функции должен быть разделен запятой.
- В диалоговом окне функции VLOOKUP нажмите таблица_массив линия.
- Введите следующее ВЫБИРАТЬ функция:ВЫБРАТЬ ({1,2}, $ F: $ F, $ D: $ D)
Индекс номера столбца
Обычно индекс индекса столбца указывает, какой столбец массива таблицы содержит данные, которые вы используете. Однако в этой формуле он относится к порядку столбцов, заданных функцией CHOOSE.
Функция CHOOSE создает массив таблиц шириной в два столбца, сначала столбец F, а затем столбец D. Поскольку искомая информация - название части - находится в столбце D, значение аргумента индекса столбца должно быть установлено равным 2.
- Нажми на Col_Index_Num в диалоговом окне.
- Наберите "А 2 в этой строке.
Поиск диапазона
Аргумент Range_lookup VLOOKUP - это логическое значение (только TRUE или FALSE), которое указывает, хотите ли вы, чтобы VLOOKUP находил точное или приблизительное соответствие для значения поиска.
- Если TRUE или если этот аргумент опущен, VLOOKUP возвращает либо точное соответствие Lookup_value, либо, если точное совпадение не найдено, VLOOKUP возвращает следующее наибольшее значение. Для этой формулы данные в первом столбце Table_array должны быть отсортированы в порядке возрастания.
- Если FALSE, VLOOKUP использует точное совпадение с Lookup_value. Если в первом столбце таблицы Table_array есть два или более значения, которые соответствуют значению поиска, используется первое найденное значение. Если точное совпадение не найдено, возвращается ошибка # N / A.
В этом уроке, поскольку мы ищем конкретное имя части, Range_lookup настроен на Ложь так что формула возвращает только точные совпадения.
- Нажми на диапазон_просмотра в диалоговом окне.
- Введите слово Ложь в этой строке, чтобы указать, что мы хотим, чтобы VLOOKUP возвращал точное соответствие для данных, которые мы ищем.
- Нажмите Хорошо для завершения формулы поиска слева и закрытия диалогового окна.
- Поскольку мы еще не ввели название компании в ячейку D2, в ячейке E2 появляется ошибка # N / A.
Возвращение данных с помощью формулы поиска слева
Чтобы найти, какие компании поставляют части, введите название компании в ячейку D2 и нажмите ВОЙТИ на клавиатуре.
Название детали отображается в ячейке E2.
- Нажмите на ячейку D2 в вашем рабочем листе.
- Тип Гаджеты Плюс в клетку D2 и нажмите ВОЙТИ на клавиатуре.
- Текст «Гаджеты» - часть, предоставленная компанией Gadgets Plus, - должна отображаться в ячейке E2.
Еще раз проверьте формулу поиска, набрав другие имена компаний в ячейку D2, и соответствующее имя части должно появиться в ячейке E2.
Если в ячейке E2 появится сообщение об ошибке, такое как # N / A, проверьте наличие орфографических ошибок в ячейке D2.
04 из 05Создание массива таблицы из двух столбцов
Синтаксис функции CHOOSE:
= CHOOSE (Index_number, Value1, Value2, … Value254)
Функция CHOOSE обычно возвращает одно значение из списка значений (Value1 - Value254) на основе введенного номера индекса.
Если номер индекса равен 1, функция возвращает Value1 из списка; если номер индекса равен 2, функция возвращает Value2 из списка и так далее.
Когда вводится несколько номеров индексов, функция возвращает несколько значений в любом порядке. Получение CHOOSE для возврата нескольких значений осуществляется путем создания массива.
Чтобы ввести массив, объедините числа, введенные в фигурные скобки или скобки. Для номера индекса вводятся два номера:{ 1,2 }.
Следует отметить, что CHOOSE не ограничивается созданием таблицы с двумя столбцами. Включив в массив дополнительный номер, например {1,2,3}, и дополнительный диапазон в аргументе value, CHOOSE создает таблицу с тремя столбцами.
Дополнительные столбцы позволяют возвращать различную информацию с помощью формулы поиска слева, изменяя аргумент номера столбца столбца VLOOKUP на номер столбца, содержащего требуемую информацию.
05 из 05Изменение порядка столбцов с помощью функции ВЫБОР
В функции CHOOSE, используемой в этой формуле:
ВЫБРАТЬ ({1,2}, $ F: $ F, $ D: $ D)
диапазон для столбца F указан перед столбцом D.
Поскольку функция CHOOSE устанавливает массив таблиц VLOOKUP (источник данных для этой функции), то переключение порядка столбцов в функции CHOOSE передается вместе с VLOOKUP.
Что касается VLOOKUP, массив таблиц имеет ширину всего два столбца, а столбец F слева и столбец D справа. Поскольку столбец F содержит имя компании, которую мы хотим найти, а так как столбец D содержит названия деталей, VLOOKUP может выполнять свои регулярные функции поиска при поиске данных, расположенных слева от значения поиска.
В результате VLOOKUP может использовать название компании, чтобы найти часть, которую они поставляют.
