Условное форматирование - как лучше организовать?

[siberian-man]

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

При создании своего велосипеда-гроссбуха задался вопросами оптимального подода к созданию и использованию УФ. Полистал несколько тем на форуме - поожих вопросов вроде бы не обсуждали.

УФ хороши тем, что позволяют акцентировать внимание на каких-то определенных данных. Вот несколько примеров.

Один простой - для подсветки ячейки с текущей датой

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

$A2 = TODAY()
И один сложный - для подсветки строки при несоответствии категории и подкатегории (кстати, похожую формулу я нашел здесь же на форуме)

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

AND($B2<>"";$C2<>"";ISNA(MATCH($C2;INDEX(Категории_Матрица;0;MATCH($B2; Категории_Список;0));0)))
Я задумался. Насколько критично, в плане производительности ЭТ, вычислять условия в самих УФ? Не будет ли более оптимальным вычисления формул вынести на лист - куда-нибудь вправо от основных данных, а в УФ - только проверять соответствующие ячейки на выполнение условия срабатывания УФ?

Насколько я понимаю работу ЭТ, отрисовка таблицы - дорогостоящая операция, соответственно: TODAY() перевычисляется для каждой строки, вторая формула - для каждой строки и ячейки в каждой строке. То есть имеет два варианта: оставить вычисления в УФ или вынести в таблицу ценой утяжеления самого файла.

[sokol92]

Я задумался. Насколько критично, в плане производительности ЭТ, вычислять условия в самих УФ? Не будет ли более оптимальным вычисления формул вынести на лист - куда-нибудь вправо от основных данных, а в УФ - только проверять соответствующие ячейки на выполнение условия срабатывания УФ?

Для начала Вы можете провести эксперименты и выяснить, в каких случаях в LibreOffice Calc пересчитываются формулы УФ. Для этого можно использовать в формуле УФ пользовательскую функцию (UDF), которая будет где-то фиксировать факт своего вызова.

В идеальном случае формулы УФ не должны вычисляться для ячеек, которые не показаны на экране (но реальность, естественно, отличается от идеала).

Расскажите нам о результате.

P.S. Кстати, есть замечательная статья Н.Павлова "Ад Условного Форматирования" про условное форматирование в Excel.

[siberian-man]

использовать в формуле УФ пользовательскую функцию (UDF),

Все очень-очень грустно и печально!

Даже любое перемещение по листу (влево-вправо, вверх-вниз) приводит к вызову УФ.

Еще одно интересное наблюдение: УФ работает, видимо, на опережение и применяется к строке, предшествующей верхней видимой, и строке, последующей после нижней видимой.

UDF постянно писала на диск адрес ячейки - через некоторое время это приводило к краху приложения.

замечательная статья Н.Павлова "Ад Условного Форматирования"

Да-да. Знакомая ситуация. Даже последовательные CTRL-Z / CTRL-Y превращают все болото.

[siberian-man]

предшествующей верхней видимой

Вспомнил. У меня УФ для подсветки строки, если имеются разрывы - пустые строки в блоке данных. Может быть потому срабатывает для строки выше первой видимой.

[sokol92]

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

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

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

Наличие некоторых "летучих" (volatile) функций в формуле превращает ячейки в вечно "грязные": TODAY, INDIRECT, ...

P.S. Вот интересные эксперименты известного эксперта по Excel. Когда-то и я проводил подобные опыты (с Excel).

[economist]

Вместо УФ в Calc можно использовать обычные формулы ЕСЛИ(B2;"🛡";"") в узких столбцах шириной один emoji-символ и выводить собсна только их. Уже протухла ссылка на большую классную статью про тесты UX таблиц, но помню несколько неожиданный выбор большинства фин-пользователей по удобству:

1) панели инструментов - серые, почти однотонные значки
2) вместо УФ (цвет шрифта и заливка) - узкая колонка с emoji рядом с числами
3) итоги, статусы, рекомендации - вверху, а не внизу экрана
4) веб-верстка (адаптивная), а не фиксированная

На самом деле есть очень эффективные реализации УФ, например df.Styler() в Pandas. Он используется мощь и легкость JS/CSS для динамической подсветки и расшифровки значений в таблицах на десятки тыс строк в Блокнотах и разовое применение подсветки при экспорте. Также быстры всякие iTables.

Это можно использовать и в Calc, раскрасив сам отчет однострочным Python-скриптом:

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

pd.read_excel('my_table.ods', sheet='Отчет').style.format(мой_словарь_УФ).to_excel('Отчет.ods')

Полученный отчет можно просто раз отзеркалить: Лист-Вставить Лист из Файла - Связь

Про то как УФ сделано в Calc/Excel и во что оно превращается после серии небрежных "протягиваний" и копирования - это действительно, выстрел в ногу.