528

Excel понятным языком: функция ВПР (вертикальный просмотр)

Практика показывает, более 85% вакансий работодателей имеют запрос "знание программы Excel", 40% из которых - "углубленное знание Excel".

Менеджеры всех уровней, руководители, секретари, помощники руководителей, бухгалтера, экономисты, аналитики, логисты все эти вакансии требуют знания программы Excel.

Вопрос на собеседовании:
Вы умеете ВПэрить?
ЧАВО???

Давайте разбираться.

6 минут, видео на тему ⬇⬇⬇

Итак, функция ВПР (вертикальный просмотр) предназначена для поиска элемента(-ов) в таблице или диапазоне по строкам.


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

Формула:

Excel понятным языком: функция ВПР (вертикальный просмотр) Microsoft office, Обучение, Офис, Маркетинг, Видео, Длиннопост, Полезное, Работа, Отдел кадров, Бизнес

Аргументы функции:

Аргумент_1 Искомое_значение, значение, которое вам нужно найти;

Аргумент_2 Таблица, диапазон с искомым значением, в котором находится искомое значение;

Аргумент_3 Номер_столбца, содержащий возвращаемое значение, считается от искомого значения, влево;

Аргумент_4 Интервальный просмотр (не обязательный): 1/ИСТИНА приблизительное совпадение, 0/ЛОЖЬ, точное совпадение возвращаемого значения. По умолчанию всегда 1/ИСТИНА.

Функцию ВПР можно вызвать, через:

Мастер формул, раздел Ссылки и массивы;

Вкладку Формулы ► Ссылки и массивы

