☰

Excel 计算每 X 行数据的列平均值

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 计算每 X 行数据的列平均值

我有一个包含多个数据点的大型数据集。我附上了一张包含以下内容的图片：

    column A = IDs
    column B = Colors 
    column C = Data points need to average

例如图片

我需要仅前两行的平均值数据（C 列），其中 ID（A 列）= 1 且颜色（B 列）= 红色。然后我需要具有相同条件的下 2 行数据的平均值。然后我将执行相同的操作以找到 ID = 1 且颜色 = 蓝色的平均值。然后我将执行相同的操作以找到 ID = 2 且颜色 = 红色的平均值。

通常我会使用 averageifs 公式，但由于我只需要对前 X 行数据进行平均，因此我不确定如何解决这个问题。另外需要注意的是，并非每个 ID 都有相同数量的数据点。因此，ID 1 颜色红色可能有 4 个数据点，而 ID 2 颜色蓝色可能有 6 个数据点。任何帮助都将不胜感激。

答案1

谢谢 Iliana 的澄清。解决问题的关键是（形象地）将数据分成四行组。然后公式可以对该组中每种颜色的两个值求平均值。

您已经解释过，给定的 ID 可能有 7 到 10 行数据。因此并非所有组都有四行。以下方法使用辅助列为每个 ID 指定四行（或更少）的组。

下表针对所示的四个 ID 分别有 7、8、9 和 10 行，并且此公式从下表中的 D2 向下填充，将每个 ID 的数据分成 4 个或更少的组，并使用 0 到 2 之间的数字指定组：

=IF(COUNTIF(A$2:A2,A2)>8,2,IF(COUNTIF(A$2:A2,A2)>4,1,0))

此公式计算每个 ID 向下填充时的出现次数。如果计数 > 8，则将助手设置为 2。如果计数 > 4，则将助手设置为 1。如果计数为 4 或更少，则将助手设置为 0。

在此处输入图片描述

现在可以轻松计算平均值。此公式给出每个唯一 ID、颜色和助手组合的结果值的平均值：

=AVERAGEIFS(C$2:C$35,A$2:A$35,A2,B$2:B$35,B2,D$2:D$35,D2)

最后，只需稍加努力即可消除重复值。此公式计算范围内 ID/Helper 组合的数量，该范围随着公式的填充而“增长”。如果计数为 3 或更多，则返回空白而不是平均值。

=IF(COUNTIFS(D$2:D2,D2,A$2:A2,A2)<3, AVERAGEIFS(C$2:C$35,A$2:A$35,A2,B$2:B$35,B2,D$2:D$35,D2),"")

再次感谢您清楚地解释您想要实现的目标。希望这对您有所帮助。祝您好运。

答案2

您可以结合使用 SUMIFS 和 COUNTIFS 函数来计算 ID 和颜色对的平均值，以创建数据点（匹配行），其中 COUNTIFS 创建数据点

=SUMIFS(C2:C17,A2:A17,"1",B2:B17,"RED")/COUNTIFS(A2:A17,"1",B2:B17,"RED")


=SUMIFS(C2:C17,A2:A17,"1",B2:B17,"BLUE")/COUNTIFS(A2:A17,"1",B2:B17,"BLUE")


=SUMIFS(C2:C17,A2:A17,"2",B2:B17,"RED")/COUNTIFS(A2:A17,"2",B2:B17,"RED")

我希望这对你有帮助。

相关内容