有没有办法在Excel中查看哪些过滤器是活动的，而不仅仅是漏斗图标？ [英] Is there a way to see which filters are active in Excel, other than just the funnel icons?

问题描述

这个问题实际上是Excel GUI，而不是Excel编程本身。但是，如果在GUI中不可用，如果有VBA解决方案（尽管我基本上有0 VBA知识/经验），我会很好奇。

This question is actually for the Excel GUI, rather than Excel "programming", per se. However, if this is unavailable in the GUI, I would be curious if there's a VBA solution (although, I have basically 0 VBA knowledge/experience).

有没有办法在Excel中查看哪些过滤器是活动的，而不仅仅是查看渠道图标？如我附带的屏幕截图所示，一些电子表格可以具有从可见屏幕延伸的列，因此可以容易错过渠道图标，指示有效的过滤器。 （另外，我认为忽略图标很容易，即使在几列之间）。

Is there a way to see which filters are active in Excel, other than just by looking at the funnel icons? As shown in my attached screenshot, some spreadsheets can have columns that extend off the visible screen, so it can be easy to miss the funnel icon indicating an active filter. (Additionally, I think it can be pretty easy to overlook the icon, even amidst only a few columns.)

理想情况下，会有某种列表显示哪些列/头被主动过滤。

Ideally, there would be some kind of list showing which columns/headers are actively filtered.

谢谢。

推荐答案

如果您只需要一个简单的列表，那么以下VBA代码可能就足够了：

If you merely want a simple list of the columns where a filter is applied then the following VBA code may suffice:

Option Explicit

Function FilterCrit() As String

Dim i As Long
Dim ws As Worksheet
Dim Filter As String

'Application.Volatile

Set ws = ThisWorkbook.Worksheets(1)

If Not ws.FilterMode Then
    FilterCrit = "not filtered"
    Exit Function
End If
For i = 1 To ws.AutoFilter.Filters.Count
    If ws.AutoFilter.Filters(i).On Then
        FilterCrit = FilterCrit & "Filter on column " & i & Chr(10)
    End If
Next i

End Function

这将重复列，如果过滤器应用于任何这些列，那么它将被列出。

This will iterate the columns and if a filter is applied on any of these columns then it will be listed.

默认情况下，所有UDF 用户定义的函数不是易失性的，因此不会自动重新计算。但是，您可以迫使他们自动重新计算 Application.Volatile 。但是强烈建议不要使用此选项，因为它可能会严重影响您的Excel文件。建议使用其他解决方案： Application.Volatile可自动更新UDF

By default all UDFs user defined functions are not volatile and therefore do not automatically recalculate. Yet, you can force them to automatically recalculate with Application.Volatile. But it is highly recommend not to use this option as it can severely slow down your Excel file. Alternative solutions are recommended here: Alternative to Application.Volatile to auto update UDF

这篇关于有没有办法在Excel中查看哪些过滤器是活动的，而不仅仅是漏斗图标？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！