SVERWEIS, WENN, VERKETTEN – Adressen in einer Rechnung auslesen [mit Download]

Hinweis: Dieser Artikel setzt die Wenn-Funktion und das Erstellen von Listen voraus, hierauf wird nicht eingegangen.

Wer kennt das nicht, Rechnungen in Excel zu schreiben und über eine Kundennummer die Daten der Rechnungsanschrift mit dem SVerweis auszulesen. Dadurch stehen z.B. Vor- und Nachname oder Postleitzahl und Ort ziemlich weit auseinander, da pro Zelle immer nur ein Teil der Adresse durch den SVerweis eingetragen wird. Bei langen Vornamen, besonders bei Doppelnamen, kommt es vor, dass er nicht mehr in die Zelle passt. Man könnte zwar die Zellgröße anpassen, doch erstens bringt das unseren Rechnungsaufbau durcheinander, zweitens bekommen wir dadurch Probleme mit dem Abstand zwischen der Postleitzahl und dem Ort und drittens muss die Zelle für die nächste Rechnung wieder angepasst werden.

Adresse hinter B Adresse opt. Spalte

Abhilfe schafft hier die Funktion Verketten mit dem SVerweis zusammen. Die Funktion Verketten verbindet 1-255 Zeichenfolgen zu einer Zeichenfolge. In unserem Fall beziehe ich mich nur auf die Beispieltabelle (Download).

Zuerst werden in der Zelle E10 und in den Zellen A17:A48 Listeneinträge erstellt, die sich auf die jeweiligen Einträge in den dazugehörigen Tabellenblättern beziehen.

Verketten Funktion

Aus wie vielen Zeichenfolgen besteht nun unser Name? Richtig, aus fünf Zeichenfolgen.

1. Titel =Text 1

2. Leerzeichen =Text 2

3. Vorname =Text 3

4. Leerzeichen =Text 4

5. Nachname =Text 5

Also lautet unsere Formel im Groben erst einmal: =Verketten(SVerweis;“ “;(SVerweis;“ “;(SVerweis)))

Bevor überhaupt mit der Eingabe der Funktion für die Adressdaten begonnen wird, legen wir in der Zelle E10 eine Liste mit den vorhandenen Kundennummern aus dem Tabellenblatt Adressen an. Diese Liste wird später unser Suchkriterium für den SVerweis der Adresse.

Wir fangen in A6 mit der Anschrift an. In dieser Zelle steht die Anrede (Herrn oder Frau). Die Anrede ist im Tabellenblatt Adressen nicht vorhanden, wohl aber das Geschlecht. In der Spalte J werden nun die Anreden über die Wenn-Funktion eingefügt.

Jetzt kann über den SVerweis in A6 die Anrede eingefügt werden. =SVERWEIS(E10;Adressen!A2:J9;10;FALSCH)

Das Verketten des Namens wird etwas komplizierter:

Wir öffnen die Funktion Verketten und öffnen in Text1 den SVerweis.

Verketten wo SVerweis

Verketten SVerweis Titel

 

Hier lassen wir zuerst nach dem Titel suchen (siehe oberes Bild). Als Suchkriterium ist die 2 als Kundennummer bereits in E10 eingegeben.

In der Bearbeitenleiste schalten wir um in die Funktion Verketten, um das Leerzeichen zwischen Titel und Vorname einzugeben.

Dazu klicken wir zwischen die grüne und schwarze Klammer und setzen dort ein Semikolon. Die Verketten-Funktion öffnet sich wieder und wir tragen in der Zeile Text2 ein Leerzeichen ein. Die Anführungsstriche setzt Excel beim Klick in die Zeile von Text3 automatisch. Jetzt steht hinter der grüner Klammer in der Bearbeitungsleiste: ;“ “). Es kann vorkommen, dass Excel eigenständig Semikolons und Klammern in der Bearbeitungsleiste setzt. Dann die falschen Semikolons bitte entfernen und auch die überflüssigen Klammern entfernen. Die letzte Klammer zu ist immer eine Schwarze.

Verketten erstes Leerzeichen

In Text3 wird wieder wie oben beschrieben der SVerweis eingefügt. Dazu reicht es diesmal aus, direkt auf das Wort SVerweis im Namenfeld zu klicken.

Verketten SVerweis Vorname

Wir suchen diesmal in der Spalte 3 nach dem Vornamen und bekommen als Formelergebnis schon mal Dr. Elvira angezeigt. Ein Klick in die Bearbeitungsleiste und das Semikolon hinter die grüne Klammer zusetzen bringt uns wieder in die Verketten-Funktion.

Verketten zweites Leerzeichen

In die Zeile von Text4 schreiben wir wieder ein Leerzeichen, klicken in die Zeile von Text5 und fügen dort den letzten SVerweis ein. Als Ergebnis bekommen wir jetzt den Text Dr. Elvira Neumann angezeigt. Der Name ist fertig, also das Fenster mit OK schließen.

Verketten SVereis Nachname

Titel und Name fertig

Die Straße mit Hausnummer wird wieder mit dem ganz normalen SVerweis ausgelesen.

=SVERWEIS(E10;Adressen!A2:J9;4;FALSCH)

Postleitzahl und Ort werden wieder verkettet(SVerweis;“ “;(SVerweis))

Die Formel besteht diesmal nur aus 3 Zeichenfolgen und ist identisch der Namen-Funktion, nur das wir diesmal bei der zweiten SVerweis-Funktion aufhören.

Profi Tipp

Entfernen des lästigen #NV!-Fehlers, wenn bei Kundennummer oder Artikelnummer nichts eingetragen ist.

NV Fehler Adresse

Das lässt sich recht einfach durch eine vorangesetzte Wenn-Funktion lösen. Wir sagen den drei Formeln in der Adresse, dass das Verketten erst erfolgen soll, wenn die Kundennummer oder die Artikelnummer eingetragen ist. E10>0 bedeutet Kundennummer vorhanden.

In A6, A7, A8 und A9 nacheinander vor der Verketten-Funktion die Wenn-Funktion in der Bearbeitungsleiste eintragen.

Beispiel für A6: =WENN(E10>0;SVERWEIS(E10;Adressen!A2:J9;10;FALSCH);““)

Beispiel für A7: =WENN(E10>0;VERKETTEN(SVERWEIS(E10;Adressen!A2:J9;9;FALSCH);“ „;SVERWEIS(E10;Adressen!A2:J9;3;FALSCH);“ „;SVERWEIS(E10;Adressen!A2:J9;2;FALSCH));““)

blauer Text: Funktion SVerweis bzw. Verketten

grüner Text: nachträglich eingearbeitete Wenn-Funktion

Wir sind mit dem Erstellen des Adressfeldes fertig und beginnen nun mit

Teil 2: Be Formel ierung des Artikelbereiches

Falls für die Artikelnummern noch keine Listeneinträge angefertigt wurden, diese jetzt anfertigen.

Die Bezeichnung und der Einzelpreis werden über den Sverweis ausgelesen und bis in die Zeile 48 vorbereitet. Die Menge ist sinnvoll frei einzutragen und der Gesamtpreis wird berechnet. Netto. MwSt und Brutto werden ebenfalls berechnet.

Zu beachten ist, dass nicht alle Kunden die gleiche Anzahl von Artikeln kaufen werden. Sind Artikelnummern nicht vorhanden, wird es wieder den #NV!-Fehler geben. Er zieht sich durch die gesamte Einkaufsliste, selbst Netto, MwSt und Brutto lassen sich jetzt nicht mehr berechnen.

Demzufolge wird bei den Bezeichnungen die Wenn-Funktion vorangesetzt. Der Sonst-Wert ist hier ebenfalls ““. Der Einzelpreis darf bei der Wenn-Funktion die ““ nicht bekommen. Sie müssen durch die Null ersetzt werden, damit Excel weiter rechnen kann.

Artikel mit #NV!

Nach der voran gesetzten Wenn-Funktion und der Null als Sonst-Wert werden alle Zeilen, in denen noch keine Artikelnummer eingetragen ist, auf Null gesetzt. Diese Null-Einträge dürfen auf der Rechnung natürlich nicht erscheinen.

leere Artikel 0,00

