Die Excel Funktion INDIREKT() genial & einfach erklärt
Die folgende Abbildung zeigt im rechten Fenster bereits das Ziel: Aus den immer gleich aufgebauten Tabellenblättern AL, FS, GA, KW und BZ mit den Mitarbeiterdaten im linken Fenster wird auf dem Blatt Zusammen eine Kalenderwoche eingegeben. In der Mitarbeiterliste wird dazu die passende Zeile der gewünschten Kalenderwoche aus den zugehörigen Mitarbeiterblättern angezeigt.
Wenn du weitere nützliche Excel Funktionen kennenlernen möchtest, empfehle ich dir unsere Excel Kompakt Schulung!
Excel Funktion INDIREKT ersetzt copy & paste
Diese Aufgabenstellung wurde bisher mühselig durch Kopieren und Einfügen manuell zusammengetragen. In Zukunft reicht nun einfach die Angabe der gewünschten Kalenderwoche in der Zelle B3.
Die Funktion INDIREKT() holt den Inhalt einer Zelle und zeigt ihn in der aktuellen Zelle an. Es gibt in Excel mehrere Funktionen, die den Inhalt einer Zelle anzeigen, aber keine ist so leistungsstark. Bei allen anderen Funktionen muss die Zelladresse in der Form Spaltenbuchstabe und Zeilennummer z. B. A3 vorliegen. Bei der Funktion INDIREKT() setzt du die Zelladresse selbst zusammen.
So holst du dir eine bestimmte Zeile aus mehreren Tabellenblättern, um die Daten zu summieren
Dieses Beispiel verwendet die beiden Funktionen INDIREKT() und VERGLEICH(). Damit dieses Beispiel auch bei dir klappt, lies unbedingt auch den Bonusteil:
- In diesem Beispiel gibt es sechs Tabellenblätter. Auf den ersten 5 sind jeweils die Daten eines Mitarbeiters erfasst. Der Tabellenblattname besteht aus den Initialen des Mitarbeiters.
Wichtig: In den Tabellenblattnamen befinden sind in diesem Beispiel keine Leer- und keine Sonderzeichen. Wenn sich in deinem Tabellenblattnamen Leer- und/oder Sonderzeichen befinden, lies dir unbedingt den letzten Abschnitt durch.
- Auf dem Tabellenblatt Zusammen stehen die Initialen der Mitarbeiter in der ersten Spalte. Die Einträge sind genauso geschrieben wie die Tabellenblattnamen, auf denen sich die jeweiligen Daten befinden. Die Kalenderwoche, nach der ausgewertet werden soll, steht in der Zelle B3.
- Klicke in die Zelle B6 und gib die folgende Formel ein:
=INDIREKT($A6&“!B“&VERGLEICH($B$3;AL!$A$1:$A$60;0))
- Drücke [↵] und kopiere diese Formel nach unten.
- Kopiere anschließend die Formel nach rechts und ändere in der Formel den Spaltenbuchstaben um von B in C.
=INDIREKT($A6&“!C„&VERGLEICH($B$3;AL!$A$1:$A$60;0))
- Kopiere diese Formel nach unten und führe den letzten Schritt auch für die Spalte D durch.
- Nun kannst Du die Summen in der Zeile 12 bilden.
Hinweis: Weiter unten erfährst du, wie du auch die Angabe der Spaltenangabe verformeln kannst.
Die Funktion VERGLEICH()
Die Funktion VERGLEICH() findet die Nummer der Zeile, in der die gesuchte Kalenderwoche steht.
VERGLEICH($B$3;AL!$A$1:$A$60;0)
In der Zelle B3 steht die gesuchte Kalenderwoche. Da alle Blätter gleich aufgebaut sind, kann die Suche auf einem der Tabellenblätter z. B. auf dem Ersten in der ersten Spalte von A1 bis A60 (oder noch weiter nach unten) stattfinden. Der letzte Parameter 0 prüft auf Gleichheit.
In diesem Beispiel stehen die Informationen zur KW 4 in der Zeile 9.
Die Funktion INDIREKT()
Die Funktion INDIREKT() holt den Inhalt einer Zelle durch Angabe des Tabellennamens und der Zelladresse und zeigt diese an:
=INDIREKT(A6&“!B“&9)
Info: In diesem Beispiel wird das Ergebnis 9, das die Funktion VERGLEICH() ermittelt hat, als letzter Wert noch manuell eingetippt.
In A6 steht die Initiale. Diese ist auch der Tabellenblattname.
=INDIREKT(A6
Daran wird mit dem &-Zeichen das !-Zeichen und der Spaltentitel B angehängt. Da beide Werte Texte sind, werden sie in Anführungszeichen gesetzt.
=INDIREKT(A6&„!B„
Und daran wird wieder mit dem &-Zeichen das Ergebnis der Funktion VERGLEICH – hier der ermittelte Zahlenwert – angehängt.
=INDIREKT(A6&“!B“&9)
Da du die Formel nach unten und nach rechts kopieren möchtest, muss der Spaltenbuchstabe A festgesetzt werden:
=INDIREKT($A6&“!B“&9)
Beide Funktionen zusammengesetzt ergibt dann die vollständige Formel:
=INDIREKT($A6&“!B“&VERGLEICH($B$3;AL!$A$1:$A$60;0))
Also holt diese Formel den Inhalt der Zelle B9 vom Tabellenblatt AL.
=INDIREKT(AL!B9)
Die Funktionen mit automatischer Ermittlung des Spaltenbuchstabens
Wenn dir das manuelle Ändern des Spaltenbuchstabens zu mühsam ist, dann erweitere deine Funktion. Dazu musst du Folgendes wissen:
- Die Funktion SPALTE() ermittelt die Nummer der Spalte, in der die Funktion geschrieben wird.
- Du kannst die Adressierung einer Zelle auch über die Schreibweise R1C1 (Row1 Column1) durchführen. So ist z. B. die Zelle B9 auch R9C2. Zeilennummer 9 und Spaltennummer 2 (B).
In der Funktion INDIREKT() kannst du auch mit dieser Adressierungsart arbeiten. Allerdings musst du die Werte „Z“ und „S“ einfügen und als letzten Parameter den Wert FALSCH erfassen.
Außerdem musst du zuerst die Zeilennummer und dann die Spalte benennen:
=INDIREKT($A6&“!“&“Z“&VERGLEICH($B$3;AL!$A$1:$A$13;0)&“S“&SPALTE();FALSCH)
Auch diese Formel ruft den Inhalt der Zelle B9 (Zeile 9, Spalte 2) vom Tabellenblatt AL ab.
=INDIREKT(„AL!Z9S2“;FALSCH)
Sonderzeichen und/oder Leerzeichen im Tabellenblattnamen
Damit der Tabellenblattname korrekt erkannt wird, muss er in ‚Hochkommata‘ geschrieben werden. Das Zeichen findest du auf deiner Tastatur beim #-Zeichen.
=INDIREKT(„‚“&$A6&“‚!“&“Z“&VERGLEICH($B$3;AL!$A$1:$A$13;0)&“S“&SPALTE();FALSCH)
Kontakt
„*“ zeigt erforderliche Felder an
powered by Borlabs Cookie