Как создать формулу поиска 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. Выберите ИНДЕКС , чтобы открыть диалоговое окно «Выбор аргументов».
  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. Введите 0 (ноль) после запятой в текстовом поле 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 + SHIFT + ENTER .
  4. В ячейке F3 появляется ошибка # N/A . Это ячейка, в которой была введена функция.
  5. Ошибка # N/A появляется в ячейке F3, поскольку ячейки D3 и E3 не заполнены. D3 и E3 – это ячейки, в которых функция ищет значения Lookup_values. После добавления данных в эти две ячейки ошибка заменяется информацией из базы данных.

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

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

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

  1. Выберите ячейку D3 .
  2. Введите виджеты .
  3. Выберите ячейку E3 .
  4. Введите Titanium и нажмите Enter .
  5. Название поставщика, Widgets Inc., появляется в ячейке F3. Это единственный поставщик в списке, который продает титановые виджеты.
  6. Выберите ячейку F3 . Функция отображается на панели формул над рабочим листом.
 {= ИНДЕКС (D6: F11, MATCH (D3 & Е3, D6: D11 & Е6: E11,0), 3)} 

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

Оцените статью
Solutics.ru
Добавить комментарий