Горизонтальная фильтрация столбцов в Excel

Если вы не совсем начинающий пользователь, то, должно быть, уже заметили, что на 99% всё в Excel устроено для работы именно с вертикальными таблицами, где по столбцам идут параметры или атрибуты (поля), а в строчках располагается информация об объектах или событиях. Сводные таблицы, промежуточные итоги, копирование формул двойным щелчком - всё заточено именно под такой формат данных.

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

Исходные данные

И если сортировать по горизонтали Excel ещё умеет (командой Данные - Сортировка - Параметры - Сортировать столбцы), то с фильтрацией всё обстоит хуже - встроенных инструментов для фильтрации столбцов, а не строк в Excel просто нет. Так что, если перед вами встала такая задача, придется придумывать обходные пути разной степени сложности.

Способ 1. Новая функция ФИЛЬТР

Если вы работаете на новой версии Excel 2021 или в подписке Excel 365, то можно воспользоваться недавно появившейся функцией ФИЛЬТР (FILTER), умеющей фильтровать исходные данные не только по строчкам, но и по столбцам. Для работы эта функция требует вспомогательный горизонтальный одномерный массив-строку, где каждое значение (ИСТИНА или ЛОЖЬ) определяет показываем ли мы или, наоборот, скрываем очередной столбец в таблице.

Добавим такую строку над нашей таблицей и пропишем в ней статус каждого столбца:

Строка проверки условий

  • Допустим, мы всегда хотим отображать первый и последний столбцы (заголовки и итоги), поэтому для них в первой и последней ячейках массива зададим значение =ИСТИНА.
  • Для остальных столбцов содержимое соответствующих ячеек будет формулой, которая проверяет нужное нам условие с помощью функций И (AND) или ИЛИ (OR). Например, что итог находится в интервале от 300 до 500.

После этого останется лишь использовать функцию ФИЛЬТР для отбора столбцов, над которыми в нашем вспомогательном массиве есть значение ИСТИНА:

Функция ФИЛЬТР для горизонтальной фильтрации

Аналогичным образом можно отфильтровать столбцы и по заданному списку. В этом случае поможет функция СЧЁТЕСЛИ (COUNTIF), проверяющая количество вхождений очередного названия столбца из шапки таблицы в разрешённый список:

Фильтрация столбцов по списку

Способ 2. Сводная таблица вместо обычной

На текущий момент встроенная горизонтальная фильтрация по столбцам есть в Excel только в сводных таблицах, поэтому если нам удастся преобразовать нашу исходную таблицу в сводную, то можно будет воспользоваться этим встроенным функционалом. Для этого наша исходная таблица должна удовлетворять следующим условиям:

  • иметь "правильную" однострочную строку заголовка без пустых и объединенных ячеек - иначе не получится построить сводную таблицу;
  • не содержать дубликатов в подписях строк и столбцов - они "схлопнутся" в сводной в список только уникальных значений;
  • содержать только числа в области значений (на пересечении строк и столбцов), т.к. сводная таблица обязательно применит к ним какую-то агрегирующую функцию (сумму, среднее и т.д.) и с текстом это не сработает

Если все эти условия выполняются, то для построения сводной, внешне похожей на нашу исходную таблицу, её (исходную) нужно будет развернуть из кросс-таблицы в плоскую (нормализовать). А это проще всего сделать с помощью надстройки Power Query - мощного инструмента преобразования любых данных, встроенного в Excel начиная с 2016-й версии. 

А именно:

  1. Преобразуем таблицу в "умную" динамическую командой Главная - Форматировать как таблицу (Home - Format as Table).
  2. Загружаем в Power Query командой Данные - Из таблицы/диапазона (Data - From Table/Range).
  3. Фильтруем строку с итогами (в сводной будут свои итоги).
  4. Щёлкаем правой кнопкой мыши по заголовку первого столбца и выбираем Отменить свёртывание других столбцов (Unpivot Other Columns). Все невыделенные столбцы преобразуются в два - имя сотрудника и значение его показателя.
  5. Фильтруем столбец с итогами, который ушёл в колонку Атрибут.
  6. Строим сводную таблицу по получившейся плоской (нормализованной) таблице командой Главная - Закрыть и загрузить - Закрыть и загрузить в... (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).

Ссылки по теме

 


Наверх