在Google Sheets中连接两个范围

Question

简述: 如何将两个范围合并为一个函数参数？例如，我想查看除 E5 之外的 E 列中的每个单元格：
=MY_FUNCTION( somehowjoin( E1:E4, E6:E ) )

背景
我正在编写类似于 Microsoft Project 的 Google Sheets 项目跟踪器。在其中，我有一个Google Sheets 自定义函数，它允许我通过查看所有依赖任务的最大结束日期来计算各种任务的开始日期：

=MAX_LOOKUP( G9, A:A, I:I )

然而，这个任务的结束日期是基于开始日期计算的，而该结束日期位于检查所有结束日期的列中。因此，我面临循环依赖的问题。 因此，我希望为每行编写公式以跳过当前行，例如：

=MAX_LOOKUP( G9, A1:A8 + A10:A, I1:I8 + I10:I )

---

就我目前的解决办法而言，是将自定义函数的签名重写为以下形式：

=MAX_LOOKUP_SKIPROW( G9, ROW(), "A", "I" )

并在该函数内手动构建这两个范围，使用：

var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var keys1 = sheet.getRange(keyCol+"1:"+keyCol+(skipRow-1)).getValues();
var vals1 = sheet.getRange(valCol+"1:"+valCol+(skipRow-1)).getValues();

var keys2 = sheet.getRange(keyCol+(skipRow+1)+":"+keyCol).getValues();
var vals2 = sheet.getRange(valCol+(skipRow+1)+":"+valCol).getValues();

这是一个实用的解决方案，但会使函数更具有特殊目的性。我认为这并不是连接范围这个更普遍问题的“答案”。

- Phrogz

---

我更倾向于不重写我的自定义函数以接受多个参数，尽管这当然是可能的。 - Phrogz

我认为indirect()和row()的组合可以帮助您构建不包括当前行的范围。不是吗？ - Marc

@Marc 我所知道的是不能将不连续的区域作为单个参数。我错了吗？是否有A1或R1C1语法来表示不连续的区域？ - Phrogz

可能是 http://stackoverflow.com/q/18264423/405017 的重复。 - Phrogz

Answer 1

简短回答

创建自己的数组，并将它们用作自定义函数的参数。

说明

在Google Sheets中，用户可以通过将元素括在方括号内来创建自己的数组。

而不是使用：

=MY_FUNCTION( somehowjoin( E1:E4, E6:E ) )

使用

=MY_FUNCTION({E1:E4;E6:E})

参考资料

在Google表格中使用数组 - Google文档编辑器帮助

- Rubén

---

谢谢！这正是我所希望的：一种简单的、内置的语法，可以连接不连续的范围。 - Phrogz

Answer 2

我不太明白你想做什么，但这可能会有所帮助。您可以像下面这样连接范围。前两个方法将数据返回到行或列中。我包含这些只是为了让您看到正在发生的事情。第三种方法我在测试中使用，以确保数据可以发送到自定义公式。希望这能帮到您：

=split(join(",",A2:A6,A8:A12),",",true)

=transpose(split(join(",",A2:A6,A8:A12),",",true))

=MAX_LOOKUP( G9,  split(join(",",A1:A8,A10:A),",",true),split(join(",",I1:I8,I10:I),",",true) )

- Ed Nelson

Answer 3

当您将范围传递给Google表格中的自定义函数时，该范围会作为数组的数组传入。这是一个真正的数组，因为 Array.isArray() 返回 true。

有了这个知识，您可以创建自己的自定义函数：

function UNION(range1,range2){
  return range1.concat(range2);
}

然后简单地：

=MY_FUNCTION( UNION( E1:E4, E6:E ) )

...并且您的原始函数将不知道它正在操作已合并的不连续范围。

- Phrogz

---

我仍然希望存在一种更标准的方法来连接范围，而不是使用自定义函数。除非变得明显没有更好的选择，否则我不会接受这个答案。 - Phrogz