☰

使用表格外部单元格中的信息来过滤表格

tag-icon • tag-icon microsoft-excel
使用表格外部单元格中的信息来过滤表格

我有一个表格，它从工作簿中的多个不同来源提取信息。表格中有 4 个类别，将定期对其进行筛选，并供 Excel 技能不熟练的用户使用。

我想设置一个过滤部分，其中包含用于过滤下表的列表。屏幕截图显示了建议的设置，其中列表值位于单元格B4、C4、D4中E4，下表是根据输入的值进行过滤的内容。

有谁知道如何实现这个或者类似的事情？

在此处输入图片描述
（点击图片放大）

答案1

您的意思是您想按特定的单元格值进行过滤，而不是使用内置表格过滤进行过滤？

好吧，您可以使用宏调用自动过滤器，并使用单元格的值作为标准。

您可以将宏放入工作表中，并在每次使用子项更改相关单元格之一时自动调用它Worksheet_Change。

有很多种方法可以写这个，但是我们需要指定要过滤哪个表，还需要指定要过滤哪一行。

这是我在工作表中输入的代码（通过右键单击工作表选项卡并选择“显示代码”），用于我的示例：

Private Sub Worksheet_Change(ByVal Target As Range)
Dim tabl As String, i As Long, C as Range
tabl = "Table1" 'Name of the Table
If Not Application.Intersect(Range(Target.Address), Range("B4:E4")) Is Nothing And Target.Count < 5 Then
    For Each C In Target
        tCol = Range(tabl & "[" & C.Offset(-1).Value2 & "]").Column
        If C.Value2 = "" Then
            ListObjects(tabl).Range.AutoFilter Field:=tCol
        Else
            ListObjects(tabl).Range.AutoFilter Field:=tCol, Criteria1:=C.Value2
        End If
    Next C
End If
End Sub

结果：

在此处输入图片描述

编辑
添加循环以便能够一次清除所有过滤器。

此代码要求您的标题在输入单元格和表格中相同，否则将引发错误。

-

Worksheet_Change当工作表上的单元格发生更改时调用。
这通常与
If Not Application.Intersect(Range(Target.Address), Range("")) Is Nothing Then
限制子程序运行（除非针对特定区域）一起使用。

限制Target.Count也是一个好主意，可以防止代码崩溃。通常，您希望将其限制为单个目标，除非您打算循环选择，就像我在本例中所做的那样。

如果您喜欢有带选项的下拉列表，这里有一个 VBA 方法：Excel 使用数据验证删除重复项

答案2

我想建议一个数组（CSE）公式，它将帮助您根据几个条件提取多行。

在此处输入图片描述

怎么运行的：

  • 我假设源数据在范围内 A2:E10。
  • 标准范围是A16:E16。
  • 在单元格中输入此公式A20，最后 Ctrl+Shift+Enter，向右填充，然后向下填充。

{=IFERROR(INDEX($A$2:$E$10,SMALL(IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})=COUNTA($A$16:$E$16),MATCH(ROW($A$2:$A$10),ROW($A$2:$A$10)),""),ROWS($A$1:A1)),COLUMNS($A$1:A1)),"")}

笔记：

  • 如果“条件单元格”为空，则此公式将返回所有记录。

在此处输入图片描述

  • 您可以输入一个或多个条件来过滤相关记录。

在此处输入图片描述

让我解释一下该命令的机制。

  • MMULT function不能使用布尔值，因此为了使其工作，公式必须将数组乘以 1。

MMULT(($A$2:$E$10=$A$16:$E$16)*1,{1;1;1;1;1;1;1})

变成，

MMULT({0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 1, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 0, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 0, 0;0, 0, 1, 0, 0, 1, 0;0, 0, 1, 0, 0, 0, 0},{1;1;1;1;1;1;1})

并返回，

{0;0;1;0;0;2;1;1;2;1}

和，

MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16)

变成，

{0;0;1;0;0;2;1;1;2;1}=COUNTA($A$16:$E$16)

变成，

{0;0;1;0;0;2;1;1;2;1}=2

并返回，

{FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}.

IF(MMULT(($A$2:$E$10=$A$16:$E$16)*1, {1;1;1;1;1;1;1})=COUNTA($A$16:$E$16), MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")

变成，

IF({FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; TRUE; FALSE}, MATCH(ROW($A$2:$A$10), ROW($A$2:$A$10)), "")

  • 小的函数获取数组中第 k 个最小的数字。

  • 指数函数根据行号和列号返回单元格区域或数组的值。

相关内容