过滤数据时,是否可以将过滤器的值放入单元格中?

过滤数据时,是否可以将过滤器的值放入单元格中?

我有这个电子表格(链接到谷歌驱动器--- 请注意,在线应用程序没有显示过滤,您需要下载它...):

开始电子表格

它运行良好,让我在使用条件 1 或 2(或两者)进行过滤时可以看到总数。现在,我想在单元格中输入“选定”值B8,并将其输入到过滤器中:C8

希望的结果选择b

如果我改变过滤器:

希望的结果选择一个

... 上也类似B8。我不介意在仅选择 Crit1 时B8单元格是空白的还是包含列表的随机值。在实际应用中,C8单元格值将用于索引另一个表。

这个想法是“放入B8上面两行显示的内容,注意过滤”---这可能吗calc?(或者excel,我不介意切换)。

答案1

您可以进行设置,以便在单元格中输入值来过滤表格,而不是相反。

在此处输入图片描述

G2这里我在和中定义了两个命名单元格,H2并将它们称为p_crit1p_crit2p是“参数”的意思。当然,您可以随意命名它们,或者根本不命名它们,但我认为这更容易理解。

完整数据表位于单元格中A1:E6。我已将其格式化为表格,以便如果向表中添加更多数据,其他公式中对它的任何引用都会增长。此表称为full_data。您可以考虑将其放在另一个隐藏的工作表上,或者隐藏包含完整数据的列。

在单元格中G5我有这个公式来获取标题:

=full_data[#Headers]

在单元格中G6我有这个公式来获取过滤后的数据:

=LET(qcrit1,FILTER(full_data,full_data[Crit1]=p_crit1,full_data),FILTER(qcrit1,INDEX(qcrit1,,3)=p_crit2,qcrit1))

让我们更仔细地看一下:

=LET(
    qcrit1,
    FILTER(
        full_data,
        full_data[Crit1]=p_crit1,
        full_data
            ),
    FILTER(
        qcrit1,
        INDEX(qcrit1,,3)=p_crit2,
        qcrit1
        )
    )

假设名称qcrit1(按条件 1 查询)为 的结果,FILTER其中full_data的列full_data[Crit1]等于 中的值p_crit1。如果尝试的过滤器返回空集(即没有匹配项),则只返回 的全部。这意味着如果用户在(单元格)full_data中输入无效值或在该单元格中不输入任何内容,我们将获取所有数据。p_crit1G2

现在,过滤第一个过滤器(称为)返回的数据,qcrit1其中第三列(通过访问INDEX)等于中的值p_crit2。如果尝试的过滤器返回一个空集(即没有匹配),则只返回整个。这意味着如果用户在(单元格)qcrit1中输入无效值或在该单元格中不输入任何内容,我们将只获得(第一个过滤器)返回的任何内容。p_crit2H2qcrit

最终结果是我们可以改变单元格中的值G2H2看到过滤后的数据集发生变化。

小计公式位于过滤数据集上方,因此它有空间根据数据量full_data和所选查询条件改变形状。它们将小计应用于动态数组引用的第 4 列和第 5 列$G$6#,这将自动获取过滤器返回的所有数据。当然,MATCH如果您愿意,您可以使用它来代替硬编码列索引 4 和 5。

这种方法的好处是,您现在可以轻松地在工作簿中的任意位置引用p_crit1和。您可以选择向单元格添加数据验证并限制用户可以选择的值。这些可以是所有可能的 Crit1 和 Crit2 值,也可以是每个值的子集。p_crit2G2H2

在此处输入图片描述

答案2

您可以使用辅助列来实现它,并TEXTJOIN()在 Excel 365 中可用:

  • 辅助列中的公式:=SUBTOTAL(2,D4)
  • 总计行公式:=TEXTJOIN(", ",TRUE,UNIQUE(FILTER(B2:B7,$E$2:$E$7=1)))
  • 总和公式:=SUM(FILTER(D2:D7,$E$2:$E$7=1))

当然你甚至可以隐藏你的辅助列

在此处输入图片描述

在此处输入图片描述

相关内容