В этом видео мы рассмотрим, как использовать ВПР для поиска значений в таблице Excel.
На этом листе у меня есть таблица с данными о сотрудниках с именем Table1.
Чтобы проиллюстрировать, как работать с ВПР, когда исходные данные находятся в таблице, я настрою формулы справа для извлечения данных из таблицы, сопоставления по идентификатору сотрудника.
Сначала я выберу заголовок таблицы и использую специальную вставку с транспонированием, чтобы получить значения полей.
Затем я добавлю форматирование и значение идентификатора, чтобы у меня было с чем сравнивать.
Теперь напишу первую формулу ВПР.
Для поиска мне нужно заблокировать значение из K4, чтобы оно не менялось, когда я копирую формулу.
Для массива таблиц мне нужна сама таблица поиска Table1.
Теперь, поскольку VLOOKUP смотрит только вправо, важно, чтобы поиск происходил слева от значений, которые мы хотим получить.
В этой таблице идентификатор - это крайний левый столбец, поэтому мы можем получить любое значение.
Для идентификатора столбца мне нужно 2, поскольку первое имя - это второй столбец в таблице.
Тип соответствия равен нулю или false, поскольку я хочу добиться точного совпадения.
Когда я ввожу формулу, мы получаем «Джули», что правильно.
Если я скопирую формулу в следующую строку, мне нужно будет изменить только номер столбца, чтобы получить фамилию.
И я могу сделать то же самое для всех других полей.
Вы можете спросить, есть ли простой способ избежать жесткого кодирования номера столбца в формуле?
Ответ положительный. Поскольку имена в столбце J соответствуют значениям в заголовке таблицы, я могу использовать функцию ПОИСКПОЗ, чтобы получить индекс для каждого поля.
Чтобы продемонстрировать, я использую ПОИСКПОЗ в столбце L.
Значение поиска берется из столбца J.
Массив - это заголовок таблицы.
Тип соответствия равен нулю, для точного соответствия.
Когда я копирую формулу вниз, я получаю числовой индекс для каждого поля.
Теперь мне просто нужно скопировать формулу MATCH в VLOOKUP, чтобы заменить жестко заданный индекс столбца.
Это пример вложенных функций внутри формулы.
Когда я копирую формулу, я получаю результат для каждого поля.
Я удалю вспомогательный столбец.
При изменении значения ID все работает.
А поскольку я использую таблицу, я могу легко добавить больше данных, и те же формулы будут работать без изменений.
Наконец, такой способ сопоставления дает действительно приятное преимущество: я могу легко изменять порядок полей в формулах вывода или в самой таблице, и формулы ВПР продолжают работать.
Курс
Таблицы ExcelСвязанные ярлыки
Копировать выделенные ячейки Ctrl + C ⌘ + C Показать диалоговое окно Специальная вставка Ctrl + Alt + V ⌘ + ⌃ + V Enter и переместиться вниз Enter Return Enter и переместиться вправо Tab Tab Вставить содержимое из буфера обмена Ctrl + V ⌘ + V Применить формат даты Ctrl + Shift + # ⌃ + ⇧ + # Удалить столбцы Ctrl + - ⌘ + - Выбрать таблицу Ctrl + A ⌘ + A Завершить запись и остаться в том же ячейка Ctrl + Enter ⌃ + Return Переключение абсолютных и относительных ссылок F4 ⌘ + T Выбор столбца таблицы Ctrl + Space ⌃ + Space
