Я пытаюсь вычислить код Excel VBA, чтобы добавить формулы промежуточных итогов (= SUBTOTAL (9, __: _) в несколько строк итогов на листе. Проблема в том, что диапазон для промежуточных итогов может быть любой высоты. Согласно изображению ниже , есть надежда, что код VBA сможет определить, когда "Итого" присутствует в столбце B, а затем перейти к добавлению формулы промежуточного итога в эту строку для столбцов от E до J. Формула промежуточного итога должна подтянуть вышеуказанные балансы вверх пока не останется пустая ячейка для расчета суммы для конкретного клиента (длина диапазона обозначена стрелками на изображении ниже). Диапазон может быть буквально любым количеством строк, поэтому должен быть полностью изменяемым. Любой помощь в этом будет очень признательна !!
VBA для добавления промежуточного итога с переменным диапазоном
comment
Почему промежуточный итог, а не сумма? Планируете ли вы скрыть ненужные строки в целом?
- person   schedule 14.07.2018
comment
Я хотел бы использовать промежуточный итог bc. Я также хотел бы добавить строку в самом низу списка, которая вычисляет общую сумму для всех клиентов. Промежуточный итог позволил бы просто увеличить весь диапазон и рассчитать весь текущий баланс. В этом есть смысл?
- person User22   schedule 14.07.2018
comment
Думаю, мне следовало включить это в свой вопрос :) Большое спасибо за помощь в этом вопросе !!
- person User22   schedule 14.07.2018
comment
А как насчет сводной таблицы на основе ваших данных?
- person AJD   schedule 15.07.2018
Ответы (1)
Это может быть выполнено с помощью простого ПРОМЕЖУТОЧНОГО ИТОГА по динамическому диапазону. Расположение Total в столбце B можно зациклить.
Option Explicit
Sub totalAllClients()
Dim rng As Range, rngsb As Range, addr As String
With Worksheets("sheet11")
With Intersect(.Columns(2), .UsedRange)
Set rng = .Find(What:="total", After:=.Cells(1), MatchCase:=False, _
LookAt:=xlWhole, SearchDirection:=xlPrevious)
If Not rng Is Nothing Then
addr = rng.Address(0, 0)
Do
rng.Offset(0, 1).FormulaR1C1 = _
"=index(c2, match(""zzz"", r1c2:r[-1]c2))"
rng.Offset(0, 3).Resize(, 6).FormulaR1C1 = _
"=subtotal(109, r[-1]c:index(c, match(""zzz"", r1c2:r[-1]c2)))"
Set rng = .FindNext(After:=rng)
Loop Until rng.Address(0, 0) = addr
rng.Offset(2, 3).Resize(1, 6).FormulaR1C1 = _
"=aggregate(9, 3, r2c:r" & rng.Row & "c)"
End If
End With
End With
End Sub
person
Community
schedule
14.07.2018
Я решил использовать subtotal bc. Я также хотел бы добавить строку в самом низу списка, которая вычисляет общую сумму для всех клиентов. Промежуточный итог позволил бы просто увеличить весь диапазон и рассчитать весь текущий баланс. В этом есть смысл?
- person User22; 14.07.2018
Думаю, мне следовало включить это в свой вопрос :) Большое спасибо за помощь в этом вопросе !!
- person User22; 14.07.2018
внизу просто используйте
=sum(e1:e29)-sumifs(e1:e29, $b1:$b29, "total")
- person ; 14.07.2018
Хорошо, размер данных (количество строк) будет меняться каждый раз, когда мне нужно выполнить расчет. Так будет ли добавление, о котором вы упомянули, просто вставить строку после последней суммы клиентов? Я надеялся добавить этот общий итог в макрос, чтобы мне не приходилось каждый раз добавлять новую строку. Спасибо еще раз
- person User22; 14.07.2018
Кстати, код, который вы предоставили для добавления в функции суммы, отлично работает! Как вы думаете, было бы лучше всего сделать эти промежуточные итоги, а затем добавить код для добавления строки промежуточных итогов в самом низу (последняя строка +2), чтобы подытожить весь столбец?
- person User22; 14.07.2018
Конечно. В любом случае вы просматриваете все строки с Total в столбце B.
- person ; 14.07.2018
Хорошо, я немного новичок в использовании vba, поэтому не совсем уверен, как внести эти изменения. Но я могу повозиться с ним, чтобы посмотреть, смогу ли я заставить его работать. Спасибо
- person User22; 14.07.2018
Я изменил сумму на промежуточный итог и добавил общую сумму внизу.
- person ; 14.07.2018
Работает отлично! Спасибо еще раз
- person User22; 14.07.2018
Есть еще одна вещь, которая позволит мне делать все через макрос. Любой способ добавить к приведенному выше коду способ переноса имени клиента в ячейку справа от соответствующей суммы. Итак, в качестве примера я бы хотел скопировать имя клиента AAAA из ячейки B2 в ячейку C4. И скопируйте BBBB из B5 в ячейку C13 .... скопируйте CCCC из B14 в ячейку C16 .... и так далее. Опять же, файл изменит размеры, поэтому ему придется использовать цикл для Total, а затем сможет распознать, где находится соответствующее имя клиента ... затем скопировать эту ячейку справа от Total.
- person User22; 14.07.2018
Здравствуйте, я обновил изображение для этого вопроса, чтобы показать настоящую электронную таблицу, для которой я пытаюсь использовать макрос промежуточных итогов. Макрос по какой-то причине не работает. Есть идеи, в чем может быть проблема? Спасибо
- person User22; 16.07.2018
Здравствуйте, я пытался использовать код, как указано выше, но на данный момент он не вычисляет какие-либо промежуточные итоги. Спасибо за любую помощь, которую вы можете в этом оказать!
- person User22; 18.07.2018