Verwaltung von Kundendaten mit Excel
Aufbau einer Adressdatenbank mit Excel
Ich beginne den Aufbau meiner Datenbank mit Eingabe von Spaltenüberschriften: "Firma1", "Firma2" (für die zweite Zeile des Firmennamens), "Anrede", "Vorname", "Name", "Straße, Hausnummer", "PLZ, Ort", "Land", "Kundensitz" und "Umsatzsteuer-Id".
Da ich später diese Daten nach Kundentyp (Agentur, Direkt- oder Privatkunde) sortieren und filtern möchte, füge ich in die Spalte B noch eine Überschrift "Kundentyp" ein. In der Spalte A ("Kunde") werden später Kundenbezeichnungen in Kurzform eingefügt.
Um Informationen zu Kundentyp, Anrede und Kundensitz nicht jedes Mal neu eintippen zu müssen, werde ich drei kleine Auswahllisten erstellen.
Auswahlliste (Drop-Down-Liste) erstellen
Außerhalb des sichtbaren Bereiches, z. B. in den Zellen AA1, AA2 und AA3 trage ich folgende Werte ein: Agentur, Direktkunde, Privatkunde
Ich ziehe den Inhalt der Zelle nach unten (mit dem Mauszeiger auf die untere rechte Ecke der Zelle zeigen, der Mauszeiger verwandelt sich in ein Pluszeichen, die linke Maustaste gedrückt halten und nach unten bis Ende der Liste ziehen).
Auf die gleiche Weise erstelle ich Auswahllisten in der Spalte "Anrede" mit Angaben "Frau" und "Herr" sowie in der Spalte "Kundensitz" mit Angaben "DE", "EU" und "Drittland". Diese Angaben brauche ich für die automatische Erfassung von Umsätzen für die Umsatzsteuervoranmeldung.
Diese Datenbank möchte ich für meine Rechnungsvorlage und beim Drucken von Adressetiketten nutzen. Zu diesem Zweck vergebe ich den Spalten M und N Überschriften "Rechnungsadresse" und "Anschrift". Wenn man Kunden mit "Sehr geehrter Herr" bzw. "Sehr geehrte Frau" in der Rechnung anreden möchte, kann man noch eine Spalte anlegen und sie z. B. "Briefanrede" nennen.
In diese Spalten werde ich später Formeln eintragen, um Adressen und Briefanreden automatisch erstellen zu lassen. Um diese Formeln übersichtlich zu gestalten, möchte ich zuerst Namen der Zellen definieren und gehe wie folgt vor:
Zellennamen definieren
Ich markiere die Spalte C und überschreibe den Namen "C1" im Namenfeld mit "Firma1". Die Eingabe beende ich mit der Enter-Taste. Nun haben alle Zellen dieser Spalte neben ihren Namen C1, C2 usw. noch den Namen "Firma1".
Das gleiche mache ich mit den Spalten "Firma2", "Anrede", "Vorname", "Name", "Straße, Hausnummer", "PLZ, Ort" und "Umsatzsteuer-Id". Achtung: Die Namen dürfen keine Leerzeichen oder Sonderzeichen enthalten. Aus diesem Grund nenne ich Spalten "Straße, Hausnummer" und "PLZ, Ort" einfach "Straße" und "Ort" und die Spalte "Umsatzsteuer-Id" nenne ich "Umsatzsteuernummer".
Adressen mit der Funktion "Verketten" erstellen
Die Funktion "Verketten" dient zum Verbinden von Zellinhalten zu einer Textzeichenfolge. Man kann bis zu 255 Elemente mit insgesamt bis zu 8.192 Zeichen verknüpfen. Daten eines Privatkunden kann man z. B. mit folgender Formel verbinden:
=VERKETTEN(Anrede;" ";Vorname;" ";Name;ZEICHEN(10);Straße;ZEICHEN(10);ZEICHEN(10);Ort)
Das zweite Argument in dieser Formel (" ") ist ein Leerzeichen. Leerzeichen, Text, Satzzeichen usw. müssen in Anführungszeichen gesetzt werden. "ZEICHEN(10)" steht für Zeilenumbruch bzw. für eine Leerzeile. Ein Semikolon (;) trennt Argumente der Formel voneinander.
Wichtig: In Excel 2016 wurde Funktion „Verketten“ durch die Funktion „Textkette“ ersetzt. Obwohl die Funktion „Verketten“ aus Gründen der Abwärtskompatibilität weiterhin verfügbar ist, sollte man die Verwendung von der Funktion „Textkette“ in Betracht ziehen. Der Grund: Möglicherweise steht die Funktion „Verketten“ in künftigen Excel-Versionen nicht mehr zur Verfügung.
Ich habe in dieser Liste gewerbliche und private Kunden erfasst und muss für diese Kunden verschiedene Zellen miteinander verknüpfen. Aus diesem Grund werde ich die Funktion "Verketten" mit der Funktion "Wenn" verbinden.
Verbinden von Funktionen "Wenn" und "Verketten"
In der Zelle M2 trage ich folgende Formel ein: =WENN(Firma1="";VERKETTEN(Anrede;" ";Vorname;" ";Name;ZEICHEN(10);
Straße;ZEICHEN(10);ZEICHEN(10);Ort);
VERKETTEN(Firma1;ZEICHEN(10);Firma2;ZEICHEN(10);
Umsatzsteuernummer;ZEICHEN(10);
Straße;ZEICHEN(10);ZEICHEN(10);Ort;))
Das heißt:
Wenn die Zelle "Firma1" leer ist ("" ohne Leerzeichen dazwischen), verknüpfe die Argumente folgendermaßen: Anrede, Leerzeichen, Vorname, Leerzeichen, Name, Zeilenumbruch, Straße, zwei Zeilenumbrüche, Ort.
Ansonsten verknüpfe die Argumente wie folgt: Firma1, Zeilenumbruch, Firma2, Zeilenumbruch, Umsatzsteuernummer, Zeilenumbruch, Straße, zwei Zeilenumbrüche, Ort.
Die Formeleingabe wird mit der Enter-Taste beendet.
Wenn das Ergebnis ohne Zeilenumbrüche dargestellt wird, markieren Sie die entsprechende Spalte, auf der Registerkarte "Start" klicken Sie auf "Zeilenumbruch" (Gruppe "Ausrichtung"). Klicken Sie auf "Format" (Gruppe "Zellen") und auf "Zeilenhöhe automatisch anpassen".
Ziehen Sie die Formel nach unten.
Briefanrede automatisch erstellen
In die Zelle O2 trage ich folgende Formel ein:
=WENN(Anrede="Herr";"Sehr geehrter Herr"&" "&Name&",";WENN(Anrede="Frau";"Sehr geehrte Frau"&" "&Name&",";"Sehr geehrte Damen und Herren,"))
Hier verwende ich anstelle von "VERKETTEN" den Operator "&", um Elemente miteinander zu verbinden.
Die Funktion heißt also:
Wenn in der Zelle "Anrede" der Wert "Herr" steht, gib "Sehr geehrter Herr" UND ein Leerzeichen (" ") UND den Wert aus der Zelle "Name" UND ein Komma (",") zurück.
Wenn in der Zelle "Anrede" der Wert "Frau" steht, gib "Sehr geehrte Frau" UND ein Leerzeichen (" ") UND den Wert aus der Zelle "Name" UND ein Komma (",") zurück.
Ansonsten (also, wenn in der Zelle weder "Herr" noch "Frau" steht) gib "Sehr geehrte Damen und Herren," zurück.
Ziehen Sie die Formel nach unten.
Kundenbezeichnung in Kurzform
In der Spalte A ("Kunde") möchte ich eine Kundenbezeichnung in Kurzform haben, damit ich diese in der Auswahlliste meiner Rechnungsvorlage verwenden kann. Es ist wichtig, dass diese Spalte sich links von allen anderen Spalten befindet, um die SVERWEIS-Funktion verwenden zu können.
Als Kurzform möchte ich bei Firmenkunden die erste Zeile der Firma haben. Bei Privatkunden sollen die Daten als "Anrede Name, Vorname" ausgegeben werden. Die Formel dafür sieht folgendermaßen aus:
=WENN(Kundentyp="Privatkunde";Anrede & " " & Vorname &"," & " " & Name;Firma1)
Kopfzeile fixieren
Auf der Registerkarte "Ansicht" (Gruppe "Fenster") klicke ich auf "Fenster einfrieren" und wähle "Oberste Zeile einfrieren". Das bewirkt, dass die Kopfzeile immer sichtbar bleibt, auch beim Scrollen der Liste.
Daten sortieren
Ich gehe zur Registerkarte "Start" ⇒ Gruppe "Bearbeiten" ⇒ "Sortieren und Filtern" ⇒ "Benutzerdefiniertes Sortieren" und setze ein Häkchen bei "Daten haben Überschriften". Über die Schaltfläche "Ebene hinzufügen" füge ich noch zwei Ebenen hinzu und sortiere die Liste nach drei Kriterien:
1. Firma1
2. Anrede
3. Name
Daten filtern
Ein Filter ermöglicht uns, Daten nach bestimmten Kriterien zu filtern.
Auf der Registerkarte "Daten" klicke ich auf "Filtern" (Gruppe "Sortieren und Filtern").
Das erlaubt mir z. B. Privatkunden aus meiner Stadt oder gewerbliche Kunden ohne Umsatzsteuernummer usw. anzeigen zu lassen.
Spalten gruppieren oder ausblenden
Wenn die Datenbank so breit wird, dass sie nicht mehr auf den Bildschirm passt, kann man Spalten, die nicht gerade benötigt werden, ausblenden: zuerst eine oder mehrere Spalten markieren, die rechte Maustaste drücken und auf "Ausblenden" klicken.
Um die Spalten wieder einzublenden, muss man die angrenzenden Spalten markieren, auf die rechte Maustaste und anschließend auf "Einblenden" klicken.
Der Menüpunkt "Gruppieren" befindet sich auf der Registerkarte "Daten" (Gruppe "Gliederung"). Ich markiere Spalten, die Formeln enthalten (Rechnungsadresse, Anschrift und Briefanrede), und klicke auf "Gruppieren". Jetzt kann ich auf das Minuszeichen klicken und die Spalten "einklappen". Bei Bedarf kann ich sie wieder "ausklappen", indem ich auf das Pluszeichen klicke.
Eingabemaske verwenden
Bei langen Listen kann die Eingabe direkt in die Zellen umständlich sein. Deswegen empfiehlt es sich, Daten über die Datenmaske einzutragen und zu pflegen.
In Excel 2003 konnte man die Maske direkt über den Menüpunkt "Daten" ⇒ "Maske" öffnen. In Excel 2007 und 2010 muss man die Maske auf das Menüband zuerst selbst platzieren.
Datenmaske aufrufen
Ich gehe zu "Datei" ⇒ "Optionen" ⇒ "Menüband anpassen".
Unter "Befehle auswählen" wähle ich "Alle Befehle" aus und finde in der Liste den Befehl "Maske".
Unter "Hauptregisterkarten" klicke ich mit der rechten Maustaste auf Registerkarte "Daten" und wähle "Neue Gruppe hinzufügen". "Neue Gruppe" nenne ich "Eingabemaske". Danach klicke ich auf "Hinzufügen" und anschließend auf "OK".
Nun habe ich auf der Registerkarte "Daten" den Menüpunkt "Maske":
Wenn ich auf eine beliebige Zelle in meiner Datenbank und dann auf "Maske" klicke, erscheint die Eingabemaske.
Ich sehe den ersten Datensatz und die Anzahl der Datensätze. Mit Hilfe der Bildlaufleiste kann ich zum nächsten Datensatz wechseln und Angaben ergänzen oder korrigieren.
Wenn ich auf "Neu" klicke, erscheint eine leere Maske. Ich gebe meine Daten in die Felder ein und klicke auf "Schließen". Der neue Datensatz wird automatisch in die nächste leere Zeile der Liste eingefügt.
Datensätze suchen
Wenn ich auf "Kriterien" klicke, kann ich meine Suchkriterien eingeben. Beim Klicken auf Schaltflächen "Vorherigen suchen" oder "Weitersuchen" wird die Suche durchgeführt.