Работа с постоянно обновляемыми данными в таблицах часто требует гибких решений. Представьте ситуацию: у вас есть столбец с ежедневно добавляемой информацией, и вам нужно автоматически отслеживать количество заполненных ячеек за последние 7 дней. Обычные функции вроде COUNTA или INDEX могут не справиться, если данные меняются динамически. Здесь на помощь приходит комбинация функций SORTN и COUNTA – мощный инструмент, о котором многие даже не догадываются.
Кстати, стандартный подход с формулой =COUNTA(INDEX(C:C, MAX(2, COUNTA(C:C)-6)):C) действительно выглядит логичным на первый взгляд. Он пытается определить нижнюю границу диапазона, отступая 6 строк от конца. Но в реальности эта конструкция может давать сбои, если в столбце встречаются пропуски или данные добавляются нерегулярно. Например, если в середине таблицы случайно окажется пустая ячейка, расчёт «последних 7 строк» собьётся.
Почему SORTN – оптимальное решение
Функция SORTN позволяет сортировать данные и выбирать N первых или последних записей по заданному критерию. В сочетании с COUNTA она решает проблему динамического диапазона. Вот готовая формула:
=COUNTA(SORTN(C2:C, 7, 0, ROW(C2:C) * NOT(ISBLANK(A2:A)), FALSE)Разберём её по частям:
- SORTN(C2:C, 7, 0, …) – выбирает 7 последних строк из диапазона C2:C.
- ROW(C2:C) * NOT(ISBLANK(A2:A)) – создаёт ключ сортировки: чем ниже строка (больший номер), тем выше приоритет, но только если в столбце A есть данные (это фильтрует пустые строки).
- FALSE – отключает сортировку по убыванию, оставляя исходный порядок.
Обратите внимание: формула предполагает, что столбец A содержит даты или маркеры заполнения. Если у вас данные начинаются, скажем, с 5-й строки, замените C2:C и A2:A на актуальные диапазоны.
Тонкости настройки и частые ошибки
1. Проверьте привязку к столбцу-маркеру. В примере используется NOT(ISBLANK(A2:A)) – это гарантирует, что учитываются только строки с данными в столбце A. Если ваш «маркер» находится в другом столбце (например, дата в B), измените A2:A на соответствующий диапазон.
2. Убедитесь, что в столбце C нет формул. COUNTA считает ячейки с формулами (даже возвращающими “”) как непустые. Если вам нужно игнорировать такие случаи, используйте вместо COUNTA комбинацию SUMPRODUCT и LEN:
=SUMPRODUCT(--(LEN(SORTN(C2:C, 7, 0, ROW(C2:C) * NOT(ISBLANK(A2:A)), FALSE))>0))3. Динамический заголовок. Если первая строка – шапка таблицы, начинайте диапазон со второй строки (C2:C вместо C:C), чтобы избежать её учёта в подсчёте.
Альтернативный подход для сложных сценариев
Если данные добавляются не ежедневно или требуется учитывать пропуски, можно комбинировать FILTER и ROW:
=COUNTA(FILTER(C2:C, ROW(C2:C)>=LARGE(IF(NOT(ISBLANK(A2:A)), ROW(C2:C)), 7))Эта формула находит 7 последних строк с непустыми значениями в A, а затем подсчитывает непустые ячейки в C для этого диапазона. Однако такой метод может замедлять работу в больших таблицах.
Подводя итог: SORTN – наиболее стабильный и производительный вариант. Если вы только начинаете работать с динамическими диапазонами, смело рекомендую начать с него. А если столкнётесь с неочевидными ошибками – проверьте, нет ли скрытых символов или формул в ячейках. Готов ответить на ваши вопросы в комментариях!