Объединив в Excel функция ВПР с функцией COLUMN вы можете создать формулу подстановки , которая возвращает несколько значений из одной строки в базе данных или таблицы данных. Узнайте, как создать формулу поиска, которая возвращает несколько значений из одной записи данных.
Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; и Excel для Office 365.
Возврат нескольких значений в Excel VLOOKUP
Формула поиска требует, чтобы функция COLUMN была вложена в VLOOKUP. Вложение функции включает в себя ввод второй функции в качестве одного из аргументов для первой функции.
Введите учебные данные
В этом руководстве функция COLUMN вводится в качестве аргумента номера индекса столбца для VLOOKUP. Последний шаг в руководстве включает копирование формулы поиска в дополнительные столбцы для получения дополнительных значений для выбранной детали.
Первый шаг в этом учебнике — ввести данные в таблицу Excel . Чтобы выполнить действия, описанные в этом руководстве, введите данные, показанные на рисунке ниже, в следующие ячейки :
- Введите верхний диапазон данных в ячейки от D1 до G1.
- Введите второй диапазон в ячейки от D4 до G10.
Критерии поиска и формула поиска, созданные в этом учебном пособии, вводятся в строку 2 рабочего листа.
Это руководство не включает базовое форматирование Excel, показанное на изображении, но это не влияет на работу формулы поиска.
Создайте именованный диапазон для таблицы данных
Именованный диапазон является простым способом , чтобы обратиться к диапазону данных в формуле. Вместо того, чтобы вводить ссылки на ячейки для данных, введите имя диапазона.
Второе преимущество использования именованного диапазона состоит в том, что ссылки на ячейки для этого диапазона никогда не меняются, даже если формула копируется в другие ячейки на листе. Имена диапазонов являются альтернативой использованию абсолютных ссылок на ячейки для предотвращения ошибок при копировании формул.
Имя диапазона не включает заголовки или имена полей для данных (как показано в строке 4), только данные.
-
Выделите ячейки от D5 до G10 на листе.
-
Поместите курсор в поле имени, расположенное над столбцом A, введите Table , затем нажмите Enter . Ячейки от D5 до G10 имеют диапазон имен таблицы.
-
Имя диапазона для аргумента массива таблицы VLOOKUP будет использовано позже в этом руководстве.
Откройте диалоговое окно VLOOKUP
Хотя формулу поиска можно ввести непосредственно в ячейку на рабочем листе, многим людям сложно сохранить синтаксис прямым, особенно для такой сложной формулы, как та, которая использовалась в этом руководстве.
В качестве альтернативы используйте диалоговое окно VLOOKUP Function Arguments . Почти все Ехсе функции имеют диалоговое окно , в котором каждый из аргументов функции вводится на отдельной строке.
-
Выбор ячейки E2 из листа . Это место, где будут отображаться результаты двумерной формулы поиска.
-
На ленте перейдите на вкладку Формулы и выберите « Поиск и справка» .
-
Выберите VLOOKUP, чтобы открыть диалоговое окно Аргументы функций .
-
В диалоговом окне «Аргументы функции» находятся параметры функции VLOOKUP.
Введите аргумент «Значение поиска»
Обычно значение поиска соответствует полю данных в первом столбце таблицы данных. В этом примере значение поиска относится к имени части, информацию о которой вы хотите найти. Допустимые типы данных для значения поиска — это текстовые данные, логические значения, числа и ссылки на ячейки.
Абсолютные ссылки на ячейки
Когда формулы копируются в Excel, ссылки на ячейки меняются, отражая новое местоположение. Если это произойдет, D2, ссылка на ячейку для значения поиска , изменится и создаст ошибки в ячейках F2 и G2.
Абсолютные ссылки на ячейки не изменяются при копировании формул.
Чтобы избежать ошибок, преобразуйте ссылку на ячейку D2 в абсолютную ссылку на ячейку. Чтобы создать абсолютную ссылку на ячейку, нажмите клавишу F4. Это добавляет знаки доллара вокруг ссылки на ячейку, такие как $ D $ 2.
-
В диалоговом окне « Аргументы функции » поместите курсор в текстовое поле lookup_value . Затем на листе выберите ячейку D2, чтобы добавить эту ссылку на ячейку в lookup_value . В ячейке D2 будет указано имя детали.
-
Не перемещая точку вставки, нажмите клавишу F4, чтобы преобразовать D2 в абсолютную ссылку на ячейку $ D $ 2.
-
Оставьте открытым диалоговое окно функции VLOOKUP для следующего шага в учебнике.
Введите аргумент массива таблицы
Массив таблиц — это таблица данных, в которой формула поиска ищет нужную информацию. Массив таблицы должен содержать как минимум два столбца данных.
Первый столбец содержит аргумент значения поиска (который был задан в предыдущем разделе), а второй столбец ищется по формуле поиска, чтобы найти указанную вами информацию.
Аргумент массива таблицы должен быть введен либо как диапазон, содержащий ссылки на ячейки для таблицы данных, либо как имя диапазона.
Чтобы добавить таблицу данных в функцию VLOOKUP , поместите курсор в текстовое поле table_array в диалоговом окне и введите Table, чтобы ввести имя диапазона для этого аргумента.
Гнездо Колонна Функция
Обычно VLOOKUP возвращает данные только из одного столбца таблицы данных. Этот столбец устанавливается аргументом номера индекса столбца. В этом примере, однако, есть три столбца, и номер индекса столбца необходимо изменить без редактирования формулы поиска. Для этого вложите функцию COLUMN в функцию VLOOKUP в качестве аргумента Col_index_num.
При вложении функций Excel не открывает диалоговое окно второй функции для ввода ее аргументов. Функция COLUMN должна быть введена вручную. Функция COLUMN имеет только один аргумент — аргумент Reference, который является ссылкой на ячейку.
Функция COLUMN возвращает номер столбца, предоставленного в качестве аргумента Reference. Он преобразует букву столбца в число.
Чтобы узнать цену товара, используйте данные в столбце 2 таблицы данных. В этом примере столбец B используется в качестве ссылки для вставки 2 в аргумент Col_index_num.
-
В диалоговом окне « Аргументы функции » поместите курсор в текстовое поле Col_index_num и введите « COLUMN» ( . (Обязательно включите открытую круглую скобку.)
-
На листе выберите ячейку B1, чтобы ввести ссылку на эту ячейку в качестве аргумента Reference.
-
Введите закрывающую круглую скобку, чтобы завершить функцию 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 о точном соответствии данных.
Нажмите OK, чтобы завершить формулу поиска и закрыть диалоговое окно. Ячейка E2 будет содержать ошибку # N / A, поскольку критерии поиска не введены в ячейку D2. Эта ошибка временная. Это будет исправлено, когда критерии поиска будут добавлены на последнем шаге этого урока.
Скопируйте формулу поиска и введите критерии
Формула поиска извлекает данные из нескольких столбцов таблицы данных одновременно. Для этого формула поиска должна находиться во всех полях, из которых вы хотите получить информацию.
Чтобы извлечь данные из столбцов 2, 3 и 4 таблицы данных (цена, номер детали и название поставщика), введите частичное имя в качестве Lookup_value.
Поскольку данные располагаются в виде регулярного шаблона на листе, скопируйте формулу поиска в ячейке E2 в ячейки F2 и G2 . Когда формула копируется, Excel обновляет относительную ссылку на ячейку в функции COLUMN (ячейка B1), чтобы отразить новое местоположение формулы. Excel не изменяет абсолютную ссылку на ячейку (например, $ D $ 2) и именованный диапазон (таблица) при копировании формулы.
Существует несколько способов копирования данных в Excel, но самый простой способ — использовать ручку заполнения .
-
Выберите ячейку E2 , где находится формула поиска, чтобы сделать ее активной ячейкой.
-
Перетащите маркер заполнения через ячейку G2 . Ячейки F2 и G2 отображают ошибку # N / A, которая присутствует в ячейке E2.
-
Чтобы использовать формулы поиска для извлечения информации из таблицы данных, выберите на листе ячейку D2 , введите виджет и нажмите клавишу Enter .
Следующая информация отображается в ячейках от E2 до G2.
- E2: $ 14,76 — цена виджета
- F2: PN-98769 — номер детали для виджета
- G2: Widgets Inc. — название поставщика для виджетов
-
Чтобы проверить формулу массива VLOOKUP, введите имя других частей в ячейку D2 и просмотрите результаты в ячейках E2-G2.
-
Каждая ячейка, содержащая формулу поиска, содержит различные данные об искомом элементе оборудования.
Функция VLOOKUP с вложенными функциями, такими как COLUMN, предоставляет мощный метод для поиска данных в таблице, используя другие данные в качестве справочной информации.