Обработка большого массива данных.
Задание 1
В электронную таблицу занесли результаты тестирования учащихся по физике и информатике. Вот первые строки получившейся таблицы:
A |
B |
C |
D |
|
1 |
Ученик |
Округ |
Физика |
Информатика |
2 |
Брусов Анатолий |
Западный |
18 |
12 |
3 |
Васильев Александр |
Восточный |
56 |
66 |
4 |
Ермишин Роман |
Северный |
44 |
49 |
5 |
Моникашвили Эдуард |
Центральный |
65 |
78 |
6 |
Круглов Никита |
Центральный |
57 |
67 |
7 |
Титова Анастасия |
Северный |
54 |
63 |
В столбце А указаны фамилия и имя учащегося; в столбце В — округ учащегося; в столбцах С, D — баллы, полученные, соответственно, по физике и информатике. По каждому предмету можно было набрать от 0 до 100 баллов. Всего в электронную таблицу были занесены данные по 266 учащимся. Порядок записей в таблице произвольный.
Выполните задание.
Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
1. Чему равна наименьшая сумма баллов по двум предметам среди учащихся округа «Центральный»? Ответ на этот вопрос запишите в ячейку G1 таблицы.
2. Сколько процентов от общего числа участников составили ученики, получившие по физике меньше 70 баллов? Ответ с точностью до одного знака после запятой запишите в ячейку G3 таблицы.
3. Постройте круговую диаграмму, отображающую соотношение учеников из округов «Восточный», «Центральный» и «Южный». Левый верхний угол диаграммы разместите вблизи ячейки G6.
Решение.
1. В столбце Е для каждого учащегося вычислим сумму баллов по двум предметам, если это ученик школы округа «Центральный». Для ученика другого округа ячейка будет содержать пустую строку. В ячейку Е2 запишем формулу =ЕСЛИ(В2=«Центральный»; С2+D2; "") Скопируем формулу во все ячейки диапазона ЕЗ:Е273. Благодаря использованию относительных ссылок в столбце Е непустые значения строк 2−267 будут равны суммам баллов учеников школ округа «Центральный». Для того чтобы найти наименьшую сумму, в ячейку G1 внесём формулу =МИН(Е2:Е267).
2. Для ответа на второй вопрос в дополнительной ячейке, например в НЗ, найдём количество участников, набравших по физике менее 70 баллов. Это можно сделать различными способами, в том числе при помощи функции =СЧЁТЕСЛИ(С2:С267; "<70"). Выразим полученное значение в процентах от общего числа участников тестирования. Результат запишем в ячейку G3: =НЗ/266*100.
3. В ячейку J2 вставим формулу =СЧЁТЕСЛИ(B2:B267; "Восточный"), в ячейку J3 вставим формулу =СЧЁТЕСЛИ(B2:B267; "Центральный"), в ячейку J4 вставим формулу =СЧЁТЕСЛИ(B2:B267; "Южный"). Теперь построим по полученным значениям круговую диаграмму, подпишем сектора.
Ответ: 1) 68; 2) 90,2.
Задание 2
В электронную таблицу занесли данные о тестировании учеников. Ниже приведены первые пять строк таблицы:
A |
B |
C |
D |
|
1 |
округ |
фамилия |
предмет |
балл |
2 |
C |
Ученик 1 |
обществознание |
246 |
3 |
В |
Ученик 2 |
немецкий язык |
530 |
4 |
Ю |
Ученик 3 |
русский язык |
576 |
5 |
СВ |
Ученик 4 |
обществознание |
304 |
В столбце А записан округ, в котором учится ученик; в столбце В — фамилия; в столбце С — любимый предмет; в столбце D — тестовый балл. Всего в электронную таблицу были занесены данные по 1000 ученикам.
Выполните задание.
Откройте файл с данной электронной таблицей (расположение файла Вам сообщат организаторы экзамена). На основании данных, содержащихся в этой таблице, ответьте на два вопроса.
1. Сколько учеников в Южном округе (Ю) выбрали в качестве любимого предмета английский язык? Ответ на этот вопрос запишите в ячейку Н2 таблицы.
2. Каков средний тестовый балл у учеников Юго-Восточного округа (ЮВ)? Ответ на этот вопрос запишите в ячейку Н3 таблицы с точностью не менее двух знаков после запятой.
3. Постройте круговую диаграмму, отображающую соотношение числа участников из округов с кодами «CВ», «ЮВ» и «З». Левый верхний угол диаграммы разместите вблизи ячейки G6.
Решение.
1. Запишем в ячейку H2 следующую формулу =ЕСЛИ(A2="Ю";C2;0) и скопируем ее в диапазон H3:H1001. В таком случае, в ячейку столбца Н будет записываться название предмета, если ученик из Южного округа и «0», если это не так. Применив операцию =ЕСЛИ(H2="английский язык";1;0), получим столбец(J) с единицами и нулями. Далее, используем операцию =СУММ(J2:J1001). Получим количество учеников, которые считают своим любимым предметом английский язык. Таких 12 человек.
2. Для ответа на второй вопрос используем операцию «ЕСЛИ». Запишем в ячейку E2 следующее выражение: =ЕСЛИ(A2="ЮВ";D2;0), в результате применения данной операции к диапазону ячеек Е2:Е1001, получим столбец, в котором записаны баллы только учеников из Юго-Восточного округа. Сложив значения в ячейках, получим сумму баллов учеников: 28 913. Найдём количество учеников из Юго-Восточного округа с помощью команды =СЧЁТЕСЛИ(A2:A1001;"ЮВ"), получим 56. Разделив сумму баллов на количество учеников, получим: 516,30 — искомый средний балл.
3. В ячейку J2 вставим формулу =СЧЁТЕСЛИ(A2:A1001; "СВ"), в ячейку J3 вставим формулу =СЧЁТЕСЛИ(A2:A1001; "ЮВ"), в ячейку J4 вставим формулу =СЧЁТЕСЛИ(A2:A1001; "З"). Теперь построим по полученным значениям круговую диаграмму, подпишем сектора.
Ответ: 1) 12; 2) 516,30.
Ниже можно скачать файл с задачами для тренировки: