Оптимизация вычислений СУММЕСЛИМН

[offza]

Добрый день.

есть файлик Личных финансов. Ведется довольно давно, есть 3-4 несложных макроса, диаграммы. При этом весит не больше 1 мб. Показать, к сожалению, не могу из этических соображений, файл не мой. При этом, если очистить от данных пропадет суть вопроса.

Файл работает следующим образом (листы не относящиеся к сути вопроса опускаю, всего книга состоит из 9 листов):
1 лист: Календарь с разбивкой по счетам (столбцы). Расходы вносятся в строки по датам (аккумулируются в месяцы). Один из столбцов - код затрат, подтягивается из соответствующего справочника во 2-м листе.
2 лист: Справочник доходов/расходов.
3 лист: Анализ расходов. Статьи - из справочника (строки). В столбцах месяца. С 15года используется следующая функция (в формате ячеек RC она едина для всеех расчетных ячеек: =СУММЕСЛИМН('Фин. календарь'!C15;'Фин. календарь'!C2;'Анализ расходов'!RC1;'Фин. календарь'!C1;">="&R3C;'Фин. календарь'!C1;"<"&R2C). Два условия в формуле для расчета суммы: кодировка дохода/расхода, диапазон дат, определяющий месяц.
На всякий случай формула в стандартном формате ячеек для случайной ячейки: =СУММЕСЛИМН('Фин. календарь'!$O:$O;'Фин. календарь'!$B:$B;'Анализ расходов'!$A5;'Фин. календарь'!$A:$A;">="&AY$3;'Фин. календарь'!$A:$A;"<"&AY$2), где:
$O:$O - диапозон суммирования,
'Анализ расходов'!$A5 - условия 1. Кодировка дохода/расхода.
'Фин. календарь'!$A:$A;">="&AY$3;'Фин. календарь'!$A:$A;"<"&AY$2 - условие 2. Задается через 2 самостельных условия - больше или равно 1-го числа анализируемого месяца, и меньше 1-го числа следующего месяца.


Проблема связана с данной формулой.
При ведении календаря происходит лаг-задержка в несколько секунд (идет пересчет формул).
Как можно оптимизировать формулу для снижения задержки?

Сам додумался только до того, чтобы за предыдущие месяца актуализировать в формуле диапазон суммирования и диапазон условий в соответствии с фактическим количеством строк в "закрытом" месяце, например, в Листе анализа формулы для расчета в январе: =СУММЕСЛИМН('Фин. календарь'!$O$100:$O$300;'Фин. календарь'!$B$100:$B$300;'Анализ расходов'!$A5;'Фин. календарь'!$A$100:$A$300;">="&AY$3;'Фин. календарь'!$A$100:$A$300;"<"&AY$2).

Прошу прощения за сумбур. Надеюсь, проблема понятна, и надеюсь, кто-нибудь из уважаемых форумчан, отзовется и посоветует решение проблемы.
заранее спасибо.

[cheshiki1]

скорей всего тормозит из за диапазонов в весь столбец (Фин. календарь'!$A:$A).
как вариант таблицу сделать умной (для 2007 Главная - Форматировать как таблицу) и в формуле использовать диапазоны умной таблицы.

[offza]

Спасибо за ответ.
В таблице больше 6000 строк. И форматирование не позволяет безболезненно впилить "Умную таблицу".
Но ранее в качестве пробы делал такое упражнение: Диапазон $A:$A и соответственно другие по формуле менял на $A$1:$A$10000. Помогало не сильно.
Мне видится возможным решением было бы, если бы формула эти диапазоны изначально выбирала под конкретный месяц, и делала бы расчеты только по нему.

[vikttur]

СУММЕСЛИМН сама умеет определять диапазоны. Вряд ли из-за полных столбцов будет такая задержка.
Много формул, наличие ошибок в вычислениях, ресурсоемкие вычисления...
Возможно, поможет разгрузить обрезание ненужных вычислений с помощью ЕСЛИ...
Чтобы определить причину, нужно видеть файл.

[offza]

Прикладываю файлик.
Все данные почистил, оставил только структуру файла.
Года 11-14 + январь 2015 г. в Анализе используют старую упрощенную формулу, в которой необходимо подставлять диапозон исходя из кол-ва операций.
в данным пустом файлике на моей конфигурации компьютера, при введении на Листе Фин календарь кода статьи процесс рассчитывается ок 5 секунд.
Ctrl+Shift+Y - макрос по смене типа формул на RC и обратно.

[offza]

Сам допёр как оптимизировать формулу благодаря ДВССЫЛ. Может, кому-то может быть полезно. Для моего примера новая формула выглядит так:
=СУММЕСЛИМН(ДВССЫЛ("'Фин. календарь'!$O"&R1C&":$O"&R2C);ДВССЫЛ("'Фин. календарь'!$B"&R1C&":$B"&R2C);'Анализ расходов'!RC1;ДВССЫЛ("'Фин. календарь'!$A"&R1C&":$A"&R2C);">="&R4C;ДВССЫЛ("'Фин. календарь'!$A"&R1C&":$A"&R2C);"<"&R3C)
, где в ячейках R1C и R2C через формулу ПОИСКПОЗ определяется расположение первых и послежних дней месяца, т.о. позволяя через статичную формулу определять точный диапозон месячных значений.

[vikttur]

Сомнительно... ДВССЫЛ - функция медленная, да еще и пересчитывается ПРИ ЛЮБОМ изменении на листе.

[offza]

может, и медленная, но выигрыш в моем случае на лицо - 1 секунда против 4-5 сек. пересчета.
Наверняка, можно и иначе оптимизировать, но мне другое в голову не пришло, да и тут не подсказали, так что на безрыбье и рак - рыба