excel在单列中有多个平均值 [英] excel multiple average in single column

问题描述

我需要我的Excel电子表格帮助.在第一栏中，我有产品编号，在第二栏中，有执行时间:
A B C
1 0:17
1 0:18
1 0:19 0:18
2 1:12
2 1:12
2 1:13 1:12:6
3 0:45 0:45
我想在下一列(C列)中为每种产品取平均值.我尝试过:

I would like help with my excel spreadsheet. In one column I have product number in the second column there is execution time:
A B C
1 0:17
1 0:18
1 0:19 0:18
2 1:12
2 1:12
2 1:13 1:12:6
3 0:45 0:45
I would like to make an average for every product in the next column (column C). I have tried:

=IF(A1=A2;0;AVERAGE(IF(A:A=A1;B:B)))

但是它失败了.因此，我的问题是如何自动在A中选择一个具有相同数字的范围以求平均值并在该产品的末尾显示它?

but it fails miserably. So my question is how can I automatically select a range in A with the same number to make an average and display it at the end of that product?

我使用excel 2003.

I use excel 2003.

谢谢您的帮助.

推荐答案

您的方法可以使用，但是公式需要输入数组".最好在复制的C1中使用像这样的 AVERAGEIF 函数

Your way will work but the formula needs to be "array entered". Better to use AVERAGEIF function like this in C1 copied down

= IF(A1 = A2;"; AVERAGEIF(A $ 1:A1; A1; B $ 1:B1))

对于Excel 2003或更早版本，您可以使用此数组公式"(本质上就是原来的格式)

For Excel 2003 or earlier versions you can either use this "array formula" (essentially what you had originally)

= IF(A1 = A2;"; AVERAGE(IF(A $ 1:A1 = A1; B $ 1:B1)))

通过 CTRL + SHIFT + ENTER 确认，然后向下复制

confirmed with CTRL+SHIFT+ENTER then copied down

....或使用此非数组版本

....or use this non-array version

= IF(A1 = A2;"; SUMIF(A $ 1:A1; A1; B $ 1:B1)/COUNTIF(A $ 1:A1; A1))

这篇关于excel在单列中有多个平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！