☰

在 Excel 中，使用动态数组时，是否有办法使用包含范围参数的函数？

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
在 Excel 中，使用动态数组时，是否有办法使用包含范围参数的函数？

我将使用 NETWORKDAYS() 来说明。假设我在 Excel 中有三列数据。假设它们占据 A1:C5，如下所示：

  • A 列：开始日期
  • B 列：结束日期
  • C 列：两者之间的工作日数

因此，C 列中的单元格可以是（为简单起见，忽略假期参数）

C1 = NETWORKDAYS(A1,B1)
C2 = NETWORKDAYS(A2,B2)

等等，直到

C3 = NETWORKDAYS(A5,B5)

但我当然可以不是使用以下数组公式。

= {NETWORKDAYS(A1:A5,B2:B5)}

好吧，这很不幸，但可以忍受。是的，我必须将公式复制粘贴到所有行，但这不会让我丧命。

然而，随着动态数组的出现，这种限制变得非常烦人。这是因为如果 A 列和 B 列都是 DA，那么我可以使用：

= NETWORKDAYS(A1#,B1#)

这带来了巨大的好处，我不必知道这些输入数组的长度。溢出会解决这个问题。不过，我无法获得这种好处，因为 Excel 仍然会对 NETWORKDAYS() 的前两个参数的范围感到困惑。

当然，NETWORKDAYS() 只是一个例子。还有许多类似的挑战函数++。

有没有解决的办法？

++ 奇怪的是，并非所有带有标量参数的函数都会遇到这种情况。一个例子是 MATCH()，其中第一个参数通常是一个标量，但它可以接受 DA。

答案1

我们可以用IF({1},)

=NETWORKDAYS(IF({1},A1#),IF({1},B1#))

在此处输入图片描述

答案2

嗯，是的，一定要记住这IF({1},件事，不要忘记{}。

然而，还有第二个问题，如果数据是逐个构建的，那么给定的答案就无法起作用，这是功能上的一个重大缺陷SPILL（或者Dynamic Arrays如果你愿意的话）。

不能简单地将随机引用作为动态数组：使用A1#不会导致 Excel 查看内容并决定您必须指的是下面每个连续使用的单元格（不是右边？还是两者兼而有之？我们是否要求 Excel 假设我们的方向？）因此将其视为可以称为“A1#”的官方数组。

您可以定义这样的内容。也许您=A1:A110在 C1 中输入。SPILL 数组将显示在单元格 C1:C10 中，使用IF({1},技巧（有类似的技巧，但IF非常非常简单且易于应用）将 SPILL 输出到您想要的任何位置。不过，再次强调，只有您定义动态数组，Excel 才会发挥 SPILL 功能。

顺便说一句，请注意，只需要将一列定义为动态数组。您不需要将 B1:B10 也指定为一个。任一（或“多个中的一个”，如果超过两个）参数的范围作为单独定义的动态数组给出即可激活该功能。当然，您可以同时执行这两项操作，并且效果也很好。

所以答案是正确的，就目前而言，这是几种类似的事情之一，也是几种不同的整体事情之一，人们可以强制构建一个以一个单元作为其参数的函数，仅此而已。

（这是一回事，另一类“事情”是将最终公式或公式部分包含在确实触发 SPILL 功能的函数中。显然，即使对于过去的函数，它们也总是警告您，即使您为它们提供了一个范围，它们也只会使用范围内的“第一个”单元格（左上角）来产生结果，是的，即使对于它们，Excel 实际上也始终维护了您提供的范围所提供的整个数组，只是没有办法，或者没有人意识到并弄清楚如何使用它（或者更确切地说，MS 实际上重写了每个为 SPILL 做准备的旧函数，以便它们可以保存整个数组，而不是将其内部切割为单个单元格，然后工作……不，他们没有重写他们不必重写的东西），以利用它。现在我们这样做了。我怀疑会出现几种提高这些旧功能标准的策略。但答案很简单，而且易于应用（因为没有令人讨厌的扭曲来将其构建到公式中）。

另一种方法是将范围设为 aNamed Range并提供 NR 名称。然后可以强制执行许多函数。而现在，由于LET()本质上创建了一个命名范围（它覆盖了通过与 Excel 4 宏功能匹配的命名范围功能创建的任何更普遍的范围（SETNAME）），因此您可以结合强制技术（IF({1}在本例中）使用它。查阅强制一般知识以获取您可能不时发现有用的更多信息。顺便说一句，这两种方法实际上都不会强制 SPILL 功能对工作进行任何评估，但由于 SPILL 始终在寻找要显示的数组，并且它们在内部为结果生成数组，因此 Excel 不再挑选“第一个”值/单元格作为输出，而是采用 SPILL 尽职尽责地完整显示或给出错误的整个数组。

（哎呀，网上出现了一系列问题：人们想知道为什么当函数不需要超过一个单元格输入时，他们的公式会出现 SPILL 错误；或者他们指定的范围只有 20 个单元格，但他们的公式似乎考虑了整行或整列，因此出现 SPILL 错误，但他们如何对只有 20 个单元格长的范围做到这一点？好吧，是的，我使用了ROW(1:20)而不是来SEQUENCE()正确限制 Excel 正在做的事情，但是……另一组无聊的问题由此而来。无论如何......）

但它不起作用，除非答案提供的数据列已经是数组，如果一个接一个地输入它们，这实际上不可能发生，所以......你必须将其中一个定义为一个数组，然后才能真正将这个想法应用到这个问题上。

相关内容