Показать в заголовке критерий, по которому применен фильтр

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Показать в заголовке критерий, по которому применен фильтр (Форматирование)

zamboga

Дата: Четверг, 15.08.2013, 14:59 | Сообщение № 1

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Есть таблица с ~20 столбцами, при работе с которой я активно использую фильтры в 5-7 разных столбцах.

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

Это очень неудобно и трудоемко.

есть ли способ показать в заголовке критерий, по которому применен фильтр? или, например, в дополнительных ячейках в самом верху файла отображать текущие критерии фильтра?

Ответить

Michael_S

Дата: Четверг, 15.08.2013, 15:15 | Сообщение № 2

Группа: Друзья

Ранг: Старожил

Сообщений: 2012

Репутация: 373 ±

Замечаний: 0% ±

Excel2016

Формулами - нет. Макросом - возможно.
В заголовке, наверно, бессмысленно.

Ответить

KuklP

Дата: Четверг, 15.08.2013, 15:15 | Сообщение № 3

Группа: Проверенные

Ранг: Старожил

Сообщений: 2369

Репутация: 486 ±

Замечаний: 0% ±

2003-2010

Цитата (zamboga, 15.08.2013 в 14:59, в сообщении №1)

Это очень неудобно и трудоемко.

[offtop]Офигеть! Трудоемко на 16 этаж поддон кирпича в руках перенести.[/offtop]

Ну с НДС и мы чего-то стoим! kuklp60@gmail.com
WM Z206653985942, R334086032478, U238399322728

Сообщение отредактировал KuklP - Четверг, 15.08.2013, 15:15

Ответить

zamboga

Дата: Четверг, 15.08.2013, 15:42 | Сообщение № 4

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Цитата (Michael_S, 15.08.2013 в 15:15, в сообщении №2)

Макросом - возможно

