Задание 14: электронная таблица — формулы и функции

Учимся обрабатывать массив данных формулами таблицы — это «дорогое» задание №14 на 3 балла.

Задание 14 — практическое: вам дают готовую таблицу с данными и просят с помощью формул посчитать ответы на вопросы (сколько, сколько в среднем, сумма по условию) и иногда построить диаграмму.

Что проверяет задание 14

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

Ссылки на ячейки

Ячейка адресуется буквой столбца и номером строки: B2, C10. Диапазон — через двоеточие: B2:B100 (все ячейки столбца B со 2-й по 100-ю строку). Это «сырьё» для функций.

Главные функции

Функция	Что делает	Пример
СУММ	сумма чисел диапазона	`=СУММ(C2:C100)`
СРЗНАЧ	среднее арифметическое	`=СРЗНАЧ(C2:C100)`
СЧЁТ	сколько чисел в диапазоне	`=СЧЁТ(C2:C100)`
СЧЁТЕСЛИ	сколько ячеек удовлетворяют условию	`=СЧЁТЕСЛИ(B2:B100;"Москва")`
СУММЕСЛИ	сумма по условию	`=СУММЕСЛИ(B2:B100;"Москва";C2:C100)`
МИН / МАКС	наименьшее / наибольшее	`=МАКС(C2:C100)`
ЕСЛИ	выбор по условию	`=ЕСЛИ(C2>60;"сдал";"нет")`

В англоязычном интерфейсе это SUM, AVERAGE, COUNT, COUNTIF, SUMIF, MIN, MAX, IF — смысл тот же.

Разбор типовой задачи

Дана таблица: фамилия, город, балл. Вопросы: (1) сколько участников из Москвы; (2) средний балл москвичей; (3) сколько человек набрали больше 60 баллов.

Формулы-ответы (для диапазона строк 2–6):

(1) =СЧЁТЕСЛИ(B2:B6;"Москва")
(2) =СУММЕСЛИ(B2:B6;"Москва";C2:C6) / СЧЁТЕСЛИ(B2:B6;"Москва")
(3) =СЧЁТЕСЛИ(C2:C6;">60")

Обратите внимание на вопрос (2): средний балл москвичей — это «сумма баллов москвичей», делённая на «их количество». В современных таблицах есть готовая СРЗНАЧЕСЛИ, но через СУММЕСЛИ/СЧЁТЕСЛИ надёжнее и понятнее.

Python-аналог расчёта (для самопроверки)

Чтобы убедиться, что формулы дадут верный ответ, смоделируем ту же таблицу на Python. На самом экзамене вы пишете формулы в таблице, но дома такой расчёт — отличный способ проверить себя.

data = [
    ("Иванов",   "Москва", 78),
    ("Петров",   "Казань", 55),
    ("Сидоров",  "Москва", 91),
    ("Кузнецов", "Пермь",  47),
    ("Смирнов",  "Москва", 63),
]

moscow = [d for d in data if d[1] == "Москва"]
print("Москвичей:", len(moscow))                         # СЧЁТЕСЛИ
print("Средний балл москвичей:",
      round(sum(d[2] for d in moscow) / len(moscow), 2))   # СУММЕСЛИ/СЧЁТЕСЛИ
print("Набрали больше 60:", sum(1 for d in data if d[2] > 60))

Вывод:

Москвичей: 3
Средний балл москвичей: 77.33
Набрали больше 60: 3

Эти три числа — и есть ответы, которые в таблице получатся формулами. Совпали ваши формулы с расчётом — значит, всё верно.

Относительные и абсолютные ссылки

Когда формулу «растягивают» вниз по столбцу, ссылки сдвигаются: из C2 в следующей строке станет C3 — это относительная ссылка. Если нужно, чтобы ссылка не менялась (например, на ячейку с курсом или порогом), её «закрепляют» знаком $: $C$1 — это абсолютная ссылка. Путаница тут — частая причина потери баллов при копировании формул.

Диаграммы

Иногда просят построить диаграмму (например, столбчатую по количеству участников из разных городов). Алгоритм: выделить нужные данные → «Вставка → Диаграмма» → выбрать тип. Важно выделять именно те столбцы, что просят, иначе диаграмма получится не по тем данным.

Типичные ошибки

Считают вручную и вписывают число — а проверяют наличие формулы; результат может «уплыть».
Неверный диапазон: захватили лишние или забыли часть строк.
Условие в кавычках записано неточно: ">60" против ">=60".
Забыли про абсолютную ссылку — при копировании формула «съехала».
Диаграмма построена не по тем столбцам.

Итог

Отвечайте формулами: СУММ, СРЗНАЧ, СЧЁТ, СЧЁТЕСЛИ, СУММЕСЛИ, МИН/МАКС, ЕСЛИ.
«Среднее по условию» = СУММЕСЛИ ÷ СЧЁТЕСЛИ.
Следите за диапазоном и за $ в абсолютных ссылках.
Дома проверяйте себя расчётом на Python — числа должны совпасть.