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

[Tigrik]

Здравствуйте!

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

У меня очень объемная задача, и одна из частей задачи - это очень большой автопросчёт формул. Весь алгоритм этой части очень громоздкий (около 2 млн. ячеек с формулами) - если это всё поместить в один файл, то он будет, практически, малопригодным - по любому "чиху" долго пересчитывается (полегче стало, когда нашёл, что можно убрать "Вычислять автоматически"), но, что более важное, при работе он очень медлительный, что не приемлемо в режиме реального времени.
Пришлось устроить лишнюю "разборку" вводимых данных, чтобы разбить эту часть задачи на несколько подчастей (которые размещены в отдельные ФАЙЛЫ). В каждой подчасти созданы несколько расчётных таблиц, чтобы, в зависимости от вводимых данных, работали только необходимые таблицы.
Но и в этом случае, самый сложный вариант просчёта "ложится" на три таблицы с общим объемом 1,5 млн. ячеек с формулами (100 тысяч строк по 15 столбцов).

Всё сказанное выше, некоторая попытка "на пальцах" объяснить свою ситуацию при невозможности, тут я должен попросить извинения, привести конкретные файлы или коды - рабочий вариант очень громоздкий.
Но это (приводить файлы), мне думается, и не обязательно. В моих расчётных таблицах используются: в 8 столбцах функция СМЕЩ(), столбец с функцией СОВПАД(сравнение небольших слов), столбец с ОБЪЕДИНИТЬ(однострочный небольшой диапазон) и несколько столбцов с простыми проверками и присвоениями.
И только в одном столбце, вероятнее всего, наиболее трудоёмкая функция, которая и тормозит всё действие - функция ВПР(). Таблицы (одностолбцовые - для удобства назовём их словарями), по которым происходит поиск с помощью ВПР размещены в других листах в этой же книге и разделены по 5-8 тысяч строк. Конечно же, поиск по максимальной таблице-словарю (около 8 тысяч строк) из 100 тысяч строк расчётных таблиц - это долго (около 40 секунд). Пришлось в расчётные таблицы добавить дополнительный столбец с расчётом укороченного диапазона в словарях (теперь, самый большой диапазон в словарях - это 1300 строк, а остальные существенно меньше), чтобы ВПР трудилась поменьше - это намного улучшило быстродействие алгоритма - стало максимум 6 секунд. Правда, дополнительный столбец также использует ВПР, но только максимум 10 раз, а остальное время он копируется в соседние (снизу) ячейки.

И вот, собственно, и некоторые вопросы, по которым и создавалась эта тема:
1. Есть ли существенная разница по быстродействию для много-многострочных авторасчётах между ячейкой с длинной формулой или несколькими ячейками, но с простыми формулами, а с результатом, что в одной ячейке? [Чаще всего, я и "раскладываю" большую формулу на несколько ячеек - мне так удобнее, но насколько это правильно при подобных алгоритмах?!]

2. В формуле стоит проверка, где при некоторых условиях может быть исполнена функция ВПР, например:

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


=ЕСЛИ(KD16; ВПР(JW16; ДВССЫЛ($JW$13); 7; 0); JX15)

(это и есть дополнительный столбец для перерасчёта укороченного диапазона для основного ВПР). Правильно ли я понимаю, что при KD16=0 (в данной ячейке) ВПР не грузит процесс, так как "обходится" стороной?

3. В основном столбце стоит формула:

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


=ВПР(JY16; ДВССЫЛ(JX16); 1; 0)

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

4. Из приведенных выше функций, кроме уже рассмотренной ВПР, (СМЕЩ(), СОВПАД(сравнение небольших слов), ОБЪЕДИНИТЬ(однострочный небольшой диапазон); добавлю ещё ЕОШИБКА()) есть ли функции, которые могут реально тормозить выполнение задачи? И если они медленные - есть ли более быстродействующие аналоги этих функций?

5. Для удобства и наглядности таблицы-словари вынесены в отдельные листы и из одного столбца в таблицах расчётов к ним происходит обращение.
Влияет ли это на быстродействие расчётов? Если всё, что считается в одном файле соединить на одном листе - это улучшить быстродействие?

