Штрих-код давно стал обыденностью, и необходимостью. Если компания хочет выйти в любой магазин, крупнее палатки с шаурмой, ей нужно обеспечить штрих-коды на товаре. Существует много программ, которые его сгенерировать, но когда речь заходит про автоматизацию и аналитику, когда нам нужно сделать это в Excel, готовых рецептов нет.
В этой статье из двух частей я расскажу как сделать штрих-код в Excel (аналогично его можно сделать и в Google Sheets), разберу алгоритм, и "на закуску" выложу готовую таблицу.
План такой:
- Изучаем, из чего состоит штрих-код и как считать проверочную цифру.
- Делаем шаблон для ввода данных, добавляем проверку ввода.
- Разбиваем код поразрядно (по цифрам), чтобы в дальнейшем с ним работать.
- Разбираемся, как кодируются отдельные цифры
- Преобразовываем отдельные цифры в 95 значений 0 или 1 - черное или белое.
- Размечаем поле для 95 модулей (отдельных элементов штрих-кода)
- Используем условное форматирование, чтобы сделать заливку модулей черным или белым.
Поехали?
СогласновWikipedia, самый распространненый штрих-код EAN 13 состоит из:
- Префикс GS1 (3 цифры)
- Код производителя (переменная длина)
- Код продукта (переменная длина)
- Проверочный разряд (1 цифра)
Сделаем простую табличку, куда можно ввести эти данные (пока без проверочного кода - мы же его не знаем!).
Чтобы не допустить ошибок при создании штрих-кода, нужно ввести проверку данных: ограничить максимальные вводимые числа, предусмотреть правильное форматирование, и сигнализировать цветом, если данные не подходят. Примеры всех настроек проверки данных и условного форматирования в галерее выше.
Теперь можно приступить к вычислению проверочной цифры. Все 12 цифр кода складываются между собой, согласно весу указанному в таблице ниже и дополняется до ближайшего кратного 10 числа:
Например, для кода 123456789012x, где x - это неизвестная проверочная цифра, вычисление будет следующим:
1*1 + 2*3 + 3*1 + 4*3 + 5*1 + 6*3 + 7*1 + 8*3 + 9*1 + 0*3 + 1*1 + 2*3 = 92
Дополняем до ближайшего кратного 10 числа: 100-92 = 8. Это и есть наша проверочная цифра.
Чтобы разобрать число по разрядам в Excel, можно воспользоваться функцией QUOTIENT (ЧАСТНОЕ) и POWER (СТЕПЕНЬ). Для примера выше (123456789012) посмотрим как найти разряды:
=QUOTIENT ( 123456789012; POWER(10;11)) = 1
=QUOTIENT(123456789012 - POWER(10;11)* QUOTIENT(123456789012; POWER(10;11)) ; POWER(10;10)) = 2
=QUOTIENT(123456789012 - POWER(10;10)* QUOTIENT(123456789012; POWER(10;10)) ; POWER(10;9)) = 3
=QUOTIENT(123456789012 - POWER(10;9)* QUOTIENT(123456789012; POWER(10;9)) ; POWER(10;8)) = 4
... и так далее. Таким образом мы можем выписать поразрядно наш код в ячейки: G2:R2, а в ячейке S2 посчитать проверочный разряд:
=MOD(10-MOD(G2+I2+K2+M2+O2+Q2+3*(H2+J2+L2+N2+P2+R2);10);10)
Посмотрим, как должен выглядеть готовый штрих-код:
Первая цифра вынесена влева, дальше идет блок из 6 цифр, потом центральная секция и еще блок из 6 цифр. Для простоты работы в нашем файле под разрядами 2-7 поставим букву L, под разрядами 8-13 поставим букву R, при этом первая цифра кодируется опосредованно, за счет выбора "словаря", каким образом кодируется первая группа из 6 цифр.
Каждая цифра (кроме первой) представленна в штрих-коде последовательностью из 7 полос, и может быть закодирована тремя способами. Назовем эти способы L, G и R. Можно создать отдельный лист в Excel, например, "Справочники" и скопировать туда таблицу:
И еще одну таблицу:
Теперь в нашем арсенале есть все, чтобы преобразовать цифровой штрих-код в набор семизначных двоичных "слов", описывающих чередование черных и белых полос - этим мы займемся в следующей части статьи.
А вот и обещанный файл Excel: https://yadi.sk/i/Z3DE9XpY_tgyKg
До скорых встреч!