Подсчет промежуточных итогов в таблице переменного размера

@shavka · Регистрация: 27.11.2013

Студворк — интернет-сервис помощи студентам

Есть таблица, кол-во строк в которой меняется. Образец прилагается. В желтых строчках нужно ввести формулы промежут. итогов, например сумма и среднее. Я делаю по-школьному - рассчитываю границы промежуточных рейнджей (это тикеры в 4 столбце), а потом с пом. формул R1C1 циклом обсчитываю всю таблицу. Подозреваю, что это не самый эффективный метод - считает очень долго. Можно как-то оптимизировать процесс через массивы или объектные переменные? Причем в промежут. итогах мне нужны не значения, а именно формулы - они важны в текущей работе для быстрого подсчета.

@pashulka · 04.11.2015, 22:47

Один из возможных вариантов :

Visual Basic
1
2
3
4
5
6
7
8
9
Private Sub Test()
    Application.ScreenUpdating = False
    Dim iSource As Range
    For Each iSource In [B:B].SpecialCells(xlConstants, xlNumbers).Areas
        iSource(0, 3).Formula = "=SUBTOTAL(1," & iSource.Offset(, 3).Address & ")"
        iSource(0, 4).Formula = "=SUBTOTAL(9," & iSource.Offset(, 3).Address & ")"
    Next
    Application.ScreenUpdating = True
End Sub

@Казанский · 04.11.2015, 22:56

shavka, есть предложение задействовать штатную команду "Промежуточные итоги". Но для этого придется удалять существующие желтые строки. Все это делает следующий макрос, полученный в основном записью ручных действий.

Visual Basic
1
2
3
4
5
6
7
8
9
Sub shavka()
  Cells.AutoFilter 3, "Тикер"
  Range("2:" & Rows.Count).SpecialCells(xlCellTypeVisible).Delete
  ActiveSheet.AutoFilterMode = False
  Cells.Sort Range("C1"), xlAscending, Header:=xlYes
  Range("A1").Subtotal GroupBy:=3, Function:=xlSum, TotalList:=Array(4, 5), _
        Replace:=True, PageBreaks:=False, SummaryBelowData:=False
  Range("E:E").Replace "=SUBTOTAL(9", "=SUBTOTAL(1", xlPart
End Sub

@pashulka · 05.11.2015, 00:16

Если сумму необходимо считать по столбцу D , а среднее по столбцу E , то небольшое изменение :

Visual Basic
1
2
3
4
5
6
7
8
9
Private Sub Test()
    Application.ScreenUpdating = False
    Dim iSource As Range
    For Each iSource In [B:B].SpecialCells(xlConstants, xlNumbers).Areas
        iSource(0, 3) = "=SUBTOTAL(9," & iSource.Offset(, 2).Address & ")"
        iSource(0, 4) = "=SUBTOTAL(1," & iSource.Offset(, 3).Address & ")"
    Next
    Application.ScreenUpdating = True
End Sub

@shavka · 05.11.2015, 02:12 **[ТС]**

чо то там у вас не дописалось в конце в макросе. Ну ладна, если это записью макроса делается, я допишу. Но че то как-то стремно удалять желтые строчки. Он их точно мне сам вставит, где надо?

Добавлено через 5 минут
Pashulka, у вас такая аскетичная изящная формулка, она меня вдохновляет. Но я не совсем понял, вот эти константы - 9, 1 - это относится к типу формул? Просто у меня реально формул больше, и столбцов, в которых надо эти формулы считать, больше. Я не стал утруждать аксакалов, думал, главное - принцип просечь. Если нестандартная какая-нить формула, подойдет ваш метод?

@pashulka · 05.11.2015, 10:16

shavka, Если посмотрите справку, касательно стандартной функции рабочего листа =ПРОМЕЖУТОЧНЫЕ.ИТОГИ() , то увидите

что первый обязательный аргумент - это номер функции, в нашем случае, это 9 - СУММ и 1 - СРЗНАЧ

Если же результаты вычислений не должны зависеть от наличия фильтра(скрытых строк), то ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно заменить на SUM и AVERAGE.

Разумеется, подобным способом Вы можете вводить и более сложные(нестандартные) формулы, только не забудьте, что для программного ввода формул массива - нужно использовать свойство .FormulaArray

@shavka · 05.11.2015, 13:19 **[ТС]**

Блин, точно, саффсем забыл. FormulaArray! А я просто Formula написал свойство. Потому он и не считает. Спасибо!!!

