ВПР в Excel - статьи TechTV

Содержание

Многие люди пытаются использовать Excel в качестве базы данных. Хотя он может работать как база данных, некоторые задачи, которые были бы очень простыми в программе базы данных, в Excel довольно сложны. Одна из этих задач - сопоставление двух списков на основе общего поля; это легко сделать с помощью ВПР в Excel. Вы найдете функцию VLOOKUP чрезвычайно полезной, поэтому ознакомьтесь с примером того, когда и как использовать эту функцию ниже.

Предположим, ваше туристическое агентство отправляет вам отчет на конец месяца обо всех местах, где побывали ваши сотрудники. В отчете используются коды аэропортов вместо названий городов. Было бы полезно, если бы вы могли легко ввести настоящее название города, а не просто код.

В Интернете вы найдете и импортируете список с названием города для каждого кода аэропорта.

Но как получить эту информацию по каждой записи в отчете?

  1. Используйте функцию ВПР. VLOOKUP означает «вертикальный просмотр». Его можно использовать в любое время, когда у вас есть список данных с ключевым полем в крайнем левом столбце.
  2. Начните вводить функцию, =VLOOKUP(. Нажмите Ctrl + A, чтобы получить справку по функции.
  3. ВПР требует четырех параметров. Во-первых, это код города в исходном отчете. В этом примере это будет ячейка D4.
  4. Следующий параметр - это диапазон с вашей таблицей поиска. Выделите диапазон. Обязательно используйте F4, чтобы диапазон был абсолютным. (Абсолютная ссылка имеет знак доллара перед номером столбца и номером строки. При копировании формулы ссылка по-прежнему будет указывать на I3: J351.
  5. Третий параметр сообщает Excel, в каком столбце находится название города. В диапазоне I3: J351 название города находится в столбце 2. Введите 2 для этого параметра.
  6. Четвертый параметр сообщает Excel, подходит ли «близкое» совпадение. В этом случае это не так, поэтому введите False.
  7. Щелкните ОК, чтобы заполнить формулу. Перетащите маркер заполнения, чтобы скопировать формулу вниз.
  8. Поскольку вы тщательно ввели абсолютные формулы, вы можете скопировать столбец E в столбец D, чтобы получить город назначения. В этом случае все вылеты выполняются из международного аэропорта Пирсон в Торонто.

Хотя этот пример сработал идеально, когда зрители используют ВПР, это обычно означает, что они сопоставляют списки, полученные из разных источников. Когда списки поступают из разных источников, всегда могут быть небольшие различия, из-за которых списки трудно сопоставить. Вот три примера того, что может пойти не так и как их исправить.

  1. В одном списке есть тире, а в другом - нет. Используйте =SUBSTITUTE()функцию, чтобы убрать тире. В первый раз, когда вы попробуете VLOOKUP, вы получите ошибку N / A.

    Чтобы удалить тире в формуле, используйте формулу ЗАМЕНА. Используйте 3 аргумента. Первый аргумент - это ячейка, содержащая значение. Следующий аргумент - это текст, который вы хотите изменить. Последний аргумент - текст замены. В этом случае вы хотите заменить тире на ничто, поэтому формула такова =SUBSTITUTE(A4,"-","").

    Вы можете заключить эту функцию в ВПР, чтобы получить описание.

  2. Этот тонкий, но очень распространенный. В одном списке после записи есть конечный пробел. Используйте = TRIM (), чтобы удалить лишние пробелы. Когда вы впервые вводите формулу, вы обнаруживаете, что все ответы являются ошибками N / A. Вы точно знаете, что значения есть в списке, и с формулой все в порядке.

    Одна стандартная вещь для проверки - перейти к ячейке со значением поиска. Нажмите F2, чтобы перевести ячейку в режим редактирования. В режиме редактирования вы можете увидеть, что курсор находится на расстоянии одного пробела от последней буквы. Это означает, что в записи есть конечный пробел.

    Для решения проблемы воспользуйтесь функцией ОБРЕЗАТЬ. =TRIM(D4)удалит начальные и конечные пробелы и заменит любые внутренние двойные пробелы на одиночный. В этом случае TRIM отлично работает, чтобы удалить конечное пространство. =VLOOKUP(TRIM(D4),$I$3:$J$351,2,FALSE)это формула.

  3. В примечаниях к шоу я упомянул бонусный совет: как заменить результат # N / A для пропущенных значений пробелом. Если значение поиска отсутствует в таблице поиска, функция ВПР вернет ошибку Н / Д.

    Эта формула использует =ISNA()функцию, чтобы определить, является ли результат формулы ошибкой N / A. Если вы получите сообщение об ошибке, второй аргумент в функции ЕСЛИ скажет Excel ввести любой текст, который вы хотите.

    =IF(ISNA(VLOOKUP(D4,$I$3:$J$351,2,FALSE)),"Invalid Code",VLOOKUP(D4,$I$3:$J$351,2,FALSE))

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

Интересные статьи...