查找数据透视表过滤器的已检查元素的数量 [英] Find the number of checked elements of a pivot table filter

问题描述

我有一个数据透视表，其数据可以通过选择多个月来过滤。最后一列是一个总体数字，右边的列应该是每月平均值（总计/月数）。现在，平均值公式是 = IF（E34 =，，E34 / 3），即它是平均3个月的硬编码。但是，如果在数据透视表月份过滤器中选择了更多的月份，则这将不正确。有没有办法在数据透视表过滤器中查找所选项目的数量？

谢谢。

解决方案

要正式回答你的问题，你可以做一个counta公式，它查找不是空的单元格。这里不是一个好主意，因为你会有一个标题和总数，但可以做到 = COUNTA（F4：F17）-2 将不需要单元格数空，负2为标题，总行返回12，如果它引用了所有12个月过滤的数据透视表。

我的建议是做到这一点：

1. 添加您想要平均值到
   字段列表的值部分的字段（您可以选择相同的项目更多而不是一次在数据透视表中）


2. 左键单击它（它将在右下方），
   选择值字段设置



3. 现在，您有一个新的列，每月平均值


4. 如果您不需要总额，您可以直接编辑现有列

祝你好运！ p>

I have a pivot table whose data can be filtered by selecting multiple months. It's last column is a grand total figure, and the column to the right of that is supposed to be a monthly average (grand total / # of months). Right now the formula for average is =IF(E34="","",E34/3), i.e. it is hardcoded for the average of 3 months. However if more months are selected in the pivot table month filter, this will be incorrect. Is there a way to find the number of selected items in a pivot table filter?

Thanks.

解决方案

Yes, a few ways actually. To officially answer your question, you could do a counta formula, which looks for cells that are not empty. Not a good idea here because you'll have a header and grand total, but it can be done =COUNTA(F4:F17)-2 would take the number of cells not empty, minus 2 for the header and total row to return 12 if it were referencing a pivot table with all 12 months filtered in.

My advice would be to do this:

1. add the field you want an average of to the Values section of the field list (you can select the same item more than once in pivot tables)
2. left click on it (it'll be in the bottom right) and choose "Value Field Settings"
3. under Summarize value field by, select Average (by default, either count or sum will most likely be selected)
4. Now you have a new column with a monthly average
5. if you do not need the sum, you can simply edit the existing column

Good luck!

这篇关于查找数据透视表过滤器的已检查元素的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！