Sammlung ==> #04 Vornullen-Beispiel

Vornullen-Beispiel:

Hinweis: Zum Vornullen-Beispiel gab es in der Vergangenheit 2 Hinweise von Besuchern meiner Website, die auch Excel im Einsatz haben. Mit freundlicher Genehmigung dieser Excel-User finden Sie unten die entsprechenden Ergänzungen.

Ausgangspunkt ist der Import von Feldnummern (z.B. Artikel- o. Personalnummern), die im Fremdsystem eine geringere Stellenanzahl haben als dies im Eigensystem erforderlich ist. Im Beispiel werden im Fremdsystem maximal 3stellige Personalnummern geführt, im Eigensystem jedoch exakt 6stellige. Die Daten wurden bereits importiert und es liegt eine Spalte mit den 1-3stelligen Nummern vor. Diese Nummern sollen nun in das 6stellige System überführt werden, in dem entsprechende Nullen den Fremddaten vorangestellt werden. Aus bspw. der Nummer 120 soll 000120 werden. Zu beachten ist, dass im Fremdsystem bei Zahlen kleiner oder gleich 99 keine Vornullen im 3stelligen System vorhanden waren, so dass folgende Konvertierungsbeispiele möglich sind:

Die Umsetzung erfolgt mittels der Excelfunktionen LÄNGE und SVERWEIS. Für die Erklärung werden hier zunächst Hilfsspalten eingeführt, die entsprechende Zwischenschritte verdeutlichen. Anschließend werden die Zwischenschritte in einer Funktion zusammengefasst. Die Erläuterungen beziehen sich auf folgende Tabelle:

Spalte A enthält die importierten Personalnummern aus dem Fremdsystem. In Spalte B wird zunächst die Zeichenanzahl der Nummern mittels der Funktion LÄNGE ermittelt. Würden die Daten in der Excelzelle A1 beginnend stehen, wäre in der Zelle B1 der Funktionseintrag entsprechend: =LÄNGE(A1). Diese Funktion kann nach unten ausgefüllt/kopiert werden.

Spalte C ermittelt die jeweiligen Vornullen, die den importierten Personalnummern vorangestellt werden müssen. Hierfür muss ein Hilfssheet erstellt werden, auf das dann der SVERWEIS zugreifen kann. Das Hilfssheet sieht für das Beispiel so aus und steht (irgendwo) am Rand der Tabelle:

Spalte J enthält die möglichen Zeichenlängen 1, 2 und 3 der import. Personalnummern. Spalte K enthält die entsprechenden Vornullen in der passenden Anzahl. Dabei wurden die Zellen der Spalte K vor der Eingabe mit einer speziellen Zellformatierung 'Text' versehen. Hierzu wurden die K-Zellen markiert und über die Menufolge FORMAT | ZELLEN | ZAHLEN | KATEGORIE auf TEXT gesetzt. Damit können dann mehrere Nullen hintereinander eingegeben werden. Mit diesem Hilfssheet können nun in Spalte C die entsprechenden passenden Vornullen mittels SVERWEIS gezogen werden - Funktionseintrag der Zelle C1: =SVERWEIS(B1;J$1:K$3;2). Die Funktion wird nach unten ausgefüllt/kopiert.

In Spalte D werden die ermittelten Vornullen in C abschliessend mit den import. Personalnummern der Spalte A verknüpft - Funktionseintrag der Zelle D1: =C1&A1. Die Funktion wird nach unten ausgefüllt/kopiert.

Die bisherige Darstellung verwendet mit den Spalten B und C Hilfsspalten bzw. Zwischenschritte. Diese Zwischenschritte können jedoch auch komplett in eine Funktion zusammengefasst werden. (Das Hilfssheet jedoch muss jedoch bestehen bleiben.) Trotz der zusammengefassten Funktion ist jedoch o. g. Weg über Zwischenspalten zu empfehlen, da

- die Ergebnisse der Zwischenspalten für andere Zwecke Informationen liefern können;
- die Zwischenspalten ausgeblendet werden können.

