如何创建工作表的动态视图（过滤器）？ [英] How can I create a dynamic view (filter) of a worksheet?

问题描述

为了解释我的问题，我正在尝试修改一个excel文件来改进其功能，但不幸的是我没有很多Excel的经验。假设我有2个工作表：WS1和WS2。
WS1包含用户输入的所有数据。第一列包含整数。
我希望WS2包含WS1的动态视图。特别是WS2应该包含
WS1中的所有行，其第一列包含1。 WS2的内容每次更新时都会自动更改。

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

解决方案

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

< ！pre> = INDEX（ 'WS1' $ A：$ A，SMALL（IF（ 'WS1' $ A：$ A = 1，ROW（INDIRECT（ 1： &安培; COUNTA（'WS1'！A：A）））），ROWS（'WS1'！$ A $ 1：$ A1）））


使用 Ctrl - Shift - 确认此公式输入表示这是一个数组公式。因此，您将在其周围看到大括号 {} 。

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

  = INDEX（'WS1'！$ B：$ B，SMALL（IF（'WS1'！$ A：$ A = 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及更高版本）中，如

<$ ！p $ p> = IFERROR（INDEX（ 'WS1' $ A：$ A，SMALL（IF（ 'WS1' $ A：$ A = 1，ROW（INDIRECT（ 1： &安培; $ COUNT（'WS1'！A：A）））），ROWS（'WS1'！$ A $ 1：$ A1））））


工作表 WS2 中的列的内容自动更新，如果列 A code> WS1 被调整。

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

<$ p ！$ p> = 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））
  

并选择并拖下来。

您可以找到上传的示例工作簿在这里

I'm trying to modify a an excel file to improve its functionality, but unfortunately I don't have much experience with excel.

To explain my problem, let's say I have 2 worksheets: WS1 and WS2. WS1 contains all the data entered by the user. The first column contains integer numbers. I would like WS2 to contain a dynamic view of WS1. In particular WS2 should contain all the lines in WS1 whose first column contains "1". The content of WS2 should change automatically every time WS1 is updated.

Can I achieve this functionality with standard excel functions/techniques? Thanks

解决方案

You can achieve this using array formulas. Suppose that your data is in columns A, B and C in worksheet WS1. Enter the following formula in cell A1 on worksheet WS2:

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

Confirm this formula using Ctrl-Shift-Enter to indicate that this is an array formula. As a result, you will see curly brackets {} around it.

Similarly, enter the following array formulas in cells B1 and C1 on worksheet 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)))

Then select cells A1, B1 and C1 on worksheet WS2 and drag the right bottom corner all the way down as far as needed to contain all rows. Note that if you drag down too far, you will see #NUM. To avoid that, wrap the formulas in an IFERROR() formula (for Excel 2007 and later), as in

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

The contents of the columns on worksheet WS2 get updated automatically if values in column A on WS1 are adjusted.

A somewhat more efficient solution uses a helper column, lets say on worksheet WS3. It first calculates the indices of the relevant rows with the following formula (as an array formula in A1):

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

Then enter the following formulas in B1, C1 and D1 (regular, not array formula):

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

and select and drag down.

You can find an uploaded example workbook here

这篇关于如何创建工作表的动态视图（过滤器）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！