Google Sheets — Время расчёта формулы

Правильный канал о Таблицах (Google Sheets): @google_sheets

Здравствуйте, товарищи!

Люди пишут формулы, чтобы решить какую-то задачу, считают что-то. Когда одна и та же задача решается несколькими разными способами, у людей возникает вопрос "какой из вариантов лучше?" Критериев, вообще говоря, много, например:

что проще и быстрее написать?
что проще поддерживать, изменять?
сложно ли сломать?
что быстрее работает?

Мы про скорость расчёта формулы сейчас поговорим. Будем замерять время расчёта: у какого варианта меньше, тот и лучше.

Имеющиеся решения

Подходов было много. Самый простой - посмотреть глазками: один вариант адски тормозит, прогресс-бар еле ползёт - плохой вариант.

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

Чисто формульный вариант через REDUCE

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

Нам понадобится REDUCE() (дока).

Вместе с лямбдами Гугл выкатил и Named Functions (Именованные Функции). Их можно импортировать из одной таблицы в другую. Ещё статья с картинками от Бени. И не менее полезная официальная документация.

Создаём именованную функцию BENCHMARK(func):

=N(REDUCE(0; {0; 1; 0}; LAMBDA(acc; cur; IF(cur = 0; LAMBDA(x; x)(NOW()) - acc; acc + 0 * ROWS(func())))) * 24 * 60 * 60)

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

Работает так:

Начальное значение (первый параметр REDUCE()) - 0. Не нужно по сути, и не испортит нам логику внутри лямбды.
Передаём простой диапазон ({0; 1; 0} - одна колонка: 0, потом 1, потом снова 0), по которому пройдётся REDUCE(): каждый его элемент будет передан в LAMBDA(). По краям (на 0) мы будем отмечать время, а в середине выполним функцию.
В LAMBDA() происходит следующее: если нам дали 0, то получаем текущее время (NOW()) и вычитаем из него acc (то, что REDUCE() вернула в прошлый раз). При первом 0, у нас в acc начальное значение (тоже 0), то есть после вычитания в acc будет просто текущее время.
Теперь в LAMBDA() попадает 1 - будет выполнена наша функцию func(). Результат функции нас не интересует, поэтому мы заворачиваем его во всеядную ROWS(), которая всегда вернёт число, умножаем на 0 и прибавляем acc. В acc у нас в этот момент дата-время из первого шага, таким образом оно не изменится.
В LAMBDA() снова попадает 0 - и мы снова из NOW() вычитаем acc. Так как теперь в acc у нас предыдущее время, то результатом будет разница - время выполнения функции. Часть с REDUCE() закончилась.
Результат получился в днях. Чтобы получить время в секундах, надо доносить результат на 24 * 60 * 60, что мы и делаем.
Чтобы таблицы не умничали с форматом, обернули всё в N() - имеем на выходе обыкновенное число.

NOW() используется несколько странно: завёрнута в LAMBDA(x; x)(...). Это необходимо, чтобы формула не пересчитывалась при любом изменении в таблице, как это происходит обычно для NOW() (и для TODAY(), и для RAND() и пр.). Подробнее про это (с кучей примеров) написано на SO.

Не претендуем на оригинальность, но в дикой природе этот вариант нам пока не встречался. Пользуйтесь на здоровье.

Пример использования BENCHMARK()

В BENCHMARK() надо передать работающую формулу.

Дать ссылку на ячейку с формулой - неправильно. Так вы передадите результат работы.

Собственно, тут маленькая заморочка. Передаётся формула так:

Копипастим формулу
Убираем = в начале
Оборачиваем в LAMBDA()
Вот в таком виде и передаём

Например, у нас есть формула, которая считает сумму квадратов от 1 до 1000000:

=SUM(ARRAYFORMULA(SEQUENCE(1000000)^2))

А вот так мы замеряем время расчёта:

=BENCHMARK(LAMBDA(SUM(ARRAYFORMULA(SEQUENCE(1000000)^2))))

Среднее время выполнения - BENCHMARKN()

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

Это мы сделаем в BENCHMARKN():

=N(REDUCE(0; {0; SEQUENCE(number); 0}; LAMBDA(acc; cur; IF(cur = 0; LAMBDA(x; x)(NOW()) - acc; acc + 0 * ROWS(func())))) * 24 * 60 * 60 / number)

Тут всё то же самое, но добавился один параметр number - количество запусков. Собственно, в прошлый раз у нас была одна 1, соответствующая запуску, а теперь number чисел отличных от 0. Не 0 - запуск, а их как раз number штук, так что будет number запусков func(). Не забываем поделить итог на number, чтобы получить среднее время выполнения.

На том же примере суммы квадратов, используется так:

=BENCHMARKN(LAMBDA(SUM(ARRAYFORMULA(SEQUENCE(1000000)^2))); 100)

Табличка с полезными функциями

Мы для вас подготовили таблицу, где уже реализовали BENCHMARK() и BENCHMARKN().

Импортируйте и пользуйтесь. Ну, или можете скопипастить по старинке.

Там же можно посмотреть другой пример использования (сумма накопительным итогом, а не просто сумма).

Сумма накопительным итогом через SCAN() значительно быстрее работает, чем старый метод через SUMIF()

Иногда будем добавлять в таблицу какие-нибудь полезные функции.

На этом всё. Спасибо за внимание.

Ссылки

Named Functions Library - наша таблица с полезными именованными функциями, которые можно к себе импортировать
Про именованные функции - статья Бэна Коллинза и официальная документация
Про Lambda-функции - ещё статья Бэна Коллинза и официальная документация
Документация на REDUCE()
Рецепты от Бена по борьбе с тормозами в таблице
Полезная заметка от Player0 на SO про то, как зафиксировать результат NOW(), RAND() и пр. с помощью LAMBDA().

Другие мои статьи