Функция ВПР в Excel для чайников и не только

Как сделать ВПР в Excel: понятная пошаговая инструкция

Для начала на простом примере разберем, как работает функция ВПР в Excel. Предположим, у нас есть две таблицы. Первая – это прайс-лист с наименованиями и ценами. Вторая – это заказ на покупку некоторых из этих товаров. Искать в прайс листе нужный товар и руками вписывать в заказ его цену – занятие очень утомительное. Ведь прайс с ценами может насчитывать сотни строк. Нам необходимо сделать всё автоматически.

Нам необходимо обнаружить интересующее нас наименование в первом столбце и возвратить (то есть показать в ответ на наш запрос) содержимое из желаемого столбца той же строки, где находится наименование.

Наш прайс-лист расположен в столбцах А и В. Список покупок – в E-H. Допустим, первая позиция в списке покупок – бананы. Нам нужно в столбце A, где указаны все наименования, найти этот товар, затем его цену поместить в ячейку G2.

Для этого в G2 запишем следующую формулу:

=ВПР(E2;$A$2:$B$7;2;0)

А теперь разберем подробно, как сделать ВПР.

1. Мы берем значение из E2.
2. Ищем точное совпадение (поскольку четвертым параметром указан 0) в диапазоне $A$2:$B$7 в первой его колонке (крайней левой). Обратите внимание, что лучше сразу же использовать абсолютные ссылки на прайс-лист, чтобы при копировании этой формулы ссылка не «соскользнула».
3. Если товар будет найден, то нужно перейти во второй столбец диапазона (на это указывает третий параметр = 2).
4. Взять из него цену и вставить ее в нашу ячейку G2.

Получилось? Теперь просто скопируйте формулу из G2 в G3:G8.

Отчет о продажах готов.

Также чтобы понять, что такое точное совпадение, попробуйте в A5 или в E2 изменить наименование товара. К примеру, добавьте пробел в конце. Внешне ничего не изменилось, но вы сразу же получите ошибку #Н/Д. То есть, товар не был обнаружен. В то же время, таких случайных ошибок можно легко избежать, о чем мы поговорим отдельно.

Особо остановимся на четвертом параметре. Мы указали ноль (можно было написать ЛОЖЬ), что означает «точный поиск». А что, если забыть его указать и закончить номером столбца, из которого извлекаются нужные данные?

Давайте еще раз шаг за шагом разберем, что в этом случае будет происходить.

1. Берем значение из E2.
2. Начинаем его искать в крайней левой колонке диапазона $A$2:$B$7, то есть в столбце A. Поскольку в A2 совпадение не найдено, смотрим дальше: что находится ниже.
3. Там обнаруживаем товар «Сливы». При этом предполагается, что наш список отсортирован по алфавиту. Ведь именно это – главное условие поиска приблизительного совпадения.
4. Поскольку в сортированном списке «сливы» находятся ниже, чем «бананы», то функция принимает решение, что дальше искать слово, начинающееся на «Б» нет смысла. Процесс можно остановить. И остаться на букве «А». То есть, там и находится наиболее близкое значение.
5. Поскольку поиск завершен, переходим из A2 во второй столбец, то есть в B. Вставляем данные из B2 в G2 как результат вычислений.

К сожалению, «бананы» были в нашем прайс-листе ниже, но до них просто «не дошел ход». И в список покупок теперь записана неправильная цена.

При помощи этой инструкции мы рассмотрели только основы. А как реально этим можно пользоваться?

Можно ли отказаться от ВПР?

Обязанность школьников писать ВПР никаким федеральным законом не установлена. Методические указания Рособрнадзора, где сказано, что работу должны писать все учащиеся, касается образовательных организаций, а не граждан. То есть школа обязана такую работу провести, а вот участвовать в ней или нет – решать школьнику и его родителю. Но это на бумаге, а на практике отказ от написания ВПР может быть чреват определёнными сложностями.

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

Видео

Автозаполнение

В конце протягиваем формулу вниз до конца, в результате чего происходит автозаполнение.

