如何在Google电子表格中联合范围 [英] How to union ranges in google spreadsheets
本文介绍了如何在Google电子表格中联合范围的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
示例
Sheet1! A:A
$ b $ pre $ {12,131,45}
Sheet2!A:A
$未知函数 / p>
= 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 |
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屋!
查看全文