Содержание:
Если вы не совсем начинающий пользователь, то наверняка уже заметили, что 99% всего в Excel предназначено для работы с вертикальными таблицами, где через столбцы проходят параметры или атрибуты (поля), а также располагается информация об объектах или событиях. в строках. Сводные таблицы, промежуточные итоги, копирование формул двойным кликом – все заточено специально под этот формат данных.
Однако правил без исключений не бывает и с довольно регулярной частотой мне задают вопрос, что делать, если в работе попалась таблица с горизонтальной смысловой направленностью, или таблица, где строки и столбцы имеют одинаковый вес по смыслу:
А если Excel еще умеет сортировать по горизонтали (командой Данные – Сортировка – Параметры – Сортировка столбцов.), то с фильтрацией ситуация хуже — встроенных инструментов для фильтрации столбцов, а не строк в Excel просто нет. Итак, если вы столкнулись с такой задачей, вам придется придумывать обходные пути разной степени сложности.
Способ 1. Новая функция ФИЛЬТР
Если вы используете новую версию Excel 2021 или подписку на Excel 365, вы можете воспользоваться новой функцией. ФИЛЬТР (ФИЛЬТР), который умеет фильтровать исходные данные не только по строкам, но и по столбцам. Для работы данной функции необходим вспомогательный горизонтальный одномерный массив-строка, где каждое значение (ИСТИНА или ЛОЖЬ) определяет, будем ли мы показывать или, наоборот, скрывать следующий столбец таблицы.
Добавим над нашей таблицей следующую строку и напишем в ней статус каждого столбца:
- Допустим, мы хотим всегда отображать первый и последний столбцы (заголовки и итоги), поэтому для них в первой и последней ячейках массива мы устанавливаем значение = TRUE.
- Для остальных столбцов содержимым соответствующих ячеек будет формула, проверяющая нужное нам условие с помощью функций И (И) or OR (OR). Например, что сумма находится в диапазоне от 300 до 500.
После этого останется только воспользоваться функцией ФИЛЬТР чтобы выбрать столбцы, над которыми наш вспомогательный массив имеет значение TRUE:
Аналогичным образом вы можете фильтровать столбцы по заданному списку. В этом случае поможет функция COUNTIF (СЧЁТЕСЛИ), который проверяет количество вхождений имени следующего столбца из заголовка таблицы в список разрешенных:
Способ 2. Сводная таблица вместо привычной
На данный момент в Excel встроена горизонтальная фильтрация по столбцам только в сводных таблицах, поэтому, если нам удастся преобразовать исходную таблицу в сводную, мы сможем использовать эту встроенную функциональность. Для этого наша исходная таблица должна удовлетворять следующим условиям:
- иметь «правильную» однострочную строку заголовка без пустых и объединенных ячеек – иначе построить сводную таблицу не получится;
- не содержать дублей в метках строк и столбцов – в сводке они «свернутся» в список только уникальных значений;
- содержать только числа в диапазоне значений (на пересечении строк и столбцов), потому что сводная таблица обязательно применит к ним какую-то агрегатирующую функцию (сумму, среднее и т. д.), а с текстом это не сработает
Если все эти условия соблюдены, то для того, чтобы построить сводную таблицу, похожую на нашу исходную таблицу, ее (исходную) нужно будет развернуть из кросс-таблицы в плоскую (нормализованную). Самый простой способ сделать это — использовать надстройку Power Query — мощный инструмент преобразования данных, встроенный в Excel с 2016 года.
К ним относятся:
- Преобразуем таблицу в «умную» динамическую команду Главная – Форматировать в виде таблицы (Главная страница — Форматировать как таблицу).
- Загрузка в Power Query с помощью команды Данные – Из таблицы/диапазона (Данные – из таблицы/диапазона).
- Фильтруем строку с итогами (в сводке будут свои итоги).
- Щелкните правой кнопкой мыши заголовок первого столбца и выберите Развернуть другие столбцы (Отменить поворот других столбцов). Все невыделенные столбцы преобразуются в два – имя сотрудника и значение его показателя.
- Фильтрация столбца по суммам, поступившим в столбец Атрибут.
- По полученной плоской (нормализованной) таблице строим сводную таблицу командой На главную — Закрыть и загрузить — Закрыть и загрузить… (Главная страница — Закрыть и загрузить — Закрыть и загрузить в…).
Теперь вы можете использовать возможность фильтрации столбцов, доступных в сводных таблицах – обычные галочки перед названиями и элементами. Сигнатурные фильтры (Фильтры ярлыков) or Фильтры по значению (Фильтры значений):
И конечно, при изменении данных вам нужно будет обновить наш запрос и сводку с помощью сочетания клавиш. Ctrl+другой+F5 или команда Данные – обновить все (Данные — Обновить все).
Способ 3. Макрос в VBA
Все предыдущие методы, как легко заметить, не являются именно фильтрацией — мы не скрываем столбцы в исходном списке, а формируем новую таблицу с заданным набором столбцов из исходной. Если требуется отфильтровать (скрыть) столбцы в исходных данных, то нужен принципиально другой подход, а именно макрос.
Предположим, мы хотим на лету отфильтровать столбцы, в которых имя менеджера в заголовке таблицы удовлетворяет маске, указанной в желтой ячейке А4, например, начинается с буквы «А» (то есть получаем «Анна» и «Артур»). " как результат).
Как и в первом методе, сначала реализуем вспомогательный диапазон-строку, где в каждой ячейке будет проверяться наш критерий по формуле и для видимых и скрытых столбцов будут отображаться логические значения ИСТИНА или ЛОЖЬ соответственно:
Затем давайте добавим простой макрос. Щелкните правой кнопкой мыши вкладку листа и выберите команду. Источник (Исходный код). Скопируйте и вставьте следующий код VBA в открывшееся окно:
Private Sub Worksheet_Change(ByVal Target As Range) Если Target.Address = "$A$4" Тогда для каждой ячейки в диапазоне ("D2:O2") Если ячейка = True Тогда cell.EntireColumn.Hidden = False Иначе cell.EntireColumn.Hidden = Истина Конец, если следующая ячейка Конец, если Конец подпункта Его логика такова:
- В общем это обработчик событий Рабочий лист_Изменить, т.е. этот макрос будет автоматически запускаться при любом изменении любой ячейки на текущем листе.
- Ссылка на измененную ячейку всегда будет находиться в переменной цель.
- Сначала проверяем, что пользователь изменил именно ячейку с критерием (А4) — это делает оператор if.
- Затем начинается цикл Для каждого… для перебора серых ячеек (D2:O2) со значениями индикатора TRUE/FALSE для каждого столбца.
- Если значение следующей серой ячейки ИСТИНА (true), то столбец не скрыт, в противном случае мы его скрываем (свойство Скрытый).
- Функции динамических массивов из Office 365: FILTER, SORT и UNIC.
- Сводная таблица с многострочным заголовком с использованием Power Query
- Что такое макросы, как их создавать и использовать