如何将多个过滤器应用于数据透视表中的多个值字段？

I use the following data...

我使用以下数据......

...to create the following pivot table.

...创建以下数据透视表。

I want to apply the filter "FLORIDA SALES = Y" only to the first values column, and the filter "S.EAST SALES = Y" only to the second values column, to produce a pivot tables that looks like this:

我想将过滤器“FLORIDA SALES = Y”仅应用于第一个值列，将过滤器“S.EAST SALES = Y”仅应用于第二个值列，以生成如下所示的数据透视表：

I'm using colors here to show that I want each filter to filter only ONE of my value columns. I have 16,592 distinct UPCs so choosing to filter based on UPC is out of the question.

我在这里使用颜色来表明我希望每个过滤器只过滤一个值列。我有16,592个不同的UPC，所以选择基于UPC进行过滤是不可能的。

1 个解决方案

#1

Add 2 new columns which will be populated via formula as follows:

添加2个新列，将通过公式填充，如下所示：

1. "Florida Count" - =if( B1 = "Y", 1, 0) - where column B is "Florida Sales"
2. “Florida Count” - = if（B1 =“Y”，1,0） - 其中B列是“Florida Sales”
3. "SE Count" =if( C1 = "Y", 1, 0) - where column C is "S. East Sales"
4. “SE Count”= if（C1 =“Y”，1,0） - 其中C列为“S. East Sales”

Then you will use the pivot to sum these two new columns and you won't need any filtering.

然后，您将使用pivot来汇总这两个新列，您将不需要任何过滤。