☰

如何在 Excel 中的公式中将范围和单元格合并为新范围

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2013 microsoft-excel-2016
如何在 Excel 中的公式中将范围和单元格合并为新范围

我在 Excel 中有一个公式，它将数组/值范围作为输入之一。

我有一系列值，比如说A1:A6，以及一个单元格C11。我希望按照精确的顺序将范围与单个单元格组合在一起，即组合的结果是一个数组/值范围，将从 开始到A1结束A6。C11我不想通过A1:A6与连接来获取文本值，而是想要包含和C11中的数据的数组。A1:A6C11

在此处输入图片描述

例如，在上图中，我想要将包含A1:A6和值的数组C11应用于名为 IRR 的公式/函数，该公式/函数以数组作为第一个参数。

我怎样才能将其作为公式的一部分来实现？

答案1

值得一提的是，您可以使用花括号轻松完成您在 Google 表格中尝试执行的操作：{range1 ; range2}（垂直组合）或 {range1, range2}（水平组合）。这将创建一个新数组，其中包含 range1 中的值，后跟 range2 中的值（垂直或水平，取决于您使用分号还是逗号分隔）。

答案2

对于 Microsoft Office 2016仅有的

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

在您的示例中，这将是

=TEXTJOIN(" ", TRUE, A1:A6, C11)

结果将由空格分隔，并且它将忽略包含空白值的单元格。

替代解决方案

  1. 选择您想要结果的单元格。
  2. 转到公式栏并输入=TRANSPOSE("A1:A5")&" "
  3. 选择整个公式并按F9将公式转换为数值。
  4. 删除两端的花括号。
  5. 添加=CONCATENATE( result )
  6. 进入。

来源

答案3

如果要通过公式合并一系列值和单个单元格，可以使用$-sign 来固定单个单元格地址。例如，当公式=A1+$C$11扩展到整个范围时，范围的每个单元格中将出现以下内容=AN+$C$11，其中N是单元格编号。

答案4

Vijay L的答案是正确的。但是，您必须删除单元格集周围的括号CHOOSE()才能执行此操作：

=Choose({1;2;3;4;5;6;7},  A1,A2,A3,A4,A5,A6,C11  )

原因是，使用括号后，您只有一个 for 对象CHOOSE()要执行，而且由于您告诉它做出七个选择，因此它将返回#VALUE!所有选择的错误。它甚至不会执行第一个选择。这样 editVijay L的方法就可以正常工作了。

像这样的一长串单元格或范围非常难输入，而且容易出现拼写错误和其他错误。您可以使用一种简单的技术来构建此串单元格：

  1. 选择超出范围的单元格
  2. 按“=”打开公式编辑器
  3. 按照列表中的顺序单击每个单元格，每次单击后输入一个逗号。请注意，会出现类似于 =A1,A2,A3,A4 的字符串。
  4. 验证所选单元格，如果它们正确，则用鼠标突出显示字符串
  5. 将其复制到剪贴板
  6. 输入 CHOOSE() 函数并将字符串粘贴在括号内，例如“=CHOOSE(A1,A2,A3,A4)”

如果您在无法立即将其粘贴到函数中的情况下使用此技术（就像这里一样），只需将其粘贴到靠近公式单元格的单元格中，以便在需要时方便使用。

另一种方法是使用TEXTJOIN()来获取一个字符串，然后FILTERXML()将其重新放回到单元格数组中。

相关内容