我有这个电子表格(链接到谷歌驱动器--- 请注意,在线应用程序没有显示过滤,您需要下载它...):
它运行良好,让我在使用条件 1 或 2(或两者)进行过滤时可以看到总数。现在,我想在单元格中输入“选定”值B8
,并将其输入到过滤器中:C8
如果我改变过滤器:
... 上也类似B8
。我不介意在仅选择 Crit1 时B8
单元格是空白的还是包含列表的随机值。在实际应用中,C8
单元格值将用于索引另一个表。
这个想法是“放入B8
上面两行显示的内容,注意过滤”---这可能吗calc
?(或者excel
,我不介意切换)。
答案1
您可以进行设置,以便在单元格中输入值来过滤表格,而不是相反。
G2
这里我在和中定义了两个命名单元格,H2
并将它们称为p_crit1
和p_crit2
。p
是“参数”的意思。当然,您可以随意命名它们,或者根本不命名它们,但我认为这更容易理解。
完整数据表位于单元格中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_crit1
G2
现在,过滤第一个过滤器(称为)返回的数据,qcrit1
其中第三列(通过访问INDEX
)等于中的值p_crit2
。如果尝试的过滤器返回一个空集(即没有匹配),则只返回整个。这意味着如果用户在(单元格)qcrit1
中输入无效值或在该单元格中不输入任何内容,我们将只获得(第一个过滤器)返回的任何内容。p_crit2
H2
qcrit
最终结果是我们可以改变单元格中的值G2
并H2
看到过滤后的数据集发生变化。
小计公式位于过滤数据集上方,因此它有空间根据数据量full_data
和所选查询条件改变形状。它们将小计应用于动态数组引用的第 4 列和第 5 列$G$6#
,这将自动获取过滤器返回的所有数据。当然,MATCH
如果您愿意,您可以使用它来代替硬编码列索引 4 和 5。
这种方法的好处是,您现在可以轻松地在工作簿中的任意位置引用p_crit1
和。您可以选择向单元格添加数据验证并限制用户可以选择的值。这些可以是所有可能的 Crit1 和 Crit2 值,也可以是每个值的子集。p_crit2
G2
H2