Wie du dynamisch die Prüfziffer eines Barcodes berechnest

In seinem kürzlich veröffentlichten Beitrag „Barcode EAN bzw. GTIN Prüfziffer mit Excel berechnen, Code erstellen und per App einscannen“ beschreibt Andreas Unkelbach [1] die Digitalisierung von Belegen im Rechnungswesen mittels eines Barcodes.

Er beschreibt u.a., wie mit dem „Modulo 10 Berechnungsverfahren mit der Gewichtung 3“ in Excel eine Prüfziffer für eine 9-stellige Ziffernfolge berechnet werden kann. Die 9 Ziffern plus der Prüfziffer ergeben dann den 10-stelligen Barcode.

Unkelbach lässt offen, wie die Excelformel dynamisch auf unterschiedlich lange Ziffernfolgen reagieren kann. Bei einer EAN für eine Handelsware z.B. sind 12 Ziffern die Basis, bei der ISBN ebenfalls.

Das Problem der Dynamisierung habe ich für diesen Beitrag aufgegriffen, wobei ich das grundsätzliche Verfahren hier nicht noch einmal erläutere. Das kann bei Unkelbach [1] oder bei Wikipedia [2] nachgelesen werden.

Starre Berechnung der Prüfziffer

Unkelbach verwendet diese Formel, um die Prüfziffer für eine 9-stellige Ziffer zu berechnen:

=REST(10-REST(SUMMENPRODUKT((TEIL($A$1;{1;2;3;4;5;6;7;8;9};1))*{3;1;3;1;3;1;3;1;3});10);10)

Aus der Ziffernfolge 130719789 (in A1) errechnet sich somit die Prüfziffer 9, der Barcode (10-stellig) lautet dann 1307197899.

Hat die Ziffernfolge nun 10, 11 oder 12 Stellen, funktioniert die Formel nicht mehr. Erst wenn die in geschweiften Klammern dargestellten Matrizen (als Matrixkonstanten) entsprechend erweitert wurden, kann wieder gerechnet werden. Zu beachten ist allerdings, dass die Matrix mit den zwischen 1 und 3 wechselnden Faktoren rechts mit einer 3 beginnen muss. Bei 10 Stellen z.B. wäre das dann:

{1;3;1;3;1;3;1;3;1;3}

 

Dynamische Berechnung der Prüfziffer

Nachfolgende Tabelle enthält 14 Ziffernbeispiele, wobei Pos. 1 die durch Unkelbach verwendete Ziffernfolge ist. Die Pos. 13 ist die ISBN-Nr. eines Buches und Pos. 14 die EAN eines Handelsartikels. Alle anderen Ziffern mit unterschiedlichen Stellen sind frei erfunden.

Barcode1

Für die Berechnung der Prüfziffer in Abhängigkeit von der Länge der Ziffernfolge habe ich eine Formel verwendet, die auf die von Unkelbach aufsetzt, die einfachste Art, das Problem zu lösen, denke ich. Die unterschiedlichen Längen werden lediglich durch die Verwendung der WENN-Funktion unterschieden.

Die Formel lautet:

=WENN(LÄNGE($B8)=9;REST(10-REST(SUMMENPRODUKT((TEIL($B8;{1;2;3;4;5;6;7;8;9};1))*{3;1;3;1;3;1;3;1;3});10);10);WENN(LÄNGE($B8)=10;REST(10-REST(SUMMENPRODUKT((TEIL($B8;{1;2;3;4;5;6;7;8;9;10};1))*{1;3;1;3;1;3;1;3;1;3});10);10);WENN(LÄNGE($B8)=11;REST(10-REST(SUMMENPRODUKT((TEIL($B8;{1;2;3;4;5;6;7;8;9;10;11};1))*{3;1;3;1;3;1;3;1;3;1;3});10);10);WENN(LÄNGE($B8)=12;REST(10-REST(SUMMENPRODUKT((TEIL($B8;{1;2;3;4;5;6;7;8;9;10;11;12};1))*{1;3;1;3;1;3;1;3;1;3;1;3});10);10)))))

Zugegeben, die Formel ist nicht ganz kurz, unterscheidet aber sichtbar die Längen einer Ziffernfolge zwischen 9 und 12. Weitere Fälle mit anderen Längen lassen sich einfach in die Formel einpflegen. Und: Eine lange Formel rechnet nicht langsamer, nur weil sie lang ist.

Weitere Formeln in der Tabelle sind:

C8:  =LÄNGE($B8)

E8:  =B8&D8

Beide Formel werden mit dem Ausfüllkästchen bis Zeile 21 herunter gezogen.

 

Quellen:

[1] http://www.andreas-unkelbach.de/blog/?go=show&id=721

[2] https://de.wikipedia.org/wiki/Internationale_Standardbuchnummer

Advertisements

Autor: Gerhard Pundt

Auf meiner Site https://clevercalcul.wordpress.com geht es um die Tabellenkalkulation mit Excel. Es wird über Funktionen, Diagramme, Basiswissen u.a.m. geschrieben.

6 Kommentare zu „Wie du dynamisch die Prüfziffer eines Barcodes berechnest“

  1. Halllo Gerhard,

    die Fortsetzung des Artikels empfinde ich als sehr gelungen, insbesondere da eigentlich Artikelnummern oder sonsitge Barcodes in der Regel tatsächlich eine fixe Länge (oder zumindest im Bereich von bis) liegen. Entsprechend elegant ist hier deine Lösung :-).

    Besonders gut gefallen hat mir natürlich das ISBN Beispiel, was aber vermutlich eine private Vorliebe für bestimmte Zahlen ist ;-)….

    Mein ursprünglicher Gedanke war es einmal gewesen dynamisch per LÄNGE() und ZEILE() sowohl die Länge des Feldes als auch der Multiplikatoren per Formel zu bestimmen und spätestens da bin ich im Artikel dann ausgestiegen. Entsprechend nachvollziehbar ist es sicherlich, dass ich da irgendwann ausgestiegen bin und an eine Lösung wie deine gar nicht erst gedacht hatte 🙂

    Von daher verweise ich gerne in meinen Artikel auf diene Lösung und werde die Formel direkt in meine Simulationstabelle übertragen 🙂

    Viele Grüße
    Andreas

    Gefällt mir

    1. Hallo Andreas,
      danke für deinen Kommentar. Eigentlich hatte ich vor, die Matrixkonstanten dynamisch zu bilden, dann hätte das WENN-Prozedere gespart werden können. Das erwies sich doch als schwierig, so dass ich die vorliegende Variante gewählt habe. Bei 4 angenommenen Längen geht das auch noch.
      Die Aufgabe ist aber reizvoll, ich bleibe mal dran.
      Grüße
      Gerhard

      Gefällt mir

  2. Ich bin gespannt. An der dynamischen Matrixkonstanten bin ich grandios gescheitert und hatte den Knoten auch nicht mehr ohne Probleme lösen können… da für die konkrete Fragestellung allerdings tatsächlich nur eine beschränkte Anzahl an möglicher Längen in Frage kam (bzw. für diesen speziellen Barcode tatsächlich nur 9+1) bin ich einfach neugierig auf alternative Lösungsmöglichkeiten neugierig gewesen und war sehr froh, dass du die zugeworfene Kopfnuss tatsächlich aufgefangen und auf eine praktische Art geknackt hast :-).

    Viele Grüße
    Andreas

    Gefällt mir

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s