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

问题描述

示例

Sheet1！ A：A
$ b $ pre $ {12,131,45}


/ pre>

Sheet2！A：A

  
 
 
 $未知函数   
 
 
  = formula_for_union_range（Sheet1！A：A; Sheet2！：A：A）
  
 应该返回  
 
 
  {12,131,45,12,131,46} 
  
 
 
 
问题
 
 
 
这怎么可能？ 
 
解决方案
 
 Google Apps脚本
 
 
 
但问题是关于剧本。我仍然成功地使用了以下代码： 
 
 
  function unionRanges（e）{
 var result = []; 
 var length = 0; 
 var i = 0; 
尝试{
 for（i = 0; i< arguments.length; i ++）
 length + = arguments [i] .length; 
 if（length> 3000）返回'#BIGRANGE'; 
 for（var i = 0; i< arguments.length; i ++）
 result = result.concat（arguments [i] .filter（function（el）{
 return el.join （''）.length> 0 
}））; 
返回结果; 
} catch（err）{
 return JSON.stringify（err）; 
 
 
 $ / code $ / pre 
 $ b 
电子表格功能
 
 
 
但是，如上所述，使用 {}  -notation更容易。
 
 
 
垂直连接 
 
 
  = {Range（Cols = N）; Range（Cols = N）} 
  
 
水平连接 
 
 
  = {范围（行= M），范围（行= M）} 
  
可以结合
  = {{,,}; {,,}} 
  
或者更难的东西 
 
 
  = {{;;}，{;;} }; {{;;}，{;;}}; {{;;}，{;;}} 
  
 
 
 
 Sheet 'Data 1'！A1：C20  
 
 
 
  |名称|日期|总和| 
 | Ethan | 3/4/2017 | 31 | 
 | Logan | 3/6/2017 | 62 | 
 | Brian | 3/26/2017 | 61 | 
 | ... | ... | ... | 
  
 
 
 
工作表'Data 2'！A1：C20  
 
 
 
  |名称|日期|总和| 
 | Nathan | 3/30/2017 | 53 | 
 | Alyssa | 3/13/2017 | 72 | 
 |约翰| 3/24/2017 | 79 | 
 |梅根| 3/16/2017 | 10 | 
 | ... | ... | ... | 
  
 
 
 
级联
 
 
 
垂直级联
 
 
 
  = {'Data 1'！A1：C20;'Data 2'！A2：C20} 
  
 
 
结果
 
 
  |名称|日期|总和| 
 | 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 | 
 |约翰| 3/24/2017 | 79 | 
 | ... | ... | ... | 
  
 
 
 
水平拼接
 
 
 
  = {TRANSPOSE（'Data 1'！A1：C20），TRANSPOSE（'Data 2'！A2：C20）} 
  
 
 
结果
 
 
  |名称| Ethan | Logan | Brian | ... | Nathan | Alyssa |约翰| 
 |日期| 3/4/2017 | 3/6/2017 | 3/26/2017 | ... | 3/30/2017 | 3/13/2017 | 3/24/2017 | 
 |总和| 31 | 62 | 61 | ... | 53 | 72 | 79 | 
  
更多关于如何连接Google电子表格中的范围 
 
I want to union ranges from any Google spreadsheets.

The example

Sheet1!A:A
{12, 131, 45}
Sheet2!A:A
{12, 131, 46}
The unknown function
=formula_for_union_range(Sheet1!A:A; Sheet2!:A:A)
should return
{12, 131, 45, 12, 131, 46}


The question

How is it possible?
解决方案
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);
  }
}


Spreadsheets feature

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

Vertical concatenation
={Range(Cols=N);Range(Cols=N)}
Horizontal concatenation
={Range(Row=M),Range(Rows=M)}
It's possible to combine
={{,,};{,,}}
Or something more hard
={{;;},{;;}};{{;;},{;;}};{{;;},{;;}}


Sheet 'Data 1'!A1:C20

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


Sheet '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 |
|   ...   |     ...   | ... |


Concatenation

Vertical concatenation

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


Result

|  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 |
| ...    |       ... | ... |


Horizontal concatenation

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


Result

| 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 |
More about this How to concatenate ranges in Google spreadsheets

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