Sammlung ==> #17 Zweidimensionale Suche mit Matrixfunktionen

Zweidimensionale Suche mit Matrixfunktionen:

Gastbeitrag

Man kann mit den Matrixfunktionen Werte nicht nur eindimensional suchen lassen, sondern auch zweidimensional. Auch wenn man mit der Funktion SVERWEIS eine Menge Fälle lösen kann, so greift sie dennoch nicht für alle Beispiele. Angenommen in einer Tabelle befinden sich Entfernungen. Dabei werden die Orte in der obersten Zeile und in der ersten Spalte eingetragen. In der Matrix befinden sich die Entfernungen. Die Tabelle ist symmetisch, da die Entfernung von A nach B so groß ist wie von B nach A.

In einer zweiten Tabelle gibt der Benutzer in einer Spalte einen Ausgangsort ein, in einer zweiten Spalte einen Zielort. Es soll die Entfernung in der ersten Tabelle gesucht werden, damit über die Kilometerangaben eine Fahrtkostenabrechnung durchgeführt wird.

Damit der Anwender eine Dropdownliste in jeder Zelle hat, muss die Liste der Orte von der ersten Tabelle auf die zweite verknüpft werden. Stehen die Orte in der ersten Spalte, dann kann die Zeilenposition der zelle C2 mit der Funtkion

=VERGLEICH(C2;$A$1:$A$51;0)

ermittelt werden. Analog wird die Position der Spalte von D2 mit

=VERGLEICH(D2;$A$1:$A$51;0)

gefunden. Stehen diese Ergebnisse in E2 und F2, dann liefert INDEX mit der Zeil- und Spaltennummer das Ergebnis des Wertes der Zeile und der Spalte:

=INDEX(Entfernungen!$A$1:$AY$51;E2;F2)

Oder man fasst die Funktion in einer Zelle zusammen:

=INDEX(Entfernungen!$A$1:$AY$51;VERGLEICH(C2;$A$1:$A$51;0);
VERGLEICH(D2;$A$1:$A$51;0))

Hat der Benutzer allerdings noch keine Werte eingegeben, dann liefert VERGLEICH einen Fehler (#NV). Dies kann mit ISTFEHLER abgefangen werden:

=WENN(ISTFEHLER(INDEX(Entfernungen!$A$1:$AY$51;VERGLEICH(C2;
$A$1:$A$51;0);VERGLEICH(D2;$A$1:$A$51;0)));"";
INDEX(Entfernungen!$A$1:$AY$51;VERGLEICH(C2;$A$1:$A$51;0);
VERGLEICH(D2;$A$1:$A$51;0)))

Mit diesem Ergebnis kann nun weitergerechnet werden.

Noch ein ähnliches Beispiel: Angenommen in einer Matrix stehen mehrere Kilometerangaben untereinander und mehrere Gewichtsangaben nebeneinander. Ihnen sind jeweils bestimmte Werte (Preise) zugeordnet.

Nun soll in ein zweites Tabellenblatt in einer Spalte ein Wert aus der ersten Spalte stehen (Kilometerangabe); daneben ein Wert aus der Beschriftungszeile (Gewichtsangabe). Der Wert, der in der ersten Matrix gefunden wird, soll in die dritte Zelle daneben geschrieben werden.

In die erste Zelle gibt der Benutzer die km ein. Die Formel

=VERGLEICH(A2;Tabelle1!$B$2:$B$19)

berechnet die Zeile, in der sich dieser Wert befindet. Mit

=VERGLEICH(B2;Tabelle1!$B$2:$Q$2)

wird die Spalte berechnet, in der sich der Wert kg befindet. Nun kann über

=INDEX(Tabelle1!$B$2:$Q$19;C2;D2)

die entsprechende Zelle herausgeholt werden. Excel sucht bei Zwischenwerten den nächstkleineren Wert, also bei 101 oder 199 wird immer 100 verwendet. Wollen Sie aufrunden, dann zählen Sie eins dazu:

=INDEX(Tabelle1!$B$2:$Q$19;C2;D2+1)

Dies kann nun zu einer Formel zusammengefasst werden:

=INDEX(Tabelle1!$B$2:$Q$19;VERGLEICH(A3;Tabelle1!$B$2:$B$19);
VERGLEICH(B3;Tabelle1!$B$2:$Q$2))

Und noch nicht eingegebene Werte sollen keine Fehler melden:

=WENN(ISTFEHLER(INDEX(Tabelle1!$B$2:$Q$19;VERGLEICH(A4;
Tabelle1!$B$2:$B$19);VERGLEICH(B4;Tabelle1!$B$2:$Q$2)));"";
INDEX(Tabelle1!$B$2:$Q$19;VERGLEICH(A4;Tabelle1!$B$2:$B$19);
VERGLEICH(B4;Tabelle1!$B$2:$Q$2)))

Die Formeln stehen alle in Spalte E. Nun können die Werte in Spalte A und B eingetragen werden. Wenn die Formel aufrunden soll (siehe oben), dann so:

=WENN(ISTFEHLER(INDEX(Tabelle1!$B$2:$Q$19;
VERGLEICH(A4;Table1!$B$2:$B$19);VERGLEICH(B4;Tabelle1!$B$2:$Q$2)
+1));"";INDEX(Tabelle1!$B$2:$Q$19;VERGLEICH(A4;Tabelle1!$B$2:$B$19);
VERGLEICH(B4;Tabelle1!$B$2:$Q$2)+1))

Achtung: Die Werte müssen als WERTE, d.h. als Zahlen eingegeben werden. Sonst rechnet Excel nicht!

Sollten Rückfragen zu diesem Gastbeitrag bestehen, stehe ich Ihnen gerne zur Verfügung. Oliver Basel.