Комбинируя функцию ВПР Excel с функцией СТОЛБЦ, вы можете создать формулу поиска, которая возвращает несколько значений из одной строки базы данных или таблицы данных. Узнайте, как создать формулу поиска, которая возвращает несколько значений из одной записи данных.
Инструкции в этой статье относятся к Excel 2019, 2016, 2013, 2010; и Excel для Microsoft 365.
Итог
Формула поиска требует, чтобы функция COLUMN была вложена внутрь функции VLOOKUP. Вложение функции предполагает ввод второй функции в качестве одного из аргументов первой функции.
Введите обучающие данные
В этом руководстве функция СТОЛБЦ вводится в качестве аргумента индекса столбца для функции ВПР. Последний шаг в руководстве включает копирование формулы поиска в дополнительные столбцы для получения дополнительных значений для выбранной части.
Первым шагом в этом руководстве является ввод данных в рабочий лист Excel. Чтобы выполнить шаги, описанные в этом руководстве, введите данные, показанные на изображении ниже, в следующие ячейки:
- Введите верхний диапазон данных в ячейки с D1 по G1.
- Введите второй диапазон в ячейки от D4 до G10.
Критерии поиска и формула поиска, созданные в этом руководстве, вводятся во второй строке рабочего листа.
Это руководство не включает базовое форматирование Excel, показанное на изображении, но это не влияет на работу формулы поиска.
Создать именованный диапазон для таблицы данных
Именованный диапазон - это простой способ сослаться на диапазон данных в формуле. Вместо того, чтобы вводить ссылки на ячейки для данных, введите имя диапазона.
Второе преимущество использования именованного диапазона заключается в том, что ссылки на ячейки для этого диапазона никогда не меняются, даже когда формула копируется в другие ячейки на листе. Имена диапазонов являются альтернативой использованию абсолютных ссылок на ячейки для предотвращения ошибок при копировании формул.
Имя диапазона не включает заголовки или имена полей для данных (как показано в строке 4), а только сами данные.
-
Выделите ячейки с D5 по G10 на рабочем листе.
Image -
Поместите курсор в поле имени, расположенное над столбцом A, введите Table, затем нажмите Enter. Ячейки с D5 по G10 имеют имя диапазона Table.
Image - Имя диапазона для аргумента массива таблицы ВПР используется далее в этом руководстве.
Откройте диалоговое окно ВПР
Несмотря на то, что можно ввести формулу поиска непосредственно в ячейку на листе, многим людям трудно сохранить синтаксис прямым, особенно для сложной формулы, такой как та, что используется в этом руководстве.
В качестве альтернативы используйте диалоговое окно «Аргументы функции ВПР». Почти все функции Excel имеют диалоговое окно, в котором каждый из аргументов функции вводится в отдельной строке.
-
Выберите ячейку E2 рабочего листа. Это место, где будут отображаться результаты формулы двумерного поиска.
Image -
На ленте перейдите на вкладку Формулы и выберите Поиск и справочник.
Image -
Выберите ВПР, чтобы открыть диалоговое окно Аргументы функции.
Image - В диалоговом окне «Аргументы функции» вводятся параметры функции ВПР.
Введите аргумент значения поиска
Обычно искомое значение соответствует полю данных в первом столбце таблицы данных. В этом примере значение поиска относится к имени части, информацию о которой вы хотите найти. Допустимые типы данных для значения поиска: текстовые данные, логические значения, числа и ссылки на ячейки.
Абсолютные ссылки на ячейки
При копировании формул в Excel ссылки на ячейки изменяются, чтобы отразить новое местоположение. В этом случае D2, ссылка на ячейку для искомого значения, изменяется и создает ошибки в ячейках F2 и G2.
Абсолютные ссылки на ячейки не изменяются при копировании формул.
Чтобы предотвратить ошибки, преобразуйте ссылку на ячейку D2 в абсолютную ссылку на ячейку. Чтобы создать абсолютную ссылку на ячейку, нажмите клавишу F4. Это добавляет знаки доллара вокруг ссылки на ячейку, например $D$2.
-
В диалоговом окне «Аргументы функции» поместите курсор в текстовое поле lookup_value. Затем на листе выберите cell D2, чтобы добавить ссылку на эту ячейку в lookup_value. В ячейку D2 будет введено название детали.
Image -
Не перемещая точку вставки, нажмите клавишу F4, чтобы преобразовать D2 в абсолютную ссылку на ячейку $D$2.
Image - Оставьте диалоговое окно функции ВПР открытым для следующего шага в руководстве.
Введите аргумент табличного массива
Табличный массив - это таблица данных, в которой формула поиска ищет нужную информацию. Массив таблицы должен содержать не менее двух столбцов данных.
Первый столбец содержит аргумент значения поиска (который был настроен в предыдущем разделе), а второй столбец используется для поиска указанной вами информации по формуле поиска.
Аргумент массива таблицы должен быть введен либо как диапазон, содержащий ссылки на ячейки для таблицы данных, либо как имя диапазона.
Чтобы добавить таблицу данных в функцию ВПР, поместите курсор в текстовое поле table_array в диалоговом окне и введите Tableчтобы ввести имя диапазона для этого аргумента.
Вложить функцию COLUMN
Обычно функция ВПР возвращает данные только из одного столбца таблицы данных. Этот столбец задается аргументом номера индекса столбца. Однако в этом примере есть три столбца, и порядковый номер столбца необходимо изменить без редактирования формулы поиска. Для этого вложите функцию COLUMN в функцию VLOOKUP в качестве аргумента Col_index_num.
При вложении функций Excel не открывает диалоговое окно второй функции для ввода ее аргументов. Функцию COLUMN необходимо вводить вручную. Функция COLUMN имеет только один аргумент, аргумент Reference, который является ссылкой на ячейку.
Функция COLUMN возвращает номер столбца, указанный в качестве аргумента Reference. Он преобразует букву столбца в число.
Чтобы узнать цену товара, используйте данные из столбца 2 таблицы данных. В этом примере столбец B используется в качестве ссылки для вставки 2 в аргумент Col_index_num.
-
В диалоговом окне Аргументы функции поместите курсор в текстовое поле Col_index_num и введите COLUMN(. (Обязательно используйте открытую круглую скобку.)
Image -
На рабочем листе выберите cell B1, чтобы ввести ссылку на эту ячейку в качестве аргумента Reference.
Image - Введите закрывающую круглую скобку, чтобы завершить функцию СТОЛБЦА.
Введите аргумент поиска диапазона ВПР
Аргумент Range_lookup функции VLOOKUP представляет собой логическое значение (ИСТИНА или ЛОЖЬ), указывающее, должна ли функция VLOOKUP находить точное или приблизительное совпадение с Lookup_value.
- TRUE или опущено: ВПР возвращает близкое совпадение с Lookup_value. Если точное совпадение не найдено, функция ВПР возвращает следующее наибольшее значение. Данные в первом столбце Table_array должны быть отсортированы по возрастанию.
- FALSE: ВПР использует точное совпадение с Lookup_value. Если в первом столбце Table_array есть два или более значений, соответствующих искомому значению, используется первое найденное значение. Если точное совпадение не найдено, возвращается ошибка Н/Д.
В этом руководстве будет выполняться поиск конкретной информации о конкретном элементе оборудования, поэтому Range_lookup имеет значение FALSE.
В диалоговом окне «Аргументы функции» поместите курсор в текстовое поле Range_lookup и введите False, чтобы функция ВПР вернула точное совпадение данных.
Выберите OK, чтобы завершить формулу поиска и закрыть диалоговое окно. Ячейка E2 будет содержать ошибку N/A, поскольку критерии поиска не были введены в ячейку D2. Эта ошибка временная. Это будет исправлено, когда критерии поиска будут добавлены на последнем шаге этого руководства.
Скопируйте формулу поиска и введите критерии
Формула поиска извлекает данные из нескольких столбцов таблицы данных одновременно. Для этого формула поиска должна находиться во всех полях, из которых вы хотите получить информацию.
Чтобы получить данные из столбцов 2, 3 и 4 таблицы данных (цена, номер детали и имя поставщика), введите частичное имя в качестве Lookup_value.
Поскольку данные располагаются на рабочем листе в обычном порядке, скопируйте формулу поиска из ячейки E2 в ячейки F2 и G2 По мере копирования формулы Excel обновляет относительную ссылку на ячейку в функции СТОЛБЦ (ячейка B1), чтобы отразить новое местоположение формулы. Excel не изменяет абсолютную ссылку на ячейку (например, $D$2) и именованный диапазон (таблица) при копировании формулы.
Существует несколько способов копирования данных в Excel, но самый простой способ - использовать маркер заполнения.
-
Выберите ячейку E2, где находится формула поиска, чтобы сделать ее активной ячейкой.
Image -
Перетащите маркер заполнения на ячейку G2. В ячейках F2 и G2 отображается ошибка Н/Д, присутствующая в ячейке E2.
Image -
Чтобы использовать формулы поиска для извлечения информации из таблицы данных, на листе выберите ячейку D2, введите Widget и нажмите Введите.
Image В ячейках с E2 по G2 отображается следующая информация.
- E2: $14,76 - цена виджета
- F2: PN-98769 - артикул виджета
- G2: Widgets Inc. - название поставщика виджетов
-
Чтобы проверить формулу массива ВПР, введите имя других частей в ячейку D2 и посмотрите результаты в ячейках от E2 до G2.
Image - Каждая ячейка, содержащая формулу поиска, содержит разные данные об аппаратном элементе, который вы искали.
Функция VLOOKUP с вложенными функциями, такими как COLUMN, предоставляет мощный метод поиска данных внутри таблицы, используя другие данные в качестве ссылки для поиска.
