Как создать формулу левого поиска Excel с помощью VLOOKUP

Объедините VLOOKUP и CHOOSE в Excel, чтобы создать формулу поиска слева

Функция Excel VLOOKUP используется для поиска и возврата информации из таблицы данных на основе выбранного вами значения поиска.

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

Однако, комбинируя VLOOKUP с функцией CHOOSE, можно создать левую формулу поиска, которая:

  • Позволяет искать значение из любого столбца в таблице данных
  • Возвращает информацию, расположенную в любом столбце слева от значения поиска

Эти инструкции относятся к версиям Excel 2019, 2016, 2013, 2010 и Excel для Office 365.

Учебник. Использование функций VLOOKUP и CHOOSE в формуле левого поиска

Чтобы создать формулу поиска слева, показанную на изображении примера, используйте формулу:

= ВПР ($ D $ 2, ВЫБРАТЬ ({1,2}, $ F: $ F, $ D: $ D), 2, FALSE)

В этом примере формула позволяет найти детали, поставляемые различными компаниями, перечисленными в столбце 3 таблицы данных.

Задача функции CHOOSE в формуле состоит в том, чтобы обмануть VLOOKUP, полагая, что столбец 3 является столбцом 1. В результате имя компании можно использовать в качестве значения поиска, чтобы найти имя детали, поставляемой каждой компанией.

Введите учебные данные

  1. Введите заголовок Поставщик в ячейку D1 .
  2. Введите заголовок Деталь в ячейку E1 .
  3. Введите таблицу данных, показанную на рисунке выше, в ячейки от D4 до F9
  4. Строки 2 и 3 оставлены пустыми для соответствия критериям поиска и левой формуле поиска, созданной во время этого урока.

Откройте диалоговое окно VLOOKUP

Хотя формулу можно ввести непосредственно в ячейку F1 на листе, многие люди испытывают трудности с синтаксисом формулы.

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

  1. Нажмите на ячейку E2 рабочего листа. E2 – это место, где будут отображаться результаты левой формулы поиска.
  2. Нажмите на вкладку Формулы на ленте.
  3. Нажмите на ссылку Поиск и справка на ленте, чтобы открыть раскрывающийся список функций.
  4. Нажмите VLOOKUP в списке, чтобы открыть диалоговое окно функции.

Ввод аргументов в диалоговое окно VLOOKUP

Аргументы функции – это значения, используемые функцией для вычисления результата.

В диалоговом окне функции имя каждого аргумента находится на отдельной строке, за которой следует поле для ввода значения.

Введите следующие значения для каждого из аргументов VLOOKUP в правильной строке диалогового окна, как показано на прилагаемом изображении.

Значение поиска

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

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

  1. Нажмите на строку lookup_value в диалоговом окне.
  2. Нажмите на ячейку D2 , чтобы добавить ссылку на эту ячейку в строку lookup_value .
  3. Нажмите клавишу F4 на клавиатуре, чтобы сделать ссылку на ячейку абсолютной – $ D $ 2.

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

Ввод функции ВЫБРАТЬ

Аргумент массива таблицы – это блок непрерывных данных, из которых извлекается конкретная информация.

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

В этой формуле функция CHOOSE выполняет две задачи:

  1. Он создает массив таблиц шириной всего в два столбца (столбцы D и F).
  2. Он изменяет порядок столбцов в массиве таблиц справа налево, поэтому столбец F стоит первым, а столбец D – вторым.

Ввод функций

При вводе функций вручную каждый из аргументов функции должен быть разделен запятой.

  1. В диалоговом окне функции VLOOKUP нажмите на строку Таблица_array .
  2. Введите следующую функцию ВЫБРАТЬ : ВЫБРАТЬ ({1,2}, $ F: $ F, $ D: $ D)

Номер столбца

Обычно порядковый номер столбца указывает, в каком столбце массива таблицы содержатся данные, к которым вы стремитесь. Однако в этой формуле это относится к порядку столбцов, установленному функцией CHOOSE.

