Обработка только видимых строк
Постановка задачи
При работе в Microsoft Excel иногда возникают ситуации, когда нам с вами нужно как-то особо и отдельно обрабатывать только видимые или, наоборот, только скрытые пользователем строки. Например, считать какие-то хитрые отчёты именно по видимым ячейкам, или загружать только видимые ячейки для дальнейшей обработки в Power Query и так далее.
Можно, конечно, выделить и скопировать куда-нибудь отдельно только видимые ячейки, используя сочетание клавиш Ctrl+G и далее кнопки Выделить и Только видимые ячейки (GoTo - Visible cells only), но это разовый ручной вариант, который придётся многократно повторять в будущем. Хотелось бы решить эту задачу более изящно, раз - и навсегда.
При этом надо держать в уме, что менять видимость строк пользователь может разными способами:
- Использовать фильтры в шапке "умной" таблицы или включенные вручную через Данные - Фильтр (Data - Autofilter).
- Сворачивать и разворачивать группировки с помощью значков "плюс" и "минус" слева, созданных через Данные - Группировать (Data - Group).
- Прятать и отображать отдельные строки вручную, щёлкая по ним правой кнопкой мыши и выбирая команды Скрыть/Показать (Show / Hide) в контекстном меню.
- Если данные отформатированы в виде "умной" динамической таблицы, то добавить к ним срез через Конструктор таблиц - Срез (Table Design - Slicer), выбор в котором также будет приводить к фильтрации строк, т.е. их скрытию.
Во всех этих случаях для каждой строки нам нужно каким-то образом однозначно определять - осталась ли она видимой и отображается сейчас на экране или скрыта от глаз пользователя.
Определяем видимость каждой строки
Проще всего решить эту задачу с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS). В стандартном варианте эта функция используется для подсчёта итогов по заданному одномерному вертикальному диапазону (столбцу):
Причём первый аргумент здесь - это код функции, которая будет вычисляться по исходным данным в качестве итога: 9 - это сумма, 1- среднее, 4 - максимум и т.д.
Хитрость в том, что функции с кодами больше 100 у этой функции чувствительны не только к скрытым фильтром строкам, но и к другим вариантам скрытия (вручную или группировкой). Так что мы с вами можем использовать этот момент для определения того, видима ли каждая строка в отдельности. Для этого достаточно добавить к исходной таблице ещё один столбец, где использовать эту функцию для подсчёта количества непустых ячеек, например, в одном из соседних столбцов:
Теперь, если любым способом скрывать строки в нашей таблице, то в добавленном столбце функция вернёт в качестве результата 0 для скрытых и 1 для оставшихся видимыми строк.
Другой способ достичь того же результата - это использовать функцию АГРЕГАТ (AGGREGATE), появившуюся в Microsoft Excel, начиная с 2010 года. По сути, она представляет собой улучшенную версию функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, где первый аргумент задаёт нужную функцию (в нашем случае - количество), а второй как раз позволяет пропускать в вычислениях скрытые строки:
Таким образом, мы получим столбец-индикатор видимости для каждой строки - останется лишь использовать его при дальнейшей обработке данных. Давайте рассмотрим несколько примеров такого применения.
Сценарий 1. Формулы
Например, можно легко вычислить общую стоимость (цена умноженная на количество) только для отфильтрованных товаров, используя функцию СУММПРОИЗВ (SUMPRODUCT), в которую добавить дополнительным множителем созданный столбец Видимость. Нулевые значения в нём обнулят и стоимость для скрытых строк, так что мы в результате увидим сумму произведений только для видимых сделок:
Похожим образом теперь можно использовать созданный столбец и для динамических массивов. С помощью новой функции ФИЛЬТР (FILTER) можно легко отфильтровать только видимые строки по добавленному столбцу статуса и далее уже только их обработать - например, вывести список уникальных товаров из отфильтрованных строк, добавив к формуле функцию УНИК (UNIQUE):
Сценарий 2. Power Query
Ещё один полезный сценарий применения - это выборочная загрузка данных в Power Query (и далее - в Power Pivot, при необходимости). По умолчанию при импорте Power Query не видит форматирования на листе (к которому также относится и скрытие) и всегда тупо загружает всю имеющуюся на листе информацию. В большинстве случаев это правильно, но бывают ситуации, когда хотелось бы загружать в Power Query именно видимые (после фильтрации, скрытия или группировки) строки.
Если добавить к исходным данным столбец с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ, то впоследствии по нему легко отфильтровать нужные строки уже в самом редакторе запросов Power Query:
А если добавить к исходной таблице фильтрацию с помощью срезов, вставив их через вкладку Конструктор - Срез (Design - Slicer), то станет совсем красиво - фильтруя начальные данные срезом и обновляя затем наш запрос, мы будем обрабатывать в Power Query только нужный нам фрагмент исходной таблицы:
Всё это в целом очень похоже на сводную таблицу со срезами, но позволяет реализовать практически любые преобразования и вычисления с исходными данными - даже те, которые в сводных сделать невозможно.
Ссылки по теме
- Вставка в отфильтрованные строки
- Суммирование только видимых ячеек функциями ПРОМЕЖУТОЧНЫЕ.ИТОГИ и АГРЕГАТ
- Быстрое скрытие и отображение ненужных строк и столбцов
Сам бы до такого ни за что не догадался.
P.S. Когда уже книга? Финиш видно?..
))
Николай, просьба добавить данную статью в раздел "Power Query, Pivot, BI" на сайте.
Очень удобно пользоваться данными подборками статей.
а почему начинает странно работать фильтр?
Выбрал 1 позицию, осталось выбранная и последняя.