Excel：平均列和自动填充子表

Question

我对 Microsoft Excel (2016) 非常陌生。

我有一个数据集，我想计算某一列中某个位置的所有数据点的平均值。示例：

**Days Open**   **Site**
Not Closed  
Not Closed  Boulder, USA
1           Boulder, USA
1           Boulder, USA
Not Closed  Eden Prairie, USA
Not Closed  Boulder, USA
4           Boulder, USA
Not Closed  Boulder, USA
Not Closed  Boulder, USA
Not Closed  Boulder, USA
1           Boulder, USA
Not Closed  Boulder, USA
Not Closed  Eden Prairie, USA
Not Closed  Boulder, USA
Not Closed  Boulder, USA
7           Boulder, USA
Not Closed  Boulder, USA
Not Closed  Boulder, USA
Not Closed  Boulder, USA
Not Closed  Cluj, Romania
Not Closed  Boulder, USA
Not Closed  Nanjing, China
8          Boulder, USA
4          Boulder, USA
9          Nanjing, China
6          Boulder, USA
1          Boulder, USA
Not Closed  Boulder, USA
Not Closed  Boulder, USA
2          Nanjing, China

我想完全忽略“未关闭”的数据点。

我的最终目标是显示......

Boulder, 21
Nanjing, 18 ... etc

每个“站点”及其所有“开放天数”的平均值。

谢谢你！

Answer 1

您可以尝试使用AVERAGEIF。（在本例中，我们将其放在 E 列）

就您而言，假设您使用 A 列和 B 列，则可以这样写：

=AVERAGEIF(B$2:B$100,D1,A$2:A$100)

其中单元格D1包含您想要求和的值，例如 Boulder。