Чтобы функция ВПР правильно сработала во время автозаполнения, искомое значение должно быть относительной ссылкой, а таблица – абсолютной. 

• В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.

  Читайте также: Создание тульпы, или как завести воображаемого друга. Чем опасна тульпа

• Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу. 

Сдаём ВПР: о чём важно помнить во время работы

Пропускайте сложные задания. Если какой-то пример даётся тяжело, просто пропустите его. Вы сможете вернуться к нему позже, если останется время. Лучше за полчаса сделать 10 упражнений, за которые вы получите по одному баллу, чем провести это время за тщетным решением 5-балльного задания.

Используйте черновик. Делать это нужно только со сложными заданиями, чтобы осталось время переписать в чистовик. Черновики попросят сдать, но оценивать не будут.

Перепроверяйте и не спешите сдавать. Важно использовать всё время, отведённое на выполнение работы. Дополнительная проверка поможет поймать глупую ошибку, описку, пропуск задания. Поэтому, если всё уже сделано, а времени до конца урока ещё много, не стоит торопиться сдавать работу.

Схемы работы формул

Следствия для формул вида I

1. Формулы можно использовать для распределения значений по диапазонам.
2. Если первый столбец <ГДЕ> содержит повторяющиеся значения и правильно отсортирован, то будет возвращена последняя из строк с повторяющимися значениями.
3. Если искать значение заведомо большее, чем может содержать первый столбец, то можно легко находить последнюю строку таблицы, что бывает довольно ценно.
4. Данный вид вернёт ошибку #Н/Д, только если не найдёт значения меньше или равное искомому.
5. Понять, что формула возвращает неправильные значения, в случае если ваш массив не отсортирован, довольно затруднительно.

Следствия для формул вида II

Если искомое значение встречается в первом столбце массива несколько раз, то формула выберет первую строку для последующего извлечения данных.

Отношение к ВПР

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

Это попытались учесть и ввели принцип недублирования предмета. Например, если выпускник будет сдавать ЕГЭ по математике, то он не пишет ВПР по этому предмету, если по истории — то не пишет по истории и т.д

Суть ВПР в том, чтобы определить уровень знаний ученика, и формально даже плохие результаты не должны влиять на итоговую оценку школьника. А вот преподавателям и руководству школы за результаты ВПР приходится отчитываться, поэтому часто подготовка (некоторые учителя употребляют слово «натаскивание») к ВПР начинается почти за год и отнимает много часов по предмету.

Применение функции ВПР на практике

Допустим, у нас есть таблица с продажами товаров. В ней отсутствуют данные по цене каждой позиции, однако, они есть в другой таблице рядом.

Наша задача – заполнить недостающие в основной таблице цены, чтобы мы могли посчитать итоговые суммы по продажам.

Смотрите также: “Автофильтр в Excel: как сделать, использовать, отменить”

Порядок действий в данном случае следующий:

1. Щелкаем по самой верхней ячейке столбца, значения которого мы хотим заполнить (в нашем случае – это C2). После этого нажимаем на кнопку “Вставить функцию” (fx) слева от строки формул.
2. В окне вставки функции нам нужна категория “Ссылки и массивы”, в которой выбираем оператор “ВПР” и щелкаем OK.
3. Теперь предстоит правильно заполнить аргументы функции:
   • в поле “Искомое_значение” указываем адрес ячейки в основной таблице, по значению которой будет производиться поиск соответствия во второй таблице с ценами. Координаты можно прописать вручную, либо, находясь курсивом в поле для ввода информации просто кликнуть в самой таблице по нужной ячейке.
   • переходим к аргументу “Таблица”. Здесь мы указываем координаты таблицы (или ее отдельной части), в которой будет выполняться поиск искомого значения. При этом важно, чтобы первый столбец указанного диапазона содержал именно те данные, по которым будет осуществляться поиск и сопоставление значений (в нашем случае – это наименования позиций). И, конечно же, в указанные координаты должны попадать ячейки с информацией, которая будет “подтягиваться” в основную таблицу (в нашем случае – это цены).Примечание: Таблица может располагаться как на том же листе, что и основная, так и на других листах книги.
   • Чтобы координаты, указанные в аргументе “Таблица” не сместились при возможных дальнейших корректировках данных, делаем их абсолютными, так как по умолчанию они являются относительными. Для этого выполняем выделение всей ссылки в поле и нажимаем кнопку F4. В результате перед всеми обозначениями строк и столбцов будут добавлены символы “$”.
   • в поле аргумента “Номер_столбца” указываем порядковый номер столбца, значения которого нужно вставить в основную таблицу при совпадении искомого значения. В нашем случае это столбец с ценами, который занимает вторую позицию в указанной выше области (аргумент “Таблица”).
   • в значении аргумента “Интервальный_просмотр” можно указать два значения:
     • ЛОЖЬ (0) – результат будет выводиться только в случае точного совпадения;
     • ИСТИНА (1) – будут выводиться результаты по приближенным совпадениям.
     • мы выбираем первый вариант, так как нам важна предельная точность.
   • Когда все готово, нажимаем OK.
