如果两个工作表没有唯一的通用属性，我如何将它们合并到一个枢轴表中？ [英] How do I combine two sheets into a single pivot table when they have no unique common attribute?

问题描述

我将数据库对象从T-SQL到PL / SQL（即Microsoft到Oracle）的大型转换。我有两张表跟踪它们。一个是现有的程序正在转换，另一个是为了新的，我不得不创建。它们都有以下列：

I am doing a big conversion of database objects from T-SQL to PL/SQL (i.e. Microsoft to Oracle). I've two sheets tracking them. One is for existing procedures that are being converted and the other is for new ones that I've had to create. They both have the following columns:

Object Name   
Object Type   
-- other details inc lines of code etc   
Conversion Status   
Phase of move

我想要做的是创建一个图表，其中我可以有效地将两组数据组合在一起，使用切片器来选择要查看的阶段。有五个阶段，所以理想情况下，我可以选择第一阶段，看到在两个工作表中的28项，25已完成，2仍在测试，1不开始。如果它可以显示新的v。现有的将是好的，但不是必要的。

What I want to do is create a chart where I can effectively group both sets of data together, with a slicer to choose which phase to view. There are five phases, so ideally I could choose phase one and see that out of 28 items in both sheets, 25 are done, 2 are still being test and 1 isn't started. If it could show new v. existing that would be good, but not essential.

但是，我不是很清楚如何做到这一点。我试图创建一个数据模型，并结合这两组数据，但它想要它们之间的关系。我试图提供阶段的移动，但因为它不是唯一的Excel不会有它。

However, I'm not really clear on how to do this. I tried to create a data model and combine both sets of data, but it wanted a relationship between them. I tried to provide Phase of move, but as it's not unique Excel wouldn't have it.

我的下一步是什么？

'm after，this is a chart from the first data set：

To explain what I'm after, here's a chart from the first data set:

这是第二个数据集的图表：

Here's a chart from the second data set:

我基本上需要的是透视表（或正则图表），它结合了这两组信息

What I basically want is a pivot chart (or regular chart) which combines both of these sets of information.

>转换状态选项在两个工作表中都很常见。如果在阶段1中，在表单1上有十个项目完成，在阶段1中，在表单2上的阶段1中完成了五个项目，则我希望我的数据透视图显示为阶段1完成的十五个项目。

The Phase option and the Transition State options are common across both sheets. If there are ten items completed, in phase 1, on sheet 1, and five items completed, in phase 1, on sheet 2, I want my pivot chart to display fifteen items completed for phase 1.

看来@pnuts解决方案是我想要的，但我还是有麻烦。这是我的（新的和组合的）数据透视表：

It seems @pnuts solution is what I want, but I'm still having trouble. This is my (new and combined) PivotTable:

我做了这些步骤：

• 
  
• 多个合并范围/数据透视表


• 我将创建页面字段


• 我在第一张纸上选择了范围J：M，然后添加


• 我选择了另一张纸并选择范围C：F，然后添加


• 页面字段保留为0。


• 我选择了下一个。
>

• 我选择导出到现有工作表。

• CTRL, D, P on first sheet
• Multiple consolidation ranges / pivot table
• I will create the page fields
• I chose range J:M on the first sheet and then "add"
• I selected the other sheet and chose range C:F and then add (note, these are the same four columns, in same order, from both sheets).
• Page fields was left at 0.
• I chose Next.
• I chose to export to an existing worksheet.

它执行所有的计算， ：

It does all of its calculations and gives me this:

>

显然，这是不正确的。如果我改变计数总和，我得到0的一切。以下是一些基于我的 表的示例数据：

Obviously, this is incorrect. If I change count to sum, I get 0 for everything. Here is some sample data, based on my first sheet:

我做错了什么？

修改3 ：

@pnuts请求中的其他信息：

Additional info at @pnuts request:

img src =https://i.stack.imgur.com/xVmiU.pngalt =enter image description here>

推荐答案

从多个合并范围创建数据透视表（例如，请参见此处），然后对生成的表进行透视。

Create a pivot table from multiple consolidation ranges (eg see here) then pivot the resulting Table.

这是我得到的：

虽然我绘制了表格，但不打扰它。

though I charted the Table without bothering to pivot that.

这篇关于如果两个工作表没有唯一的通用属性，我如何将它们合并到一个枢轴表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！