Этикетки в OpenOffice - без слияния

Или: учимся работать с косвенной адресацией ячеек в электронных таблицах. Основная цель этого поста - показать возможности функции indirect(), ну а выбранный пример - это последний, где я ее применил

В большинстве офисных пакетов предусмотрена такая штука как "рассылка писем", "стандартные письма", "печать слиянием" и т.п. Имеется в виду, что существует некий табличный список, например:

SysName IT# AssetTag Ordinal
cnt300901 18006 JR0046780 1
cnt300911 18007 JR0046781 2
cnt300921 18008 JR0046782 3
cnt300931 18009 JR0046783 4
cnt300941 18010 JR0046784 5
cnt300951 18011 JR0046785 6
cnt300961 18012 JR0046786 7
cnt300971 18013 JR0046787 8
cnt300981 18014 JR0046788 9
cnt300991 18015 JR0046789 10

Где SysName - сетевое имя компа, IT# - внутренний номер для службы ИТ, AssetTag - инвентарный номер, Ordinal - порядковый номер.
(последнее поле по логике должно быть крайним слева, но я в реальном документе добавил его в последний момент, и чтобы не сломать форматирование, добавил справа. впрочем, на теоретические основы этого поста сей факт не влияет)



И есть бланк - болванка, заготовка, шаблон и т.п. - в которую надо в нужные места впихнуть данные из списка, чтобы из бланков получились готовые этикетки. Например, вот так:

Но общая проблема всех этих "слияний" состоит в том, что:

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

Вот и мне понадобилось распечатать десяток наклеек на системники по списку, но возиться с перетаскиванием табличек с места на место мне лень. А вот автоматизировать свою работу, чтобы экономить время в будущем - это не лень. И вот как я поступил.
Всё делалось в OOo 4.1.7, но с учетом перевода формул вполне открылось в excel 2016.

На одном листе рабочей книги у меня этот список. Причем он вовсе не обязан быть упорядоченным. На другом - шаблон этикетки с несколькими формулами. И формулы - самая изюминка моей идеи.
Казалось бы, чего проще - вставь ссылки на нужные ячейки списка в нужные места бланка и радуйся жизни. Но какой же это геморрой - потом эти ссылки править. Нужны какие-то универсальные формулы, и их есть у меня. Знакомьтесь: функция INDIRECT (ДВССЫЛ в переводе от MS). Она позволяет обратиться к ячейке или диапазону не по обычному адресу, а по адресу, содержащемуся в текстовом аргументе.
Непонятно? А вы попробуйте вставьте формулу типа "=concatenate("A";"1")", чтобы обратиться к ячейке А1. Не получится - вставится просто текст "А1", а вовсе не значение указанной ячейки. Для таких случаев и создана функция косвенной адресации indirect().
Если кто программировал в свое время ПМК, тот может помнить, что кроме команд типа "ИП рег" (из такого-то регистра памяти), были и команды типа "КИП рег" - из такого-то регистра памяти, косвенно - данные брались из регистра, номер которого хранился в регистре "рег". Аналогично ведет себя и функция indirect - она возвращает не текст своего аргумента, а значение ячейки, адрес которой содержится в ее аргументе.

В своих таблицах я обычно использую такую связку (все пробелы вставлены только для удобочитаемости):

indirect( concatenate( " 'имя_листа!' "; адрес_ячейки ))

на выходе concatenate() имеем полный адрес ячейки в виде: 'имя_листа'!адрес_ячейки, например 'лист1'!A5. Имя листа взято в апострофы для надежности, адрес ячейки в этом не нуждается. И результатом формулы indirect("'лист1'!A5") будет значение ячейки А5 с листа "Лист1".

Но как это всё связано с темой поста? Самым direct-образом :-) В нужных местах бланка вставлены формулы, которые состоят из кусков текста и ссылок на содержимое списка. А косвенная адресация используется, чтобы не заниматься переписыванием формул.

Вот как список хранится на листе таблицы:

Начинается с 19 строки и занимает столбцы с A по D. И необходимость переписывания формул связана как раз со сменой номеров строк: при копировании фрагмента табличный процессор правит относительные ссылки, но строго на то смещение, на которое копия отличается от исходной формулы
Например, если в ячейке А1 будет формула =B1, то при копировании этой формулы в A3, она изменится на =B3. Если мы скопируем шаблон со вставленными формулами на новое место (нам же надо несколько этикеток на листе), то номера строк в формулах изменятся не на 1 строку, а на несколько.

Исходно (получаем ИТ-номер): (бланк!А2) =лист1!b19
Высота бланка 3 строки, значит его копия разместится, начиная с (бланк!А5), и формула, взятая из (бланк!А2) превратится в =лист1!b22, хотя нам надо взять следующую строку, =лист1!b20.
Использование абсолютных ("через доллар") ссылок тоже неудобно, потому что потом придется уже в формулах поиском и заменой подставлять правильный номер строки во всех формулах бланка сразу.
Функции типа vlookup() - вариант. Но в силу своей природы они работают довольно медленно. На небольших списках вроде взятого для примера это не существенно, но в длинных списках, да еще и с выборкой нескольких десятков значений, тормоза могут быть заметны. Функции, оперирующие с координатами ячеек работают быстрее.

Как я вышел из положения? (наклейка имеет ширину 1 столбец и начинается с ячейки А3)
Формула для получения ИТ-номера из списка:

=INDIRECT(CONCATENATE("'Лист1'!B";B3))

 Коричневое нам не особо важно, мы уже знаем, что это функция indirect(). Куда интереснее её параметры.
Функция concatenate() сама по себе тоже малоинтересна. Что насчет ее параметров?
Как видно, на выходе получается нечто вида 'Лист1'!Bxxx, где ххх - это содержимое ячейки B3 на текущем листе. А в этой ячейке у меня записано число 19, и вся формула читается как "взять содержимое ячейки B19 с листа "Лист1"

Аналогичная формула создает текст во второй строке наклейки:

CONCATENATE(INDIRECT(CONCATENATE("'Лист1'!C";B3)); " - Моноблок СОТИН № ";INDIRECT(CONCATENATE("'Лист1'!G";B3)))

Не буду расписывать ее подробно, в ней используются две подобных конструкции indirect-concatenate. И смысл в том, что в них обеих номер строки, из которой берутся ячейки из столбцов С и G, также хранится в ячейке B3 на текущем листе.

(серым выделена текстовая константа, просто чтобы не отвлекать взгляд от самих формул)

Что нам это даёт? Сформированную таким образом заготовку наклейки вместе с ячейкой В3 копируем в нужное место, например, на три строки ниже, из ячейки А3  в ячейку А7. Поскольку В3 - это относительная ссылка, то она, разумеется, заменится на В7. Но в ней будет тоже, что и раньше значение 19, поэтому содержимое наклейки, несмотря на изменение формул, останется прежним. И нам останется вписать в В7 новое значение - 20, чтобы брать данные из 20-ой строки листа со списком.

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

Поскольку мы не обязаны копировать шаблон строго ниже предыдущего, то для оптимального размещения на листе делаем это в два или больше столбцов. Скриншот из реального документа, поэтому замазаны начала инвентарников, они ведь вовсе не JR, как в примере.