Excel中的水平列过滤

如果您不是一个新手用户，那么您一定已经注意到 Excel 中 99% 的所有内容都设计为使用垂直表，其中参数或属性（字段）穿过列，并定位有关对象或事件的信息在行中。 数据透视表、小计、双击复制公式——一切都是专门为这种数据格式量身定制的。

但是，没有任何规则没有例外，并且经常有人问我，如果在工作中遇到具有水平语义方向的表或行和列具有相同权重的表，我该怎么办：

Excel中的水平列过滤

如果 Excel 仍然知道如何水平排序（使用命令 数据——排序——选项——列排序)，那么过滤的情况就更糟了——根本没有用于过滤列的内置工具，而不是 Excel 中的行。 因此，如果您面临这样的任务，您将不得不想出不同复杂程度的解决方法。

方法 1. 新增 FILTER 功能

如果您使用的是新版本的 Excel 2021 或 Excel 365 订阅，则可以利用新引入的功能 FILTER （筛选），它不仅可以按行过滤源数据，还可以按列过滤。 为了工作，这个函数需要一个辅助的水平一维数组行，其中每个值（TRUE 或 FALSE）决定我们是显示还是隐藏表中的下一列。

让我们在表格上方添加以下行，并在其中写入每一列的状态：

Excel中的水平列过滤

  • 假设我们总是想显示第一列和最后一列（标题和总计），因此对于它们在数组的第一和最后一个单元格中，我们将值设置为 TRUE。
  • 对于剩余的列，相应单元格的内容将是一个公式，它使用函数检查我们需要的条件 И （和） or OR （要么）. 例如，总数在 300 到 500 之间。

之后，它仍然只使用该功能 FILTER 选择我们的辅助数组具有 TRUE 值的列：

Excel中的水平列过滤

同样，您可以按给定列表过滤列。 在这种情况下，该功能将有所帮助 COUNTIF （县），它检查允许列表中表头中下一列名称的出现次数：

Excel中的水平列过滤

方法 2. 数据透视表而不是通常的数据透视表

目前，Excel 仅在数据透视表中内置了按列进行的水平过滤，因此如果我们设法将原始表格转换为数据透视表，我们可以使用此内置功能。 为此，我们的源表必须满足以下条件：

  • 有一个“正确”的单行标题行，没有空单元格和合并单元格——否则将无法构建数据透视表；
  • 行和列的标签中不包含重复项——它们将在摘要中“折叠”为仅包含唯一值的列表；
  • 仅包含值范围内的数字（在行和列的交叉处），因为数据透视表肯定会对它们应用某种聚合函数（求和、平均值等），这不适用于文本

如果满足所有这些条件，那么为了构建一个看起来像我们的原始表的数据透视表，它（原始的）将需要从交叉表扩展为平面表（标准化）。 最简单的方法是使用 Power Query 加载项，这是自 2016 年以来内置于 Excel 中的强大数据转换工具。 

它们是：

  1. 让我们将表格转换为“智能”动态命令 主页 – 格式为表格 （首页——表格格式）.
  2. 使用命令加载到 Power Query 数据 - 来自表/范围（数据 - 来自表/范围）.
  3. 我们用总计过滤行（摘要将有自己的总计）。
  4. 右键单击第一列标题并选择 展开其他列 （取消透视其他列）. 所有未选择的列都转换为两列——员工的姓名和他的指标值。
  5. 使用进入列的总数过滤列 属性.
  6. 我们使用命令根据生成的平面（标准化）表构建数据透视表 主页 — 关闭并加载 — 关闭并加载... （首页 - 关闭并加载 - 关闭并加载到...）.

现在您可以使用过滤数据透视表中可用列的功能——名称和项目前面的常用复选标记 签名过滤器 （标签过滤器） or 按值过滤 （值过滤器）:

Excel中的水平列过滤

当然，在更改数据时，您需要使用键盘快捷键更新我们的查询和摘要 按Ctrl+其他+F5 或团队 数据 – 全部刷新 （数据——全部刷新）.

方法 3. VBA 中的宏

正如您很容易看到的那样，所有以前的方法都不是完全过滤 - 我们不会隐藏原始列表中的列，而是使用原始列表中的一组给定列形成一个新表。 如果需要过滤（隐藏）源数据中的列，则需要一种根本不同的方法，即宏。

假设我们要动态过滤表头中经理的姓名满足黄色单元格 A4 中指定的掩码的列，例如，以字母“A”开头（即获取“Anna”和“Arthur “ 因此）。 

与第一种方法一样，我们首先实现一个辅助范围行，在每个单元格中，我们的标准将由一个公式检查，逻辑值 TRUE 或 FALSE 将分别显示为可见列和隐藏列：

Excel中的水平列过滤

然后让我们添加一个简单的宏。 右键单击工作表选项卡并选择命令 来源 （源代码）. 将以下 VBA 代码复制并粘贴到打开的窗口中：

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Then For Each cell In Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = True End If Next Cell End If End Sub

其逻辑如下：

  • 一般来说，这是一个事件处理程序 工作表_更改，即此宏将在对当前工作表上的任何单元格进行任何更改时自动运行。
  • 对更改单元格的引用将始终在变量中 目标.
  • 首先，我们检查用户是否使用标准 (A4) 准确更改了单元格——这是由操作员完成的 if.
  • 然后循环开始 对于每个… 使用每列的 TRUE / FALSE 指标值迭代灰色单元格 (D2:O2)。
  • 如果下一个灰色单元格的值为TRUE（真），那么该列不隐藏，否则我们隐藏它（属性 老旧房屋).

  •  Office 365 中的动态数组函数：FILTER、SORT 和 UNIC
  • 使用 Power Query 的具有多行标题的数据透视表
  • 什么是宏，如何创建和使用它们

 

发表评论