Задание 14: сортировка, фильтр и подсчёт по нескольким условиям

Берём задание 14 на максимум: сложные условия, сортировка и фильтр — все 3 балла.

Фильтр показывает только строки, удовлетворяющие условию; сортировка переставляет строки по возрастанию/убыванию значения столбца.

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

Часть вопросов задания 14 решается не одной функцией, а комбинацией: сначала отобрать нужные строки, потом что-то по ним посчитать. И почти всегда требуется условие по двум признакам сразу — например, «москвичи с баллом больше 60». Освоив эти приёмы, вы закрываете задание 14 целиком.

Сортировка

Сортировка переупорядочивает таблицу. Команда: выделить таблицу (с заголовками) → «Данные → Сортировка» → выбрать столбец и порядок. Пример вопроса: «фамилия участника с наибольшим баллом» — отсортируйте по баллу по убыванию и посмотрите первую строку. Но осторожно: для подсчётов сортировка не нужна, она лишь помогает «увидеть» крайние значения.

Фильтр

Фильтр временно скрывает лишние строки. «Данные → Фильтр», затем в шапке столбца выбрать условие. Удобно для проверки: отфильтровали «Москва» — видно, сколько таких строк. Но в ответ всё равно лучше писать формулу, чтобы результат был воспроизводим.

Подсчёт по нескольким условиям

Для условия «И» (оба признака) есть функции с окончанием МН (множественные):

«Сколько москвичей набрали больше 60 баллов?» Формула:

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

Если функции МН под рукой нет, тот же результат даёт вспомогательный столбец: в столбце D поставить =ЕСЛИ(И(B2="Москва";C2>60);1;0), растянуть вниз и сложить столбец D функцией СУММ.

Разбор сложной задачи

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

Смоделируем и посчитаем ответы на Python — это и есть «эталон», с которым сверяют формулы:

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

# (1) москвичи с баллом > 60
q1 = sum(1 for f, g, b in data if g == "Москва" and b > 60)
print("Москвичей > 60:", q1)

# (2) максимум среди казанцев
kazan = [b for f, g, b in data if g == "Казань"]
print("Максимум казанцев:", max(kazan))

# (3) сумма баллов всех, кроме Перми
q3 = sum(b for f, g, b in data if g != "Пермь")
print("Сумма (кроме Перми):", q3)

Вывод:

Москвичей > 60: 3
Максимум казанцев: 72
Сумма (кроме Перми): 359

Разберём ответы. (1) Москвичи: Иванов 78, Сидоров 91, Смирнов 63 — все трое больше 60. (2) Казанцы: 55 и 72, максимум 72. (3) Сумма всех минус Пермь (47): 78+55+91+63+72 = 359. Соответствующие формулы — СЧЁТЕСЛИМН, МАКС по отфильтрованным и СУММЕСЛИ с условием «не Пермь».

Стратегия на 3 балла

1. Прочитайте все вопросы, прежде чем считать, — иногда один вспомогательный столбец помогает сразу нескольким.
2. Используйте формулы, а не ручной подсчёт; диапазоны проверяйте дважды.
3. Для условий «И» — функции МН или вспомогательный столбец с ЕСЛИ/И.
4. Сохраните файл с правильным именем; ещё раз сверьте ячейки-ответы.

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

• Сортируют таблицу и теряют соответствие строк (если выделили не всю таблицу).
• Для «И» используют два отдельных СЧЁТЕСЛИ и складывают — это даёт неверный результат.
• Записывают условие ">60" без кавычек — таблица не понимает.
• Считают «кроме Перми» как «только Москва», забывая Казань.

Итог

• Сортировка и фильтр помогают «увидеть» данные; ответы давайте формулами.
• Несколько условий — функции СЧЁТЕСЛИМН/СУММЕСЛИМН или вспомогательный столбец с ЕСЛИ(И(...)).
• Сверяйте результат с расчётом-эталоном; следите за диапазонами.