можете помочь? в VBA я совсем не силен=(

Ответить

SkyPro

Дата: Четверг, 15.08.2013, 16:23 | Сообщение № 5

Группа: Друзья

Ранг: Старожил

Сообщений: 1206

Репутация: 255 ±

Замечаний: 0% ±

2010

Переделал макрос с этого форума. Автор KoGG.
Нужно добавить верхнюю строку с форматом ячеек "текст".
[vba]

Код

Sub Опросить_автофильтр()
Dim i%, j%
With ActiveSheet
     If .AutoFilterMode Then
         ' Активная ячейка должна находиться в диапазоне фильтра
         .AutoFilter.Range.Cells(2, 1).Select
         i = .AutoFilter.Filters.Count
         ReDim onoffstate(i) As Boolean, Crit1(i) As String, Crit2(i) As String, Oper(i) As Integer
         Dim MassivZnacheniy As Variant
         For i = 1 To .AutoFilter.Filters.Count
             With .AutoFilter.Filters(i)
                 onoffstate(i) = .On
                 If .On Then
                      Oper(i) = .Operator
                      Crit2(i) = ""
                      If .Operator <> xlFilterValues Then Crit1(i) = .Criteria1
                      Select Case .Operator
                         Case xlAnd              ' 1 ' Logical AND of Criteria1 and Criteria2.
                             Crit2(i) = .Criteria2
                         Case xlBottom10Items    '=4 'Lowest-valued items displayed (number of items specified in Criteria1).
                         Case xlBottom10Percent  '=6 'Lowest-valued items displayed (percentage specified in Criteria1).
                         Case xlFilterCellColor  '=8 'Color of the cell
                         Case xlFilterDynamic    '=11'Dynamic filter
                         Case xlFilterFontColor  '=9 'Color of the font
                         Case xlFilterIcon       '=10'Filter icon
                         Case xlFilterValues     '=7 'Filter values
                             MassivZnacheniy = .Criteria1
                             Debug.Print "Фильтр "; i; onoffstate(i); " Массив значений: ";
                             For j = 1 To UBound(MassivZnacheniy)
                    Debug.Print MassivZnacheniy(j);
                             Next j
                             Exit Sub
                         Case xlOr               '=2 'Logical OR of Criteria1 or Criteria2.
                             Crit2(i) = .Criteria2
                         Case xlTop10Items       '=3 'Highest-valued items displayed (number of items specified in Criteria1).
                         Case xlTop10Percent     '=5' Highest-valued items displayed (percentage specified in Criteria1).
                     End Select
                 End If
                 If onoffstate(i) = True Then
                 Cells(1, i).Formula = Crit1(i) & Chr(10) & Crit2(i)
                 End If
             End With
         Next
     End If
End With
End Sub

[/vba]
Я лишь добавил вывод параметра автофильтра в первую строку.

Переделал макрос с этого форума. Автор KoGG.
Нужно добавить верхнюю строку с форматом ячеек "текст".
[vba]

Код

[/vba]
Я лишь добавил вывод параметра автофильтра в первую строку.SkyPro

skypro1111@gmail.com

Ответить

СообщениеПеределал макрос с этого форума. Автор KoGG.
Нужно добавить верхнюю строку с форматом ячеек "текст".
[vba]

Код

[/vba]
Я лишь добавил вывод параметра автофильтра в первую строку.

Автор - SkyPro
Дата добавления - 15.08.2013 в 16:23

zamboga

Дата: Четверг, 15.08.2013, 17:52 | Сообщение № 6

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Работает, спасибо огромное!

Ответить

zamboga

Дата: Четверг, 15.08.2013, 20:13 | Сообщение № 7

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

К сожалению, при изменении фильтра данные не обновляются, необходимо запускать скрипт повторно.

Что нужно добавить в код, что бы он выполнялся с частотой, например, раз в секунду? там же все равно цикл, наверно сработает такой способ? Или такой способ не позволит мне запускать другие макросы и работать в excel? т.к. excel будет занят прогоном данного макроса?

Или может можно в этот код что-то добавить, что бы он автоматически запускался после любого изменения состояния фильтра (что-то добавил, изменил, удалил в фильтре)?

Ответить

zamboga

Дата: Четверг, 15.08.2013, 20:20 | Сообщение № 8

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

и при очистке фильтра данные о состоянии не обновляются даже после запуска скрипта

Ответить

SkyPro

Дата: Пятница, 16.08.2013, 10:12 | Сообщение № 9

Группа: Друзья

Ранг: Старожил

Сообщений: 1206

Репутация: 255 ±

Замечаний: 0% ±

2010

[vba]

Код

Sub Опросить_автофильтр()
Dim i%, j%
With ActiveSheet
     If .AutoFilterMode Then
         ' Активная ячейка должна находиться в диапазоне фильтра
         .AutoFilter.Range.Cells(2, 1).Select
         i = .AutoFilter.Filters.Count
         ReDim onoffstate(i) As Boolean, Crit1(i) As String, Crit2(i) As String, Oper(i) As Integer
         Dim MassivZnacheniy As Variant
         For i = 1 To .AutoFilter.Filters.Count
             With .AutoFilter.Filters(i)
                 onoffstate(i) = .On
                 If .On Then
                     Oper(i) = .Operator
                     Crit2(i) = ""
                     If .Operator <> xlFilterValues Then Crit1(i) = .Criteria1
                     Select Case .Operator
                         Case xlAnd              ' 1 ' Logical AND of Criteria1 and Criteria2.
                             Crit2(i) = .Criteria2
                         Case xlBottom10Items    '=4 'Lowest-valued items displayed (number of items specified in Criteria1).
                         Case xlBottom10Percent  '=6 'Lowest-valued items displayed (percentage specified in Criteria1).
                         Case xlFilterCellColor  '=8 'Color of the cell
                         Case xlFilterDynamic    '=11'Dynamic filter
                         Case xlFilterFontColor  '=9 'Color of the font
                         Case xlFilterIcon       '=10'Filter icon
                         Case xlFilterValues     '=7 'Filter values
                             MassivZnacheniy = .Criteria1
                             Debug.Print "Фильтр "; i; onoffstate(i); " Массив значений: ";
                             For j = 1 To UBound(MassivZnacheniy)
                     Debug.Print MassivZnacheniy(j);
                             Next j
                             Exit Sub
                         Case xlOr               '=2 'Logical OR of Criteria1 or Criteria2.
                             Crit2(i) = .Criteria2
                         Case xlTop10Items       '=3 'Highest-valued items displayed (number of items specified in Criteria1).
                         Case xlTop10Percent     '=5' Highest-valued items displayed (percentage specified in Criteria1).
                     End Select
                 End If
                 If onoffstate(i) = True Then
                 Cells(1, i).Formula = Crit1(i) & Chr(10) & Crit2(i)
                 End If
                 If onoffstate(i) = False Then
                 Cells(1, i).Formula = ""
                 End If
             End With
         Next
     End If
End With
End Sub

[/vba]
Я не знаю, как повесить этот макрос на событие смены фильтра. Так что запускайте каждый раз вручную, или подставьте запуск макроса на любое событие книги\листа (в модуле книги или листа).

[vba]

Код

Sub Опросить_автофильтр()
Dim i%, j%
With ActiveSheet
     If .AutoFilterMode Then
         ' Активная ячейка должна находиться в диапазоне фильтра
         .AutoFilter.Range.Cells(2, 1).Select
         i = .AutoFilter.Filters.Count
         ReDim onoffstate(i) As Boolean, Crit1(i) As String, Crit2(i) As String, Oper(i) As Integer
         Dim MassivZnacheniy As Variant
         For i = 1 To .AutoFilter.Filters.Count
             With .AutoFilter.Filters(i)
                 onoffstate(i) = .On
                 If .On Then
                     Oper(i) = .Operator
                     Crit2(i) = ""
                     If .Operator <> xlFilterValues Then Crit1(i) = .Criteria1
                     Select Case .Operator
                         Case xlAnd              ' 1 ' Logical AND of Criteria1 and Criteria2.
                             Crit2(i) = .Criteria2
                         Case xlBottom10Items    '=4 'Lowest-valued items displayed (number of items specified in Criteria1).
                         Case xlBottom10Percent  '=6 'Lowest-valued items displayed (percentage specified in Criteria1).
                         Case xlFilterCellColor  '=8 'Color of the cell
                         Case xlFilterDynamic    '=11'Dynamic filter
                         Case xlFilterFontColor  '=9 'Color of the font
                         Case xlFilterIcon       '=10'Filter icon
                         Case xlFilterValues     '=7 'Filter values
                             MassivZnacheniy = .Criteria1
                             Debug.Print "Фильтр "; i; onoffstate(i); " Массив значений: ";
                             For j = 1 To UBound(MassivZnacheniy)
                     Debug.Print MassivZnacheniy(j);
                             Next j
                             Exit Sub
                         Case xlOr               '=2 'Logical OR of Criteria1 or Criteria2.
                             Crit2(i) = .Criteria2
                         Case xlTop10Items       '=3 'Highest-valued items displayed (number of items specified in Criteria1).
                         Case xlTop10Percent     '=5' Highest-valued items displayed (percentage specified in Criteria1).
                     End Select
                 End If
                 If onoffstate(i) = True Then
                 Cells(1, i).Formula = Crit1(i) & Chr(10) & Crit2(i)
                 End If
                 If onoffstate(i) = False Then
                 Cells(1, i).Formula = ""
                 End If
             End With
         Next
     End If
End With
End Sub

skypro1111@gmail.com

Ответить

Сообщение[vba]

Код

Sub Опросить_автофильтр()
Dim i%, j%
With ActiveSheet
     If .AutoFilterMode Then
         ' Активная ячейка должна находиться в диапазоне фильтра
         .AutoFilter.Range.Cells(2, 1).Select
         i = .AutoFilter.Filters.Count
         ReDim onoffstate(i) As Boolean, Crit1(i) As String, Crit2(i) As String, Oper(i) As Integer
         Dim MassivZnacheniy As Variant
         For i = 1 To .AutoFilter.Filters.Count
             With .AutoFilter.Filters(i)
                 onoffstate(i) = .On
                 If .On Then
                     Oper(i) = .Operator
                     Crit2(i) = ""
                     If .Operator <> xlFilterValues Then Crit1(i) = .Criteria1
                     Select Case .Operator
                         Case xlAnd              ' 1 ' Logical AND of Criteria1 and Criteria2.
                             Crit2(i) = .Criteria2
                         Case xlBottom10Items    '=4 'Lowest-valued items displayed (number of items specified in Criteria1).
                         Case xlBottom10Percent  '=6 'Lowest-valued items displayed (percentage specified in Criteria1).
                         Case xlFilterCellColor  '=8 'Color of the cell
                         Case xlFilterDynamic    '=11'Dynamic filter
                         Case xlFilterFontColor  '=9 'Color of the font
                         Case xlFilterIcon       '=10'Filter icon
                         Case xlFilterValues     '=7 'Filter values
                             MassivZnacheniy = .Criteria1
                             Debug.Print "Фильтр "; i; onoffstate(i); " Массив значений: ";
                             For j = 1 To UBound(MassivZnacheniy)
                     Debug.Print MassivZnacheniy(j);
                             Next j
                             Exit Sub
                         Case xlOr               '=2 'Logical OR of Criteria1 or Criteria2.
                             Crit2(i) = .Criteria2
                         Case xlTop10Items       '=3 'Highest-valued items displayed (number of items specified in Criteria1).
                         Case xlTop10Percent     '=5' Highest-valued items displayed (percentage specified in Criteria1).
                     End Select
                 End If
                 If onoffstate(i) = True Then
                 Cells(1, i).Formula = Crit1(i) & Chr(10) & Crit2(i)
                 End If
                 If onoffstate(i) = False Then
                 Cells(1, i).Formula = ""
                 End If
             End With
         Next
     End If
End With
End Sub

Автор - SkyPro
Дата добавления - 16.08.2013 в 10:12

Poltava

Дата: Пятница, 16.08.2013, 10:56 | Сообщение № 10

Группа: Друзья

Ранг: Форумчанин

Сообщений: 232

Репутация: 50 ±

Замечаний: 0% ±

Вот нашел тему по этому поводу на соседнем форуме Событие автофильтра как отследить вот пример из той темы

К сообщению приложен файл: AutoFilterEvent.zip (9.4 Kb)

Ответить

zamboga

Дата: Пятница, 16.08.2013, 16:04 | Сообщение № 11

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Коллеги, спасибо за помощь!

Цитата (SkyPro, 16.08.2013 в 10:12, в сообщении №9)

подставьте запуск макроса на любое событие книги\листа (в модуле книги или листа)

Цитата (_Boroda_, 16.08.2013 в 10:27, в сообщении №10)

макрос можно повесить на изменение ячейки, в которой мы предварительно напишем формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3,...)

