#excel #vba #loops
#превосходить #vba #петли
Вопрос:
Я пытаюсь что-то сделать в 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% Другими словами, для строк, которые имеют одну и ту же группу, которые непосредственно следуют друг за другом, я хотел бы иметь среднее значение скорости каждой из этих строк для группы.
Как я могу сделать что-то подобное в VBA?
Мы можем предположить, что значения для Группы и Скорости заполнены в столбцах 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 lt;gt; "" 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 Комментарии:
1. @braX — Я добавил некоторые основные работы из того, что я пробовал. Извините, я не слишком разбираюсь в VBA. Я не совсем уверен, на правильном ли я пути.
2. Вы можете использовать AVERAGEIF, чтобы сделать это без VBA
3. @DarrellH — Я думал об этом, но проблема в том, что я не знаю, как его сбросить. Например, если один и тот же номер группы вернется позже, но не повторится в этом конкретном случае, тогда как что-то вроде AVERAGEIF просто даст мне общее среднее значение для группы, а не «текущий повторный выбор» группы.
4. @DarrellH , или я, возможно, что-то упускаю? Не могли бы вы, пожалуйста, показать мне СРЕДНЕЕ значение, если бы оно обеспечивало необходимое? Я с радостью приму это в качестве ответа. Более чем рад не использовать VBA, если в этом нет необходимости.
5. С помощью Office 365:
=AVERAGE(XLOOKUP(TRUE,$A$1:A2lt;gt;A2,$B$2:B3,,0,-1):XLOOKUP(TRUE,A2:A$1040000lt;gt;A2,B1:B$1039999))
Ответ №1:
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 lt;= 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 gt; 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 Комментарии:
1. Один комментарий, неквалифицированные диапазоны(диапазоны без продиктованного родительского листа) обычно не одобряются в ответах. Это одна из проблем, которая часто возникает здесь. Но мне нравится использовать массивы для итерации. Даже если вы просто поставите
ActiveSheet.перед своими диапазонами, это более приемлемо, чем ничего.
Ответ №2:
Один из способов использования формулы включает добавление вспомогательного столбца. Вспомогательный столбец изменит значение при изменении «Группы».
Я поместил вспомогательный столбец в другое место на листе, но вы можете поместить его куда угодно и/или скрыть его
Вспомогательная колонка
E2: 1 E3: =IF($A3=A2,E2,E2 1) и заполните
окончательная_рейт
C2: =AVERAGEIFS(Sheet1!$B$2:$B$11,Sheet1!$E$2:$E$11,Sheet1!$E2) и заполните
Если у вас есть Office 365 с XLOOKUP , вы можете использовать более сложную формулу, упомянутую @ScottCraner в комментариях, и избегать вспомогательной колонки.