Запуск функции Вложенная функция MATCH
Вернуться в Часть 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 линия.
- Введите имя функции матч а затем открытый круглый кронштейн " ( '
- Нажмите на ячейку E2 для ввода этой ссылки на ячейку в диалоговом окне.
- Введите запятую ' , ' после ссылки на ячейку E3 для завершения ввода функции MATCH lookup_Value аргумент.
- Оставьте диалоговое окно функции VLOOKUP открытым для следующего шага в учебнике.
На последнем этапе учебного процесса Lookup_values будут введены в ячейки D2 и E2 рабочего листа.
02 из 06Добавление 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 для завершения ввода функции MATCH Lookup_Array аргумент.
Добавление типа соответствия и завершение функции 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 line, введите нуль " 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 равным Ложь .
- Нажми на диапазон_просмотра строка в диалоговом окне
- Введите слово Ложь в этой строке, чтобы указать, что мы хотим, чтобы VLOOKUP возвращал точное соответствие для данных, которые мы ищем
- Нажмите «ОК», чтобы завершить формуляр двумерного поиска и закрыть диалоговое окно
- Поскольку мы еще не ввели критерии поиска в ячейки D2 и E2, в ячейке F2 появится ошибка # N / A
- Эта ошибка будет исправлена на следующем этапе учебника, когда мы добавим критерии поиска на следующем этапе учебника.
Тестирование формулы двустороннего поиска
Тестирование формулы двустороннего поиска
Чтобы использовать формулу поиска в двух направлениях для поиска ежемесячных данных о продажах для разных файлов cookie, перечисленных в массиве таблицы, введите имя cookie в ячейку D2, месяц в ячейку E2 и нажмите клавишу ENTER на клавиатуре.
Данные о продажах будут отображаться в ячейке F2.
- Нажмите на ячейку D2 на листе
- Тип Овсяная каша в ячейку D2 и нажмите ВОЙТИ клавиша на клавиатуре
- Нажмите на ячейку E2
- Тип февраль в ячейку E2 и нажмите ВОЙТИ клавиша на клавиатуре
- Значение $1,345 - сумма продаж для печенья из овсяной муки в феврале - должна отображаться в ячейке F2
- На этом этапе ваш рабочий лист должен соответствовать примеру на стр. 1 данного руководства
- Еще раз проверьте формулу поиска, набрав любую комбинацию типов файлов cookie и месяцев, присутствующих в Table_array, и цифры продаж должны отображаться в ячейке F2
- Последний шаг в учебнике охватывает копирование формулы поиска с помощью Fill Handle.
Если сообщение об ошибке, такое как #REF! появляется в ячейке F2, этот список сообщений об ошибках VLOOKUP может помочь вам определить, в чем проблема.
06 из 06Копирование двухмерной формулы поиска с помощью ручки заполнения
Копирование двухмерной формулы поиска с помощью ручки заполнения
Чтобы упростить сравнение данных для разных месяцев или разных файлов cookie, формула поиска может быть скопирована в другие ячейки, чтобы одновременно отображать несколько сумм.
Поскольку данные выложены в виде обычного шаблона на листе, мы можем скопировать формулу поиска в ячейку F2 в ячейку F3.
По мере копирования формулы Excel будет обновлять относительные ссылки на ячейки, чтобы отразить новое местоположение формулы. В этом случае D2 становится D3, а E2 становится E3,
Кроме того, Excel сохраняет абсолютную ссылку на ячейки одинаково, поэтому абсолютный диапазон $ D $ 5: $ G $ 5 остается неизменным при копировании формулы.
Существует несколько способов копирования данных в Excel, но, вероятно, самым простым способом является использование Fill Handle.
- Нажмите на ячейку D3 на листе
- Тип Овсяная каша в ячейку D3 и нажмите ВОЙТИ клавиша на клавиатуре
- Нажмите на ячейку E3
- Тип марш в ячейку E3 и нажмите ВОЙТИ клавиша на клавиатуре
- Нажмите на ячейку F2, чтобы сделать ее активной ячейкой
- Поместите указатель мыши на черный квадрат в нижнем правом углу. Указатель изменится на знак плюса «+» - это Fill Handle
- Щелкните левой кнопкой мыши и перетащите дескриптор заполнения в ячейку F3
- Отпустите кнопку мыши, и ячейка F3 должна содержать двумерную форму поиска
- В ячейке F3 должно отображаться значение в размере 1 287 долларов США - сумма продаж кукурузы овсянки в марте месяце -
