Объединив ExcelВПР функции сКОЛОНКА мы можем создать формулу поиска, которая позволяет вам возвращать несколько значений из одной строки базы данных или таблицы данных.
01 из 10Возвращать несколько значений с помощью Excel VLOOKUP
Следуя приведенным ниже шагам, создайте формулу поиска, показанную на изображении выше, которая вернет несколько значений из одной записи данных.
Формула поиска требует, чтобы КОЛОНКА функция вложена внутри ВПР, Вложение функции включает ввод второй функции в качестве одного из аргументов для первой функции.
В этом уроке КОЛОНКА функция будет введена как индекс столбца число аргумент для ВПР, Последний шаг в учебнике включает копирование формулы поиска в дополнительные столбцы, чтобы получить дополнительные значения для выбранной части.
Введите данные учебника
Первым шагом в учебнике является ввод данных в рабочий лист Excel. Чтобы следовать инструкциям в учебнике, введите данные, показанные на изображении выше, в следующие ячейки:
- Введите верхний диапазон данных в ячейки D1 в G1.
- Введите второй диапазон в ячейки D4 в G10.
Критерии поиска и формула поиска, созданные во время этого учебника, будут введены в строка 2 рабочего листа.
Учебник не включает форматирование, видимое на изображении, но это не повлияет на работу формулы поиска. Информация о параметрах форматирования, аналогичная приведенной выше, доступна в этом Основном учебнике по форматированию Excel.
03 из 10Создание именованного диапазона для таблицы данных
Именованный диапазон - это простой способ ссылаться на диапазон данных в формуле. Вместо того, чтобы вводить ссылки на ячейки для данных, вы можете просто ввести имя диапазона.
Второе преимущество использования именованного диапазона состоит в том, что ссылки на ячейки для этого диапазона никогда не изменяются, даже когда формула копируется в другие ячейки на листе. Поэтому имена диапазонов являются альтернативой использованию абсолютных ссылок на ячейки для предотвращения ошибок при копировании формул.
Название диапазона не включает заголовки или имена полей для данных (строка 4), а только сами данные.
- основной момент ячейки D5 в G10 на листе, чтобы выбрать их.
- Нажми на Поле имени расположенный выше столбец А.
- Тип Таблица в Поле имени.
- нажмите Войти на клавиатуре.
- Ячейки D5 в G10 теперь имеют название диапазона Таблица, Мы будем использовать имя для ВПР таблица массива аргумент позже в учебнике.
Открытие диалогового окна VLOOKUP
Хотя можно просто набирать нашу формулу поиска непосредственно в ячейке на листе, многим людям трудно поддерживать синтаксис прямо - особенно для сложной формулы, такой как тот, который мы используем в этом учебнике.
Альтернативой, в данном случае, является использование ВПР диалоговое окно. Почти все функции Excel имеют диалоговое окно, которое позволяет вам вводить каждый аргумент функции в отдельной строке.
Шаги для обучения
- Нажмите на ячейка E2 рабочего листа - место, где будут отображаться результаты двумерной формулы поиска.
- Нажми на Вкладка «Формулы» из лента.
- Нажми на Поиск и ссылки в лента для открытия раскрывающегося списка.
- Нажмите на ВПР в списке, чтобы открыть диалоговое окно функции.
Ввод аргумента значения поиска
Как правило, уважать значение совпадает с полем данных в первом столбце таблицы данных. В нашем примере уважать значение относится к названию аппаратной части, о которой мы хотим найти информацию. Допустимые типы данных для уважать значение это текстовые данные, логические значения, числа и ссылки на ячейки.
Абсолютные ссылки на ячейки
Обычно, когда формулы копируются в Excel, ссылки на ячейки меняются, чтобы отразить их новое местоположение. Если это произойдет, D2, ссылка на ячейку для уважать значение, изменится по мере копирования формулы, создавая ошибки в ячейки F2 а также G2.
Абсолютные ссылки на ячейки не изменяются при копировании формул.
Чтобы предотвратить ошибки, мы преобразуем ссылку на ячейку D2 в абсолютную ссылку на ячейку. Абсолютные ссылки на ячейки создаются нажатием F4 на клавиатуре. Это добавляет знаки доллара вокруг ссылки на ячейку, такие как $ D $ 2.
- Нажми на искомое_значение в диалоговом окне.
- Нажмите на клетка D2 добавить эту ссылку на ячейку искомое_значение line - это ячейка, в которой мы будем набирать имя части, о которой мы ищем информацию
- Не перемещая точку вставки, нажмите F4 ключ на клавиатуре для преобразования D2 в абсолютную ячейку $ D $ 2.
- Оставь ВПР откроется диалоговое окно функции для следующего шага в учебнике.
Ввод аргумента массива таблицы
Массив таблицы - это таблица данных, которую ищет формула поиска, чтобы найти нужную нам информацию.Массив таблицы должен содержать не менее двух столбцов данных.
Первый столбец содержит аргумент значения поиска (предыдущий шаг в учебнике), а второй столбец будет искать по формуле поиска, чтобы найти указанную информацию.
Аргумент массива таблицы должен быть введен как диапазон, содержащий ссылки на ячейки для таблицы данных или как имя диапазона.
- Нажми на таблица_массив в диалоговом окне.
- Тип Таблица для ввода имени диапазона для этого аргумента.
- Оставь ВПР откроется диалоговое окно функции для следующего шага в учебнике.
Вложение функции COLUMN
Обычно ВПР возвращает только данные из одного столбца таблицы данных, и этот столбец устанавливается номер столбца аргумент.
Однако в этом примере у нас есть три столбца, из которых мы хотим вернуть данные, поэтому нам нужен способ легко изменить номер столбца без редактирования нашей формулы поиска - вот где КОЛОНКА функция входит.
Введя его как номер столбца аргумент, он изменится по мере копирования формулы поиска из ячейка D2 в ячейки E2 а также F2 позже в учебнике.
Функции вложенности
КОЛОНКА следовательно, действует как ВПР«s номер столбца аргумент; это достигается путем вложения КОЛОНКА функции внутри ВПР в Col_Index_Num строки диалогового окна.
Ввод функции COLUMN вручную
Когда функции вложенности, Excel не позволяет нам открыть диалоговое окно второй функции, чтобы ввести свои аргументы. КОЛОНКА поэтому, необходимо ввести вручную в Col_Index_Num линия. КОЛОНКА функция имеет только один аргумент - Ссылка аргумент, который является ссылкой на ячейку.
Выбор аргумента функции функции COLUMN
КОЛОНКА функция функции - вернуть номер столбца, заданного как Ссылка аргумент. Другими словами, он преобразует буквенное обозначение столбца в число с столбец А являясь первой колонкой, колонка B второй и т. д.
Начиная с первого поля данных, мы хотим вернуть цену элемента, который находится в столбце 2 таблицы данных. Мы можем выбрать ссылку на ячейку для любой ячейки в колонка B как Ссылка Аргумент, чтобы получить номер 2 для Col_Index_Num аргумент.
- в ВПР , нажмите на Col_Index_Num линия.
- Введите имя функции колонка а затем открытый круглый кронштейн.
- Нажмите на клетка B1 на листе, чтобы ввести эту ссылку на ячейку в качестве Ссылка аргумент.
- Наберите "А закрывающий круглый кронштейн для завершения КОЛОНКА функция
- Оставь ВПР откроется диалоговое окно функции для следующего шага в учебнике.
Ввод аргумента поиска диапазона VLOOKUP
ВПР«s диапазон_просмотра аргумент является логическим значением (ПРАВДА или же ЛОЖНЫЙ), который указывает, хотите ли вы ВПР найти точное или приблизительное соответствие lookup_Value.
- Если ПРАВДА или если этот аргумент опущен: ВПР возвращает либо точное соответствие lookup_ValueИли, если точное совпадение не найдено, ВПР возвращает следующее наибольшее значение. Для формулы, чтобы сделать это, данные в первом столбце таблица_массив должны сортироваться в порядке возрастания.
- Если ЛОЖНЫЙ: ВПР будет использовать только точное соответствие lookup_Value, Если в первом столбце имеется два или более значений таблица_массив которые соответствуют значению поиска, используется первое найденное значение. Если точное совпадение не найдено, # N / A ошибка возвращается.
В этом учебнике, поскольку мы ищем конкретную информацию об определенном элементе оборудования, мы установим диапазон_просмотра равно Ложь.
- Нажми на диапазон_просмотра в диалоговом окне.
- Введите слово Ложь в этой строке, чтобы указать, что мы хотим ВПР чтобы вернуть точное соответствие для данных, которые мы ищем.
- Нажмите Хорошо для завершения формулы поиска и закрытия диалогового окна.
- Поскольку мы еще не ввели критерии поиска в клетка D2 # N / A ошибка будет присутствовать в ячейка E2.
- Эта ошибка будет исправлена, когда мы добавим критерии поиска на последнем этапе учебника.
Копирование формулы поиска
Формула поиска предназначена для извлечения данных из нескольких столбцов таблицы данных за один раз. Чтобы сделать это, формула поиска должна находиться во всех полях, из которых мы хотим получить информацию.
В этом уроке мы хотим, чтобы он извлекал данные из столбцы 2, 3, а также 4 таблицы данных - это цена, номер детали и имя поставщика, когда мы вводим частичное имя в качестве lookup_Value.
Поскольку данные изложены на регулярной схеме на листе, мы можем скопировать формулу поиска в ячейка E2 в ячейки F2 а также G2.
По мере копирования формулы Excel будет обновлять относительную ссылку на ячейку в КОЛОНКА функции (клетка B1), чтобы отразить новое местоположение формулы.
Кроме того, Excel не меняет абсолютную ссылку на ячейку $ D $ 2 и названный диапазон Таблица как копируется формула.
Существует несколько способов копирования данных в Excel, но, вероятно, самый простой способ - использовать Заполнить ручку.
- Нажмите на ячейка E2, где находится формула поиска, чтобы сделать ее активной ячейкой.
- Поместите указатель мыши над черный квадрат в правом нижнем углу - указатель изменится на знак плюса, чтобы обозначить дескриптор заполнения.
- Нажмите левую кнопку мыши и перетяните ручку заливки через ячейка G2.
- Релиз кнопку мыши и ячейка F3 должен содержать двумерную формулу поиска.
- Если все сделано правильно, ячейки F2 а также G2 должны теперь также содержать # N / A ошибка, которая присутствует в ячейку E2.
Ввод критериев поиска
После того, как формула поиска скопирована в нужные ячейки, ее можно использовать для извлечения информации из таблицы данных. Для этого введите имя элемента вы хотите получить в lookup_Value клетка (D2) и нажмите Войти на клавиатуре.
После завершения каждая ячейка, содержащая формулу поиска, должна содержать другую часть данных об аппаратном элементе, который вы ищете.
- Нажмите на ячейка D2 на листе.
- Тип Виджет в ячейка D2 и нажмите Войти на клавиатуре.
- Следующая информация должна отображаться в ячейки E2 в G2.
- E2: $ 14,76 - цена виджета
- F2: PN-98769 - номер детали для виджета
- G2: Widgets Inc. - название поставщика для виджетов
- Проверьте ВПР формулу массива далее, введя имя других частей в ячейка D2 и наблюдение за результатами в ячейки E2 в G2
