Тест: Насколько хорошо вы знаете функцию ВПР в Excel?
Ответьте на 10 вопросов за 4 минуты, узнайте свой реальный уровень и получите чек-лист по исправлению ошибок в формулах.
Тест: Насколько хорошо вы знаете функцию ВПР в Excel?
Ответьте на 10 вопросов за 4 минуты, узнайте свой реальный уровень и получите чек-лист по исправлению ошибок в формулах.
Пройдите тест, если у вас:
- Постоянно ломаются формулы и вылезает ошибка #Н/Д
- Тратите часы на ручное сопоставление таблиц
- Хотите проверить, готовы ли вы к техническому собеседованию
Кому стоит пройти этот тест
Насколько хорошо ты владеешь функцией ВПР?
Пройди тестирование, чтобы узнать уровень владения функцией ВПР.
Какими функциями поиска данных в Excel Вы пользуетесь? (можно выбрать несколько)
ВПР - функция для поиска по столбцам. Она самая популярная.
ГПР - аналог функции ВПР, но ищет эта функция значение по строкам, а не по столбцам.
Интересно в каких случаях Вы используете эту функцию. Она ищет, только если данные отсортированы по возрастанию. Лично мы (сотрудники школы Pro-excel) этой функцией не пользуемся.
О, сладкая парочка ИНДЕКС ПОИСКПОЗ! Если бы не необходимость печатать длинное название обеих функции при поиске данных, то этот тандем явно был бы на первом месте среди функций поиска. Поиск с ними абсолютно логичен и понятен.
Что умеет искать функция ВПР? (можно выбрать несколько вариантов)
Верно! Это самый популярный режим работы ВПР. Мы используем его, когда нужно найти конкретный артикул, ID сотрудника или название товара.
Верно! При приблизительном поиске (когда последний аргумент равен 1 или ИСТИНА) ВПР возвращает ближайшее наименьшее значение. Это идеальный инструмент для расчета прогрессирующих премий (в зависимости от объемов продаж) или размера скидок (в зависимости от суммы покупки).
Неверно. К сожалению, ВПР технически не умеет искать ближайшее наибольшее. Пример такой задачи — подобрать насос, мощность которого должна быть не меньше заданного значения. Для этого в Excel приходится использовать другие связки функций.
Мимо, но за юмор плюс! Судя по ответу, вы явно перетрудились. Срочно закройте ноутбук, заварите чай и идите отдыхать. Иначе ещё чуть-чуть, и вы начнёте ждать квартальную премию от фикуса в коридоре (а он, поверьте, тоже выдаст вам только #Н/Д).
Неверно. ВПР всегда возвращает только первое найденное совпадение сверху вниз. Найти и вывести сразу все дубликаты строк эта функция не способна.
Перед Вами таблица с данными о сотрудниках. Вам нужно вывести информацию о должности сотрудника по фамилии Кругов. Какой функцией Вы воспользуетесь.
Верно! ВПР — это вертикальный просмотр. Функция идет сверху вниз по первому столбцу, находит нужную фамилию и возвращает значение из соседней ячейки справа. Так как фамилии сотрудников у нас расположены в столбце, ВПР — идеальный выбор!
Неверно. ГПР — это горизонтальный просмотр. Эта функция ищет значение слева направо в строке. В нашем примере данные сотрудников идут вертикально, поэтому ГПР здесь не поможет.
Эх, если бы такая функция существовала в Excel... Мир стал бы чуточку счастливее, а отчеты сдавались бы сами собой! Функция отличная, но для поиска должности Кругова всё же придется применить ВПР. А кофе обязательно выпейте, но уже после успешного прохождения этого теста! ☕
Перед Вами словарь. В левом столбце записано английское слово, в правом — русское. Какой вариант перевода можно найти, используя функцию ВПР?
Верно! Функция ВПР всегда ищет ключевое слово в крайнем левом столбце выделенного диапазона и возвращает значение из столбца, который расположен правее. Так как английские слова слева, ВПР легко найдет для них русский перевод.
Неверно. Увы, ВПР не умеет искать «налево». Так как русские слова находятся в правом столбце, функция просто не сможет заглянуть обратно в левый столбец за английским вариантом.
Неверно. ВПР — это «одностороннее движение». Она сработает только для перевода с английского на русский. Чтобы переводить в обратную сторону, вам придется либо физически поменять столбцы местами, либо использовать более гибкие инструменты: «сладкую парочку» ИНДЕКС + ПОИСКПОЗ или современную функцию ПРОСМОТРХ.
Мимо, но забавно! Астрологи уверяют, что в период ретроградного Меркурия количество ошибок #Н/Д увеличивается вдвое. Но на самом деле ВПР — парень стабильный и предсказуемый. Он всегда ищет строго в крайнем левом столбце и выдает то, что правее. Никакой магии, только суровая геометрия таблиц. Возвращайтесь на Землю, я и функции поиска ждем Вас! 🪐
Выберите формулу, которую нужно записать в ячейку I2, чтобы вывести должность сотрудницы Елисеевой (фамилия указана в ячейке I1):
Неверно. Четвертый параметр равен 1 (или ИСТИНА), что включает приблизительный поиск. Для текстовых данных (таких как фамилии) нам нужен строго точный поиск, иначе Excel может вернуть должность совершенно другого человека. Четвертый параметр должен быть равен 0.
Неверно. Здесь четвертый параметр вообще опущен. По умолчанию Excel считает его равным 1 и включает приблизительный поиск. Как мы уже знаем, для поиска конкретной фамилии это не подходит — нужен точный поиск и 0 на конце.
Близко, но не то. Поиск точный (стоит 0), но третий аргумент равен 3. Функция отсчитает три столбца вправо и вернет данные из столбца C — то есть Отдел, а нам нужна Должность.
Абсолютно верно! Всё указано идеально: ищем фамилию из I1, смотрим в таблице A:F, забираем данные из 4-го столбца (Должность) и делаем это с точностью ювелира благодаря 0 (или ЛОЖЬ) в конце формулы.
В ячейке K2 написана формула =ВПР(K1;A:H;4;0). Почему вместо должности отображается дата?
Неверно. Номер столбца как раз указан — это цифра 4 (третий аргумент). Функция честно берет данные из 4-го по счету столбца диапазона.
Абсолютно верно! Это один из главных минусов ВПР. Функция абсолютно «слепа» к дизайну таблицы. Ей всё равно, скрыли вы столбцы B и C руками или нет — она всё равно считает их. В итоге 4-м по счету в диапазоне A:H оказался столбец D (где как раз и лежали даты), а реальная должность уехала в 6-й столбец (F).
Шутка хорошая, но нет! Искусственный интеллект в Excel, может, и есть, но ВПР — это старый добрый робот, который строго следует математике. Он понятия не имеет, что вам нужно, и просто считает столбцы слева направо. Так что возвращаемся к ручной настройке индексов!
Столбец A содержит имена сотрудников: фамилия и инициалы. Столбец B - объем продаж сотрудника. Нужно вывести информацию о сотруднике по фамилии “Кругов”. Имя и отчество Вы не знаете. Сможет ли функция =ВПР("Кругов";A:B;2;0) найти объем продаж сотрудника?
Неверно. Функция вернет сообщение об ошибке #Н/Д. При точном поиске (когда на конце стоит 0) ВПР требует идеального совпадения. Так как ячейки «Кругов» в чистом виде в столбце A нет (там написано «Кругов А. Я.»), функция его не увидит.
Абсолютно верно! ВПР не сможет найти сотрудника, потому что искомое значение и текст в таблице должны совпадать на 100%.💡 Лайфхак: Чтобы ВПР всё-таки сработала по части фамилии, используйте знак подстановки * (звёздочку), которая заменяет любые символы. Формула вида =ВПР("Кругов*";A:B;2;0) легко решила бы эту задачу!
Железная логика, но балл мы вам не дадим! Правильный ответ действительно Нет, но по техническим причинам: ВПР требует идеального совпадения, а «Кругов» и «Кругов А. Я.» для него — два абсолютно разных человека. Excel не умеет угадывать отчество, даже если вы очень устали. Закройте ноутбук и переложите эту задачу на самого Кругова.
Из прошлого примера Вы узнали, что, зная только фамилию, нельзя найти данные из представленной таблицы (так как столбец A содержит инфо и о фамилии, и инициалах).
В подобных случаях, когда Вы знаете только часть данных (знаете только фамилию, но не помните инициалы), выручают подстановочные знаки. Какой подстановочный знак использовать, чтобы ВПР нашла сотрудника в таблице?
В подобных случаях, когда Вы знаете только часть данных (знаете только фамилию, но не помните инициалы), выручают подстановочные знаки. Какой подстановочный знак использовать, чтобы ВПР нашла сотрудника в таблице?
Не совсем так. Вопросительный знак ? заменяет строго один любой символ. В нашей таблице после фамилии идут инициалы (две буквы, две точки и пробелы) — всего около 5–6 символов. Чтобы ВПР сработала с этим знаком, пришлось бы писать кучу вопросов: "Кругов??????". Это неудобно.
Абсолютно верно! Звёздочка * заменяет любое количество любых символов (или даже их отсутствие). Написав "Кругов*", вы говорите функции: «Найди мне строку, которая начинается на "Кругов", а что там написано дальше — не имеет значения».
Эх, если бы Excel понимал язык денежных знаков! Но, увы, правильный ответ — Кругов*. Именно звездочка заменяет любое количество символов и спасает от необходимости вычислять, кто он там — Кругов А. Я. или Кругов И. И. А ваш вариант со знаком доллара идеален для отправки руководству...
В правой таблице (диапазон E:F) указан размер скидки в зависимости от накопленной суммы покупок: чем больше сумма, тем выше процент.
Слева указаны данные клиента. Сумма его покупок равна 5 300 руб. (ячейка B2). Какую формулу правильнее использовать, чтобы автоматически определить размер скидки для клиента?
Слева указаны данные клиента. Сумма его покупок равна 5 300 руб. (ячейка B2). Какую формулу правильнее использовать, чтобы автоматически определить размер скидки для клиента?
Формула сработает, но это «костыль». Представьте, что градация скидок изменится или их станет не 4, а 15? Вам придется переписывать эту огромную и запутанную цепочку ЕСЛИ, в которой легко ошибиться. Зачем строить лабиринты, если есть ВПР?
Неверно. Из-за нуля на конце включен точный поиск. Функция будет искать в таблице скидок именно число 5300. Скорее всего, такой круглой суммы в шкале скидок нет (там обычно указаны пороги: 0, 3000, 5000, 10000), поэтому формула вернет ошибку #Н/Д.
Абсолютно верно! Единица в конце включает приблизительный поиск. ВПР посмотрит на сумму 5300, спустится по таблице скидок и найдет ближайшее наименьшее значение (например, порог в 5000 руб.) и корректно подтянет нужный процент. Это идеальное решение! (Главное условие — шкала скидок в столбце E должна быть отсортирована по возрастанию).
Неверно. Функция ГПР предназначена для горизонтального поиска в строках. А наша таблица со шкалой скидок расположена вертикально (в столбцах E:F), поэтому здесь применима только ВПР.
Продолжаем прошлый кейс. В правой таблице (E:F) указана шкала скидок. У клиента сумма покупок в ячейке B2 равна 9 250 руб. В ячейке C2 записана верная формула =ВПР(B2;E:F;2;1). Однако функция вернула скидку 5%, хотя по логике должна быть 7% (так как сумма больше 9 001 руб.).
Почему функция ВПР выдала неверный результат?
Почему функция ВПР выдала неверный результат?
Неверно. Если мы заменим 1 на 0, функция перейдет в режим точного поиска. Она начнет искать в таблице скидок именно число 9250 и, не найдя его, просто выдаст ошибку #Н/Д. Для интервалов сумм нам по-прежнему нужен именно приблизительный поиск (1).
Мимо. Проблема с текстовым форматом чисел часто ломает ВПР при точном поиске, но в данном кейсе причина в другом. Даже если форматы совпадают, функция всё равно ошибается.
Абсолютно верно! Бинго! 🎯 Это железное и самое важное правило для приблизительного поиска ВПР (когда на конце стоит 1). Если пороги сумм в столбце E разбросаны в случайном порядке, алгоритм ВПР просто «сходит с ума», прекращает поиск раньше времени и выдает случайный некорректный результат. Стоит отсортировать таблицу от меньшего к большему — и формула тут же оживет!
Путь в тысячу миль начинается с первого шага.
Вы пока не знакомы с функцией ВПР и её синтаксисом. Но это отличная стартовая точка! Чтобы стать экспертом в поиске данных, нужно просто немного попрактиковаться. Скорее записывайтесь на курс «ВПР и ПРОСМОТРХ: Искусство точного поиска в Excel». Уже с первых уроков вы почувствуете уверенность, начнете щелкать задачи как орешки, а к концу обучения перейдете на «ты» с любыми таблицами!
Неудачи непременно будут, и то, как вы с ними справитесь, будет важнейшим показателем того, добьетесь ли вы успеха.
Видно, что у вас уже есть определенный опыт работы с ВПР. В то же время тест показал, что в знаниях остаются обидные пробелы, из-за которых формулы на работе могут внезапно ломаться.
Я рекомендую вам подойти к изучению функций поиска систематизированно. На курсе мы разберем множество интересных и непростых практических примеров, чтобы довести ваши навыки до автоматизма. Помимо ВПР, мы подчиним себе легендарный тандем ИНДЕКС + ПОИСКПОЗ и освоим суперсовременную функцию ПРОСМОТРХ!
Я рекомендую вам подойти к изучению функций поиска систематизированно. На курсе мы разберем множество интересных и непростых практических примеров, чтобы довести ваши навыки до автоматизма. Помимо ВПР, мы подчиним себе легендарный тандем ИНДЕКС + ПОИСКПОЗ и освоим суперсовременную функцию ПРОСМОТРХ!
Талантливый человек стремится использовать время, а средний - его убить.
Поздравляю! Вы блестяще справились с тестом! Вы не просто умеете писать функцию ВПР, но и прекрасно знаете её скрытые ловушки, ограничения и тонкости (вроде скрытых столбцов и знаков подстановки). Прекрасный результат, так держать!
Привет! Я — Ольга Мичурина.
Мой путь к созданию школы Excelsior соткан из двух страстей: строгой науки и визуального искусства.
Я создала школу Excel для тех, кто хочет укротить формулы без скуки.
- Математический фундамент: 6 лет магистратуры прикладной математики, диплом британского университета Heriot-Watt и более 6 лет моделирования недр в проектном институте «Роснефти».
- Реальный бизнес: Я круто изменила траекторию и создала кондитерскую онлайн-школу, которой успешно руковожу почти 10 лет.
Я создала школу Excel для тех, кто хочет укротить формулы без скуки.
Остались вопросы? Напишите нам
Ответы на частые вопросы
Тест разработан практикующими аналитиками и проверяет не просто базовый синтаксис ВПР, а реальные боевые нюансы. Вы столкнетесь с вопросами на использование подстановочных знаков (* и ?) для поиска по части слова, разберете логику выбора третьего параметра (номер столбца) и подводные камни, которые возникают, если внутри таблицы есть скрытые столбцы. Также мы проверим, понимаете ли вы разницу между точным совпадением и скрытыми угрозами приблизительного поиска.
Многие знают, что 0 (или ЛОЖЬ) ищет точное совпадение, а 1 (или ИСТИНА) — приблизительное. Но главная ловушка в том, что при указании 1 Excel требует, чтобы первый столбец таблицы был строго отсортирован по возрастанию. Если этого не сделать, функция выдаст случайное неверное значение или ошибку, которую визуально сложно заметить. Тест покажет, умеете ли вы ловить такие баги.
Классическая ВПР жестко привязана к цифре в третьем параметре (порядковому номеру столбца). Если вы или ваш коллега случайно скроете, удалите или добавите новый столбец внутрь диапазона таблицы, ВПР продолжит отсчитывать старый номер. В итоге формула начнет подтягивать совершенно другие данные, ломая всю аналитику. В тесте есть вопрос, проверяющий вашу готовность к таким офисным сюрпризам.
Они незаменимы, когда вам нужно найти данные, зная лишь часть текстового кода, артикула или фамилии. Звездочка (*) заменяет любое количество символов, а вопросительный знак (?) — один конкретный символ. Это продвинутый уровень владения функцией, который в разы ускоряет сведение «кривых» выгрузок из CRM или маркетплейсов.
Не паниковать, а ликвидировать пробелы. Специально для тех, кто хочет перестать воевать с формулами и пугаться знаков #Н/Д или #ЗНАЧ!, мы создали 8-урочный интенсив. На нем мы не только разберем ВПР до косточек, но и освоим современную суперфункцию ПРОСМОТРХ, которая лишена всех недостатков старого ВПР.
Функция ПРОСМОТРХ (XLOOKUP) действительно мощнее и проще, но у нее есть одно главное ограничение — она доступна только в Office 365 и самых свежих версиях Excel. На реальной работе вы неизбежно столкнетесь со старыми файлами коллег, отчетами клиентов, выгрузками из 1С или CRM-систем, которые годами настраивались на классической ВПР. Если вы не знаете нюансов ВПР (например, как ведут себя скрытые столбцы или как работает точный поиск), вы не сможете работать с чужими таблицами и исправлять в них ошибки. ВПР — это международный стандарт автоматизации, который обязан знать каждый специалист.
Нейросети — отличные помощники, но они регулярно ошибаются в логике формул и «галлюцинируют», выдавая несуществующие аргументы за правильные. Без понимания базы Excel вы не сможете проверить работу ИИ и рискуете сдать руководству отчет, который окажется красивой, но абсолютно неверной выдумкой робота. Кроме того, чтобы составить для ChatGPT правильный промпт (ТЗ) на написание формулы, вы должны сами четко понимать архитектуру связей в таблице. Интенсив учит вас мыслить структурами, чтобы вы управляли нейросетью как цифровым подмастерьем, а не слепо копировали её ответы.
