Как пользоваться VLOOKUP в Эксель

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

Что означает эта аббревиатура

Данный инструмент на английском языке расшифровывается как «Vertical LOOK UP». В русской версии Экселя эта функция называется ВПР, которая в свою очередь означает «Вертикальный Просмотр».

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

Как использовать VLOOKUP в Excel

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

VLOOKUP в Экселе

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

Поля вывода

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

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

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

  1. После этого вас попросят указать аргументы функции. В поле «Искомое значение» указываем ссылку на ячейку с исходным словом. В графе «Таблица» нужно выделить все столбцы, в которых есть искомая информация. В аргументе «Номер столбца» необходимо указать, из какой именно колонки будут возвращаться результаты поиска.

Обратите внимание на то, что отчет происходит в пределах выделенной области в поле «Таблица».

  1. Интервальный просмотр отвечает за то, требуется ли точное совпадение результатов поиска (0 – полное совпадение, а 1 – допускаются погрешности и небольшие отклонения). Для завершения настроек нужно нажать на «OK».

Клик по OK

  1. Благодаря этим действиям функция найдет нужную строку и вставит соответствующее имя.

Результат работы

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

Растягивание

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

Копирование данных

Использование выпадающих списков

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

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

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

  1. Раскройте выпадающий список и выберите соответствующий пункт.

Выпадающий список

  1. Сразу после этого появится дополнительное поле. Кликните на него, затем выделите колонку значений, которые можно использовать для поиска информации. Для продолжения кликните на «OK».

Нажатие на OK

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

Выбор записи

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

Пример

  1. Все данные автоматически будут найдены.

Данные удалены

Поиск изменения данных при помощи VLOOKUP

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

  1. Для начала нужно создать ещё один столбец.

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

  1. Затем скопируем старую таблицу на другой лист. Именно там мы и будем вносить все обновления.

Копирование

  1. В качестве примера изменим какую-нибудь запись.

Изменение записи

  1. Возвращаемся на предыдущий лист. Переходим в первую колонку и вводим там следующую формулу (вам нужно будет отредактировать ссылки под свою таблицу).
=ВПР($B$3:$B$11;'Последние данные'!$B$3:$G$11;6;ЛОЖЬ)
  1. Затем нажимаем на клавишу Enter.

Нажатие на Enter

  1. Благодаря этому мы получим обновленное соответствующее значение из новой таблицы, но оно совпадает со старым.

Новые значения

  1. Копируем эту формулу во все остальные строки.

Копирование формулы

  1. Результат получается следующим.

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

Новый результат

Возможные ошибки

В большинстве случаев у новичков появляется ошибка «#Н/Д». Причем бороться с ней, как показывает практика, довольно сложно. На самом деле ничего страшного тут нет. Главное – найти причину и всё сразу же станет на свои места. Скорее всего, причина вашей проблемы заключается в следующем:

  • вы допустили ошибку при написании формулы;
  • у вас опечатка в слове для поиска;
  • включен неточный поиск (интервальный просмотр равен 1) и данные не отсортированы;
  • в аргументах функции используются различные типы данных;
  • неправильно указана таблица для поиска данных.

Версии MS Excel

Данную функцию можно использовать абсолютно во всех версиях этого редактора (2016, 2013, 2010, 2007). Она есть даже в 2003 году.

Excel 2003

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

Заключение

В данной статье мы рассмотрели принцип работы функции «VLOOKUP» в редакторе Excel. Обратите внимание на то, что для поиска какой-нибудь информации достаточно возможностей этого инструмента. Для реализации сложного поиска не обязательно использовать макросы на VBA. Всё можно сделать подручными средствами.

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

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

Если вам что-то непонятно или остались какие-нибудь вопросы, вы можете попробовать найти на них ответы в следующем видеоролике.

Читайте также:

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Adblock detector