Задание 14 ОГЭ по информатике: электронная таблица, формулы СЧЁТЕСЛИ и СРЗНАЧ

Тысяча строк данных — и нужно быстро найти ответ. Вручную не пересчитать, а вот формулами электронной таблицы — за секунды. Задание 14 проверяет именно это: работу с большим массивом через формулы.

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

Содержание

Что нужно знать

Работа с формулами таблиц — курс «Информатика 5-11»: Табличный процессор и Инструменты анализа данных.

Что проверяет задание 14

Дан файл с таблицей из ~1000 строк (например, результаты тестирования учеников). Нужно: 1) посчитать количество записей по условию; 2) найти среднее значение по условию; 3) построить диаграмму по категориям. Ответ — заполненный файл таблицы (проверяется вручную, максимум 3 балла).

Ключевые формулы

=СЧЁТЕСЛИ(диапазон; условие)         -- сколько строк подходит под условие
=СЧЁТЕСЛИМН(усл1;усл2)               -- сколько строк подходит под НЕСКОЛЬКО условий
=СРЗНАЧ(диапазон)                    -- среднее значение диапазона
=СРЗНАЧЕСЛИ(диапазон; условие; диапазон_ср)  -- среднее ПО УСЛОВИЮ

Пример: сколько учеников из округа «В» набрали >600 баллов по информатике
=СЧЁТЕСЛИМН(A:A;"В"; C:C;"информатика"; D:D;">600")

Порядок решения. 1) скачай файл таблицы; 2) для количества — используй СЧЁТЕСЛИ/СЧЁТЕСЛИМН с нужными условиями по столбцам; 3) для среднего — СРЗНАЧЕСЛИ; 4) для диаграммы — выдели итоговые данные по категориям и вставь круговую диаграмму в указанную ячейку.

Как скачать и распаковать архив. 1) Кликни по ссылке на архив (обычно .rar) — браузер сохранит файл в папку «Загрузки» (внизу экрана появится уведомление о скачивании, либо открой папку «Загрузки» через проводник). 2) Найди скачанный файл, нажми на него ПРАВОЙ кнопкой мыши → в меню выбери «Извлечь всё» (Windows) или «Извлечь здесь». 3) Появится новая папка с тем же именем — открой её, внутри и лежат нужные файлы и подпапки. Если ничего не произошло после клика по ссылке — проверь, не заблокировал ли браузер скачивание (иконка в адресной строке), и попробуй скачать ещё раз.

Разбор примера

В таблице: округ (A), предмет (C), балл (D). Сколько учеников из округа «В» получили по информатике больше 600 баллов?
Формула: =СЧЁТЕСЛИМН(A:A;"В";C:C;"информатика";D:D;">600") — три условия сразу: округ, предмет, балл.

Частые ошибки

Считают вручную вместо формул — на 1000 строках это долго и с ошибками.
Забывают округлить среднее до нужного числа знаков (обычно 2 после запятой).
Диаграмму строят не в той ячейке, которая указана в условии.
Путают СРЗНАЧ (весь диапазон) и СРЗНАЧЕСЛИ (по условию).

Проверь себя

Контрольный вопрос. Какая формула посчитает количество строк, где в столбце A стоит значение «Москва»?

А=СЧЁТЕСЛИ(A:A;»Москва»)

Б=СРЗНАЧ(A:A;»Москва»)

В=СУММ(A:A)

Г=СЧЁТ(A:A)

Подсказка: СЧЁТЕСЛИ считает количество ячеек по условию.

Онлайн-проверка ответа появится позже

Контрольный вопрос. Нужно найти среднее значение баллов (столбец D) только у учеников из округа «В» (столбец A). Какая формула подходит?

А=СРЗНАЧЕСЛИ(A:A;»В»;D:D)

Б=СРЗНАЧ(D:D)

В=СЧЁТЕСЛИ(A:A;»В»)

Г=СУММЕСЛИ(A:A;»В»)

Подсказка: СРЗНАЧЕСЛИ считает среднее ПО УСЛОВИЮ в другом столбце.

Онлайн-проверка ответа появится позже

Задание. Нужно посчитать количество учеников, у которых в столбце B «Пол» стоит «М» И в столбце C «Балл» больше 80. Сколько условий нужно указать в формуле СЧЁТЕСЛИМН? Впиши число.

Подсказка: Два условия: пол=М, балл>80.

✅ Готово, если: ты ввёл(а) верный ответ, и онлайн-проверка его приняла.

Онлайн-проверка ответа появится позже

Итог

Итог. СЧЁТЕСЛИ(МН) — количество по условию(ям), СРЗНАЧЕСЛИ — среднее по условию. Строй диаграмму в указанной ячейке. Проверяй округление ответа.

Тренировка (часть 2, ручная проверка диаграммы). Ниже — 25 реальных задач с РешуОГЭ. Скачай файл-таблицу по ссылке, найди оба числовых ответа формулами — впиши их здесь для самопроверки. Полный балл (с диаграммой) сверяй с критериями на сайте-источнике:
Банк задач 14 на РешуОГЭ

Видео. ▶ Видеотеория задания 14 · ▶ Видеоразбор задания 14 (обзор)

Задания для тренировки

Банк задач с РешуОГЭ по заданию 14 (25 шт., часть 2 — ручная проверка по критериям сайта). У части задач есть видеоразбор.

← Назад: задание 13 · ⌂ В начало курса · К навигатору задания 14 →