Быстрый поиск нескольких полей данных с помощью функции Excel VLOOKUP

Создайте формулу поиска, которая возвращает несколько значений из записи данных.

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

Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; и Excel для Office 365.

Возврат нескольких значений в Excel VLOOKUP

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

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

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

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

  • Введите верхний диапазон данных в ячейки от D1 до G1.
  • Введите второй диапазон в ячейки от D4 до G10.

Критерии поиска и формула поиска, созданные в этом учебном пособии, вводятся в строку 2 рабочего листа.

Это руководство не включает базовое форматирование Excel, показанное на изображении, но это не влияет на работу формулы поиска.

Создайте именованный диапазон для таблицы данных

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

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

Имя диапазона не включает заголовки или имена полей для данных (как показано в строке 4), только данные.

  1. Выделите ячейки от D5 до G10 на листе.

  2. Поместите курсор в поле имени, расположенное над столбцом A, введите Таблица , и нажмите Ввод . Ячейки от D5 до G10 имеют диапазон имен таблиц.

  3. Имя диапазона для аргумента массива таблицы VLOOKUP будет использовано позже в этом руководстве.

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

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

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

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

  2. На ленте перейдите на вкладку Формулы и выберите Поиск и справка .

  3. Выберите VLOOKUP , чтобы открыть диалоговое окно Аргументы функций .

  4. Диалоговое окно «Аргументы функции» – это то, где вводятся параметры функции VLOOKUP.

Введите аргумент значения поиска

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

Абсолютные ссылки на ячейки

Когда формулы копируются в Excel, ссылки на ячейки меняются, отражая новое местоположение. Если это происходит, D2, ссылка на ячейку для поискового значения , изменяется и создает ошибки в ячейках F2 и G2.

Абсолютные ссылки на ячейки не изменяются при копировании формул.

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

  1. В диалоговом окне «Аргументы функции» поместите курсор в текстовое поле lookup_value .Затем на листе выберите ячейка D2 , чтобы добавить эту ссылку на ячейку в lookup_value . В ячейке D2 будет указано имя детали.

  2. Не перемещая точку вставки, нажмите клавишу F4 , чтобы преобразовать D2 в абсолютную ссылку на ячейку $ D $ 2.

  3. Оставьте открытым диалоговое окно функции VLOOKUP для следующего шага в учебнике.

Введите аргумент массива таблицы

Массив таблиц – это таблица данных, в которой формула поиска ищет нужную информацию. Массив таблицы должен содержать как минимум два столбца данных.

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

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

Чтобы добавить таблицу данных в функцию VLOOKUP, поместите курсор в текстовое поле table_array в диалоговом окне и введите Таблица , чтобы ввести имя диапазона для этого аргумента.

Гнездо Колонна Функция

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

При вложении функций Excel не открывает диалоговое окно второй функции для ввода ее аргументов. Функция COLUMN должна быть введена вручную. Функция COLUMN имеет только один аргумент – аргумент Reference, который является ссылкой на ячейку.

Функция COLUMN возвращает номер столбца, предоставленного в качестве аргумента Reference. Он преобразует букву столбца в число.

Чтобы узнать цену товара, используйте данные в столбце 2 таблицы данных. В этом примере столбец B используется в качестве ссылки для вставки 2 в аргумент Col_index_num.

  1. В диалоговом окне Аргументы функций поместите курсор в текстовое поле Col_index_num и введите COLUMN (. (Обязательно добавьте открытую круглую скобку) .)

  2. На листе выберите ячейка B1 , чтобы ввести ссылку на эту ячейку в качестве аргумента «Ссылка».

  3. Введите закрывающую круглую скобку , чтобы завершить функцию COLUMN.

Введите аргумент поиска диапазона VLOOKUP

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

  • ИСТИНА или опущено : VLOOKUP возвращает близкое совпадение со значением Lookup_value. Если точное совпадение не найдено, VLOOKUP возвращает следующее наибольшее значение. Данные в первом столбце Table_array должны быть отсортированы в порядке возрастания.
  • FALSE : VLOOKUP использует точное соответствие значению Lookup_value. Если в первом столбце Table_array есть два или более значений, соответствующих значению поиска, используется первое найденное значение. Если точное совпадение не найдено, возвращается ошибка # N/A.

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

В диалоговом окне «Аргументы функции» поместите курсор в текстовое поле Range_lookup и введите False , чтобы VLOOKUP вернул точное совпадение для данных.

Выберите ОК , чтобы завершить формулу поиска и закрыть диалоговое окно. Ячейка E2 будет содержать ошибку # N/A, поскольку критерии поиска не введены в ячейку D2. Эта ошибка временная. Это будет исправлено, когда критерии поиска будут добавлены на последнем шаге этого урока.

Скопируйте формулу поиска и введите критерии

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

Чтобы извлечь данные из столбцов 2, 3 и 4 таблицы данных (цена, номер детали и имя поставщика), введите частичное имя в качестве Lookup_value.

Поскольку данные располагаются в виде регулярного шаблона на листе, скопируйте формулу поиска в ячейке E2 в ячейки F2 и G2 . По мере копирования формулы Excel обновляет относительную ссылку на ячейку в функции COLUMN (ячейка B1), чтобы отразить новое местоположение формулы.Excel не изменяет абсолютную ссылку на ячейку (например, $ D $ 2) и именованный диапазон (Таблица) при копировании формулы.

Существует несколько способов копирования данных в Excel, но самый простой способ – использовать ручку заполнения.

  1. Выберите ячейка E2 , где находится формула поиска, чтобы сделать ее активной ячейкой.

  2. Перетащите маркер заполнения через ячейку G2 . Ячейки F2 и G2 отображают ошибку # N/A, которая присутствует в ячейке E2.

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

    Следующая информация отображается в ячейках от E2 до G2.

    • E2: 14,76 $ – цена виджета
    • F2: PN-98769 – номер детали для виджета
    • G2: Widgets Inc. – название поставщика виджетов
  4. Чтобы проверить формулу массива VLOOKUP, введите имя других частей в ячейку D2 и просмотрите результаты в ячейках E2-G2.

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

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

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