Как использовать функцию ВПР в Excel

Опубликовано: 2020-06-04

У вас когда-нибудь была большая электронная таблица с данными в Excel, и вам нужен простой способ фильтровать и извлекать из нее конкретную информацию? Если вы научитесь использовать функцию ВПР в Excel, вы сможете выполнять этот поиск с помощью всего лишь одной мощной функции Excel.

Функция ВПР в Excel многих пугает, потому что у нее много параметров и есть несколько способов ее использования. В этой статье вы узнаете обо всех способах использования функции ВПР в Excel и о том, почему эта функция настолько эффективна.

Оглавление

    Параметры ВПР в Excel

    Когда вы начнете вводить =ВПР( в любую ячейку Excel, вы увидите всплывающее окно со всеми доступными параметрами функции.

    Давайте рассмотрим каждый из этих параметров и их значение.

    • lookup_value : значение, которое вы ищете в электронной таблице.
    • table_array : диапазон ячеек на листе, в котором вы хотите выполнить поиск.
    • col_index_num : столбец, из которого вы хотите получить результат.
    • [range_lookup] : режим соответствия (TRUE = приблизительно, FALSE = точно)

    Эти четыре параметра позволяют выполнять множество различных полезных операций поиска данных в очень больших наборах данных.

    Простой пример ВПР Excel

    ВПР не является одной из основных функций Excel, которые вы могли изучить, поэтому для начала рассмотрим простой пример.

    В следующем примере мы будем использовать большую таблицу результатов SAT для школ в США. Эта таблица содержит более 450 школ, а также индивидуальные результаты SAT по чтению, математике и письму. Не стесняйтесь загружать, чтобы следовать. Существует внешнее соединение, которое извлекает данные, поэтому при открытии файла вы получите предупреждение, но это безопасно.

    Поиск интересующей вас школы в таком большом наборе данных займет очень много времени.

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

    Затем вам нужно будет использовать функцию ВПР в Excel, чтобы эти три поля работали. В поле Чтение создайте функцию ВПР следующим образом:

    1. Введите =ВПР(
    2. Выберите поле School, в данном примере это I2 . Введите запятую.
    3. Выделите весь диапазон ячеек, содержащих данные, которые вы хотите найти. Введите запятую.

    Когда вы выбираете диапазон, вы можете начать со столбца, который вы используете для поиска (в данном случае столбец с названием школы), а затем выбрать все остальные столбцы и строки, содержащие данные.

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

    1. Затем, чтобы получить оценку по чтению, вам нужно выбрать 3-й столбец из крайнего левого выбранного столбца. Итак, введите 3 , а затем введите еще одну запятую.
    2. Наконец, введите FALSE для точного совпадения и закройте функцию с помощью ) .

    Ваша окончательная функция ВПР должна выглядеть примерно так:

     =ВПР(I2,B2:G461,3,ЛОЖЬ)

    Когда вы впервые нажмете Enter и завершите функцию, вы заметите, что поле Reading будет содержать #N/A .

    Это связано с тем, что поле «Школа» пустое и функция ВПР ничего не может найти. Однако, если вы введете название любой средней школы, которую хотите найти, вы увидите правильные результаты из этой строки для оценки чтения.

    Что делать, если функция ВПР чувствительна к регистру

    Вы можете заметить, что если вы не введете название школы в том же регистре, что и в наборе данных, вы не увидите никаких результатов.

    Это связано с тем, что функция ВПР чувствительна к регистру. Это может раздражать, особенно для очень большого набора данных, где столбец, в котором вы просматриваете, не соответствует тому, как пишутся заглавные буквы.

    Чтобы обойти это, вы можете заставить то, что вы ищете, переключиться на нижний регистр, прежде чем искать результаты. Для этого создайте новый столбец рядом с искомым столбцом. Введите функцию:

     = ОТРЕЗАТЬ (НИЖЕ (B2))

    Это сделает название школы строчными и удалит все посторонние символы (пробелы), которые могут быть слева или справа от названия.

    Удерживая нажатой клавишу Shift, поместите курсор мыши в правый нижний угол первой ячейки, пока он не изменится на две горизонтальные линии. Дважды щелкните мышью, чтобы автоматически заполнить весь столбец.

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

    Теперь, когда все ваши данные очищены в этом новом столбце, немного измените функцию ВПР в Excel, чтобы использовать этот новый столбец вместо предыдущего, начав диапазон поиска с C2 вместо B2.

     =ВПР(I2,C2:G461,3,ЛОЖЬ)

    Теперь вы заметите, что если вы всегда вводите поиск строчными буквами, вы всегда будете получать хорошие результаты поиска.

    Это удобный совет по работе с Excel, позволяющий преодолеть тот факт, что функция ВПР чувствительна к регистру.

    ВПР Приблизительное совпадение

    В то время как пример LOOKUP с точным соответствием, описанный в первом разделе этой статьи, довольно прост, приблизительное совпадение немного сложнее.

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

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

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

    Теперь, используя функцию ВПР и приблизительное совпадение, вы можете найти правильную буквенную оценку, соответствующую правильному числовому диапазону.

    В этой функции ВПР:

    • lookup_value : F2, окончательная усредненная оценка
    • table_array : I2: J8, диапазон поиска буквенной оценки
    • index_column : 2, второй столбец в таблице поиска
    • [range_lookup] : TRUE, приблизительное совпадение

    После того, как вы закончите работу с функцией ВПР в G2 и нажмете Enter, вы сможете заполнить остальные ячейки, используя тот же подход, который описан в предыдущем разделе. Вы увидите, что все буквенные оценки заполнены правильно.

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

    Таким образом, «C» должна быть буквой, назначенной нижнему диапазону (75), а B назначена нижней части (минимуму) своего собственного диапазона букв. ВПР «найдет» результат для 60 (D) как ближайшее приблизительное значение для любого значения в диапазоне от 60 до 75.

    ВПР в Excel — очень мощная функция, которая доступна уже давно. Это также полезно для поиска совпадающих значений в любом месте рабочей книги Excel.

    Однако имейте в виду, что пользователи Microsoft с ежемесячной подпиской на Office 365 теперь имеют доступ к более новой функции XLOOKUP. Эта функция имеет больше параметров и дополнительную гибкость. Пользователям с полугодовой подпиской нужно будет дождаться выпуска обновления в июле 2020 года.