平均值为空值 [英] average with null values
问题描述
您好！
我有一个表格计算平均值如下：
" =（NZ（[value1]）+ Nz（[value2]）+ Nz（[value]））/ 3"
但我现在需要调整空值，所以例如，如果
value2为null，那么我需要将我的平均值仅基于2个值
而不是3我当前在我的字符串中使用。如何将
表格更新为3表格。根据填充的值的数量？
谢谢！
Hello!
I have a form that is calculating averages as follows:
" =(NZ([value1])+Nz([value2])+Nz([value]))/3 "
However I need to now adjust for null values , so for example if
value2 is null I would then need to base my average on just 2 values
instead of 3 i am currently using in my string. How can i have the
form update the "3" based on the number of values that are populated?
Thanks!
推荐答案
On 10 Ott，23：45，Toby Gallier< azr ... @ gmail.comwrote：
On 10 Ott, 23:45, Toby Gallier <azr...@gmail.comwrote:
你好！
我有一个计算平均值的表格如下：
" =（NZ（[value1]）+ Nz（[value2]）+ Nz（[value]））/ 3"
但我现在需要调整空值，所以例如，如果
value2为null，那么我需要将我的平均值仅基于2个值
而不是3我当前在我的字符串中使用。如何将
表格更新为3表格。基于填充的值的数量？
谢谢！
Hello!
I have a form that is calculating averages as follows:
" =(NZ([value1])+Nz([value2])+Nz([value]))/3 "
However I need to now adjust for null values , so for example if
value2 is null I would then need to base my average on just 2 values
instead of 3 i am currently using in my string. How can i have the
form update the "3" based on the number of values that are populated?
Thanks!
如何用3的总和替换3（在分母中）
IIFs
0当值为NULL或任何无效数字时，
和1否则？
注意换行符：
IIF（ISNULL（Value1）和ISNULL（Value2）和ISNULL（Value3），0，
（NZ（value1）+ NZ（value2）+ NZ（value3））/（IIF（ISNULL（ Value1），0,1）
+ IIF（ISNULL（Value2），0,1）+ IIF（ISNULL（Value3），0,1）））
[包括对3个零点的病理情况的额外检查]
-P
---------------- -------------------------------------------------- --------- * ------------------
为访问用户提供世界上最好的报告解决方案
http://www.datatime.eu/download.aspx
How about replacing the 3 (in the denominator) with a sum of three
IIFs
taking 0 when the value is NULL or any invalid number,
and 1 otherwise ?
watch for line breaks:
IIF( ISNULL(Value1) and ISNULL(Value2) and ISNULL(Value3), 0,
( NZ(value1) + NZ(value2) +NZ( value3) ) / (IIF(ISNULL(Value1),0,1)
+ IIF(ISNULL(Value2),0,1) + IIF(ISNULL(Value3),0,1)))
[includes an additional check for the pathological case of 3 nulls]
-P
---------------------------------------------------------------------------*------------------
Providing Access Users with the world''s best Reporting Solution
http://www.datatime.eu/download.aspx
这里有几个陷阱，包括划分的可能性零
如果所有3个字段都为空。
像这样（未经测试的）表达式应该有效：
IIf（[value1]为空，[value2]为空，[value3]为空，空，
（IIf（[value1]为空，0，[value1]）
+ IIf（[value2]是Null，0，[value2]）
+ IIf（[value3]是Null，0，[value3]））
/ - （（[value1]不为空）
+（[value2]不为空）
+（[value3]不为空）））
那里没有VBA函数调用，例如Nz（）或IsNull（）。 （JET有一个
本机IIf（）函数。）所以这应该给出最好的性能，并且应该不会弄乱数据类型（Nz（）的方式可以。）你可能需要调整
括号。
分母中的表达式依赖于Access使用-1表示
True。
您在字段间平均的事实可能意味着表格没有正确标准化。如果你有重复的字段（例如value1，value2，
value3，......），你很有可能会有很多*记录*
在相关表中，而不是此表上的许多字段。这将是孩子玩平均那些字段的玩法（使用总查询。）
-
Allen Browne - 微软MVP。西澳大利亚珀斯
访问用户提示 - http：// allenbrowne .com / tips.html
回复群组，而不是mvps dot org的allenbrowne。
" Toby Gallier" < az **** @ gmail.com写信息
news：11 ********************** @ y42g2000hsy.googlegr oups.com ...
There are several traps here, incluing the possibility of division by zero
if all 3 fields are null.
Something like this (untested) expression should work:
IIf( [value1] Is Null And [value2] Is Null And [value3] Is Null, Null,
(IIf([value1] Is Null, 0, [value1])
+ IIf([value2] Is Null, 0, [value2])
+ IIf([value3] Is Null, 0, [value3]))
/ - (([value1] Is Not Null)
+ ([value2] Is Not Null)
+ ([value3] Is Not Null)))
There a no VBA function calls there, such as Nz() or IsNull(). (JET has a
native IIf() function.) So that should give the best performance, and should
not mess up the data type (the way Nz() can.) You may need to adjust the
brackets.
The expression in the denominator relies on the fact that Access uses -1 for
True.
The fact that you are averaging across fields may mean the table is not
normalized correctly. If you have repeating fields (such as value1, value2,
value3, ...), there''s a very good chance that you should have many *records*
in a related table, instead of many fields on this table. It would then be
child''s play to average those fields (using a Total query.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Toby Gallier" <az****@gmail.comwrote in message
news:11**********************@y42g2000hsy.googlegr oups.com...
您好！
我有一个计算平均值的表格如下：
" =（NZ（[value1]）+ Nz（[value2]）+ Nz（[value]））/ 3"
但我现在需要调整空值，所以例如，如果
value2为null，那么我需要将我的平均值仅基于2个值
而不是3我当前在我的字符串中使用。如何将
表格更新为3表格。基于填充的值的数量？
谢谢！
Hello!
I have a form that is calculating averages as follows:
" =(NZ([value1])+Nz([value2])+Nz([value]))/3 "
However I need to now adjust for null values , so for example if
value2 is null I would then need to base my average on just 2 values
instead of 3 i am currently using in my string. How can i have the
form update the "3" based on the number of values that are populated?
Thanks!
10月10日，9：39 pm，Allen Browne < AllenBro ... @ SeeSig.Invalidwrote：
On Oct 10, 9:39 pm, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
这里有几个陷阱，包括划分为零的可能性
如果全部3个字段为空。
类似这样（未经测试的）表达式应该有效：
IIf（[value1]为空且[value2]是否为空且[value3]为空，空，
（IIf（[value1]为空，0，[value1]）
+ IIf（[value2]为空，0，[value2]）
+ IIf（[value3]是Null，0，[value3]））
/ - （（[value1]不是空的）
+（[value2]不是空的）
+（[value3]不是空）））
有一个没有VBA函数调用，例如Nz（）或IsNull（）。 （JET有一个
本机IIf（）函数。）所以这应该给出最好的性能，并且应该不会弄乱数据类型（Nz（）的方式可以。）您可能需要调整
括号。
There are several traps here, incluing the possibility of division by zero
if all 3 fields are null.
Something like this (untested) expression should work:
IIf( [value1] Is Null And [value2] Is Null And [value3] Is Null, Null,
(IIf([value1] Is Null, 0, [value1])
+ IIf([value2] Is Null, 0, [value2])
+ IIf([value3] Is Null, 0, [value3]))
/ - (([value1] Is Not Null)
+ ([value2] Is Not Null)
+ ([value3] Is Not Null)))
There a no VBA function calls there, such as Nz() or IsNull(). (JET has a
native IIf() function.) So that should give the best performance, and should
not mess up the data type (the way Nz() can.) You may need to adjust the
brackets.
好主意。有人做过任何性能测试吗？
Nice idea. Did anyone do any performance tests?
>
分母中的表达式依赖于Access使用-1表示
是的。
>
The expression in the denominator relies on the fact that Access uses -1 for
True.
您无需额外费用就会失去信赖：
/（IIf（[value1] IS NULL，0， 1）+ IIf（[value2] IS NULL，0,1）+
IIf（[value2] IS NULL，0,1））
You lose the reliance at no extra cost using:
/ (IIf([value1] IS NULL, 0, 1) + IIf([value2] IS NULL, 0, 1) +
IIf([value2] IS NULL, 0, 1))
>
您跨字段平均的事实可能意味着表格没有正确标准化。如果你有重复的字段（例如value1，value2，
value3，......），你很有可能会有很多*记录*
在相关表中，而不是此表上的许多字段。这将是孩子平均那些字段的游戏（使用总查询。）
>
The fact that you are averaging across fields may mean the table is not
normalized correctly. If you have repeating fields (such as value1, value2,
value3, ...), there''s a very good chance that you should have many *records*
in a related table, instead of many fields on this table. It would then be
child''s play to average those fields (using a Total query.)
我同意你的警告。值相似的事实足够平均在一起使得这种可能性更有可能。
James A. Fortune
CD ******** @ FortuneJames.com
I agree with your warning. The fact that the values are similar
enough to be averaged together makes the possibility even more likely.
James A. Fortune
CD********@FortuneJames.com
这篇关于平均值为空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
