Что нужно сделать в задании 14 ОГЭ
В задании 14 ОГЭ по информатике нужно обработать данные электронной таблицы: ответить на два числовых вопроса и построить диаграмму по указанным категориям. Разберём четыре примера из шпаргалки в LibreOffice Calc: баллы учеников, города, опрос и погоду.
В каждом примере сначала определим подходящие строки, затем выберем действие: количество, среднее, максимум или процент. Для диаграммы отдельно посчитаем три значения.
Шпаргалка с четырьмя подробными разборами
Формулы, два способа решения, условия и диаграммы доступны в исходном PDF.
Скачать шпаргалку №14 · PDFКонтрольные числа на странице относятся к полным таблицам, разобранным в шпаргалке. Для своего варианта вычисляй ответы заново.Ячейки, диапазоны и вспомогательные столбцы
C2 — ячейка в столбце C и строке 2. Запись D2:D1001 обозначает диапазон из 1000 ячеек. Если первая строка содержит заголовки, в расчёт чисел её не включаем.
Помечаем подходящие строки единицами
Когда нужно посчитать строки по условию, в свободном столбце ставим 1, если строка подходит, и 0 — если нет. Потом складываем единицы.
=ЕСЛИ(И(C2="информатика";D2>600);1;0)И означает, что оба условия должны выполниться одновременно. ИЛИ подходит, если достаточно хотя бы одного из вариантов. Например, весенний месяц — март или апрель или май.
Оставляем числа для среднего
=ЕСЛИ(C2="информатика";D2;"")Если предмет подходит, берём балл. Иначе возвращаем пустую строку "". Функция среднего не учитывает такой текстовый результат. Ноль — другое значение: он войдёт в расчёт и изменит среднее.
| Действие | Функция | Пример |
|---|---|---|
| Количество по пометкам 1/0 | СУММ | =СУММ(E2:E1001) |
| Среднее выбранных чисел | СРЗНАЧ | =СРЗНАЧ(F2:F1001) |
| Максимум | МАКС | =МАКС(D2:D1001) |
| Процент | Часть / нужная группа × 100 | =13/37*100 |
Как копировать формулы вниз
Введи формулу во вторую строку и протяни маркер заполнения до последней строки данных. Ссылка C2 при копировании вниз станет C3, затем C4. Для постоянного диапазона используй доллары: $B$2:$B$1001.
В формуле =ЕСЛИ($A2=I$1;1;0) буква A закреплена, а номер строки меняется. У заголовка I$1, наоборот, закреплена строка 1, а буква столбца меняется при копировании вправо.
Примеры записаны для русских имён функций и разделителя ;, как в шпаргалке. Если в твоём редакторе включены английские названия, соответствия такие: IF, AND, OR, SUM, AVERAGE, MAX, SUBTOTAL.
>600, а не >=600. Балл ровно 600 в первом случае не подходит.Как считать после фильтра
Включи автофильтр для всей таблицы с заголовками и оставь нужные строки. Не сортируй один столбец отдельно от остальных: значения должны оставаться связанными со своей строкой.
Обычные СУММ и СРЗНАЧ продолжают учитывать скрытые фильтром строки. Чтобы считать по видимым строкам, используй ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
| Код | Что считаем | Пример |
|---|---|---|
| 1 | Среднее видимых чисел | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;D2:D1001) |
| 2 | Количество видимых числовых ячеек | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2;D2:D1001) |
| 3 | Количество непустых видимых ячеек | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;A2:A219) |
| 4 | Максимум среди видимых чисел | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(4;B2:B1001) |
| 9 | Сумма видимых чисел | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C152) |
В разборе временная ячейка для таких расчётов — P2. В своём файле сначала убедись, что выбранное место свободно. При способе только формулами ответы обычно можно оставить формулами: их диапазоны и условия не зависят от видимости строк.
Ученики, предметы и баллы
Файл в шпаргалке: 1.xls. Данные находятся в строках 2–1001, первая строка — заголовки.
A — округ; B — фамилия; C — предмет; D — балл.
Способ 1. Вспомогательные столбцы и формулы
| Ячейка | Формула | Что получаем |
|---|---|---|
| E2 | =ЕСЛИ(И(C2="информатика";D2>600);1;0) | Пометка 1 для нужного предмета и балла строго больше 600; иначе 0. |
| F2 | =ЕСЛИ(C2="информатика";D2;"") | Оставляем баллы всех участников по информатике. Для других предметов — пустая строка. |
| H2 | =СУММ(E2:E1001) | Сумма единиц — число участников по первому условию. |
| H3 | =СРЗНАЧ(F2:F1001) | Среднее по всем числам, оставленным в F. |
Способ 2. Фильтры и подсчёт видимых строк
Перед каждым новым вопросом сбрасывай предыдущие ограничения и ставь только нужные. Формулу ниже вводи во временную свободную ячейку, например P2. Полученный ответ зафиксируй в нужной ячейке как значение.
| Что считаем | Фильтр | Расчёт во временной ячейке | Куда записать |
|---|---|---|---|
| Количество | C = информатика; D > 600 | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2;D2:D1001) | H2 |
| Средний балл | Убрать ограничение по D; оставить C = информатика | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;D2:D1001) | H3 |
Данные для круговой диаграммы
Считай участников по округам во всей исходной таблице. Ограничение «информатика» относится к двум числовым вопросам, а не к диаграмме.
| Категория | Количество по шпаргалке |
|---|---|
| В | 132 |
| Зел | 29 |
| З | 108 |
Для подсчёта формулами впиши категории в I1:K1. В I2 введи:
=ЕСЛИ($A2=I$1;1;0)Протяни её вправо до K2 и вниз до строки 1001. Затем создай итоговую таблицу N1:O4: в первой строке заголовки, ниже три категории. В O2 запиши =СУММ(I2:I1001); для O3 и O4 сложи соседние вспомогательные столбцы.
Города, население и страны
Файл в шпаргалке: 20.xlsx. Данные находятся в строках 2–1001, первая строка — заголовки.
A — город; B — население в тысячах человек; C — страна.
Способ 1. Вспомогательные столбцы и формулы
| Ячейка | Формула | Что получаем |
|---|---|---|
| D2 | =ЕСЛИ(C2="Египет";B2;"") | Оставляем население только городов Египта. |
| E2 | =ЕСЛИ(И(C2="Египет";B2>СРЗНАЧ($B$2:$B$1001));1;0) | Помечаем города Египта, население которых выше общего среднего. |
| F2 | =МАКС(D2:D1001) | Максимум населения среди египетских городов. |
| F3 | =СУММ(E2:E1001) | Количество городов по второму условию. |
Способ 2. Фильтры и подсчёт видимых строк
Перед каждым новым вопросом сбрасывай предыдущие ограничения и ставь только нужные. Формулу ниже вводи во временную свободную ячейку, например P2. Полученный ответ зафиксируй в нужной ячейке как значение.
| Что считаем | Фильтр | Расчёт во временной ячейке | Куда записать |
|---|---|---|---|
| Максимум | C = Египет; других ограничений нет | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(4;B2:B1001) | F2 |
| Количество выше среднего | C = Египет и E = 1; столбец E рассчитан формулой выше | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2;B2:B1001) | F3 |
Во втором вопросе столбец E уже сравнивает население с точным средним по всей таблице. Фильтр E = 1 позволяет не переносить в числовое условие округлённое среднее.
Данные для круговой диаграммы
На диаграмме показано количество городов каждой страны. Численность населения этих городов суммировать не нужно.
| Категория | Количество по шпаргалке |
|---|---|
| Швейцария | 10 |
| Канада | 36 |
| Германия | 20 |
Для подсчёта формулами впиши категории в I1:K1. В I2 введи:
=ЕСЛИ($C2=I$1;1;0)Протяни её вправо до K2 и вниз до строки 1001. Затем создай итоговую таблицу N1:O4: в первой строке заголовки, ниже три категории. В O2 запиши =СУММ(I2:I1001); для O3 и O4 сложи соседние вспомогательные столбцы.
Социологический опрос учеников
Файл в шпаргалке: 8.xls. Данные находятся в строках 2–219, первая строка — заголовки.
A — фамилия; B — имя; C — класс; D — любимый предмет; E — оценка.
Способ 1. Вспомогательные столбцы и формулы
| Ячейка | Формула | Что получаем |
|---|---|---|
| F2 | =ЕСЛИ(D2="алгебра";1;0) | Пометка для любимого предмета «алгебра». |
| G2 | =ЕСЛИ(И(C2=7;E2=3);1;0) | Числитель: семиклассники с оценкой 3. |
| I2 | =ЕСЛИ(C2=7;1;0) | Знаменатель: все ученики 7 класса. |
| H2 | =СУММ(F2:F219) | Количество любителей алгебры. |
| H3 | =СУММ(G2:G219)/СУММ(I2:I219)*100 | Доля нужных семиклассников в процентах. |
Формула H3 уже умножает долю на 100. Поэтому для результата выбери обычный числовой формат с двумя знаками после запятой. Если вместо этого использовать процентный формат, умножение на 100 нужно убрать.
Способ 2. Фильтры и подсчёт видимых строк
Перед каждым новым вопросом сбрасывай предыдущие ограничения и ставь только нужные. Формулу ниже вводи во временную свободную ячейку, например P2. Полученный ответ зафиксируй в нужной ячейке как значение.
| Что считаем | Фильтр | Расчёт во временной ячейке | Куда записать |
|---|---|---|---|
| Алгебра | D = алгебра | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;A2:A219) | H2: 25 |
| Числитель процента | C = 7; E = 3 | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;A2:A219) | Зафиксировать 13 |
| Знаменатель процента | C = 7; ограничение E = 3 снять | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;A2:A219) | Зафиксировать 37 |
После двух отдельных подсчётов вычисли =13/37*100 и запиши результат в H3. На другом наборе данных используй полученные из него числитель и знаменатель.
Данные для круговой диаграммы
Для диаграммы считаем выбор каждого из трёх предметов среди всех участников опроса. Фильтры по 7 классу и оценке перед этим снимаем.
| Категория | Количество по шпаргалке |
|---|---|
| русский язык | 12 |
| литература | 19 |
| физкультура | 31 |
Для подсчёта формулами впиши категории в J1:L1. В J2 введи:
=ЕСЛИ($D2=J$1;1;0)Протяни её вправо до L2 и вниз до строки 219. Затем создай итоговую таблицу N1:O4: в первой строке заголовки, ниже три категории. В O2 запиши =СУММ(J2:J219); для O3 и O4 сложи соседние вспомогательные столбцы.
Наблюдения за погодой
Файл в шпаргалке: 12.xls. Данные находятся в строках 2–366, первая строка — заголовки.
A — дата; B — температура; C — осадки; D — давление; E — направление ветра; F — скорость ветра.
Способ 1. Вспомогательные столбцы и формулы
| Ячейка | Формула | Что получаем |
|---|---|---|
| H2 | =СРЗНАЧ(C61:C152) | Средние осадки за март, апрель и май в данном файле. |
| G2 | =ЕСЛИ(E2="ЮЗ";F2;"") | Оставляем скорость только в дни с направлением ЮЗ. |
| H3 | =СРЗНАЧ(G2:G366) | Средняя скорость по выбранным дням. |
Если даты хранятся как настоящие даты, отбирай календарный диапазон с 1 марта по 31 мая нужного года. Если они записаны текстом, отбирай названия трёх месяцев. Не используй поиск слова «марта» для ячеек, где дата только отображается таким образом, но хранится числом.
Способ 2. Фильтры и подсчёт видимых строк
Перед каждым новым вопросом сбрасывай предыдущие ограничения и ставь только нужные. Формулу ниже вводи во временную свободную ячейку, например P2. Полученный ответ зафиксируй в нужной ячейке как значение.
| Что считаем | Фильтр | Расчёт во временной ячейке | Куда записать |
|---|---|---|---|
| Осадки весной | Март ИЛИ апрель ИЛИ май по столбцу даты | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;C2:C366) | H2 |
| Скорость при ЮЗ | Снять ограничение по месяцу; E = ЮЗ | =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;F2:F366) | H3 |
Данные для круговой диаграммы
ЮЗ — условие для среднего, а на диаграмме нужны другие три направления: СЗ, Ю и В. Для неё используем данные за весь год.
| Категория | Количество по шпаргалке |
|---|---|
| СЗ | 37 |
| Ю | 62 |
| В | 48 |
Для подсчёта формулами впиши категории в I1:K1. В I2 введи:
=ЕСЛИ($E2=I$1;1;0)Протяни её вправо до K2 и вниз до строки 366. Затем создай итоговую таблицу N1:O4: в первой строке заголовки, ниже три категории. В O2 запиши =СУММ(I2:I366); для O3 и O4 сложи соседние вспомогательные столбцы.
Как построить круговую диаграмму в Calc
Сначала создай маленькую таблицу из трёх категорий и их количеств. Мы используем N1:O4: одна строка заголовков и три строки данных. Эта область находится правее вспомогательных столбцов всех четырёх примеров.
- В N1 напиши «Категория», в O1 — «Количество».
- Заполни N2:N4 нужными названиями, а O2:O4 — подсчитанными количествами.
- Если использовал фильтры, зафиксируй каждое из трёх чисел как значение до перехода к следующей категории.
- Выдели N1:O4 целиком и выбери «Вставка → Диаграмма → Круговая».
- Проверь, что источник подписей — названия категорий, а данные — их количества.
- Включи легенду и числовые значения, если их требует условие.
- В приведённых условиях левый верхний угол диаграммы нужно разместить вблизи G6. Диаграмма — отдельный объект: её расположение не обязано совпадать с адресом таблицы-источника.
Если итоговая таблица состоит только из трёх строк данных без заголовка, выделяй эти три строки. Если добавил заголовок, включи и его: это уже четыре строки. Всегда проверяй подписи в мастере диаграмм.
На диаграмме должны быть ровно нужные три категории. Проценты вычисляются относительно суммы этих трёх чисел. Не включай четвёртым сектором строку «Итого».
Почему для количества и среднего нужны разные условия
Это отдельный учебный набор из шести строк. Его ответы отличаются от полных файлов выше. Сначала выбери информатику и порог 600: для количества учитываем балл строго выше порога, а для среднего — все строки выбранного предмета.
Единица в последнем столбце означает, что строка подходит для первого вопроса.
| Округ | Участник | Предмет | Балл | Пометка 1/0 |
|---|---|---|---|---|
| В | Ученик 1 | информатика | 700 | 1 |
| Зел | Ученик 2 | информатика | 600 | 0 |
| З | Ученик 3 | физика | 900 | 0 |
| В | Ученик 4 | информатика | 800 | 1 |
| Зел | Ученик 5 | математика | 650 | 0 |
| З | Ученик 6 | информатика | 500 | 0 |
Количество по предмету и порогу: 2
Средний балл по предмету без порога: 650,00
Попробуй порог 700. Количество изменится, а среднее по информатике останется прежним: второй вопрос не содержит ограничения по баллу.
Шесть упражнений с проверкой
Первые два вопроса относятся к маленькой учебной таблице выше. Используй исходные условия: информатика, балл строго больше 600. Остальные проверяют отдельные шаги разбора.
Решено: 0 из 6
1. Количество по условию
Сколько участников по информатике в учебной таблице набрали больше 600 баллов?
2. Среднее без лишнего ограничения
Найди средний балл всех участников по информатике в учебной таблице. Не применяй порог 600.
3. Процент семиклассников
Из 37 учеников 7 класса оценку 3 имеют 13. Какой это процент? Округли до двух знаков после запятой и запиши число.
4. Правильный знаменатель
В опросе 218 участников, из них 37 семиклассников. Какое число должно стоять в знаменателе процента семиклассников с оценкой 3?
5. Среднее после фильтра
Какой код функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ выбирают для среднего видимых чисел?
6. Максимум по группе
В таблице три города Египта с населением 90, 300 и 150 тыс. человек и город другой страны с населением 900 тыс. Какое население у самого крупного города Египта?
Финальная самопроверка
- Диапазоны включают все данные и не включают заголовки.
- Условия «больше», «не меньше», «И» и «ИЛИ» прочитаны точно.
- Для среднего выбрана нужная группа; вместо ненужных строк не подставлены нули.
- В процентах числитель и знаменатель относятся к одной указанной группе.
- Постоянные диапазоны закреплены, формулы протянуты до последней строки.
- Числа записаны в ячейки ответа, заданные условием, с нужным числом знаков.
- Смена фильтра не портит уже записанные ответы и данные диаграммы.
- На диаграмме нужные три категории, подписи и расположение.
- Файл сохранён под нужным именем, в нужной папке и разрешённом формате.
Для двух знаков после запятой выбери числовой формат 0,00 в настройках отображения. Не округляй промежуточное среднее перед сравнением с ним других чисел.
Видео по заданию №14
После самостоятельного решения сравни ход решения с видеоразбором.
Закрепи работу с электронными таблицами
Повтори теорию и видео в учебнике и материалах, затем переходи к практике своего номера. В кабинете можно продолжить подготовку к ОГЭ.
Создать аккаунтЕсли результат не совпал
Почему среднее получилось слишком маленьким?
Проверь, не заменил ли ты неподходящие строки нулями. Числовой ноль учитывается в среднем. В примерах для исключения строки используется пустая строка "".
Почему обычная сумма не меняется после фильтра?
Обычные функции продолжают работать со всеми ячейками диапазона. Для видимых после фильтра строк используй ПРОМЕЖУТОЧНЫЕ.ИТОГИ с нужным кодом.
Почему после диаграммы изменился ответ?
Проверь, не перезаписал ли ты вспомогательные формулы итоговой таблицей. Также промежуточные итоги пересчитываются при смене фильтра: фиксируй найденные результаты как значения.
Почему получилось 3514 вместо 35,14 процента?
Если формула уже умножает долю на 100, используй обычный числовой формат. При процентном формате умножение на 100 в формуле не требуется.
Всегда ли ответы такие, как в шпаргалке?
Нет. Контрольные числа относятся к конкретным полным исходным таблицам. В другом варианте проверяй его данные, адреса ячеек и условия.
Можно ли использовать другой табличный редактор?
Логика отбора, среднего, процента и диаграммы сохраняется. Но названия функций, разделители и команды интерфейса проверь в программе, которой пользуешься. На странице разобрана русская запись из LibreOffice Calc.