📊 Задание 14 ОГЭ — Электронные таблицы
📑 Содержание
💡 Что такое задание 14?
Задание 14 — практическое задание ОГЭ по информатике, в котором требуется выполнить обработку данных в электронных таблицах (Microsoft Excel или LibreOffice Calc).
В задании дана таблица с исходными данными. Необходимо:
- Выполнить вычисления с использованием формул и функций
- Построить круговую диаграмму
- Сохранить полученную таблицу
В отличие от задания 13.2 (текстовый редактор), задание 14 проверяет умение работать с формулами, использовать встроенные функции и строить диаграммы.
За каждый верно выполненный элемент даётся 1 балл. Максимальный балл — 3.
📋 Примеры заданий
Вариант 1 (Грузоперевозки)
В электронную таблицу внесли информацию о грузоперевозках, совершённых некоторым автопредприятием с 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 перевозках.
Задания к таблице
- Какова суммарная протяжённость всех перевозок, произведённых с 7 по 9 октября? Ответ запишите в ячейку H2 таблицы.
- Какова средняя масса груза при автоперевозках из города Осинки? Ответ запишите в ячейку H3 таблицы с точностью не менее одного знака после запятой.
- Постройте круговую диаграмму, отображающую соотношение количества перевозок 1 октября, 2 октября и 3 октября. Левый верхний угол диаграммы разместите вблизи ячейки G6. В поле диаграммы должны присутствовать легенда и числовые значения данных.
Вариант 2 (Тестирование учеников)
В электронную таблицу занесли данные о тестировании учеников по выбранным ими предметам.
| A | B | C | D |
|---|---|---|---|
| округ | фамилия | предмет | балл |
| С | Ученик 1 | физика | 240 |
| В | Ученик 2 | физкультура | 782 |
| Ю | Ученик 3 | биология | 361 |
| СВ | Ученик 4 | обществознание | 377 |
Всего в таблицу внесены данные 1000 учеников.
Задания к таблице
- Определите, сколько учеников, которые проходили тестирование по биологии, набрали более 500 баллов. Ответ запишите в ячейку H2 таблицы.
- Вычислите средний тестовый балл у учеников, которые проходили тестирование по биологии. Ответ запишите в ячейку H3 таблицы с точностью не менее двух знаков после запятой.
- Постройте круговую диаграмму, отображающую соотношение числа участников из округов с кодами «В», «Зел» и «З». Левый верхний угол диаграммы разместите вблизи ячейки G6. В поле диаграммы должны присутствовать легенда и числовые значения данных.
📊 Основы работы с электронными таблицами
🔢 Ячейка, строка, столбец
Ячейка — основной элемент таблицы. Каждая ячейка имеет уникальный адрес, состоящий из буквы столбца и номера строки (например, A1, B5, C10).
Столбцы обозначаются буквами латинского алфавита (A, B, C, …). Строки — числами (1, 2, 3, …).
📐 Диапазон ячеек
Диапазон — одна или несколько смежных ячеек. Обозначается через двоеточие: A1:A10 (все ячейки от A1 до A10), B5:D10 (прямоугольная область).
📝 Формулы
Формула — выражение для вычисления значения. Все формулы в Excel начинаются со знака равенства (=). Примеры:
- =A1+B1 — сложение двух ячеек
- =СУММ(A1:A10) — сумма диапазона
- =СРЗНАЧ(B2:B100) — среднее значение
⚠️ Важно!
В русской версии Excel и LibreOffice Calc аргументы функций разделяются точкой с запятой (;), а не запятой (,). Текстовые значения обязательно заключаются в кавычки.
📌 Адресация ячеек
Относительная адресация
Обычная ссылка на ячейку (A1). При копировании формулы в другую ячейку адрес автоматически изменяется относительно нового положения. Например, если в ячейке B1 записана формула =A1+1 и её скопировать в B2, она станет =A2+1.
Абсолютная адресация
Ссылка со знаком $ перед буквой столбца и номером строки ($A$1). При копировании формулы такая ссылка не меняется.
Смешанная адресация
Закрепляется только столбец ($A1) или только строка (A$1).
Пример использования абсолютной ссылки
В ячейке C1 записана формула =A1*$B$1. Если скопировать её в C2, она станет =A2*$B$1. Ссылка на B1 осталась неизменной благодаря знакам $.
🎯 Функции Excel для задания 14
Для решения задания 14 потребуются следующие функции. Нажмите на название функции, чтобы перейти к подробному описанию:
📋 ЕСЛИ (IF)
Проверяет условие и возвращает одно значение, если оно ИСТИНА, и
другое, если ЛОЖЬ.
Применение: для фильтрации данных по условию
(например, =ЕСЛИ(C2="биология"; 1; 0))
🚫 НЕ (NOT)
Инвертирует логическое значение: ИСТИНА → ЛОЖЬ, ЛОЖЬ → ИСТИНА.
Применение: для исключения значений из выборки
(например, =ЕСЛИ(НЕ(C2="биология"); 0; D2))
🔗 И (AND)
Возвращает ИСТИНА, если все условия истинны.
Применение: для проверки нескольких условий
одновременно (например,
=ЕСЛИ(И(C2="биология"; D2>500); 1; 0))
🔀 ИЛИ (OR)
Возвращает ИСТИНА, если хотя бы одно из условий истинно.
Применение: для проверки нескольких условий —
достаточно выполнения хотя бы одного (например,
=ЕСЛИ(ИЛИ(C2="биология"; C2="химия"); 1; 0))
🔢 СЧЁТ (COUNT)
Подсчитывает количество ячеек, содержащих числа.
Применение: для подсчёта количества записей с
числовыми значениями (например, =СЧЁТ(A2:A1001))
🔍 СЧЁТЕСЛИ (COUNTIF)
Подсчитывает количество ячеек, удовлетворяющих условию.
Применение: для подсчёта количества записей по
условию (например, =СЧЁТЕСЛИ(A2:A1001; "В"))
🔍🔍 СЧЁТЕСЛИМН (COUNTIFS)
Подсчитывает количество ячеек, удовлетворяющих нескольким условиям одновременно.
Применение: для подсчёта записей по нескольким
критериям (например, =СЧЁТЕСЛИМН(C2:C1001; "биология"; D2:D1001; ">500"))
➕ СУММ (SUM)
Суммирует числа в диапазоне ячеек.
Применение: для подсчёта суммы значений
(например, =СУММ(E2:E1001))
➕🔍 СУММЕСЛИ (SUMIF)
Суммирует ячейки по заданному условию.
Применение: для суммирования значений,
соответствующих критерию
➕🔍🔍 СУММЕСЛИМН (SUMIFS)
Суммирует ячейки, удовлетворяющие нескольким условиям одновременно.
Применение: для суммирования значений по
нескольким критериям (например, =СУММЕСЛИМН(D2:D1001; C2:C1001; "биология"; A2:A1001; "В"))
📊 СРЗНАЧ (AVERAGE)
Вычисляет среднее арифметическое чисел в диапазоне.
Применение: для расчёта среднего значения
(например, средний балл)
📊🔍 СРЗНАЧЕСЛИ (AVERAGEIF)
Вычисляет среднее арифметическое по заданному условию.
Применение: для расчёта среднего только для
определённых записей
📊🔍🔍 СРЗНАЧЕСЛИМН (AVERAGEIFS)
Вычисляет среднее арифметическое по нескольким условиям.
Применение: для среднего по нескольким критериям
(например, =СРЗНАЧЕСЛИМН(D2:D1001; C2:C1001; "биология"; A2:A1001; "В"))
⬇️ МИН (MIN)
Возвращает наименьшее число в диапазоне.
Применение: для нахождения минимального значения
(например, =МИН(D2:D1001))
⬇️🔍 МИНЕСЛИ (MINIFS)
Возвращает наименьшее число по заданному условию.
Применение: для поиска минимума только для
определённых записей
⬆️ МАКС (MAX)
Возвращает наибольшее число в диапазоне.
Применение: для нахождения максимального значения
(например, =МАКС(D2:D1001))
⬆️🔍 МАКСЕСЛИ (MAXIFS)
Возвращает наибольшее число по заданному условию.
Применение: для поиска максимума только для
определённых записей
🛠️ Пошаговый алгоритм решения задания 14
=ЕСЛИ(условие; 1; 0).
- Для суммы:
=СУММ(диапазон) -
Для среднего:
=СРЗНАЧ(диапазон)или=СУММ(диапазон)/СЧЁТ(диапазон) -
Для подсчёта по условию:
=СЧЁТЕСЛИ(диапазон; критерий)
- Выделите данные, которые нужно отобразить на диаграмме (категории и их числовые значения)
- Вставка → Диаграмма → Круговая
- Добавьте легенду и подписи данных с числовыми значениями
- Разместите диаграмму вблизи указанной ячейки (обычно G6)
🧮 Три подхода к решению задания 14
Подход 1: Со вспомогательными столбцами (самый популярный)
-
Создайте столбцы E и F с формулами
ЕСЛИдля фильтрации данных - Скопируйте формулы на все строки таблицы
-
Используйте
СУММиСЧЁТЕСЛИдля получения ответов -
Постройте диаграмму по данным, подготовленным с помощью
СЧЁТЕСЛИ
Подход 2: Сортировка данных
- Отсортируйте данные так, чтобы нужные строки стояли подряд
- Подсчитайте количество, используя нумерацию строк
- Подходит, если нужно просто подсчитать количество записей
Подход 3: Использование СУММЕСЛИ и СРЗНАЧЕСЛИ
-
Используйте
=СУММЕСЛИ(диапазон_критерия; критерий; диапазон_суммирования) -
Используйте
=СЧЁТЕСЛИ(диапазон_критерия; критерий) - Разделите сумму на количество для получения среднего
-
Или используйте
=СРЗНАЧЕСЛИ(диапазон_критерия; критерий; диапазон_усреднения)
💡 Какой подход выбрать?
На экзамене используйте тот подход, который вам понятнее. Если вы уверенно работаете с функциями — используйте подход 1 или 3. Если предпочитаете наглядность — используйте сортировку. Главное — чтобы ответы были правильными.
🎯 Ключевые функции и их применение
Если оба условия верны — возвращает 1, иначе — 0.
Затем СУММ по столбцу даст количество учеников, сдавших биологию на >500 баллов.
которые содержат значение "В".
Используется для подсчёта количества записей, соответствующих критерию.
делится на количество ненулевых записей (т.е. количество учеников по биологии).
Результат — средний балл по биологии.
1. Записать названия категорий (например, названия округов)
2. Вычислить количество записей для каждой категории с помощью СЧЁТЕСЛИ
3. Выделить подготовленные данные → Вставить круговую диаграмму
4. Добавить легенду и подписи данных (числовые значения)
✅ Схема решения для варианта «Тестирование учеников»
Подготовительная часть
| Ячейка | Формула | Назначение |
|---|---|---|
| E2 | =ЕСЛИ(И(C2="биология"; D2>500); 1; 0) |
Отметка: 1 если биология и балл >500 |
| F2 | =ЕСЛИ(C2="биология"; D2; 0) |
Балл, если биология, иначе 0 |
Скопируйте эти формулы на весь диапазон E3:F1001.
Задание 1
| Ячейка | Формула | Результат |
|---|---|---|
| H2 | =СУММ(E2:E1001) |
Количество учеников с биологией и баллом >500 |
Задание 2
| Ячейка | Формула | Назначение |
|---|---|---|
| I2 | =СЧЁТЕСЛИ(F2:F1001; ">0") |
Количество учеников по биологии |
| I3 | =СУММ(F2:F1001) |
Сумма баллов учеников по биологии |
| H3 | =I3/I2 |
Средний балл по биологии |
Задание 3 (Построение диаграммы)
| Ячейка | Значение / Формула |
|---|---|
| K2 | "В" (название округа) |
| L2 | =СЧЁТЕСЛИ(A2:A1001; K2) |
| K3 | "Зел" (название округа) |
| L3 | =СЧЁТЕСЛИ(A2:A1001; K3) |
| K4 | "З" (название округа) |
| L4 | =СЧЁТЕСЛИ(A2:A1001; K4) |
По значениям диапазона K2:L4 постройте круговую диаграмму.
📌 Ожидаемые ответы
При правильном выполнении задания с эталонными данными должны получиться следующие ответы:
- Задание 1: 91
- Задание 2: 468,86
- Задание 3: Секторы диаграммы должны визуально соответствовать соотношению 132 : 29 : 108
⏱️ Как распределить время на экзамене
На выполнение задания 14 у вас есть примерно 20–25 минут. Правильное распределение времени поможет уложиться в срок и избежать ошибок.
✅ Чек-лист проверки перед сдачей
Перед сохранением файла последовательно проверьте каждый пункт:
📏 Критерии оценивания задания 14
Задание оценивается по трём элементам. Каждый верно выполненный элемент даёт 1 балл. Максимальный балл — 3.
| Оценка | Описание |
|---|---|
| 3 балла |
Верно выполнены все три оцениваемых элемента:
|
| 2 балла | Верно выполнены два из трёх оцениваемых элементов |
| 1 балл | Верно выполнен один из трёх оцениваемых элементов |
| 0 баллов | Ни один из элементов не выполнен верно |
📌 Важное замечание
Оценивается не ход выполнения задания, а правильность полученных числовых ответов и построения диаграммы. Если ответ записан в другую ячейку (не H2 или H3), но он правильный — балл всё равно засчитывается. Эксперт должен просмотреть решение и найти правильные ответы.
⚠️ Внимание: точность ответов
Если ответ на второй вопрос получен с избыточной точностью (например, 468,8642 вместо 468,86) — это не является ошибкой, так как задание требует «не менее» определённого количества знаков. Но если точность меньше требуемой — это ошибка.
Эксперт должен учитывать разницу в настройках формата ячеек и при близком (но не точно совпадающем) ответе проверять, не связана ли разница с форматом отображения.
📊 Как эксперт проверяет задание
Эксперт проверяет задание 14 по трём блокам:
1️⃣ Первое числовое значение
Проверяется, что в ячейке H2 (или другой указанной) записано верное число.
Пример верного ответа: 91
2️⃣ Второе числовое значение
Проверяется, что в ячейке H3 (или другой) записано верное число с точностью не менее указанной.
Пример верного ответа: 468,86
3️⃣ Круговая диаграмма
Проверяется:
- Геометрические элементы правильно отображают данные
- Отображаемые данные определены правильно
- Числовые значения данных указаны на диаграмме
- Диаграмма снабжена легендой
💡 Что считается верной диаграммой?
Диаграмма считается построенной верно, если:
- Её геометрические элементы (секторы) правильно отображают представляемые данные
- Отображаемые данные определены правильно (верные числовые значения для каждого сектора)
- Числовые значения данных явно указаны на диаграмме (подписи данных)
- Диаграмма снабжена легендой (обозначение, какой сектор каким данным соответствует)
Разбор примеров оценивания
✅ Пример 1 — 3 балла (эталон)
В ячейке H2 — правильный ответ 91. В ячейке H3 — правильный ответ 468,86 (или с большей точностью, например 468,8642). Построена круговая диаграмма с правильными данными, есть легенда, указаны числовые значения. Формулы во вспомогательных столбцах записаны верно.
Оценка: 3 балла.
⚠️ Пример 2 — 1 балл
Числовые ответы не совпадают с эталонными (ошибка в формулах). Однако диаграмма построена верно, данные для неё рассчитаны правильно, есть легенда и числовые значения.
Оценка: 1 балл (только за диаграмму).
❌ Пример 3 — 0 баллов
Неверные ответы на первые два задания. На диаграмме отсутствуют подписи данных (числовые значения).
Оценка: 0 баллов.
⚠️ Типичные ошибки и как их избежать
❌ Неправильный синтаксис формул
Ошибка: использование запятой вместо точки с запятой для разделения аргументов функции.
Решение: в русской версии Excel и LibreOffice Calc
аргументы разделяются точкой с запятой (;), а не
запятой (,).
❌ Ошибки в текстовых критериях
Ошибка: несоответствие регистра или написание текста в критерии (например, "Биология" вместо "биология").
Решение: внимательно сверяйте написание текстовых значений в условии. В Excel текстовые значения чувствительны к регистру в функциях ЕСЛИ, но в СЧЁТЕСЛИ регистр обычно не учитывается.
❌ Неправильный диапазон данных
Ошибка: неверно указан диапазон ячеек (например, A2:A1000 вместо A2:A1001).
Решение: проверьте, сколько строк данных в таблице. Если данных 1000 строк (строки 2–1001), то диапазон должен быть A2:A1001.
❌ Неправильная точность ответа
Ошибка: ответ записан с недостаточным количеством знаков после запятой.
Решение: настройте формат ячейки (Формат → Ячейки → Число → Числовой) с нужным количеством десятичных знаков. Если в задании сказано «не менее одного знака» — покажите минимум 1 знак.
❌ Отсутствие легенды или подписей на диаграмме
Ошибка: на круговой диаграмме нет легенды или не указаны числовые значения данных.
Решение: после создания диаграммы добавьте легенду (через контекстное меню или Работа с диаграммами) и подписи данных, выбрав отображение «Значения».
❌ Диаграмма не в том месте
Ошибка: диаграмма размещена не вблизи указанной ячейки.
Решение: перетащите диаграмму так, чтобы её левый верхний угол находился возле указанной ячейки (обычно G6).
❌ Формула не скопирована на весь диапазон
Ошибка: вы записали формулу в E2, но не скопировали её в E3:E1001.
Решение: используйте маркер автозаполнения (двойной щелчок по правому нижнему углу ячейки), чтобы быстро скопировать формулу на все строки.
❌ Подписи данных — доли (проценты) вместо чисел
Ошибка: на диаграмме указаны проценты вместо числовых значений.
Решение: выбирайте подписи данных «Значения», а не «Доли» или «Проценты». Исключение: если числовые значения указаны в легенде, это тоже считается выполнением требования.
🔑 5 секретов успешного выполнения задания 14
🔑 Секрет 1: Начните с анализа данных
Прежде чем писать формулы, изучите структуру таблицы: сколько строк, какие столбцы, какие данные в них содержатся. Определите, сколько строк нужно обработать. Это поможет избежать ошибок с диапазонами.
🔑 Секрет 2: Используйте автозаполнение формул
После записи формулы в первой ячейке (например, E2) дважды щёлкните по маркеру автозаполнения (правый нижний угол ячейки) — формула автоматически скопируется на все строки таблицы. Это быстрее и точнее, чем перетаскивание.
🔑 Секрет 3: Проверьте формат ячеек с ответами
После получения ответов убедитесь, что в ячейках H2 и H3 установлен числовой формат с нужным количеством десятичных знаков. Если формат «Общий», число может отображаться неполностью.
🔑 Секрет 4: Сначала подготовьте данные для диаграммы
Не пытайтесь построить диаграмму по исходным данным. Сначала подготовьте отдельную таблицу с категориями и числовыми значениями, полученными с помощью СЧЁТЕСЛИ. Только после этого выделите подготовленные данные и вставьте круговую диаграмму.
🔑 Секрет 5: Не торопитесь при построении диаграммы
После вставки диаграммы обязательно добавьте легенду и подписи данных, выбрав отображение «Значения». Проверьте, что на диаграмме отображаются именно числа, а не проценты. Разместите диаграмму вблизи ячейки G6.
❓ Часто задаваемые вопросы
Можно ли записать ответ не в H2, а в другую ячейку?
Да, это допускается. Если правильный ответ записан в другой ячейке (не в той, которая указана в задании), эксперт должен просмотреть решение и, найдя правильный ответ, засчитать балл. Однако для вашего же удобства лучше записывать ответы в указанные ячейки.
Что делать, если ответ получился с большей точностью, чем требуется?
Это не ошибка. Если в задании сказано «не менее двух знаков после запятой», то ответ 468,8642 подходит, так как точность больше требуемой. Но если точность меньше (например, 469) — это ошибка.
Как правильно построить круговую диаграмму?
Сначала подготовьте данные: в одном столбце запишите названия категорий, в соседнем — числовые значения для каждой категории (полученные с помощью СЧЁТЕСЛИ). Выделите оба столбца → Вставка → Диаграмма → Круговая. Затем добавьте легенду и подписи данных, выбрав отображение «Значения».
Обязательно ли использовать вспомогательные столбцы?
Нет, это не обязательно. Можно использовать функции СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ напрямую, без вспомогательных столбцов. Также можно отсортировать данные и подсчитать вручную. Выбор способа решения не влияет на оценку — важны только правильные ответы и диаграмма.
Что если на диаграмме нет легенды, но есть подписи данных?
Задание требует и легенду, и числовые значения данных. Если чего-то из этого нет, элемент «диаграмма» может считаться невыполненным. Всегда добавляйте оба элемента.
Как быть, если я случайно закрыл файл без сохранения?
К сожалению, все данные будут потеряны. Придётся начать заново. Поэтому регулярно сохраняйте файл (Ctrl+S) в процессе работы.
📋 Краткая памятка на экзамен
📝 Запомните главное
- Формулы начинаются с = (знак равенства)
- Аргументы разделяются ; (точкой с запятой)
- Текст в условиях — в кавычках ("биология")
- Операторы сравнения: =, <>, >, <, >=, <=
- Для нескольких условий используйте И / ИЛИ
- ЕСЛИ(условие; да; нет) — базовая логическая функция
- СУММ(диапазон) — сумма чисел
- СЧЁТЕСЛИ(диапазон; критерий) — подсчёт по условию
- СРЗНАЧ(диапазон) — среднее арифметическое
- СУММЕСЛИ(диапазон_крит; критерий; диапазон_сумм) — сумма по условию
- Диаграмма: круговая, с легендой и числовыми значениями
- Ответы: записывайте в указанные ячейки (H2, H3)
- Точность: не менее указанного количества знаков после запятой
- Сохраняйте файл под именем, указанным организаторами
💡 Полезные советы
- Используйте автозаполнение — двойной щелчок по маркеру ячейки копирует формулу на все строки
- Проверьте формат ячеек с ответами — числовой с нужным количеством знаков
- Сначала подготовьте данные для диаграммы через СЧЁТЕСЛИ
- Не торопитесь — диаграмма должна иметь легенду и числовые значения