Эксель – один из лучших современных редакторов, в котором можно работать с большими таблицами. В нем есть все необходимы функции и различные возможности. Большинство пользователей не умеют пользоваться всем арсеналом, который предлагает Excel. В рамках этой статьи мы рассмотрим, как пользоваться функцией VLOOKUP.
Что означает эта аббревиатура
Данный инструмент на английском языке расшифровывается как «Vertical LOOK UP». В русской версии Экселя эта функция называется ВПР, которая в свою очередь означает «Вертикальный Просмотр».
Данная функция выполняет вертикальный поиск по всем указанным строкам и вытаскивает значение с какого-нибудь фиксированного столбца. Давайте рассмотрим, как это должно работать на примерах.
Как использовать VLOOKUP в Excel
Для того чтобы продемонстрировать возможности этого инструмента, нужно будет создать какую-нибудь таблицу с небольшим набором данных.
Также нужно добавить несколько полей, в которых будут выводиться результаты поиска.
Для того чтобы составить нужную формулу, от вас потребуются следующие действия.
- Введите какое-нибудь слово, по которому мы будем осуществлять поиск остальных значений. Перейдите в соседнюю клетку. Вызовите окно «Вставка функции». Выберите категорию «Полный алфавитный перечень». Найдите там функцию «ВПР» и нажмите на «OK».
- После этого вас попросят указать аргументы функции. В поле «Искомое значение» указываем ссылку на ячейку с исходным словом. В графе «Таблица» нужно выделить все столбцы, в которых есть искомая информация. В аргументе «Номер столбца» необходимо указать, из какой именно колонки будут возвращаться результаты поиска.
Обратите внимание на то, что отчет происходит в пределах выделенной области в поле «Таблица».
- Интервальный просмотр отвечает за то, требуется ли точное совпадение результатов поиска (0 – полное совпадение, а 1 – допускаются погрешности и небольшие отклонения). Для завершения настроек нужно нажать на «OK».
- Благодаря этим действиям функция найдет нужную строку и вставит соответствующее имя.
- Наведите курсор в правый нижний угол ячейки. После того как изменится внешний вид указателя на черный крестик, нужно будет потянуть его до конца таблицы.
- Благодаря этому формула скопируется во все остальные ячейки. При этом номер искомого столбца скорректируется автоматически.
Использование выпадающих списков
Иногда для облегчения поиска используются массивы данных. Это намного удобнее, чем вбивать информацию вручную. Сделать это можно следующим образом.
- Перейдите в клетку, в которой мы вводили слово для поиска.
- Откройте уже знакомую нам вкладку.
- Кликните на указанный инструмент.
- Выберите пункт «Проверка данных».
- Раскройте выпадающий список и выберите соответствующий пункт.
- Сразу после этого появится дополнительное поле. Кликните на него, затем выделите колонку значений, которые можно использовать для поиска информации. Для продолжения кликните на «OK».
- Теперь вы можете выбрать любую запись и по этому слову будет происходить поиск остальных значений.
- В качестве примера можно выбрать любого другого преподавателя.
- Все данные автоматически будут найдены.
Поиск изменения данных при помощи VLOOKUP
Благодаря этой функции можно определить, какие именно значения были отредактированы. Для того чтобы продемонстрировать возможности этого инструмента, нужно будет выполнить следующие манипуляции.
- Для начала нужно создать ещё один столбец.
- Затем скопируем старую таблицу на другой лист. Именно там мы и будем вносить все обновления.
- В качестве примера изменим какую-нибудь запись.
- Возвращаемся на предыдущий лист. Переходим в первую колонку и вводим там следующую формулу (вам нужно будет отредактировать ссылки под свою таблицу).
[kod]=ВПР($B$3:$B$11;’Последние данные’!$B$3:$G$11;6;ЛОЖЬ)[/kod]
- Затем нажимаем на клавишу [knopka]Enter[/knopka].
- Благодаря этому мы получим обновленное соответствующее значение из новой таблицы, но оно совпадает со старым.
- Копируем эту формулу во все остальные строки.
- Результат получается следующим.
Обратите внимание на то, что благодаря этому можно провести сравнительный анализ и увидеть, что у последнего преподавателя изменилась категория.
Возможные ошибки
В большинстве случаев у новичков появляется ошибка «#Н/Д». Причем бороться с ней, как показывает практика, довольно сложно. На самом деле ничего страшного тут нет. Главное – найти причину и всё сразу же станет на свои места. Скорее всего, причина вашей проблемы заключается в следующем:
- вы допустили ошибку при написании формулы;
- у вас опечатка в слове для поиска;
- включен неточный поиск (интервальный просмотр равен 1) и данные не отсортированы;
- в аргументах функции используются различные типы данных;
- неправильно указана таблица для поиска данных.
Версии MS Excel
Данную функцию можно использовать абсолютно во всех версиях этого редактора (2016, 2013, 2010, 2007). Она есть даже в 2003 году.
То есть, если ваш файл открыть на компьютере со старым офисом, то ничего страшного не произойдёт. А подобная проблема встречается очень часто в различных бюджетных организациях.
Заключение
В данной статье мы рассмотрели принцип работы функции «VLOOKUP» в редакторе Excel. Обратите внимание на то, что для поиска какой-нибудь информации достаточно возможностей этого инструмента. Для реализации сложного поиска не обязательно использовать макросы на VBA. Всё можно сделать подручными средствами.
Для тех, у кого постоянно появляются различные ошибки и ничего не получается, мы подготовили бесплатный демо-файл с указанными выше примерами. Его можно скачать по этой ссылке. Благодаря этому с составлением формул сможет разобраться даже чайник.
Видеоинструкция
Если вам что-то непонятно или остались какие-нибудь вопросы, вы можете попробовать найти на них ответы в следующем видеоролике.
