Быстродействия Calc - полтора миллиона ячеек с формулами

[kompilainenn]

Ждем реализацию функции LET (?).

Не забудьте указать номер бага, где ждёте

https://bugs.documentfoundation.org/show_bug.cgi?id=137543

[sokol92]

Роман, спасибо!

[Tigrik]

Всем огромное-преогромное СПАСИБО за полезную и интересную информацию.
Одновременно открыл слишком много тем - по-тихоньку добрался и до главной.
Теперь, не спеша, буду продвигаться.

2 млн. яч. с ДВВСЫЛ, кмк, не дадут работать.

Но одновременно и максимально будут работать только 1.5 млн ячеек с формулами - справляется за 6 секунд. Но это, ещё пока, много.
python+pandas - это совсем новая для меня тема - хочется, конечно, заняться, но чуть позже.

Поместите кнопку поближе (чтобы не лазить в меню) и подключите к ней макрос.

Очень полезный макрос - Благодарю, буду использовать.

Если всё соединить на одном листе, то это увеличит объём файла и затруднит его обслуживание.

В листе с расчётными таблицами уже больше 2 млн ячеек (не все они с формулами) и добавление 25 тысячи статичных ячеек от таблиц-словарей, скорее всего, не намного утяжелит файл.
К тому же, сейчас, отключен режим автоматического перерасчёта - файл не так тяжело редактируется.
На данный момент, 100 тысяч ячеек обращаются к словарям в соседние листы - если всё это (и расчётные таблицы и таблицы-словари) будет на одном листе - это, хоть немного, увеличит быстродействие?

[Tigrik]

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

У меня, как раз, и есть такой случай - дополнительная ячейка-формула заменяет двойное вычисление.
Но мой вопрос немного в другом.
В моём текущем варианте, есть столбцы с промежуточными значениями - это, пока, удобно для визуального контроля, но эту формулу можно спокойно добавить в основную формулу при окончательном варианте программы.

Каждая формула занимает место в памяти компьютера.

Это понятно. А как насчёт ячейки с формулами? Каждая ЯЧЕЙКА С ФОРМУЛАМИ использует память компа? Что лучше для быстродействия: две ячейки, где каждая со своей функцией или одна ячейка с этими двумя функциями?

[mikekaganski]

В теории одна ячейка с формулой (при условии отсутствия двойного вычисления в этой формуле) несколько эффективнее.

Но я бы категорически не рекомендовал этот подход: я уверен, что Вы не заметите выигрыша даже на своём огромном массиве данных. А вот проблем с отладкой формул будет сколько угодно (и на возможное "собрать единую формулу после отладки" я скажу, что не бывает момента, когда можно быть на 100% уверенным, что багов не осталось).

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

[Tigrik]

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

Спасибо за ответ. Хорошо - отпишусь.

---------

Кроме того, формулы ДВССЫЛ(INDIRECT) и СМЕЩ (OFFSET) являются волатильными (пересчитываются при изменении любых других ячеек, в т. ч. не влияющих на них). В Вашем огромном файле их надо убрать.

В самом первом своём варианте у меня была возможность не использовать ДВССЫЛ() - было определен диапазон таблиц-словарей и это использовалось напрямую. Но этот вариант оказался очень длительным - около 40 секунд.
Пришлось сделать динамически изменяемые диапазоны для словарей (они содержат намного меньше строк), что улучшило скорость вычисления - стало 6 секунд.
Пока, я не вижу как можно избиваться от этой функции в моей программе.

Функция СМЕЩ() используется больше чем в половине активных ячеек - 800 тысяч.
Для каждой расчётной таблицы, после ввода данных, составляется своя строка и вычисляется её длина (в определенной ячейке). Она "раскладывается" по ячейкам в однострочный диапазон.
Вот эта функция и применяется для поиска значения в ячейке, на которую указывает параметр "столбец" в этой функции (параметр "ссылка" - ячейка прямо перед диапазоном; параметр "строка" - пустой).
Для неё я нашёл аналог - ИНДЕКС(), где используется тот же диапазон, а "позиция" - значение, которое использовалось для "столбца" в предыдущей функции. И вариант программы с этой функцией, по быстродействию, тот же самый - 6 секунд.
Мне думается, что эти функции совсем одинаковые по своей внутренней структуре (СМЕЩ - более универсальный - с двумя уровнями: строка и столбец). Но отличаются ли они по быстродействию? Может быть, эта функция также - волатильная?

Есть ещё одно решение - немного экзотическое, на мой взгляд.

Код Выделить Развернуть


ЛЕВ(ПРАВ(D17;E17 - C19 + 1);1)