я так не умею, к сожалению, но скрипт, присланный Poltava, как раз так и умеет, как я понял. Но подружить два скрипта я не сумел.

Цитата (_Boroda_, 16.08.2013 в 10:27, в сообщении №10)

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

вот она (оставил только один лист и заменил все формулы на значения):
http://yadi.sk/d/YHHoRtSB7zCga

Цитата (Poltava, 16.08.2013 в 10:56, в сообщении №11)

нашел тему по этому поводу на соседнем форуме

К сообщению приложен файл: AutoFilterEvent.zip(9Kb)

Оригинал работает.

Попробовал прикрутить к одному своему листу в одном своем файле (он же по ссылке выше).
Планировал заменить в коде строку [vba]

Код

MsgBox "Выполнение нашего макроса", , ""

[/vba] на код, предложенный SkyPro. Но пока так не работает.

Код AutoFilterEvent, как я обнаружил, "зашит" в 3-х местах: Лист1, Module1, ThisWorkbook.

Что сделал:
Пробовал копировать код из 3х мест в одно в свой файл в новый Module -- MsgBox не выскакивает (значит, состояние не отлавливает).
Попробовал скопировать код в свой файл в 3 такие же разные места Лист11, Module1, ЭтаКнига --- сразу ругается на ошибку в исходном коде AutoFilterEvent.
Что не так, не пойму.

