Создание эффективных формул с помощью @cell и @row

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

Чем больше становятся таблицы, тем более важную роль с точки зрения производительности приобретает способность создавать эффективные формулы. В этом могут помочь аргумент @cell и нотация @row.

Оптимизация вычислений в ячейках с помощью аргумента @cell

Если вы хотите выполнить вычисления с помощью формул, которые анализируют диапазоны ячеек (например, SUMIF, SUMIFS, COUNTIF и COUNTIFS), в критериях функций можно использовать аргумент @cell. Аргумент @cell выполняет вычисления в каждой строке, в то время как основная функция (например, SUMIF) проверяет соответствие диапазона критерию. Так формула становится более эффективной.

На изображении ниже приведены данные для примера. Чтобы сложить стоимость всех задач, которые были выполнены в 2014 году, можно использовать следующую формулу:

=SUMIF([Дата выполнения]1:[Дата выполнения]5; YEAR(@cell) = 2014; Стоимость1:Стоимость5)

Результат вычислений равен 875,75 доллара (суммарная стоимость всех задач, выполненных в 2014 году).

СОВЕТ. Добавьте функцию IFERROR, чтобы основная функция не возвращала сообщение об ошибке, если в аргументе @cell будет обнаружена пустая ячейка. Пример: =COUNTIF(Дата:Дата; IFERROR(YEAR(@cell); 0) = 2016)

Повышение производительности формул с помощью @row

Нотация @row позволяет улучшить производительность за счёт автоматического изменения ссылок на ячейки в столбце (например, для статуса задачи). 
Предположим, мы хотим с помощью формулы автоматически изменять символы в столбце статуса этой таблицы:

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

=IF(Выполнено3 <> 1, IF(TODAY() - [Дата выполнения]3 > 0; "Красный"; IF(TODAY() - [Дата выполнения]3 > -3; "Жёлтый"; "Зелёный")))

Формула выше содержит несколько ссылок на ячейки в строке 3. При добавлении новой строки над строкой 3 они обновятся автоматически и будут ссылаться на строку 4. Точно так же обновятся формулы во всех строках ниже. Чем больше формул нужно изменить, тем сильнее страдает производительность в Smartsheet.

Чтобы повысить производительность таблиц, можно заменить номера строк в ссылках на ячейки на @row. В результате формула будет выглядеть следующим образом:

=IF(Выполнено@row <> 1; IF(TODAY() - [Дата выполнения]@row > 0; "Красный"; IF(TODAY() - [Дата выполнения]@row > -3; "Желтый"; "Зеленый")))

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