(Электронные таблицы: применяем с пользой; часть 14)
(описание используемых условных обозначений, а также список других публикаций канала по теме электронных таблиц, находится здесь).
Предложенные в предыдущей заметке Вариант 1 и Вариант 2 генератора узоров несмотря на свою работоспособность обладают одним недостатком: чтобы создать и увидеть очередной узор, необходимо сначала сгенерировать новый массив случайных чисел (например нажатием клавиши [Delete] при выделенной какой-нибудь заведомо пустой ячейке), затем выделить и скопировать из ячеек таблицы в буфер обмена соответствующий html-код, после этого – переключиться на открытый в «Блокноте» html-файл и вставить из буфера информацию в него, не забыв сохранить изменения, а в завершение – переключиться на открытый в браузере этот же файл и обновить страницу.
В связи с этим ниже представлено описание создания ещё двух вариантов генераторов узоров, а заодно будут рассмотрены некоторые особенности табличных процессоров, про которые я ранее в своих публикациях ещё ни разу не упоминал.
Вариант 3. Узор из квадратных паттернов с визуализацией средствами табличного процессора
Процесс визуализации каждого нового узора можно сделать быстрым, причём прямо в среде табличного процессора, хотя для этого потребуется более хлопотная подготовка. Способ заключается в построении диаграммы, где вместо стандартных маркеров точек графика (кружки, квадратики, треугольники и т. п.) будут использоваться изображения, загруженные из внешнего файла (подходят популярные графические форматы, в том числе – PNG). Но сначала, чтобы программа могла нормально воспринять и корректно отобразить сами точки, данные из “E2:N11” (см. Вариант 1) нужно рассортировать. Так как в указанном диапазоне ячеек каждому числу соответствует какой-либо определённый паттерн (тип рисунка и вариант его поворота), попробуем представить для начала все случаи выпадения числа 1 в “E2:N11” в виде точек с координатами, соответствующими номеру столбца (координата x) и строки (координата y) в этом рассматриваемом блоке (эти значения для наглядности проставлены в “E1:N1” и “D2:D11”). В столбцах “P” и “Q” нужно перебрать все возможные комбинации номеров столбцов и строк в блоке ячеек “E2:N11”:
Те их сочетания, при которых в “E2:N11” будет находиться ячейка с числом 1, нужно будет отобразить в “R” и “S”. Для этого поместим в “R3” такую формулу:
=ЕСЛИ(ИНДЕКС($E$2:$N$11;$Q3;$P3)=R$1;$P3;-3)
=IF(INDEX($E$2:$N$11;$Q3;$P3)=R$1;$P3;-3)
В ней использована функция
ИНДЕКС( ; ; )
INDEX( ; ; )
Результатом её работы является ссылка на определённую ячейку листа электронной таблицы, которая выбирается с учётом значений аргументов. Первый из них – это диапазон (блок) ячеек, внутри которого ищется нужная. Второй и третий аргументы – номера, соответственно, строки и столбца, отсчитывающиеся от верхнего левого угла блока. У рассматриваемой функции есть ещё четвёртый необязательный аргумент, который в данной ситуации не нужен, так как в качестве первого аргумента указан только один блок ячеек.
Таким образом, формула в “R3” проверяет, стоит ли в ячейке “E2” число 1 и если да, то возвращает значение координаты x, то есть соответствующий номер столбца внутри блока (диапазона) “E2:N11”, а в противном случае формула возвращает число –3, чуть позже станет понятно, почему нужно именно оно.
В ячейке “S3” понадобится указать такую формулу:
=ЕСЛИ(R3=-3;3;-$Q3)
=IF(R3=-3;3;-$Q3)
Как легко видеть, она возвращает число 3, если в “R3” оказалось –3, в остальных случаях возвращается номер строки (координату y), взятый с обратным знаком. Для чего это надо тоже станет ясно позже, а пока следующим действием должно быть размножение формул в “R3” и “S3” маркером заполнения вниз вплоть до 102-й строки:
На основе полученного ряда пар чисел (координат) в “R3:S102” можно построить диаграмму в виде графика с изображением только точек (маркеров) без соединения их линиями (Пособие, с. 58). Выглядеть это будет примерно так:
В процессе построения графика в окне «Мастера диаграмм» были сняты флажки «Подписи в первой строке», «Подписи в первом столбце», «Ось X» и «Ось Y» («Отображать сетки»), а также «Показать легенду».
Легко заметить, что маркеры точек на графике располагаются в четвёртом квадранте соответственно тому, как расположены числа 1 в блоке “E2:N11”, а ячейки с другими числами отображаются как одна и та же точка с координатами (–3; 3), то есть во втором квадранте – вот для чего были необходимы составленные именно таким образом формулы в столбцах “R” и “S”. Дело в том, что когда график на диаграмме строится по диапазону, в котором отсутствуют прогалы (пустые ячейки), программа изображает точки как надо, иначе она может повести себя неадекватно, отобразив данные в соответствии с ведомой только ей внутренней логикой.
Полученная диаграмма нуждается в дополнительной настройке и прежде всего – в изменении маркера.
Вызовите настройки вида маркера:
В выпадающем списке с типами маркеров выберите загрузку рисунка для него из внешнего файла:
Переключитесь в режим правки диаграммы, выполнив двойной щелчок по ней и вызовите настройки вида маркера:
В появившемся диалоговом окне «Ряды данных» на вкладке «Линии» выберите в выпадающем списке «Значок» загрузку рисунка маркера из внешнего файла:
Откроется стандартное диалоговое окно, в котором нужно будет указать файл “1.png”. В итоге диаграмма приобретёт следующий вид:
Теперь стала понятна необходимость других дополнительных настроек.
Прежде всего следует удалить элемент «Название диаграммы», линии координатной сетки, а в свойствах маркера убрать обрамление синего цвета.
В параметрах каждой оси надо явно указать границы от –1 до 11 (для горизонтальной) и от –11 до 1 (для вертикальной), благодаря чему маркер точки с координатами (–3; 3) перестанет показываться. После этого останется подправить размеры самой диаграммы, добившись, чтобы края маркеров (паттернов) при соседстве лишь касались друг друга, а не перекрывались:
Ну что ж, самое главное сделано. Теперь можно заняться сортировкой всех выпадающих в “E2:N11” значений числа 2. Для этого достаточно последовательно скопировать через буфер обмена формулы из “R3” и “S3” в “T3” и “U3” соответственно, а затем размножить их вниз, до 102-й строки включительно – благодаря абсолютной адресации в выражениях формул, они сами подстроятся нужным образом для правильной работы:
После можно будет на основе значений в “T3:U102” добавить на диаграмму ещё один ряд данных (Пособие, с. 78) и настроить его маркеры в виде изображения из файла “2.png”. Действуя далее аналогичным образом, следует добавить отображение остальных паттернов и получить в итоге готовый генератор узоров, способный создавать рисунки, подобные тем, что приведены ниже.
Вариант 4. Узор из шестиугольных паттернов с визуализацией в среде табличного процессора
После того, как пример быстрой визуализации с квадратными паттернами разобран, вполне понятно, что генератор узоров «на шестиугольниках» делается аналогично, хотя формулы для координаты x нужно подкорректировать, ведь здесь необходимо, чтобы каждый маркер-рисунок, стоящий в чётном ряду, был смещён на половину единицы вправо. Рассмотрим в качестве примера формулу в ячейке “R3” для данного варианта:
=ЕСЛИ(ИНДЕКС($E$2:$N$11;$Q3;$P3)=R$1; $P3+ЕСЛИ($Q3/2=ЦЕЛОЕ($Q3/2);0,5;0);-3)
=IF(INDEX($E$2:$N$11;$Q3;$P3)=R$1; $P3+IF($Q3/2=INT($Q3/2);0,5;0);-3)
Фрагмент, выделенный курсивом, проверяет номер строки в блоке “E2:N11” на чётность (если чётное число разделить пополам, то частное будет целым, в случае же нечётного числа получится дробная величина, целая часть которой, разумеется, не равна ей самой) – в этом случае он каждый раз дополнительно прибавляет к координате x значение 0,5. По этой причине также желательно в параметрах горизонтальной оси диаграммы явно задать границы в интервале от –1 до 12.
Примеры получающихся «каракулей» приведены ниже.
Файлы с примерами: xlsx / ods / zip-архив с паттернами
Перечень публикаций на канале