Функция CHOOSE создает массив таблиц шириной в два столбца, за которым сначала следует столбец F, а затем столбец D. Поскольку искомая информация – имя детали – находится в столбце D, значение аргумента индекса столбца должно быть равно 2.

  1. Нажмите на строку Col_index_num в диалоговом окне.
  2. Введите 2 в этой строке.

Диапазон поиска

Аргумент Range_lookup в VLOOKUP – это логическое значение (только TRUE или FALSE), которое указывает, хотите ли вы, чтобы VLOOKUP нашел точное или приблизительное совпадение со значением поиска.

  • Если TRUE или этот аргумент пропущен, VLOOKUP возвращает либо точное совпадение с Lookup_value, либо, если точное совпадение не найдено, VLOOKUP возвращает следующее наибольшее значение. Чтобы формула сделала это, данные в первом столбце Table_array должны быть отсортированы в порядке возрастания.
  • Если FALSE, VLOOKUP использует только точное совпадение с Lookup_value. Если в первом столбце Table_array есть два или более значений, соответствующих значению поиска, используется первое найденное значение. Если точное совпадение не найдено, возвращается ошибка # N/A.

В этом уроке, поскольку мы ищем определенное имя детали, Range_lookup устанавливается на False , так что по формуле возвращаются только точные совпадения.

  1. Нажмите на строку Range_lookup в диалоговом окне.
  2. Введите слово False в этой строке, чтобы указать, что мы хотим, чтобы VLOOKUP возвращал точное соответствие для данных, которые мы ищем.
  3. Нажмите ОК , чтобы заполнить формулу поиска слева и закрыть диалоговое окно.
  4. Поскольку мы еще не ввели название компании в ячейку D2, в ячейке E2 появляется ошибка # N/A.

Возврат данных с левой формулой поиска

Чтобы узнать, какие компании поставляют какие детали, введите название компании в ячейку D2 и нажмите клавишу ENTER на клавиатуре.

Название детали отображается в ячейке E2.

  1. Нажмите на ячейку D2 на рабочем листе.
  2. Введите Gadgets Plus в ячейку D2 и нажмите клавишу ENTER на клавиатуре.
  3. Текст «Гаджеты» – часть, поставляемая компанией Gadgets Plus – должен отображаться в ячейке E2.

Далее проверьте формулу поиска, введя другие названия компаний в ячейку D2, и соответствующее имя детали должно появиться в ячейке E2.

Если в ячейке E2 появляется сообщение об ошибке, например # N/A, проверьте наличие орфографических ошибок в ячейке D2.

Создание двухколоночного массива таблиц

Синтаксис для функции CHOOSE:

= CHOOSE (индекс_значение, значение1, значение2, … значение254)

Функция CHOOSE обычно возвращает одно значение из списка значений (от Value1 до Value254) на основе введенного номера индекса.

Если порядковый номер равен 1, функция возвращает Value1 из списка; если индекс равен 2, функция возвращает Value2 из списка и так далее.

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

Чтобы ввести массив, заключите числа, введенные в фигурные скобки или скобки. Для номера индекса вводятся два числа: {1,2} .

Следует отметить, что CHOOSE не ограничивается созданием таблицы из двух столбцов. Включая в массив дополнительный номер, например {1,2,3}, и дополнительный диапазон в аргументе значения, CHOOSE создает таблицу из трех столбцов.

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

Изменение порядка столбцов с помощью функции ВЫБРАТЬ

В функции CHOOSE, используемой в этой формуле:

ВЫБРАТЬ ({1,2}, $ F: $ F, $ D: $ D)

диапазон для столбца F указан перед столбцом D.

Поскольку функция CHOOSE устанавливает массив таблиц VLOOKUP (источник данных для этой функции), переключение порядка столбцов в функции CHOOSE передается в VLOOKUP.

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

В результате VLOOKUP может использовать название компании, чтобы найти часть, которую они поставляют.

Оцените статью
Solutics.ru
Добавить комментарий