Двухсторонний поиск в Excel с использованием VLOOKUP, часть 2

Запуск функции вложенного 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 будет ссылкой на местоположение или ячейку для поискового запроса, который мы хотим найти в базе данных.

1. В диалоговом окне функции VLOOKUP щелкните строку Col_index_num .
2. Введите имя функции match , а затем открытую круглую скобку “ (”
3. Нажмите на ячейку E2 , чтобы ввести ссылку на эту ячейку в диалоговое окно.
4. Введите запятую “,” после ссылки на ячейку E3 , чтобы завершить ввод аргумента функции Lookup_value функции MATCH.
5. Оставьте открытым диалоговое окно функции 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.

1. При необходимости щелкните строку Col_index_num после запятой, чтобы поместить точку вставки в конец текущей записи.
2. Выделите ячейки от D5 до G5 на рабочем листе, чтобы ввести эти ссылки на ячейки в качестве диапазона, который должна выполнять функция.
3. Нажмите клавишу F4 на клавиатуре, чтобы изменить этот диапазон на абсолютные ссылки на ячейки. Это позволит скопировать заполненную формулу поиска в другие места на рабочем листе на последнем шаге руководства.
4. Введите запятую “,” после ссылки на ячейку 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.

1. После второй запятой в строке Col_index_num введите ноль « 0 », поскольку мы хотим, чтобы вложенная функция возвращала точное совпадение с месяцем, введенным в ячейку E2.
2. Введите закрывающую круглую скобку “) “, чтобы завершить функцию MATCH.
3. Оставьте открытым диалоговое окно функции 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 .

1. Нажмите на строку Range_lookup в диалоговом окне.
2. Введите слово False в этой строке, чтобы указать, что мы хотим, чтобы VLOOKUP возвращал точное соответствие для данных, которые мы ищем
3. Нажмите OK, чтобы завершить формулу двумерного поиска и закрыть диалоговое окно
4. Поскольку мы еще не ввели критерии поиска в ячейки D2 и E2, в ячейке F2 будет присутствовать ошибка # N/A.
5. Эта ошибка будет исправлена ​​на следующем шаге учебника, когда мы добавим критерии поиска на следующем шаге учебника.

Тестирование формулы двустороннего поиска

Тестирование формулы двустороннего поиска

Чтобы использовать формулу двустороннего поиска, чтобы найти данные о ежемесячных продажах для различных файлов cookie, перечисленных в массиве таблиц, введите имя файла cookie в ячейку D2, месяц в ячейку E2 и нажмите клавишу ВВОД на клавиатуре.

Данные о продажах будут отображаться в ячейке F2.

1. Нажмите на ячейку D2 в вашем рабочем листе.
2. Введите Овсянка в ячейку D2 и нажмите ENTER на клавиатуре.
3. Нажмите на ячейку E2 .
4. Введите Февраль в ячейку E2 и нажмите ENTER на клавиатуре.
5. Значение 1 345 $ – сумма продаж печенья овсянки в феврале – должно отображаться в ячейке F2.
6. На этом этапе ваш рабочий лист должен соответствовать примеру на странице 1 этого урока.
7. Далее протестируйте формулу поиска, введя любую комбинацию типов файлов cookie и месяцев, присутствующих в массиве Table_array, и цифры продаж должны отображаться в ячейке F2.
8. Последний шаг в руководстве посвящен копированию формулы поиска с помощью ручки заполнения.

Если в ячейке F2 появляется сообщение об ошибке, например #REF! , этот список сообщений об ошибках VLOOKUP может помочь вам определить причину проблемы.

Копирование двумерной формулы поиска с помощью ручки заливки

Копирование двумерной формулы поиска с помощью ручки заливки

Чтобы упростить сравнение данных за разные месяцы или разные файлы cookie, формулу поиска можно скопировать в другие ячейки, чтобы одновременно отображать несколько сумм.

Поскольку данные располагаются в виде регулярного шаблона на листе, мы можем скопировать формулу поиска в ячейке F2 в ячейку F3.

По мере копирования формулы Excel обновит относительные ссылки на ячейки, чтобы отразить новое местоположение формулы. В этом случае D2 становится D3, а E2 становится E3,

Кроме того, Excel сохраняет абсолютную ссылку на ячейку одинаковой, поэтому абсолютный диапазон $ D $ 5: $ G $ 5 остается неизменным при копировании формулы.

Существует несколько способов копирования данных в Excel, но, вероятно, самый простой способ – использовать Fill Handle.

1. Нажмите на ячейку D3 на рабочем листе.
2. Введите Овсянка в ячейку D3 и нажмите клавишу ENTER на клавиатуре.
3. Нажмите на ячейку E3
4. Введите March в ячейку E3 и нажмите клавишу ENTER на клавиатуре.
5. Нажмите на ячейку F2 , чтобы сделать ее активной.
6. Поместите указатель мыши на черный квадрат в правом нижнем углу.Указатель изменится на знак плюс “+” – это ручка заполнения
7. Нажмите левую кнопку мыши и перетащите маркер заливки в ячейку F3.
8. Отпустите кнопку мыши, и ячейка F3 должна содержать двумерную формулу поиска
9. Значение $ 1 287 – сумма продаж печенья из овсянки в марте – должно отображаться в ячейке F3