自动逐行计算变化数值的平均值 [英] Auto calculate average over varying number values row by row

问题描述

我有一个Excel文件，其中包含多列和多行.假设A的一列具有ID号.另一列说G有价格.A列具有重复的ID号，但是并非所有的ID都重复相同的次数.有时只是一次，其他时候是2、3或几次.该行的每一列G都有一个唯一的价格.

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.

基本上，我需要对A列中给定ID的价格进行平均.如果每个ID重复相同的次数，这将非常简单，但是因为它们不是必需的，我必须手动为每个分组.由于我的电子表格有很多行，所以这是永远的事.

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.

这里是一个例子(H列是我当前正在手动计算的平均值):

Here is an example (column H is the average that I am currently calculating manually):

     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
...

因此，在上面的示例中，ID#1234的平均价格为3.50.同样，ID#3456的平均价格为3.98，#8890的平均价格为0.95.

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.

• 要注意第5行和第11行之间缺少行，第12行也缺少行吗?那是因为它们由于其他原因而被过滤掉了.我需要从计算中排除那些隐藏的行，而只计算可见行的平均值.

我正在尝试编写一个VBA脚本，该脚本将自动计算该值，然后打印H列中每个ID的平均值.

Im trying to write a VBA script that will automatically calculate this, then print that average value for each ID in column H.

这是我考虑过的一些代码:

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

我知道上述代码存在一些问题.我不太熟悉VBA.同样，这将每次在同一行上平均打印avg.我不确定如何迭代整行.

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.

这似乎过于复杂.从理论上讲，这是一个简单的问题，但是缺少行和ID#重复的次数不同，使得问题更加复杂.

This seems overly complicated. Its a simple problem in theory, but the missing rows and differing number of ID# repetitions makes it more complex.

如果可以在Excel函数中完成此操作，那就更好了.

If this can be done in an Excel function, that would be even better.

任何想法或建议将不胜感激.谢谢.

Any thoughts or suggestions would be greatly appreciated. thanks.

推荐答案

如果您可以在数据顶部添加另一行(将列标题置于其中)，则使用公式非常简单.

If you can add another row to the top of your data (put column Headers in it) its quite simple with a formula.

C2 的公式为

=IF(A2<>A1,AVERAGEIFS(B:B,A:A,A2),"")

将其复制为所有数据行.

copy this down for all data rows.

这适用于Excel 2007或更高版本.如果使用Excel 2003或更早版本，请改用 AVERAGEIF ，相应地调整范围

This applies for Excel 2007 or later. If using Excel 2003 or earlier, use AVERAGEIF instead, adjusting ranges accordingly

如果您无法添加标题行，请将第一个公式(单元格 C1 )更改为

If you can't add a header row, change the first formula (cell C1) to

=AVERAGEIFS(B:B,A:A,A1)

这篇关于自动逐行计算变化数值的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！