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

2024 Автор: Abigail Brown | [email protected]. Последнее изменение: 2023-12-17 06:59

Что нужно знать

• Сначала создайте функцию ИНДЕКС, затем запустите вложенную функцию ПОИСКПОЗ, введя аргумент Lookup_value.
• Далее добавьте аргумент Lookup_array, за которым следует аргумент Match_type, затем укажите диапазон столбцов.
• Затем превратите вложенную функцию в формулу массива, нажав Ctrl+ Shift+ Enter. Наконец, добавьте условия поиска на лист.

В этой статье объясняется, как создать формулу поиска, которая использует несколько критериев в Excel для поиска информации в базе данных или таблице данных с помощью формулы массива. Формула массива включает в себя вложение функции ПОИСКПОЗ внутри функции ИНДЕКС. Информация охватывает Excel для Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 и Excel для Mac.

Следуйте инструкциям

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

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

Создать функцию ИНДЕКС в Excel

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

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

Выполните следующие действия, чтобы создать функцию ИНДЕКС:

1. Выберите ячейку F3, чтобы сделать ее активной ячейкой. В эту ячейку будет введена вложенная функция.

2. Перейти к Формулы.

   

3. Выберите Поиск и справочник, чтобы открыть раскрывающийся список функций.
4. Выберите ИНДЕКС, чтобы открыть диалоговое окно Выбрать аргументы.
5. Выберите array, row_num, column_num.
6. Выберите OK, чтобы открыть диалоговое окно Аргументы функции. В Excel для Mac открывается построитель формул.
7. Поместите курсор в текстовое поле Массив.

8. Выделите ячейки с D6 по F11 на листе, чтобы ввести диапазон в диалоговое окно.

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

   

Запуск вложенной функции ПОИСКПОЗ

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

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

Первым шагом для входа во вложенную функцию ПОИСКПОЗ является ввод аргумента Искомое_значение. Lookup_value - это местоположение или ссылка на ячейку для условия поиска, которое должно быть сопоставлено в базе данных.

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

1. В диалоговом окне Аргументы функции поместите курсор в текстовое поле Row_num.

2. Введите ПОИСКПОЗ(.

3. Выберите ячейку D3, чтобы ввести ссылку на эту ячейку в диалоговое окно.
4. Введите & (амперсанд) после ссылки на ячейку D3, чтобы добавить вторую ссылку на ячейку.
5. Выберите ячейку E3, чтобы ввести ссылку на вторую ячейку.

6. Введите , (запятая) после ссылки на ячейку E3, чтобы завершить ввод аргумента Lookup_value функции ПОИСКПОЗ.

   

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

Завершить вложенную функцию ПОИСКПОЗ

Этот шаг охватывает добавление аргумента Lookup_array для вложенной функции ПОИСКПОЗ. Lookup_array - это диапазон ячеек, в которых функция ПОИСКПОЗ ищет аргумент Lookup_value, добавленный на предыдущем шаге руководства.

Поскольку в аргументе Lookup_array были идентифицированы два поля поиска, то же самое необходимо сделать для Lookup_array. Функция ПОИСКПОЗ ищет только один массив для каждого указанного термина. Чтобы ввести несколько массивов, используйте амперсанд для объединения массивов вместе.

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

5. Введите , (запятая) после ссылки на ячейку E3, чтобы завершить ввод аргумента Lookup_array функции ПОИСКПОЗ.

   

6. Оставьте диалоговое окно открытым для следующего шага обучения.

Добавить аргумент типа MATCH

Третий и последний аргумент функции ПОИСКПОЗ - аргумент Тип_совпадения. Этот аргумент сообщает Excel, как сопоставить Lookup_value со значениями в Lookup_array. Доступные варианты: 1, 0 или -1.

Это необязательный аргумент. Если он опущен, функция использует значение по умолчанию 1.

• Если Match_type=1 или опущено, ПОИСКПОЗ находит наибольшее значение, которое меньше или равно Lookup_value. Данные Lookup_array должны быть отсортированы в порядке возрастания.
• Если Match_type=0, ПОИСКПОЗ находит первое значение, равное Lookup_value. Данные Lookup_array можно сортировать в любом порядке.
• Если Match_type=-1, ПОИСКПОЗ находит наименьшее значение, которое больше или равно искомому_значению. Данные Lookup_array должны быть отсортированы в порядке убывания.

Введите эти шаги после запятой, введенной на предыдущем шаге в строке Row_num в функции ИНДЕКС:

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

2. Введите ) (закрывающая круглая скобка), чтобы завершить функцию ПОИСКПОЗ.

   

3. Оставьте диалоговое окно открытым для следующего шага обучения.

Завершение функции ИНДЕКС

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

1. Поместите курсор в текстовое поле Column_num.

2. Введите 3 (цифра три). Это число указывает формуле искать данные в третьем столбце диапазона от D6 до F11.

   

3. Оставьте диалоговое окно открытым для следующего шага обучения.

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

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

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

1. Выберите OK, чтобы закрыть диалоговое окно. В Excel для Mac выберите Готово.
2. Выберите ячейку F3, чтобы просмотреть формулу, затем поместите курсор в конец формулы в строке формул.
3. Чтобы преобразовать формулу в массив, нажмите CTRL+ SHIFT+ ENTER.
4. A Н/Д появляется ошибка в ячейке F3. Это ячейка, в которую была введена функция.

5. В ячейке F3 появляется ошибка Н/Д, поскольку ячейки D3 и E3 пусты. D3 и E3 - это ячейки, в которых функция ищет Lookup_value. После добавления данных в эти две ячейки ошибка заменяется информацией из базы данных.

   

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

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

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

1. Выберите ячейку D3.
2. Enter Виджеты.
3. Выберите ячейку E3.
4. Введите Titanium и нажмите Enter.
5. Имя поставщика, Widgets Inc., отображается в ячейке F3. Это единственный из перечисленных поставщиков, который продает виджеты Titanium.

6. Выберите ячейку F3. Функция отображается в строке формул над рабочим листом.

   {=ИНДЕКС(D6:F11, ПОИСКПОЗ(D3&E3, D6:D11&E6:E11, 0), 3)}
   В этом примере есть только один поставщик титановых изделий. Если было более одного поставщика, функция возвращает поставщика, указанного в базе данных первым.