Запуск функции вложенного MATCH
Ввод функции MATCH в качестве аргумента порядкового номера столбца.
Это продолжение двустороннего поиска в Excel с использованием VLOOKUP, часть 1 .
Ввод функции MATCH в качестве аргумента порядкового номера столбца
Обычно VLOOKUP возвращает данные только из одного столбца таблицы данных, и этот столбец задается аргументом номера индекса столбца .
Однако в этом примере у нас есть три столбца, в которых мы хотим найти данные, поэтому нам нужен способ легко изменить порядковый номер столбца без редактирования формулы поиска.
Это где функция MATCH вступает в игру. Это позволит нам сопоставить номер столбца с именем поля ( январь, февраль или март), которое мы вводим в ячейку E2 рабочего листа.
Вложенные функции
Это достигается путем вложения функции MATCH внутри VLOOKUP в строку Col_index_num диалогового окна.
Ввод функции MATCH вручную
При вложении функций Excel не позволяет нам открывать диалоговое окно второй функции для ввода ее аргументов.
Поэтому функцию MATCH необходимо вводить вручную в строке Col_index_num .
При вводе функций вручную каждый из аргументов функции должен быть разделен запятой «,» .
Ввод аргумента Lookup_value функции MATCH
Первый шаг при вводе вложенной функции MATCH — ввести аргумент Lookup_value .
Значение Lookup_value будет ссылкой на местоположение или ячейку для поискового запроса , которому мы хотим соответствовать в базе данных.
- В диалоговом окне функции VLOOKUP щелкните строку Col_index_num .
- Введите совпадение имени функции с последующей круглой скобкой » ( «
- Нажмите на ячейку E2, чтобы ввести ссылку на эту ячейку в диалоговое окно.
- Введите запятую «,» после ссылки на ячейку E3, чтобы завершить ввод аргумента Lookup_value функции MATCH .
- Оставьте открытым диалоговое окно функции VLOOKUP для следующего шага в учебнике.
На последнем этапе учебника значения Lookup_values будут введены в ячейки D2 и E2 рабочего листа .
Добавление Lookup_array для функции MATCH
Добавление Lookup_array для функции MATCH.
Добавление Lookup_array для функции MATCH
Этот шаг охватывает добавление аргумента Lookup_array для вложенной функции MATCH.
Lookup_Array это диапазон ячеек, функция MATCH будет искать , чтобы найти Lookup_Value аргумент , добавленный в предыдущем шаге урока.
В этом примере мы хотим, чтобы функция MATCH искала в ячейках от D5 до G5 совпадение с названием месяца, который будет введен в ячейку E2.
Эти шаги необходимо вводить после запятой, введенной на предыдущем шаге в строке Col_index_num в диалоговом окне функции VLOOKUP .
- При необходимости щелкните строку Col_index_num после запятой, чтобы поместить точку вставки в конец текущей записи.
- Выделите ячейки от D5 до G5 на рабочем листе, чтобы ввести эти ссылки на ячейки в качестве диапазона, который должна выполнять функция.
- Нажмите клавишу F4 на клавиатуре, чтобы изменить этот диапазон на абсолютные ссылки на ячейки. Это позволит скопировать заполненную формулу поиска в другие места на рабочем листе на последнем шаге учебного пособия.
- Введите запятую «,» после ссылки на ячейку E3, чтобы завершить ввод аргумента Lookup_array функции MATCH .
Добавление типа совпадения и завершение функции MATCH
Двусторонний поиск в Excel с помощью VLOOKUP.
Добавление типа соответствия и завершение функции MATCH
Третий и последний аргумент функции 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 в диалоговом окне функции VLOOKUP .
- После второй запятой в строке Col_index_num введите ноль « 0 », поскольку мы хотим, чтобы вложенная функция возвращала точное совпадение с месяцем, введенным в ячейку E2.
- Введите закрывающую круглую скобку « ) » для завершения функции MATCH.
- Оставьте открытым диалоговое окно функции VLOOKUP для следующего шага в учебнике.
Ввод аргумента поиска диапазона VLOOKUP
Ввод аргумента поиска диапазона.
Аргумент поиска диапазона
Аргумент Range_lookup в VLOOKUP — это логическое значение (только TRUE или FALSE), которое указывает, хотите ли вы, чтобы VLOOKUP нашел точное или приблизительное совпадение с Lookup_value .
- Если TRUE или этот аргумент пропущен, VLOOKUP возвращает либо точное совпадение с Lookup_value, либо, если точное совпадение не найдено, VLOOKUP возвращает следующее наибольшее значение. Чтобы формула сделала это, данные в первом столбце Table_array должны быть отсортированы в порядке возрастания.
- Если FALSE, VLOOKUP будет использовать только точное совпадение с Lookup_value. Если в первом столбце Table_array есть два или более значений, которые соответствуют значению поиска, используется первое найденное значение. Если точное совпадение не найдено, возвращается ошибка # N / A.
В этом уроке, поскольку мы ищем цифры продаж за конкретный месяц, мы установим Range_lookup равным False .
- Нажмите на строку Range_lookup в диалоговом окне.
- Введите слово False в этой строке, чтобы указать, что мы хотим, чтобы VLOOKUP возвращал точное соответствие для данных, которые мы ищем
- Нажмите кнопку ОК, чтобы завершить формулу двумерного поиска и закрыть диалоговое окно
- Поскольку мы еще не ввели критерии поиска в ячейки D2 и E2, в ячейке F2 будет присутствовать ошибка # N / A.
- Эта ошибка будет исправлена на следующем шаге учебника, когда мы добавим критерии поиска на следующем шаге учебника.
Тестирование формулы двустороннего поиска
Двусторонний поиск в Excel с помощью VLOOKUP.
Тестирование формулы двустороннего поиска
Чтобы использовать формулу двустороннего поиска, чтобы найти данные о ежемесячных продажах для различных файлов cookie, перечисленных в массиве таблиц, введите имя файла cookie в ячейку D2, месяц в ячейку E2 и нажмите клавишу ВВОД на клавиатуре.
Данные о продажах будут отображаться в ячейке F2.
- Нажмите на ячейку D2 в вашем рабочем листе.
- Введите Oatmeal в ячейку D2 и нажмите клавишу ВВОД на клавиатуре
- Нажмите на ячейку E2
- Введите февраль в ячейку E2 и нажмите клавишу ВВОД на клавиатуре
- Значение 1345 долларов США — сумма продаж печенья из овсяной муки в феврале — должно отображаться в ячейке F2.
- На этом этапе ваш рабочий лист должен соответствовать примеру на странице 1 этого урока.
- Далее протестируйте формулу поиска, введя любую комбинацию типов файлов cookie и месяцев, представленных в массиве Table_array, и цифры продаж должны отображаться в ячейке F2.
- Последний шаг в учебнике охватывает копирование формулы поиска с помощью ручки заполнения .
Если сообщение об ошибке, например #REF! появляется в ячейке F2, этот список сообщений об ошибках VLOOKUP может помочь вам определить, где находится проблема.
Копирование двумерной формулы поиска с помощью ручки заливки
Копирование двумерной формулы поиска с помощью ручки заполнения.
Копирование двумерной формулы поиска с помощью ручки заливки
Чтобы упростить сравнение данных за разные месяцы или разные файлы cookie, формулу поиска можно скопировать в другие ячейки, чтобы одновременно отображать несколько сумм.
Поскольку данные располагаются в виде регулярного шаблона на листе, мы можем скопировать формулу поиска в ячейке F2 в ячейку F3.
По мере копирования формулы Excel обновит относительные ссылки на ячейки, чтобы отразить новое местоположение формулы. В этом случае D2 становится D3, а E2 становится E3,
Кроме того, Excel сохраняет неизменную ссылку на ячейку, поэтому абсолютный диапазон $ D $ 5: $ G $ 5 остается неизменным при копировании формулы.
Существует несколько способов копирования данных в Excel, но, возможно, самый простой способ — использовать Fill Handle.
- Нажмите на ячейку D3 в вашем рабочем листе.
- Введите Oatmeal в ячейку D3 и нажмите клавишу ВВОД на клавиатуре
- Нажмите на ячейку E3
- Введите March в ячейку E3 и нажмите клавишу ВВОД на клавиатуре
- Нажмите на ячейку F2, чтобы сделать ее активной ячейкой
- Поместите указатель мыши на черный квадрат в нижнем правом углу. Указатель изменится на знак плюс «+» — это ручка заполнения
- Нажмите левую кнопку мыши и перетащите маркер заполнения вниз в ячейку F3.
- Отпустите кнопку мыши, и ячейка F3 должна содержать формулу двумерного поиска
- Значение $ 1 287 — сумма продаж печенья из овсяной муки в марте — должно отображаться в ячейке F3.