Barcode generator

Het Excel bestand dat hoort bij deze pagina over de barcode generator (voor EAN-13/GTIN-13 streepjescodes) staat onderaan de tekst en mag gratis worden gedownload voor eigen gebruik.

Uitdaging

Op internet vind je verschillende barcode generators, die door enthousiastelingen in Excel zijn geprogrammeerd. Echter, deze enthousiastelingen zijn veelal professionele Excel gebruikers. Zij programmeren daarom macro’s en coderen in Visual Basic om hun barcode generator tot leven te brengen. Maar omdat ik mijn blog vooral schrijf voor beginners en (licht)gevorderden, wilde ik een begrijpbare barcode generator maken. Eentje die EAN-13 streepjescodes maakt en slechts uit relatief eenvoudige tabellen en Excel formules bestaat. En waar je als lezer wat van kunt leren.

Europese artikelnummering

De EAN-13 (tegenwoordig GTIN-13) streepjescode bestaat uit Europese artikelnummering en zwarte streepjes. Om een barcode generator te kunnen maken, moet je eerst begrijpen hoe deze artikelnummering en streepjes in elkaar zitten. In de basis bestaat de gehele EAN code uit 13 getallen:

• De eerste 2 getallen vormen de systeemcode en geven aan door welke organisatie en in welk land de streepjescode is uitgegeven. Zo staat 87 voor GS1 Nederland (voorheen EAN Nederland). Het wil niet altijd zeggen dat het betreffende product ook in het land van code-uitgifte is gemaakt.
• De volgende 5 getallen (3 t/m 7) heten het aansluitnummer en vormen samen met de eerste twee het bedrijfsnummer van degene die de code heeft aangevraagd. Zo zijn codes die beginnen met 87 10400 de codes die door GS1 Nederland aan Albert Heijn zijn gegeven. Het aansluitnummer van Albert Heijn is namelijk 10400.
• De daaropvolgende 5 getallen (8 t/m 12) vormen het artikelnummer.
• Het 13e getal is een controlegetal dat wordt berekend.

Zwarte streepjes

De 13-cijferige code wordt afgebeeld als een zogenaamde streepjescode. Elk getal uit de code kan in principe uit zeven zwarte, verticale streepjes bestaan. In het voorbeeld rechts heb ik het getal 6 weergegeven volgens het R-patroon. In dit geval bestaat de 6 daarom uit twee zwarte streepjes, namelijk op de 1e en 3e positie van de mogelijke 7 posities voor zwarte streepjes. De overige posities blijven ongekleurd (de ‘wit’-posities).

Om de juiste zwarte streepjes met de barcode generator te generen, moet je de volgende uitgangspunten kennen:

• Het eerste getal van de 13-cijferige code is direct een uitzondering; dit getal staat vóór de streepjescode en wordt zelf niet omgezet in zwarte streepjes.
• De streepjescode begint vervolgens met een startblok, bestaande uit het patroon wit-streepje-wit-streepje-wit, met strepen die langer zijn dan de overige streepjes.
• De getallen op positie 2 t/m 7 worden afgebeeld als korte zwarte streepjes (en witte posities). Per getal zijn er 2 mogelijkheden: Het getal wordt getoond volgens de L-code of volgens de G-code.
• In het midden van de streepjescode staat een tussenblok, wederom bestaande uit het patroon wit-streepje-wit-streepje-wit, met strepen die langer zijn dan de overige streepjes.
• De getallen op positie 8 t/m 13 worden afgebeeld als korte zwarte streepjes (en witte posities). Elk cijfer wordt getoond volgens de R-code.
• Na het 13 cijfer staat een eindblok, wederom bestaande uit het patroon wit-streepje-wit-streepje-wit, met strepen die langer zijn dan de overige streepjes.

Excel stap 1: Hulptabellen maken

Een streepjescode bestaat dus uit 13 getallen die op drie manieren vertaald kunnen worden in zwarte streepjes. Via L-, G- of R-codes wordt aangegeven hoe een getal wordt uitgedrukt in zwarte streepjes (1) en witte posities (0). Zo wordt het getal 3 volgens de G-code weergegevens als:

wit-streepje-wit-wit-wit-wit-streepje

Of je een L-, G- of R-code voor een getal nodig hebt, wordt bepaald door het eerste getal uit de 13-cijferige code. Ook hiervoor is een standaardtabel beschikbaar.

Om straks getallen in zwarte streepjes om te kunnen zetten, ga ik vier tabellen aanmaken op tabblad ‘Rekenmodel’. Deze tabellen zijn alle terug te vinden op internet:

1) Een tabel horend bij het eerste getal uit de code, waarin je kunt aflezen welk type code je nodig hebt voor de overige 12 getallen.
2) Een tabel met de L-code.
3) Een tabel met de G-code.
4) Een tabel met de R-code.

De tabellen zien er in Excel als volgt uit:

