在主表中减去两列

My source data looks like this screenshot

我的源数据看起来像这个屏幕截图

I would like to know

我想知道

1. How many purchase order numbers (column po no) has each project?
2. 每个项目有多少个采购订单号(po no) ?
3. How many po no I have received in admin until now?
4. 到目前为止，我在管理部门收到了多少po no ?
5. How many remain?
6. 保持多少?

Using a pivot table i solved my first and second question. To calculate the value for the sum of remain column i tried to use a calculated field but I got wrong results (see below).

使用透视表，我解决了第一个和第二个问题。为了计算余列和的值，我尝试使用一个计算字段，但得到了错误的结果(见下面)。

Desired result and question

I would like to achieve to get a pivot table like the example table below.

我希望获得如下示例表所示的数据透视表。

How can create a pivot table where the colum remain is the result of count of po no-count of po_in_admin?

如何创建一个pivot表，其中的colum是po_in_admin的po no-count的结果?

2 个解决方案

#1

One comment linked this youtube tutorial Learn Excel 2013 - "Subtract in a Pivot Table": Podcast #1655 by Bill Jelen and Bill gives these steps

一个评论链接了youtube教程“学习Excel 2013”——“在数据透视表中减去”:播客#1655由Bill Jelen和Bill给出了这些步骤

• on the design tab change the report layout of the pivot-table to tabular form
• 在design选项卡上，将数据透视表的报表布局更改为表格形式
• under options click the button Field Settings under the tab Subtotals & Filters set the radio-button under subtotals to none and click ok
• 在“选项”下，单击“选项卡小计和过滤器”下的按钮字段设置，将“小计”下的无线电按钮设置为none，然后单击“确定”
• now select the first column (in your case count of po no) - ‘count of po_in_admin’
• 现在选择第一列(在您的案例中是po no)——“po_in_admin的计数”
• under options click on Calculations click on Field Items & Set and pick calculate elements enter a column name (for example Delta po no) click the first entry add - click the second entry and hit ok
• 在“选项”下单击“计算”，单击“字段项”并选择“计算元素”，输入一个列名(例如po no)，单击“第一个条目添加”，单击“第二个条目”并单击“确定”

#2

Okay, no need for a difference column.

好的，不需要不同的列。

Do what you did above:

做你上面所做的:

Right click on one cell with data that you would like to calculate the difference of (note you will deal with the entire row in a minute).

右键单击一个单元格，其中包含您希望计算的数据(注意，您将在一分钟内处理整个行)。

Click insert formula.

点击插入公式。

Write (insert first header - insert second header).

写入(插入第一个页眉-插入第二个标题)。

Change name to your liking.

根据你的喜好更改名字。

Press ok

按ok

You should then see a new field within your four quadrants ("diff"). Click the info button and change the type from Count to SUM (up on the top). Let me know if that fixes it. The sum will actually calculate the formula associated with the one you just inserted into the pivot table.

然后，您应该在您的四个象限(“diff”)中看到一个新的字段。单击info按钮，将类型从计数更改为SUM(在顶部)。如果修好了，请告诉我。求和将实际计算与刚才插入到pivot表中的公式相关的公式。