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...