Stap 2: Invoermogelijkheid EAN-13 code

Alles draait om de EAN-13 code. Mijn volgende stap is daarom het aanmaken van 12 velden op tabblad ‘Barcode’ waarin de code getal voor getal door de gebruiker ingevoerd kan worden.

Het 13e veld is het controlegetal dat met een vaste formule – die je overal op internet kunt vinden – wordt berekend. Dit getal hoeft dus niet ingevuld te worden door de gebruiker:

=ALS(10-REST(3*(B2+D2+F2+H2+J2+L2)+(A2+C2+E2+G2+I2+K2);10)=10;0;
10-REST(3*(B2+D2+F2+H2+J2+L2)+(A2+C2+E2+G2+I2+K2);10))

In essentie telt deze formule een aantal keer de waarden van de eerste 12 losse getallen bij elkaar op en deelt ze door 10. Het getal achter de komma (REST) wordt van 10 afgetrokken en de uitkomst is het controlegetal. Als de uitkomst 10 blijkt te zijn, wordt het controlegetal 0 (ALS…..=10;0;….).

Stel de berekening luidt 96/10=9,6 dan is het getal achter de komma 6 en wordt het controlegetal 10-6=4.
Stel de berekening luidt 110/10=11,0 dan is het getal achter de komma 0. Het controlegetal wordt 10-0=10 en dus 0 volgens de ALS formule.

Stap 3: Bepalen welk cijfer welke code (L, G of R) krijgt

Ik neem als voorbeeld de ingevoerde EAN-13 code – namelijk 8710506073683 – over op tabblad ‘Rekenmodel’ in de cellen A15:M15. Vervolgens ga ik voor elk getal bepalen welke code (L, G of R) van toepassing is, in de cellen B16:M16. Pas als ik dat weet, kan ik straks de juiste zwarte streepjes tekenen. Om vast te stellen of ik de L, G of R codes nodig heb, gebruik ik het eerste getal van de EAN code (de ‘8’). Ik gebruik vervolgens tabel A1:M11 die ik van internet heb gehaald en een INDEX formule om de juiste codes te vinden.

Formule B16
=INDEX(opbouw_ean13;VERGELIJKEN(opbouwindicator;getal1;0);VERGELIJKEN(B14;getal2tm13;0))

Let op! Ik verwijs naar celreeksen die ik namen heb gegeven.

In normale taal staat in de gebruikte INDEX formule:
1) Geef de waarde uit tabel ‘opbouw_ean13’ (=B2:M11)
2) die hoort bij de rij waar de ‘opbouwindicator’ (=A15 dus ‘8’) overeenkomt met ‘getal1’ (=A2:A11) en
3) die hoort bij de kolom waar de waarde uit cel B14 (‘2e’) overeenkomt met ‘getal2tm13’ (=B1:M1)

Wellicht moet je even wennen aan het gebruik van celreeksen met een naam (‘opbouw_ean13’) in plaats van adres (B2:M11). Maar als je zelf namen hebt aangemaakt, worden formules juist makkelijker leesbaar en maak je minder fouten bij het kopiëren.

De formule uit B16 kopieer ik naar rechts en alle getallen uit de EAN-13 code worden nu voorzien van de juiste L-, G- of R-code

Stap 4: Cijfers omzetten naar een streepjespatroon

Nu ik weet welk type code bij welk getal hoort, kan ik in de andere aangemaakte tabellen zoeken naar het bijbehorende streepjespatroon (uitgedrukt in 0-en en 1-en).

Voor het gemak noem ik de inhoud van de patroontabellen ‘Ltabel’, ‘Gtabel’ en ‘Rtabel’. De kolommen waarin ik moet zoeken naar de EAN getallen (onder de letters L, G en R) noem ik ‘Lwaarde’, ‘Gwaarde’ en ‘Rwaarde’. De rijen waarin ik moet zoeken naar de positie van de zwarte streepjes noem ik ‘posL’, ‘posG’ en ‘posR’

Voor het 1e getal (‘8’) hoef ik niets te doen want dat staat altijd vóór de streepjescode in een wit vlak.

Ik zet in cel A18 tot en met cel A24 daarom de getallen 1 tot en met 7 voor de zeven posities waarop eventueel een zwart streepje terecht moet komen (1) of niet (0).

Vervolgens maak ik in cel B18 de volgende formule aan.

Formule B18
=INDEX(INDIRECT(B$16&”tabel”;);VERGELIJKEN(B$15;INDIRECT(B$16&”waarde”;);0);VERGELIJKEN(B$16&$A18;INDIRECT(“pos”&B$16;);0))

Wat doet de formule in B18?

