如何创建仅显示符合特定条件的数据的滚动视图窗口

Question 1

我无法解决滚动条问题，因为我对“开发人员”选项卡中的滚动条（表单控件或 Active X 控件）没有使用经验。但是，当涉及到填充它所控制的任何区域时，我可以说几件事：

我看不出该OFFSET()公式如何执行需要滚动的操作，除非您不想一次查看多行，并且滚动是从左到右的。由于这似乎不太可能，我将讨论获取（通常，人们会认为）多行：实际上，获取“完成”列中包含“否”的所有行。然后，由于“不太可能”与“从不”不同，我还将讨论获取单行的问题。
正如Rajesh S所说，FILTER()函数在这里是你的朋友。但是，如果你有Spill功能，就像大多数人最终似乎拥有的一样，如果使用 O365，你将不需要使用 CSE（获取数组的旧样式）。然而，它确实存在一个问题，因为你不想要来自数据源的连续列。

FILTER()将以任何可以在 Excel 中呈现的方式获取不连续的列范围。INDEX()是一种很好的方法。但是，它会简单地为您提供从最左边到最右边的所有列，无论您在示例中多么小心地指定只有四列。因此您将获得八列，其中四列为 0。要获得这四个不连续的列，最好使用“辅助表”。

大多数人都听说过“帮助列”和我最喜欢的“帮助页”，但许多人都讨厌它们。他们不仅认为它们不够优雅，而且还存在一个问题，即把它们放在哪里，如果它们必须存在于最终版本中，如何才能让它们在成品中不丑陋、不优雅，更不用说不让用户粗心地（或更糟？故意……）编辑它们。

但这里你需要它。你需要在某个地方构建所需输出表的四列，也许是一个隐藏的工作表，需要花费很大力气才能找到。但一定要在某个地方。

首先从 A 列制作所需的输出，构建四列，即 B、G、F 和 I 列。我将所需的单元格范围指定为 B3:B102，但显然，您可以将行值编辑为所需的任何值。甚至可以巧妙地动态构建它们。对于 B 列，使用：

=FILTER(B3:B102,$I$3:$I$102="No")

这告诉它从 B 列收集记录，查看 I 列中相应行的值，并仅获取 B 列中 I 列单元格中显示“否”的记录。

在单元格 B3 中输入该内容。然后将其复制并粘贴到单元格 F3、G3 和 I3。它将更改第一个范围以匹配这些列。这是确保为每个列构建完全正确的公式的简单方法。然后是有趣的技巧，它不仅可以消除空列，还可以使每列中的公式保持原样：选择单元格 G3 并将其拖到单元格 C3 上并放下。然后对 F3 执行相同操作，但将其放在单元格 D3 上，对单元格 I3 也一样，将其放在单元格 E3 上。

现在，数据源的 B、G、F、I 列顺序中就有了完美的公式。辅助表的 B、C、D 和 E 列中也有。保留这些公式，或者将这四个列拖到 A3:D3（如果您愿意）。辅助表现在已全部设置好。

转到输出页面，并FILTER()在单元格 B28 中再创建一个公式。这一次，它将引用辅助表/范围，而不是源数据。由于没有额外的列（即：辅助表的列是连续的，而不是分隔的），您需要在单元格 B28 中创建一个公式：

=FILTER(A3#:D3#,A3#=A3#)

这次它告诉 Excel 使用Spill范围 A3#、B3#、C3# 和 D3 (A3#:D3#)。顺便说一句，您可以突出显示它们，然后按F4使它们成为绝对值，以防止它们被插入行等。仅此一个指定这些Spill范围的公式将从辅助表中收集所有四列。对于条件，我使用第一列的单元格等于它们自己，因此辅助表中的所有单元格都收集到您的输出中。还有其他几种方法可以像那样包罗万象，我相信总有一天会有人运行时间测试并继续进行它，就像他们以前使用VLOOKUP()vs 一样。INDEX/MATCH但现在，一切都很好，只要您真的使用任何技术获得它们。

这是一种非常干净的小方法，使用所述输出中的一个公式来填充您的输出，即使在中间某处有一个辅助表。

如果您想要一条记录（如您在OFFSET()给出的公式中指定的那样），只需将该条件与“否”条件一起使用即可。但老实说，我看不出您给出的所有部分都匹配。只是说这样的函数确实暗示想要一条记录，但如果 B28 是输出单元格，那将是循环的，所以……，但谈到只想要“完成”列中显示“否”的记录，暗示一次想要多条记录。但它仍然可能意味着前者，只是如果您提供给函数的记录号OFFSET()是完整的，您根本不想显示任何内容。所以你打电话。这只是进一步限制它的问题。AND()在标准中使用如下，也许：

=FILTER(B3:B102,  AND($I$3:$I$102="No",  OFFSET('DATA DUMP'!B3,$B$28,0))  )

因此，必须满足两个条件才能TRUE显示记录。

这引出了最后一件事FILTER()：它将采用第三个参数（只需在末尾添加），当未找到匹配的记录时，它会给出您想要给出的消息。只是一个字符串，所以类似于"No record matches."

像这样的使用FILTER()可以给用户提供非常精细的数据，这些数据完全适合他们的任务和安全，以及“他们应该被允许看到什么数据”的情况。但要真正做到这一点，您需要数据输入控件，这样他们才能将添加、更改和删除的数据保存回源数据，前提是它通过了您可能设计的某些测试。由于您选择他们看到的列以及过滤行，因此一线用户可能看不到客户的地址，因此可能无法跟踪听起来不错的女性声音。等等，无论您的需求是什么，精细的粒度都是完全可用的。只要您有可靠的方法来链接不同的数据集，该函数也可以用于多个表。您始终可以完全控制源，因为您自己的数据输入表单可以处理对源的实际更改。用户甚至看不到辅助表中所做的选择，因此可能很难猜测除了他们看到的内容之外还有什么可用内容，而且，由于他们甚至看不到实际数据列的名称或表/范围名称本身，因此无法在工作区中编写引用一列又一列的公式（即进行勘探）。这极大地限制了他们尝试做他们不应该做的事情。如果打印机和文件的输出仅限于在数据控件中完成，您也可以控制它。

但您只需要上面的简单版本。

Answer