ВПР и ПРОСМОТРХ: Искусство точного поиска в Excel
Перестаньте тратить часы на рутину и ломать формулы. Научитесь за секунды связывать таблицы, автоматически находить ошибки и управлять данными на космической скорости.
Насколько хорошо ты владеешь функцией ВПР?
Пройди тестирование, чтобы узнать уровень владения функцией.
Какими функциями поиска данных в Excel Вы пользуетесь? (можно выбрать несколько)
ВПР - функция для поиска по столбцам. Она самая популярная.
ГПР - аналог функции ВПР, но ищет эта функция значение по строкам, а не по столбцам.
Интересно в каких случаях Вы используете эту функцию. Она ищет, только если данные отсортированы по возрастанию. Лично мы (сотрудники школы Pro-excel) этой функцией не пользуемся.
О, сладкая парочка ИНДЕКС ПОИСКПОЗ! Если бы не необходимость печатать длинное название обеих функции при поиске данных, то этот тандем явно был бы на первом месте среди функций поиска. Поиск с ними абсолютно логичен и понятен.
Что ищет функция ВПР? (можно выбрать несколько вариантов)
Верно. У функции ВПР 2 варианта поиска. Один из них - поиск точного совпадения.
Верно при приблизительном поиске функция ВПР возвращает ближайшее наименьшее значение. Этот вариант подходит для решения задач выплаты премия (которая зависит от объемов продаж) или расчета размера скидки ( которая зависит от суммы покупки).
К сожалению ВПР не может решить задачу, когда нужно найти ближайшее наибольшее. Пример подобной задачи - подобрать насос, мощность которого должна быть не меньше заданного значения.
Судя по ответу, вы явно перетрудились. Срочно закройте ноутбук и идите отдыхать. Иначе ещё чуть-чуть, и вы начнёте ждать квартальную премию от фикуса в коридоре (а он, поверьте, тоже выдаст #Н/Д).
Найти и вывести несколько значений, удовлетворяющих заданному значению ВПР не может.
Перед Вами таблица с данными о сотрудниках. Вам нужно вывести информацию о должности сотрудника по фамилии Кругов. Какой функцией Вы воспользуетесь
Верно. ВПР - это вертикальный просмотр. Функция просматривает столбец, находит искомое значение и возвращает значение из другого столбца. Фамилии сотрудников расположены в столбце, поэтому именно ВПР нужна в данном случае.
ГПР - это горизонтальный просмотр. Функция ищет значение в строке. В данном примере фамилии сотрудников указаны в столбце. Поэтому нужна функция ВПР, а ГПР не подходит.
Перед Вами словарь. В левом столбце английское слово, в правом - русское. Какой вариант перевода можно найти, используя функцию ВПР?
Верно! Функция ВПР ищет данные в крайнем левом столбце выделенного диапазона и возвращает значение из столбца, который расположен правее.
К сожалению, ВПР не сможет вернуть перевод русского слова на английский, если данные представлены в таком виде. Чтобы выполнить перевод, нужно либо изменить таблицу, либо использовать сладкую парочку ИНДЕКС и ПОИСКПОЗ.
Не верно. В данном случае ВПР сможет найти перевод слова с английского на русский. Поиск осуществляется следующим образом: в крайнем левом столбце диапазона (он задается в параметрах функции) ищется искомое значение. Затем функция ВПР возвращает значение из соответствующей ячейки столбца, который расположен правее (номер столбца задается также в параметрах функции).
Если нужно осуществлять обратный перевод (или поиск), то нужно либо изменить таблицу, либо использовать сладкую парочку ИНДЕКС и ПОИСКПОЗ.
Если нужно осуществлять обратный перевод (или поиск), то нужно либо изменить таблицу, либо использовать сладкую парочку ИНДЕКС и ПОИСКПОЗ.
Выберите формулу, которую нужно записать в ячейку I2, чтобы вывести должность сотрудницы Елисеевой (фамилия указана в ячейке I1):
Четвертый параметр функции равен 1 - это означает приблизительный поиск. В этом случае нужен точный поиск. Четвертый параметр должен равняться 0.
Четвертый параметр функции опущен. По умолчанию он равен 1 - это означает приблизительный поиск. В этом случае нужен точный поиск. Четвертый параметр должен равняться 0.
Третий параметр функции равен 3. Функция вернет значение 3-го по счету столбца в выделенном диапазоне. Это столбец с данными об отделе, в котором работает сотрудник.
Правильно! Все параметры функции указаны верно.
В ячейке K2 написана формула =ВПР(K1;A:H;4;0). Почему вместо должности отображается дата?
Столбец A содержит имена сотрудников: фамилия и инициалы. Столбец B - объем продаж сотрудника. Нужно вывести информацию о сотруднике по фамилии “Кругов”. Имя и отчество Вы не знаете. Сможет ли функция =ВПР("Кругов";A:B;2;0) найти объем продаж сотрудника?
Функция вернет сообщение об ошибке, так как в столбце A нет именно такого значения (Кругов). Искомое значение должно полностью совпадать с одним из значений столбца A (в таблице указано Кругов А. Я.).
Верно. Функция не найдет такого сотрудника в таблице поиска A:B. Потому, что искомое значение и одно из значений столбца A должны совпадать полностью.
Из прошлого примера Вы узнали, что, зная только фамилию, нельзя найти данные из представленной таблицы (так как столбец A содержит инфо и о фамилии, и инициалах).
В подобных случаях, когда Вы знаете только часть данных (знаете только фамилию, но не помните инициалы), выручают подстановочные знаки. Какой подстановочный знак использовать, чтобы ВПР нашла сотрудника в таблице?
В подобных случаях, когда Вы знаете только часть данных (знаете только фамилию, но не помните инициалы), выручают подстановочные знаки. Какой подстановочный знак использовать, чтобы ВПР нашла сотрудника в таблице?
? Подстановочный вопросительный знак заменяет один любой символ. В столбце кроме фамилии указаны инициалы. Это 2 буквы, 2 точки и 2 пробела. Можно использовать этот знак, но правильная запись должна быть такой Кругов??????
* Подстановочный знак звездочка означает любое количество любых символов. Именно его нужно использовать в этом примере.
В правой таблице (E:F) указан размер скидки в зависимости от накопленной суммы заказов. Чем больше сумма заказа, тем больше скидка.
Слева указаны данные клиента A-46. Сумма его покупок равна 5300 руб (B2). Нужно высчитать размер скидки клиента. Какую формулу правильнее использовать?
Слева указаны данные клиента A-46. Сумма его покупок равна 5300 руб (B2). Нужно высчитать размер скидки клиента. Какую формулу правильнее использовать?
В правой таблице (E:F) указан размер скидки в зависимости от накопленной суммы заказов. Чем больше сумма заказа, тем больше скидка.
Слева указаны данные клиента A-46. Сумма его покупок (B2) равна 9250 руб. В ячейке C2 вычисляется скидка по формуле =ВПР(B2;E:F;2;1). Функция вернула размер скидки 5 %, хотя она должна быть 7 % (сумма заказов больше 9001 руб).
Почему функция ВПР неправильно вычислила размер скидки?
Слева указаны данные клиента A-46. Сумма его покупок (B2) равна 9250 руб. В ячейке C2 вычисляется скидка по формуле =ВПР(B2;E:F;2;1). Функция вернула размер скидки 5 %, хотя она должна быть 7 % (сумма заказов больше 9001 руб).
Почему функция ВПР неправильно вычислила размер скидки?
Путь в тысячу миль начинается с первого шага.
Вы практически не знакомы с функцией ВПР и ее синтаксисом. Вам предстоит много узнать, чтобы стать экспертом поиска данных. Скорее записывайтесь на курс "Excel: Тренажер ВПР". Уже с первых уроков курса Вы значительно улучшите навыки, а концу курса станете экспертом.
Неудачи непременно будут, и то, как вы с ними справитесь, будет важнейшим показателем того, добьетесь ли вы успеха.
Видно, что у Вас есть определенный опыт работы с функцией ВПР. В тоже время результаты показывают, что есть и пробелы в знаниях. Я Вам рекомендую систематизировано подойти к изучению функций поиска в Excel. Вам будет полезно прорешать примеры курса, чтобы довести знания до совершенства. Кроме функции ВПР Вы изучите функции ИНДЕКС и ПОИСКПОЗ, ПРОСМОТРХ. И думаю, Вы оцените интересные, но непростые примеры, которые я подготовил для курса.
Талантливый человек стремится использовать время, а средний - его убить.
Поздравляю! Вы отлично справились с тестом. Вы умеете работать с функцией ВПР и знаете ее тонкости.
Результат после курса
- Навык за секунды объединять данные из разных таблиц
- Иммунитет к ошибкам #Н/Д. Научитесь обходить скрытые ловушки Excel (несовпадение форматов, лишние пробелы) и защищать отчеты от сбоев.
- Мастерство ПРОСМОТРХ. Освоите «убийцу ВПР» — сможете искать данные влево, снизу вверх и собирать отчеты в 3 раза быстрее коллег.
- Навык управления ИИ. Научитесь писать идеальные промпты, чтобы нейросеть мгновенно создавала, расшифровывала и чинила формулы за вас.
- +100 к уверенности. Перестанете бояться сложных таблиц от руководства и начнете щелкать их как орешки.
Если в течение 7 дней после начала курса вы поймёте, что этот формат или программа вам не подходят, мы вернём 100% стоимости обучения без лишних вопросов и бюрократии.
Остались вопросы? Напишите нам
Старт курса сразу после оплаты. Вы сами выбираете удобное время для просмотра уроков и выполнения практики.
Да, в Office 365 вам доступна суперфункция ПРОСМОТРХ. Она гораздо проще и мощнее старого ВПР. В курсе мы разберем обе функции: вы освоите продвинутый ПРОСМОТРХ для быстрой работы, но будете легко щелкать и классический ВПР, который до сих пор встречается в файлах у коллег и клиентов.
Будет полезно. В курсе рассмотрены задачи поиска данных из реальной жизни. Такие, с которыми Вы столкнетесь на работе. Если в курсе Вы не найдете решения Вашей задачи поиска данных, расскажите о ней преподавателю. Он подскажет как ее решать.
Дадим контакты. После оплаты курса на Вашу электронную почту придет приветственное письмо с ссылкой на обучающую платформу и контакты преподавателя. На странице урока также указаны контакты преподавателя.
Да. После прохождения курса (выполнения всех заданий), Вы получите именной сертификат о прохождении курса.
Сдавать домашние задания необязательно, но, в таком случае, мы не сможем дать Вам обратную связь о проделанной работе и разобрать ошибки. При невыполнении домашних заданий, к сожалению, Вы не получите сертификат о прохождении курса.
Вы можете работать в своем ритме. Поддержка преподавателя, возможность сдавать домашние задания и право на получение сертификата останутся.
