Die Excel INDEX-Funktion ist ein mächtiges Werkzeug, mit dem Sie Werte basierend auf ihrer Position in einem definierten Zellbereich auslesen können. Stellen Sie sich das wie das Spiel “Schiffe versenken” vor: Sie geben Koordinaten (Zeile und Spalte) an und erhalten den Wert, der sich an dieser Stelle befindet. Innerhalb eines ausgewählten Bereichs wie A1:E5 ermöglicht die INDEX-Funktion beispielsweise, den Wert in Zelle Zeile 4/Spalte 4 oder Zeile 2/Spalte 5 zu ermitteln. Um Ihre Produktivität in Excel weiter zu steigern, finden Sie hier eine umfassende excel formel hilfe.
Geben Sie zunächst die gewünschten Suchparameter ein, zum Beispiel den Wert 2 für die Zeile und 5 für die Spalte.
Für die Eingabe der INDEX-Formel wählen Sie zuerst den Bereich aus, in dem Sie suchen möchten. Anschließend wählen Sie für die gewünschte Zeile und Spalte als Suchparameter die Zellen (z.B. B8 und B9) aus, in die Sie zuvor die Zahlen bzw. Suchparameter eingegeben haben.
In unserem Beispiel erhalten Sie als Ergebnis den Wert 20, da dieser Wert in der Zelle steht, wo sich Zeile 2 und Spalte 5 kreuzen.
Wenn Sie die Suchparameter für Zeile und Spalte ändern, werden Ihnen entsprechend andere Werte angezeigt. Wenn Sie als Parameter für die Zeile oder die Spalte den Wert 1 eingeben, wird Ihnen die entsprechende Beschriftung angezeigt, da sich die Zeilen- und Spaltentitel innerhalb des von Ihnen ausgewählten Bereichs befinden.
Die INDEX-Funktion lässt sich für vielfältige Aufgaben effektiv einsetzen und wird insbesondere in Kombination mit anderen Excel-Funktionen zu einem äußerst leistungsfähigen Werkzeug.
INDEX & VERGLEICH: Dynamische Datenabfrage in Excel
Die Kombination aus INDEX und VERGLEICH ist in Excel besonders leistungsstark, da sie die Nachteile der reinen INDEX-Funktion und der SVERWEIS-Funktion umgeht. Mithilfe der INDEX-Funktion ermitteln Sie einen Wert in Abhängigkeit von seiner Position innerhalb eines zuvor ausgewählten Bereichs. Das bedeutet, Sie definieren eine Position durch Angabe der gewünschten Zeile und Spalte, um den Wert zu erhalten, der sich im Schnittpunkt dieser beiden Parameter befindet.
In der Praxis ist diese Vorgehensweise oft nicht optimal, da Sie in vielen Fällen die genaue Position (Zeile, Spalte oder beides) des gesuchten Werts innerhalb eines Bereichs nicht kennen. Betrachten wir ein Beispiel mit einer kleinen Tabelle, die drei Zeilen mit Werten und vier Spalten mit Produktkriterien enthält. Für einen umfassenden Überblick über alle möglichen Formeln, die Ihnen in Excel zur Verfügung stehen, besuchen Sie alle formeln excel.
Müssten Sie aus einer Tabelle mit mehreren Dutzend, Hunderten oder sogar Tausenden von Zeilen bzw. Spalten einen ganz bestimmten Wert heraussuchen, wäre es ineffizient, immer wieder sämtliche Spalten- und/oder Zeilenköpfe manuell durchzugehen, um die Position des gesuchten Werts zu finden.
Nehmen wir an, wir suchen in der Tabelle den Wert, der für Produkt A in der Spalte mit dem Produktkriterium „grün“ eingetragen ist.
Würde man nur mit der INDEX-Funktion arbeiten, müsste man zuerst alle Zeilen und Spalten zählen, um herauszufinden, in welcher Zeile sich Produkt A und in welcher Spalte sich das Produktkriterium „grün“ befindet. Dieses manuelle Zählen wird durch die VERGLEICH-Funktion eliminiert.
Für die Erstellung der Formel geben Sie zunächst den Bereich (z.B. A1:D4) ein, in dem sich der gesuchte Wert befindet. Im nächsten Schritt müssten Sie eigentlich die betreffende Zeilen- bzw. Spaltennummer eingeben. Stattdessen verwenden wir die Produktangabe A und das Produktkriterium „grün“, um Excel selbst vergleichen bzw. abzählen zu lassen, in welcher Zeile bzw. Spalte sich der jeweilige Suchparameter befindet.
Dazu erweitern Sie die Formel mit der VERGLEICH-Funktion. In der jeweiligen Klammer geben Sie zuerst die Zelle mit dem gesuchten Parameter ein (z.B. „A“ = B6 | „grün“ = B8), danach wählen Sie den dazugehörigen Suchbereich aus (alle Zeilenköpfe A1:A4 bzw. alle Spaltenköpfe A1:D1), innerhalb dessen Excel die gesuchten Werte vergleichen soll. Am Ende der Klammer geben Sie den Wert 0 ein, um eine genaue Übereinstimmung zu fordern.
Als Ergebnis erhalten Sie den Wert 20, da dieser sich in der Zelle an der Schnittstelle von Zeile 2 (Produkt A) und Spalte D (Produktkriterium grün) befindet.
Die Kombination von INDEX und VERGLEICH ersetzt in vielen Fällen die SVERWEIS-Funktion und bietet dabei mehr Flexibilität, insbesondere wenn der Suchwert nicht in der ersten Spalte der Tabelle steht.
Die INDIREKT-Funktion verstehen
Die INDIREKT-Funktion von Excel ist eine anspruchsvollere Funktion, deren Verständnis möglicherweise etwas Übung erfordert, da die Bezeichnung „INDIREKT“ anfangs verwirrend sein kann. Eine mögliche Erklärung, die das Verständnis erleichtert, ist die Vorstellung einer „Relais-Station“.
Üblicherweise werden zwischen zwei Zellen oder einem Zellbereich direkte Bezüge hergestellt. Mit der INDIREKT-Funktion nehmen Sie ebenfalls Bezug auf eine bestimmte Zelle, doch hier fungiert eine dritte Zelle quasi als Relais-Station. Man kann sich dies wie eine Fernsehübertragung zwischen zwei Kontinenten vorstellen, die über einen TV-Satelliten erfolgt.
Stellen Sie sich vor, Zelle A1 (mit Wert 1000) ist ein Übertragungswagen in Washington D.C., der über eine Demonstration berichtet. Zelle C5 ist der Fernsehapparat in einem Wohnzimmer in Europa. Aufgrund der Erdkrümmung ist keine direkte Funkverbindung von A1 nach C5 möglich. Wir benötigen also eine Relais-Station in Form eines TV-Satelliten (Zelle C1), der in einer geostationären Umlaufbahn um die Erde kreist und sowohl zu A1 als auch zu C5 eine ständige direkte Verbindung besitzt. Für die wichtigsten Excel-Formeln, die Ihnen den Arbeitsalltag erleichtern, klicken Sie auf excel wichtige formeln.
Der TV-Satellit ist auf die Frequenz der Zelle A1 eingestellt, daher schreiben wir in die Zelle C1 den Wert A1. Von Zelle C5 aus nehmen wir nun auf dem Umweg über Zelle C1 einen INDIREKTEN Bezug auf Zelle A1.
Hier wird kein direkter Bezug zwischen C5 und A1 hergestellt, sondern ein INDIREKTER „Bezug aus einem Text(feld)“. Als Ergebnis wird uns dann der Text/Wert, der in Zelle A1 steht, ausgegeben. Anstatt also in Zelle C5 den direkten Bezug zur Zelle A1 als Formel („=A1“) einzugeben, holen wir uns diesen Text/Wert über eine andere Zelle (in unserem Fall über C1). Der Text bzw. der Wert, den sich Zelle C1 aus Zelle A1 geholt hat, fließt somit über die Formel =INDIREKT(C1) von A1 über C1 in Zelle C5 ein.
Abwandlung der INDIREKT-Funktion: ZS-Schreibweise
Für Bezüge können Sie nicht nur die Standard-Schreibweise (z.B. A1), sondern auch die „ZS“- bzw. „Zeilen-Spalten“-Schreibweise (Z1S1 für 1. Zeile, 1. Spalte) einsetzen. Der Eintrag „Z1S1“ in Zelle B11 entspricht in unserem Fall somit der Zelle „A1“. Damit die INDIREKT-Formel diese Schreibweise auch als Bezug interpretieren kann, muss die Formel noch durch den Zusatz FALSCH ergänzt werden.
INDIREKT & INDEX & VERGLEICH: Die ultimative Kombination für dynamische Abfragen
Die Kombination dieser drei Excel-Funktionen eröffnet ungeahnte Möglichkeiten für dynamische Datenabfragen. Die INDEX-Funktion gibt uns einen Wert zurück, abhängig von der Position innerhalb einer Tabelle. Die VERGLEICH-Funktion findet für uns die gesuchte Position (und diese Position übergeben wir dann an unsere INDEX-Funktion). Mithilfe der INDIREKT-Funktion können wir den Bereich, in dem wir suchen, flexibel verändern. Das bedeutet, wir sind nicht auf eine einzige Tabelle beschränkt, sondern können dieselbe Formel auf verschiedene Tabellen und Bereiche anwenden.
Betrachten wir zwei Tabellen, aus denen wir Daten ziehen möchten. Auf der rechten Seite wurden in den Zellen H3, H4 und H5 bereits Dropdown-Listen mit Auswahlmöglichkeiten für die Kriterien „Extras“, „Produkt“ und „Farbe“ vorbereitet. Eine bestimmte Kombination dieser Kriterien soll uns dann die entsprechenden Daten ausgeben. Für alle, die tiefer in die Welt der Excel-Formeln eintauchen möchten, sind die wichtigsten excel formeln ein Muss.
Durch Klicken auf eine der drei Zellen H3, H4 oder H5 erscheint rechts ein kleines Kästchen, mit dessen Hilfe sich das dazugehörige Dropdown-Menü öffnen lässt; auf diese Weise kann man die jeweils gewünschte Kriterien-Kombination auswählen.
Wir demonstrieren den Einsatz der INDEX-Funktion allein sowie in Kombination mit der VERGLEICH-Funktion für die Kriterien-Kombination „Ohne Extras – B – grün“.
Schritt 1) INDEX-Funktion allein
Die einfache INDEX-Funktion erfordert die manuelle Angabe der Zeilen- und Spaltennummer:
=INDEX(A3:D6;3;4)
Schritt 2) Kombination INDEX- & VERGLEICH-Funktion
Durch Hinzunahme der VERGLEICH-Funktion werden Zeile und Spalte dynamisch ermittelt:
=INDEX(A3:D6;VERGLEICH(H4;A3:A6;0);VERGLEICH(H5;A3:D3;0))
In beiden Fällen ist das Ergebnis 35.
Schritt 3) Kombination INDEX- & VERGLEICH- & INDIREKT-Funktion
Durch die Hinzunahme der INDIREKT-Funktion ermöglicht es uns Excel, nicht nur im Bereich der oberen Tabelle „Ohne Extras“ (A3:D6) zu suchen, sondern unsere Suche auch auf die untere Tabelle „Mit Extras“ (A9:D12) auszuweiten. Dies ist besonders nützlich für die excel buchhaltung formeln, wo Sie oft mit dynamischen Datenbereichen arbeiten.
Wir ersetzen zu diesem Zweck den bisher fest definierten Suchbereich (A3:D6) am Beginn der Formel durch die INDIREKT-Funktion und wählen dabei die Zelle I3 aus, die den besagten Suchbereich (A3:D6) beschreibt:
=INDEX(INDIREKT(I3);VERGLEICH(H4;A3:A6;0);VERGLEICH(H5;A3:D3;0))
Diese Erweiterung der Formel ermöglicht nun, dass der Bereich, in dem gesucht wird, über eine Dropdown-Box dynamisch verändert werden kann. Dadurch sind Sie in der Lage, hochflexible und leistungsstarke Berichte und Analysen in Excel zu erstellen, die sich an wechselnde Datenstrukturen anpassen.
Fazit
Die INDEX-Funktion, insbesondere in Kombination mit VERGLEICH und INDIREKT, ist ein unverzichtbares Werkzeug für jeden fortgeschrittenen Excel-Anwender. Sie bietet eine Flexibilität und Leistung, die über die Möglichkeiten einfacherer Suchfunktionen hinausgeht. Durch das Verständnis und die Anwendung dieser Funktionen können Sie Ihre Datenanalyse erheblich optimieren, komplexe Abfragen dynamisieren und somit wertvolle Zeit sparen. Egal ob Sie spezifische Werte in großen Datensätzen finden oder dynamische Berichte erstellen möchten – die Kombination dieser Funktionen ist der Schlüssel zu effizientem Arbeiten in Excel. Nehmen Sie sich die Zeit, diese mächtigen Tools zu meistern, und steigern Sie Ihre Produktivität nachhaltig.