Практика показывает, более 85% вакансий работодателей имеют запрос "знание программы Excel", 40% из которых - "углубленное знание Excel".
Менеджеры всех уровней, руководители, секретари, помощники руководителей, бухгалтера, экономисты, аналитики, логисты все эти вакансии требуют знания программы Excel.
Распространённый вопрос на собеседовании:
Вы умеете ВПэрить?
ЧАВО???
Давайте разбираться.
Итак, функция ВПР (вертикальный просмотр) предназначена для поиска элемента(-ов) в таблице или диапазоне по строкам.
Например, нужно найти сумму заказа по его номеру, подтянуть текущий прайс к заказу или получить имя сотрудника зная его табельный номер.
Формула:
Аргументы функции:
- Аргумент_1 Искомое значение, значение, которое вам нужно найти;
- Аргумент_2 Таблица, диапазон с искомым значением, в котором находится искомое значение;
- Аргумент_3 Номер_столбца, содержащий возвращаемое значение, считается от искомого значения, влево;
- Аргумент_4 Интервальный просмотр (не обязательный): 1/ИСТИНА приблизительное совпадение, 0/ЛОЖЬ, точное совпадение возвращаемого значения. По умолчанию всегда 1/ИСТИНА.
Функцию ВПР можно вызвать, через:
- Мастер формул, раздел Ссылки и массивы;
- Вкладку Формулы ► Ссылки и массивы;
- Введя в строку =ВПР(.
Пример
Необходимо найти по наименованию цену продукта:
Введём формулы:
=ВПР(H4; $B$4:$E$10;4;0)
=ВПР(H4;$B$4:$E$10;4;ИСТИНА)
ВНИМАНИЕ: искомый столбец Цена четвертый, а не пятый, т.к. отсчёт идет от самого левого столбца с Искомым значением.
Точный поиск выдает верное значение, а приблизительный выводит значение из последней строки, т.к. список не отсортирован по алфавиту.
Сортируем исходную таблицу по алфавиту:
Готово. Подробнее про ограничения читайте ниже.
Именованные диапазоны
Именованные диапазоны — отличный инструмент. Позволяют присвоить имя ячейке или диапазону с данными и в дальнейшем ссылаться на него вместо того, чтобы указывать громоздкие координаты (A2:B1000), делая формулы понятнее.
Создать именованный диапазон, можно на вкладке Формулы ► Задать имя:
В открывшемся окне, задайте имя диапазона и укажите сам диапазон:
Посмотреть все созданные диапазоны или отредактировать их можно на вкладке Формулы ► Диспетчер имен:
Используя созданный диапазон напишем формулу =ВПР(G3;Таблица;4;0) Можно заметить формула стала меньше и теперь диапазон таблицы можно не закреплять при протягивании формулы.
Ограничения
Не может искать влево
ВПР может искать значения только в крайнем левом столбце. В случае неверной ссылки формула выдаст ошибку # Н/Д.
Настройте формулу таким образом, чтобы она ссылалась на правильный столбец, если это невозможно, попробуйте переместить столбцы или используйте комбинацию функций ИНДЕКС и ПОИСКПОЗ.
Если, значение повторяется, функция выведет только первое найденное значение
- Вам нужны повторяющиеся данные в списке? Если нет – удалите их, при помощи кнопки Удалить дубликаты на вкладке Данные.
- Нужно оставить дубликаты? Для таких случаев отлично подойдёт Сводная таблица, позволяющая выбрать значение и посмотреть результаты.
ВПР не чувствительный к регистру
Добавили или удалили столбец из таблицы, все сломалось
Формулы с ВПР перестают работать каждый раз, когда в таблицу поиска добавляется новый или из него удаляется столбец.
Используйте в формуле ИНДЕКС+ПОИСКПОЗ, так Вы раздельно зададите столбцы для поиска и извлечения данных, в результате можете удалять или вставлять сколько угодно столбцов, без возникновения ошибок.
Ссылки на ячейки съехали при копировании или протягивании формулы
Используйте абсолютные ссылки на ячейки при записи диапазона, например $A$1:$D$100 или $A:$D. В строке формул Вы можете быстро переключать тип ссылки, нажимая F4.
Ошибки
- Функция выдаёт ошибку # Н/Д:
- Включен точный поиск (0/ЛОЖЬ), но искомого значения (Аргумент_1) нет в диапазоне поиска или он написан с опечаткой;
- Включен приблизительный поиск (1/ИСТИНА), но таблица, в которой происходит поиск не отсортирована по возрастанию наименований;Столбец поиска не является крайним левым;
- Съехал Диапазон с искомым значением (Аргумент_2), ссылка на который должна быть абсолютной, нажмите внутри формулы на нём F4;
- Функция не может найти нужного значения, потому в строке присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.).
Используйте текстовые функции СЖПРОБЕЛЫ и ПЕЧСИМВ: =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(Аргумент_1));Аргумент_2;Аргумент_3;Аргумент_4).
- Формат ячейки, откуда берется искомое значение и формат ячеек первого столбца таблицы отличаются (например, числовой и текстовый). Особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, коды, идентификаторы, даты и т.п.).
Для преобразования числовых форматов данных в текст, используйте функцию ТЕКСТ : =ВПР(ТЕКСТ(Аргумент_1; " # ");Аргумент_2;Аргумент_3;Аргумент_4) или Аргумент_1&"".
Обратный вариант преобразования текста в число: --Аргумент_1, Аргумент_1*1 или Аргумент_1+0.
Универсальная формула для исправления ошибки числа, как текст: =ЕСЛИОШИБКА(ВПР(Аргумент_1*1; Аргумент_2;Аргумент_3;Аргумент_4;ВПР(Аргумент_1&""; Аргумент_2;Аргумент_3;Аргумент_4).
Убрать сообщения об ошибке # Н/Д (для версии Excel 2007+)
В случаях, когда функция не может найти совпадения, используйте функцию ЕСЛИОШИБКА.
Например, заменяет нулями: =ЕСЛИОШИБКА(ВПР(Аргумент_1;Аргумент_2;Аргумент_3;Аргумент_4);0).
Выводит пустое значение: =ЕСЛИОШИБКА(ВПР( Аргумент_1;Аргумент_2;Аргумент_3;Аргумент_4 );"").
Теперь вы знаете достаточно и можете уверенно ответить работодателю или похвастаться перед коллегами.
6 минут, видео на тему ⬇⬇⬇
Спасибо, что дочитали до конца!
Если Вам было интересно, ставьте лайк, пишите, что думаете в комментариях и подписывайтесь на канал, если еще не сделали этого.
Еще интересное по теме Excel:
- Мгновенное заполнение
- Быстрое перемещение строк и столбцов
- Сводные таблицы в Excel: как создать?
- Суммирование в Excel
- Удаление дубликатов, Текст по столбцам
- ТОП-30 горячих клавиш в Excel