excel - 如何创建工作表的动态 View (过滤器)？

Question

我正在尝试修改一个 excel 文件以改进其功能，但不幸的是我对 excel 没有太多经验。

为了解释我的问题，假设我有 2 个工作表:WS1 和 WS2。
WS1 包含用户输入的所有数据。第一列包含整数。
我希望 WS2 包含 WS1 的动态 View 。特别是 WS2 应该包含
WS1 中第一列包含“1”的所有行。每次更新 WS1 时，WS2 的内容都会自动更改。

我可以使用标准的 Excel 功能/技术来实现此功能吗？
谢谢

Answer 1

您可以使用数组公式来实现这一点。假设您的数据位于 A 列中, B和 C在工作表中 WS1 .在单元格 A1 中输入以下公式在工作表上 WS2 :

=INDEX('WS1'!$A:$A,SMALL(IF('WS1'!$A:$A=1,ROW(INDIRECT("1:"&COUNTA('WS1'!A:A)))),ROWS('WS1'!$A$1:$A1)))

使用 Ctrl-Shift-Enter 确认此公式以指示这是一个数组公式。结果，您将看到大括号 {}周围。

同样，在单元格 B1 中输入以下数组公式和 C1在工作表上 WS2 :

=INDEX('WS1'!$B:$B,SMALL(IF('WS1'!$A:$A=1,ROW(INDIRECT("1:"&COUNTA('WS1'!A:A)))),ROWS('WS1'!$A$1:$A1)))       
=INDEX('WS1'!$C:$C,SMALL(IF('WS1'!$A:$A=1,ROW(INDIRECT("1:"&COUNTA('WS1'!A:A)))),ROWS('WS1'!$A$1:$A1)))

然后选择单元格 A1 , B1和 C1在工作表上 WS2并将右下角一直向下拖动到需要包含所有行的位置。请注意，如果您向下拖得太远，您将看到 #NUM .为避免这种情况，请将公式包装在 IFERROR() 中。公式(适用于 Excel 2007 及更高版本)，如

=IFERROR(INDEX('WS1'!$A:$A,SMALL(IF('WS1'!$A:$A=1,ROW(INDIRECT("1:"&COUNTA('WS1'!A:A)))),ROWS('WS1'!$A$1:$A1))),"")

工作表上的列内容 WS2如果列 A 中的值自动更新在 WS1被调整。

一个更有效的解决方案使用辅助列，让我们在工作表 WS3 上说。 .它首先使用以下公式计算相关行的索引(作为 A1 中的数组公式):

=IFERROR(SMALL(IF('WS1'!$A:$A=1,ROW(INDIRECT("1:"&COUNTA('WS1'!A:A)))),ROWS('WS1'!$A$1:$A1)),"")

然后在 B1中输入以下公式, C1和 D1 (常规，不是数组公式):

=IF(ISBLANK($A1),"",INDEX('WS1'!$A:$A,$A1))
=IF(ISBLANK($A1),"",INDEX('WS1'!$B:$B,$A1))
=IF(ISBLANK($A1),"",INDEX('WS1'!$C:$C,$A1))

并选择并向下拖动。

你可以找到一个上传的 example workbook here