Можно, конечно, сделать наоборот: из моего файла скопировать листы книги в скрипт AutoFilterEvent -- но листов много, и книга далеко не одна, где мне нужно видеть состояние автофильтра, так что это не самый хороший способ.

Помогите, плз, подружить скрипты, выложенные SkyPro из Сообщение № 9 и скрипт, выложенный Poltava из Сообщение № 11, в какой-нибудь единый код, который я уже воткну в нужные мне файлы или в "личную книгу макросов"

Коллеги, спасибо за помощь!

Цитата (SkyPro, 16.08.2013 в 10:12, в сообщении №9)

подставьте запуск макроса на любое событие книги\листа (в модуле книги или листа)

Цитата (_Boroda_, 16.08.2013 в 10:27, в сообщении №10)

вот она (оставил только один лист и заменил все формулы на значения):
http://yadi.sk/d/YHHoRtSB7zCga

Цитата (Poltava, 16.08.2013 в 10:56, в сообщении №11)

нашел тему по этому поводу на соседнем форуме

К сообщению приложен файл: AutoFilterEvent.zip(9Kb)

Код

MsgBox "Выполнение нашего макроса", , ""

Сообщение отредактировал zamboga - Пятница, 16.08.2013, 16:07

Ответить

СообщениеКоллеги, спасибо за помощь!

