Задание 14. Обработка данных в электронной таблице — теория и разбор

Это задание всегда даёт большую таблицу (на экзамене — до тысячи строк), которую руками не обработать. Но нужны буквально три готовые функции — остальное электронная таблица сделает сама.

Что нужно знать

Задание всегда состоит из трёх независимо оцениваемых частей, по 1 баллу каждая:

1. Посчитать количество строк по условию — функция СЧЁТЕСЛИ (COUNTIF).
2. Посчитать среднее значение по условию — через СУММ и СЧЁТЕСЛИ вместе (или функцией СРЗНАЧЕСЛИ / AVERAGEIF напрямую).
3. Построить круговую диаграмму по нескольким категориям — с легендой и подписанными значениями.

СЧЁТЕСЛИ(диапазон; условие) считает, сколько ячеек в диапазоне удовлетворяют условию. СУММЕСЛИ(диапазон_условия; условие; диапазон_суммирования) складывает значения только тех строк, что подходят под условие.

Если нужно условие сразу по двум столбцам (например, «предмет = Биология» И «балл > 500»), в одну формулу СЧЁТЕСЛИ такое не поместить — надёжнее завести вспомогательный столбец с функцией ЕСЛИ(И(...);1;0) и уже его просуммировать или посчитать СЧЁТЕСЛИ по нему.

Формула среднего по условию без готовой функции: среднее = СУММЕСЛИ(диапазон_условия; условие; диапазон_значений) / СЧЁТЕСЛИ(диапазон_условия; условие).

Разбор примера

Таблица с результатами тестирования (округ, ученик, предмет, балл):

Определите: (1) сколько учеников по предмету «Биология» набрали более 500 баллов; (2) средний балл по предмету «Биология».

Как думать. Для первого вопроса нужно условие сразу по двум столбцам (предмет и балл) — считаем через вспомогательный столбец. Для второго — сумма баллов по биологии делится на количество учеников по биологии.

Как решить в Python

Логику формул удобно проверить кодом — так же, как электронная таблица считает по условиям:

rows = [
    ('С', 'Иванов', 'Биология', 520), ('В', 'Петров', 'Биология', 480),
    ('Ю', 'Сидоров', 'Химия', 510), ('С', 'Козлов', 'Биология', 610),
    ('В', 'Смирнова', 'Биология', 495), ('Ю', 'Волкова', 'Химия', 530),
    ('С', 'Морозов', 'Биология', 540), ('В', 'Зайцев', 'Биология', 470),
    ('Ю', 'Лебедева', 'Биология', 505), ('С', 'Орлова', 'Химия', 600),
]

biology = [r for r in rows if r[2] == 'Биология']
count_over_500 = sum(1 for r in biology if r[3] > 500)
average = sum(r[3] for r in biology) / len(biology)

print('Больше 500 баллов:', count_over_500)   # 4
print('Средний балл:', round(average, 2))      # 517.14

На экзамене то же самое делают формулы СЧЁТЕСЛИ/СУММЕСЛИ прямо в таблице — код здесь только для самопроверки логики.