Für die letzten beiden Sachen, das Entfernen der Nullen bzw. das Rechnen mit dem Text MwSt 19%, werde ich einen Spoiler anlegen. So hat jeder die Möglichkeit, sich erst einmal selbst zu testen und nur bei Schwierigkeiten auf die Hilfe zurück zu greifen.

Spoiler: zum Anzeigen Zeilen unterhalb markieren.

0,00 entfernen

Alle Einträge bei Einzelpreis, Gesamtpreis, Netto, MwSt 19% und Brutto markieren und unter dem Reiter Start bei bedingter Formatierung den Eintrag „Regeln zum Hervorheben von Zellen“ „Gleich…“ auswählen. Hier die 0 eintragen und im Feld „hellrote Füllung 2“ benutzerdefiniert die Schriftfarbe auf Weiß stellen.

 Rechnen mit MwSt 19%

Zelle D51 auswählen, rechte Maustaste und Zellen formatieren. Das wird Benutzerdefiniert gemacht. Die 0% rechts raussuchen und in der Zeile Typ MwSt mit einem Leerzeichen in Abführungsstrichen davor schreiben.

Hier geht’s zum Download

5 Comments

  • Guten Tag,

    sie Schreiben in Ihrem Artikel, dass man an Stelle der Anführungszeichen [„“] lieber mit einer „0“ arbeiten soll. Kann ich zwar nachvollziehen, allerdings nutze ich in meinen Schulungen seit Jahren die Anführungszeichen. Habe auch noch nie Probleme bekommen.

    Allerdings ist hier die Nutzung der „0“ eine Möglichkeit in Schulungen auf Regeln hinzuweisen.

    Beste Grüße
    Michael Haase

    • Andreas

      Hallo Michael,

      für die Adressdaten ist es richtig, da können die „“ stehen bleiben. Sobald aber mit dem Ergebnis weiter gerechnet wird, kann man die „“ nicht verwenden, da Excel sie als Text erkennt. Demzufolge machen wir aus den „“ eine Null und schon rechnet Excel weiter.

      Am besten ist das bei den Artikeln zu überprüfen. Wird bei den Einzelpreisen mit „“ gearbeitet, verschwindet der NV-Fehler, allerdings setzt Excel nun bei den Gesamtpreisen den #Wert!-Fehler. Deshalb ist es zu empfehlen, an Stelle der „“ die Null zu nutzen.

      =WENN(A21>0;SVERWEIS(A21;Artikel!$A$2:$C$19;3;FALSCH);““) beim Einzelpreis, wird zu #Wert! beim Gesamtpreis. Dieser #Wert!-Fehler zieht sich dann durch bis zum Brutto, auch wenn danach die Formeln richtig sind.

      Ich empfehle deshalb immer, sollte der #NV!-Fehler auftreten, bei der vorangesetzten Wenn-Funktion mit der Null als Sonst-Wert zu arbeiten, um den #Wert!-Fehler zu vermeiden. Dadurch wird gleichzeitig die bedingte Formatierung geübt, denn die ausgenullten Zeilen müssen ja auch verschwinden.

      beste Grüße Foulcrow

  • Hallo und guten Tag,

    eine sehr schöne Anleitung. Ich habe alles nachvollziehen können und es geschafft, alles umzusetzen. Leider fällt mir auf, das in der zweiten Zeile der Anrede, wenn kein Titel vorhanden ist, der Name+Vorname dann mit einem Leerzeichen am Anfang versehen ist. Sprich, der Name steht nicht unter der Anrede sondern beginnt ein Zeichen weiter hinten. Was mir ebenfalls aufgefallen ist, sind die Postleitzahlen, welche mit einer 0 beginnen. Diese 0 wird weggelassen bei der Adresse. Ich hab schon verschiedene Formatierungen versucht aber kam zu keinem Erfolg. Vielleicht kann man mir hier weiterhelfen.

    Mit freundlichen Grüßen

    Michael P.

    • Hallo Michael,

      für PLZ nimmst Du immer das benutzerdefinierte Zahlenformat:

      00000

      Gruß

      Tim

  • Andreas

    Hallo Michael,
    formatiere die Spalte mit den Postleitzahlen als Text. Der einzige Nachteil ist, dass du die PLZ mit der führenden Null neu eingeben musst.

    beste Grüße Andreas

Frage oder Kommentar zu diesem Artikel?