ОГЭ Задание 14

В этой статье будет разобрано задание 14.

Рассмотрим задачи последних лет из демоверсий ОГЭ по информатике.

Данное задание относится к высокому уровню сложности.

Время выполнения задания ≈ 30 минут.

Задание проверяет умение проводить обработку большого массива данных с использованием средств электронной таблицы.

Базовые знания по электронной таблице есть по ссылке: обработка информации в электронных таблицах.

Задача 1 (Демоверсия 2023)

В электронную таблицу внесли данные о тестировании учеников по выбранным ими предметам.

В столбце A записан код округа, в котором учится ученик; в столбце B – код фамилии ученика; в столбце C – выбранный учеником предмет; в столбце D – тестовый балл. Всего в электронную таблицу были внесены данные о 1000 учеников.

Выполните задание.
Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На основании данных, содержащихся в этой таблице, выполните задания.

1. Сколько учеников, которые проходили тестирование по информатике, набрали более 600 баллов? Ответ запишите в ячейку H2 таблицы.
2. Каков средний тестовый балл учеников, которые проходили тестирование по информатике? Ответ запишите в ячейку H3 таблицы с точностью не менее двух знаков после запятой.
3. Постройте круговую диаграмму, отображающую соотношение числа участников тестирования из округов с кодами «В», «Зел» и «З». Левый верхний угол диаграммы разместите вблизи ячейки G6. В поле диаграммы должны присутствовать легенда (обозначение соответствия данных определённому сектору диаграммы) и числовые значения данных, по которым построена диаграмма.

Полученную таблицу необходимо сохранить под именем, указанным организаторами экзамена.

Решение:

Открываем файл с заданием в редакторе электронных таблиц. Начинаем с первого вопроса. Сначала нужно составить формулу в ячейке для одной строки данных, а потом её распространить на все строки данных.

1. Выбираем ячейку E2 и вставляем функцию;
   
2. Находим и выбираем функцию ЕСЛИ. Нажимаем ОК. В поле Лог_выражение: "И(C2="Информатика"; D2>600)", в поле Значение_если_истина: 1, в поле Значение_если_ложь: 0. Нажимаем ОК;
   
   
3. Чтобы распространить формулу на все строки, подводим курсор мыши к правому нижнему углу ячейки E2 и когда появится чёрный крестик в месте курсора мыши, нажимаем левую кнопку мыши и тянем вниз формулу.
   
4. Распространив формулу до конца данных по столбцу E мы увидим в правом нижнем углу: Сумма: 32. Сумма единиц в столбце E это будет ответ на первый вопрос задачи. В ячейку H2 запишем: 32 или формулу =СУММ(E2:E1001);
   
5. Чтобы ответить на второй вопрос, включаем фильтр к столбцу С как показано на рисунке на вкладке Главная и оставляем только тех кто сдавал информатику. Нажимаем ОК.
   
   
6. Выделяем весь столбец Баллы и снизу таблицы можно посмотреть среднее значение выделенных ячеек. Это и будет ответ на второй вопрос. Только не забываем округлить до сотых: 546,82.
   
7. Осталось закрыть третий вопрос построив круговую диаграмму. Для этого нужно узнать количество участников из округов с кодами «В», «Зел» и «З». Отфильтруем участников из каждого округа, выделяем столбец с участниками и внизу программа покажет количество выделенных ячеек (программа показывает количество выделенных ячеек, не строчек). Необходимо создать вот такую таблицу с полученными данными(рядом с основной таблицей):
   
   
8. Выделяем эту таблицу нажимаем вкладку Вставка → Диаграммы, выбираем круговую диаграмму. Нажмём на "плюсик", который расположен в правом верхнем углу и выберем метки данных. Должна получится примерно вот такая диаграмма:
   
9. Диаграмму перемещаем в нужное место, так чтобы левый верхний угол диаграммы был рядом с ячейкой G6. Сохраним таблицу под именем, указанным организаторами экзамена.

Пример решения вы можете скачать файлом ниже:

Задача 2 (Демоверсия 2024)

В электронную таблицу внесли данные наблюдения за погодой в течение одного года. Ниже приведены первые пять строк таблицы.

В столбце A записана дата наблюдения; в столбце B – среднесуточная температура воздуха для указанной даты; в столбце C – количество выпавших осадков (в миллиметрах) для указанной даты; в столбце D – среднесуточное атмосферное давление (в миллиметрах ртутного столба). В столбце E записано направление ветра для указанной даты – одно из восьми возможных значений: «СЗ», «С», «СВ», «В», «ЮВ», «Ю», «ЮЗ», «З». В столбце F записана среднесуточная скорость ветра (в метрах в секунду). Всего в электронную таблицу были внесены данные по всем 365 дням года в хронологическом порядке.

Выполните задание.
Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На основании данных, содержащихся в этой таблице, выполните задания.

1. Какое среднее количество осадков выпадало за сутки в осенние месяцы(сентябрь, октябрь, ноябрь)? Ответ на этот вопрос запишите в ячейку H2 таблицы.
2. Какая средняя температура была в те дни года, когда дул северный (С)ветер? Ответ на этот вопрос запишите в ячейку H3 таблицы.
3. Постройте круговую диаграмму, отображающую соотношение количества дней, когда дули ветры «В», «СВ» и «ЮВ». Левый верхний угол диаграммы разместите вблизи ячейки G6. В поле диаграммы должны присутствовать легенда (обозначение, какой сектор диаграммы соответствует каким данным) и числовые значения данных, по которым построена диаграмма.

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

Решение:

Открываем файл с заданием в редакторе электронных таблиц. Можно с помощью фильтра столбцов находить ответы на вопросы, но гораздо удобнее знать и использовать формулы. Начнём с первого вопроса.

1. Нам нужна функция: СРЗНАЧ или AVERAGE (одно и тоже). Эта функция вернёт нам среднее значение по данным осадков. Сначала находим откуда начинаются и заканчиваются данные за осень: с 1 сентября(ячейка C245) по 30 ноября(ячейка C335). Выбираем ячейку H2 и вставляем функцию =СРЗНАЧ(C245:C335); Ответ на первый вопрос найден!
   
2. Находим ответ на второй вопрос. Для ответа на второй вопрос нам помогут функции: СУММЕСЛИ(или SUMIF) и СЧЁТЕСЛИ(или COUNTIF). Функция СУММЕСЛИ найдет нам сумму значений из столбца Температура если в столбце Ветер будет значение С (т.е. северный ветер). И чтобы найти среднее значение, нужно будет эту сумму поделить на количество таких дней. С количеством дней когда дул северный ветер поможет аналогичная функция СЧЁТЕСЛИ — она посчитает сколько всего дней было когда дул северный ветер по столбцу Ветер с записью С. Итак в ячейку H3 пропишем формулу: =СУММЕСЛИ(E2:E366;"С";B2:B366)/СЧЁТЕСЛИ(E2:E366;"С"). Получили ответ на второй вопрос — переходим к третьёму!;
   
3. Построим диаграмму. Для этого необходимо сначала для неё подготовить данные. В ячейки G4, G5, G6 запишем подписи: В, СВ, ЮВ. В ячейках H4, H5, H6 посчитаем соотношение количества дней для указанных ветров. Для этого нужно также с помощью функции СЧЁТЕСЛИ посчитать дни когда дул В(восточный), СВ(северо-восточный) и ЮВ (юго-восточный ветер). В ячейку H4 пропишем: =СЧЁТЕСЛИ(E2:E366;"В"). В ячейку H5 пропишем: =СЧЁТЕСЛИ(E2:E366;"СВ"). В ячейку H6 пропишем: =СЧЁТЕСЛИ(E2:E366;"ЮВ").
   
4. Далее осталось построить саму диаграмму. Выделяем созданную нами "мини таблицу", переходим в пункт меню: Вставка → Диаграммы и выбираем круговую диаграмму. Появляется диаграмма на которую осталось добавить метки данных(нажимаем на зелёный плюсик: Элементы диаграммы → поставить галочку Метки данных) и переместить её на нужное по заданию место(левый верхний угол рядом с ячейкой G6). Сохраним таблицу под именем, указанным организаторами экзамена.
   

