如何根据其他单元格中的条件计算平均值

Question

也许最好用表格来解释这个问题，而不是用文字。

我试图将这个表格转换成：

Site | Year | Value
A    | 1999 | 5
A    | 1999 | 10
A    | 2001 | 12
B    | 1999 | 6
C    | 1996 | 4
C    | 1996 | 10

将其转化为以下表格：

Site | Year | Value | Year-Average | Site-Average
A    | 1999 | 5     | 7.5          | 9.75
A    | 1999 | 10    | 7.5          | 9.75
A    | 2001 | 12    | 12           | 9.75
B    | 1999 | 6     | 6            | 6
C    | 1996 | 4     | 7            | 8.5
C    | 1996 | 10    | 7            | 8.5

如果您注意到，年平均数会对同一网站的同一年份中重复的数值进行平均。然后，站点平均数会从年平均数中计算出其平均值。

我可以手动完成这个过程，但我的真实数据集有成千上万行，因此我正在寻找可重复使用的过程来处理这些数据。

- amoeba

---

我发布了一个想法，但删除了它，因为我无法得到与您展示的相同答案。网站A有5+10+12=27，所以平均值应该是9，对吗？ - Gene

你是怎么得出“C”为8.5的？难道不应该是7吗？ - Siddharth Rout

Answer 1

年度平均数的公式为：

=AVERAGE(IF(($A$2:$A$7=A2)*($B$2:$B$7=B2),$C$2:$C$7,""))

并为站点平均值计算：

=AVERAGE(IF($A$2:$A$7=A2,$D$2:$D$7,""))

将其作为数组公式输入到第一个单元格中，并使用Ctrl-Shift-Enter复制整个列。 如果需要在计算站点平均值时排除重复年份，则公式会变得稍长：

=AVERAGE(IF(($A$2:$A$8=A2)*(FREQUENCY(($A$2:$A$7=A2)*($B$2:$B$7=B2),($A$2:$A$7=A2)*($B$2:$B$7=B2))>0),$D$2:$D$7,""))

请注意，第一个输入范围$A$2:$A$8中有一行额外的内容，而不是$A$2:$A$7。

- panda-34

---

我相信你的第二个公式是不正确的。它会给出"A"的值为9。正确的值实际上是9.75 :) - Siddharth Rout

@SiddharthRout 从问题中不清楚OP想要什么类型的平均值。如果您的意思是排除重复项 - 我已经更新了答案，使用了一个公式。而且输入范围也不必排序 ;) - panda-34

这看起来像是我需要的解决方案。而且我确实需要排除重复项，所以你的第三个方程很棒！ - amoeba

Answer 2

使用这些数组公式。在单元格中输入公式后，需要按下 CTL + SHIFT + ENTER。

对于 年度平均值，请将其放在单元格 D2 中。

=SUMPRODUCT(($A$2:$A$7=A2)*($B$2:$B$7=B2)*($C$2:$C$7))/COUNT(IF($A$2:$A$7=A2,IF($B$2:$B$7=B2,$C$2:$C$7)))

我不确定，你是如何得出C的8.5的。

快照：

编辑：

这是获取网站平均值的方法：)

要实现您想要的网站平均值，我们必须使用帮助列。请参见快照

在单元格E2中放置此公式

=D2

在 E3 单元格中输入以下公式：

=IF(OR(A3<>A2,B3<>B2),D3,"")

并将其向下拖动。

现在在 F2 单元格中输入此公式

=AVERAGE(IF($A$2:$A$7=A2,$E$2:$E$7,""))

按下CTL+SHIFT+ENTER。

将公式向下复制。

如果需要，隐藏E列。

- Siddharth Rout

---

感谢您所做的工作。您的方程式和panda-34的方程式可能正是我所需要的。 - amoeba

Answer 3

您可以使用数据透视表按年份和站点计算平均值，然后使用vlookup将相关平均值附加到初始表的每一行。

- davesnitty