Горизонтальная фильтрация столбцов в Excel
Если вы не совсем начинающий пользователь, то, должно быть, уже заметили, что на 99% всё в Excel устроено для работы именно с вертикальными таблицами, где по столбцам идут параметры или атрибуты (поля), а в строчках располагается информация об объектах или событиях. Сводные таблицы, промежуточные итоги, копирование формул двойным щелчком - всё заточено именно под такой формат данных.
Однако же, нет правил без исключений и с достаточно регулярной периодичностью меня спрашивают что делать если в работе попалась таблица с горизонтальной смысловой ориентацией, либо таблица, где строки и столбцы имеют одинаковый по смыслу вес:
И если сортировать по горизонтали Excel ещё умеет (командой Данные - Сортировка - Параметры - Сортировать столбцы), то с фильтрацией всё обстоит хуже - встроенных инструментов для фильтрации столбцов, а не строк в Excel просто нет. Так что, если перед вами встала такая задача, придется придумывать обходные пути разной степени сложности.
Способ 1. Новая функция ФИЛЬТР
Если вы работаете на новой версии Excel 2021 или в подписке Excel 365, то можно воспользоваться недавно появившейся функцией ФИЛЬТР (FILTER), умеющей фильтровать исходные данные не только по строчкам, но и по столбцам. Для работы эта функция требует вспомогательный горизонтальный одномерный массив-строку, где каждое значение (ИСТИНА или ЛОЖЬ) определяет показываем ли мы или, наоборот, скрываем очередной столбец в таблице.
Добавим такую строку над нашей таблицей и пропишем в ней статус каждого столбца:
- Допустим, мы всегда хотим отображать первый и последний столбцы (заголовки и итоги), поэтому для них в первой и последней ячейках массива зададим значение =ИСТИНА.
- Для остальных столбцов содержимое соответствующих ячеек будет формулой, которая проверяет нужное нам условие с помощью функций И (AND) или ИЛИ (OR). Например, что итог находится в интервале от 300 до 500.
После этого останется лишь использовать функцию ФИЛЬТР для отбора столбцов, над которыми в нашем вспомогательном массиве есть значение ИСТИНА:
Аналогичным образом можно отфильтровать столбцы и по заданному списку. В этом случае поможет функция СЧЁТЕСЛИ (COUNTIF), проверяющая количество вхождений очередного названия столбца из шапки таблицы в разрешённый список:
Способ 2. Сводная таблица вместо обычной
На текущий момент встроенная горизонтальная фильтрация по столбцам есть в Excel только в сводных таблицах, поэтому если нам удастся преобразовать нашу исходную таблицу в сводную, то можно будет воспользоваться этим встроенным функционалом. Для этого наша исходная таблица должна удовлетворять следующим условиям:
- иметь "правильную" однострочную строку заголовка без пустых и объединенных ячеек - иначе не получится построить сводную таблицу;
- не содержать дубликатов в подписях строк и столбцов - они "схлопнутся" в сводной в список только уникальных значений;
- содержать только числа в области значений (на пересечении строк и столбцов), т.к. сводная таблица обязательно применит к ним какую-то агрегирующую функцию (сумму, среднее и т.д.) и с текстом это не сработает
Если все эти условия выполняются, то для построения сводной, внешне похожей на нашу исходную таблицу, её (исходную) нужно будет развернуть из кросс-таблицы в плоскую (нормализовать). А это проще всего сделать с помощью надстройки Power Query - мощного инструмента преобразования любых данных, встроенного в Excel начиная с 2016-й версии.
А именно:
- Преобразуем таблицу в "умную" динамическую командой Главная - Форматировать как таблицу (Home - Format as Table).
- Загружаем в Power Query командой Данные - Из таблицы/диапазона (Data - From Table/Range).
- Фильтруем строку с итогами (в сводной будут свои итоги).
- Щёлкаем правой кнопкой мыши по заголовку первого столбца и выбираем Отменить свёртывание других столбцов (Unpivot Other Columns). Все невыделенные столбцы преобразуются в два - имя сотрудника и значение его показателя.
- Фильтруем столбец с итогами, который ушёл в колонку Атрибут.
- Строим сводную таблицу по получившейся плоской (нормализованной) таблице командой Главная - Закрыть и загрузить - Закрыть и загрузить в... (Home - Close & Load - Close & Load to...).
Теперь можно пользоваться имеющейся в сводных таблицах возможностью фильтрации столбцов - привычными галочками напротив имён и пунктами Фильтры по подписи (Label Filters) или Фильтры по значению (Value Filters):
И само собой при изменении данных нужно будет обновить наш запрос и сводную сочетанием клавиш Ctrl+Alt+F5 или командой Данные - Обновить всё (Data - Refresh All).
Способ 3. Макрос на VBA
Все предыдущие способы, как легко заметить, представляют собой не совсем фильтрацию - мы не скрываем столбцы в исходном списке, а формируем новую таблицу с заданным набором столбцов из исходной. Если же требуется именно фильтровать (скрывать) столбцы в исходных данных, то нужен принципиально другой подход, а именно - макрос.
Предположим, что мы хотим "на лету" фильтровать столбцы, где имя менеджера в шапке таблицы удовлетворяет заданной в жёлтой ячейке А4 маске, например, начинается с буквы "А" (то бишь получить в результате "Анна" и "Артур").
Как и в первом способе, сначала реализуем вспомогательный диапазон-строку, где в каждой ячейке формулой будет проверяться наш критерий и выводиться логические значения ИСТИНА или ЛОЖЬ для видимых и скрытых столбцов соответственно:
Затем добавим простой макрос. Щёлкните правой кнопкой мыши по ярлычку листа и выберите команду Исходный код (Source code). В открывшееся окно скопируйте и вставьте следующий VBA-код:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$4" Then
For Each cell In Range("D2:O2")
If cell = True Then
cell.EntireColumn.Hidden = False
Else
cell.EntireColumn.Hidden = True
End If
Next cell
End If
End Sub
Логика его работы следующая:
- В общем и целом - это обработчик события Worksheet_Change, т.е. этот макрос будет автоматически запускаться при любом изменении любой ячейки на текущем листе.
- Ссылка на изменённую ячейку всегда будет лежать в переменной Target.
- Сначала мы проверяем, что пользователь изменил именно ячейку с критерием (A4) - это делает оператор if.
- Затем запускается цикл For Each... для перебора серых ячеек (D2:O2) со значениями-индикаторами ИСТИНА / ЛОЖЬ для каждого столбца.
- Если значение очередной серой ячейки равно ИСТИНА (true), то столбец не скрывается, в противном случае - скрываем его (свойство Hidden).
Ссылки по теме
- Функции динамических массивов из Office 365: ФИЛЬТР, СОРТ и УНИК
- Сводная по таблице с многострочной шапкой с помощью Power Query
- Что такое макросы, как их создавать и использовать
Огромное спасибо за Макрос на VBA
Скажите как скорректировать VBA-код так чтобы он отслеживал 2 ячейки
If Target.Address = "$F$2" Or Target.Address = "$F$3" Then
вроде работает.
Private Sub Worksheet_Change(ByVal Target As Range)
В моем случае target A1, диапазон ИСТИНА-ЛОЖЬ D1:GP1
Мне нужно скрыть столбцы по таргету A1 (формула в диапазоне D1:GP1 =ЕСЛИ(D2=$A$1;ЛОЖЬ;ИСТИНА)
Соотвественно, мои замены в макросе:
If Target.Address = "$A$1" Then
For Each cell In Range("D1:GP1"
Что я делаю не так?
Опишите, пожалуйста, горизонтальную сортировку через Power Query.
Способ 3 работает в стоке с некоторыми ошибками.
1. заметил, если раскрыть скрытые при помощи макроса столбцы, то макрос перестаёт работать, для юзера это будет как минимум замешательство и реакция типа "задолбали ваши макросы"
2. Добавил 2 и 3 уровень фильтра в формулу но макрос работает некорректно, реагирует только на первый уровень фильтра. Но в какой-то момент происходит магия и фильтр начинает работать. Но опять же после сработки фильтра меняю 2 или третий уровень и фильтр не отрабатывает. Возможно кто ни будь сталкивался с подобным? Подскажите пожалуйста ка решить эту задачу.
слегка переделал формулу :