143. Excel: Projekt: Suche in Datenbanken mit Formeln SVERWEIS, INDIREKT und ZÄHLEWENNN


Einleitung

Primärziel des letzten Excel-Projektes des Jahres 2013 ist es, die Spalten einer Datenbank nach Begriffen zu durchsuchen, die beliebig oft in einer Tabellenspalte vorkommen.

Sekundär wird die Ergebnisanzeige mehrerer Ergebnisse in einer Reihe mittels eines Drehelementes behandelt.

Dabei wird nur mit einem Minimum an Formeln gearbeitet. Die Tabelle zur Abhandlung können Sie hier frei downloaden. Lern.xlsm (21 KB) beinhaltet ein Makro, was der  Implementierung  eines Auswahl-Listenfeldes geschuldet ist und als Makro interpretiert wird.

Sie können die Sicherheitswarnung "Makros wurden deaktiviert" durch den eingeblendeten Button Optionen: Diesen Inhalt aktivieren ankreuzen und bestätigen gefahrlos aktivieren - ansonsten funktioniert das Listenfeld nicht...

Ansonsten ist kein Makro implementiert. Das Dokument ist quelloffen. Die Exceltabelle ist mit Kommentaren an allen wichtigen Stellen gespickt.

Der Zugriff und die Ergebnisdarstellung sind selbst für Fortgeschrittene eine Herausforderung und es gibt verschiedene Ansätze zur Lösung.

In diesem Projekt werden sechs Datensätze stellvertretend für eine beliebig lange Datenbank verwendet, obwohl ab einigen Tausend Einträgen dieses mit diesem System zeitlich zu langsam wird.

Die zwei Hilfsreihen bei der Datenbank sind so konzipiert, dass die Formelinhalte mit dem kleinen Anfasser unten rechts beliebig weit nach unten kopiert werden können - um es in der Fachsprache zu sagen: Relative und Absolute Bezüge wurden beachtet...

Voraussetzung zum Verständnis sind Grundzüge von SVERWEIS und der Aufbau von Formeln.



Aufbau der Exceltabelle



Abbildung: Arbeitstabelle Excel, mit freundlicher allgemeiner Genehmigung der Microsoft Corporation abgebildet

Die Tabelle ist in drei Bereiche gegliedert:

1. Ergebnisanzeige mit Schaltfläche zum Wechseln zwischen den Suchtreffern

2. Suchmaske: Nicht sehbar in A13 ein Listenauswahlfeld, wo gerade die Suche nach Nachname ausgewählt ist. Dann daneben in B13 das Sucheingabefeld, mit dem nach den Namen gesucht wird, den Sie gerade eingetragen haben.

Daneben ist eine Hilfstabelle zur Erstellung der Spalte, in der gerade gesucht wird. Im o. a. Bild ist die Spalte B, in der die Nachnamen stehen, selektiert. H16 ist dann die Bezugszelle zum letzten Teil...

3. Datenbank von Nachname bis Stadt stehen die Überschriften. Die Demo ist mit sechs Datensätzen ausreichend.  Die Spalten A und I sind Hilfsreihen, die Sie benötigen und die man auch ausblenden könnte....

Rollen wir das ganze Elend von der Mitte her auf:

ad 2)

Also bleiben wir jetzt beim Beispiel mit dem Nachnamen Schulze, den Sie z. B. bei B13 eingetragen haben. Aus dem Listenfeld A13 haben Sie den Nachnamen als Kriterium eingegeben.

----

Einschub Listenfeld

A13 wurde markiert und in der Hilfstabelle sind untereinander vom Nachnamen bis zur Stadt alle Überschriften der Tabelle  aufgeschrieben (G9-G15). 

Diese werden nun in die Liste über Menüregisterkarte Daten - Rubrik Datentools - Datenüberprüfung im Assistenten übernommen: Registerkarte Einstellungen im Dropdonmenü Liste auswählen. Dann bei Quelle Auswahlknopf rechts drücken. Nun markieren Sie die ganze Liste, schließen den verkleinerten Assistenten und bestäigen mit OK.

Einschub ende

---

Ziel der Hilfstabelle ist es, die Spalte mit Nachnamen aus der Demo-Tabelle herauszufinden, damit nach Herrn Schulze nur in dieser Spalte gesucht wird. Also schauen wir uns mal exemplarisch die Zelle neben den Nachnamen in der Hilfstabelle an. In H10 steht also im Beispiel ein B  (B ist die Spalte mit den Nachnamen in der Demotabelle).

