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

~5 минут · 2 примера · 4 вопроса

Учимся обрабатывать массив данных формулами таблицы — это «дорогое» задание №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 — числа должны совпасть.

Проверь себя

Четыре вопроса по уроку. Ответы никуда не идут — это для тебя.

1Какая функция посчитает, сколько ячеек диапазона содержат слово «Москва»?

2Как вычислить средний балл только москвичей?

3Чем абсолютная ссылка $C$1 отличается от относительной C1?

4Почему в задании 14 нельзя просто вписать посчитанное вручную число?

Следующий урок
15 · Задание 14: сортировка, фильтр и подсчёт по нескольким условиям Открыть урок 15
Урок 13: Задание 13: текстовый документ и презентация