Office
27.04.2018, 10:30 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.
(Quelle: pctipp.ch)
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
Quelle: pctipp.ch
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
Quelle: 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
Version: ; Sprache: Deutsch; Kategorie: Office
Download von www pctipp.ch
- » 20180419Name In Liste Suchen (85.57 KB) - 53 Downloads
