excel - 如何得到一个忽略错误的平均公式

Question

我正在尝试获取四个数据点的平均值。
问题在于可能会丢失一个或多个数据点。

平均值应该是最近四个星期一或最后四个星期二的平均值等。
每个数据点相距约 1000 行，因此我的想法是“列出”所需的日期并使用 vlookup 和平均值。

通用公式

// I only add two dates, but the same formula is repeated for four dates
=AVERAGE(VLOOKUP(DATE_1;Table;25;FALSE);VLOOKUP(DATE_2;Table;25;FALSE))

DATE_1 和 DATE_2 是前两个日期的动态计算，假设是星期一。
如果所有日期都存在，则此方法有效，但如果缺少一个星期一，VLOOKUP 将返回错误，并且无法将错误计算为平均值。
我想我可以用 IFERROR 包装 VLOOKUP，但我也无法让它工作

// for simplicity I removed the average and only show one. 
IFERROR(VLOOKUP(DATE_1;Table;25;FALSE);"") // returns empty string, can't calculate

IFERROR(VLOOKUP(DATE_1;Table;25;FALSE);0) // Works, but it skews the result with a zero.

我知道 AVERAGE 会跳过空单元格，但如何“模拟”空单元格。 "" 是空字符串，两者不同。
是否有可以处理错误并仍然给出平均值的公式，或者返回“空单元格”的公式？

Answer 1

这就是 the AGGREGATE function 的全部要点.

不要使用 AVERAGE(SomeRange)，而是使用 AGGREGATE(1, 6, SomeRange)。使用 AGGREGATE(1, 6, Value1, Value2)

而不是 AVERAGE(Value1, Value2)

1 告诉 AGGREGATE 计算 AVERAGE，6 告诉它“忽略错误值” 。完整的值列表位于本文底部

=AGGREGATE(1,6,VLOOKUP(DATE_1;Table;25;FALSE);VLOOKUP(DATE_2;Table;25;FALSE))

(正如人们所指出的，如果没有临时计算单元，这将无法正常工作 - 当您在函数中使用公式时，Excel 拒绝在引用表单中接受它)

引用表格:AGGREGATE(function_num, options, ref1, [ref2], …)
数组形式:AGGREGATE(function_num, options, array, [k])

Function_num | Function 
      1      | AVERAGE
      2      | COUNT
      3      | COUNTA
      4      | MAX
      5      | MIN
      6      | PRODUCT
      7      | STDEV.S
      8      | STDEV.P
      9      | SUM
     10      | VAR.S
     11      | VAR.P
     12      | MEDIAN
     13      | MODE.SNGL
     14      | LARGE
     15      | SMALL
     16      | PERCENTILE.INC
     17      | QUARTILE.INC
     18      | PERCENTILE.EXC
     19      | QUARTILE.EXC

 

Option | Behaviour
   0   | Ignore nested SUBTOTAL and AGGREGATE functions
   1   | Ignore hidden rows, nested SUBTOTAL and AGGREGATE functions 
   2   | Ignore error values, nested SUBTOTAL and AGGREGATE functions
   3   | Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
   4   | Ignore nothing
   5   | Ignore hidden rows
   6   | Ignore error values
   7   | Ignore hidden rows and error values