4. В выбранной ячейку, куда мы вставили функцию, автоматически вставилась требуемая цена.Причем, если мы изменим значение во второй таблице с ценами, так как данные взаимосвязаны посредством функции, то и в основной таблице произойдут соответствующие изменения.
5. Чтобы автоматически заполнить аналогичными данными другие ячейки столбца, воспользуемся Маркером заполнения. Для этого наводим курсор мыши на нижний правый угол ячейки с результатом, когда появится черный плюсик, зажав левую кнопку мыши тянем его вниз до конца таблицы или до той ячейки, которую нужно заполнить.
6. В итоге нам удалось получить в основной таблице все данные по ценам, а также посчитать итоговые суммы по продажам, что и требовалось сделать.

Как проводят ВПР?

Варианты проверочных работ формируются для каждой школы индивидуально

на основе контрольно-измерительных материалов, разработанных Рособрнадзором — главным регулятором качества образования в стране.

Все включённые в ВПР задания стандартизированы, то есть имеют одинаковую структуру и уровень сложности. Они составлены с учётом основных образовательных программ и содержания учебников, используемых в СОШ. Задания могут повторяться, но существование двух полностью одинаковых контрольных работ по какому-либо предмету исключено, утверждают в Рособрнадзоре.

В ходе написания контрольной ученикам запрещено пользоваться учебниками, рабочими тетрадями, какими-либо справочниками.

Проверяют ВПР учителя школы в день написания. Критерии оценки выполнения заданий также расписаны и стандартизированы, чтобы снизить риски «человеческого фактора» и необъективного оценивания. Результаты вносят в единую информационную систему, с данными которой могут работать эксперты.

Выполняются всероссийские проверочные работы на специальных обезличенных бланках, где вместо имени и фамилии ученика указывают специальный четырёхзначный код. Узнать результат можно у учителя или на сайте общеобразовательного учреждения при помощи кода, выданного перед выполнением контрольной.

Быстрое сравнение двух таблиц с помощью ВПР

Функция помогает сопоставить значения в огромных таблицах. Допустим, поменялся прайс. Нам нужно сравнить старые цены с новыми ценами.

1. В старом прайсе делаем столбец «Новая цена».

2. Выделяем первую ячейку и выбираем функцию ВПР. Задаем аргументы (см. выше). Для нашего примера: . Это значит, что нужно взять наименование материала из диапазона А2:А15, посмотреть его в «Новом прайсе» в столбце А. Затем взять данные из второго столбца нового прайса (новую цену) и подставить их в ячейку С2.

Данные, представленные таким образом, можно сопоставлять. Находить численную и процентную разницу.

Начало работы

Для построения синтаксиса функции ВПР вам потребуется следующая информация:

1. Значение, которое вам нужно найти, то есть искомое значение.

2. Диапазон, в котором находится искомое значение. Помните, что для правильной работы функции ВПР искомое значение всегда должно находиться в первом столбце диапазона. Например, если искомое значение находится в ячейке C2, диапазон должен начинаться с C.

