Мой шаблон для ценников в Google Sheets

Есть у нас небольшой семейный бизнес — интернет-магазин изделий ручной работы. Однажды собрались мы на выставку и встал вопрос ценников. Чтобы сразу были видны название, цена и QR-код для перехода к описанию и оплате.

Сначала я принялся рисовать шаблон в Illustrator, но понял, что для сотни позиций рехнусь делать руками каждый отдельный ценник и генерировать для них QR-коды. А если ещё и изменения какие-то случатся…

И тут меня осенило — Гугл-таблицы! Повозившись день, я сделал шаблон, в котором автоматизировал всё, что смог:

• Список товаров импортируется из нашей таблицы, где считается себестоимость и конечная цена изделия. Оттуда же импортируются ссылки на страницу товара на сайте.
• С помощью функции СЦЕПИТЬ к ссылкам добавляются UTM-метки, чтобы потом в аналитике можно было посмотреть, кто пришёл на сайт с ценника.
• С помощью API Гугла генерируются QR-коды (со штрих-кодами не пробовал, т. к. без надобности, но и с ними что-то придумать, наверное, можно).
• Потом все эти данные собираются в свёрстанный шаблон ценников. Если что-то меняется в каталоге товаров — сразу же меняется и в ценниках.
• Логотип и наименование продавца задаются в отдельном служебном листе.

Ну не красота ли?

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

Сделайте копию таблицы на свой диск, чтобы получить полный доступ к редактированию. В меню нажмите «Файл — Создать копию» и выберите папку для сохранения.

Список товаров

Делаем отдельный лист, в котором будет таблица со всеми необходимыми колонками. К ценникам в законах есть определённые требования, которые здесь легко учесть. Конкретно в моём шаблоне я добавлял не всё, т. к. это выставочный вариант.

Вы можете вести весь список прямо в таблице-генераторе, можете импортировать из какой-то другой таблицы. Это работает и в Гугл-таблицах, и в Экселе. Я использовал формулу импорта диапазона для колонок «Тип», «Наименование», «Цена» и «Ссылка на сайт»:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/bla-bla-bla/";"Расчёт розницы!a6:a")

Первый параметр — ссылка на таблицу, из которой происходим импорт, второй — лист и диапазон ячеек для импорта данных.

В столбце F прописал UTM-метку, которую будем сцеплять со ссылкой на страницу сайта, чтобы получить итоговую ссылку для генерации QR-кода. Когда всё будет готово, эти служебные столбцы можно скрыть, чтобы не отвлекали.

Формула в столбце D для формирования ссылки включает в себя функцию ЕСЛИ:

=if(E2=""; ""; CONCATENATE(E2; F2))

Сделано это для того, чтобы ячейка оставалась пустой, когда ссылки нет. В противном случае вместо ссылки будет подставляться только хвост с UTM-меткой, а QR-код сгенерируется некорректный.

И, наконец, столбец с QR-кодом:

=if(D2="";""; image("https://quickchart.io/qr?chs=250x250&text="&ENCODEURL($D2)))

Используем функцию IMAGE. В параметре chs указываем размеры QR-кода, в параметре text — адрес ячейки со ссылкой, которую нужно превратить в QR-код.

Функция ЕСЛИ используется и здесь, чтобы QR-код не выводился, если ячейка со ссылкой пуста.

С таблицей товаров закончили.

Логотип и наименование организации

У меня два глобальных параметра, которые будут неизменны на каждом ценнике — наименование организации и логотип. Таких параметров может быть сколько угодно. Главное, чтобы на ценнике потом хватило места.

Важный момент по логотипу. Его нужно вставить именно в ячейку. Не поверх ячейки. Иначе вы не сможете сослаться на логотип из листа с ценниками.

Служебный лист для расчёта размеров

Тут уже моя личная заморочка. Я хотел, чтобы ценники имели точный и управляемый размер. Нагуглил соотношение миллиметров к пикселям, проставил формулы и пошёл верстать ценники.

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

Ценники. Размеры и подгонка для печати

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

Не делайте размер шрифта надписей меньше 7. Я пробовал делать 6 — получается так мелко, что сложно разобрать.

Я добавил общие размеры в миллиметрах, потом поделил для каждой строки и колонки, потом добавил формулу, чтобы самая широкая строка и колонка вычислялась, и добавил формулы перевода миллиметров в пиксели. Для наглядности. Чтобы видеть, какой размер задать.

Можете так не заморачиваться. Просто мне хотелось максимальной точности размера.

Когда сделаете один ценник, посмотрите, как он будет выглядеть и сколько места займёт при печати. Это поможет понять, как располагать лист: горизонтально или вертикально. А также сколько ценников в одном ряду у вас будет.

У меня при горизонтальной ориентации страницы на листе помещается 10 ценников — по пять в ряд. А вот при вертикальной ориентации умещалось девять — три ряда по три ценника.

Масштаб 100% (не зря же с размерами заморачивался). Поля — широкие. Это помогло избежать переносов, когда следующие ценники частично попадали на одну страницу, а остальная часть переносилась на следующую. Значение полей можно задавать и вручную.

В разделе форматирования снимите галочку «Показать линии сетки», чтобы не печаталась сетка таблицы.

Формулы для подстановки значений из таблицы товаров в ценники

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

В первом ценнике вручную проставлена цифра 1. Во втором уже формула: =F12+1. Соответственно, в третьем и дальше до пятого формула ссылается на предыдущий ценник и прибавляет единицу.

Начиная со второй строки первый в ряду ценник имеет формулу: =F12+5. Т. е. я прибавил 5 к номеру первого ценника с предыдущей строки. У вас это значение будет зависеть от количества ценников в одной строке.

Всё. Теперь осталовь добавить формулы для вставки данных товаров и копировать ценники до бесконечности.

Логотип и другие глобальные данные. Ссылаемся на служебный лист, на ячейку с логотипом:

='Наименование и логотип'!$D$5

Не забудьте про символы $ перед буквой столбца и номером ячейки. Иначе при копировании Гугл-таблица (да и Эксель) поставят следующую букву / номер и лого не подставится. То же касается и других глобальных данных.

Тип, наименование товара, цена, QR-код вставляются по единому принципу. Отличия только в букве колонки:

=OFFSET('Список товаров'!$A$1; F12; 0)

Вся магия кроется в функции OFFSET. Мы ссылаемся на заголовок столбца таблицы со списком товаров. Жёстко фиксируем эту ячейку символами $. Следующим параметром задаём смещение строки. И вот тут ссылаемся на ячейку с номером ценника, которые мы проставили выше. Теперь при копировании ценников формула будет смещаться на величину номера ценника. Таким образом вам нужно лишь скопировать столько ценников, сколько у вас товаров в таблице.

Последнее значение — это смещение по столбцам. Ставим 0, т. к. это смещение нам не нужно.

Вот, собственно, и всё. В тексте выглядит несколько сложнее, чем в жизни, но, уверен, имея перед глазами шаблон самой таблицы, вы разберётесь.

Дублирую ссылку на шаблон: