Для выполнения 14-го задания ОГЭ по информатике необходимо уметь обрабатывать большой массив данных с помощью средств электронной таблицы. Задание содержит три оцениваемых элемента: нужно определить два числовых значения и построить диаграмму.

Некоторые функции, которые могут использоваться в электронных таблицах:

СУММ (диапазон) — вычисляет сумму чисел в ячейках выделенного диапазона;

МИН (диапазон) — определяет наименьшее значение из списка аргументов;

МАКС (диапазон) — определяет наибольшее значение из списка аргументов;

СЧЁТ (диапазон) — подсчитывает количество чисел в аргументе (счётчик);

СРЗНАЧ (диапазон) — вычисляет среднее арифметическое чисел в ячейках выделенного диапазона;

СЧЕТЕСЛИ (диапазон; критерий) — считает количество ячеек, данные в которых соответствуют условию.

Диаграмма считается построенной верно, если её геометрические элементы правильно отображают представляемые данные, отображаемые данные определены правильно и явно указаны на диаграмме тем или иным способом, диаграмма снабжена легендой. Сохранить задание нужно в виде файла в формате *.ods, *.xls или *.xlsx.

Помимо функций, с помощью которых можно решить данное задание, ещё используют сортировку данных.

Чтобы провести сортировку данных в Excel, можно использовать инструмент «Сортировка» на вкладке «Данные»:

Сортировка по одному критерию:

1. В столбце, по которому должна быть выполнена сортировка, нужно выделить любую ячейку (весь столбец выделять не надо).
2. На вкладке «Данные» найти группу команд «Сортировка и фильтр».
3. Выбрать нужную кнопку: «Сортировка по возрастанию» или «Сортировка по убыванию».

Сортировка по форматированию:

1. Щёлкнуть по любой ячейке из столбца, по которому будет выполняться сортировка.
2. На вкладке «Данные» выбрать группу «Сортировка и фильтр», а затем выбрать команду «Сортировка».
3. В поле «Столбец» указать столбец, по которому будет проводиться сортировка.
4. В поле «Сортировка» из всплывающего меню выбрать критерий сортировки: цвет ячейки, цвет шрифта или значок ячейки.
5. Поле «Порядок» содержит два выпадающих списка. В первом нужно выбрать тип критерия, а во втором — размещение ячеек, отсортированных по данному критерию (строку «Сверху» или «Снизу»).
6. При необходимости добавить ещё один критерий сортировки, в окне «Сортировка» нужно выбрать кнопку «Добавить уровень».
7. После выбора сортировки нажать кнопку «ОК».

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

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

Выполните задание.

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

1. Определите, сколько учеников, которые проходили тестирование по информатике, набрали более 600 баллов. Ответ запишите в ячейку H2 таблицы.

2. Найдите средний тестовый балл учеников, которые проходили тестирование по информатике. Ответ запишите в ячейку H3 таблицы с точностью не менее двух знаков после запятой.

3. Постройте круговую диаграмму, отображающую соотношение числа участников из округов с кодами «В», «Зел» и «З». Левый верхний угол диаграммы разместите вблизи ячейки G6.

РЕШЕНИЕ

1. Запишем в ячейку E2 следующую формулу =ЕСЛИ(И(D2>600; C2="информатика");D2;0) и скопируем ее в диапазон E3:E1001. Применив операцию =ЕСЛИ(E2>0;1;0), получим столбец (F): с единицами и нулями. Далее, используем операцию =СУММ(F2:F1001). Получим количество учеников, которые проходили тестирование по информатике и набрали более 600 баллов. Таких 32 человека.

2. Для ответа на второй вопрос используем операцию «ЕСЛИ». Запишем в ячейку G2 следующее выражение: =ЕСЛИ(C2="информатика"; D2;0), в результате применения данной операции к диапазону ячеек G2:G1001, получим столбец, в котором записаны баллы только учеников, сдававших информатику. Сложив значения в ячейках, получим сумму баллов учеников: 39 371. Найдем количество учеников, сдававших информатику, с помощью команды =СЧЁТЕСЛИ(C2:C1001;"информатика"), получим 72. Разделив сумму баллов на количество учеников, получим: 546,8194 — искомый средний балл.

3. В ячейку J2 вставим формулу =СЧЁТЕСЛИ(A2:A1001; "В"), в ячейку J3 вставим формулу =СЧЁТЕСЛИ(A2:A1001; "Зел"), в ячейку J4 вставим формулу =СЧЁТЕСЛИ(A2:A1001; "З"). Теперь построим по полученным значениям круговую диаграмму, подпишем сектора.

Ответ: 1) 32; 2) 546,82.

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

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

Выполните задание.

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

1. Сколько учеников в Центральном округе (Ц) выбрали в качестве любимого предмета английский язык? Ответ на этот вопрос запишите в ячейку Н2 таблицы.

2. Каков средний тестовый балл у учеников Восточного округа (В)? Ответ на этот вопрос запишите в ячейку Н3 таблицы с точностью не менее двух знаков после запятой.

3. Постройте круговую диаграмму, отображающую соотношение числа участников из округов с кодами «C», «Ю» и «З». Левый верхний угол диаграммы разместите вблизи ячейки G6.

РЕШЕНИЕ

1. Запишем в ячейку H2 следующую формулу =ЕСЛИ(A2="Ц";C2;0) и скопируем ее в диапазон H3:H1001. В таком случае, в ячейку столбца Н будет записываться название предмета, если ученик из Центрального округа и «0», если это не так. Применив операцию =ЕСЛИ(H2="английский язык";1;0), получим столбец(J) с единицами и нулями. Далее, используем операцию =СУММ(J2:J1001). Получим количество учеников, которые считают своим любимым предметом английский язык. Таких 20 человек.

2. Для ответа на второй вопрос используем операцию «ЕСЛИ». Запишем в ячейку E2 следующее выражение: =ЕСЛИ(A2="В";D2;0), в результате применения данной операции к диапазону ячеек Е2:Е1001, получим столбец, в котором записаны баллы только учеников из Восточного округа. Просуммировав значения в ячейках, получим сумму баллов учеников: 66 012. Далее посчитаем количество учеников из Восточного округа с помощью команды =СЧЁТЕСЛИ(A2:A1001;"В"), получим: 132. Разделив сумму баллов на количество учеников, получим: 500,09 — искомый средний балл.

3. В ячейку J2 вставим формулу =СЧЁТЕСЛИ(A2:A1001; "С"), в ячейку J3 вставим формулу =СЧЁТЕСЛИ(A2:A1001; "Ю"), в ячейку J4 вставим формулу =СЧЁТЕСЛИ(A2:A1001; "З"). Теперь построим по полученным значениям круговую диаграмму, подпишем сектора.

Ответ: 1) 20; 2) 500,09.