6. Может быть есть какие-нибудь общие рекомендации, советы или методы для разработки алгоритмов более быстрой работы при объемном автопросчёте?

Помогите, пожалуйста, разобраться в этой теме.
Спасибо.

[eeigor]

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

Но, все-таки, замедляет ли этот ДВССЫЛ() функцию ВПР или быстрее будет с прямой ссылкой?

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

Большие данные лучше обрабатывать в LO Base (SQL) и импортировать на лист в готовом виде.
Если Вы импортируете данных из LO Base, то они помещаются на лист в виде диапазона базы данных (DatabaseRange).

В LO Calc можно использовать другую методику: не "вытягивания" (PULL) данных посредством формул, а "вталкивания" (PUSH) данных в ячейки макросом (по команде). В последнем случае обновлением данных управляете Вы сами, а количество формул резко уменьшается, и файл открывается быстрее. Диапазонам надо присвоить имена, и грамотно управлять их расширением при добавлении новых данных. Если Вы будете использовать диапазоны базы данных (DatabaseRange), тогда вообще не надо вычислять ссылки, а можно использовать структурные ссылки как на листе, так и в коде, которые при повторном открытии файла будут заменены абсолютными (ODS формат их не поддерживает, но это уже неважно: главное - это удобство при их первичном написании, где Вы управляете целыми диапазонами без их вычисления; код становится более читабельным).