Введя в строку =ВПР(.

Excel понятным языком: функция ВПР (вертикальный просмотр) Microsoft office, Обучение, Офис, Маркетинг, Видео, Длиннопост, Полезное, Работа, Отдел кадров, Бизнес

Пример

Необходимо найти цену продукта по наименованию:

Excel понятным языком: функция ВПР (вертикальный просмотр) Microsoft office, Обучение, Офис, Маркетинг, Видео, Длиннопост, Полезное, Работа, Отдел кадров, Бизнес

Введём формулы:

=ВПР(H4; $B$4:$E$10;4;0) - точный поиск

=ВПР(H4;$B$4:$E$10;4;ИСТИНА) - Приблизительный

ВНИМАНИЕ: искомый столбец Цена четвертый, а не пятый, т.к. отсчёт идет от самого левого столбца с Искомым_значением.
Точный поиск выдает верное значение, а приблизительный выводит значение из последней строки, т.к. список не отсортирован по алфавиту.

Сортируем исходную таблицу по алфавиту:

Excel понятным языком: функция ВПР (вертикальный просмотр) Microsoft office, Обучение, Офис, Маркетинг, Видео, Длиннопост, Полезное, Работа, Отдел кадров, Бизнес

Готово. Подробнее про ограничения читайте ниже.

Именованные диапазоны

Именованные диапазоны — отличный инструмент. Позволяют присвоить имя ячейке или диапазону с данными и в дальнейшем ссылаться на него вместо того, чтобы указывать громоздкие координаты (A2:B1000), делая формулы понятнее.

Создать именованный диапазон, можно на вкладке Формулы ► Задать имя:

Excel понятным языком: функция ВПР (вертикальный просмотр) Microsoft office, Обучение, Офис, Маркетинг, Видео, Длиннопост, Полезное, Работа, Отдел кадров, Бизнес

В открывшемся окне, задайте имя диапазона и укажите сам диапазон:

Excel понятным языком: функция ВПР (вертикальный просмотр) Microsoft office, Обучение, Офис, Маркетинг, Видео, Длиннопост, Полезное, Работа, Отдел кадров, Бизнес

Посмотреть все созданные диапазоны или отредактировать их можно на вкладке Формулы ► Диспетчер имен:

Excel понятным языком: функция ВПР (вертикальный просмотр) Microsoft office, Обучение, Офис, Маркетинг, Видео, Длиннопост, Полезное, Работа, Отдел кадров, Бизнес

Используя созданный диапазон напишем формулу =ВПР(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:

Трюки с листами книги

Как перевернуть таблицу в Excel

Мгновенное заполнение

Быстро удалить все картинки с листа

Быстрое перемещение строк и столбцов

Сводные таблицы в Excel: как создать?

"Умные" таблицы в Excel

Как отобразить листы в файлах Excel, выгруженных из 1С

Найдены возможные дубликаты

MS, Libreoffice & Google docs

347 постов11.4K подписчика

Добавить пост

Правила сообщества

1. Не нарушать правила Пикабу

2. Публиковать посты соответствующие тематике сообщества

3. Проявлять уважение к пользователям

4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.

По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях


Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.

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

Подробнее
+15
Менеджеры всех уровней, руководители, секретари, помощники руководителей, бухгалтера, экономисты, аналитики, логисты все эти вакансии требуют знания программы Excel.

Зато как эйчаров  в ступор вгоняет наличие у меня сертификата эксперта - эт прям надо видеть лица иногда. Знания программы они все требуют, но чем эти знания меряются, никто и не в курсе. Так что заканчивается все банально : "ВПР-ить умеешь?"

раскрыть ветку 1
+5
Это ещё что. Я когда собеседовался на текущую должность (повышение, по сути), то пришлось выполнить огромное задание по Excel. И мне понравилось, что разрешали делать задания разными формулами и методами. То есть - знаешь как получить результат не самым простым способом, то пойдет. В то время я научился такие костыли в формулах делать, что HR просто в ступор впадал от них. Самое большое "достижение" того времени - формула из более чем 100 символов. Хотя, имея текущие знания в Excel я понимаю, что Vlookup и IfError сократили бы костыль до 20-25 символов за формулу. А тогда If, And, Or стали моими спасителями
+7
Даешь функционал курсов на пикабу!
+5

Пример в посте контрпродуктивный.

Потом приходят такие повелители экселя со своим золотым молотком и начинается:

- у нас тут БД в экселе на 250Мб перестала работать, что с ней не так?

- у нас макросы что-то не то в новом экселе выдают, почини!

- а нельзя это все в гугль-документы перенести?

Иллюстрация к комментарию
+6

Перевод формул на русский язык придумал сатана...

раскрыть ветку 2
+4
Спасибо за комментарий. Ничего более
уродского на свете не существует, чем локализованные формулы экселя.
0

Эксель *понятным* языком.



Бха ха ха ха!

+3

В нашей организации каждый день обрабатывают большое количество данных в Excel. Обрабатывается несколько сотен тысяч строк. Обработка ВПР занимает большое количество времени. И было принято решение написать свой ВПР. Программка умеет сравнивать данные по нескольким столбцам (в доп настройка можно убрать пробелы, добавить слова исключения), сразу же переносить несколько столбцов. Кидаю ссылку: https: //yadi.sk/d/N2ePHLyIWO71kA (не читал про добавление ссылок на сайт, поэтому на всякий случай добавил пробел после https:). Может кому-нибудь пригодится. Ни рекламы, ни ограничений не содержит.

раскрыть ветку 2
+1

Есть же добрые люди на этом свете!

Спасибо за ссылку и программу, все просто и понятно, и самое главное - массив из 275000 строк программа обработала за секунду.

Намного функциональнее, чем стандартная ВПР функция. Ты спас мой рабочий день :)

0
Ооо надо будет попробовать, спасибо!
+3

Дружище,я подписался, в ожидании постов про VBA. Макросы, автоматически расширяющие ячейки, макросы, вставляющие разрыв страницы по проставленным меткам и т.д. Очень хочется увидеть простые и стандартные решения. Патентованные изобретения велосипедов из интернетов уже заебадолбали.


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


Если будут такие посты - заплюсую до изнеможения!

раскрыть ветку 2
+2
Потому что макросы в Экселе это как примерно компас в мобильном телефоне. Нужна вам обработка данных? Ну прогоните через код на любом удобном вам языке программирования и ввгрузите в Эксель. Кто вообще все те люди, кто готов трахаться с монструозным интерфейсом макросов в экселе?
раскрыть ветку 1
+3

Долго мучился с лукапом, когда решал свои задачи в гуглдоках, пока не узнал о функции query. sql-подобный синтаксис просто сотворил чудо) Жаль, что в эксель не завезли еще :(

раскрыть ветку 6
+1
Бро, может напишишь пару годных постов про гугл доки?)
раскрыть ветку 5
+12

