Was genau macht eigentlich INDIREKT?

Verena_7dd250 · 12. November 2019 um 00:07

Liebe Wissende,

ich finde die Excel-Hilfe im Allgemeinen sehr hilfreich, aber in diesem Fall nützt sie mir nix. Ich begreife einfach nicht, was INDIREKT bewirkt. Gestolpert bin ich darüber in meinem Excel-Buch (von den Machern der Seite www.excelformeln.de) bei foltender Formel - es geht da um Verzinsung:
=ZEILE(INDIREKT(Start_Periode&":"&End_Periode))

In der Hilfe verweist INDIREKT auf einen Wert z. B. in A3, in der nur B2 steht. Da kann ich doch gleich B2 angeben?! Ich glaub, ich bin zu blöd, um das zu kapieren. Wenn in A3 eine Formel stehen würde, die sich beispielsweise auf einen veränderlichen Wert in B2 bezieht, wäre es mir ja noch halbwegs verständlich, obwohl mir auch da nicht ganz klar ist, warum dann nicht gleich statt INDIREKT die Formel eingetragen wird …

Kann mir das mal jemand für Begriffsstutzige erklären, bitte? Ich würde mich sehr freuen.

Danke im Voraus,
Verena

Thomas_Ramel · 12. November 2019 um 00:07

Grüezi Verena

Ich begreife einfach nicht, was INDIREKT bewirkt.
Gestolpert bin ich darüber in meinem
Excel-Buch (von den Machern der Seite www.excelformeln.de) bei
folgender Formel - es geht da um Verzinsung:
=ZEILE(INDIREKT(Start_Periode&":"&End_Periode))

Kurz gesagt wertet INDUREKT() Bezüge die als Text vorliegen in funktionierende Bezüge um.

Im obigen Beispiel wird ein Wert aus der benannten Zelle ‚Start_Periode‘ und ein weiterer aus ‚End_Periode‘ geholt (das könnten auch gleich Zellbezüge sein). In diesen Zellen steht dann z.B: A2 und A10, dann wird der Bereich A2:A10 ausgewertet.

Wie die Strings zusammengebaut werden usf. ist der Funktion egal, das kann auch auf ein anders Tabellenblatt oder eine andere Mappe (sofern diese geöffnet ist) gehen.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Verena_7dd250 · 12. November 2019 um 00:08

Danke sehr!
Danke, Thomas!

Ab und zu benutze ich ja auch einfach mal was, ohne es zu begreifen - aber merken kann man sich den Kram doch besser, wenn man weiß, was man tut … *g*

So recht weiß ich zwar noch nicht, wofür das nun gut sein soll, aber wenn ich mal auf etwas stoße, wo ich’s gebrauchen könnte, fällt mir hoffentlich wieder ein, dass da doch mal was war.

Jedenfalls herzlichen Dank.

Gruß,
Verena

Thomas_Ramel · 12. November 2019 um 00:08

Grüezi Verena

So recht weiß ich zwar noch nicht, wofür das nun gut sein
soll, aber wenn ich mal auf etwas stoße, wo ich’s gebrauchen
könnte, fällt mir hoffentlich wieder ein, dass da doch mal was
war.

Machen wir ein kleines Beispiel…

Angenommen Du hast eine Mappe mit den folgenden Tabellenblättern:

- Übersicht
- Meier
- Müller
- Huber
- Hotz

und willst im Blatt ‚Übersicht‘ untereinander die Werte aus Zelle A1 eines jeden anderen Tabellenblattes aufgelistet haben.

Du schreibst also in A1:A4 untereinander folgendes:

Meier
Müller
Huber
Hotz

In B1:B4 schreibst Du dann die Bezugs-Formel:

=Meier!A1
=Müller!A1
=Huber!A1
=Hotz!A1

Da das nur 4 Tabellenblätter und je eine Zelle ist, geht das sehr fix.

Nun stelle dir aber vor, Du möchtest das von 100 Blättern machen - da wirst Du selig, bis Du die Bezüge alle abgetippt hast.
Hier kommt nun INDIREKT() ins Spiel - in B1 schreibst Du nun nämlich:

=INDIREKT(A1&"!A1")

und kopierst das Ganze dann einfach nach unten - in jeder neuen Zeile wird dann der Wert des Tabellenblattes ausgelesen dessen Name in Spalte A steht.

Du siehst nun wie INDIREKT() einen Bezug zusammensetz und auswertet, den Du so von Hand nicht hinbekommst.

Mit freundlichen Grüssen

Thomas Ramel

MVP für MS-Excel -

Verena_7dd250 · 12. November 2019 um 00:08

Hallo Thomas,
boah, da eröffnen sich ja ganz neue Möglichkeiten …

Angenommen Du hast eine Mappe mit den folgenden
Tabellenblättern:

Übersicht

Meier

Müller

Huber

Hotz

und willst im Blatt ‚Übersicht‘ untereinander die Werte aus
Zelle A1 eines jeden anderen Tabellenblattes aufgelistet
haben.

Du schreibst also in A1:A4 untereinander folgendes:

Meier
Müller
Huber
Hotz

Hier kommt nun INDIREKT() ins Spiel - in B1 schreibst Du nun
nämlich:

=INDIREKT(A1&"!A1")

Thomas Ramel

MVP für MS-Excel -

