Объединив в Excel функции ВПРА с функцией MATCH , мы можем создать то , что известно как двусторонний или двухмерной поиск формула , которая позволяет легко перекрестные ссылки два полех информации в базе данных или таблицах данных.
Формула двустороннего поиска полезна, когда вы хотите найти или сравнить результаты для различных ситуаций.
В примере, показанном на рисунке выше, формула поиска позволяет легко получить данные о продажах для разных файлов cookie в разные месяцы, просто изменив имя файла cookie и месяц в правильных ячейках.
Найти данные в точке пересечения строки и столбца
Двусторонний поиск в Excel с помощью VLOOKUP.
Этот урок разбит на две части. Выполнение шагов, перечисленных в каждой части, создает формулу двустороннего поиска, показанную на рисунке выше.
Учебное пособие включает в себя вложение функции MATCH внутри VLOOKUP .
Вложение функции включает в себя ввод второй функции в качестве одного из аргументов для первой функции.
В этом руководстве функция MATCH будет введена в качестве аргумента номера индекса столбца для VLOOKUP.
Ввод данных учебника
Двусторонний поиск в Excel с помощью VLOOKUP.
Чтобы выполнить шаги, описанные в руководстве, введите данные, показанные на рисунке выше, в следующие ячейки .
- Введите верхний диапазон данных в ячейки от D1 до F1
- Введите второй диапазон в ячейки от D4 до G8
Строки 2 и 3 оставлены пустыми, чтобы соответствовать критериям поиска и формуле поиска, созданной в этом руководстве.
Учебное пособие не включает форматирование, видимое на изображении, но это не повлияет на работу формулы поиска.
Информация о параметрах форматирования, аналогичных приведенным выше, доступна в этом учебном пособии по форматированию в Excel .
- Введите данные, как показано на рисунке выше, в ячейки от D1 до G8
Создание именованного диапазона для таблицы данных
Создание именованного диапазона в Excel.
Именованный диапазон является простым способом , чтобы обратиться к диапазону данных в формуле. Вместо того, чтобы вводить ссылки на ячейки для данных, вы можете просто ввести имя диапазона.
Второе преимущество использования именованного диапазона состоит в том, что ссылки на ячейки для этого диапазона никогда не меняются, даже если формула копируется в другие ячейки на листе.
- Выделите ячейки от D5 до G8 на листе, чтобы выбрать их
- Нажмите на поле с именем, расположенное над столбцом A
- Введите «таблица» (без кавычек) в поле «Имя»
- Нажмите клавишу ВВОД на клавиатуре
- Ячейки от D5 до G8 теперь имеют диапазон имен «таблица». Мы будем использовать имя для аргумента массива таблицы VLOOKUP позже в руководстве
Открытие диалогового окна VLOOKUP
Открытие диалогового окна VLOOKUP.
Хотя можно просто ввести нашу формулу поиска непосредственно в ячейку на рабочем листе, многим людям сложно сохранить синтаксис прямым, особенно для сложной формулы, такой как та, которую мы используем в этом руководстве.
Альтернативой в этом случае является использование диалогового окна VLOOKUP . Почти все Excel в функции есть диалоговое окно , которое позволяет вводить каждый из аргументов функции на отдельной строке.
- Нажмите на ячейку F2 рабочего листа — место, где будут отображаться результаты двухмерной формулы поиска
- Нажмите на вкладку » Формулы » на ленте.
- Выберите опцию « Поиск и ссылка» на ленте, чтобы открыть раскрывающийся список функций.
- Нажмите на VLOOKUP в списке, чтобы открыть диалоговое окно функции.
Ввод аргумента значения поиска
Двусторонний поиск в Excel с помощью VLOOKUP.
В нашем примере значение поиска относится к типу cookie, информацию о котором мы хотим найти.
Допустимые типы данных для значения поиска :
- текстовые данные
- логическое значение (истина или ложь только)
- число
- ссылка на ячейку со значением в рабочем листе
В этом примере мы введем ссылку на ячейку, в которой будет находиться имя файла cookie — ячейка D2.
- Нажмите на строку lookup_value в диалоговом окне.
- Нажмите на ячейку D2, чтобы добавить эту ссылку на ячейку в строку lookup_value . Это ячейка, в которую мы будем вводить имя файла cookie, о котором мы ищем информацию.
Ввод аргумента массива таблицы
Двусторонний поиск в Excel с помощью VLOOKUP.
Массив таблиц — это таблица данных, в которой формула поиска ищет нужную нам информацию.
Массив таблицы должен содержать как минимум два столбца данных .
- первый столбец содержит аргумент значения поиска (предыдущий шаг в руководстве)
- второй и любые дополнительные столбцы будут искать по формуле поиска, чтобы найти указанную нами информацию.
Аргумент массива таблицы должен быть введен либо как диапазон, содержащий ссылки на ячейки для таблицы данных, либо как имя диапазона .
В этом примере мы будем использовать имя диапазона, созданное на шаге 3 этого урока.
- Нажмите на строку table_array в диалоговом окне
- Введите «таблица» (без кавычек), чтобы ввести имя диапазона для этого аргумента
- Оставьте открытым диалоговое окно функции VLOOKUP для следующей части урока