@shavka · 26.12.2015, 18:36 **[ТС]**

pashulka, наслаждался вашим изящным макросом и тут вдруг облом: если в каком-либо из списков таблицы всего одна строчка, он дальше не группирует. Ошибка 1004. А если запросить через MsgBox адреса группируемых строк, то в этой единичной строчки он пишет такую билеберду:
$B$1:$L$1,$M$1:$AD$6,$B$2:$C$2,$F$2:$G$2 ,$J$2:$K$2,$B$3:$L$3,$B$4:$C$4,$F$4:$G$4 ,$J$4:$K$4,
$B$5:$L$6,$Q$7:$AC$7,$A$1:$A$8,$C$8,$J$8 :$K$8,$M$8:$N$8,$Q$8:$AD$912,
$L$9:$P$21,$L$22:$N$22,$L$23:$P$24,$L$25 :$N$25,$L$26:$P$33,$L$34:$N$34,$L$35:$P$ 40,$L$41:$N$41. О как!
Никак нельзя это исправить? В принципе, я нашел решение. Просто рассчитываешь номера строк по границам списков, а потом циклом их группируешь через Rows.Group. Но я подозреваю, что это непрофессионально. А я уже чессслово, поверил в ваше всесилие

@pashulka · 26.12.2015, 19:09

shavka, Не заметил я в своём макросе группировки, но даже ежели её добавить (см.аттач), то если в столбце B будет дата(число) мы пролучим группу, если нет, макрос эту строку просто проигнорирует, но ошибки не будет.

@shavka · 26.12.2015, 19:22 **[ТС]**

500 раз спасибо! Точно, я ж сначала хотел просто итоги подсчитать, а потом думаю - нахли мне такая простыня, конца-края не видно, дай-ка загруппирую тем же макросом, но понятым мной несколько извращенно. Но не доперло до меня окончательно, я вот так написал:

Visual Basic
1
2
3
4
5
Dim iSour As Range
For Each iSour In Range(Cells(7, 3), Cells(opEnd, 3)).SpecialCells(xlConstants, xlNumbers).Areas
    gr = iSour.SpecialCells(xlConstants, xlNumbers).Address
        Range(gr).Rows.Group
Next iSour

на что макрос высказал мне все, что он обо мне думает

@pashulka · 26.12.2015, 19:46

На всякий случай - цитата из офисной справки, об'ясняющая когда нужно применять SpecialCells (за исключением xlLastCell) к одной единственной ячейке :

Выделение ячеек в соответствии с содержащимися в них данными

1 Выделите диапазон, содержащий тип ячеек, которые нужно выделить.

Чтобы выделить все ячейки этого типа на текущем листе, наведите указатель на любую ячейку и нажмите кнопку мыши.

2 В меню Правка выберите команду Перейти (мой совет - или используйте клавишу F5)
3 Нажмите кнопку Выделить.
4 Установите нужные параметры.

Новые блоги и статьи Все статьи Все блоги /
PhpStorm 2025.3: WSL Terminal всегда стартует в ~ and_y87 14.12.2025 PhpStorm 2025. 3: WSL Terminal всегда стартует в ~ (home), игнорируя директорию проекта Симптом: После обновления до PhpStorm 2025. 3 встроенный терминал WSL открывается в домашней директории. . .	Access VikBal 11.12.2025 Помогите пожалуйста !! Как объединить 2 одинаковые БД Access с разными данными.	Новый ноутбук volvo 07.12.2025 Всем привет. По скидке в "черную пятницу" взял себе новый ноутбук Lenovo ThinkBook 16 G7 на Амазоне: Ryzen 5 7533HS 64 Gb DDR5 1Tb NVMe 16" Full HD Display Win11 Pro	Музыка, написанная Искусственным Интеллектом volvo 04.12.2025 Всем привет. Некоторое время назад меня заинтересовало, что уже умеет ИИ в плане написания музыки для песен, и, собственно, исполнения этих самых песен. Стихов у нас много, уже вышли 4 книги, еще 3. . .	От async/await к виртуальным потокам в Python IndentationError 23.11.2025 Армин Ронахер поставил под сомнение async/ await. Создатель Flask заявляет: цветные функции - провал, виртуальные потоки - решение. Не threading-динозавры, а новое поколение лёгких потоков. Откат?. . .
Поиск "дружественных имён" СОМ портов Argus19 22.11.2025 Поиск "дружественных имён" СОМ портов На странице: https:/ / norseev. ru/ 2018/ 01/ 04/ comportlist_windows/ нашёл схожую тему. Там приведён код на С++, который показывает только имена СОМ портов, типа,. . .	Сколько Государство потратило денег на меня, обеспечивая инсулином. Programma_Boinc 20.11.2025 Сколько Государство потратило денег на меня, обеспечивая инсулином. Вот решила сделать интересный приблизительный подсчет, сколько государство потратило на меня денег на покупку инсулинов. . . .	Ломающие изменения в C#.NStar Alpha Etyuhibosecyu 20.11.2025 Уже можно не только тестировать, но и пользоваться C#. NStar - писать оконные приложения, содержащие надписи, кнопки, текстовые поля и даже изображения, например, моя игра "Три в ряд" написана на этом. . .	Мысли в слух kumehtar 18.11.2025 Кстати, совсем недавно имел разговор на тему медитаций с людьми. И обнаружил, что они вообще не понимают что такое медитация и зачем она нужна. Самые базовые вещи. Для них это - когда просто люди. . .	Создание Single Page Application на фреймах krapotkin 16.11.2025 Статья исключительно для начинающих. Подходы оригинальностью не блещут. В век Веб все очень привыкли к дизайну Single-Page-Application . Быстренько разберем подход "на фреймах". Мы делаем одну. . .

