В первой части статьи мы разобрались, как устроен штрих-код и подготовились к кодированию цифрового значения штрих-кода в непосредственно черные и белые полосы.
Ссылка на первую статью, для тех кто пропустил: https://zen.yandex.ru/media/id/5f24070dd98a994308ce5e45/kak-sdelat-shtrihkod-v-excel-i-googletablicah-pri-pomosci-formul-bez-makrosov-chast-1-5f4634a39fc11f364e3c623d В конце статьи также можно скачать Excel-файл, с которым удобно начать работу над примером.
В прошлой статье мы закончили на том, что подготовили два "словарика" - по какому шаблону кодируются цифры, и непосредственно коды, отвечающие этим шаблонам. Осталось дело за малым - закодировать и нарисовать полосы :-)
Поехали!
Для начала, добавим в наш файл определение шаблона для кодирования. Мы знаем, что правая часть штрих-кода (то, что мы обозначили буквами R), кодируется по шаблону R, в то время как левая, кодируется по шаблону L или G. Это зависит от первой цифры кода. Поэтому добавим на лист формулу, состоящую из INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ). Впрочем, если вам удобнее, можете использовать VLOOKUP (ВПР) для аналогичного результата:
=INDEX(Справочники!B18:B27;MATCH(G2;Справочники!A18:A27;0))
На листе Справочники в диапазоне A17:C27 размещен наш "словарик", определяющий шаблон кодирования для левой части кода.
Приступим к кодированию. Штрих-код состоит из 95 модулей равной ширины. Каждый модули может быть либо черным (1) или белым (0). Слева направо эти модули:
- 3 модуля для маркера начала: 101
- 42 модуля (7 на каждую цифру) для цифр 2-7, а также чтобы непосредственно закодировать цифру 1.
- 5 модулей для центрального маркера: 01010
- 42 модуля для цифр 8-13 (7 на каждую цифру)
- 3 модуля для маркера конца: 101
Разметим место для штирх-кода. Его можно разместить на отдельном листе, или прямо на листе с формой. Для себя я выбрал вариант размещения на листе с формой, чтобы сразу видеть как меняется штрих-код при изменениеи цифр - у меня он будет размещаться в столбцах с X по DN. Вернее, даже с с W по DN, чтобы разместить первую цифру.
Для начала, давайте переведем цифры в ячейках H2:S2 в битовое представление. Заменим в ячейке H3 букву "L" на формулу:
=MID($C$8;COLUMN(H$2)-7;1)
и скопируем эту формулу в ячейки I3:M3. MID (ПСТР) позволяет выбрать часть текста из первого аргумента, начиная с позиции второго аргумента. COLUMN (СТОЛБЕЦ) возвращает номер столбца - нам нужно, чтобы понимать номер цифры в коде. Должно получиться, как на скриншоте ниже:
Теперь введем в ячейку H4 следующую формулу (а потом скопируем ее до S4):
=INDEX(Справочники!$B$2:$D$11;MATCH(H2;Справочники!$A$2:$A$11;0);MATCH(H3;Справочники!$B$1:$D$1;0))
Как и в начале статьи, мы смотрим значение каждой цифры в справочнике, в зависимости от шаблона кодирования (L,G, R). Здесь уже не получится обойтись формулой VLOOKUP, нужно использовать связку INDEX и MATCH, именно из-за этой универсальности я и предпочитаю пользоваться этими формулами. Если сделать вертикальное отображение, должно получиться похоже на скриншот:
Мы уже близко! Мы смогли закодировать каждую цифру нашего кода. Осталось нарисовать полосы.
Выбираем строку, например 4, и в колонках X, Y, Z ставим, соответственно, 1, 0, 1 - это наш маркер начала. Дальше в колонках BQ, BR, BS, BT и BU ставим цифры 0, 1, 0, 1, 0 соответственно. Это маркер середины. И в колонках DL, DM и DN тоже поставим 1, 0, 1 - маркер конца.
Сконструируем формулу для заполнения пространства между маркерами. В зависимости от номера столбца (у меня начиная с 27), формула должна выбирать один бит из побитового представления цифр кода. Для первой цифры формула должна быть такой:
=MID($H$4;COLUMN(AA3)-26;1)
Чтобы расширить ее для первых 6 цифр, можно использовать функции OFFSET (СМЕЩ) и QUOTIENT. Про вторую функцию я писал в части 1 этой стати, а первая функция позволяет отступить от заданной ячейки на нужное количество строк и столбцов.
=MID(OFFSET($H$4;0;QUOTIENT(COLUMN(AA3)-27;7));COLUMN(AA3)-26-QUOTIENT(COLUMN(AA3)-27;7)*7;1)
Теперь можно учесть смещение из-за центрального маркера и адаптировать функцию и для второй группы из 6 цифр:
=IF(COLUMN(AB3)<=42+26;MID(OFFSET($H$4;0;QUOTIENT(COLUMN(AB3)-27;7));COLUMN(AB3)-26-QUOTIENT(COLUMN(AB3)-27;7)*7;1);
MID(OFFSET($H$4;0;QUOTIENT(COLUMN(AB3)-32;7));COLUMN(AB3)-31-QUOTIENT(COLUMN(AB3)-32;7)*7;1)
)
В формуле выделил две части - одна, курсивом, для первых 6 цифр, а вторая, с подчеркиванием, для вторых 6 цифр. Получаем такую картину:
Всего 95 цифр, по количеству модулей в штрих-коде. Можно проверить, что все цифры генерируются автоматически и меняются при изменении изначального штрих-кода.
А теперь приступим к раскраскам. Выделим диапазон X4:DN7 и создадим новое правило условного форматирования (см. на скриншоты в галерее):
В настройках условного форматирования выбираем использовать формулу для определения, какие ячейки нужно форматировать, и вводим формулу:
=VALUE(X$4)=1
В настройках формата выбираем черную заливку. Давайте проверим что получилось, возьмите любую упаковку, найдите штрих-код с нее и вбейте цифры в форму на нашем листе. А потом попробуйте отсканировать код.
Получилось? Почти. Но код уже рабочий, его можно просканировать. Например, в приложении Озон на телефоне. Напишите в комментариях, если удастся распознать код со скриншота выше! :-)
Но нам осталось еще немного подправить наш штрих-код. Давайте выделим область W3:DO7 и установим для нее белую заливку, чтобы убрать границы ячеек. А еще нужно убрать нолики и единички в X3:DN3 выделяем, делаем цвет текста белым, а в настройках условного форматирования для диапазона делаем шрифт черным. Таким образом, если стоит 0, то он будет белым цветом на белом фоне, мы не увидим. Если единичка, она по правилу условного форматирования будет черной на черном фоне. И давайте сделаем маркер начала, конца и середины выступающими - для этого отредактируем формулу в условном форматировании:
=IF(VALUE(X$4)=1;IF(ROW(X4)=6;OR(AND(COLUMN(X4)>=24;COLUMN(X4)<=26);AND(COLUMN(X4)>=70;COLUMN(X4)<=74);AND(COLUMN(X4)>=116;COLUMN(X4)<=118));TRUE);FALSE)
Эта формула позволяет форматировать черные полосы в 6 строке только для модулей маркера.
Обратите внимание, в формулах иногда используется знак $ в ссылке на ячейку перед номером строки или буквой столбца, а иногда и там и там. Знак $ означает, что ссылка абсолютная, а отсутствие - означает, что ссылка относительная. Подробнее про это я написал в этой статье: https://zen.yandex.ru/media/id/5f24070dd98a994308ce5e45/znak--v-excel-i-google-sheets-5f3c221470f86555aa62308f
Еще немного, и мы будем близки к идеалу. Выделим ячейки AA6:AG6 и отформатируем их так, чтобы текст центровался относительно выбранного диапазона (см. скриншот). Точно также выделем диапазоны для оставшихся 11 цифр и отцентруем текст.
В ячейки W6, AA6, AH6 и так далее, соответственно введем формулы: =G2, =H2, =I2 и т.д. Ну и ширину колонки можно уменьшить до 0,13, начиная с X и до DN.
Вот ссылка на готовый Excel-файл: https://yadi.sk/i/YqijSGaB-Trevw
Таким же образом можно разместить много штрих-кодов на листе, например, чтобы сделать наклейки. Но придется потрудиться.
Пишите в комменатрии, если есть вопросы или расскажите о своих интересных проектах. До новых встреч.
