我正在嘗試在 VBA 中做一些事情，但我不確定如何去做。
假設我在 Excel 中有如下內容：
Group Rate
1 20%
2 35%
3 31%
3 24%
3 25%
1 15%
3 22%
4 50%
2 50%
2 32%
我想計算每組的平均值，每次找到新的組號時都會重置，即：
Group Rate Final_Rate
1 20% 20%
2 35% 35%
3 31% 26.67%
3 24% 26.67%
3 25% 26.67%
1 15% 15%
3 22% 22%
4 50% 50%
2 50% 41%
2 32% 41%
換句話說，對于直接彼此跟隨的具有相同 Group 的行，我希望獲得該組中每一行的比率的平均值。
我怎樣才能在 VBA 中做這樣的事情？
我們可以假設 Group 和 Rate 的值分別填充在 A 列和 B 列中。
我是一個初學者，我試過寫一些虛擬代碼，但我不知道我是否在正確的軌道上。
Sub Calculate_Funny_Average()
Dim sh As Worksheet
Dim rw As Range
Dim RowCount As Integer
Set sh = Worksheets("Sheet1")
For Each rw In sh.Rows
If sh.Cells(rw.Row, 1).Value <> "" Then
If sh.Cells(rw.Row, 1).Value = "" Then
Exit For
End If
Cur_Group = sh.Cells(rw.Row, 1).Value
Cur_Rate = sh.Cells(rw.Row, 4)
Next_Group = sh.Cells(rw.Row 1, 1).Value
Do While Cur_Group = Next_Group
'Something ??
Loop
End If
Next rw
End Sub
uj5u.com熱心網友回復：
Option Explicit
Sub test()
Dim arr
Dim i As Integer, m As Integer, x As Integer
arr = Cells(1, 1).CurrentRegion 'catch all data to an array. Make sure there are headers Group, Rate and Final Rate
For i = LBound(arr) 1 To UBound(arr) 'loop all rows, without the header
arr(i, 3) = CDbl(arr(i, 2)) 'the first one must be recorded. cdbl() converts to Double type
x = 1 'x will be the times the group is repeated
If i x <= UBound(arr) Then 'ifthe row times the group appears is bigger than number of data rows,quit looping, else
Do While arr(i, 1) = arr(i x, 1) 'while the group below is the same as the "i"
arr(i, 3) = arr(i, 3) CDbl(arr(i x, 2)) 'record the value the value below
x = x 1 'add 1 to x to see the next row
If i x > UBound(arr) Then 'again, if is i x is bigger than the array, quit
Exit Do
End If
Loop
End If
For m = x - 1 To 0 Step -1 'now, let's divide. Step-1 is to keep the original number and for the last one being changed
arr(i m, 3) = arr(i, 3) / x
'if you want, record the value with %
arr(i m, 3) = Format(arr(i m, 3), "0.00 %")
Next m
i = i x - 1
Next i
Range(Cells(1, 1), Cells(UBound(arr), UBound(arr, 2))) = arr 'print the array over the original range
End Sub
uj5u.com熱心網友回復：
使用公式的一種方法涉及添加輔助列。當“組”更改時，輔助列將更改值。
我把輔助欄放在作業表的其他地方，但你可以把它放在任何你想要的地方，和/或隱藏它
輔助欄
E2: 1
E3: =IF($A3=A2,E2,E2 1)
并填寫
Final_Rate
C2: =AVERAGEIFS(Sheet1!$B$2:$B$11,Sheet1!$E$2:$E$11,Sheet1!$E2)
并填寫
如果您的 Office 365 帶有XLOOKUP，則可以使用@ScottCraner 在評論中提到的更復雜的公式，并避免使用輔助列。
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/372600.html