除了漏斗图标之外，有没有办法查看 Excel 中哪些过滤器处于活动状态?

问题描述

这个问题实际上是针对 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 中哪些过滤器处于活动状态?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！