筛选数据透视表中的0值 [英] Filter 0-values in PivotTable

问题描述

我有以下Excel电子表格:

I have the following Excel spreadsheet:

      A                  B                    C
    Product         Sent Quantity       Returned Quantity
1   Product A           500                     0
2   Product A           400                   300
3   Product A           600                   400
4   Product B           250                     0
5   Product B           300                   150
6   Product C           700                     0

该表显示了产品的销售额( B列)和退货( C列).

The table shows the sales (Column B) an returns (Column C) of a product.

我根据上面的数据创建了数据透视表，这导致了以下结果:

I created a Pivot-Table out of the data above which leads to the follwoing result:

                Sum of Sent Quantity          Sum of Returned Quantity
Product A           1.500                          700
Product B             550                          150
Product C             700                            0

现在，我将退货数量用作报告过滤器. 我将"150"设置为过滤条件，并得到以下结果:

Now I use the Returned Quantity as a Report Filter. I set "150" as the filter criteria and I get the following result:

                 Sum of Sent Quantity          Sum of Returned Quantity
Product B             550                          150

到目前为止，一切正常.

So far everything works fine.

现在，我将过滤器从"150"更改为"0"，并得到以下结果:

Now I change the filter from "150" to "0" and I get the following result:

                Sum of Sent Quantity          Sum of Returned Quantity
Product A              500                            0
Product B              250                            0
Product C              750                            0

但是，我的目标结果是:

However, my target result is:

              Sum of Sent Quantity          Sum of Returned Quantity
Product C              700                            0

要达到目标结果，我需要更改什么?

What do I have to change to reach my target result?

推荐答案

因此，我已经在"Sheet1"中输入了您的数据，如下所示:

So, I have entered your data in "Sheet1" like this:

然后，在"Sheet2"中创建以下表格:

Then, in "Sheet2" I made folowing table:

这是您的数据透视表.但是单元格是用公式定义的:

Which is your pivot table. But cells are defined with formulas:

B2:=SUMIF(Sheet1!$A$2:$A$7,Sheet2!A2,Sheet1!$B$2:$B$7)

C2:=SUMIF(Sheet1!$A$2:$A$7,Sheet2!A2,Sheet1!$C$2:$C$7)

其余的只是将其向下拖动(如您所知，它将自动调整).现在，只需锁定第一行，然后就可以对其应用任何过滤器.

The rest is just matter of draging it down (it will adjust automatically, as you know). Now, just lock first row and then you can apply any filters to it.

这篇关于筛选数据透视表中的0值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！