☰

在 Excel 中将不连续的范围作为单个函数参数传递

tag-icon • tag-icon microsoft-excel
在 Excel 中将不连续的范围作为单个函数参数传递

我希望这张图片能解释问题（见右下角的错误）。我需要将 B3:D3 加 E4:G4 作为单个范围传递给 FORECAST.LINEAR 函数。我以为创建一个命名范围可能会有帮助，但看起来这样做不行。

有没有办法将两个范围合并为一个概念范围，以便将其传递给这样的函数？

问题示例

答案1

像这样？将bar_1和定义bar-2为单独的名称，然后使用范围运算符将它们连接起来。

酒吧=bar_1:bar_2

在此处输入图片描述

答案2

只是为了帮助那些想要概括这一点的人，它只在范围之间没有行或列时才有效……而且……那时它也不起作用。例如，以下内容不会创建一个“连续”范围，就像答案所暗示的那样，对于这个特定的问题：

A1:C1 and F2:H2（如 A1:C1:F2:H2 或者 Excel 使用该表达式的结果 A1:H2）

此示例将给出两行八列，而不是由两个不连续区域组成的单“行”六个单元格。虽然答案产生的两行可能适用于此问题，但即使此问题也不会得到单行六个单元格区域。

在为试验此方法而模拟的裸工作表中，这些单元格会填充零。在实际电子表格中，这些单元格可能会填充真实（非零、非空）数据。在此问题中，按照图片所示进行设置，一切都很酷，答案是 7。（不知道这是否是正确答案，但它似乎让发帖人满意。）但是向 A2：C2 和 F1：H1 添加值...结果没有任何变化......一切似乎都很好，这些单元格一定不是创建范围的一部分......似乎如此。HOWEVER，开始将中的六个单元格更改为bar，可能为 9，结果 7 没有任何变化，直到所有单元格都为 9，从而#DIV/0!发生错误。实际上对调查此问题很有用：当您在 A2：C2 或 F1：H1 之一中输入其他值时，它会恢复为 7。在这些单元格中使用 9 也很有趣，但有点离题。

因此很明显，其他六个单元格是创建范围的一部分（也支持使用该公式的任F9一元素，该公式显示在 Excel 内部用于解析公式。（如果您在其他单元格中输入了其他任何值，并且这些值已填充，则可以使用这些值。）bar{9,9,9,0,0,0;0,0,0,9,9,9}

按照使用方式，FORECAST.LINEAR()由于 x 和 y 使用相同的范围，因此得出 7：由于 x 和 y 值相同，因此预测任何值时的 x 将与 y 相同。

这给人的印象与答案相同。对已填充单元格的任何更改都必须给出 7，只要它们是数字即可。这样看起来就像创建了一个真实的一行六个单元格的连续范围，而不是真正存在的两行六个单元格。错误#DIV/0!必须来自函数在得出答案时所采取的真实世界步骤：如果所有值（即所有 9，而不是传递给函数的两个范围）相同，则它无法为不在单元格中的值生成任何 x 和 y 对，因为它们显然永远不会是除了现在的值之外的任何其他值。（鉴于其方法显然经过了一个在这种情况下除以 0 的值的阶段，即使使用它们都具有的相同值作为预测 x 和 y 对的值也会失败。）

此外，如何创建范围并不重要。A1:H2 在此处与 A1:C1:F2:H2 相同，也与使用命名范围相同。示例的简单性使它看起来像是成功了，但实际上并没有。

然而，如果要处理的细胞数量有限，则可以使用：

=CHOOSE({1,2,3,4,5,6},B3,C3,D3,E4,F4,G4)  (most any Excel)

或者

=CHOOSE(SEQUENCE(1,6),B3,C3,D3,E4,F4,G4)  with newer Excel's

创建单行数组。

甚至可以使用类似这样的方法：

=CHOOSE(SEQUENCE(1,6),OFFSET(B3:D3,0,0,1,1),OFFSET(B3:D3,0,1,1,1),OFFSET(B3:D3,0,2,1,1),OFFSET(E4:G4,0,0,1,1),OFFSET(E4:G4,0,1,1,1),OFFSET(E4:G4,0,2,1,1))

有一个公式可以计算输入，因此可以使用来自另一个单元格的文本输入使其任意长（很多单元格）。INDIRECT()没有帮助，所以这是一种构建它而不是硬编码的方法。

还可以创建一个范围，即“辅助”范围以扩展“辅助”术语，以自己选择的方式包含所有部分和位，然后引用该范围。甚至可以使用以下方法“自动化”它（如果可能的话，使用动态范围，因此对辅助范围进行硬编码很难维护）：

=IF(COLUMN()-1<=COUNTA(bar_1),INDEX(bar_1,1,COLUMN()-1),INDEX(bar_2,1,COLUMN()-1-COUNTA(bar_1)))

并复制以包含足够的单元格来容纳整个范围。它目前设置为从第一个范围所在的列开始，并且不允许跳过列。所以它基本上解决了图片中的问题。必须复制到辅助范围中的每个单元格，而不是溢出（它不会像上面那样溢出，但也许可以修改，但这会破坏它），并且它本身可以动态引用。此外，如果范围重叠，它将无法工作（可能，未经测试），尽管也许UNIQUE()可以在那里提供帮助。这主要是 3-D 问题，而不是 2-D。由于它需要辅助范围中每个单元格一个“完整”公式，因此您无法在命名范围内构建它，据我所知。

相关内容