将多个表行合并到主表中 [英] Combine Multiple Tables Rows Into Master Table
问题描述
大家星期一快乐！
有一个问题，希望能为您提供帮助.我有一个带有预算标签的预算电子表格.在此选项卡上，大约有8个表格细分为不同的类别.选项卡中的每个表都具有完全相同的列.有没有一种非vbscript/marco方式来创建主表，该主表将所有表组合到另一个选项卡中的单个表中.看来这没什么大不了的，但是我已经尝试了所有我能想到的并在网上找到的东西，而且如果没有名为power query的插件，似乎没有一个不错的解决方案.
Have a question and hope you can help. I have a budget spreadsheet that has a budget tab. On this tab is about 8 tables broken down into different categories. Every table in the tab has the exact same columns. Is there a non-vbscript/marco way to create a master table that combines all of the tables into a single table in a different tab. This seems like it would be a no brainer but I have tried everything I can think of and find online and there doesn't seem to be a decent solution without an addon called power query.
推荐答案
您可以使用数据透视表向导的功能将多个范围(属于您的表)合并到一个数据透视表中.
You can use the functionality of the pivottable wizard to consolidate multiple ranges (which are your tables) together into one pivottable.
当提示您添加范围时，请使用具有以下语法的表名:Table4[#All]
When it prompts for you to add your ranges use the table names with the following syntax: Table4[#All]
您需要[#All]才能获取与表关联的所有数据.只需对要合并的每个表名重复此操作.
You need the [#All] to get all the data associated with the table. Just repeat this for each of your tables names you want to consolidate.
我在这里的回答中给出了完整的描述:
Full description i have given in my answer here:
注意:如果要保留原始表名或表号，则需要选择以下选项:
Note: If you want to keep the original table names or table numbers you will need to select the option:
1)我将创建页面字段"
1) "I will create the Page Fields"
2)使用表格名称输入范围，例如表4 [#All]
2) Enter the Ranges using the table name e.g. Table4[#All]
3)选择您想要的页面字段数1-4，并在下方添加用于标识所选范围的项目标签，例如表4.
3) Select how many page fields do you want 1-4 and add item label used to identify the selected ranges below e.g. Table4.
我不确定最多4个项目还是可以通过VBA进行扩展.但是，您也可以使用PowerQuery或UnionQuery.
I am not sure if 4 items is the maximum or if this can be extended through VBA. However you can also use PowerQuery or UnionQuery.
以下引号来自此处: http://www.contextures.com/xlPivot08.html
我提供了一些轮廓，以防链接丢失.
I include some outline in case links are lost.
PowerQuery:
PowerQuery:
如果您具有支持Microsoft的Power Query加载项的Excel版本，则可以使用它来组合两个或多个表中的数据.这些表可以在同一工作簿中，也可以在不同文件中.
If you have a version of Excel that supports Microsoft's Power Query add-in, you can use it to combine the data in two or more tables. The tables can be in the same workbook, or in different files.
http://www.contextures.com/xlPivot08.html#videopowerquery
联盟查询:
如果您无法在单个工作表上合并数据，另一种解决方案是在Excel文件中创建命名范围，然后使用Microsoft Query(MS Query)合并数据.
If you can't combine your data on a single worksheet, another solution is to create named ranges in an Excel file, and use Microsoft Query (MS Query) to combine the data.
http://www.contextures.com/xlPivot08.html#union01
这篇关于将多个表行合并到主表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
