excel - Excel数组公式自动扩展范围(无宏)

Question

问题

当我手动将行添加到表中时，如何自动将行添加到数组公式？ Google Docs 似乎在 https://support.google.com/docs/answer/3093275?rd=1 上提供了类似的功能。 , 并适用于 Excel 中的宏 http://www.wilmott.com/messageview.cfm?catid=10&threadid=62734 .但是，使用此宏需要启用 Microsoft Scripting Runtime，我不希望这样做，而且最终用户也难以维护。我正在运行 Excel 2013。

我正在为可能不了解 VBA 或数组公式但需要将项目添加到验证表(如下所述)的最终用户创建此工作表。当用户添加表格行时，我的数据验证范围及其数组公式的长度不会自动增加，并且数据验证可能不再显示所有可接受的值。

过滤数据验证的设置

您可以查看禁用宏的精简工作表 here . Main 表中 Process 列上的数据验证将仅显示当前显示在 Validation 表中的 Testing Process 列中的值。包括切片器以便于过滤。

使用 http://www.contextures.com/xlDataVal02.html 的帮助和其他一些来源，我创建了一个名为 Main_HIGHLIGHT 的表，其中包含数据验证，仅允许在名为 Validation 的第二个表中的列的可见/过滤值。

第二个表具有三个相关列，可见、类别和测试过程。该表使用类别列上的切片器进行过滤，数据验证从测试过程列返回值。使用三步过程来防止过滤值出现在数据验证中:

如果表格行被过滤掉，可见列中的单元格显示空白，如果没有过滤掉，则显示测试过程的值。其公式为:=IF(AGGREGATE(3, 5,[@[Testing Process]])>0,[@[Testing Process]],"")

直接位于表格左侧但不是表格一部分的数组公式从 Visible 中获取范围并对其进行排序，以使所有空白单元格都位于范围的底部，并且所有具有值的单元格都位于顶端。它填充宽度为 1 且高度等于表中条目数的范围。该范围被赋予定义名称 Visible_Tests_with_filtered_removed。像往常一样使用 Ctrl-Shift-Enter 输入的公式是:

=INDEX(Validation[Visible],
        SMALL(
            IF( Validation[Visible]<>"",
                    ROW(INDIRECT("$A$1:$A$"&COUNTA(Validation[Category]))),
                    ""
            ),
            ROW(INDIRECT("A1:A"&COUNTA(Validation[Category])))
        )
)

创建的定义名称 Visible_Tests_with_blanks_removed 仅包含 Visible_Tests_with_filtered_removed 中的值，不包含任何空白或错误​​。其公式为:=OFFSET(PPRNT!$A$34,0,0,MATCH("*",Visible_Tests_with_filtered_removed,-1),1)

潜在的解决方案

理想情况下，我想将数组公式添加到验证表中，因为这会在添加数组公式时自动将数组公式复制到任何新行。但是，当我尝试这样做时，我收到“表格中不允许使用多单元格数组公式”的错误消息。

或者，也许我可以将整个范围放入另一个已定义名称中，例如 Visible_Tests_with_blanks_removed，其值实际上并不位于工作表上的单元格中。我不知道

如果一切都失败了，我可以使用上面链接的宏，但在我看来它不应该这么难，我可能只会在 HowTo 选项卡中包含扩展数组公式的说明。

Answer 1

感谢@OldUgly 朝着正确的方向前进。
从 OneDrive 下载示例工作簿，地址为 https://1drv.ms/x/s!Ak4Lq2gGjO8hleIyd60JuPkctlDhGw ，但请注意在线预览不支持数据验证!您必须 下载文件看到它在行动。
这个三步过程为数据验证创建了正确的列表，并且它是表的一部分，因此它会自动更新。请注意，我在原始问题中使用的 CSE 公式是多单元格(选择一堆单元格，然后输入公式并按 Ctrl-Shift-Enter)，但表格中不允许使用这些公式，因此此解决方案使用单-单元格数组公式，它会自动复制到表中列中的每个单元格。

在表上创建一个可见列以确定当前隐藏了哪些行。这是一个普通公式，而不是 CSE 公式。=IF(AGGREGATE(3, 5,[@[Testing Process]])>0,TRUE,FALSE)

在表格中添加一个过滤列表列，然后输入这个单单元格 CSE 公式(应该会自动复制到表格中的其余单元格，就像任何其他表格公式一样)。如果您按 Enter 而不是 Ctrl-Shift-Enter，您将获得 #NUM!除了第一个单元格之外的所有单元格都出错。

         =INDEX([Testing Process],
         SMALL(
             IF([Visible], ROW([Testing Process])-ROW(Validation[[#Headers],[Testing Process]]), ""),
             ROW([@[Testing Process]])-ROW(Validation[[#Headers],[Testing Process]])
         )
 )

使用公式 =OFFSET(Validation[[#Headers],[Filtered List]], 1, 0, MATCH("*",Validation[Filtered List],-1), 1) 创建定义名称 Testing_Processes_for_Data_Validation (Formulas->Define Name)这样数据验证就没有一堆#NUM!错误在它的最后。
在单元格上激活数据验证时，将允许设置为“列表”，将源设置为 =Testing_Processes_for_Data_Validation .

就是这样!这会根据表格的过滤列创建一个自动扩展的动态列表，并从该列表中删除空白和错误。