Dieses ist das Ergebnis der Formel in H10 =WENN($A$13=G9;"B";0).  Mal übersetzt: Wenn im Listenfeld A13  Nachname steht, dann gib den Buchstaben B aus oder eben eine Null, wenn dort etwas anderes steht. Deshalb steht hier im Beispiel ein B und bei den anderen darunterliegenden Formeln eine 0, weil dort statt dem B der jeweils betreffende ander Spaltenname der Demotabelle in Buchstabenform steht.

Vielleicht mal die Formel darunter: =WENN($A$13=G10;"C";0)

Richtig, der Vorname steht in unserer Demo-Tabelle in der Spalte C. Deshalb muss beim Auswahl des Filters Vornamen aus dem Listenfeld der Buchstabe C ausgewählt werden.

So, jetzt Schauen Sie sich die Formel H16 neben dem Wort Matrix an: =SVERWEIS(A13;G9:H15;2;FALSCH)

Damit holen wir das oben ermittelte B in eine einheitliche Zelle - je nach Spaltenkriterium könnte ja auch ein C,D,E,F,G, oder H ermittelt werden...

Also heißt die Formel: Nehme den aktuellen Begriff aus dem Listenfeld (im Beispiel Nachname in der Spalte B) und Suche im Nebenfeld in der Hilfstabelle den zugehörigen Wert - in diesem Fall B.

Würden wir z. B. ein Geburtsdatum suchen, so müsste in A 13 das Wort Geburtsdatum selektiert werden. Im Suchfeld würde dann z. B. 25.06.1970 stehen. In H11 steht dann =WENN($A$13=G11;"D";0), also wird in der Spalte D gesucht. In H16 steht dann wieder jener Wert D

ad 3)

Kleiner Zwischenstand: Wir wissen also, dass wir das Suchwort Schulze (aus A13) in der Spalte B (H16) suchen müssen.

Dann suchen wir jetzt mal zeitnah die Spalte B nach Schulze ab oder als erste Formel in A19 unter der Überschrift unserer kleinen Datenbank:

=WENN(INDIREKT($H$16 &I19)=$B$13;ZÄHLENWENN(INDIREKT($H$16 & 19): INDIREKT($H$16 & I19);$B$13);0)

Das ist im Prinzip einfach, wenn Sie beginnend bei der Wenn-Formel den Inhalt in die drei farblich unterschiedlich dargestellten Argumente aufbröseln:

Fangen wir mit der gelben Bedingung der Wennformel (INDIREKT($H$16 &I19)=$B$13) an: wenn B19 ist gleich dem Suchwort (Schulze) B13 steht da eigentlich.

Ja, INDIREKT montiert nur das zuvor ermittelte B in Zelle H16 mit (kaumännische UND getrennt) mit I19 zusammen, wo eine 19 drinsteht. Deshalb wird in der Spalte I eine Hilfsspalte mit der jeweiligen Zeilennummer eröffnet.

