Пересчет только видимых ячеек

Старая форма входа

Страница 1 из 1 1
Модератор форума: китин, _Boroda_

Пересчет только видимых ячеек (Макросы/Sub)

and_evg

Дата: Четверг, 06.06.2019, 13:42 | Сообщение № 1

Группа: Проверенные

Ранг: Обитатель

Сообщений: 465

Репутация: 79 ±

Замечаний: 0% ±

Excel 2007

Всем доброго времени суток.

Возникла следующая проблема.
Имеется таблица порядка 5000 строк, в каждой строке порядка 20 колонок с формулами при чем формулы ссылаются на ячейках только в данной строке. Также на данную таблицу наложен автофильтр и используются формулы типа

Код

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;F4:F5099)

Так как в работе постоянно необходимо использование автофильтра и при изменении набора строк происходит пересчет данной таблицы (на слабеньких машинах занимает продолжительное время). Вот что то не соображу - как сделать, что бы пересчитывались только видимые ячейки.
Заранее спасибо за подсказку в каком направлении копать.

Ответить

_Boroda_

Дата: Четверг, 06.06.2019, 14:12 | Сообщение № 2

Группа: Админы

Ранг: Местный житель

Сообщений: 16718

Репутация: 6505 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Не уверен, но попробуйте поставить ручной пересчет и в модуль листа что-то типа
[vba]

Код

Private Sub Worksheet_Change(ByVal Target As Range)
Range("A1:C5").SpecialCells(xlCellTypeVisible).Calculate
End Sub

[/vba]

Скажи мне, кудесник, любимец ба’гов...
Платная помощь:
Boroda_Excel@mail.ru
Яндекс-деньги: 41001632713405 | Webmoney: R289877159277; Z102172301748; E177867141995

Ответить

and_evg

Дата: Четверг, 06.06.2019, 14:26 | Сообщение № 3

Группа: Проверенные

Ранг: Обитатель

Сообщений: 465

Репутация: 79 ±

Замечаний: 0% ±

Excel 2007

_Boroda_, Спасибо
Сам пробовал вот это
[vba]

Код

Private Sub Worksheet_Change(ByVal Target As Range)
Me.UsedRange.SpecialCells(xlCellTypeVisible).Calculate
End Sub

[/vba]
Помогает при внесении изменений, но к сожалению не спасает при применении автофильтра

Ответить

_Boroda_

Дата: Четверг, 06.06.2019, 14:34 | Сообщение № 4

Группа: Админы

Ранг: Местный житель

Сообщений: 16718

Репутация: 6505 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Можно конечно циклом проверять каждую ячейку (а лучше каждую строку) на видимость и пересчитывать только видимые. Но нужно смотреть - не факт, что это получится быстрее, чем считать всё

Еще вариант переписать формулы примерно вот так

Код

=если(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;A2);ФОРМУЛА;)

Или в отдельный столбец (пусть в Е) написать

Код

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;A2)

и формулы переписать так

Код

=если(E2;ФОРМУЛА;)

Ответить

and_evg

Дата: Четверг, 06.06.2019, 15:34 | Сообщение № 5

Группа: Проверенные

Ранг: Обитатель

Сообщений: 465

Репутация: 79 ±

Замечаний: 0% ±

Excel 2007

Цитата _Boroda_, 06.06.2019 в 14:34, в сообщении № 4 ()

конечно циклом проверять каждую ячейку (а лучше каждую строку) на видимость

А по какому событию? Вот в чем вопрос... насколько я понял, что у автофильтра нет события

Ответить

_Boroda_

Дата: Четверг, 06.06.2019, 15:55 | Сообщение № 6

Группа: Админы

Ранг: Местный житель

Сообщений: 16718

Репутация: 6505 ±

Замечаний: ±

2003; 2007; 2010; 2013 RUS

Сделайте лучше по второму варианту второго варианта.
Думаю, что так быстрее работать будет, чем макросом считать построчно. Впрочем, можно проверить - повесьте его (макрос) на кнопку и попробуйте. Там, кстати, даже не построчно можно, а по areas. Но все равно не нравится мне очень это.
Кстати, формулы нельзя оптимизировать?

Ответить

and_evg

Дата: Пятница, 07.06.2019, 07:23 | Сообщение № 7

Группа: Проверенные

Ранг: Обитатель

Сообщений: 465

Репутация: 79 ±

Замечаний: 0% ±

Excel 2007

Цитата _Boroda_, 06.06.2019 в 15:55, в сообщении № 6 ()

Сделайте лучше по второму варианту второго варианта.

Цитата _Boroda_, 06.06.2019 в 15:55, в сообщении № 6 ()

формулы нельзя оптимизировать?

К сожалению формулы вводят пользователи (каждый в свой файл) и всем невозможно будет объяснить логику, тем более что у каждого пользователя свои условия договора и свои специфические формулы.

Цитата _Boroda_, 06.06.2019 в 14:34, в сообщении № 4 ()

циклом проверять каждую ячейку (а лучше каждую строку) на видимость и пересчитывать только видимые

Цитата _Boroda_, 06.06.2019 в 15:55, в сообщении № 6 ()

проверить - повесьте его (макрос) на кнопку

Пока решил повесить на кнопку:
[vba]

Код

Public Sub myCalc()
Me.UsedRange.SpecialCells(xlCellTypeVisible).Calculate
End Sub

[/vba]
и для обработки изменения
[vba]

Код

Private Sub Worksheet_Change(ByVal Target As Range)
myCalc
End Sub

[/vba]

На будущее думаю проверять изменения автофильтра раз в секунду, но что то настораживает это <_<

Ответить

Пересчет только видимых ячеек (Макросы/Sub)

Страница 1 из 1
1