3. Номер столбца в диапазоне, содержащий возвращаемое значение. Например, если в качестве диапазона указать B2:D11, следует посчитать B первым столбцом, C — вторым и так далее.

4. При желании вы можете указать слово ИСТИНА, если вам достаточно приблизительного совпадения, или слово ЛОЖЬ, если вам требуется точное совпадение возвращаемого значения. Если вы ничего не указываете, по умолчанию всегда подразумевается вариант ИСТИНА, то есть приблизительное совпадение.

Теперь объедините все перечисленное выше аргументы следующим образом:

=ВLOOKUP(искомые значения, диапазон, содержащий искомые значения, номер столбца в диапазоне, содержащий возвращаемую величину, приблизительное совпадение (ИСТИНА) или Точное совпадение (ЛОЖЬ)).

Что делать?

Во-первых,  успокоиться. Ничего такого, с чем действительно не смог бы справиться среднестатистический ученик, в ВПР нет. 

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

• узнать, из каких заданий состоит ВПР;
• понимать, которые из них чаще всего вызывают трудности;

Размеренная подготовка поможет снять тревожность у всех — учителей, детей и родителей — и заменить механическое «натаскивание» на осознанную планомерную подготовку. Тут есть сразу два плюса: возможность хорошо написать ВПР и ликвидировать пробелы в знаниях. 

Как готовиться?

Больше внимания уделять работе с текстом

На уроках русского и литературного чтения в начальной школе и уроках литературы в 5-8 классах:

• выделять основную мысль текста;
• делить его на смысловые части;
• предлагать задания на восстановление последовательности текста. 

Работайте в классе с ключевыми словами и фразами, ищите лишние и недостающие фразы. Учите детей составлять план текста — это поможет объединить все перечисленные навыки.

Развивать речь и ежедневно уделять внимание фольклору:

• разъяснять смысл пословиц и поговорок;
• подбирать подходящие пословицы для выражения главной мысли произведения к образу главных героев, к морали произведения, к конкретным ситуациям.

Дело здесь не в том, что задания с пословицами и поговорками есть в ВПР. Работа с фольклором развивает речь, мышление, пробуждает интерес к родному языку. При анализе литературного произведения говорите о жизни самих школьников, ищите параллели, сопоставляйте их жизненные ситуации с ситуациями, описанными в произведениях.

Дома

Разберитесь, что из себя представляют задания ВПР по русскому — посмотрите демоверсии, полистайте сборники по подготовке к ВПР. 

Не избегайте онлайн-платформ: например, на образовательной платформе iSMART есть тренировочные задания по подготовке к ВПР по русскому языку с 4 по 8 класс. Задания платформы полностью соответствуют заданиям ВПР.

Их отличительная особенность в том, что каждое задание — это тестовый вариант с выбором правильного ответа. Кроме того, в каждом задании есть пояснение к правильному ответу. Благодаря выполнению заданий ребенку удается «ухватить их суть» и потом перейти к выполнению тех же тестов, но уже с самостоятельной формулировкой ответа, как этого требует задание ВПР.

Если ребенку сложно выполнять одни и те же задания , можно отработать проблемную тему более тщательно в удобное для ребенка время и в удобном темпе.

В каждом классе ребенок может освоить сложную для него тему самостоятельно или с помощью родителей благодаря системе подсказок — в них есть подробное объяснение каждого задания. И, конечно, не забывайте читать с детьми. Чем больше ребенок читает, тем шире его кругозор, тем ярче и образнее речь, тем выше грамотность.

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

ВПР — это не экзамен, а мониторинг. Не нагнетайте атмосферу, не запугивайте ребенка. Успокойтесь сами и научите ребенка не паниковать, быть уверенным в своих силах. Дайте ему понять:

«Ты справишься!»

«Ты все знаешь, бояться нечего!»

«Мы в тебя верим!»

Тогда ВПР станет для ученика просто очередной контрольной, которая покажет, как он учился весь год, какой материал усвоил лучше, а какой хуже.

Теги