Забудьте про ВПР, у нее есть нормально работающий аналог ИНДЕКС+ПОИСКПОЗ

раскрыть ветку 9
+6
Индекс мэтч решает, кто минусит?
раскрыть ветку 1
+1
А разница? Я просто ВПР юзаю вообще постоянно и каждый день, но не понимаю почему ваш аналог проще? Подскажете, а то вдруг я себе жизнь смогу упростить?)
раскрыть ветку 6
+2

Нужно. Больше. Тем. Про. ВПР!

+2

Когда научился пользоваться данной функцией, так просто все стало (про остальные изученные вещи молчу)

+1
А Ctrl+F не проще будет?
раскрыть ветку 3
+1

Попробуй сравнить хотя бы 30 позиций, все станет ясно.

0
А если 1000000 значений надо найти?
раскрыть ветку 1
0

@ExceLifeHack, никак не пойму, для чего вы F4 нажимаете? Знаю, что это повторить последнее действие. Но в результате получаете преобразование B4:E10 -> $B$4:$E$10 . Для чего это? В чем разница?
Помню, что когда-то вручную знак $ добавлял, но совсем уже забыл для чего он.

раскрыть ветку 2
+1
Закрепить ссылку на ячейку, чтобы формула протягивалась без сползания
раскрыть ветку 1
0

ВПР - это вроде как вертикальный поиск результата (ГПР горизонтальный) использую только для поиска текстовых совпадений в небольших массивах. Если нужны числовые значения, то суммесли, остальное в больших массивах через индекс.поискпоз.

раскрыть ветку 1
0
это вроде как вертикальный поиск результата

Так и есть.

0
Нихуа не понятно, но я сохраню.
0
Плюсище! 🤣
-4

Это сейчас даже в ОГЭ есть. Ребенок 8-классник, в глаза ексель не видавший, научился за 2 часа. Я фигею с того, что люди не могут этому научиться

раскрыть ветку 2
+2
Ну, в возрасте 8-классника, наверное, проще выучить что - то новое, чем в возрасте 40+. Мозг уже не так радуется новым значениям
раскрыть ветку 1
-1

Кстати да,не многие умеют влукапить)

Похожие посты
44

А ещё поговорить?

А ещё поговорить? Авторский рассказ, Рассказ, Бизнес, Бизнес по-русски, Управление, Управление проектами, Секретарь, Начальник, Компания, Юмор, Цифровизация, Совещание, Увольнение, Контроль, Антикризис, Кризис, Офис, Офисный планктон, Офисные истории, Офисные будни, Длиннопост
Итак, по порядку от первого лица
Для них не стало топлива – эмоций
Показать полностью
970

Как убивают мотивацию эффективные менеджеры

815

Доброе слово и кошке приятно

62

Полезный календарь

Полезный календарь Полезное, Бизнес, Отчетность, Календарь, Длиннопост
Полезный календарь Полезное, Бизнес, Отчетность, Календарь, Длиннопост
Показать полностью 1
76

Обещанный пост о руководителе

Показать полностью
265

Маркетинговый кейс (как вы яхту назовете)

Маркетинговый кейс (как вы яхту назовете) Маркетинг, Боги маркетинга, Маркетологи, Бизнес, Бизнес по-русски, Цифровая экономика, Нейминг, Приправы, Еда, Переговоры, Цифровизация, Кейс, Менеджер по продажам, Менеджмент, Менеджер
874

