Задание 14 ОГЭ по информатике: обработка большого массива данных в таблице

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

Задание 14 проверяет умение работать с готовым большим массивом данных, оформленным в виде электронной таблицы (обычно 1000 строк). Проверяемые навыки:

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

Задание практическое: его выполняют не на бумаге, а в реальном файле электронной таблицы (Excel, LibreOffice Calc, «МойОфис» или аналог), который открывают на компьютере во время экзамена.

Как выглядит формулировка и сколько баллов

Типичная формулировка: дана таблица с данными о тестировании учеников (округ, фамилия, предмет, балл). Нужно:

1. Найти количество учеников, удовлетворяющих условию (например, сдавали информатику и набрали больше определённого балла).
2. Найти средний балл группы учеников по условию (округ или предмет), с точностью не менее двух знаков после запятой.
3. Построить круговую диаграмму, показывающую соотношение количества участников по трём категориям (обычно по предметам или округам).

За задание можно получить от 0 до 2 баллов: обычно 1 балл — за оба числовых ответа (иногда за каждый отдельно), 1 балл — за корректно построенную диаграмму. Точные критерии смотрите в демоверсии текущего года, но логика оценивания именно такая: числа отдельно, диаграмма отдельно.

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

• Устройство таблицы: строки — записи (ученики), столбцы — поля (округ, фамилия, предмет, балл).
• Диапазон данных обычно записывается как $A2:A1001$ (если данные с 1000 строками начинаются со второй строки).
• Функция подсчёта количества по одному условию: $=\text{СЧЁТЕСЛИ}(диапазон\_условия;\ условие)$.
• Функция подсчёта по нескольким условиям: $=\text{СЧЁТЕСЛИМН}(диап1;усл1;диап2;усл2;\dots)$.
• Функция среднего по одному условию: $=\text{СРЗНАЧЕСЛИ}(диап\_условия;условие;диап\_усреднения)$.
• Функция среднего по нескольким условиям: $=\text{СРЗНАЧЕСЛИМН}(диап\_усреднения;диап1;усл1;диап2;усл2;\dots)$.
• Формула среднего значения как определение: $\bar{x}=\dfrac{\sum_{i=1}^{n} x_i}{n}$.
• Округление ответа: если нужно «не менее двух знаков после запятой», можно использовать функцию $=\text{ОКРУГЛ}(значение;2)$ или просто увеличить количество знаков в формате ячейки.
• Круговая диаграмма строится по вспомогательной таблице из 2–3 небольших ячеек: подписи категорий и соответствующие числа (количества).
• Условие в текстовом виде в формулах пишется в кавычках: "информатика", ">600", "В".
• Ссылка на диапазон должна быть точной: если данные заканчиваются на строке 1001, диапазон $A2:A1001$, а не $A2:A1000$.

Методы решения

1. Подсчёт количества по одному условию. Нужно узнать, сколько учеников выбрали физику. Формула: $=\text{СЧЁТЕСЛИ}(C2:C1001;"физика")$. Программа сама пройдёт по всем 1000 строкам и посчитает совпадения.

2. Подсчёт по двум условиям одновременно. Нужно узнать, сколько учеников сдавали информатику и набрали больше 600 баллов. Здесь одно условие текстовое (столбец предмета), второе — числовое (столбец баллов). Формула: $=\text{СЧЁТЕСЛИМН}(C2:C1001;"информатика";D2:D1001;">600")$.

3. Среднее значение по условию. Нужно найти средний балл учеников определённого округа. Формула: $=\text{СРЗНАЧЕСЛИ}(A2:A1001;"С";D2:D1001)$, где первый диапазон — столбец с округом, второй — условие (буква округа), третий — столбец, из которого берутся баллы для усреднения.

4. Комбинация условия по предмету и среднего балла. Если нужно найти средний балл только у тех, кто сдавал конкретный предмет: $=\text{СРЗНАЧЕСЛИ}(C2:C1001;"биология";D2:D1001)$. Логика та же, что и в третьем методе, меняется только столбец условия.

