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.

von Gaby Salvisberg 26.04.2018

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.

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 Die Formel trägt den Primärschlüssel (die Kd-Nr.) ein Zoom© pctipp.ch

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.

  • 20180419Name In Liste Suchen

    Freeware für Windows 10, Windows 7.

    Version: ; Sprache: Deutsch; Kategorie: Office
    Neue Version?

    Download von www pctipp.ch

    Neue Version melden

    Wenn Sie von einem Download eine aktuellere Version gefunden haben,
    können Sie uns das hier melden.

    Leserwertung

    100%
    0%
    0%

    Jetzt abstimmen!

    Was halten Sie vom Produkt?


    Kommentare

    • mountain 26.04.2018, 15.46 Uhr

      guter tipp versuchte eine solche Datei aufzubauen. leider zickt irgend was.... wär es möglich mir diese Datei zuzusenden?

    • Gaby Salvisberg 26.04.2018, 16.25 Uhr

      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

    • kubra 30.04.2018, 10.11 Uhr

      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

    weitere Kommentare

    Sie müssen eingeloggt sein, um Kommentare zu verfassen.