функция ВПР в Excel

В современном мире навыки работы с большими объёмами данных становятся всё более востребованными. А одним из самых удобных инструментов для работы с данными является Microsoft Excel.

Сегодня программы из офисного пакета Microsoft Office применяются не только в офисной работе, но и в самых разных сферах, от информационных технологий до научных исследований. Например, в бизнесе Excel помогает анализировать финансовые данные, строить прогнозы и управлять запасами. В сфере образования и науки электронные таблицы используются для проведения сложных вычислений и обработки статистических данных, а в маркетинге и аналитике — для визуализации информации и отслеживания показателей эффективности.

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

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

Функция ВПР()

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

Давайте вспомним нашу реляционную базу данных из прошлой статьи. У нас есть 3 таблицы Excel:

«Ученики»

рис 13

«Предметы»

рис 14

и сводная таблица «Оценки и домашние задания».

рис 12

Теперь предположим, что нам необходимо узнать, сколько пятёрок получил Михаил.

Конечно, в такой маленькой таблице мы можем просто запомнить ID ученика из таблицы «Ученики» и найти строчки с таким же идентификатором в таблице «Оценки и домашние задания». Но в реальных таблицах таких строк может быть несколько тысяч и вручную искать уже не получится. Более того, может потребоваться информация сразу из нескольких связанных таблиц.

Самым оптимальным вариантом решения такой задачи может быть перенос нужных данных из других таблиц в одну — сводную. А в этой сводной таблице мы уже можм воспользоваться фильтрами и различными условиями.

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

=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])

Давайте подробно разберемся что означает каждый аргумент этой функции.

  • Искомое значение — это тот самый уникальный идентификатор, по которому мы будем сопоставлять данные из разных таблиц. В нашем примере — это «ID ученика»
  • Таблица — здесь мы выбираем диапазон ячеек откуда будем переносить информацию (здесь выберем столбцы с A3 по C5 таблицы «Ученики»)
  • Номер столбца — указываем из какого столбца таблицы нужно вернуть значения. В нашей таблице «Ученики» имена, которые мы и хотим перенести, содержатся во втором столбце.
  • Интервальный просмотр — принимает логическое значение ИСТИНА или ЛОЖЬ (1 или 0, TRUE или FALSE) и задаёт тип поиска: приближённый или точный.

Если выбран интервальный просмотр в режиме ИСТИНА (приближённый поиск), то Excel ищет ближайшее меньшее или равное значение, если точное совпадение не найдено.

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

Например, есть такая таблица с баллами и соответствующими им оценками:

Баллы Оценка
50 2
60 3
75 4
90 5

Если мы ищем оценку для балла 85, функция вернёт 4, так как это ближайшее значение меньше 85. Если значение точно найдено (например, 90), то вернётся оценка 5.

В случае, когда мы работаем с артикулами, идентификаторами сотрудников и другими точными значениями, необходимо использовать интервальный просмотр в режиме ЛОЖЬ (точный поиск). В этом режиме Excel ищет только точное совпадение с искомым значением. Если точное совпадение не найдено, то функция вернёт ошибку #Н/Д (значение не найдено).

Теперь давайте применим функцию ВПР() и перенесём имена учеников в сводную таблицу.

Для этого добавим еще один столбец с заголовком «Имя» в нашу таблицу. В ячейке F3 пропишем формулу: =ВПР(B3;Ученики!A$3:C$5;2;0) и растянем её вниз до конца таблицы.

рис 4

Разберем подробнее аргументы этой формулы. В качестве искомого значения указываем ячейку ряда B той же строки, что и наша формула (B3).

Далее указываем таблицу. Это будет таблица «Ученики» с диапазоном от A3 до C3. Закрепим значения строк с помощью знака $, чтобы они не изменялись при растягивании формулы вниз.

В качестве столбца выбираем второй, где и находятся имена. Интервальный просмотр ставим в значение ЛОЖЬ, проще всего тут прописать эквивалентное значение — 0.

В итоге получаем столбец F, в котором перечислены имена учеников, соответствующие их идентификатору («ID ученика») в каждой строке:

рис 5

Фильтрация значений

Сформируем из наших значений таблицу. Для этого во вкладке «Главная» выберем пункт «Форматировать как таблицу». В открывшемся окне оставляем выбранным пункт «Таблица с заголовками» и подтверждаем.

рис 6

Это действие нам позволяет использовать фильтры для столбцов таблицы. Далее оставим только строки, относящиеся к Михаилу. Для этого нажимаем на кнопку со стрелкой вниз в ячейке «Имя». В открывшемся меню оставляем выбранным только пункт «Михаил»:

рис 7

В результате получим такую таблицу:

рис 8

Теперь же, чтобы посчитать количество пятёрок есть несколько подходов. Можно применить функцию СЧЁТЕСЛИ(), которой передадим диапазон значений оценок и в качестве условия счёта поставим значение 5.

Также можно отфильтровать столбец оценок и оставить только значения 5. Тогда вместо СЧЁТЕСЛИ() будем использовать просто функцию СЧЁТ().

Но обратите внимание, что часть строк при фильтрации просто скрывается, но они никуда не исчезают из таблицы. Так что если мы пропишем такую формулу: =СЧЁТЕСЛИ(D3:D8;5), то получим значение 4, хотя на экране у нас всего две пятёрки

рис 9

Аналогичная ситуация будет и с функцией СЧЁТ(). Даже если мы оставим только пятёрки в столбце с оценками, функция нам все равно вернёт 6 — значение всех ячеек в этом столбце, в которых есть какое-либо значение.

рис 10

В таких случаях, когда используется фильтрация в таблице, к диапазонам следует применять функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ().

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ()

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() в Excel используется для расчёта итоговых значений (например, суммы, среднего, количества) по выбранным данным, при этом учитывая или исключая скрытые строки. Эта функция особенно полезна для анализа больших таблиц с возможностью фильтрации и скрытия строк.

Она имеет следующий синтаксис:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(номер_функции; диапазон1; [диапазон2]; …)

Номер функции — это число, указывающее, какую функцию применять для вычисления итогов. Excel предлагает несколько встроенных функций (сумма, среднее, минимум, максимум и др.), и выбор определяется этим параметром.

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

Номера функций приведены в таблице:

Номер функции (без учёта скрытых строк) Номер функции (с учётом скрытых строк) Описание функции
1 101 СРЗНАЧ
2 102 СЧЁТ
3 103 СЧЁТЗ
4 104 МАКС
5 105 МИН
6 106 ПРОИЗВЕД
7 107 СТАНДОТКЛОН
8 108 СТАНДОТКЛОНП
9 109 СУММ
8 108 СТАНДОТКЛОНП
10 110 ДИСП

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

В итоге, чтобы посчитать количество пятёрок, которые получил Михаил, нам нужно к диапазону оценок D3:D8 в уже отфильтрованной таблице применить функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ() с номером функции 2 (СЧЁТ()): =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2;D3:D8). Тогда мы и получим искомое значение, а именно — 2.

рис 11