Используя формулу массива в Excel, мы можем создать формулу поиска, которая использует несколько критериев для поиска информации в базе данных или таблице данных. Формула массива включает вложение функции MATCH внутри функции INDEX.
Этот учебник включает пошаговый пример создания формулы поиска, которая использует несколько критериев для поиска поставщикаТитановые виджеты в примерной базе данных. Следуйте дальше, чтобы узнать, насколько легко создавать собственные формулы поиска.
Подготовьте книгу Excel с данными
Чтобы выполнить шаги этого руководства, вам нужно начать с ввода данных образца в следующие ячейки, как показано на рисунке выше. Строки 3 и 4 остаются пустыми, чтобы соответствовать формуле массива, созданной во время этого руководства.
- Введите верхний диапазон данных в ячейки D1 в F2
- Введите второй диапазон в ячейки D5 в F11
Учебник не включает форматирование, видимое на изображении, но это не повлияет на работу формулы поиска. Информация о параметрах форматирования, аналогичная описанной выше, приведена в этом Основном учебнике по форматированию Excel.
02 из 08Создание функции INDEX в Excel
ИНДЕКС функция является одной из немногих в Excel, которая имеет несколько форм. Функция имеет Форма массива и Справочная форма, Форма массива возвращает фактические данные из базы данных или таблицы данных, а справочная форма дает вам ссылку на ячейку или местоположение данных в таблице.
В этом учебном пособии мы будем использовать форму массива, так как мы хотим узнать имя поставщика для титановых виджетах, а не ссылку на ячейку для этого поставщика в нашей базе данных. Выполните следующие действия, чтобы создать свою функцию INDEX:
- Нажмите на ячейку F3 чтобы сделать его активной ячейкой - здесь мы введем вложенную функцию.
- Нажми на Формулы вкладку меню ленты.
- выберите Поиск и ссылки от ленты, чтобы открыть функцию.
- Нажмите на ИНДЕКС в списке, чтобы Выберите Аргументы диалоговое окно.
- Выбрать array, row_num, col_num в диалоговом окне.
- Нажмите Хорошо открыть диалоговое окно функции INDEX; это откроет формулу Builder в Excel.
- в Formula Builder, нажми на массив линия.
- Выделите ячейки D6 в F11, щелкнув и перетащив на листе, чтобы ввести диапазон в конструктор.
В старых версиях Excel Formula Builder будут заменены Аргументы функции окно. Примените те же шаги, что и в этом уроке, в окне.
03 из 08Запуск функции Вложенная функция MATCH
При вложенности одной функции внутри другой невозможно открыть конструктор формул второй или вложенной функции o ввести необходимые аргументы. Вложенную функцию нужно вводить как одну из отсортированных в первой функции.
При вводе функций вручную аргументы функции разделяются запятой.
Первым шагом при вводе вложенной функции MATCH является ввод lookup_Value аргумент. Lookup_value будет ссылкой местоположения или ячейки для поискового запроса, который мы хотим сопоставить в базе данных.
Обычно Lookup_value принимает только один критерий поиска или термин. Чтобы найти несколько критериев, мы должны расширить Lookup_value; Tего делается путем конкатенации или соединения двух или более ссылок на ячейки вместе с использованием символа амперсанда - &.
- в Formula Builder, нажми на ROW_NUM линия.
- Введите имя функции МАТЧ а затем открытый круглый кронштейн.
- Нажмите на ячейку D3 для ввода этой ссылки на ячейку в диалоговом окне.
- Введите амперсанд после ссылки на ячейку D3 чтобы добавить вторую ссылку на ячейку.
- Нажмите на ячейку E3 для ввода этой второй ссылки на ячейку.
- Введите запятую после ссылки на ячейку E3 для завершения ввода функции MATCH lookup_Value аргумент.
На последнем этапе учебника наш Lookup_values будут введены в ячейки D3 и E3 рабочего листа.
04 из 08Завершите функцию вложенного MATCH
Этот шаг охватывает добавление Lookup_Array аргумент для вложенных МАТЧ функция. Lookup_array - это диапазон ячеек, который функция MATCH будет искать, чтобы найти аргумент Lookup_value, добавленный на предыдущем этапе учебника.
Поскольку мы определили два поля поиска в аргументе Lookup_array, мы должны сделать то же самое для Lookup_array. Функция MATCH выполняет поиск только одного массива для каждого указанного термина, поэтому для ввода нескольких массивов мы снова используем амперсанд для совместного объединения массивов.
Эти шаги должны быть введены после запятой, введенной на предыдущем шаге на ROW_NUM линии в ИНДЕКС функция.
- Нажми на ROW_NUM после запятой, чтобы поместить точку ввода в конец текущей записи.
- Выделите ячейки D6 в D11 на листе для ввода диапазона - это первый массив, который функция выполняет для поиска.
- Введите амперсанд после ссылки на ячейку D6: D11 потому что мы хотим, чтобы функция искала два массива.
- Выделите ячейки E6 в E11 в листе для ввода диапазона - это второй массив, который функция выполняет для поиска.
- Введите запятую после ссылки на ячейку E3 для завершения ввода МАТЧ функция-х Lookup_Array аргумент.
- Оставь ИНДЕКС откроется диалоговое окно функции для следующего шага в учебнике.
Добавить аргумент типа MATCH
Третьим и последним аргументом функции MATCH является тип_сопоставления аргумент; он указывает 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 линии в ИНДЕКС функция.
- После запятой на ROW_NUM line, введите нуль - 0 - поскольку мы хотим, чтобы вложенная функция возвращала точные совпадения с терминами, введенными в ячейки D3 а также E3.
- Введите закрывающий круглый кронштейн - ) - завершить МАТЧ функция.
- Оставь Formula Builder откроется для следующего шага в учебнике.
Завершение функции INDEX
Теперь, когда МАТЧ мы перейдем к третьей строке построителя формул и введем последний аргумент для ИНДЕКС функция. Этот третий и последний аргумент номер_столбца аргумент, который указывает Excel номер столбца в диапазоне D6 в F11; это то, где он найдет информацию, которую мы хотим вернуть функции. В этом случае поставщик для титановые виджеты.
- Нажми на номер_столбца линия.
- Введите номер три - 3 - на этой строке, так как мы ищем данные в третьем столбце диапазона D6 в F11.
- Снова оставьте Formula Builder откроется для следующего шага в учебнике.
Создание формулы массива
Перед закрытием Formula Builder, нам нужно превратить нашу вложенную функцию в формулу массива; это то, что позволяет нашей функции искать несколько терминов в таблице данных. В этом учебном пособии мы рассмотрим два условия: Виджеты из столбца 1 и титан из столбца 2.
Создание формулы массива в Excel выполняется нажатием CTRL, СДВИГ, а также ВОЙТИ клавиши на клавиатуре одновременно. После нажатия функция будет окружена фигурными фигурными скобками, указывая, что теперь функция представляет собой массив.
- Закрой Formula Builder нажав Готово кнопка.
- Затем выберите ячейку F4 и нажмите Войти для просмотра формулы.
- Чтобы преобразовать формулу в массив, одновременно нажмите CTRL + SHIFT + ВВОД на клавиатуре.
- Если все сделано правильно # N / A ошибка появится в ячейке F3 - ячейке, в которую мы ввели функцию.
- # N / A в ячейке появляется ошибка F3 потому что клетки D3 а также E3 пустые. D3 и E3 - ячейки, в которых мы сказали функции, чтобы найти Lookup_values, После добавления данных в эти две ячейки ошибка будет заменена информацией из базы данных.
Добавление критериев поиска
Наконец, мы добавим поисковые запросы в наш рабочий лист. Как упоминалось в предыдущем шаге, мы стремимся соответствовать условиям Виджеты из столбца 1 и титан из столбца 2. Если и только если наша формула найдет соответствие для обоих терминов в соответствующих столбцах в базе данных, вернет ли она значение из третьего столбца.
- Нажмите на ячейку D3.
- Тип Виджеты и нажмите Войти на клавиатуре.
- Нажмите на ячейку E3.
- Тип титан и нажмите Войти на клавиатуре.
- Имя поставщика Widgets Inc. должен появиться в ячейке F3 - расположение функции, так как это единственный поставщик, который продает титановые виджеты.
- Когда вы нажимаете на ячейку F3, полная функция отображается в строке формул над листом.
{= ИНДЕКС (D6: F11, MATCH (D3 & E3, D6: D11 и E6: E11, 0), 3)}
В нашем примере для титановых виджета был только один поставщик. Если было несколько поставщиков, поставщик, указанный в базе данных, возвращается функцией.
