Access女士:如何计算一列的平均值，而忽略另一列为0的行? [英] Ms Access: How can I take the average of a column but ignore the rows where another column is 0?

问题描述

我在报告中有2列，分别是qty_req和qty_issued.我需要在qty_issued列中找到平均值.问题是有时qty_req的对应值是0.我只需要对qty_req列否 0的行取qty_issued列的平均值.我该怎么办这个吗?

I have 2 columns, qty_req and qty_issued in a report. I need to find the average of the values in the qty_issued column. The problem is that sometimes the corresponding value of qty_req is 0. I need to take the average of the qty_issued column for only the rows where qty_req is NOT 0. How do I do this?

从这里其他问题中解脱出来:

Spun off from my other question here: MS Access: How can I average a list of quantities on a report where the quantities are not zero?

推荐答案

如果您想在报表文本框的控制源中执行此操作，则可以利用以下事实: Avg()忽略Null值.

If you want to do that in the Control Source of a text box on your report, you can take advantage of the fact that Avg() ignores Null values.

因此，当qty_req<> 0时，在平均的值中包括qty_issued.否则，请使用Null代替qty_issued值.

So, when qty_req <> 0, include qty_issued among the values which are averaged. Otherwise use Null instead of the qty_issued value.

=Avg(IIf(qty_req <> 0, qty_issued, Null))

如果您想在查询中执行此操作...

If you want to do it in a query instead ...

SELECT Avg(IIf(qty_req <> 0, qty_issued, Null)) FROM YourTable;

这篇关于Access女士:如何计算一列的平均值，而忽略另一列为0的行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！