如何从使用相同模板的不同表合并数据?

I have one spreadsheet which has 5 sheets

我有一个电子表格，有5张纸

All the sheets share the same template, so they are alike. The only difference is the data

所有的表都共享相同的模板，所以它们是相似的。唯一的区别是数据

I would like to make another sheet, whichs gets all the data from column A3 (from row 3 to the end) in sheet 1,2,3,4,5, and puts it in 1 column in my new sheet.

我想再做一个表，它从A3(从第3行到最后)得到所有的数据，在表格1、2、3、4、5中，并把它放在我的新表中的1列中。

How is this possible?

这怎么可能?

I am using Google Docs, but i guess Excel and Google Docs are much alike.

我使用的是谷歌文档，但我想Excel和谷歌文档非常相似。

Thanks

谢谢

4 个解决方案

#1

=CONCATENATE(Sheet1!A:A,",",Sheet2!A:A,",",Sheet3!A:A,",",Sheet4!A:A,",",sheet5!(A:A)) This will concatenate A1,A1,A1,A1,A1 for sheets 1-5 on sheet 6. Drag it down to concatenate by cells (A2 then A3). You don't need to define the ranges A:A but you can if you want.

=连接(Sheet1!A:A，"，"，Sheet2!A:A，"，"，Sheet3!A:A，"，"， "，Sheet4!A:A，"，"，sheet5!将其向下拖动到由单元格连接的地方(A2然后A3)。你不需要定义范围A:A但是如果你想的话你可以。

#2

By far the best answer I've found to this question is found here: http://www.jessespevack.com/systems-leadership/2015/4/22/pulling-spreadsheet-data-no-scripts-required. In essence use ImportRange to pull in data from several other sheets. Then wrap those in an ArrayFormula so that they appear one after the other on the sheet. Lastly wrap the ArrayFormula in a Sort so that the blank lines are all left at the end.

到目前为止，我发现这个问题的最佳答案是:http://www.jessespevack.com/systemsleadership/2012/4/22/pulling-spreadsheet -data-no- scriptsts -required。本质上，使用无描述从其他几个表中提取数据。然后用ArrayFormula把它们包起来，这样它们就会出现在纸上。最后，对ArrayFormula进行排序，以便所有的空行都在末尾。

Say you have Sheet1, Sheet2 and Sheet3 and you would like to combine columns A through E on MergeSheet. Put the column headings in the cells MergeSheet!A1:E1

假设你有Sheet1、Sheet2和Sheet3，你想在MergeSheet中将A到E列合并在一起。将列标题放在单元格中合并!A1:E1

Then in cell A2 enter a formula like this:

然后在单元格A2中输入如下公式:

=SORT(
    ARRAYFORMULA({
         IMPORTRANGE("https://docs.google.com/spreadsheets/d/UniqueKey","Sheet1!A2:E");
         IMPORTRANGE("https://docs.google.com/spreadsheets/d/UniqueKey","Sheet2!A2:E");
         IMPORTRANGE("https://docs.google.com/spreadsheets/d/UniqueKey","Sheet3!A2:E")
,1,TRUE}))

The URL is the url of the spreadsheet, and can be copied from the address bar of the browser.

URL是电子表格的URL，可以从浏览器的地址栏复制。

It's best to test that the IMPORTRANGE function works for each range individually before combining them into the one long function.

在将它们组合成一个长函数之前，最好先测试无描述函数是否对每个范围都有效。

#3

I don't think that you have a built-in function for such functionality.

我不认为你有这样的内置功能。

You can easily write an Apps Script that performs this

您可以很容易地编写执行此操作的应用程序脚本

var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

var newData = new Array();

for (var i = 1; i <= 5; i++)  {
  var s = sheets[i];
  var r = s.getRange("A:A");
  // Go over the values and fill up a new range in sheet 6...
  var data = r.getValues();
  for(i in data){
    var row = data[i];
    newData.push(row);
  }
}

#4

This is the simpliest solution to merge multiple sheets of multiple google spreadsheets (with the same columns)

这是合并多个谷歌电子表格的最简单的解决方案(具有相同的列)

=SORT(
    {
        IMPORTRANGE("unique_spreadsheet_key1";"'Your first sheet'!A2:G");
        IMPORTRANGE("unique_spreadsheet_key2";"'Your second sheet'!A2:G");
        IMPORTRANGE("unique_spreadsheet_key2";"'Your third sheet'!A2:G")
    };
    3;  TRUE
)

Based on dcb solution, but corrected (the SORT part) and without ARRAYFORMULA!

基于dcb的解决方案，但修正了(排序部分)，没有ARRAYFORMULA!

You can change 3 to sort your merged sheet by the column of your need !
您可以更改3，根据需要对合并的表进行排序!
unique_spreadsheet_key : just the key, doesn't neet complete url
unique_spreadsheet_key:只是键，不需要完整的url

#1

#2