Пример решения вы можете скачать файлом ниже:

Задача 3 (Демоверсия 2025)

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

В столбце A указан номер участника; в столбце B – пол; в столбце C – один из трёх статусов: пенсионер, служащий, студент; в столбцах D, E – показатели тестирования для левой и правой руки.

Всего в электронную таблицу были внесены данные 1000 участников. Порядок записей в таблице произвольный.

Выполните задание.
Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На основании данных, содержащихся в этой таблице, выполните задания.

1. Сколько женщин-пенсионеров участвовало в тестировании? Ответ на этот вопрос запишите в ячейку G2 таблицы.
2. Какова разница между максимальным и минимальным показателями для правой руки? Ответ на этот вопрос запишите в ячейку G3 таблицы.
3. Постройте круговую диаграмму, отображающую соотношение количества женщин-пенсионеров, женщин-студентов и женщин-служащих. Левый верхний угол диаграммы разместите вблизи ячейки G6. В поле диаграммы должны присутствовать легенда (обозначение, какой сектор диаграммы соответствует каким данным) и числовые значения данных, по которым построена диаграмма.

Полученную таблицу необходимо сохранить под именем, указанным организаторами экзамена.

Решение:

Открываем файл с заданием в редакторе электронных таблиц. Можно с помощью фильтра столбцов находить ответы на вопросы, но гораздо удобнее знать и использовать формулы. Начнём с первого вопроса.

1. В столбце F для каждого участника определим, является ли участник женщиной и пенсионером одновременно. Для этого в ячейку F2 запишем формулу: =ЕСЛИ(И(B2="жен";C2="пенсионер");1;0) (или =IF(AND(B2="жен";C2="пенсионер");1;0)). Скопируем формулу из F2 во все ячейки диапазона F3:F1001 (двойной клик левой кнопкой мыши по правому нижнему углу ячейки с формулой). Благодаря использованию относительных ссылок, в столбце F для строк 2–1001 будет записано число 1, если участник удовлетворяет двум критериям отбора, и 0 – если это не женщина-пенсионер.
   
   Далее, чтобы вычислить количество женщин-пенсионеров, достаточно в ячейке G2 посчитать сумму значений ячеек диапазона F2:F1001: =СУММ(F2:F1001) (или =SUM(F2:F1001)).
   
2. Находим ответ на второй вопрос. В ячейке G3 вычислим минимальное и максимальное значения показателей правой руки и найдём их разность. Формула будет действовать на диапазон ячеек E2:E1001: =МАКС(E2:E1001)–МИН(E2:E1001) (или =MAX(E2:E1001)–MIN(E2:E1001));
   
3. Для построения диаграммы необходимо сначала подготовить данные. В ячейки G4, G5, G6 запишем подписи: пенсионер, студент, служащий. В ячейках H4, H5, H6 посчитаем соотношение количества женщин-пенсионеров, женщин-студентов и женщин-служащих. Для этого нужно с помощью функции СЧЁТЕСЛИМН(подсчитывает количество ячеек по заданному набору условий) посчитать статусы для женщин(первый критерий): пенсионер, студент, служащий (второй критерий). В ячейку H4 пропишем: =СЧЁТЕСЛИМН(C2:C1001;"пенсионер";B2:B1001;"жен"). В ячейку H5 пропишем: =СЧЁТЕСЛИМН(C2:C1001;"студент";B2:B1001;"жен"). В ячейку H6 пропишем: =СЧЁТЕСЛИМН(C2:C1001;"служащий";B2:B1001;"жен").
   
4. Далее осталось построить саму диаграмму. Выделяем созданную нами "мини таблицу", переходим в пункт меню: Вставка → Диаграммы и выбираем круговую диаграмму. Появляется диаграмма на которую осталось добавить метки данных(нажимаем на зелёный плюсик: Элементы диаграммы → поставить галочку Метки данных) и переместить её на нужное по заданию место(левый верхний угол рядом с ячейкой G6). Сохраним таблицу под именем, указанным организаторами экзамена.
   

Пример решения вы можете скачать файлом ниже: