vb.net 如何计算值大于 0 的数据透视表的行数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20805348/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to count rows of pivot table where value is greater than 0
提问by Benjamin Beaulieu
I have a SQL view that returns rows containing a couple of "value" columns. For the sake of simplicity, let's call them AmountA, AmountBand AmountC.
我有一个 SQL 视图，它返回包含几个“值”列的行。为简单起见，我们称它们为AmountA,AmountB和AmountC。
I'm using a pivot table in Excel to create a report for that data. The source range of the pivot table is generically generated using .Net reflection, then pushed to Excel using Excel Interop. The pivot table is also dynamically generated/configured using Excel Interop.
我在 Excel 中使用数据透视表为该数据创建报告。数据透视表的源范围一般使用 .Net 反射生成，然后使用 Excel Interop 推送到 Excel。数据透视表也是使用 Excel Interop 动态生成/配置的。
I need to calculate and display a field that will count the number of rows where AmountAis greater than 0. I also need to do the same for AmountBand AmountC. The result should be displayed in the pivot table, on the same row level, in the following order : AmountA- CountA- AmountB- CountB- AmountC- CountC. To better understand what I'm trying to accomplish, think about a pivot table field summarized by count, but with a behavior similar to the COUNTIFfunction. For example, if the group ProductXhas 10 rows, with only 8 rows having AmountAgreater than 0, it should display a CountAof 8.
我需要计算并显示一个字段，该字段将计算AmountA大于 0的行数。我还需要对AmountBand执行相同的操作AmountC。结果应显示在数据透视表中，在同一行级别，按以下顺序：AmountA- CountA- AmountB- CountB- AmountC- CountC。为了更好地理解我要完成的任务，请考虑按计数汇总的数据透视表字段，但其行为类似于该COUNTIF函数。例如，如果组ProductX有 10 行，只有 8 行AmountA大于 0，它应该显示CountA为 8。
I already tried to add a calculated field with the formula =IF(AmountA>0,1,0)and then sum on this field, but it seems that the formula is applied to the grouped value, not to the actual AmountAfor each row. For example, if the Sum of AmountAfor the group ProductXis greater than 0, it will display a count of 1 (Sum of AmountA> 0) even if the actual count of rows having AmountAgreater than 0 is 8.
我已经尝试用公式添加一个计算字段，=IF(AmountA>0,1,0)然后在该字段上求和，但似乎该公式应用于分组值，而不是AmountA应用于每一行的实际值。例如，如果Sum of AmountA组ProductX的 大于 0，Sum of AmountA即使实际AmountA大于 0的行数为 8 ，它也会显示 1 ( > 0) 的计数。
I also considered adding new columns in the source range that would use formulas similar to =IF(AmountA>0,1,0), then include them with a sum in the pivot table. However, I would like to avoid this, since I'm generically generating the source range and adapting this would require a significant amount of refactoring.
我还考虑在源范围中添加新列，这些列将使用类似于 的公式=IF(AmountA>0,1,0)，然后将它们与总和一起包含在数据透视表中。但是，我想避免这种情况，因为我通常会生成源范围并对其进行调整需要大量重构。
I thought about adding additional columns to the view to represent the count, but I don't think the view should be responsible of presentingthe data; this would be a job for the report. Additionnally, I can't see how a CountAcolumn would be relevant to each single row, since it either has or doesn't have an AmountAgreater than 0.
我想过在视图中添加额外的列来表示计数，但我认为视图不应该负责呈现数据；这将是报告的一项工作。此外，我看不出一CountA列与每一行的相关性，因为它要么具有AmountA大于 0的值，要么不具有大于 0 的值。
So, is it possible to reproduce the COUNTIFbehavior on a pivot table field, or should I consider handling the count elsewhere?
那么，是否可以COUNTIF在数据透视表字段上重现该行为，还是应该考虑在其他地方处理计数？
采纳答案by Benjamin Beaulieu
I finally resigned to hack new columns into the source range defining formulas similar to =IF(AmountA>0,1,0), and then using those column with a count summary in the pivot table.
我终于辞职了，将新列添加到源范围中定义类似于 的公式=IF(AmountA>0,1,0)，然后在数据透视表中使用那些带有计数摘要的列。
回答by Andre Silva
Consider the following data example:
考虑以下数据示例：
AmountA AmountB AmountC
2 1 2
0 0 0
3 5 1
4 0 2
5 0 0
0 6 2
2 0 0
You could use the PivotTable tool "Group" to aggregate values equal 1 and higher (i.e. separating them from values equal to zero).
您可以使用数据透视表工具“分组”来聚合等于 1 和更高的值（即将它们与等于 0 的值分开）。
Set:
放：
- "Starting at" = 1,
- "Ending at" = some big number (put 100 in the below example),
- "By" = same big number from previous step.
- “开始于”= 1，
- “结束于” = 一些大数字（在下面的例子中输入 100），
- “By” = 与上一步相同的大数字。
The only problem is that you would need to create one pivot table for each variable (A,B and C).
唯一的问题是您需要为每个变量（A、B 和 C）创建一个数据透视表。
See the below screenshot, for better understanding:
请参阅下面的屏幕截图，以便更好地理解：
If you hide columns D, E, H and I it seems you have the desired output.
如果您隐藏 D、E、H 和 I 列，则似乎您有所需的输出。
