Как создать формулу поиска Excel с несколькими критериями

Создайте формулу поиска, которая использует несколько критериев для поиска информации в базе данных или таблице данных, используя формулу массива в Excel. Формула массива предполагает вложение функции MATCH внутри функции INDEX.

Эта статья относится к Excel 2019, 2016, 2013, 2010; и Excel для Mac.

Подготовьте свою книгу Excel с данными

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

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

• Введите верхний диапазон данных в ячейки от D1 до F2.
• Введите второй диапазон в ячейки от D5 до F11.

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

Создать функцию INDEX в Excel

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

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

Выполните следующие шаги, чтобы создать функцию INDEX:

1. Выберите ячейку F3 сделать его активной клеткой. Это где вложенная функция будет введена.
2. Выбрать Формулы.
3. выберите Уважать Ссылка открыть раскрывающийся список функций.
4. Выбрать ИНДЕКС чтобы открыть диалоговое окно Select Arguments.
5. выберите массив, row_num, column_num.
6. Выбрать Хорошо открыть диалоговое окно Аргументы функций. В Excel для Mac откроется построитель формул.
7. Поместите курсор в текстовое поле Array.
8. Выделить клетки D6 в F11 на листе, чтобы ввести диапазон в диалоговом окне.

Оставьте диалоговое окно «Аргументы функций» открытым. Формула не закончена. Вы завершите формулу в инструкциях ниже.

Запустите вложенную функцию MATCH

При вложении одной функции в другую невозможно открыть второй или вложенный конструктор формул функции для ввода необходимых аргументов. Вложенная функция должна быть введена как один из аргументов первой функции.

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

Первый шаг для ввода вложенной функции MATCH — ввести аргумент Lookup_value. Lookup_value — это местоположение или ссылка на ячейку для условия поиска, которое должно быть найдено в базе данных.

Lookup_value принимает только один критерий поиска или термин. Чтобы выполнить поиск по нескольким критериям, расширьте значение Lookup_value путем объединения или объединения двух или более ссылок на ячейки вместе с помощью символа амперсанда ().

1. В диалоговом окне «Аргументы функции» поместите курсор в текстовое поле «Row_num».
2. Войти МАТЧ(.
3. Выберите ячейку D3 ввести ссылку на эту ячейку в диалоговом окне.
4. Войти (амперсанд) после ссылки на ячейку D3, чтобы добавить вторую ссылку на ячейку.
5. Выберите ячейку E3 ввести ссылку на вторую ячейку.
6. Войти , (запятая) после ссылки на ячейку E3, чтобы завершить ввод аргумента Lookup_value функции MATCH.

На последнем этапе учебника значения Lookup_value будут введены в ячейки D3 и E3 рабочего листа.

Завершить вложенную функцию MATCH

Этот шаг охватывает добавление аргумента Lookup_array для вложенной функции MATCH. Lookup_array — это диапазон ячеек, которые выполняет функция MATCH, чтобы найти аргумент Lookup_value, добавленный на предыдущем шаге руководства.

Поскольку в аргументе Lookup_array были определены два поля поиска, то же самое должно быть сделано для Lookup_array. Функция MATCH ищет только один массив для каждого указанного термина. Чтобы ввести несколько массивов, используйте амперсанд, чтобы объединить массивы вместе.

1. Поместите курсор в конец данных в текстовом поле Row_num. Курсор появляется после запятой в конце текущей записи.
2. Выделить клетки D6 в D11 в листе для ввода диапазона. Это первый массив, который ищет функция.
3. Войти (амперсанд) после ссылки на ячейку D6: D11. Это заставляет функцию искать два массива.
4. Выделите ячейки E6 в E11 в листе для ввода диапазона. Это второй массив, который ищет функция.
5. Войти , (запятая) после ссылки на ячейку E3, чтобы завершить ввод аргумента Lookup_array функции MATCH.
6. Оставьте диалоговое окно открытым для следующего шага в учебнике.

Добавьте МАТЧ Тип Аргумент

Третий и последний аргумент Функция 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:

1. Войти (ноль) после запятой в текстовом поле Row_num. Это заставляет вложенную функцию возвращать точные совпадения с терминами, введенными в ячейки D3 и E3.
2. Войти ) (закрывающая круглая скобка) для завершения функции MATCH.
3. Оставьте диалоговое окно открытым для следующего шага в учебнике.

Завершить функцию INDEX

Функция MATCH сделана. Пришло время перейти к текстовому полю Column_num диалогового окна и ввести последний аргумент для функции INDEX. Этот аргумент сообщает Excel, что номер столбца находится в диапазоне от D6 до F11. Здесь он находит информацию, возвращаемую функцией. В этом случае поставщик титановых виджетов.

1. Поместите курсор в текстовое поле Column_num.
2. Войти 3 (номер три). Это говорит формуле искать данные в третьем столбце диапазона от D6 до F11.
3. Оставьте диалоговое окно открытым для следующего шага в учебнике.

Создать формулу массива

Перед закрытием диалогового окна превратите вложенную функцию в формулу массива. Это позволяет функции выполнять поиск по нескольким терминам в таблице данных. В этом руководстве сопоставляются два термина: виджеты из столбца 1 и титан из столбца 2.

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

1. Выбрать Хорошо закрыть диалоговое окно. В Excel для Mac выберите Готово.
2. Выберите ячейку F3 чтобы просмотреть формулу и поместить курсор в конец формулы на панели формул.
3. Чтобы преобразовать формулу в массив, одновременно нажмите CTRL+СДВИГ+ВОЙТИ.
4. A # N / A ошибка появляется в ячейке F3. Это ячейка, в которой была введена функция.
5. Ошибка # N / A появляется в ячейке F3, поскольку ячейки D3 и E3 не заполнены. D3 и E3 — это ячейки, в которых функция ищет значения Lookup_values. После добавления данных в эти две ячейки ошибка заменяется информацией из базы данных.

Добавить критерии поиска

Последний шаг — добавить условия поиска на лист. Этот шаг соответствует условиям виджетов из столбца 1 и титана из столбца 2.

Если формула находит совпадение для обоих терминов в соответствующих столбцах базы данных, она возвращает значение из третьего столбца.

1. Выберите ячейку D3.
2. Войти Виджеты.
3. Выберите ячейку E3.
4. Тип титан и нажмите Войти.
5. Название поставщика, Widgets Inc., появляется в ячейке F3. Это единственный поставщик в списке, который продает титановые виджеты.
6. Выберите ячейку F3. Функция отображается на панели формул над рабочим листом.

{= ИНДЕКС (D6: F11, MATCH (D3E3, D6: D11E6: E11,0), 3)}

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