Was passiert dann? Dieses sagt uns die zweite Hälfte der Wenn-Formel in orange (ZÄHLENWENN(INDIREKT($H$16 & 19): INDIREKT($H$16 & I19) ;$B$13)

Also dann wird in der Matrix B19 bis B19 die Häufigkeit des Vorkommens des Namen Schulze B13 (;$B$13  ist der Suchbegriff von Zählewenn) gezählt. In der ersten Zeile steht jedoch Mayer und nicht Schulze, also wird dementsprechend dort nicht gezählt, und es wird der dritte Teil der Wenn-Formel in rosa aktiv. Ja, es wird nur eine Null angezeigt.

Gehen wir doch mal ein paar Zeilen tiefer in Zelle A24

Hier steht: =WENN(INDIREKT($H$16 & I24)=$B$13;ZÄHLENWENN(INDIREKT($H$16 & 19): INDIREKT($H$16 & I24);$B$13);0)

Richtig, beim Herunterziehen der Formel mit dem kleinen Anfasser unten rechts werden die relativen Bezüge ohne Dollarzeichen automatisch erhöht.  Es wird also in der Zelle B24  verglichen, ob B24 gleich dem Suchfeld  B13  (Schulze) steht.

Das ist in diesem Beispiel der Fall und die Zählewenn-Formel wird nun aktiv,  deren zwei Teile  jetzt nochmal abgehandelt werden. $H$16 & 19 entspricht in diesem Fall B19 also der Anfang der Matrix bis ($H$16 & I24), was  B24 in diesem Beispiel entspricht. Gesucht werden alle Schulze ($B$13) in diesem eben angegebenen Raum (also B19:B24) und das Ergebnis wird in die Zelle geschrieben.

Hinweis: In der Formel heißt es: ZÄHLENWENN(INDIREKT($H$16 & 19): H16 ist bekannter maßen der Buchstabe B in unserem Beispiel, aber warum steht da eine 19 nach dem &?
Richtig, die Neunzehn ist eine natürliche Zahl, die immer der erste Punkt der Matrix von ZÄHLEWENN ist. Die könnte auch in Anführungzeichen stehen "19". Beim Herunterziehen der Formel würde Excel niemals auf den Gedanken kommen daraus eine 20 zu machen. Ist also nichts weiter als ein absoluter Bezug auf Zahlenebene und eine Alternative zur Verwendung einer festen Zelle mit Dollarzeichen, in der eine 19 steht: Wenn Sie jetzt das Verstanden haben, dann wissen Sie auch, wie Sie die Zelle für Ihr Projekt vielleicht anpassen müssen.
Das Konzept von relativen und absoluten Bezügen füht leider oft zu Fehlern...


Es gibt drei Schulze inklusive dem Schulze in B24, die gezählt und in dieser Zelle nun angezeigt werden - richtig als ganze natürliche Zahl 3.

Wir haben also drei unterschiedliche Datensätze mit dem Namen Schulze, die mit den Kennzahlen 1,2 und 3 versehen sind.

Damit sind alle Vorbereitungen getroffen, um mit einer einfachen Matrixformel die Ergebnisdatensätze anzuzeigen...

ad 1)

Nach den Vorbereitungen ist die Anzeige des Ergebnises eher trivial:

In A3 könnte man auch ohne Schalter eine beliebige Zahl innerhalb des Trefferraums in der Zelle B3 (Formel: =A27, dortige Formel =MAX(A19:A24), also Ermittlung der höchsten Trefferzahl im Beispiel des Nachnamen Schulze in der Spalte B) eintragen.

Ab C3 Formel: =SVERWEIS(A3;A19:B24;2;FALSCH) werden dann nur noch zu den Zahlen die einzelnen Rubriken in der jeweiligen Zeilenspalte zugeordnet.

A3 ist bei 1 =A20 im Beispiel Schulze. In der Spalte daneben steht Schulze, also die zweite Spalte und es wird eine genaue Übereinstimmung mit falsch erwartet...

Wechselt die Zahl in A3 auf 2 oder 3, so wird dann die Zeilenspalte der neuen Fundstelle angezeigt.

Um das ein wenig professioneller zu gestalten, wurde eine Schalterfunktion aus den Entwicklertools eingebunden.

Entwicklertools - Steuerelemente - Einfügen -Drehfeld. Irgendwo in Excel mit Mauszeiger platzieren - Entwurfsmodus in Steuerelementen aktivieren - rechte Maustaste auf Schaltfläche - Steuerfläche formatieren - Registerkarte Steuerung - Minimal- und Maximalwert auf 1 und 10 ? setzen - Zellverknüpfung $A$3 durch markieren oder einfach eintragen und fertig ist die Schaltfläche...

Dasselbe Spiel in D3 zur Ermittlung des Vornamens von Schulze =SVERWEIS(A3;A19:C24;3;FALSCH)
Hier wird die dritte Spalte bei der Fundstelle angezeigt, also Spalte 3 entspricht dem Vornamen Anton...

Doch was ist, wenn kein Suchtreffer vorhanden ist?

Dann wird normalerweise #nv angezeigt, was Sie in der bedingten Formatierung für die Zellen B3:I3 ändern können, nachdem Sie markiert wurden: Start - bedingte Formatierung - neue Regel - Nur Zellen Formatieren, die enthalten - Regelbeschreibung bearbeiten: nur Zellen formatieren mit: Fehler aus Dropdownfeld - Button formatieren - Registerkarte Schrift - Farbe von automatisch auf weiß aus Dropdownfeld und OK...


Fazit

Mit ZÄHLEWENN, SVERWEIS, und INDIREKT kann man beliebig mit Suchmatrixen jonglieren und auf beliebige Bestandteile von Datensätzen zugreifen.

Suchen Sie sich für ihre Lösung Ansätze heraus. Es wurde in diesem Projekt bewußt leichte Formeln verwendet - machen Sie daraus das Beste...

Impressum
Datenschutz