Справочно:
Структурные ссылки (English Names)
Таблица[[#This Row]]
Таблица[[#This Row];[Заголовок1]]
Таблица[[#This Row];[Заголовок1]:[Заголовок2]]
Таблица[[#All]]
Таблица[]
Таблица[[#Headers]]
Таблица[[#Data]]
Таблица[[#Totals]]
Таблица[[#Headers];[#Data];[Заголовок1]]
Таблица[[#Data];[Заголовок1]]
Таблица[[Заголовок1]]
Таблица[[Заголовок1]:[Заголовок2]]
Таблица[[#Totals];[Заголовок1]]
<Источник: ссылка (page 22)>.

Структурные ссылки (русские имена)
Таблица[[#Эта строка]]но
Таблица[[#Эта строка];[Заголовок1]]
Таблица[[#Эта строка];[Заголовок1]:[Заголовок2]]
Таблица[[#Всё]]
Таблица[]
Таблица[[#Заголовки]]
Таблица[[#Данные]]
Таблица[[#Итоги]]
Таблица[[#Заголовки];[#Данные];[Заголовок1]]
Таблица[[#Данные];[Заголовок1]]
Таблица[[Заголовок1]]
Таблица[[Заголовок1]:[Заголовок2]]
Таблица[[#Итоги];[Заголовок1]]

Примечание. Использование двух подряд квадратных скобок в некоторых случаях избыточно, если, к примеру, в скобках помещено имя столбца без пробелов в имени. На столбец сослаться просто: надо указать его имя в квадратных скобках после имени диапазона (никаких тебе букв для имён столбцов и номеров для строк; кроме того, не требуется создавать заранее много-много именованных диапазонов - в этом вся соль).
Using structured references with Excel tables


Edit 1:

2. В формуле стоит проверка, где при некоторых условиях может быть исполнена функция ВПР, например:
Код:
=ЕСЛИ(KD16; ВПР(JW16; ДВССЫЛ($JW$13); 7; 0); JX15)
(это и есть дополнительный столбец для перерасчёта укороченного диапазона для основного ВПР). Правильно ли я понимаю, что при KD16=0 (в данной ячейке) ВПР не грузит процесс, так как "обходится" стороной?

Не знаю. В Python, к примеру, False-ветвь не вычисляется, если условие истинно. VBA и LO Basic вычисляет всё. А Calc - не знаю. Пусть ответят другие.
Прим. Кстати, по ссылке выше о волатильности в Excel сказано:
=IF(True,One(),Two()) will only execute the One() udf, and not the Two() udf.
Возможно, в Calc реализовано также (то есть False-ветвь не вычисляется). Проверьте.

Вообще, этот вопрос (called short-circuiting) имеет значение. См. ответ Михаила ниже (моё предположение в отношении функции IF оказалось верным).


Edit 2:

5. Для удобства и наглядности таблицы-словари вынесены в отдельные листы и из одного столбца в таблицах расчётов к ним происходит обращение.
Влияет ли это на быстродействие расчётов? Если всё, что считается в одном файле соединить на одном листе - это улучшить быстродействие?

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


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

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

Sub ToggleAutoCalculate()
Dim bEnabled As Boolean
Dim sPrompt$  'sInfo$

With Thiscomponent
bEnabled = .isAutomaticCalculationEnabled()
.enableAutomaticCalculation(Not bEnabled)
If .isAutomaticCalculationEnabled() Then
sPrompt = " ☑ Автоматический пересчёт формул включен."
Else
sPrompt = " ☐ Автоматический пересчёт формул выключен."
sPrompt = sPrompt & Chr(10) _
& Chr(10) & "F9 – пересчёт изменённых формул в текущем листе." & Chr(10) _
& "Shift+Ctrl+F9 – пересчёт всех формул на всех листах."
End If
MsgBox sPrompt, , "Автоматический пересчёт"
End With
End Sub


Edit 4:
Касательно увеличения быстродействия ВПР (VLOOKUP).
Трюк был дан для Excel (автор Charles at DecisionModels.com, специалист по "быстродействию" ). Однако пришлось добавить то, что выделено красным цветом:
 =IFNA(IF(VLOOKUP(A2;Data;1;1)=A2;VLOOKUP(A2;Data;1;1);"");"")
где Data - это словарь (lookup table), а 1 - номер столбца (в моём примере таблица представлена единственным столбцом).
Несмотря на двойной вызов функции VLOOKUP, данная "конструкция" работает, по заверению автора трюка, в сотни раз быстрее, поскольку функция вызвается с 4-м параметром, равным True (1), то есть в режиме приблизительного поиска.
Смысл формулы в том, что приблизительный поиск (данные в словаре сортировать не обязательно (?)) может вернуть предыдущую запись, то есть не ту, поэтому дополнительно выполняем проверку найденного с искомым. Совпало: повторяем поиск найденного (True-ветвь), иначе "незачёт".

Извлечение из руководства (автоперевод):
Если режим (Mode, тот самый 4-й параметр функции) равен 1 или TRUE или опущен, левый столбец таблицы данных должен быть отсортирован, при этом числа в порядке возрастания появляются перед текстовыми значениями в алфавитном порядке. ВПР решает, где в левом столбце появится искомое значение. Если есть точное совпадение, это найденная строка; если имеется более одного точного совпадения, найденная строка не обязательно является ближайшей к вершине. Если нет точного совпадения, будет найдена строка выше, где значение должно появиться в левом столбце; ошибка #N/A возникает, если этой строки нет в таблице.

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

[economist]

2 млн. яч. с ДВВСЫЛ, кмк, не дадут работать. Предлагаю посмотреть в сторону самых быстрых вычислений из доступных "под капотом" LO - это python+pandas, https://forumooo.ru/index.php/topic,8696.0.html

[kompilainenn]

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

[mikekaganski]

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

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

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

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

=IF(ISERROR(FIND("a";B1;5));"";FIND("a";B1;5))

вычисляет FIND("a";B1;5) дважды. Это замедляет вычисления (вероятно, нам следовало бы стараться обработать формулу так, чтобы избежать таких накладных расходов, но в настоящий момент это только пожелание). Выделение подвыражения в отдельную ячейку позволит избежать двойного вычисления.

Есть ещё один фактор, ограничивающий применение длинных формул: реализация ограничивает распарсенную формулу (4096 / sizeof(pointer)) токенами, что не получится напрямую перевести в длину строки или отдельных элементов, но слишком длинные формулы дадут ошибку при вводе (интересно, что это ограничение позволяет иметь формулы вдвое больше в 32-разрядной программе, чем в 64-разрядной). Ну, это ограничение будет очевидно при вводе формул (выдаст ошибку) - однако если готовить ODS вручную (скажем, формируя XML напрямую), наверное можно получить неочевидную проблему.

[mikekaganski]

 =IFNA(IF(VLOOKUP(A2;Data;1;1)=A2;VLOOKUP(A2;Data;1;1);"");"")
где Data - это словарь (lookup table), а 1 - номер столбца (в моём примере таблица представлена единственным столбцом).
Несмотря на двойной вызов функции VLOOKUP, данная "конструкция" работает, по заверению автора трюка, в сотни раз быстрее, поскольку функция вызвается с 4-м параметром, равным True (1), то есть в режиме приблизительного поиска.
Смысл формулы в том, что приблизительный поиск (данные в словаре сортировать не обязательно (?)) может вернуть предыдущую запись, то есть не ту, поэтому дополнительно выполняем проверку найденного с искомым. Совпало: повторяем поиск найденного (True-ветвь), иначе "незачёт".

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

Во-вторых, при использовании VLOOKUP в режиме SortedRangeLookup (четвёртый аргумент опущен или не FALSE), использовать несортированные данные нельзя. Если Вы попробовали и у Вас получилось, и на этом основании Вы решили так делать, знайте: Вы выстрелили себе в ногу. SortedRangeLookup использует бинарный поиск, выбирая для сравнения данные из середины набора; в зависимости от случайно оказавшегося в середине значения поиск может пойти "как надо" или не как надо. Если Вам (не) повезло, и в Вашем неотсортированном тестовом наборе случайно получилось "как надо", Вы будете полагаться на правильную работу функции там, где её нет и быть не может.

Поэтому да,

данные в словаре должны быть отсортированы.

Примеры: tdf#141989, tdf#144229.

[eeigor]

Возможно, в Excel это не проблема, но в Calc это точно нужно учитывать.

Может быть Владимир (@sokol92) найдёт время протестировать этот момент...
Edit: 2 бинарных поиска в сравнении с одним последовательным перебором значений.

И вопрос к Михаилу. В Calc в функции IF вычисляется False-ветвь, если условие истинно, или нет? В Excel, судя по всему, нет.

[mikekaganski]

В Calc в функции IF вычисляется False-ветвь, если условие истинно, или нет?

Нет.

[sokol92]

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

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

Вообще, этот вопрос (called short-circuiting) имеет значение.

LO Basic, как и VBA, к величайшему сожалению, не поддерживает сокращенное вычисление логических выражений. Об этом, в частности, написано у А.Питоньяка в разделе 3.8.2 книги OOME_4_0.odt (самостоятельная проверка будет очень полезной). То же относится и к некоторым функциям Basic, например, Iif:

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

v=Iif(True, 1, 1/0)

C формулами Calc дело обстоит лучше, хотя следует проверять каждую "подозреваемую" функцию.

[mikekaganski]

LO Basic, как и VBA, к величайшему сожалению, не поддерживает сокращенное вычисление логических выражений.

И это является частью спецификации языка, т.е. это не то, что можно "исправить" в следующем релизе. Например, short-circuiting изначально является частью спецификации C, и не является частью StarBasic. Соответственно, любые побочные эффекты в таких выражениях являются частью языка, на них можно полагаться при программировании - и значит, устранение их будет несовместимым изменением.

Но по-моему, Basic пока что не затрагивался в обсуждении?

[sokol92]

Да, разумеется. Спецификация любого языка - это бессрочный "контракт", который не подлежит изменению (по крайней мере, без модификации имени/версии языка).

В продолжение темы. Проверяем функцию Calc "And" ("И").

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

Function MyFunc(arg)
Msgbox "MyFunc"
MyFunc=0
End Function

Вводим в ячейку B1 формулу:

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

=И(0=1; MYFUNC(A1))
Получаем на экране сообщение "Myfunc". Следовательно, функция Calc "AND" не поддерживает сокращенную логику. То же и в Excel.
Для OR ("ИЛИ") выводы те же.

[sokol92]

 

Но по-моему, Basic пока что не затрагивался в обсуждении?

Прошу прощения за расширение темы. 

[mikekaganski]

В продолжение темы. Проверяем функцию Calc "And" ("И").

Тут опять-таки дело в спецификации. Для IF short-circuiting документировано, для остального - нет.

[mikekaganski]

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

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

[eeigor]

Ну вот, все узнали что-то новое:
IF function only evaluates IfTrue, or IfFalse, and never both; that is to say, it short-circuits.
Михаил, спасибо за участие.