Tipps & Tricks 26.04.2018, 06:00 Uhr

Excel: Adressabfrage in separatem Tabellenblatt

Sie möchten in Ihrer Excel-Adressliste ein separates Abfrage-Blatt einbauen, auf dem Sie bloss den Namen nachschlagen und die Resultate anschauen können.
Nehmen wir zum Beispiel eine Kundenveranstaltung, bei der sich die Kunden beim Eingang akkreditieren müssen. Es ist etwas ermüdend, sich durch eine lange Liste zu wühlen, um zu prüfen, ob die Person, die vor Ihnen steht, wirklich ein eingeladener Kunde ist. Ausserdem möchten Sie während der Veranstaltung nicht ständig die ganze Kundendatenbank auf dem Bildschirm angezeigt haben, auch ein wenig aus Datenschutzgründen. Die Liste selbst soll in einem separaten Blatt stehen. Im Abfrageblatt haben Sie ausschliesslich das Eingabefeld für die Namenssuche – und darunter die Resultate.
Das ist die Adressliste, zu der in einem separaten Tabellenblatt eine Abfrage erstellt werden soll
Erstellen Sie ein separates Blatt, z.B. namens «Abfrage». Das Blatt, das die Adressen enthält, heisst «Adressliste». Auf dem Abfrage-Blatt wollen Sie in der Zelle C1 den Namen eintippen. Darunter (von A4 bis z.B. maximal G22) sollen automatisch alle Datensätze von Kunden mit diesem Namen erscheinen.
Tippen Sie in den Zellen A4 bis G4 die Spaltentitel ein, z.B. ID (Kd-Nr.), Vorname, Name, Geburtsdatum, Strasse, PLZ und Ort. Schätzen Sie ab, wie viele Namen bei einer Abfrage wohl höchstens gefunden werden können. Wir gehen in unserer ca. 1000 Einträge zählenden Beispieladressliste (die Zufallsdaten enthält) jetzt mal von maximal 18 Leuten mit gleichem Namen aus. Der Primärschlüssel steht in der Adressliste und in der Abfrage in Spalte 1, das ist die Kundennummer. Im Prinzip würde in A5 folgende Matrixformel [1] passen: 
{=INDEX(Adressliste!A:A;KKLEINSTE(WENN(Adressliste!D$2:D$1001=$C$1;ZEILE($2:$1001));ZEILE(A1)))}
[1] Matrixformel: Sie dürfen bei diesen die geschweiften Klammern {} am Anfang und am Schluss nicht selbst eintippen. Stattdessen geben Sie die Formel ein und drücken anstelle von Enter die Tastenkombination Ctrl+Shift+Enter (Strg+Umschalt+Eingabe). Excel erkennt dann, dass es eine Matrixformel ist und fügt die geschweiften Klammern selbst hinzu.
Weil in diesem Fall aber in den Zellen, für die es kein Resultat gibt, hässliche Fehler erscheinen, fangen wir es mit einer Fehlerprüfung im Stile von «=WENN(ISTFEHLER(UnsereFormel);"";UnsereFormel)» ab (siehe auch den Artikel vom letzten Dienstag):
{=WENN(ISTFEHLER(INDEX(Adressliste!A:A;KKLEINSTE(WENN(Adressliste!D$2:D$1001=$C$1;ZEILE($2:$1001));ZEILE(A1))));"";INDEX(Adressliste!A:A;KKLEINSTE(WENN(Adressliste!D$2:D$1001=$C$1;ZEILE($2:$1001));ZEILE(A1))))}
Kopieren oder tippen Sie die Formel also ohne die geschweiften Klammern ins Formelfeld, drücken Sie Ctrl+Shift+Enter
Die Formel trägt den Primärschlüssel (die Kd-Nr.) ein
In den Zellen B5 bis G5 verwenden Sie folgende Formeln, die aufgrund des Primärschlüssels die restlichen Daten aus den Kundenadressen nachtragen. Auch diese enthalten eine Fehlerabfrage. Die «;3» bis «;8» sind die Spaltennummer. Wählen Sie andere, falls Sie im Suchresultat die Inhalte aus anderen Spalten anzeigen wollen.
=WENN(ISTFEHLER(SVERWEIS($A5;Adressliste!$A$2:$I$1001;3));"";SVERWEIS($A5;Adressliste!$A$2:$I$1001;3))
=WENN(ISTFEHLER(SVERWEIS($A5;Adressliste!$A$2:$I$1001;4));"";SVERWEIS($A5;Adressliste!$A$2:$I$1001;4))
=WENN(ISTFEHLER(SVERWEIS($A5;Adressliste!$A$2:$I$1001;5));"";SVERWEIS($A5;Adressliste!$A$2:$I$1001;5))
=WENN(ISTFEHLER(SVERWEIS($A5;Adressliste!$A$2:$I$1001;6));"";SVERWEIS($A5;Adressliste!$A$2:$I$1001;6))
=WENN(ISTFEHLER(SVERWEIS($A5;Adressliste!$A$2:$I$1001;7));"";SVERWEIS($A5;Adressliste!$A$2:$I$1001;7))
=WENN(ISTFEHLER(SVERWEIS($A5;Adressliste!$A$2:$I$1001;8));"";SVERWEIS($A5;Adressliste!$A$2:$I$1001;8))
Markieren Sie nun alle Formeln von A5 bis G5 und ziehen Sie diese bis z.B. in die Zeile 22 herunter. Färben Sie das Suchfeld nach Belieben ein und gestalten Sie Ihr Abfrageformular fertig.
Eine Datei mit Beispieldaten und allen Formeln finden Sie hier gleich nachfolgend in der Download-Box. Entzippen Sie die Datei nach dem Download.
Downloads
Download 20180419NameInListeSuchen.zip


Kommentare

Avatar
Gaby Salvisberg
26.04.2018
Hallo mountain Danke für Dein Interesse! Habe die Beispieldatei grad mal schnell in eine Downloadbox geknallt. Ist am unteren Ende des Artikels. Herzliche Grüsse Gaby

Avatar
kubra
30.04.2018
Guten Tag Frau Salvisberg Meine Adressliste enthällt über 1110 Adressen. Ihre Abfrage funktioniert leider bei mir nicht. Es handelt sich um Excel 365. Habe meine Daten in Ihr Tabellenblatt kopiert aber das funktioniert ebenfalls nicht. Meine Frage. Haben Sie im Hintergrund Formeln hinterlegt die nicht sichtlich sind. Danke für Ihre Nachricht. Wünsche Ihnen einen schönen Tag und freundliche Grüsse Kurt

Avatar
Gaby Salvisberg
30.04.2018
Hallo Kurt Da sind keine "unsichtbaren" Formeln. Bei jenen, die in geschweiften Klammern {} stehen, ist es sehr wichtig, dass man die Formeln zunächst ohne die geschweiften Klammern eintippt – und anstelle von Enter die Tastenkombination Ctrl+Shift+Enter drückt. Dann macht Excel daraus die erforderliche Matrixformel – und kennzeichnet dies mit den besagten geschweiften Klammern. Sonst wüsste ich auch nicht, wo der Fehler zu suchen wäre. Herzliche Grüsse Gaby

Avatar
testus
29.05.2018
Antwort an kubra In den Formeln mit SVERWEIS ist die Suche eingegrenzt (z.B. ... D1:D1000) - bei Bedarf diese Bereiche in den Formeln anpassen oder einfacher - im bestehenden Adressblatt (vor der letzten Zeile) Zeilen einfügen nach Bedarf - Excel passt dann die Formeln im Suchblatt selber an. Grüsse