Подсчет промежуточных итогов в таблице переменного размера

Решение

Решение

@shavka 132 / 15 / 2 Регистрация: 27.11.2013 Сообщений: 509
	05.11.2015, 02:12 [ТС]
	чо то там у вас не дописалось в конце в макросе. Ну ладна, если это записью макроса делается, я допишу. Но че то как-то стремно удалять желтые строчки. Он их точно мне сам вставит, где надо? Добавлено через 5 минут Pashulka, у вас такая аскетичная изящная формулка, она меня вдохновляет. Но я не совсем понял, вот эти константы - 9, 1 - это относится к типу формул? Просто у меня реально формул больше, и столбцов, в которых надо эти формулы считать, больше. Я не стал утруждать аксакалов, думал, главное - принцип просечь. Если нестандартная какая-нить формула, подойдет ваш метод? 0

@pashulka 4138 / 2242 / 940 Регистрация: 01.12.2010 Сообщений: 4,624
	05.11.2015, 10:16
	Сообщение было отмечено shavka как решение Решение shavka, Если посмотрите справку, касательно стандартной функции рабочего листа =ПРОМЕЖУТОЧНЫЕ.ИТОГИ() , то увидите что первый обязательный аргумент - это номер функции, в нашем случае, это 9 - СУММ и 1 - СРЗНАЧ Если же результаты вычислений не должны зависеть от наличия фильтра(скрытых строк), то ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно заменить на SUM и AVERAGE. Разумеется, подобным способом Вы можете вводить и более сложные(нестандартные) формулы, только не забудьте, что для программного ввода формул массива - нужно использовать свойство .FormulaArray 1

@shavka 132 / 15 / 2 Регистрация: 27.11.2013 Сообщений: 509
	05.11.2015, 13:19 [ТС]
	Блин, точно, саффсем забыл. FormulaArray! А я просто Formula написал свойство. Потому он и не считает. Спасибо!!! 0

@shavka 132 / 15 / 2 Регистрация: 27.11.2013 Сообщений: 509
	26.12.2015, 18:36 [ТС]
	pashulka, наслаждался вашим изящным макросом и тут вдруг облом: если в каком-либо из списков таблицы всего одна строчка, он дальше не группирует. Ошибка 1004. А если запросить через MsgBox адреса группируемых строк, то в этой единичной строчки он пишет такую билеберду: $B$1:$L$1,$M$1:$AD$6,$B$2:$C$2,$F$2:$G$2 ,$J$2:$K$2,$B$3:$L$3,$B$4:$C$4,$F$4:$G$4 ,$J$4:$K$4, $B$5:$L$6,$Q$7:$AC$7,$A$1:$A$8,$C$8,$J$8 :$K$8,$M$8:$N$8,$Q$8:$AD$912, $L$9:$P$21,$L$22:$N$22,$L$23:$P$24,$L$25 :$N$25,$L$26:$P$33,$L$34:$N$34,$L$35:$P$ 40,$L$41:$N$41. О как! Никак нельзя это исправить? В принципе, я нашел решение. Просто рассчитываешь номера строк по границам списков, а потом циклом их группируешь через Rows.Group. Но я подозреваю, что это непрофессионально. А я уже чессслово, поверил в ваше всесилие 0