如何在谷歌电子表格中联合范围 [英] How to union ranges in google spreadsheets

问题描述

我想从任何 Google 电子表格中合并范围.

I want to union ranges from any Google spreadsheets.

Sheet1!A:A

{12, 131, 45}

Sheet2!A:A

{12, 131, 46}

未知功能

=formula_for_union_range(Sheet1!A:A; Sheet2!:A:A)

应该返回

{12, 131, 45, 12, 131, 46}

问题

怎么可能?

推荐答案

Google Apps Script

然而问题是关于剧本的.我仍然成功使用以下代码:

Google Apps Script

And yet the question was about the script. I'm still successfully using the following code:

function unionRanges(e) {
  var result = [];
  var length = 0;
  var i = 0;
  try {
    for (i = 0; i < arguments.length; i++)
      length += arguments[i].length;
    if (length > 3000) return '#BIGRANGE';
    for (var i = 0; i < arguments.length; i++)
      result = result.concat(arguments[i].filter(function (el) {
        return el.join('').length > 0
      }));
    return result;
  } catch (err) {
    return JSON.stringify(err);
  }
}

电子表格功能

但是，如上所述，使用 {}-notation 更容易.

Spreadsheets feature

But, as noted above, it is easier to use {}-notation.

={ Sheet1!A1:C10 ; Sheet2!A1:C34 }

垂直串联

={ Range(Cols=N) ; Range(Cols=N) }

水平串联

={ Range(Rows=M) , Range(Rows=M) }

可以合并

={ { , , } ; { , , } }

或者更难的东西

={{{;;},{;;}};{{;;},{;;}};{{;;},{;;}}}

尝试这样的事情

={
   {{ 1; 2; 3},{ 4; 5; 6}};
   {{ 7; 8; 9},{10;11;12}};
   {{13;14;15},{16;17;18}}
 }

不需要内部水平串联

={
    { 1; 2; 3},{ 4; 5; 6};
    { 7; 8; 9},{10;11;12};
    {13;14;15},{16;17;18}
}

参数分隔符的语言环境依赖

如果您当前的语言环境支持 , 作为参数分隔符，那么您应该使用 ; 进行垂直连接，使用 , 进行水平连接.

Locale dependencies of argument delimiters

If your current locale supports , as an argument delimiter thnen you should use ; for a vertical concatenation and , for a horizontal concatenation.

否则你的参数分隔符是 ; 并且你必须分别使用 ; 和 \ (没有空格).

Otherwise your argument delimiter is ; and you have to use ; and \ (without spaces), respectively.

|   Name  |    Date   | Sum |
| Ethan   |  3/4/2017 |  31 |
| Logan   |  3/6/2017 |  62 |
| Brian   | 3/26/2017 |  61 |
|   ...   |     ...   | ... |

表 'Data 2'!A1:C20

|  Name   |    Date   | Sum |
| Nathan  | 3/30/2017 |  53 |
| Alyssa  | 3/13/2017 |  72 |
| John    | 3/24/2017 |  79 |
| Megan   | 3/16/2017 |  10 |
|   ...   |     ...   | ... |

串联

垂直串联

={'Data 1'!A1:C20;'Data 2'!A2:C20}

结果

|  Name  |    Date   | Sum |
| Ethan  |  3/4/2017 |  31 |
| Logan  |  3/6/2017 |  62 |
| Brian  | 3/26/2017 |  61 |
| ...    |       ... | ... |
| Nathan | 3/30/2017 |  53 |
| Alyssa | 3/13/2017 |  72 |
| John   | 3/24/2017 |  79 |
| ...    |       ... | ... |

水平串联

={TRANSPOSE('Data 1'!A1:C20),TRANSPOSE('Data 2'!A2:C20)}

结果

| Name |   Ethan  |   Logan  |   Brian   | ... |   Nathan  |   Alyssa  |    John   |
| Date | 3/4/2017 | 3/6/2017 | 3/26/2017 | ... | 3/30/2017 | 3/13/2017 | 3/24/2017 |
| Sum  |       31 |       62 |        61 | ... |        53 |        72 |        79 |

更多相关信息如何在 Google 电子表格中连接范围

这篇关于如何在谷歌电子表格中联合范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！