Замена (аналог) функции Суммесли()

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

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

Мир MS Excel » Вопросы и решения » Вопросы по Excel » Замена (аналог) функции Суммесли() (Формулы/Formulas)

and_evg

Дата: Четверг, 13.06.2019, 12:28 | Сообщение № 1

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

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

Сообщений: 452

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

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

Excel 2007

Всем доброго времени суток.
В продолжение темы Пересчет только видимых ячеек и по совету многоуважаемого _Boroda_, все же решил пересмотреть формулы...
В прилагаемом файле имеется два листа (урезанная версия, реальный файл Лист 1 - более 9000 строк и Лист 2 - более 5000 строк)

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

Код

=СУММЕСЛИ(номер_смет;C3;сумма_выпол)

находится она на листе 1 в желтых столбцах E и G (распостранена она на более чем 9000 строк и ссылается на листе 2 более чем на 5000 строк)
Есть ли какая либо альтернатива данной функции с целью ускорения?

К сообщению приложен файл: 6427771.xlsb (95.9 Kb)

Ответить

bmv98rus

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

Группа: Друзья

Ранг: Участник клуба

Сообщений: 4106

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

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

Excel 2013/2016

and_evg, вы заставляете 9000 раз пройтись по 5000 строк. тут дело не в функции а в её применении. При таких условиях, нужно уходить от формул в сторону MSQuery или PowerQuery, ну или VBA. тут можно как на формулы влиять, включая и отключая пересчет, так и просто все строить через VBA.

~~Замечательный~~ Временно просто медведь ~~, процентов на 20~~.

Ответить

and_evg

Дата: Пятница, 14.06.2019, 13:41 | Сообщение № 3

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

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

Сообщений: 452

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

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

Excel 2007

Цитата bmv98rus, 13.06.2019 в 12:55, в сообщении № 2 ()

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

Спасибо за наводку
Сделал следующим образом

При работе на листе с проблемными формулами:
[vba]

Код

Private Sub Worksheet_Activate()
    'Включаем в приложении ручной пересчет
    Application.Calculation = xlManual
    'Разрешаем пересчет формул на этом листе
    Me.EnableCalculation = True
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
    'Пересчитываем только те ячейки которые зависят (если есть) от измененной
    On Error Resume Next
    Target.Dependents.Calculate
End Sub

[/vba]

При работе на другом листе
[vba]

Код

Private Sub Worksheet_Deactivate()
    'Запрещаем пересчет формул на этом листе
    Me.EnableCalculation = False
    'Включаем в приложении автоматический пересчет
    Application.Calculation = xlAutomatic
End Sub

[/vba]

Может есть какие замечания?

Ответить

_Boroda_

Дата: Пятница, 14.06.2019, 14:56 | Сообщение № 4

Группа: Модераторы

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

Сообщений: 16674

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

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

2003; 2007; 2010; 2013 RUS

Еще такой файлик посмотрите, может пригодится для чего. Там похоже на Ваш, только пересчет можно вручную на листах галками включать-выключать

К сообщению приложен файл: ____.xls (41.5 Kb)

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