Wenn man die Zwischenschritte in eine Excelfunktion zusammenfassen würde, entsteht folgender Funktionseintrag:

=SVERWEIS(LÄNGE(A1);J$1:K$3;2)&A1

Das dargestellte Verfahren lässt sich auf vielerlei andere Schnittstellenprobleme übertragen. Im folgenden soll das SVERWEIS-Hilfssheet aufgestellt werden, falls die import. Personalnummern 1-6stellig sind und ohne Vornullen. Um die entsprechenden Vornullen zu ziehen, sieht in diesem Fall das Hilfssheet wie folgt aus:

Sollten bzgl. des Vornullen-Beispiels Rückfragen bestehen, steht der Autor Ihnen gerne zur Verfügung.

ERGÄNZUNG 16.01.2006:

Im Januar 2006 erreichte mich ein klasse Tipp von Christoph Sternberg zu der Vornullen-Problematik. Mit seiner freundlichen Genehmigung veröffentliche hier seine wesentlich kompaktere Lösung zum Sachverhalt:

"Hallo,

nette Seite, das "Vornullen-Problem" #4 läßt sich aber auch ganz simpel
ohne SVERWEIS und Hilfstabelle lösen:

=LINKS("000000";6-LÄNGE(A1))&A1

oder mit variabler Stellenanzahl und Anzahl Stellen in G1:

=WIEDERHOLEN("0";$G$1-LÄNGE(A1))&A1

Schönen Gruß,
Christoph Sternberg

Das ganze kann man dann noch mit "WENN" verwurschteln und eine
Überlaufskennzeichnung durch eintsprechende Anzahl "*" bei zu kleinem
Wert für die Stellenanzahl hinzufügen:

=WENN(LÄNGE(A13)<=$B$12;WIEDERHOLEN("0";$B$12-LÄNGE(A13))&A13;WIEDERHOLEN("*";$B $12))

Wenn man dann noch die Witzbolde überlisten will, die die Formel mit
negativer Stellenanzahl austricksen wollen, sähe sie so aus:

=WENN(LÄNGE(A13)<=ABS($B$12);WIEDERHOLEN("0";ABS($B$12)-LÄNGE(A13))&A13;WIEDERHO LEN("*";ABS($B$12)))

Schönen Gruß,
Christoph Sternberg"

Zum SVERWEIS-Üben stellt Herr Sternberg zum Download ==> [Klausurauswertung.zip, ca. 3 KB] ein Beispiel zur automatisierten Klausurauswertung zur Verfügung. Besten Dank!

ERGÄNZUNG 15.06.2008:

Im Juni 2008 erreichte mich ein weiterer guter Tipp von Uwe Meggers [umeggers /AT/ w e b . de (Spamschutz, bitte Leerzeichen entnehmen)]zu der Vornullen-Problematik. Mit seiner freundlichen Genehmigung zitiere ich im Folgenden seinen wichtigen Hinweis:

"...ich finde die Lösungsvorschläge zum Vornullen-Problem trotz Mitarbeit
engagierter Besucher immer noch furchtbar kompliziert... wie wärs denn
mit folgender Variante:

A1 enthalte die Zahl (125)
B1 = TEXT(A1;"000000")

liefert "000125" in B1.

Falls man nicht ausdrücklich Text braucht, genügt es sogar, die Spalte
mit den Zahlen benutzerdefiniert als "000000" zu formatieren."

Ja, das ist so machbar. Zu Beachten ist lediglich, dass die Zellen in der Voreinstellung nicht als Text-Format eingestellt sind, dies sollte man dann nachträglich noch tun, da sonst entschwinden die Vornullen bei Aktivierung der Zellen (mittels F2-Taste) wieder - und es kann zu Problemen beim Abspeichern im CSV-Format kommen.

Sollten bzgl. des Vornullen-Beispiels Rückfragen bestehen oder möchten jemand weitere Anregungen und Tipps liefern, so steht Ihnen der Autor von excelfunktionen.de gerne zur Verfügung.