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

[mikekaganski]

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

HYPERLINK тоже волатильная.

Возможно (я не смотрел код), но тут я не отметил важный аспект.
волатильность функции - не часть стандарта ODF, поскольку этот стандарт не о поведении во время выполнения, а о хранении и о семантике. Поэтому вышеуказанный кусок стандарта не оперирует терминами "MUST, SHOULD, etc.", а указывает на возможность того, что реализации делают некоторые из этих функций (а может, и другие) волатильными.

[Tigrik]

Прошу совета.

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

При моём текущем уровне программирования и в данном алгоритме, как мне думается, остался ещё один вариант улучшения алгоритма.
В приведённом снимке экрана только несколько ячеек, но чтобы не запутать ещё больше, скорее всего, этого будет достаточно.
1-я ячейка (Y15) объединяет символы в строку, которая (в следующей ячейке) ищется по словарю. Результат поиска: если нашла точное совпадение, то выводит его же; если нет - Ошибка: значение недоступно (#H/D).
Чтобы определить все найденные слова, мне пришлось добавить столбец, где ячейки с ошибкой обнуляются и остаются только найденные слова, которые объединяются в строку в ячейке Y9.

Есть ли способ использовать только строку Z (не прибегая к строке AA) для выявления найденный слов?
Может быть использовать тот же механизм регулярных выражений? Но я, вообще, ни разу не пользовался такими решениями.

[sokol92]

реализации делают некоторые из этих функций (а может, и другие) волатильными

Возможно еще и разное понимание волатильности.

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

В Calc иная ситуация. В документе с листами Sheet1 и Sheet2 запишем в ячейку B2 листа Sheet1 формулу:

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

=SHOWRECALC(INDIRECT("$Sheet2.B2"); "B2")

где

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

Function ShowRecalc(arg, adr)
  Msgbox "Cell " & adr & " recalc. Value: " & arg
  ShowRecalc=arg
End Function

Эксперимент показывает, что при изменении любой ячейки листа Sheet1 формула в B2 пересчитывается (в соответствии с ожиданиями).

Если активировать лист Sheet2 и менять там ячейки (включая B2), то формула в $Sheet1.B2 не пересчитывается. При активации листа Sheet1 формула будет пересчитана.

Насколько существенно такое различие, "сходу" сказать не могу.

[mikekaganski]

Возможно еще и разное понимание волатильности.

https://lists.oasis-open.org/archives/office-comment/202203/msg00000.html

[sokol92]

Михаил, спасибо!

[mikekaganski]

В Calc иная ситуация....

Если активировать лист Sheet2 и менять там ячейки (включая B2), то формула в $Sheet1.B2 не пересчитывается. При активации листа Sheet1 формула будет пересчитана.

Насколько существенно такое различие, "сходу" сказать не могу.

Однако сходу можно сказать, что текущая реализация в Calc (не знаю, корректная или нет) делает разбиение данных по листам очень привлекательным: если часть данных с волатильными формулами находится на другом листе, то это сэкономит время.

Интересно также поэкспериментировать, будут ли пересчитываться формулы на тех листах (волатильные и не очень), если они зависят от изменяемых данных на активном листе, а от них самих ничего на активном листе не зависит. Это уже второй уровень. (Под зависимостью я имею ввиду прямую ссылку, а не строку, которая может быть интерпретирована формулой. Например, такая волатильная формула может быть типа "=NOW() & Sheet1.A1")

[sokol92]

Я эксперименты такого типа проводил до того, как написал сообщение #32, но к четким выводам не пришел. Когда появится время, вернусь.

Выглядит так, как будто пересчет ячеек отложен до использования "по требованию". В Excel мне трудно подобрать аналог.

[Tigrik]

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

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

Как и обещал, изложу некоторые приблизительные оценки быстродействия Calc при различных вариантах размещения формул в таблицах, самих таблиц и замена функций в формулах.
Оценки приблизительные, так как замер времени проходит "на глазок" - один глазом на лист с вычислениями (две соседние ячейки: в одну ввод данных, в другой - результат) в 3/4 экрана; другим глазом - на секундомер из Браузера, на оставшейся части экрана. Понятное дело, точность - плюс-минус полсекунды.
Перебрал все найденные мною аналоги функций, которые участвуют в вычислениях в таблицах - видимого улучшения/ухудшения не наблюдал.
Убрал один лишний столбец с формулами и формулы из двух других столбцов "разбросал" по соседним столбцам - в этом случае, положительный результат явно видно - 3,5-4 секунды вместо 5-6 секунд, что было раньше.
Даже специально, одну функцию (ОБЪЕДИНИТЬ) "разложил" на 7 столбцов - это увеличило время вычисления на 1 секунду. Думается, что приложение, "пробегая" по всем задействованными в вычислениях ячейкам "теряет" времени больше, чем чуть более усложненной формуле.

Интересно также поэкспериментировать, будут ли пересчитываться формулы на тех листах (волатильные и не очень), если они зависят от изменяемых данных на активном листе, а от них самих ничего на активном листе не зависит.

На данном этапе, в моей программе есть 3 варианта вычислений: самый лёгкий - всего 1 тысяча строк; средний - 10 тысяч строк и самый объемный - 100 тысяч строк. Для каждого варианта - свои расчётные таблицы и только они загружаются данными при своём варианте. Разнёс эти таблицы, согласно вариантам, по разным листам. По времени выполнения вычислений, ничего не изменилось.
Думаю, что 100 тысяч строк - это мало, чтобы "на глазок" определить изменение быстродействия. Есть ещё два варианта вычислений, но для моих целей они избыточны, поэтому и не реализованы - пока, во всяком случае - 400 тысяч строк и 4 млн. строк. Если будет время и возможности, то, для своей практики, возможно, возьмусь за это - необходимо создать исходные таблицы - на это потребуется какое-то время. Уверен, что, тогда, можно и "на глазок" увидеть изменения для всех этих моих тестов.

---------
"На глазок" - это, конечно, мой текущий уровень - новичка.
Наверняка есть решения, которые можно использовать для замера времени вычислений на листе Calc. Возможно, что только в "содружестве" с Basic.
На форуме я видел примеры макросов, где используется "ходики" (тик-так), но как это привязать к вычислениям на листе.
Ввод данных в определенную ячейку - "запуск" секундомера, результат появился в соседней ячейке - остановка. Может быть, это уже где-то обсуждалось? Не нашёл!

[eeigor]

Про волатильность
Маленькое приложение, чуть переделанное мной из Excel, позволяет увидеть, какие функции и сколько раз пересчитываются. Нажмите F9.
Можете добавить любую функцию сами и подключить к ней счётчик вызовов.

[Tigrik]

eeigor, Благодарю.
К сожалению, вчера совсем не получилось посмотреть этот файл. Но, сегодня, весь день его тестирую и изучаю.

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

1. Я знал, что на листе можно использовать функции из макросов, но не знал как это сделать. Для этого достаточно вызвать функцию прописными буквами, задав ей необходимые аргументы и она должна быть в макросах к файлу?
2. «sequence counter» - это счётчик вызовов (в своём тексте - я, для краткости, обозвал это шагами) этой функции на этапе одного вычисления?
3. Я сохранял результаты нескольких последовательных шагов с использованием макроса для исследования и увидел, что все волатильные функции изменяются с неодинаковым количеством шагов - не в сравнение друг с другом, а сама по себе.
Про какие-то функции (ТДАТА, СЛЧИС, СЕГОДНЯ) это можно понять  - аргументы в этих функциях каждый раз разные, что может изменять количество шагов.
Но другие волатильные функции (ДВССЫЛ, ИНФОРМ и ТРАНСП), у которых не изменяются значения аргументов, на каждом этапе вычислений, также выдают разное приращение шагов. Функция СМЕЩ несколько этапов с одним количеством шагов, но, потом, "срывается" - каждый этап с новым количеством шагов.
Как это можно объяснить?
4. Функция ЯЧЕЙКА обозначена как волатильная, но не изменяется. (?)
5. Судя по тому, что функция СУММЕСЛИ обозначена двумя раскрасками, то "сидит на двух стульях", хотя в моих исследования она не обозначила себя волатильной - ни разу не изменила количество шагов.
И в этой функции меня удивило, что если диапазон в 3-ей аргументе этой функции задан только начальной ячейкой, то нужно больше шагов (всего на один), чтобы "посчитаться".
Может быть, в Calc эти две последние функции уже не волатильные?!

---------
Возможно, что это не имеет непосредственное отношение к теме волатильности функций, но не понятно назначение 2 строки на листе (с числами 1-18; в ячейке Q2, вероятнее всего, опечатка - там число 1, а, по идее, должно быть 16). Скорее всего, это осталось от файла для Экселя?

[eeigor]

1. Я знал, что на листе можно использовать функции из макросов, но не знал как это сделать. Для этого достаточно вызвать функцию прописными буквами, задав ей необходимые аргументы и она должна быть в макросах к файлу?

Просто вызвать. Все буквы будут заменены на прописные: это стиль формул Calc.

2. «sequence counter» - это счётчик вызовов...

Да

3. Я сохранял результаты нескольких последовательных шагов с использованием макроса для исследования и увидел, что все волатильные функции изменяются с неодинаковым количеством шагов - не в сравнение друг с другом, а сама по себе.

Вероятно, происходит цепь (каскад) изменений. Я тоже изучаю... поскольку осваиваю Calc в течение 2 лет. Думаю, это не очень много.


Edit:

4. Функция ЯЧЕЙКА обозначена как волатильная, но не изменяется. (?)

Там с конкретным аргументом ("filename") и для Excel. Значит, и для Calc, похоже, эта комбинация не актуальна тоже актуальна. Но есть другие отличия. Можно проверить и убедиться самому.

Может быть, в Calc эти две последние функции уже не волатильные?!

Да, надо смотреть. Плюс Владимир (@sokol92) отмечает, что некоторые функции, в отличие от Excel, не пересчитываются, если изменения прошли на других листах, а обновляются при активизации листа с данной формулой.

В общем, подходите творчески...

[Tigrik]

eeigor, Спасибо.