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

Автор Tigrik, 8 марта 2022, 22:44

0 Пользователи и 1 гость просматривают эту тему.

kompilainenn

Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

sokol92

Владимир.

Tigrik

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

Цитата: economist от  9 марта 2022, 09:022 млн. яч. с ДВВСЫЛ, кмк, не дадут работать.
Но одновременно и максимально будут работать только 1.5 млн ячеек с формулами - справляется за 6 секунд. Но это, ещё пока, много.
python+pandas - это совсем новая для меня тема - хочется, конечно, заняться, но чуть позже.

Цитата: eeigor от  9 марта 2022, 08:08Поместите кнопку поближе (чтобы не лазить в меню) и подключите к ней макрос.
Очень полезный макрос - Благодарю, буду использовать.

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

Tigrik

Цитата: mikekaganski от  9 марта 2022, 11:45однако часто слишком большие формулы невозможно составить так, чтобы вычисления производились только один раз.
У меня, как раз, и есть такой случай - дополнительная ячейка-формула заменяет двойное вычисление.
Но мой вопрос немного в другом.
В моём текущем варианте, есть столбцы с промежуточными значениями - это, пока, удобно для визуального контроля, но эту формулу можно спокойно добавить в основную формулу при окончательном варианте программы.
Цитата: eeigor от  9 марта 2022, 08:08Каждая формула занимает место в памяти компьютера.
Это понятно. А как насчёт ячейки с формулами? Каждая ЯЧЕЙКА С ФОРМУЛАМИ использует память компа? Что лучше для быстродействия: две ячейки, где каждая со своей функцией или одна ячейка с этими двумя функциями?

mikekaganski

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

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

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

Tigrik

Цитата: mikekaganski от  9 марта 2022, 18:02Да и вообще - Вы находитесь в наилучшей ситуации, чтобы ответить на Ваши вопросы (протестировав на своих данных) и написать здесь результат для информации.
Спасибо за ответ. Хорошо - отпишусь.

---------

Цитата: eeigor от  9 марта 2022, 08:08Кроме того, формулы ДВССЫЛ(INDIRECT) и СМЕЩ (OFFSET) являются волатильными (пересчитываются при изменении любых других ячеек, в т. ч. не влияющих на них). В Вашем огромном файле их надо убрать.
В самом первом своём варианте у меня была возможность не использовать ДВССЫЛ() - было определен диапазон таблиц-словарей и это использовалось напрямую. Но этот вариант оказался очень длительным - около 40 секунд.
Пришлось сделать динамически изменяемые диапазоны для словарей (они содержат намного меньше строк), что улучшило скорость вычисления - стало 6 секунд.
Пока, я не вижу как можно избиваться от этой функции в моей программе.

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

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

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

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

Tigrik

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

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

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

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

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

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


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

---------

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

eeigor

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

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

Tigrik

Цитата: eeigor от  9 марта 2022, 18:50@Tigrik, размещать данные и словари на одном листе всё-таки неправильно.
Конечно неправильно.
Я без году неделя в LO, всё равно, так не делаю - у меня всё по разным листам.
Но, здесь, в конкретной объемной программе, когда первостепенный вопрос - это вопрос быстродействия, приходится искать и применять любые варианты, которые помогут в этом вопросе.

mikekaganski

Цитата: Tigrik от  9 марта 2022, 17:52если всё это (и расчётные таблицы и таблицы-словари) будет на одном листе - это, хоть немного, увеличит быстродействие?
Нет.

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

Tigrik


eeigor

#26
Цитата: Tigrik от  9 марта 2022, 18:28
...по быстродействию, тот же самый - 6 секунд.
Мне думается, что эти функции совсем одинаковые по своей внутренней структуре (СМЕЩ - более универсальный - с двумя уровнями: строка и столбец). Но отличаются ли они по быстродействию? Может быть, эта функция также - волатильная?
Дело не в быстродействии самой функции, а в волатильности.
Ubuntu 18.04 LTS • LibreOffice 7.5.1.2 Community

mikekaganski

#27
Цитата: Tigrik от  9 марта 2022, 18:48
Код из моей программы:

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

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

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


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

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

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

Цитата: Tigrik от  9 марта 2022, 18:28Может быть, эта функция также - волатильная?

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

ЦитироватьThis function is always recalculated whenever a recalculation occurs.

Также это определено в стандарте.
С уважением,
Михаил Каганский

kompilainenn

Цитата: mikekaganski от 10 марта 2022, 10:03Кстати, если "СЦЕПИТЬ" - это CONCATENATE,
Да, именно
Цитата: mikekaganski от 10 марта 2022, 10:03что "ОБЪЕДИНИТЬ" - это то же самое, что TEXTJOIN
абсолютли
Поддержать разработчиков LibreOffice можно тут, а наш форум вот тут

eeigor

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

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