单击显示单元格内容_单击一个单元格以筛选Excel列

最新推荐文章于 2022-10-28 09:22:42 发布

culiao2169

最新推荐文章于 2022-10-28 09:22:42 发布

阅读量697

点赞数

文章标签： java python excel linux mysql

原文链接：https://contexturesblog.com/archives/2013/09/17/click-a-cell-to-filter-excel-column/

版权

单击显示单元格内容

Here is a fancy filter trick, if you frequently have to filter your Excel tables. Click on a cell in this Excel table, and the column is automatically filtered for that item. In the worksheet shown below, columns B and C have already been filtered, and when I click on Pen Set, column D will only show that item.

如果您经常不得不过滤Excel表，这是一个花哨的过滤技巧。单击此Excel表中的单元格，该列将自动进行过滤。在下面显示的工作表中，B和C列已被过滤，当我单击笔设置时，D列将仅显示该项目。

。

清除筛选 (Clear the Filter)

To see all the items in a column again, just click on the heading cell, and the filter for that field is cleared.

要再次查看列中的所有项目，只需单击标题单元格，该字段的过滤器即被清除。

FilterClick示例文件 (FilterClick Sample File)

This filtering trick is accomplished with a bit of programming. There is an event procedure – Worksheet_SelectionChange – that automatically runs when you select a cell on the worksheet .

通过一些编程即可完成此过滤技巧。有一个事件过程– Worksheet_SelectionChange –在您选择工作表上的单元格时会自动运行。

You can turn the FilterClick feature on or off, by clicking on a cell that’s named FilterStatus. When you click on that cell, its value automatically toggles between On and Off.

您可以通过单击名为FilterStatus的单元格来打开或关闭FilterClick功能。当您单击该单元格时，其值会自动在“开”和“关”之间切换。

The SelectionChange procedure checks the value in that cell, and only filters the column if the FilterStatus cell says “On”.

SelectionChange过程将检查该单元格中的值，并且仅在FilterStatus单元格显示“开”时才过滤该列。

使用Excel表的更新 (Update for Working With Excel Tables)

I created the sample file a few years ago, and have just updated it to include a worksheet that has the data in a formatted Excel table. The code is almost identical – only one line is different if you’re using a table.

几年前，我创建了示例文件，并对其进行了更新，以包括一个工作表，该工作表的数据位于格式化的Excel表中。代码几乎完全相同–如果您使用表格，则只有一行不同。

In the sample code shown below, there are two versions of the line that sets the range rngF. For formatted tables, use the first line, and for non-table AutoFilters, use the second line.

在下面显示的示例代码中，有两个版本的行设置范围rngF。对于格式化的表格，请使用第一行；对于非表格自动筛选器，请使用第二行。

Set rngF = ActiveSheet.ListObjects(1).Range 'for tables
为表设置rngF = ActiveSheet.ListObjects(1).Range'
'Set rngF = ActiveSheet.AutoFilter.Range 'for AutoFilter ranges
'设置rngF = ActiveSheet.AutoFilter.Range'用于自动过滤范围

Type an apostrophe at the start of the line that you don’t need, and remove the apostrophe at the start of the line that you do need.

在不需要的行的开头键入撇号，并在不需要的行的开头删除撇号。

You can see more examples of List AutoFilter VBA here.

您可以在此处查看List AutoFilter VBA的更多示例。

FilterClick代码 (The FilterClick Code)

Here is the code, and you can copy it to the worksheet module for the sheet that your list is on. Remember to fix the Set rngF lines, so one has an apostrophe, and one doesn’t – or delete the line that you don’t need.

这是代码，您可以将其复制到列表所在的工作表的工作表模块中。请记住要修复Set rngF行，这样一来就带有撇号，而一个就没有撇号，或者删除不需要的行。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngF As Range
Dim rngFS As Range
Dim lRow As Long
Dim lCol As Long
Set rngF = ActiveSheet.ListObjects(1).Range 'for tables
'Set rngF = ActiveSheet.AutoFilter.Range 'for AutoFilter ranges
Set rngFS = ActiveSheet.Range("FilterStatus")
lCol = rngF.Columns(1).Column - 1
lRow = rngF.Columns(1).Row
If Target.Count > 1 Then GoTo exitHandler
If Target.Address = rngFS.Address Then
  If rngFS.Value = "On" Then
    rngFS.Value = "Off"
  Else
    rngFS.Value = "On"
  End If
End If
If UCase(rngFS.Value) = "ON" Then
  If Not Intersect(Target, rngF) Is Nothing Then
    If Target.Row > lRow Then
      rngF.AutoFilter Field:=Target.Column - lCol, _
          Criteria1:=Target.Value
    ElseIf Target.Row = lRow Then
      rngF.AutoFilter Field:=Target.Column - lCol
    End If
  End If
End If
exitHandler:
  Exit Sub
End Sub