5. Построение диаграммы по трём категориям. Чтобы построить круговую диаграмму соотношения числа участников трёх округов (например, «В», «Зел», «З»), сначала считают количество для каждого округа через $\text{СЧЁТЕСЛИ}$, записывают три числа в отдельные ячейки с подписями рядом, затем выделяют эту маленькую таблицу (подписи + числа) и вставляют круговую диаграмму через меню «Вставка → Диаграмма».

Алгоритм решения по шагам

1. Открыть файл с таблицей, посмотреть заголовки столбцов и понять, что в каком столбце хранится.
2. Определить точный диапазон данных (обычно указано «1000 учеников», значит строки со 2-й по 1001-ю).
3. Для первого вопроса выбрать нужную функцию (СЧЁТЕСЛИ или СЧЁТЕСЛИМН) в зависимости от числа условий и ввести формулу в требуемую ячейку (например, H2).
4. Проверить результат на правдоподобность (число должно быть в разумных пределах, не больше 1000 и не отрицательное).
5. Для второго вопроса выбрать СРЗНАЧЕСЛИ или СРЗНАЧЕСЛИМН, ввести формулу в нужную ячейку (например, H3), при необходимости округлить через формат ячейки или функцию ОКРУГЛ, чтобы было не менее двух знаков после запятой.
6. Для диаграммы создать вспомогательный блок из подписей и значений (по каждой категории вычислить количество отдельной формулой СЧЁТЕСЛИ).
7. Выделить эти данные (подписи и числа вместе) и вставить круговую диаграмму, разместив её верхний левый угол рядом с указанной ячейкой (например, G6).
8. Сохранить файл в исходном формате и с исходным именем, ничего не переименовывая.

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

Пример 1. Таблица содержит округ (A), фамилию (B), предмет (C), балл (D) для 1000 учеников. Нужно найти: 1) сколько учеников сдавали информатику и набрали более 600 баллов; 2) средний балл сдававших информатику; 3) построить диаграмму по округам «В», «Зел», «З».

Шаг 1. Определяем диапазоны: округ — $A2:A1001$, предмет — $C2:C1001$, балл — $D2:D1001$.

Шаг 2. Для первого вопроса нужны два условия сразу: предмет = «информатика» и балл больше 600. Используем СЧЁТЕСЛИМН: $$=\text{СЧЁТЕСЛИМН}(C2:C1001;"информатика";D2:D1001;">600")$$ Вводим формулу в ячейку H2. Результат — 32.

Шаг 3. Для второго вопроса нужен средний балл только у тех, кто сдавал информатику: $$=\text{СРЗНАЧЕСЛИ}(C2:C1001;"информатика";D2:D1001)$$ Вводим в H3, форматируем ячейку на два знака после запятой. Результат — 546,82.

Шаг 4. Для диаграммы считаем количество учеников по трём округам отдельными формулами, например в ячейках F6, F7, F8 подписи «В», «Зел», «З», а рядом в G6, G7, G8 — формулы: $$=\text{СЧЁТЕСЛИ}(A2:A1001;"В")$$ $$=\text{СЧЁТЕСЛИ}(A2:A1001;"Зел")$$ $$=\text{СЧЁТЕСЛИ}(A2:A1001;"З")$$ Затем выделяем блок подписи+числа и строим круговую диаграмму, размещая её левый верхний угол у ячейки G6, как указано в задании.

Пример 2. Столбцы те же: округ, фамилия, любимый предмет, балл. Вопросы: 1) сколько учеников Восточного округа выбрали информатику; 2) средний балл учеников Северного округа; 3) диаграмма по трём предметам — информатика, русский язык, обществознание.

Шаг 1. Для первого вопроса два условия: округ = «В» и предмет = «информатика». Формула: $$=\text{СЧЁТЕСЛИМН}(A2:A1001;"В";C2:C1001;"информатика")$$ Вводим в H2, получаем 10.

