Общие вопросы
Печать
Применение формул
О проекте
Контакты
Полезные ссылки
Карта сайта
Вход для участников

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

Значение или Формула? Печать
Электронные таблицы (Calc)
Автор Владислав Орлов   
21.09.2011 г.
На одном из форумов встретил интересный вопрос.

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

X

N

N

N

N

N

N

X

N

N

N

N

N

N

X

N

N

N

N

N

N

X

N

N

N

N

N

N

X

N

N

N

N

N

N

X

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

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

Как признался сам Quang: I tried to do it manually with the conditional formatting, but it takes a large amount of time (Я пытался сделать это вручную с помощью условного форматирования, но это отнимает слишком много времени)

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

calc_matrix_1.jpg 

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

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

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

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

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

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

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

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

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

calc_matrix_2.jpg

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

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

calc_matrix_3.jpg 

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

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

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

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

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

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

matrix.gif

 

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

Последнее обновление ( 12.08.2012 г. )
 
« Пред.   След. »

Главная arrow Применение формул arrow Значение или Формула?

MyOOo.ru, 2008 — 2017. Хостинг предоставлен компанией Netangels