Электронные таблицы (Calc)

Хороший трудолюбивый парень Quang (уж не знаю, как это произносится вслух... и парень ли это...) с помощью Calc'а проделал большую работу по созданию большущей матрицы вида

Это здесь для примера табличка нарисована маленькой. А на самом деле она гораздо больше, 153x153.

И захотелось парню отметить некоторые из ячеек цветом. Причем не просто раскрасить вручную некоторые из них, а так, чтобы при изменении значений раскраска появлялась или исчезала автоматически по какому-то условию. В качестве условия Quang описал такое: «Если значение N больше, чем одна десятая от X в этой же строке, то закрасить ячейку красным».

Причина скорее всего в том, что парень пытался указывать положение X для каждого сравнения явно, то есть как-то так

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

А про худший случай и думать не хочется!

153х153 (размер матрицы) - 153 (количество X) = 153*152 = 23256 раз пришлось бы вызвать меню условного форматирования и установить параметры!

Что же делать? Ответ прост: думать! Нужно придумать формулу, которая была бы одинаковой для всех ячеек матрицы. И начать нужно с анализа задачи.

Давайте еще раз посмотрим на табличку. Что такого особенного в ячейках с X? Они расположены на диагонали квадрата, то есть номер строки каждой такой ячейки равен её номеру столбца. Ну, это просто! Функции Calc'а ROW() и COLUMN() как раз и возвращают номер строки и номер столбца. Немного сложнее получить содержимое ячейки по этим номерам, но и эту задачку можно решить!

Если матрица расположена в верхнем левом углу листа, начиная с клетки A1, то диагональные элементы можно получить формулой =INDIRECT(ADDRESS(ROW();ROW()))

Но Quang ничего не говорил про расположение матрицы на листе. И потому, на всякий случай, будем считать, что квадрат с числами может находится где угодно.

Поскольку диагональ квадрата всегда начинается с его первой клетки, то можно попробовать заменить функции INDIRECT() и ADDRESS() на смещение относительно этой первой клетки OFFSET(;;)

Отлично! Формула, которая будет сравнивать текущую ячейку с диагональной ячейкой в той же строке матрицы, выглядит так:

На этом можно было бы и остановиться, если бы не одно «но»: диагональные ячейки всегда будут больше, чем одна десятая от них и, значит, будут раскрашены так же. А мы-то добиваемся эффекта «Вау, красотища!»

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

Теперь улучшим наши формулы. Казалось бы, что здесь еще улучшать? Но нет предела совершенству.

Дело в том, что мы составили эти формулы, исходя из предположения, что матрица начинается с верхнего левого угла листа, с ячейки A1. Но это ведь не обязательно будет так. Например, перед матрицей может быть какой-то заголовок или предварительные расчеты. А значит и начало матрицы может быть в любой другой ячейке.

Внесем в формулы поправку на эту величину и получим окончательный вариант:

(ROW()-ROW()) = (COLUMN()-COLUMN())

OFFSET(;ROW()-ROW();ROW()-ROW())/10

А теперь посмотрим как это делается.

Обсудить на форуме

•  Источник