Kombiniert mit der in meinem Excel-Buch so beliebten Zeile wäre das dann ja geradezu idiotensicher:
=INDIREKT(ZEILE(A1)&"!A1") … glaub ich?
Super, denn Bezübe auf Tabellenblätter hab ich bisher außer „zu Fuß“ noch nie hinbekommen!
Klasse, danke!

LG, Verena

Reinhard_fb8cb7 · 12. November 2019 um 00:08

Kombiniert mit der in meinem Excel-Buch so beliebten Zeile
wäre das dann ja geradezu idiotensicher:
=INDIREKT(ZEILE(A1)&"!A1") … glaub ich?
Super, denn Bezübe auf Tabellenblätter hab ich bisher außer
„zu Fuß“ noch nie hinbekommen!

Hallo Verena,

warum haste denn dann nie hier gefragt (
Man kann mit Excel4-Formeln auch Bezüge auf Blätter herstellen ohne deren Namen zu kennen oder auflisten zu müssen.
Oder aufs Blatt davor, danach, …

Gruß
Reinhard

Verena_7dd250 · 12. November 2019 um 00:09

warum haste denn dann nie hier gefragt (
Man kann mit Excel4-Formeln auch Bezüge auf Blätter herstellen
ohne deren Namen zu kennen oder auflisten zu müssen.
Oder aufs Blatt davor, danach, …
Gruß
Reinhard

Hallo Reinhard,
war ja nur ein Beispiel, was mir gerade einfiel. Ich arbeite vorzugsweise mit Excel 97/2003 - auf einem Rechner habe ich auch Excel 2007, komme aber da mit den Menüs nicht wirklich klar … zum Glück funktionieren die meisten Shortcuts!
Aber sinteressieren würde mich das schon, wie man auf etwas Bezug nimmt, wovon man den Namen nicht kennt - bzw. auf das nächste Blatt z. B. Ich weiß nur soviel, dass man durch Markieren aller Tabellenblätter beispielsweise Bezug auf Zelle A1 in allen Blättern nehmen kann. Wenn ich woanders drauf Bezug nehmen will, mache ich das meist über Mausklick, da ich auch die korrekte Syntax nicht kenne.
Viele Grüße,
Verena

Reinhard_fb8cb7 · 12. November 2019 um 00:10

Man kann mit Excel4-Formeln auch Bezüge auf Blätter herstellen
ohne deren Namen zu kennen oder auflisten zu müssen.
Oder aufs Blatt davor, danach, …

Aber sinteressieren würde mich das schon, wie man auf etwas
Bezug nimmt, wovon man den Namen nicht kennt - bzw. auf das
nächste Blatt

Hallo Verena,

gehe mal auf diese Seite:

http://www.xlam.ch/xlimits/downloads.htm

suche dort mit Strg+f nach
Xlmacr8ge.zip
und lade dir diese .zip herunter. Nacht Entpacken starte die
Xlmacr8ge.hlp

Dort findest du die (alten) Excel4 Funktionen. Sie funktionieren in neueren Excelversionen über Namen.
D.h. du mußt einen Namen vergeben für so eine Excel4-Funktion. Diesen namen kannst du dann in Zellen benutzen.

Schau dir mal
Zelle.Zuordnen
Arbeitsmappe.Zuordnen
Datei.Zuordnen
an.

Angenommen du willst eine Liste aller Blätter der aktuellen Mappe.

Dann vergibst du z.B. den Namen „Blaetter“ für
=Arbeitsmappe.Zuordnen(1)

Dadurch stehen in „Blatter“ alle Blattnamen als Matrix.
Auf diese Matrix kannst du im Tabellenblatt mit INdex() zugreifen/auslesen.

Also in A1 schreiben:

=Index(Blatter,Zeile())
und es erscheint
[Mappenname]Tabellenblattname

Du willst ja nur den Blattnamen also muß [Mappenname] entfernt werden.

Den Mappennamen erhälst du mit veregebenem namen, z.B. MappName für
=Arbeitsmappe.Zuordnen(16)

Jetzt schreibst du in A1
=Wechseln(Index(Blatter,Zeile());"["&MappName&"]";"")
und kopierst das nach unten…

Mit diesen Excel4 Funktionen kannst du mehr macht als dir Excelfunktionen erlauben.

Z.B. per Formel ermitteln ob eine Zeile ausgeblendet ist. Das geht über „Zelle.Zuordnen“, damit kannst du die Zeilenhöhe einer Zelle ermitteln. Und bei ausgeblendeten Zeilen ist die Zeilenhöhe auf 0 gesetzt.

Oder auch durch „Zelle.Zuordnen“ kannst du per Formel die Zellfarbe einer Zelle ermitteln u.v.m.

Achja, zwischendurch die mappe immer speichern wenn sie wichtig ist und keine Testmappe, denn unverhofft kann Excel komplett abstürzen.

In deinem Zauberbuch, zumindest auf excelformeln.de wird davor gewarnt wenn du dort im Unterverzeichnis Excel4 Makrofunktionen bist, z.B. hier:
http://excelformeln.de/formeln.html?welcher=268

Genau das was dort steht ist mir gerade passiert. Beim Versuch von einem Blatt in ein anderes wenige Formeln per Strg+c und Strg+v zu kopieren schmierte mein Excel ab.
Anscheinend passiert das nur beim Kopieren von Blatt zu Blatt, innerhalb eines Blattes kenne ich so Abstürze nicht.

Gruß
Reinhard