Функция ВПР в Эксель

ЛогоРедактор Эксель – очень мощная программа для работы с таблицами. Иногда бывает так, что приходится работать с большим объемом данных. В таких случаях используются различные инструменты поиска информации. Функция «ВПР» в Excel – одна из самых востребованных для этой цели. Рассмотрим её более внимательно.

Расшифровка

Большинство пользователей не знают, что аббревиатура «ВПР» расшифровывается как «Вертикальный Просмотр». На английском функция называется «VLOOKUP», которая означает «Vertical LOOK UP»

Как пользоваться функцией

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

  1. Создайте таблицу, по которой можно будет сделать какой-нибудь поиск информации.

Начальная таблица

  1. Добавим несколько полей, которые будем использовать для демонстрации формул.

Добавление полей

  1. В поле «Искомая фамилия» введем какую-нибудь на выбор из тех, что есть в таблице.
  2. Затем переходим на следующую ячейку и вызываем окно «Вставка функции».
  3. Выбираем категорию «Полный алфавитный перечень».
  4. Находим нужную нам функцию «ВПР». Для продолжения нажимаем на кнопку «OK».

Полный алфавитный перечень

  1. Затем нас попросят указать «Аргументы функции»:
    • В поле «Искомое выражение» указываем ссылку на ячейку, в которой мы написали нужную нам фамилию.
    • Для того чтобы заполнить поле «Таблица», достаточно просто выделить все наши данные при помощи мышки. Ссылка подставится автоматически.
    • В графе «Номер столбца» указываем номер 2, поскольку в нашем случае имя находится во второй колонке.
    • Последнее поле может принимать значения «0» или «1» («ЛОЖЬ» и «ИСТИНА»). Если укажете «0», то редактор будет искать точное совпадение по заданным критериям. Если же «1» – то во время поиска не будут учитываться полные совпадения.
  2. Для сохранения кликните на кнопку «OK».
Читайте также:  Как сделать выпадающий список в Эксель

Сохранение результата

  1. В результате этого мы получили имя «Томара». То есть, всё правильно.

Томара

Теперь нужно воспользоваться этой же формулой и для остальных полей. Простое копирование ячейки при помощи [knopka]Ctrl[/knopka]+[knopka]C[/knopka] и [knopka]Ctrl[/knopka]+[knopka]V[/knopka] не подойдёт, поскольку у нас используются относительные ссылки и каждый раз будет меняться номер столбца.

Для того чтобы всё сработало правильно, нужно сделать следующее:

  1. Кликните на ячейку с первой функцией.
  2. Перейдите в строку ввода формул.
  3. Скопируйте текст при помощи [knopka]Ctrl[/knopka]+[knopka]C[/knopka].

Копирование текста

  1. Сделайте активной следующее поле.
  2. Снова перейдите в строку ввода формул.
  3. Нажмите на горячие клавиши [knopka]Ctrl[/knopka]+[knopka]V[/knopka].

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

Второй способ замены

  1. Затем меняем номер столбца на нужный. В нашем случае это 3. Нажимаем на клавишу [knopka]Enter[/knopka].

Замена номера столбца

  1. Благодаря этому мы видим, что данные из столбца «Год рождения» определились правильно.

Год рождения

  1. После этого повторяем те же самые действия для последнего поля, но с корректировкой номера нужного столбца.

Корректировка номеров

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

То есть нумерация начинается не с начала листа, а с начала указанной области ячеек.

Как использовать функцию «ВПР» для сравнения данных

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

  1. Добавим второй лист с точно такой же таблицей (копировали при помощи горячих клавиш [knopka]Ctrl[/knopka]+[knopka]C[/knopka] и [knopka]Ctrl[/knopka]+[knopka]V[/knopka]).
  2. Повысим стажеров до «Младший сотрудник». Эта информация будет отправной точкой для сравнения.

Младший сотрудник

  1. Добавим ещё один столбец в нашу старую таблицу.

Новый столбец

  1. Переходим в первую клетку нового столбца и вводим там следующую формулу.

[kod]=ВПР($B$3:$B$11;Лист2!$B$3:$E$11;4;ЛОЖЬ)[/kod]

Она означает:

  • $B$3:$B$11 – для поиска используются все значения первой колонки (применяются абсолютные ссылки);
  • Лист2! – эти значения нужно искать на листе с указанным названием;
  • $B$3:$E$11 – таблица, в которой нужно искать (диапазон ячеек);
  • 4 – номер столбца в указанной области данных;
  • ЛОЖЬ – искать точные совпадения.
  1. Новая информация выведется в том месте, где мы указали формулу.
  2. Результат будет следующим.

Значение таблицы

  1. Теперь продублируйте эту формулу в остальные ячейки. Для этого нужно потянуть мышкой за правый нижний угол исходной клетки.

Растягивание таблицы

  1. В итоге мы увидим, что написанная нами формула работает корректно, поскольку все новые должности скопировались как положено.

Успешное копирование

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

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

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

Функция «ВПР» и выпадающие списки

Рассмотрим примеры использования этих двух инструментов одновременно. Для этого нужно выполнить следующие действия.

  1. Перейдите в ячейку, в которой происходит выбор фамилии.
  2. Откройте вкладку «Данные».
  3. Кликните на указанный инструмент и выберите пункт «Проверка данных».

Проверка данных

  1. В новом окне в графе «Тип данных» выберите пункт «Список».

Тип данных

  1. После этого появится новое поле «Источник». Кликните туда.
  2. Затем выделите первый столбец. Ссылка на ячейки подставится автоматически.
  3. Для продолжения нажмите на «OK».

Источник

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

Выбор варианта

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

Новые данные

Сводные таблицы такой возможностью похвастаться не могут. Функция «ВПР» – очень мощный инструмент для поиска любой информации.

Ошибки #Н/Д

С подобной проблемой сталкиваются многие пользователи, которые только начинают пользоваться этой функцией. Как правило, ошибка «#Н/Д» возникает в следующих случаях:

  • вы включили точный поиск (последний параметр 0/ЛОЖЬ), а это значение в таблице отсутствует;
  • вы включили неточный поиск (последний параметр 1/ИСТИНА), но при этом данные не отсортированы по возрастанию (если используется приблизительный поиск, то разработчики Microsoft рекомендуют использовать упорядоченные данные);
  • аргументы функции имеют различный формат (например, что-то в текстовом виде, а остальное – в числовом);
  • в формуле присутствуют опечатки или лишние символы (пробелы, непечатаемые знаки, переносы и так далее).

Отличие от функции «ГПР»

Данный инструмент практически точно такой же, только ищет по горизонтали. Более подробно о нем можно узнать на официальном сайте Microsoft.

Описание

Заключение

В данной статье мы пошагово рассмотрели, как пользоваться функцией «ВПР» в редакторе Excel. Кроме этого, было показано несколько примеров. Данная инструкция ориентирована на новичков (чайников).

Если у вас что-то не получается, возможно, вы неправильно указываете аргументы функции. Например, неправильный номер столбца или неверный диапазон ячеек. Попробуйте повторить описанные выше действия заново, но на этот раз более внимательно. И у вас всё получится.

Видеоинструкция

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

Оцените статью
OS Helper
Добавить комментарий

  1. Татьяна

    Спасибо, очень толково все расписано. Только «Тамара» пишется через два «а». А в остальном — замечательно.

    Ответить
    1. Пожалуйста! Но это не обычная Томара. Дело в том что у нее и в паспорте так. Девушка из Узбекистана и там имена могут варьироваться на букву-две )

      Ответить
  2. Аноним

    спасибки все ясно обьяснили

    Ответить