Сортировка, фильтр, видимые строки

В таблице покупок сто строк, а нужна сумма только по Москве. Можно упорядочить строки по городу или спрятать лишние фильтром — но обычная сумма всё равно посчитает спрятанное. Сортировка меняет порядок записей, фильтр только прячет строки, а обычные функции считают и спрятанное.

Разберём сортировку, фильтр и то, как сложить только видимые строки.

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

Если отсортировать только столбец цен, оставив названия на месте, данные перемешаются: цена перестанет относиться к прежнему товару. Это не косметическая ошибка, а изменение смысла таблицы.

В средней таблице каждая цена осталась при своём товаре. В правой отсортирован только столбец цен: тетрадь «подешевела» до 15, а карандаш стал стоить 120.

При сортировке по нескольким признакам важен порядок уровней. «Сначала город, затем балл по убыванию» означает: сначала сгруппировать города, внутри каждого города упорядочить баллы. Обратный порядок уровней даст иной результат.

В средней таблице города идут группами, а баллы упорядочены только внутри своего города: 85 и 64 у Казани, 72 и 60 у Москвы. В правой главный порядок — баллы, и города перемешаны; город решал бы только при равных баллах.

Сортировка записей сверху вниз не равна сортировке чисел внутри каждой строки. В задании 9 обычно удобнее найти минимум, максимум и нужные порядковые элементы формулами, чем пытаться отдельно сортировать тысячи строк.

Фильтр временно скрывает неподходящие строки, данные остаются в книге. Выдели таблицу с заголовками и выбери «Данные → Фильтр»: в заголовках появятся кнопки со стрелкой. Кнопка открывает список значений столбца — сними лишние галочки, и неподходящие строки исчезнут.

Условия в разных столбцах обычно применяются совместно: город МоскваИ балл не меньше 60. Несколько отмеченных значений внутри одного столбца — любое из них: Москва ИЛИ Казань. Если часть строк неожиданно исчезла, проверь значки фильтра в заголовках.

Перед новой независимой задачей сбрось предыдущие фильтры. Иначе новое условие может добавиться к старому, и ты незаметно посчитаешь более узкую выборку.

СУММ складывает и скрытые фильтром строки; сумму видимых считает ПРОМЕЖУТОЧНЫЕ.ИТОГИ. =СУММ(D2:D100) не смотрит, какие строки сейчас спрятаны, и СУММЕСЛИ тоже проверяет только свой критерий. Поэтому сумма всего столбца после фильтра — не сумма отобранных записей. Сумму видимых строк даёт

Первый аргумент выбирает операцию: 109 — сумма без скрытых строк. На листе ниже включён фильтр «город — Москва»: строки 3 и 5 скрыты, номера строк идут с пропусками, как в Excel.

Видимая сумма и СУММЕСЛИ совпали, но по разным причинам: первая зависит от фильтра, включённого сейчас, вторая хранит условие в самой формуле. Сбрось фильтр — D9 станет 800, а D10 останется 300.

Строка состояния внизу окна Excel тоже считает только видимые ячейки выделения: сумму, среднее и количество.

Это удобная проверка, но зависит она от выделения и фильтра, а ответ задачи записывают формулой — она сохраняет условие и пересчитывается.