vba 逐行自动计算不同数值的平均值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17207351/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Auto calculate average over varying number values row by row
提问by brno792
I have an Excel file with several columns in it and many rows. One column, say A has ID numbers. Another column, say G has prices. Column A has repeating ID numbers, however not all numbers repeat the same amount of times. Sometimes just once, other times 2, 3 or several times. Each column G for that row has a unique price.
我有一个 Excel 文件，其中包含多列和多行。一列，假设 A 有 ID 号。另一列，说 G 有价格。A 列具有重复的 ID 编号，但并非所有编号都重复相同的次数。有时只是一次，有时是 2、3 或几次。该行的每一列 G 都有一个唯一的价格。
Basically, I need to average those prices for a given ID in column A. If each ID was repeated the same number of times, this would be quite simple, but because they are not I have to manually do my average calculation for each grouping. Since my spreadsheet has many many rows, this is taking forever.
基本上，我需要为 A 列中的给定 ID 平均这些价格。如果每个 ID 重复相同的次数，这将非常简单，但因为它们不是我必须手动为每个分组进行平均计算。由于我的电子表格有很多行，这需要永远。
Here is an example (column H is the average that I am currently calculating manually):
这是一个示例（H 列是我目前手动计算的平均值）：
A ... G H
1 1234 3.00 3.50
2 1234 4.00
3 3456 2.25 3.98
4 3456 4.54
5 3456 5.15
11 8890 0.70 0.95
13 8890 1.20
...
So in the above example, the average price for ID# 1234 would be 3.50. Likewise, the average price for ID# 3456 would be 3.98 and for #8890 would be 0.95.
因此，在上面的示例中，ID# 1234 的平均价格将为 3.50。同样，ID# 3456 的平均价格为 3.98，#8890 的平均价格为 0.95。
- NOTICE how rows are missing between row 5 and 11, and row 12 is missing too? That is because they are filtered out for some other reason. I need to exclude those hidden rows from my calculations and only calculate the average for the rows visible.
- 注意第 5 行和第 11 行之间的行如何丢失，第 12 行也丢失了？那是因为它们因某些其他原因被过滤掉了。我需要从我的计算中排除那些隐藏的行，只计算可见行的平均值。
Im trying to write a VBA script that will automatically calculate this, then print that average value for each ID in column H.
我正在尝试编写一个 VBA 脚本来自动计算它，然后在 H 列中打印每个 ID 的平均值。
Here is some code I have considered:
这是我考虑过的一些代码：
Sub calcAvg()
Dim rng As Range
Set rng = Range("sheet1!A1:A200003")
For Each Val In rng
Count = 0
V = Val.Value '''V is set equal to the value within the range
If Val.Value = V Then
Sum = Sum + G.Value
V = rng.Offset(1, 0) '''go to next row
Count = Count + 1
Else
'''V = Val.Value '''set value in this cell equal to the value in the next cell down.
avg = Sum / Count
H = avg '''Column G gets the avg value.
End If
Next Val
End Sub
I know there are some problems with the above code. Im not too familiar with VBA. Also this would print the avg on the same line everytime. Im not sure how to iterate the entire row.
我知道上面的代码有一些问题。我对 VBA 不太熟悉。此外，这每次都会在同一行上打印平均值。我不确定如何迭代整行。
This seems overly complicated. Its a simple problem in theory, but the missing rows and differing number of ID# repetitions makes it more complex.
这似乎过于复杂。理论上这是一个简单的问题，但缺少行和不同的 ID# 重复次数使其变得更加复杂。
If this can be done in an Excel function, that would be even better.
如果这可以在 Excel 函数中完成，那就更好了。
Any thoughts or suggestions would be greatly appreciated. thanks.
任何想法或建议将不胜感激。谢谢。
回答by chris neilsen
If you can add another row to the top of your data (put column Headers in it) its quite simple with a formula.
如果您可以在数据顶部添加另一行（将列标题放入其中），则使用公式非常简单。
Formula for C2is
公式C2就是
=IF(A2<>A1,AVERAGEIFS(B:B,A:A,A2),"")
copy this down for all data rows.
将其复制到所有数据行。
This applies for Excel 2007 or later. If using Excel 2003 or earlier, use AVERAGEIFinstead, adjusting ranges accordingly
这适用于 Excel 2007 或更高版本。如果使用 Excel 2003 或更早版本，请AVERAGEIF改用，并相应地调整范围
If you can't add a header row, change the first formula (cell C1) to
如果您无法添加标题行，请将第一个公式（单元格C1）更改为
=AVERAGEIFS(B:B,A:A,A1)
回答by matzone
In my way ..
以我的方式 ..
Sub calcAvg()
Dim x, y, i, y2, t, Count, Mount As Integer
Dim Seek0 As String
x = 1 '--> means Col A
y = 1 '--> means start - Row 1
y2 = 7 '--> means end - Row 19
For i = y To y2
If i = y Then
Seek0 = Cells(i, x)
t = i
Count = Cells(i, x + 6)
Mount = 1
Else
If Cells(i, x) <> Seek0 Then
Cells(t, x + 7) = Count / Mount
Count = Cells(i, x + 6)
Mount = 1
t = i
Seek0 = Cells(i, x)
Else
Count = Count + Cells(i, x + 6)
Mount = Mount + 1
End If
End If
Next
End Sub
Hope this helps ..
希望这可以帮助 ..
