Практика показывает, более 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:
