Задание 14 — практическое задание ОГЭ по информатике, в котором требуется выполнить обработку данных в электронных таблицах (Microsoft Excel или LibreOffice Calc).
В задании дана таблица с исходными данными. Необходимо:
В отличие от задания 13.2 (текстовый редактор), задание 14 проверяет умение работать с формулами, использовать встроенные функции и строить диаграммы.
За каждый верно выполненный элемент даётся 1 балл. Максимальный балл — 3.
В электронную таблицу внесли информацию о грузоперевозках, совершённых некоторым автопредприятием с 1 по 9 октября. Ниже приведены первые пять строк таблицы.
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| Дата | Пункт отправления | Пункт назначения | Расстояние | Расход бензина | Масса груза |
| 1 октября | Липки | Берёзки | 432 | 63 | 770 |
| 1 октября | Орехово | Дубки | 121 | 17 | 670 |
| 1 октября | Осинки | Вязово | 333 | 47 | 830 |
| 1 октября | Липки | Вязово | 384 | 54 | 730 |
Всего в таблицу внесены данные о 370 перевозках.
В электронную таблицу занесли данные о тестировании учеников по выбранным ими предметам.
| A | B | C | D |
|---|---|---|---|
| округ | фамилия | предмет | балл |
| С | Ученик 1 | физика | 240 |
| В | Ученик 2 | физкультура | 782 |
| Ю | Ученик 3 | биология | 361 |
| СВ | Ученик 4 | обществознание | 377 |
Всего в таблицу внесены данные 1000 учеников.
Ячейка — основной элемент таблицы. Каждая ячейка имеет уникальный адрес, состоящий из буквы столбца и номера строки (например, A1, B5, C10).
Столбцы обозначаются буквами латинского алфавита (A, B, C, …). Строки — числами (1, 2, 3, …).
Диапазон — одна или несколько смежных ячеек. Обозначается через двоеточие: A1:A10 (все ячейки от A1 до A10), B5:D10 (прямоугольная область).
Формула — выражение для вычисления значения. Все формулы в Excel начинаются со знака равенства (=). Примеры:
В русской версии Excel и LibreOffice Calc аргументы функций разделяются точкой с запятой (;), а не запятой (,). Текстовые значения обязательно заключаются в кавычки.
Обычная ссылка на ячейку (A1). При копировании формулы в другую ячейку адрес автоматически изменяется относительно нового положения. Например, если в ячейке B1 записана формула =A1+1 и её скопировать в B2, она станет =A2+1.
Ссылка со знаком $ перед буквой столбца и номером строки ($A$1). При копировании формулы такая ссылка не меняется.
Закрепляется только столбец ($A1) или только строка (A$1).
В ячейке C1 записана формула =A1*$B$1. Если скопировать её в C2, она станет =A2*$B$1. Ссылка на B1 осталась неизменной благодаря знакам $.
Для решения задания 14 потребуются следующие функции. Нажмите на название функции, чтобы перейти к подробному описанию:
Проверяет условие и возвращает одно значение, если оно ИСТИНА, и
другое, если ЛОЖЬ.
Применение: для фильтрации данных по условию
(например, =ЕСЛИ(C2="биология"; 1; 0))
Инвертирует логическое значение: ИСТИНА → ЛОЖЬ, ЛОЖЬ → ИСТИНА.
Применение: для исключения значений из выборки
(например, =ЕСЛИ(НЕ(C2="биология"); 0; D2))
Возвращает ИСТИНА, если все условия истинны.
Применение: для проверки нескольких условий
одновременно (например,
=ЕСЛИ(И(C2="биология"; D2>500); 1; 0))
Возвращает ИСТИНА, если хотя бы одно из условий истинно.
Применение: для проверки нескольких условий —
достаточно выполнения хотя бы одного (например,
=ЕСЛИ(ИЛИ(C2="биология"; C2="химия"); 1; 0))
Подсчитывает количество ячеек, содержащих числа.
Применение: для подсчёта количества записей с
числовыми значениями (например, =СЧЁТ(A2:A1001))
Подсчитывает количество ячеек, удовлетворяющих условию.
Применение: для подсчёта количества записей по
условию (например, =СЧЁТЕСЛИ(A2:A1001; "В"))
Подсчитывает количество ячеек, удовлетворяющих нескольким условиям одновременно.
Применение: для подсчёта записей по нескольким
критериям (например, =СЧЁТЕСЛИМН(C2:C1001; "биология"; D2:D1001; ">500"))
Суммирует числа в диапазоне ячеек.
Применение: для подсчёта суммы значений
(например, =СУММ(E2:E1001))
Суммирует ячейки по заданному условию.
Применение: для суммирования значений,
соответствующих критерию
Суммирует ячейки, удовлетворяющие нескольким условиям одновременно.
Применение: для суммирования значений по
нескольким критериям (например, =СУММЕСЛИМН(D2:D1001; C2:C1001; "биология"; A2:A1001; "В"))
Вычисляет среднее арифметическое чисел в диапазоне.
Применение: для расчёта среднего значения
(например, средний балл)
Вычисляет среднее арифметическое по заданному условию.
Применение: для расчёта среднего только для
определённых записей
Вычисляет среднее арифметическое по нескольким условиям.
Применение: для среднего по нескольким критериям
(например, =СРЗНАЧЕСЛИМН(D2:D1001; C2:C1001; "биология"; A2:A1001; "В"))
Возвращает наименьшее число в диапазоне.
Применение: для нахождения минимального значения
(например, =МИН(D2:D1001))
Возвращает наименьшее число по заданному условию.
Применение: для поиска минимума только для
определённых записей
Возвращает наибольшее число в диапазоне.
Применение: для нахождения максимального значения
(например, =МАКС(D2:D1001))
Возвращает наибольшее число по заданному условию.
Применение: для поиска максимума только для
определённых записей
=ЕСЛИ(условие; 1; 0).
=СУММ(диапазон)=СРЗНАЧ(диапазон) или
=СУММ(диапазон)/СЧЁТ(диапазон)
=СЧЁТЕСЛИ(диапазон; критерий)
ЕСЛИ для фильтрации данных
СУММ и
СЧЁТЕСЛИ для получения ответов
СЧЁТЕСЛИ
=СУММЕСЛИ(диапазон_критерия; критерий; диапазон_суммирования)
=СЧЁТЕСЛИ(диапазон_критерия; критерий)
=СРЗНАЧЕСЛИ(диапазон_критерия; критерий; диапазон_усреднения)
На экзамене используйте тот подход, который вам понятнее. Если вы уверенно работаете с функциями — используйте подход 1 или 3. Если предпочитаете наглядность — используйте сортировку. Главное — чтобы ответы были правильными.
| Ячейка | Формула | Назначение |
|---|---|---|
| E2 | =ЕСЛИ(И(C2="биология"; D2>500); 1; 0) |
Отметка: 1 если биология и балл >500 |
| F2 | =ЕСЛИ(C2="биология"; D2; 0) |
Балл, если биология, иначе 0 |
Скопируйте эти формулы на весь диапазон E3:F1001.
| Ячейка | Формула | Результат |
|---|---|---|
| H2 | =СУММ(E2:E1001) |
Количество учеников с биологией и баллом >500 |
| Ячейка | Формула | Назначение |
|---|---|---|
| I2 | =СЧЁТЕСЛИ(F2:F1001; ">0") |
Количество учеников по биологии |
| I3 | =СУММ(F2:F1001) |
Сумма баллов учеников по биологии |
| H3 | =I3/I2 |
Средний балл по биологии |
| Ячейка | Значение / Формула |
|---|---|
| K2 | "В" (название округа) |
| L2 | =СЧЁТЕСЛИ(A2:A1001; K2) |
| K3 | "Зел" (название округа) |
| L3 | =СЧЁТЕСЛИ(A2:A1001; K3) |
| K4 | "З" (название округа) |
| L4 | =СЧЁТЕСЛИ(A2:A1001; K4) |
По значениям диапазона K2:L4 постройте круговую диаграмму.
При правильном выполнении задания с эталонными данными должны получиться следующие ответы:
На выполнение задания 14 у вас есть примерно 20–25 минут. Правильное распределение времени поможет уложиться в срок и избежать ошибок.
Перед сохранением файла последовательно проверьте каждый пункт:
Задание оценивается по трём элементам. Каждый верно выполненный элемент даёт 1 балл. Максимальный балл — 3.
| Оценка | Описание |
|---|---|
| 3 балла |
Верно выполнены все три оцениваемых элемента:
|
| 2 балла | Верно выполнены два из трёх оцениваемых элементов |
| 1 балл | Верно выполнен один из трёх оцениваемых элементов |
| 0 баллов | Ни один из элементов не выполнен верно |
Оценивается не ход выполнения задания, а правильность полученных числовых ответов и построения диаграммы. Если ответ записан в другую ячейку (не H2 или H3), но он правильный — балл всё равно засчитывается. Эксперт должен просмотреть решение и найти правильные ответы.
Если ответ на второй вопрос получен с избыточной точностью (например, 468,8642 вместо 468,86) — это не является ошибкой, так как задание требует «не менее» определённого количества знаков. Но если точность меньше требуемой — это ошибка.
Эксперт должен учитывать разницу в настройках формата ячеек и при близком (но не точно совпадающем) ответе проверять, не связана ли разница с форматом отображения.
Эксперт проверяет задание 14 по трём блокам:
Проверяется, что в ячейке H2 (или другой указанной) записано верное число.
Пример верного ответа: 91
Проверяется, что в ячейке H3 (или другой) записано верное число с точностью не менее указанной.
Пример верного ответа: 468,86
Проверяется:
Диаграмма считается построенной верно, если:
В ячейке H2 — правильный ответ 91. В ячейке H3 — правильный ответ 468,86 (или с большей точностью, например 468,8642). Построена круговая диаграмма с правильными данными, есть легенда, указаны числовые значения. Формулы во вспомогательных столбцах записаны верно.
Оценка: 3 балла.
Числовые ответы не совпадают с эталонными (ошибка в формулах). Однако диаграмма построена верно, данные для неё рассчитаны правильно, есть легенда и числовые значения.
Оценка: 1 балл (только за диаграмму).
Неверные ответы на первые два задания. На диаграмме отсутствуют подписи данных (числовые значения).
Оценка: 0 баллов.
Ошибка: использование запятой вместо точки с запятой для разделения аргументов функции.
Решение: в русской версии Excel и LibreOffice Calc
аргументы разделяются точкой с запятой (;), а не
запятой (,).
Ошибка: несоответствие регистра или написание текста в критерии (например, "Биология" вместо "биология").
Решение: внимательно сверяйте написание текстовых значений в условии. В Excel текстовые значения чувствительны к регистру в функциях ЕСЛИ, но в СЧЁТЕСЛИ регистр обычно не учитывается.
Ошибка: неверно указан диапазон ячеек (например, A2:A1000 вместо A2:A1001).
Решение: проверьте, сколько строк данных в таблице. Если данных 1000 строк (строки 2–1001), то диапазон должен быть A2:A1001.
Ошибка: ответ записан с недостаточным количеством знаков после запятой.
Решение: настройте формат ячейки (Формат → Ячейки → Число → Числовой) с нужным количеством десятичных знаков. Если в задании сказано «не менее одного знака» — покажите минимум 1 знак.
Ошибка: на круговой диаграмме нет легенды или не указаны числовые значения данных.
Решение: после создания диаграммы добавьте легенду (через контекстное меню или Работа с диаграммами) и подписи данных, выбрав отображение «Значения».
Ошибка: диаграмма размещена не вблизи указанной ячейки.
Решение: перетащите диаграмму так, чтобы её левый верхний угол находился возле указанной ячейки (обычно G6).
Ошибка: вы записали формулу в E2, но не скопировали её в E3:E1001.
Решение: используйте маркер автозаполнения (двойной щелчок по правому нижнему углу ячейки), чтобы быстро скопировать формулу на все строки.
Ошибка: на диаграмме указаны проценты вместо числовых значений.
Решение: выбирайте подписи данных «Значения», а не «Доли» или «Проценты». Исключение: если числовые значения указаны в легенде, это тоже считается выполнением требования.
Прежде чем писать формулы, изучите структуру таблицы: сколько строк, какие столбцы, какие данные в них содержатся. Определите, сколько строк нужно обработать. Это поможет избежать ошибок с диапазонами.
После записи формулы в первой ячейке (например, E2) дважды щёлкните по маркеру автозаполнения (правый нижний угол ячейки) — формула автоматически скопируется на все строки таблицы. Это быстрее и точнее, чем перетаскивание.
После получения ответов убедитесь, что в ячейках H2 и H3 установлен числовой формат с нужным количеством десятичных знаков. Если формат «Общий», число может отображаться неполностью.
Не пытайтесь построить диаграмму по исходным данным. Сначала подготовьте отдельную таблицу с категориями и числовыми значениями, полученными с помощью СЧЁТЕСЛИ. Только после этого выделите подготовленные данные и вставьте круговую диаграмму.
После вставки диаграммы обязательно добавьте легенду и подписи данных, выбрав отображение «Значения». Проверьте, что на диаграмме отображаются именно числа, а не проценты. Разместите диаграмму вблизи ячейки G6.
Да, это допускается. Если правильный ответ записан в другой ячейке (не в той, которая указана в задании), эксперт должен просмотреть решение и, найдя правильный ответ, засчитать балл. Однако для вашего же удобства лучше записывать ответы в указанные ячейки.
Это не ошибка. Если в задании сказано «не менее двух знаков после запятой», то ответ 468,8642 подходит, так как точность больше требуемой. Но если точность меньше (например, 469) — это ошибка.
Сначала подготовьте данные: в одном столбце запишите названия категорий, в соседнем — числовые значения для каждой категории (полученные с помощью СЧЁТЕСЛИ). Выделите оба столбца → Вставка → Диаграмма → Круговая. Затем добавьте легенду и подписи данных, выбрав отображение «Значения».
Нет, это не обязательно. Можно использовать функции СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ напрямую, без вспомогательных столбцов. Также можно отсортировать данные и подсчитать вручную. Выбор способа решения не влияет на оценку — важны только правильные ответы и диаграмма.
Задание требует и легенду, и числовые значения данных. Если чего-то из этого нет, элемент «диаграмма» может считаться невыполненным. Всегда добавляйте оба элемента.
К сожалению, все данные будут потеряны. Придётся начать заново. Поэтому регулярно сохраняйте файл (Ctrl+S) в процессе работы.