I want to be able to select and reference all of the tabs in my excel spreadsheet (to get totals of everything), but the data source only allows one tab to be selected... is there a way to select and use the entire workbook in the pivot table?
我希望能够选择并引用我的Excel电子表格中的所有选项卡(以获取所有内容的总计),但数据源只允许选择一个选项卡...有没有办法选择和使用整个工作簿在数据透视表中?
Thank you!
5 个解决方案
#1
4
You actually just have a data formatting problem. You're using tabs to separate devices by departments instead of simply creating a department field/column and putting everything in one tab. The data was likely delivered to you that way, but don't let the delivery format define the true working format of the data.
你实际上只是有一个数据格式问题。您正在使用选项卡按部门分隔设备,而不是简单地创建部门字段/列并将所有内容放在一个选项卡中。数据很可能以这种方式传递给您,但不要让交付格式定义数据的真实工作格式。
Combine everything in one tab, and then you'll be able to use the data in a pivot table just fine.
在一个选项卡中组合所有内容,然后您就可以使用数据透视表中的数据了。
If you have more data than will fit in a tab, put it in a database such as MS Access, MS SQLServer or mySQL, and then tell excel to use the database as the pivot table source.
如果您拥有的数据超出了选项卡中的数据,请将其放在MS Access,MS SQLServer或mySQL等数据库中,然后告诉excel将数据库用作数据透视表源。
#2
1
I think Jonathan M has the best answer, but here's a way to do it using SQL that I've used successfully.
我认为Jonathan M有最好的答案,但是这是使用我成功使用的SQL的方法。
#3
0
I don't know any easy way of doing this, but you could write a macro to copy and paste all your existing sheets onto a single new sheet (assuming there's enough room on a single sheet).
我不知道这样做的简单方法,但是您可以编写一个宏来将所有现有的工作表复制并粘贴到一张新工作表上(假设单张工作表上有足够的空间)。
#4
0
In 2007 Multiple Consolidated Ranges isn't in the default pivot table window. You need to create your pivot table, click on it, hit Alt + D, then P. Click back to step one and you'll have the option for multiple consolidated ranges.
在2007年,多个合并范围不在默认数据透视表窗口中。您需要创建数据透视表,单击它,按Alt + D,然后按P.单击返回步骤1,您将可以选择多个合并范围。
#5
0
Select the data on any of the one sheet and hit Alt D > P. It will show you Step 1 of 3 for PivotTable Wizard. Now select Multiple Consolidation Ranges and hit Next.
Select and add different fields from different tabs and it will create a consolidated range for your PivotTable and then you can run your PivotTable based on data located on different tabs of sheet at the same time.
选择任何一张纸上的数据,然后按Alt D> P.它将显示数据透视表向导的第1步,共3步。现在选择Multiple Consolidation Ranges并点击Next。选择并添加不同选项卡中的不同字段,它将为您的数据透视表创建一个合并范围,然后您可以同时根据位于不同工作表上的数据运行数据透视表。
Hope it helps.
希望能帮助到你。
#1
4
You actually just have a data formatting problem. You're using tabs to separate devices by departments instead of simply creating a department field/column and putting everything in one tab. The data was likely delivered to you that way, but don't let the delivery format define the true working format of the data.
你实际上只是有一个数据格式问题。您正在使用选项卡按部门分隔设备,而不是简单地创建部门字段/列并将所有内容放在一个选项卡中。数据很可能以这种方式传递给您,但不要让交付格式定义数据的真实工作格式。
Combine everything in one tab, and then you'll be able to use the data in a pivot table just fine.
在一个选项卡中组合所有内容,然后您就可以使用数据透视表中的数据了。
If you have more data than will fit in a tab, put it in a database such as MS Access, MS SQLServer or mySQL, and then tell excel to use the database as the pivot table source.
如果您拥有的数据超出了选项卡中的数据,请将其放在MS Access,MS SQLServer或mySQL等数据库中,然后告诉excel将数据库用作数据透视表源。
#2
1
I think Jonathan M has the best answer, but here's a way to do it using SQL that I've used successfully.
我认为Jonathan M有最好的答案,但是这是使用我成功使用的SQL的方法。
#3
0
I don't know any easy way of doing this, but you could write a macro to copy and paste all your existing sheets onto a single new sheet (assuming there's enough room on a single sheet).
我不知道这样做的简单方法,但是您可以编写一个宏来将所有现有的工作表复制并粘贴到一张新工作表上(假设单张工作表上有足够的空间)。
#4
0
In 2007 Multiple Consolidated Ranges isn't in the default pivot table window. You need to create your pivot table, click on it, hit Alt + D, then P. Click back to step one and you'll have the option for multiple consolidated ranges.
在2007年,多个合并范围不在默认数据透视表窗口中。您需要创建数据透视表,单击它,按Alt + D,然后按P.单击返回步骤1,您将可以选择多个合并范围。
#5
0
Select the data on any of the one sheet and hit Alt D > P. It will show you Step 1 of 3 for PivotTable Wizard. Now select Multiple Consolidation Ranges and hit Next.
Select and add different fields from different tabs and it will create a consolidated range for your PivotTable and then you can run your PivotTable based on data located on different tabs of sheet at the same time.
选择任何一张纸上的数据,然后按Alt D> P.它将显示数据透视表向导的第1步,共3步。现在选择Multiple Consolidation Ranges并点击Next。选择并添加不同选项卡中的不同字段,它将为您的数据透视表创建一个合并范围,然后您可以同时根据位于不同工作表上的数据运行数据透视表。
Hope it helps.
希望能帮助到你。