Beitrag aus SmartTools Excel Weekly
Wertesuche in einer Matrix und Rückgabe von Zeilen- und Spaltenreferenzen
Excel 365 2024 2021 2019 2016 2013
FRAGE In unserem Kfz-Betrieb nutzen wir Regale zum Einlagern von Reifensätzen. Jedes Regal besitzt vier Fächer (Etagen). Den Regalen haben wir Buchstaben von A bis G zugewiesen und die Fächer sind jeweils von 1 bis 4 durchnummeriert. Analog dazu haben wir eine Excel-Tabelle aufgebaut mit Spalten für die einzelnen Regale und Zeilen für die Fächer. An den entsprechenden Schnittpunkten tragen wir jeweils das Kennzeichen des Fahrzeugs ein, zu dem der eingelagerte Reifensatz gehört. Nun möchten wir die Suche vereinfachen. Als Kriterium möchten wir das Kennzeichen eingeben und Excel soll anschließend den Regalbuchstaben und die Fachnummer ausgeben. Ist das machbar?
T. Anker
ANTWORT Mit einer Kombination aus INDEX- und SUMMENPRODUKT-Funktionen lässt sich die Suche realisieren. Dazu ein konkretes Beispiel:
Die im Anschluss vorgestellten Formeln gehen davon aus, dass Sie die Regalbuchstaben A bis G in der dritten Tabellenzeile, und zwar im Bereich B3:H3 eingegeben haben. Die Fächernummern 1 bis 4 stehen in der ersten Spalte, und zwar im Bereich A4:A7. Daraus ergibt sich, dass Sie die Kennzeichen für die eingelagerten Reifensätze im Bereich B4:H7 eingeben müssen.
Beachten Sie bei diesem Tabellenaufbau, dass die Spaltenbuchstaben der Excel-Tabelle nicht mit den Regalbuchstaben übereinstimmen. Gleiches gilt für die Zeilennummern und die Fachnummern. Unsere Formeln liefern die von Ihnen eingegebenen Regalbuchstaben und Fachnummern.
Das Suchkriterium - also das in der Regalmatrix zu suchende Kennzeichen - geben Sie in unserem Beispiel in Zelle B12 ein.
Anschließend lassen wir in Zelle D12 den passenden Regalbuchstaben berechnen und in Zelle E12 die zugehörige Fachnummer.
Dafür setzen Sie folgende Formel in D12 ein:
=WENN(UND(B12<>"";SUMMENPRODUKT(--(B4:H7=B12))<>0);
INDEX(A3:H3;SUMMENPRODUKT((B4:H7=B12)*SPALTE(B:H)));"")
... und folgende Formel in E12:
=WENN(UND(B12<>"";SUMMENPRODUKT(--(B4:H7=B12))<>0);
INDEX(A1:A73;SUMMENPRODUKT((B4:H7=B12)*ZEILE(4:7)));"")
Die eigentliche Suche der Spalten- oder Zeilenreferenz findet im Formelteil mit der INDEX-Funktion statt. Die umgebende WENN-Funktion mit den beiden per UND verbundenen Kriterien dient nur dazu, Fehler auszuschließen, falls noch kein Suchkriterium eingegeben wurde oder falls das gesuchte Kennzeichen gar nicht vorhanden ist.
Wenn Sie die Formeln an Ihre eigenen Tabellenmodelle anpassen wollen, ist folgendes zu beachten:
Die Funktion SUMMENPRODUKT sucht als erstes in der gesamten Matrix nach dem Suchkriterium - hier per "B4:H7=B12". Passen Sie die Zelladressen so an, dass sie mit folgender Syntax übereinstimmen:
<zu durchsuchender Bereich>=<Suchkriterium>
Die Argumente der Funktionen ZEILE bzw. SPALTE entsprechen den Zeilennummern bzw. Spaltenbuchstaben in der Excel-Tabelle, über die sich der zu durchsuchende Bereich erstreckt. Im Beispiel reicht er von Zeile 4 bis Zeile 7 und von Spalte B bis Spalte H.
Die INDEX-Funktionen verwenden als erstes Argument aber immer einen Bereich, der fest von Spalte A bis zur letzten Spalte der zu durchsuchenden Matrix bzw. fest von Zeile 1 bis zur letzten Zeile der zu durchsuchenden Matrix reicht. Der Bereich ist also nicht vollständig deckungsgleich mit den Spalten- oder Zeilenbeschriftungen.
Nun brauchen Sie nur noch das gesuchte Kennzeichen eingeben. Die Formeln verraten Ihnen sofort, wo sich der eingelagerte Reifensatz befindet.