Excel понятным языком: функция ВПР (вертикальный просмотр)
Практика показывает, более 85% вакансий работодателей имеют запрос "знание программы Excel", 40% из которых - "углубленное знание Excel".
Менеджеры всех уровней, руководители, секретари, помощники руководителей, бухгалтера, экономисты, аналитики, логисты все эти вакансии требуют знания программы Excel.
Вопрос на собеседовании:
Вы умеете ВПэрить?
ЧАВО???
Давайте разбираться.
6 минут, видео на тему ⬇⬇⬇
Итак, функция ВПР (вертикальный просмотр) предназначена для поиска элемента(-ов) в таблице или диапазоне по строкам.
Например, нужно найти сумму заказа по его номеру, подтянуть текущий прайс к заказу или получить имя сотрудника зная его табельный номер.
Формула:
Аргументы функции:
Аргумент_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.
Ошибки
1. Включен точный поиск (0/ЛОЖЬ), но искомого значения (Аргумент_1) нет в диапазоне поиска или он написан с опечаткой;
2. Включен приблизительный поиск (1/ИСТИНА), но таблица, в которой происходит поиск не отсортирована по возрастанию наименований;Столбец поиска не является крайним левым;
3. Съехал Диапазон с искомым значением (Аргумент_2), ссылка на который должна быть абсолютной, нажмите внутри формулы на нём F4;
4. Функция не может найти нужного значения, потому в строке присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.);
Используйте текстовые функции СЖПРОБЕЛЫ и ПЕЧСИМВ: =ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(Аргумент_1));Аргумент_2;Аргумент_3;Аргумент_4).
5. Формат ячейки, откуда берется искомое значение и формат ячеек первого столбца таблицы отличаются (например, числовой и текстовый). Особенно характерен при использовании вместо текстовых наименований числовых кодов (номера счетов, коды, идентификаторы, даты и т.п.).
Подробнее статье ВПР и числа-как-текст.
Для преобразования числовых форматов данных в текст, используйте функцию ТЕКСТ : =ВПР(ТЕКСТ(Аргумент_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 );"").
Теперь вы знаете достаточно и можете уверенно ответить работодателю или похвастаться перед коллегами.
Еще интересное по теме Excel:
MS, Libreoffice & Google docs
719 постов15K подписчик
Правила сообщества
1. Не нарушать правила Пикабу
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.