
Навигация по странице
Работа с данными в Excel
В современном мире навыки работы с большими объёмами данных становятся всё более востребованными. А одним из самых удобных инструментов для работы с данными является Microsoft Excel.
Сегодня программы из офисного пакета Microsoft Office применяются не только в офисной работе, но и в самых разных сферах, от информационных технологий до научных исследований. Например, в бизнесе Excel помогает анализировать финансовые данные, строить прогнозы и управлять запасами. В сфере образования и науки электронные таблицы используются для проведения сложных вычислений и обработки статистических данных, а в маркетинге и аналитике — для визуализации информации и отслеживания показателей эффективности.
Несмотря на свою простоту, Excel обладает мощными возможностями для обработки и анализа информации, которые часто недооцениваются. В этой статье мы научимся работать с большими массивами данных в Excel, используя такие полезные инструменты, как функция ВПР()
и фильтры.
Эти инструменты позволяют быстро находить нужную информацию, сопоставлять данные из разных таблиц и работать с реляционными базами данных, что особенно актуально для решения задания 3 ЕГЭ по информатике.
Функция ВПР()
При работе с большими объёмами данных, расположенных в нескольких разных таблицах, порой требуется перенести информацию из одной таблицы в другую.
Давайте вспомним нашу реляционную базу данных из прошлой статьи. У нас есть 3 таблицы Excel:
«Ученики»

«Предметы»

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

Теперь предположим, что нам необходимо узнать, сколько пятёрок получил Михаил.
Конечно, в такой маленькой таблице мы можем просто запомнить 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)
и растянем её вниз до конца таблицы.

Разберем подробнее аргументы этой формулы. В качестве искомого значения указываем ячейку ряда B
той же строки, что и наша формула (B3
).
Далее указываем таблицу. Это будет таблица «Ученики» с диапазоном от A3
до C3
. Закрепим значения строк с помощью знака $
, чтобы они не изменялись при растягивании формулы вниз.
В качестве столбца выбираем второй, где и находятся имена. Интервальный просмотр ставим в значение ЛОЖЬ
, проще всего тут прописать эквивалентное значение — 0
.
В итоге получаем столбец F
, в котором перечислены имена учеников, соответствующие их идентификатору («ID ученика») в каждой строке:

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

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

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

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

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

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