Задание 14 ОГЭ: обработка таблицы

Учимся вытаскивать из большой таблицы количество, среднее и максимум по условию — это и есть задание 14 ОГЭ.

Задание 14 ОГЭ — практическая работа в электронной таблице: открыть готовый файл с данными, посчитать несколько величин по заданным условиям и записать ответы в указанные ячейки.

На экзамене вам дают файл (обычно лист с сотнями строк) и 2–3 коротких вопроса вроде «сколько участников из города N набрали больше 80 баллов» или «какое среднее значение по предмету X». Руками такую таблицу не пересчитать — за это и проверяют умение применять функции с условием. Разберём типовой вариант от начала до конца.

Зачем это нужно

В жизни таблицы с тысячами строк — норма: журнал продаж, список заказов, результаты теста. Кликать по каждой строке бессмысленно. Функции СЧЁТЕСЛИ, СУММЕСЛИ, СРЗНАЧЕСЛИ делают за секунду то, на что вручную ушёл бы час, и не ошибаются в арифметике. Экзамен проверяет ровно этот навык: сформулировать условие на языке формул.

Условие типовой задачи

Пусть на листе с 1000 строк (строки 2–1001, строка 1 — заголовки) есть колонки: A — фамилия, B — населённый пункт, C — пол (м/ж), D — баллы за тест (целое число 0–100). Нужно ответить:

сколько участников из города «Берёзовка»;
каков средний балл участниц (пол «ж») по всей таблице;
какой максимальный балл среди тех, кто из «Берёзовки».

Порядок действий

Шаг 1. Количество по условию — СЧЁТЕСЛИ

Функция СЧЁТЕСЛИ(диапазон; условие) считает, сколько ячеек диапазона удовлетворяют условию. Город лежит в столбце B:

=СЧЁТЕСЛИ(B2:B1001; "Берёзовка")

Текстовое условие берём в кавычки. Регистр функции игнорирует, а вот опечатку — нет: «Березовка» без «ё» даст другой результат, поэтому копируйте название прямо из ячейки.

Шаг 2. Среднее по условию — СРЗНАЧЕСЛИ

Здесь условие на одном столбце (пол в C), а усреднять надо другой столбец (баллы в D). Для этого есть СРЗНАЧЕСЛИ(диапазон_условия; условие; диапазон_усреднения):

=СРЗНАЧЕСЛИ(C2:C1001; "ж"; D2:D1001)

Функция пройдёт по строкам, где в C стоит «ж», возьмёт соответствующие баллы из D и усреднит только их. Ответ обычно просят округлить — читайте формулировку: «с точностью до целых» означает обернуть результат в ОКРУГЛ(...; 0).

Шаг 3. Максимум по условию — МАКСЕСЛИ или связка

Если в вашей версии редактора есть МАКСЕСЛИ, всё просто:

=МАКСЕСЛИ(D2:D1001; B2:B1001; "Берёзовка")

Если такой функции нет (старый пакет), используют связку с массивом: создают вспомогательный столбец E, где для строк «Берёзовки» стоит балл, иначе пусто, и берут от него МАКС. Но на ОГЭ обычно версия современная, и МАКСЕСЛИ работает.

Как это работает

Под капотом функция с условием — это цикл по диапазону: для каждой ячейки проверяется логическое условие, и накопитель обновляется. По сути СЧЁТЕСЛИ считает количество истинных проверок. Формально для столбца значений $b_1, b_2, \dots, b_n$ и искомого города $g$ результат равен

$$ \text{СЧЁТЕСЛИ} = \sum_{i=1}^{n} [\, b_i = g \,] $$

где квадратные скобки (нотация Айверсона) дают 1, если равенство верно, и 0 иначе. Среднее по условию — это сумма подходящих значений, делённая на их количество:

$$ \text{СРЗНАЧЕСЛИ} = \frac{\sum_{i=1}^{n} d_i \cdot [\, c_i = \text{«ж»} \,]}{\sum_{i=1}^{n} [\, c_i = \text{«ж»} \,]} $$

Понимание формулы помогает поймать ошибку: если знаменатель равен нулю (ни одной подходящей строки), редактор вернёт #ДЕЛ/0! — значит, условие не совпало ни с чем.

Чтобы убедиться, что формулы дают то же, что «честный» перебор, проверим логику на маленьком наборе обычным Python:

data = [
    ("Иванов", "Берёзовка", "м", 88),
    ("Петрова", "Дубки",     "ж", 95),
    ("Сидорова","Берёзовка", "ж", 72),
    ("Кузин",   "Берёзовка", "м", 90),
    ("Орлова",  "Дубки",     "ж", 81),
]
count_b = sum(1 for r in data if r[1] == "Берёзовка")
zh = [r[3] for r in data if r[2] == "ж"]
avg_zh = sum(zh) / len(zh)
max_b = max(r[3] for r in data if r[1] == "Берёзовка")
print("Из Берёзовки:", count_b)
print("Средний балл девушек:", round(avg_zh, 1))
print("Максимум по Берёзовке:", max_b)

Вывод:

Из Берёзовки: 3
Средний балл девушек: 82.7
Максимум по Берёзовке: 90

Та же тройка действий, что и в формулах: фильтр по условию, агрегат над отфильтрованным. На экзамене вы это делаете встроенными функциями, но логика идентична.

Частые ошибки

Неправильный диапазон. Берут B1:B1001 вместе со строкой заголовка — для счёта текста это не страшно, но для среднего/максимума заголовок может попасть в расчёт. Начинайте со строки 2.
Путают СРЗНАЧ и СРЗНАЧЕСЛИ. Обычный СРЗНАЧ(D2:D1001) усредняет всех подряд, без условия. Если в задаче есть слова «среди тех, кто…», нужна версия с ...ЕСЛИ.
Опечатка в текстовом условии. «Берёзовка» против «Березовка» — для машины это разные строки. Копируйте значение из ячейки.
Забыли округлить. Если просят целое, а вы записали 82,7 — ответ не засчитают. Читайте требование к точности.
Записали ответ не в ту ячейку. В задании жёстко указано, куда вписать каждый результат (например, H2, H3, H4). Перепутаете порядок — потеряете баллы.

Итоги

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