vba - FormulaArray 没有计算出所有指定条目的平均值

Question

表 1:

  G             H             I             J              K 
| Lane       | Bowler      | Score        | Score        | Score        |  1
|:-----------|------------:|:------------:|:------------:|:------------:|  
| Lane 1     |       Thomas|     100      |     100      |     100      |  2
| Lane 2     |      column |     200      |     200      |     100      |  3
| Lane 3     |        Mary |     300      |     300      |     100      |  4
| Lane 1     |        Cool |     150      |     400      |     100      |  5
| Lane 2     |       right |     160      |     500      |     100      |  6
| Lane 9     |       Susan |     170      |     600      |     100      |  7

假设我想找到出现在表 2 中的每个泳道的平均值并将它们放在 O 列中:

表 2:

  N             O
| Lane       | Average     | 1
|:-----------|------------:|
| Lane 1     |             | 2 
| Lane 2     |             | 3 
| Lane 3     |             | 4

我会放

=AVERAGE(IF(N2=$G$2:$G$7, $I$2:$K$7 )) for lane 1 (put this formula on cell "O2")

=AVERAGE(IF(N3=$G$2:$G$7, $I$2:$K$7 )) for Lane 2 ("O3")

=AVERAGE(IF(N4=$G$2:$G$7, $I$2:$K$7 )) for Lane 2 ("O4")

1. 我的第一个问题是如果我想找到表 2 中出现的所有泳道的平均值怎么办？所以泳道 1、泳道 2 和泳道 3 的平均值(但不是其他泳道，例如泳道 9)。

我的尝试:

= Average(IF(G2:G7 = N2:N4, I2:K:7)) why doesn't this work?

1. 我的第二个问题是我已经使用 vba 完成了“每个车道的平均值”:

.

Dim i As Integer

    For i = 2 To 4
        Cells(i, 15).FormulaArray = "=AVERAGE(IF(RC[-1]=R2C7:R7C7,R2C9:R7C12))"
    Next i

.

如果我在没有 .formula 方法的情况下使用 vba 完成它会怎样

For Lane 1 only:
pseudo code:


Loop from G2 to G7
If cell (N1) = Gx then      //x: 2 to 7
   Sum = Sum + Ix + Jx + Kx
}
Average = Sum/totalEntries

这会比我在 .formula 中使用构建更慢吗？这样做有什么好处吗？

Answer 1

第一个问题的答案，为什么这个 FormulaArray

= Average(IF(G2:G7 = N2:N4, I2:K7)) doesn't work?

隐含在其他 FormulaArray 的工作原理上:

= AVERAGE( IF( $G$7:$G$12 = $N7, $I$7:$K$12 ) ) 

让我们看看这个“单细胞公式数组”的每个部分是如何工作的:

• 第 1 部分:$G$7:$G$12 = $N7

公式的第一部分生成一个数组，其中包含范围 $G$7:$G$12 中符合条件 = $N7 的记录。 图。图 1 将 FormulaArray 的第一部分显示为“多单元格公式数组”。

• 第二部分:$I$7:$K$12

将第一部分的结果应用到第二部分，得到符合条件=$N7的分数范围(见图2)

• 第三部分:AVERAGE

最后公式的最后一部分计算符合条件= $N7的分数的平均值

现在让我们尝试对公式应用相同的分析:

=平均(如果(G2:G7 = N2:N4, I2:K7))

不幸的是，我们无法超越第一部分 G2:G7 = N2:N4，因为它无法尝试比较两个不同维度的数组，从而导致 #N/A (见图 3)

但是，即使数组具有相同的维度，结果也不会显示重复值，因为成员是一对一比较的(见图 4)

要获得泳道 1 到 3 的平均值，请使用此 FormulaArray

=AVERAGE( IF(
( $G$7:$G$12 = $N7 ) + ( $G$7:$G$12 = $N8 ) + ( $G$7:$G$12 = $N9 ),
 $I$7:$K$12 ) )

它生成一个数组，其中的记录符合条件 = $N7 + = $N8 + = $N9 ( + 相当于运算符 OR)

关于第二个问题:

性能与维护和效率有着内在的关联。

示例程序只是输入了一个硬编码的公式，并且只适用于这种特殊情况，例如:

• 如果需要更改公式来扩大范围，则必须更新宏，可能仍然需要更改公式，但无需打开 VBA 编辑器。
• 如果 G 列之前的任何列因为过时而被删除，则需要更新宏，而公式不需要任何维护，因为它们会自动更新。

引用没有.Formula方法的宏

我发现这是多余的，因为这就像编写一个算法来做一些可以用现有函数高效准确地完成的事情，因为这样的宏不会带来它实际上不存在的任何东西。

我将考虑在工作簿非常大并且大量使用资源显着降低工作簿性能的情况下编写这样一个过程的优势，但是该过程所提供的优势将不存在并且只是编写公式，但它必须计算结果并输入公式而不是公式产生的值，从而使最终用户的工作簿轻便、快速和流畅。