Tutorials

Excel-Rechnungsvorlage: Kundendaten automatisch ausfüllen mit XVERWEIS

Melden

Hier ist ein umfassendes Tutorial, wie du eine professionelle Rechnungsvorlage in Excel erstellst, die Kundendaten mithilfe der XVERWEIS-Funktion (englisch: XLOOKUP) automatisch ausfüllt.

Dieses Tutorial zeigt dir, wie du eine Datenbank und eine Rechnungsvorlage so verknüpfst, dass du nur noch eine Kundennummer auswählen musst und alle restlichen Informationen (Name, Adresse, E-Mail) sofort erscheinen.

Schritt 1: Die Kundendatenbank anlegen

Bevor wir die Rechnung automatisieren, benötigen wir eine Datenquelle.

  1. Erstelle ein neues Tabellenblatt und nenne es "Kunden".
  2. Erstelle eine Tabelle mit den folgenden Spaltenköpfen:
    • Spalte A: Kunden-ID
    • Spalte B: Firmenname
    • Spalte C: Ansprechpartner
    • Spalte D: Straße
    • Spalte E: PLZ / Ort
    • Spalte F: E-Mail
  3. Trage einige Testdaten ein.
  4. Wichtig: Markiere den gesamten Bereich (A1 bis F...) und drücke Strg + T. Bestätige "Tabelle hat Überschriften". Nenne die Tabelle oben links im Menü "Tabellenentwurf" einfach Kundenliste.

Schritt 2: Die Rechnungsvorlage gestalten

Wechsle zu einem neuen Tabellenblatt und nenne es "Rechnung".

  1. Gestalte das Layout deiner Rechnung (Logo, Rechnungsnummer, Datum etc.).
  2. Reserviere ein Feld für die Kunden-ID (z. B. Zelle B10). Dies wird dein Steuerungsfeld.
  3. Reserviere Felder für die Kundeninfos (z. B. untereinander: Name, Straße, PLZ/Ort).

Schritt 3: Ein Dropdown-Menü für die Kunden-ID erstellen

Damit du die ID nicht jedes Mal tippen musst, erstellen wir eine Liste:

  1. Klicke auf die Zelle, in die die Kunden-ID eingetragen werden soll (z. B. B10).
  2. Gehe im Menü auf Daten -> Datenüberprüfung.
  3. Wähle unter "Zulassen" den Punkt Liste.
  4. Klicke in das Feld "Quelle" und markiere die Spalte mit den Kunden-IDs in deinem "Kunden"-Tabellenblatt.
  5. Bestätige mit OK. Jetzt kannst du die Kunden per Klick auswählen.

Schritt 4: Die XVERWEIS-Funktion einfügen

Jetzt kommt die Magie. Wir nutzen den XVERWEIS, um die Daten basierend auf der ID zu ziehen.

Die Syntax des XVERWEIS erklärt: =XVERWEIS(Suchkriterium; Suchmatrix; Rückgabematrix; [wenn_nicht_gefunden])

Beispiel 1: Den Firmennamen abrufen

Klicke in die Zelle, in der der Name des Kunden stehen soll, und gib folgende Formel ein:

=XVERWEIS(B10; Kundenliste[Kunden-ID]; Kundenliste[Firmenname]; "Kunde nicht gefunden")
  • B10: Hier steht die ID, nach der gesucht wird.
  • Kundenliste[Kunden-ID]: Hier soll Excel nach der ID suchen.
  • Kundenliste[Firmenname]: Diesen Wert soll Excel zurückgeben.

Beispiel 2: Die Adresse abrufen

Wiederhole den Vorgang für die anderen Felder. Für die Straße würde die Formel so aussehen:

=XVERWEIS(B10; Kundenliste[Kunden-ID]; Kundenliste[Straße]; "")

(Das "" am Ende sorgt dafür, dass die Zelle leer bleibt, wenn keine ID ausgewählt ist, anstatt eine Fehlermeldung anzuzeigen.)

Schritt 5: Mehrere Spalten gleichzeitig abrufen (Profi-Tipp)

Der XVERWEIS kann sogar mehrere Spalten gleichzeitig zurückgeben. Wenn deine Spalten in der Datenbank (PLZ, Ort, E-Mail) nebeneinander liegen, kannst du sie in einem Rutsch füllen:

  1. Markiere die Zellen, in die die Daten fließen sollen.
  2. Gib die Formel ein:
    =XVERWEIS(B10; Kundenliste[Kunden-ID]; Kundenliste[[PLZ / Ort]:[E-Mail]])
  3. Excel "spillt" (verteilt) die Ergebnisse automatisch in die benachbarten Zellen.

Warum XVERWEIS statt SVERWEIS?

  1. Flexibilität: Der XVERWEIS kann nach links und rechts suchen. Der SVERWEIS kann nur nach rechts suchen (die Such-ID muss immer in der ersten Spalte stehen).
  2. Robustheit: Wenn du in deiner Kundentabelle eine neue Spalte einfügst, geht der SVERWEIS oft kaputt. Dem XVERWEIS ist das egal, da er sich auf die Spaltenüberschriften oder feste Bezüge verlässt.
  3. Standardmäßiger Suchmodus: XVERWEIS sucht standardmäßig nach einer genauen Übereinstimmung – du musst nicht mehr ;FALSCH am Ende eingeben.

Bonus: Formatierung sauber halten

Damit die Rechnung professionell aussieht, wenn keine Kunden-ID ausgewählt ist, kannst du die Formel mit einer WENN-Funktion umschließen oder den "Wenn nicht gefunden"-Teil des XVERWEIS nutzen:

=XVERWEIS(B10; Kundenliste[Kunden-ID]; Kundenliste[Firmenname]; "")

So bleibt deine Rechnung sauber und leer, bis du einen Kunden auswählst.

Fertig! Du hast nun eine dynamische Rechnungsvorlage, die Fehler minimiert und Zeit spart.

0