Шаг 2. Для второго вопроса нужен средний балл по округу «С»: $$=\text{СРЗНАЧЕСЛИ}(A2:A1001;"С";D2:D1001)$$ Вводим в H3 с форматом на два десятичных знака. Результат — 540,35.

Шаг 3. Для диаграммы считаем количество по трём предметам через СЧЁТЕСЛИ по столбцу C для значений «информатика», «русский язык», «обществознание», размещаем подписи и числа рядом, строим круговую диаграмму у ячейки G6.

Пример 3. Та же структура таблицы. Вопросы: 1) сколько учеников Северо-Восточного округа выбрали математику; 2) средний балл Южного округа; 3) диаграмма по предметам информатика, немецкий язык, обществознание.

Шаг 1. Условия: округ = «СВ» и предмет = «математика»: $$=\text{СЧЁТЕСЛИМН}(A2:A1001;"СВ";C2:C1001;"математика")$$ Результат в H2 — 17.

Шаг 2. Средний балл по округу «Ю»: $$=\text{СРЗНАЧЕСЛИ}(A2:A1001;"Ю";D2:D1001)$$ Результат в H3 (формат — два знака после запятой) — 525,70.

Шаг 3. Строим вспомогательную таблицу для диаграммы: по трём предметам считаем количество через СЧЁТЕСЛИ по столбцу C, значения располагаем рядом с подписями, вставляем круговую диаграмму у ячейки G6.

Во всех трёх примерах логика одинаковая: сначала разбираемся, сколько условий нужно проверить (одно или два), затем выбираем соответствующую функцию — СЧЁТЕСЛИ/СЧЁТЕСЛИМН для количества, СРЗНАЧЕСЛИ/СРЗНАЧЕСЛИМН для среднего, и в конце строим диаграмму по отдельно посчитанным значениям.

Типичные ошибки и ловушки

• Неправильно указан диапазон: забыли учесть заголовок (строка 1) или последнюю строку таблицы, из-за чего формула считает не все 1000 записей.
• Перепутаны столбцы условия и столбец, по которому нужно искать среднее значение (например, в СРЗНАЧЕСЛИ на третьем месте должен быть столбец с баллами, а не с предметом).
• Условие для текста записано без кавычек или с ошибкой в регистре и пробелах (например, «Информатика» вместо «информатика», если в таблице строчные буквы — тогда важно смотреть точное написание в самой таблице).
• Ответ на вопрос со средним баллом дан с одним знаком после запятая, хотя требуется не менее двух — обязательно проверяйте формат ячейки.
• Диаграмма строится не по тем данным: выбраны не только числа, а весь исходный столбец на 1000 строк, из-за чего диаграмма получается бессмысленной. Для диаграммы нужен именно короткий вспомогательный блок из 2–3 значений с подписями.
• Диаграмма размещена не в том месте листа, хотя в задании чётко указано, около какой ячейки должен быть её левый верхний угол.
• Файл сохранён под другим именем или в другом формате — при сдаче экзамена это может привести к потере результата, поэтому сохранять нужно тем же способом (обычно просто Ctrl+S) без переименования.
• Перепутаны местами условия в СЧЁТЕСЛИМН/СРЗНАЧЕСЛИМН: важно, чтобы каждая пара «диапазон-условие» соответствовала друг другу, а не была перемешана случайным образом.

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

Лучший способ подготовки — практика на реальных файлах, а не только чтение теории. Скачайте несколько демонстрационных таблиц с сайта разработчика экзамена, откройте их в той программе, которая будет на экзамене (Excel, LibreOffice Calc или аналог), и тренируйтесь вводить формулы вручную, а не копировать готовые ответы. Полезно завести короткий список формул с примерами использования и держать его перед глазами первое время, пока функции не станут привычными.

Отдельно потренируйтесь строить круговые диаграммы: создайте тестовую таблицу из 3–5 категорий, посчитайте количество для каждой и постройте диаграмму несколько раз, пока процесс не станет автоматическим — от выделения данных до размещения диаграммы в нужном месте листа.

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