1) In algemene zin gaat het om een INDEX formule want ik moet in de L-, G- of R-tabel op zoek naar uitkomsten.
2) In het eerste deel van de INDEX formule staat altijd aangegeven waar de uitkomsten staan waarnaar ik op zoek ben. De uitkomsten staan in de ‘Ltabel’, ‘Gtabel’ of ‘Rtabel’. Ik moet echter expliciet aangeven in welk van de drie tabellen INDEX actief moet zoeken. Dat doe ik met de formule INDIRECT(B16&”tabel”;). In B16 staat in dit geval de letter ‘L’ dus eigenlijk staat hier INDIRECT(Ltabel;). De INDEX formule richt zich in dit voorbeeld op de uitkomsten in ‘Ltabel’. Had ik dan niet alleen INDEX(B16&”tabel”;…) zonder INDIRECT kunnen gebruiken? Nee! Door INDIRECT te gebruiken snapt Excel dat ‘Ltabel’ niet zomaar een stukje tekst is, maar een concrete verwijzing naar een celreeks met die naam.
3) Om het rijnummer vast te stellen moet in reeks INDIRECT(B16&”waarde”;) – dit is dus ‘Lwaarde’ – worden gezocht naar de waarde uit B15 (‘7’).
4) Om het kolomnummer vast te stellen moet in reeks INDIRECT(“pos”&B16;) – dit is dus ‘posL’ – worden gezocht naar de waarde uit B16&A18 (‘L1’).

Kortom, de INDEX formule toont het getal in tabel ‘Lwaarde’ dat hoort bij rij ‘7’ en kolom ‘L1’. De dollar-tekens worden gebruikt om bij kopiëren van deze formule naar alle overige cellen de juiste rij- en kolomwaarden vast te zetten.

Stap 5: Grafisch de barcode opbouwen

De laatste stap betreft het grafisch opbouwen van de barcode op tabblad ‘Barcode’. Om te beginnen verwijs ik in cel N6 naar =Rekenmodel!A15 zodat het eerste cijfer (dat geen zwarte streepjes behoeft) vóór de barcode komt te staan (‘8’).

Startblok
De streepjescode begint vervolgens met een startblok. Dit creëer ik door in cel O4 tot en met cel S4 de waarden 0, 1, 0, 1 en 0 te zetten. In cel O5 tot en met S5 gebruik ik vervolgens een voorwaardelijke opmaak functie. Als in cel O4 een 1 staat, maak ik cel O5 en cel O6 zwart. Staat daar een 0 dan laat ik de genoemde cellen wit. Als in cel P4 een 1 staat, maak ik cel P5 en cel P6 zwart. Staat daar een 0 dan laat ik de genoemde cellen wit. Etcetera.

Cijferblok positie 2 tot en met 7
Als het startblok is aangemaakt, ga ik aan de slag met de getallen op positie 2 tot en met 7 uit de EAN code. Voor elk getal maak ik 7 kolommen aan. In die 7 kolommen neem ik de 7 gevonden waarden uit de patroontabel bij stap 4 over. Dus in cel T4 verijs ik naar =Rekenmodel!B18, in cel T5 verwijs ik naar =Rekenmodel!B19, etcetera. Dit herhaal ik totdat ik alle 42 cellen voor de getallen op positie 2 tot en met 7 heb gevuld. Daarna ga ik – net als bij het startblok – met een voorwaardelijke opmaak formule de cellen in rij 5 zwart kleuren als in de cellen van rij 4 de waarde 1 wordt aangetroffen.

Tussenblok
In het midden van de streepjescode staat een tussenblok. Hier ga ik exact hetzelfde te werk als bij het startblok.

Cijferblok positie 8 tot en met 13
Na het middenblok ga ik aan de slag met de getallen op positie 8 tot en met 13. Hier ga ik exact hetzelfde te werk als bij voorgaand cijferblok.

Eindblok
Aan het einde van de streepjescode staat een eindblok. Hier ga ik exact hetzelfde te werk als bij het start- en middenblok.

Als laatste geef ik de kolommen O tot met DI de breedte 0,36 mee.

De barcode generator werkt nu. Scan de barcode uit het meegeleverde Excel document maar eens en ontdek om welk product het gaat 🙂

Tot slot een aantal tips

• De tabbladen in het onderstaande bestand zijn beveiligd ZONDER wachtwoord. Om de beveiliging te verwijderen klik je met de rechtermuisknop op een tabblad en kies je voor ‘Blad beveiligen…’ Deze beveiliging is alleen aangebracht om het per ongeluk verwijderen van formules te voorkomen.
• Loop door alle zichtbare formules heen zodat je begrijpt hoe de programmering van de barcode generator werkt. Bekijk ook de onzichtbare voorwaardelijke opmaak formules.
• Verdiep je in de INDIRECT functie om meer te leren over verwijzingen op basis van tekstwaardes.
• Kijk via Excel menu ‘Invoegen’ >> ‘Naam’ >> ‘Naam definiëren’ naar de namen die aan verschillende celreeksen zijn gegeven.