Filter 0-values in PivotTable

问题:

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

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

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.

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?

回答1:

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

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.

回答2:

Here's a slightly different way to do it, which only requires one table, and allows you to filter and display values in your pivot table:

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

The formula in cell D2 is =SUMIF($A:$A,$A2,C:C) Copy this formula down the column.

Then set Sum of Returned Quantity as your filter, and it should work correctly. Additional columns may be added for additional filters and you can hide the subtotal column(s) if you wish.