在Google表格中连接两个范围 [英] Concatenate two ranges in Google Sheets

问题描述

tl; dr ：如何让两个范围显示为一个作为函数参数的一个范围？例如，我想查看E 列中除E5之外的每个单元格：

= MY_FUNCTION（somehowjoin（E1：E4，E6：E））

背景

我正在Google Sheets中撰写项目跟踪器，项目。我有一个

tl;dr: How do I make two ranges appear as one as an argument to a function? For example, I want to look at every cell in column E except E5:
=MY_FUNCTION( somehowjoin( E1:E4, E6:E ) )

Background
I'm writing a project tracker in Google Sheets, like Microsoft Project. I have a custom function in Google Sheets that allows me to calculate the start date for various tasks by looking at the maximum end date of all tasks that it depends upon:

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

However, the end date for this task is then calculated based on the start date, and that end date is in the column with all end dates that are examined. As a result, I have a circular dependency.

So, I'd like to write my formula for each line to skip over the current line, like:

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

---

FWIW my current workaround is to rewrite the custom function's signature like so:

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

and manually construct the two ranges within that function using:

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();

This is a functional solution, but makes the function more special purpose. I do not consider this to be an "answer" to this more general question of joining ranges.

解决方案

Short answer

Create your own arrays and use them as parameters of your custom functions.

Explanation

In Google Sheets, users could create their own arrays by embracing their elements in brackets.

Instead of

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

use

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

References

Using arrays in Google Sheets - Google docs editors Help

这篇关于在Google表格中连接两个范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！