, где E17 - длина строки D17, а C19 - позиция в строке, значение которой и надо найти.
Такой вариант я, пока, не использовал в этой своей программе, хотя, очень часть применяю в других случаях для меньших объемов.
Потому что не знаю, что лучше для быстроты расчетов - две текстовые функции или одна (СМЕЩ или ИНДЕКС) табличная?
Может быть, кто-то подскажет.

[Tigrik]

В моих расчётных таблицах используются: в 8 столбцах функция СМЕЩ(), столбец с функцией СОВПАД(сравнение небольших слов), столбец с ОБЪЕДИНИТЬ(однострочный небольшой диапазон) и несколько столбцов с простыми проверками и присвоениями.

Если о СМЕЩ() уже было обсуждение, то что можно сказать о других функциях?

Код из моей программы:

Код Выделить Развернуть


ОБЪЕДИНИТЬ( ; ;JN16:JU16)

Но можно использовать и другую, хотя и выглядет это непрезентабельно:

Код Выделить Развернуть


СЦЕПИТЬ(JN16;JO16;JP16;JQ16;JR16;JS16;JT16;JU16)


100 тысяч ячеек - придеться, скорее всего, проверять на практике.

---------

А есть ли аналоги у функций СОВПАД() и ЕОШИБКА()?
Также, по 100 тысяч ячеек - если чуть будет быстрее, то в целом уже существенный выигрыш.

[eeigor]

@Tigrik, размещать данные и словари на одном листе всё-таки неправильно.

Рассмотрите функцию TEXTJOIN для объединения ячеек (вместо ОБЪЕДИНИТЬ).

[Tigrik]

@Tigrik, размещать данные и словари на одном листе всё-таки неправильно.

Конечно неправильно.
Я без году неделя в LO, всё равно, так не делаю - у меня всё по разным листам.
Но, здесь, в конкретной объемной программе, когда первостепенный вопрос - это вопрос быстродействия, приходится искать и применять любые варианты, которые помогут в этом вопросе.

[mikekaganski]

если всё это (и расчётные таблицы и таблицы-словари) будет на одном листе - это, хоть немного, увеличит быстродействие?

Нет.

Оно могло бы увеличить быстродействие хотя бы в теории, только если данные словарей могли бы уместиться в процессорный кэш и сохранялись бы там в процессе пересчёта формул. А на листе с миллионами формул, при учёте специфики алгоритмов Calc, обращение к данным непонятно где на том же листе или на другом листе одинаково по быстродействию. Нет никаких отдельных накладных расходов при адресации между листами по сравнению с тем же листом.

[Tigrik]

Михаил, Благодарю.

[eeigor]

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

Дело не в быстродействии самой функции, а в волатильности.

[mikekaganski]

Код из моей программы:

Код Выделить Развернуть


ОБЪЕДИНИТЬ( ; ;JN16:JU16)

Но можно использовать и другую, хотя и выглядет это непрезентабельно:

Код Выделить Развернуть


СЦЕПИТЬ(JN16;JO16;JP16;JQ16;JR16;JS16;JT16;JU16)


Рассмотрите функцию TEXTJOIN для объединения ячеек (вместо ОБЪЕДИНИТЬ).

У меня ощущение, что "ОБЪЕДИНИТЬ" - это то же самое, что TEXTJOIN (нет, я не хочу тратить время ещё и на поиск совпадений в каких-либо словарях). Но в принципе использование TEXTJOIN для объединения без разделителей нерационально, поскольку есть CONCAT, также принимающая диапазоны (в отличие от CONCATENATE) и не имеющая дополнительных накладных расходов на обработку неиспользуемого функционала.

Кстати, если "СЦЕПИТЬ" - это CONCATENATE, то она наименее эффективная по реализации (использует лишние аллокации памяти, что нужно исправить для улучшения быстродействия) из всех трёх упомянутых функций.

Может быть, эта функция также - волатильная?

Волатильность функций обозначена в документации (пример для OFFSET):

This function is always recalculated whenever a recalculation occurs.

Также это определено в стандарте.

[kompilainenn]

Кстати, если "СЦЕПИТЬ" - это CONCATENATE,

Да, именно

что "ОБЪЕДИНИТЬ" - это то же самое, что TEXTJOIN

абсолютли

[eeigor]

Посмотрел стандарт по ссылке от Михаила:

HYPERLINK тоже волатильная. Недавно работал с динамическими гиперссылками для реализации взаимных переходов между разными таблицами (двойники) и в пределах одной таблицы (дубликаты), и файл сильно тормозил. Ан вот в чём дело!
Полторы тысячи записей – и заметное торможение при вставке строки и переходе к следующей ячейке после ввода данных: это все гиперссылки пересчитывались без толку. Тогда-то и написал макрос переключения автопересчёта формул (ответ #1), ещё до конца не понимая, в чём дело.