Как сделать

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

Учебные данные для функции поиска с несколькими критериями в Excel

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

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

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

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

Учебные данные для функции поиска с несколькими критериями в Excel
  • Введите верхний диапазон данных в ячейки от D1 до F2.
  • Введите второй диапазон в ячейки от D5 до F11.

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

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

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

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

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

  1. Выберите ячейку F3, чтобы сделать ее активной ячейкой . Это где вложенная функция будет введена.
  2. Выберите Формулы .
  3. Выберите Lookup & Reference, чтобы открыть раскрывающийся список функций.
  4. Выберите INDEX, чтобы открыть диалоговое окно Select Arguments .
  5. Выберите массив, row_num, column_num .
  6. Выберите OK, чтобы открыть диалоговое окно «Аргументы функции». В Excel для Mac откроется построитель формул.
  7. Поместите курсор в текстовое поле Array.
  8. Выделите ячейки от D6 до F11 на листе, чтобы ввести диапазон в диалоговое окно.
Снимок экрана, показывающий, как настроить массив для функции INDEX в Excel

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

Запустите вложенную функцию 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 в качестве аргумента для функции INDEX в Excel

На последнем этапе учебника значения 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 аргумента функции соответствия в.
  6. Оставьте диалоговое окно открытым для следующего шага в учебнике.
Снимок экрана, показывающий, как ввести аргумент MATCH в функции INDEX в Excel

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

Третий и последний аргумент функции 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. Введите 0 (ноль) после запятой в текстовом поле Row_num. Это заставляет вложенную функцию возвращать точные совпадения с терминами, введенными в ячейки D3 и E3.
  2. Введите ) (закрывающая круглая скобка), чтобы завершить функцию МАТЧ.
  3. Оставьте диалоговое окно открытым для следующего шага в учебнике.
Снимок экрана, показывающий, как ввести аргумент MATCH в функции INDEX в Excel

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

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

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

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

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

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

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

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

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

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

{= ИНДЕКС (D6: F11, MATCH (D3 & Е3, D6: D11 & Е6: E11,0), 3)}
Снимок экрана, показывающий результаты выполнения функции INDEX в Excel

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

Похожие посты
Как сделать

Как получить возмещение за покупки в iTunes или App Store

Как сделать

Поверхностное перо не работает? Вот как это исправить

Как сделать

Как настроить и использовать Fire TV Recast

Как сделать

Как изменить рингтон по умолчанию на вашем iPhone