Цитата (SkyPro, 16.08.2013 в 10:12, в сообщении №9)

подставьте запуск макроса на любое событие книги\листа (в модуле книги или листа)

Цитата (_Boroda_, 16.08.2013 в 10:27, в сообщении №10)

вот она (оставил только один лист и заменил все формулы на значения):
http://yadi.sk/d/YHHoRtSB7zCga

Цитата (Poltava, 16.08.2013 в 10:56, в сообщении №11)

нашел тему по этому поводу на соседнем форуме

К сообщению приложен файл: AutoFilterEvent.zip(9Kb)

Код

MsgBox "Выполнение нашего макроса", , ""

Автор - zamboga
Дата добавления - 16.08.2013 в 16:04

SkyPro

Дата: Пятница, 16.08.2013, 16:26 | Сообщение № 12

Группа: Друзья

Ранг: Старожил

Сообщений: 1206

Репутация: 255 ±

Замечаний: 0% ±

2010

Я думаю проще всего будет повесить макрос на сочитание клавиш. И запускать каждій раз, когда нужно посмотреть критерии.

skypro1111@gmail.com

Ответить

zamboga

Дата: Пятница, 16.08.2013, 17:51 | Сообщение № 13

Группа: Пользователи

Ранг: Новичок

Сообщений: 17

Репутация: 0 ±

Замечаний: 0% ±

Excel 2013

Цитата (SkyPro, 16.08.2013 в 16:26, в сообщении №13)

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

Кроме того, AutoFilterEvent.zip отлично справляется, перехватывая любое изменение фильтра (выводя диалоговое окно, вместо которого можно повесить "ваш" скрипт).

Проблема в том, что я не могу корректно скопировать скрипт из файла AutoFilterEvent.zip в свой файл (или в свою базу макросов).

Сообщение отредактировал zamboga - Пятница, 16.08.2013, 17:53

Ответить

Poltava

Дата: Пятница, 16.08.2013, 18:38 | Сообщение № 14

Группа: Друзья

Ранг: Форумчанин

Сообщений: 232

Репутация: 50 ±

Замечаний: 0% ±

Цитата

в идеале -- что бы даже поле для ввода значений фильтра всегда было доступно без лишнего движения

С этого нужно было начинать! тогда вам нужно копать в другом направлении. Посмотрите эту тему Удобный автофильтр это один из вариантов, встречал еще несколько подобных решений но быстро не нашел, нужно покопаться в своих архивах но это только после выходных.

Если говорить о состыковке макросов то код из AutoFilterEvent должен находиться в аналогичных модулях вашей книги, а вместо месенж бокса просто напишите Call Опросить_автофильтр предварительно скопировав процедуру Опросить_автофильтр в модуль листа.
Но макрос требует доработки потому как рассчитан на отслеживания изменения только в одной колонке! то есть его все равно нужно допилить, а у меня пока нет такой возможности.

Сообщение отредактировал Poltava - Пятница, 16.08.2013, 18:40

Ответить

Страница 1 из 1
1