- Запуск функции вложенного MATCH
- Запуск функции вложенного MATCH
- Ввод функции MATCH в качестве аргумента порядкового номера столбца
- Вложенные функции
- Ввод функции MATCH вручную
- Ввод аргумента Lookup_value функции MATCH
- Добавление Lookup_array для функции MATCH
- Добавление Lookup_array для функции MATCH
- Добавление типа совпадения и завершение функции MATCH
- Добавление типа соответствия и завершение функции MATCH
- Ввод аргумента поиска диапазона VLOOKUP
- Аргумент поиска диапазона
- Тестирование формулы двустороннего поиска
- Тестирование формулы двустороннего поиска
- Копирование двумерной формулы поиска с помощью ручки заливки
- Копирование двумерной формулы поиска с помощью ручки заливки
Запуск функции вложенного MATCH
Запуск функции вложенного MATCH
Это продолжение двустороннего поиска в Excel с использованием VLOOKUP, часть 1.
Ввод функции MATCH в качестве аргумента порядкового номера столбца
Обычно VLOOKUP возвращает данные только из одного столбца таблицы данных, и этот столбец задается аргументом индексный номер столбца .
Однако в этом примере у нас есть три столбца, в которых мы хотим найти данные, поэтому нам нужен способ легко изменить индексный номер столбца без редактирования формулы поиска.
Это где функция MATCH вступает в игру. Это позволит нам сопоставить номер столбца с именем поля (январь, февраль или март), которое мы вводим в ячейку E2 рабочего листа.
Вложенные функции
Следовательно, функция MATCH действует как аргумент индексный номер столбца в VLOOKUP.
Это достигается путем вложения функции MATCH внутри VLOOKUP в строку Col_index_num диалогового окна.
Ввод функции MATCH вручную
При вложении функций Excel не позволяет нам открывать диалоговое окно второй функции для ввода ее аргументов.
Поэтому функцию MATCH необходимо вводить вручную в строке Col_index_num .
При вводе функций вручную каждый из аргументов функции должен быть разделен запятой “,” .
Ввод аргумента Lookup_value функции MATCH
Первый шаг при вводе вложенной функции MATCH – ввести аргумент Lookup_value .
Lookup_value будет ссылкой на местоположение или ячейку для поискового запроса, который мы хотим найти в базе данных.
- В диалоговом окне функции VLOOKUP щелкните строку Col_index_num .
- Введите имя функции match , а затем открытую круглую скобку “ (”
- Нажмите на ячейку E2 , чтобы ввести ссылку на эту ячейку в диалоговое окно.
- Введите запятую “,” после ссылки на ячейку E3 , чтобы завершить ввод аргумента функции Lookup_value функции MATCH.
- Оставьте открытым диалоговое окно функции VLOOKUP для следующего шага в учебнике.
На последнем этапе учебника значения Lookup_value будут введены в ячейки D2 и E2 рабочего листа.
Добавление 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
Добавление типа соответствия и завершение функции 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 возвращал точное соответствие для данных, которые мы ищем
- Нажмите OK, чтобы завершить формулу двумерного поиска и закрыть диалоговое окно
- Поскольку мы еще не ввели критерии поиска в ячейки D2 и E2, в ячейке F2 будет присутствовать ошибка # N/A.
- Эта ошибка будет исправлена на следующем шаге учебника, когда мы добавим критерии поиска на следующем шаге учебника.
Тестирование формулы двустороннего поиска
Тестирование формулы двустороннего поиска
Чтобы использовать формулу двустороннего поиска, чтобы найти данные о ежемесячных продажах для различных файлов cookie, перечисленных в массиве таблиц, введите имя файла cookie в ячейку D2, месяц в ячейку E2 и нажмите клавишу ВВОД на клавиатуре.
Данные о продажах будут отображаться в ячейке F2.
- Нажмите на ячейку D2 в вашем рабочем листе.
- Введите Овсянка в ячейку D2 и нажмите ENTER на клавиатуре.
- Нажмите на ячейку E2 .
- Введите Февраль в ячейку E2 и нажмите ENTER на клавиатуре.
- Значение 1 345 $ – сумма продаж печенья овсянки в феврале – должно отображаться в ячейке F2.
- На этом этапе ваш рабочий лист должен соответствовать примеру на странице 1 этого урока.
- Далее протестируйте формулу поиска, введя любую комбинацию типов файлов cookie и месяцев, присутствующих в массиве Table_array, и цифры продаж должны отображаться в ячейке F2.
- Последний шаг в руководстве посвящен копированию формулы поиска с помощью ручки заполнения.
Если в ячейке F2 появляется сообщение об ошибке, например #REF! , этот список сообщений об ошибках VLOOKUP может помочь вам определить причину проблемы.
Копирование двумерной формулы поиска с помощью ручки заливки
Копирование двумерной формулы поиска с помощью ручки заливки
Чтобы упростить сравнение данных за разные месяцы или разные файлы cookie, формулу поиска можно скопировать в другие ячейки, чтобы одновременно отображать несколько сумм.
Поскольку данные располагаются в виде регулярного шаблона на листе, мы можем скопировать формулу поиска в ячейке F2 в ячейку F3.
По мере копирования формулы Excel обновит относительные ссылки на ячейки, чтобы отразить новое местоположение формулы. В этом случае D2 становится D3, а E2 становится E3,
Кроме того, Excel сохраняет абсолютную ссылку на ячейку одинаковой, поэтому абсолютный диапазон $ D $ 5: $ G $ 5 остается неизменным при копировании формулы.
Существует несколько способов копирования данных в Excel, но, вероятно, самый простой способ – использовать Fill Handle.
- Нажмите на ячейку D3 на рабочем листе.
- Введите Овсянка в ячейку D3 и нажмите клавишу ENTER на клавиатуре.
- Нажмите на ячейку E3
- Введите March в ячейку E3 и нажмите клавишу ENTER на клавиатуре.
- Нажмите на ячейку F2 , чтобы сделать ее активной.
- Поместите указатель мыши на черный квадрат в правом нижнем углу.Указатель изменится на знак плюс “+” – это ручка заполнения
- Нажмите левую кнопку мыши и перетащите маркер заливки в ячейку F3.
- Отпустите кнопку мыши, и ячейка F3 должна содержать двумерную формулу поиска
- Значение $ 1 287 – сумма продаж печенья из овсянки в марте – должно отображаться в ячейке F3