Коллеги, добрый день. В этом видео мы познакомимся с функцией «Вертикальный просмотр», или сокращенно ВПР, а на английском VLookup. Это важная функция, потому что она обеспечивает перенос данных из одной таблицы в другую, связь нескольких таблиц, например, это может быть база данных и справочник, прайс-лист и таблица с заказами, выгрузка из 1C с остатками и выгрузка с отгрузками и так далее. Большинство пользователей, работающих с данными в Excel и Google таблицах, используют ее регулярно. Итак, что делает функция ВПР? Она осуществляет поиск заданного значения в какой-нибудь таблице, например, поиск названия товарной позиции в прайс-листе и возвращает данные одного из столбцов этой таблицы, например, цены искомого товара из прайс-листа. Давайте посмотрим, как она работает на практике. Начнем мы вот с этой таблицы, в которой у нас есть фамилии студентов, их оценки по стобалльной шкале, и нам необходимо автоматически получать оценку по пятибалльной шкале, конвертировать с помощью такой вспомогательной таблицы, где в первом столбце указаны границы оценок по стобалльной шкале и соответственно оценки по пятибалльной шкале, которые им соответствуют. Для этого и нужна функция ВПР, которая будет искать оценку каждого студента в этой таблице и возвращать соответствующую ей оценку по пятибалльной шкале. Давайте посмотрим на ее синтаксис. И четыре аргумента. Первое — это запрос, это то, что мы ищем. В данном случае мы будем искать оценку конкретного студента из столбца B, а второй аргумент диапазон — где мы ищем. Мы будем ее находить вот в этой таблице и возвращать оценку по пятибалльной шкале. А третий аргумент — собственно говоря, это то, что мы возвращаем, что нам нужно найти в той таблице, куда мы обращаемся, какие данные оттуда принести. Нужно просто указать номер столбца числом, то есть хотя данные, которые нам нужны стоят в столбце F, но в рамках таблицы, в которой мы ведем поиск, это второй столбец. Поэтому мы просто указываем двойку и закрываем скобку. В данном случае когда мы ведем поиск числа, ближайшего числа, нам четвертый аргумент не нужен. Позже мы будем к нему обращаться для поиска текстовых значений. Если нажать Enter, то мы получим четверку, которая действительно соответствует оценке 85. И достаточно протянуть двойным щелчком функцию, чтобы получить результат по всем оценкам. То есть функция находит число в таблице, на которую мы сослались, и возвращает оттуда соответствующую оценку из соседнего столбца, второго столбца, на который мы сослались через двойку. Это режим работы с числами, когда в функции опущен последний аргумент или он мог бы быть равен единице, но в 90 % случае, во всяком случае в моем опыте, используется другой режим работы с текстом, то есть когда вы ищете не число, входящее в интервал, а вы просто находите конкретное текстовое значение, потому что в большинстве случаев у товара, у фамилии сотрудника, у чего угодно еще есть конкретное точное текстовое значение. Как в этом примере, абсолютно типичном, когда есть лист заказа товаров, и нам необходимо из прайс-листа по названию товаров получить, подтянуть цену. Для этого используется функция ВПР. И здесь уже поиск будет вестись точный. То есть последний аргумент мы укажем равным нулю, что означает точный поиск. Итак, первый аргумент — запрос, что мы ищем. Мы ищем конкретный товар, указанный в нашем листе заказа. Второй аргумент — где мы его будем искать. Мы будем его искать в прайс-листе, столбце AB на листе «Прайс-лист». Наконец, третий аргумент — номер столбца, а из какого столбца нужно получить информацию. То есть ВПР всегда ищет в первом столбце таблицы, может из любого другого столбца вернуть информацию. Нас здесь интересует цена, собственно, в прайс-листе других столбцов нет — это второй столбец прайс-листа, указываю двойку. И вот теперь важный момент: так как мы ищем текстовое значение, точное совпадение, то последний аргумент нужно указать равным нулю. Иначе будут, скорее всего, ошибки, потому что режим приблизительного поиска работает для чисел, режим единичка. Для текстовых значений нужно точное совпадение, и в 90 % случаев, как я говорил, последним аргументом ВПР у вас будет ноль. Итак, эта функция находит название товара из столбца A, на листе «Прайс-лист» в столбцах AB, в столбце A точнее, и из второго столбца таблицы переносит информацию. Мы ее протянем, получим результаты, но, как видите, не во всех случаях. В последнем случае выдается ошибка Н/Д, что означает, что значение не было найдено, и это нормальная ситуация, то есть это означает, что такого товара в прайс-листе нет, либо же что он указан неверно. Если пойти в прайс-лист, мы увидим, что да, не хватает еще нескольких символов названий товара. Если их сюда добавить, то функция будет работать. Это важный момент, который нужно отметить. Всегда функция ведет точный поиск, когда аргумент равен нулю в случае с текстом. И даже один пробел какой-то, знак препинания, ошибка в одной букве не поможет найти информацию. Как с этим бороться? Один из инструментов — это проверка данных, когда мы выделяем ячейки в той же форме заказа, заходим в меню «Данные», вызываем проверку данных. И можно ограничить ввод значений только из существующего списка, например, из прайс-листа. Сослаться на список товаров в прайс-листе и запретить вот здесь ввод иных данных. Таким образом, теперь у нас на листе заказа, во-первых, будут выпадающие списки с названиями товаров, можно выбирать. Во-вторых, при попытке написать неверные названия, несуществующие, будет появляться ошибка, то есть пользователи просто не смогут ввести неправильное значение. И собственно говоря, также будут появляться такие всплывающие подсказки, когда вы начинаете вводить название товара. Это очень удобно и важно не только функции ВПР, но и для всех функций, которые ведут поиск по названию товаров, мы с ними будем и в будущем сталкиваться. Также и для функции суммирования и подсчета, например СУММЕСЛИ, СЧЕТЕСЛИ и так далее. Как быть, если вы хотите функцию ВПР протянуть с запасом на несколько строк, но при этом не хотите наблюдать ошибки Н/Д? Хотите вместо них видеть пустую ячейку, но формулу, тем не менее, протянуть с запасом, например, у вас форма заказа состоит из девяти строк, то есть до десятой строки? Можно воспользоваться функцией ЕСЛИОШИБКА, или IFERROR. Мы берем внутрь этой функции всю существующую конструкцию и в конце через точку с запятой указываем, а что вводить, если значение не найдено. Например, можно вводить ноль или пустые кавычки, и таким образом у вас в случае, когда функция ВПР ничего не находит, функция ЕСЛИОШИБКА будет этот момент отлавливать, возвращать пустоту. И теперь, если у нас появляется новый товар, тут же подтягивается его цена. При этом неправильный товар выбрать нельзя благодаря проверке данных. А если товар пока не указан, то хотя формула и есть, вы этого в ячейке визуально не видите. Таким образом, такая таблица исключает ошибки и имеет некоторую степень автоматизации, то есть сотруднику достаточно просто выбирать товары в выпадающем списке, указывать количество, и цены будут подтягиваться автоматически. Добавлю только, что в реальной практике прайс-лист может быть отдельным документом, данные из которого в режиме реального времени тянутся в ваш файл для составления заказов с помощью функции IMPORTRANGE. А далее, как мы уже описали, с помощью ВПР вы будете получать цены или другие параметры в список заказываемых товаров. Рассмотрим также функцию-побратим ВПР, которая называется ГПР, или горизонтальный просмотр HLookup. Она используется крайне редко, просто потому что большинство таблиц все-таки устроено вертикально. Но тем не менее важно знать, что она существует. Она полностью идентична ВПР, за исключением того, что таблица для поиска расположена горизонтально. И мы ее рассмотрим на том же примере со студентами и их оценками, то есть мы будем искать конкретную оценку, будем искать ее теперь в горизонтальной таблице, вот в такой. И в таком случае указывается не номер столбца, а номер строки. В данном случае пятибалльная оценка, которую нужно вернуть с помощью ГПР во второй строке. Это единственное отличие функции, но, как я уже отметил, крайне редко таблицы устроены таким образом, что вам пригодится ГПР, в отличие от вертикального просмотра, который нужен постоянно. Итак, коллеги, в этом видео мы с вами поговорили о двух режимах работы функции ВПР: интервальный просмотр для поиска ближайшего числа и обычный режим, когда последний аргумент равен нулю для поиска точного совпадения текстового значения или числового. И рассмотрели аналог функции ВПР для горизонтального просмотра, которая называется ГПР.
