Как показать отфильтрованные столбцы в строке состояния в Excel

Когда у вас есть большая таблица с множеством столбцов, которые не отфильтрованы, иногда трудно сказать, какие столбцы фильтруются, а какие — нет. Конечно, можно прокручивать столбцы, всматриваясь в каждый выпадающий список, но это очень долго.

Этот макрос помогает показать все фильтры в строке состояния. Панель состояния находится в нижней части окна Excel. 

Этот макрос перебирает поля в наборе данных автофильтра. После того, как цикл завершен, мы проходим заключительную строку к свойству StatusBar. 

1. Шаг 1 объявляет четыре переменные. AF является переменной автофильтр, которая используется для манипулирования объектом AutoFilter. TargetField переменная строка, которую мы используем, чтобы держать заголовки столбцов с фильтрами. strOutput переменная строка, которую мы используем для построения окончательного текста, который идет в строке состояния. И, наконец, переменная i служит как простой счетчик, что позволяет нам выполнять итерацию по полям в нашем Автофильтре. 

2. Шаг 2 проверяет свойство AutoFilterMode, чтобы увидеть, есть ли на листе фильтры. Если нет, то мы устанавливаем свойство StatusBar в значение False. Это имеет эффект очистки строки состояния. Затем мы выходим из процедуры. 

3. Шаг 3 устанавливает переменную AF к фильтру на активном листе. Затем мы устанавливаем наш счетчик от 1 до максимального количества столбцов в диапазоне AutoFiltered. Объект AutoFilter отслеживает столбцы с номерами индексов. Столбец 1 индекс 1; столбец 2 - индекс 2 и т.д. Идея заключается в том, что цикл проходит каждый столбец в фильтре, используя переменную (I), как номер индекса. 

4. Шаг 4 проверяет статус AF.Filters объекта для каждого (I) — порядковый номер столбца. Если Автофильтр фильтрует столбец, то статус этого столбца Вкл. Если столбец отфильтрован, то захватываем имя поля в TargetField. Мы на самом деле можем получить имя поля с помощью ссылки на диапазон нашего объекта AF Автофильтр. С помощью этого диапазона, мы можем использовать элемент ячейки, чтобы определить имя поля. 

Ячейка (1,1) фиксирует значение в первой строке, первом столбце. Ячейка (1,2) фиксирует значение в первой строке, втором столбце и т.д. Как видите на шаге 4 закодирована строка 1 и используется переменная (I), чтобы указать индекс столбца. Это означает, что, как Макрос проводит итерацию через столбцы, она всегда захватывает значение в строке в качестве имени TargetField. 

После того, как у нас есть имя TargetField, мы можем передать эту информацию (strOutput в нашем случае). strOutput просто находит имена и сцепляет их в удобном виде для чтения текстовой строки. 

5. Шаг 5 сначала проверяет, есть ли что-то в строке strOutput. Если strOutput пуст, это означает, что макрос не нашел ни одного столбца в фильтре. В этом случае Шаг 5 просто устанавливает свойство StatusBar False, передавая управление обратно Excel. Если strOutput не пуст, шаг 5 устанавливает свойство StatusBar равным некоторому вспомогательному тексту вместе с нашей строкой strOutput. 

В идеале нужно, чтобы этот макрос запускался каждый раз, когда поле фильтруется. Однако Excel не имеет событие OnAutoFilter. Ближе всего к этому является событие Worksheet_Calculate. Фильтры сами по себе не вычисляют ничего, так что вам нужно ввести «непостоянную» функцию на листе, которая содержит ваши отфильтрованные данные. Непостоянная функция та, которая делает перерасчет при внесении любых изменений на рабочем листе. Можно использовать функцию ТДАТА(). Функция ТДАТА является непостоянной функцией, которая возвращает дату и время. При этом ее нужно обязательно пересчитывать каждый раз, когда фильтр изменяется. Поместите ТДАТА в любом месте на листе (введя = ТДАТА() в любой клетке).

Затем скопируйте и вставьте макрос в окно Worksheet_Calculate

1. Активируйте редактор Visual Basic, нажав ALT + F11 на клавиатуре.

2. В окне проекта, найдите свой проект / рабочую книгу и нажмите на знак плюс рядом с ней, чтобы увидеть все листы.

3. Нажмите на лист, из которого вы хотите вызвать код.

4. В верхнем левом меню выберите Worksheet, а в правом событие Calculate.

5. Введите или вставьте код нашего макроса, а ниже добавьте следующие два кода:

Кроме того, можно добавить этот кусок кода в книгу BeforeClose : 

Событие Worksheet_Deactivate очищает строку состояния при переходе на другой лист или книги. Это позволит избежать путаницы при перемещении между листами. Событие Worksheet_Activate запускает макрос в Worksheet_Calculate и возвращает показатели Строки состояния при переходе обратно к листу с фильтром. Событие Workbook_BeforeClose очищает строку состояния при закрытии книги. Это позволит избежать путаницы при перемещении между книгами.