Создайте формулу поиска, которая использует несколько критериев для поиска информации в базе данных или таблице данных, используя формулу массива в Excel. Формула массива включает в себя вложение функции MATCH внутри функции INDEX .
Эта статья относится к Excel 2019, 2016, 2013, 2010; и Excel для Mac.
Подготовьте свою книгу Excel с данными
Этот учебник содержит пошаговый пример, который показывает, как создать формулу поиска, которая использует несколько критериев для поиска поставщика титановых виджетов в образце базы данных. Следуйте инструкциям, чтобы узнать, как создавать собственные формулы поиска.
Чтобы выполнить действия, описанные в этом руководстве, введите пример данных в следующие ячейки, как показано на рисунке ниже. Строки 3 и 4 оставлены пустыми, чтобы вместить формулу массива, созданную в этом уроке.
- Введите верхний диапазон данных в ячейки от D1 до F2.
- Введите второй диапазон в ячейки от D5 до F11.
Этот урок не включает форматирование, видимое на изображении. Это не влияет на работу формулы поиска. Информация о параметрах форматирования доступна в этом учебном руководстве по базовому форматированию Excel .
Создать функцию INDEX в Excel
Функция INDEX является одной из немногих функций в Excel, которая имеет несколько форм. Функция имеет форму массива и ссылка на форму. Форма массива возвращает фактические данные из базы данных или таблицы данных. Справочная форма дает ссылку на ячейку или расположение данных в таблице.
В этом руководстве форма массива используется для поиска имени поставщика титановых виджетов, а не ссылки на ячейку этого поставщика в базе данных.
Выполните следующие шаги, чтобы создать функцию INDEX:
- Выберите ячейку F3, чтобы сделать ее активной ячейкой . Это где вложенная функция будет введена.
- Выберите Формулы .
- Выберите Lookup & Reference, чтобы открыть раскрывающийся список функций.
- Выберите INDEX, чтобы открыть диалоговое окно Select Arguments .
- Выберите массив, row_num, column_num .
- Выберите OK, чтобы открыть диалоговое окно «Аргументы функции». В Excel для Mac откроется построитель формул.
- Поместите курсор в текстовое поле Array.
- Выделите ячейки от D6 до F11 на листе, чтобы ввести диапазон в диалоговое окно.
Оставьте диалоговое окно «Аргументы функций» открытым. Формула не закончена. Вы завершите формулу в инструкциях ниже.
Запустите вложенную функцию MATCH
При вложении одной функции в другую невозможно открыть второй или вложенный конструктор формул функции для ввода необходимых аргументов . Вложенная функция должна быть введена как один из аргументов первой функции.
При вводе функций вручную аргументы функции отделяются друг от друга запятой.
Первый шаг для ввода вложенной функции MATCH — ввести аргумент Lookup_value. Lookup_value — это местоположение или ссылка на ячейку для условия поиска, которое должно быть найдено в базе данных.
Lookup_value принимает только один критерий поиска или термин. Чтобы выполнить поиск по нескольким критериям, увеличьте значение Lookup_value путем объединения или объединения двух или более ссылок на ячейки вместе с помощью символа амперсанда (&).
- В диалоговом окне «Аргументы функции» поместите курсор в текстовое поле «Row_num».
- Введите МАТЧ ( .
- Выберите ячейку D3, чтобы ввести ссылку на эту ячейку в диалоговое окно.
- Введите & (амперсанд) после ссылки на ячейку D3, чтобы добавить вторую ссылку на ячейку.
- Выберите ячейку E3, чтобы ввести ссылку на вторую ячейку.
- Введите , (запятая) после того, как ссылки на ячейку E3 , чтобы завершить ввод Lookup_Value аргумента функции соответствия в.
На последнем этапе учебника значения Lookup_value будут введены в ячейки D3 и E3 рабочего листа.
Завершить вложенную функцию MATCH
Этот шаг охватывает добавление аргумента Lookup_array для вложенной функции MATCH. Lookup_array — это диапазон ячеек, которые выполняет функция MATCH, чтобы найти аргумент Lookup_value, добавленный на предыдущем шаге руководства.
Поскольку в аргументе Lookup_array были идентифицированы два поля поиска, то же самое должно быть сделано для Lookup_array. Функция MATCH ищет только один массив для каждого указанного термина. Чтобы ввести несколько массивов, используйте амперсанд, чтобы объединить массивы вместе.
- Поместите курсор в конец данных в текстовом поле Row_num. Курсор появляется после запятой в конце текущей записи.
- Выделите ячейки от D6 до D11 на листе, чтобы ввести диапазон. Это первый массив, который ищет функция.
- Введите & (амперсанд) после ссылки на ячейку D6: D11. Это заставляет функцию искать два массива.
- Выделите ячейки от E6 до E11 на листе, чтобы ввести диапазон. Это второй массив, который ищет функция.
- Введите , (запятая) после того, как ссылки на ячейку E3 , чтобы завершить ввод Lookup_Array аргумента функции соответствия в.
- Оставьте диалоговое окно открытым для следующего шага в учебнике.
Добавьте МАТЧ Тип Аргумент
Третий и последний аргумент функции MATCH — это аргумент Match_type . Этот аргумент сообщает Excel, как сопоставить Lookup_value со значениями в Lookup_array. Доступные варианты: 1, 0 или -1.
Этот аргумент не является обязательным. Если он опущен, функция использует значение по умолчанию 1.
- Если Match_type = 1 или отсутствует, MATCH находит наибольшее значение, которое меньше или равно Lookup_value. Данные Lookup_array должны быть отсортированы в порядке возрастания.
- Если Match_type = 0, MATCH находит первое значение, которое равно Lookup_value. Данные Lookup_array могут быть отсортированы в любом порядке.
- Если Match_type = -1, MATCH находит наименьшее значение, которое больше или равно Lookup_value. Данные Lookup_array должны быть отсортированы в порядке убывания.
Введите эти шаги после запятой, введенной на предыдущем шаге в строке Row_num в функции INDEX:
- Введите 0 (ноль) после запятой в текстовом поле Row_num. Это заставляет вложенную функцию возвращать точные совпадения с терминами, введенными в ячейки D3 и E3.
- Введите ) (закрывающая круглая скобка), чтобы завершить функцию МАТЧ.
- Оставьте диалоговое окно открытым для следующего шага в учебнике.
Завершить функцию INDEX
Функция MATCH сделана. Пришло время перейти к текстовому полю Column_num диалогового окна и ввести последний аргумент для функции INDEX. Этот аргумент сообщает Excel, что номер столбца находится в диапазоне от D6 до F11. Здесь он находит информацию, возвращаемую функцией. В этом случае поставщик титановых виджетов.
- Поместите курсор в текстовое поле Column_num.
- Введите 3 (число три). Это говорит формуле искать данные в третьем столбце диапазона от D6 до F11.
- Оставьте диалоговое окно открытым для следующего шага в учебнике.
Создать формулу массива
Перед закрытием диалогового окна превратите вложенную функцию в формулу массива . Это позволяет функции искать несколько терминов в таблице данных. В этом руководстве сопоставляются два термина: виджеты из столбца 1 и титан из столбца 2.
Чтобы создать формулу массива в Excel, одновременно нажмите клавиши CTRL, SHIFT и ENTER. После нажатия функция окружена фигурными скобками, указывающими, что функция теперь является массивом.
- Выберите ОК, чтобы закрыть диалоговое окно. В Excel для Mac выберите Готово .
- Выберите ячейку F3, чтобы просмотреть формулу, и поместите курсор в конец формулы на панели формул.
- Чтобы преобразовать формулу в массив, одновременно нажмите клавиши CTRL + SHIFT + ENTER .
- Ошибка # N / A появляется в ячейке F3. Это ячейка, в которой была введена функция.
- Ошибка # N / A появляется в ячейке F3, поскольку ячейки D3 и E3 не заполнены. D3 и E3 — это ячейки, в которых функция ищет значения Lookup_values. После добавления данных в эти две ячейки ошибка заменяется информацией из базы данных .
Добавить критерии поиска
Последний шаг — добавить условия поиска на лист. Этот шаг соответствует терминам Widgets из столбца 1 и Titanium из столбца 2.
Если формула находит совпадение для обоих терминов в соответствующих столбцах базы данных, она возвращает значение из третьего столбца.
- Выберите ячейку D3 .
- Введите виджеты .
- Выберите ячейку E3 .
- Введите Titanium и нажмите Enter .
- Название поставщика, Widgets Inc., появляется в ячейке F3. Это единственный поставщик в списке, который продает титановые виджеты.
- Выберите ячейку F3 . Функция отображается на панели формул над рабочим листом.
{= ИНДЕКС (D6: F11, MATCH (D3 & Е3, D6: D11 & Е6: E11,0), 3)}
В этом примере есть только один поставщик титановых виджетов. Если было более одного поставщика, функция, указанная первым в базе данных, возвращается функцией.