容差平均值(忽略 #NA 等) [英] Tolerant average (ignore #NA, etc.)
问题描述
我想计算一个范围内的平均值(图中的 B1:B12 或 C1:C12)，不包括:
I want to calculate the average over a range (B1:B12 or C1:C12 in the figure), excluding:
- 非数字单元格，包括空字符串、没有内容的空白单元格、
#NA、文本等(此处为 B1+B8:B12 或 C1+C8:C12). - 范围内的相应单元格(此处为 A1:A12)具有区间(此处为 [7,35])之外的值的单元格.这将进一步排除 B2:B3 或 C2:C3.此时，A 列中的单元格可能包含数字或没有内容.
- Cells not being numeric, including Empty strings, Blank cells with no contents,
#NA, text, etc. (B1+B8:B12 or C1+C8:C12 here). - Cells for which corresponding cells in a range (A1:A12 here) have values outside an interval ([7,35] here). This would further exclude B2:B3 or C2:C3. At this point, cells in column A may contain numbers or have no contents.
我认为不可能使用任何内置的 AVERAGE 之类的函数.然后，我尝试计算总和、计数和除法.当我在范围内有 #N/A 时，我可以计算计数(F2 和 F7)，但不能计算总和(F3)，例如
I think it is not possible to use any built-in AVERAGE-like function. Then, I tried calculating the sum, the count, and divide. I can calculate the count (F2 and F7), but not the sum (F3), when I have #N/A in the range, e.g.
我该怎么做?
注意事项:
- G 列显示 F 列中的公式.
- 我无法过滤和使用
SUBTOTAL. - B8:C8 包含没有内容的空白单元格，B9:C9 包含空字符串.
- 我正在寻找(非用户定义的)公式，即非 VBA.
推荐答案
您可以通过使用基于嵌套 IF 的数组公式来实现这一点，以提供至少部分条件.当 IF 解析为 FALSE 时，它不再处理语句的 TRUE 部分.
You can accomplish this by using array formulas based upon nested IFs to provide at least part of the criteria. When an IF resolves to FALSE it no longer process the TRUE portion of the statement.
F2:F3 中的数组公式是，
=SUM(IF(NOT(ISNA(B2:B13)), (A2:A13>=7)*(A2:A13<=35)*(B2:B13<>"")))
=SUM(IF(NOT(ISNA(B2:B13)), IF(B2:B13<>"", (A2:A13>=7)*(A2:A13<=35)*B2:B13)))
F7:F8 中的数组公式是，
=SUM(IF(NOT(ISNA(C2:C13)), (A2:A13>=7)*(A2:A13<=35)*(C2:C13<>"")))
=SUM(IF(NOT(ISNA(C2:C13)), IF(C2:C13<>"", (A2:A13>=7)*(A2:A13<=35)*C2:C13)))
数组公式需要使用 Ctrl+Shift+Enter↵ 完成.正确输入后，如有必要，它们可以像任何其他公式一样填写.
Array formulas need to be finalized with Ctrl+Shift+Enter↵. Once entered correctly, they can be filled down like any other formula if necessary.
数组公式随着它们所指范围的扩大而以对数方式增加计算负载.尽量减少多余的空白行并避免完整的列引用.
Array formulas increase calculation load logarithmically as the range(s) they refer to expand. Try to keep excess blank rows to a minimum and avoid full column references.
这篇关于容差平均值(忽略 #NA 等)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