А поговорить?

А поговорить? IT, Делопроизводство, Управление, Управление проектами, Управление людьми, Рассказ, Авторский рассказ, Технологии, Информация, Психология, Люди, Работа, Офисный планктон, Офисные будни, Офисный работник, Офис, Автоматизация, Цифровая экономика, Цифровые технологии, Длиннопост
Как говорится, собака лает – караван идёт
Показать полностью
611

Про собеседование с эйчаром

1354

Работать с китайцами и не убивать — истории невероятных провалов

Работать с китайцами и не убивать — истории невероятных провалов Китай, Китайцы, Китайские товары, Бизнес, Опыт, Работа, Fail, Длиннопост
Работать с китайцами и не убивать — истории невероятных провалов Китай, Китайцы, Китайские товары, Бизнес, Опыт, Работа, Fail, Длиннопост
Работать с китайцами и не убивать — истории невероятных провалов Китай, Китайцы, Китайские товары, Бизнес, Опыт, Работа, Fail, Длиннопост
Работать с китайцами и не убивать — истории невероятных провалов Китай, Китайцы, Китайские товары, Бизнес, Опыт, Работа, Fail, Длиннопост
Показать полностью 3
855

Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды

Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост
Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост
Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост
Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост
Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост
Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост
Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост
Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост

Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост
Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост
Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост

Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост
Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост
Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост
Как мы придумали Кофтёнышей: от игрушки-кофты до бренда необычной одежды Бизнес, Маркетинг, Маркетплейс, Производство, Интернет-Магазин, Видео, Длиннопост
Показать полностью 14 3
1670

Можно пописать без разрешения

359

Ответ на пост «Сесть на шею не получилось» 

249

Работа от почтальона печкина

Работа от почтальона печкина Работа, Отдел кадров
222

Систематическая ошибка семьянина

Показать полностью
1276

Стрессоустойчивый сотрудник

Стрессоустойчивый сотрудник Стихи, Работа, Стресс, Стрессоустойчивость, Психология, Офис, Картинка с текстом
Показать полностью
5325

Суд отклонил иск минимущества Крыма об изъятии земли у сыровара

Суд отклонил иск минимущества Крыма об изъятии земли у сыровара Политика, Крым, История, Фермер, Закон, Бизнес, Работа, Новости, Длиннопост
Суд отклонил иск минимущества Крыма об изъятии земли у сыровара Политика, Крым, История, Фермер, Закон, Бизнес, Работа, Новости, Длиннопост
Суд отклонил иск минимущества Крыма об изъятии земли у сыровара Политика, Крым, История, Фермер, Закон, Бизнес, Работа, Новости, Длиннопост
Суд отклонил иск минимущества Крыма об изъятии земли у сыровара Политика, Крым, История, Фермер, Закон, Бизнес, Работа, Новости, Длиннопост
Показать полностью 3
1448

Просто пробуйте

365

Я знаю, какое вино вы закажете

Я знаю, какое вино вы закажете Маркетинг, Вино, Алкоголь, Бизнес, Ресторан, Длиннопост
Все описанное ниже может НЕ относиться к вам. Да-да, вот конкретно к вам. Но я рассматриваю вопрос в контексте маркетинга, который хотя и идет к тому, чтобы делать максимально персонализированные предложения, пока еще в основном оперирует статистикой, особенно в оффлайне. Другими словами, маркетологам и продавцам не так важно, что выберете конкретно вы — им важно, что выберет большинство.
Я знаю, какое вино вы закажете Маркетинг, Вино, Алкоголь, Бизнес, Ресторан, Длиннопост
Выход — заказать второе по цене снизу.
Вторым по цене может оказаться вино, которое надо распродать, вне зависимости от закупочных цен.
Но не забывайте, что мы можем обманывать сами себя, даже если это понимаем.
Показать полностью 1
Похожие посты закончились. Возможно, вас заинтересуют другие посты по тегам: