Alternative zu Indirekt-Funktion

Bild

Betrifft: Alternative zu Indirekt-Funktion
von: Ari
Geschrieben am: 22.05.2015 16:28:30

Hallo zusammen,
ich nutze momentan die Indirekt-Funktion, um in meinen Formeln einen variablen Bezug zu verschiedenen Tabellenblättern herstellen zu können.
Ich muss also nur in die Zelle D1 den Tabellenblatt-Namen eingeben und alle Formeln beziehen sich nun auf dieses Blatt.
Beispiel:
SVERWEIS($E250;INDIREKT($D$1 & "!$E$1:$AO$451");3;FALSCH)
Mein Hauptproblem ist nun, dass die Blätter sehr viele Formeln enthalten, und dass alle Indirekt-Funktionen nach jeder Änderung in der Datei sich neu berechnen. Die Performance der Datei ist katastrophal :(
Außerdem suche ich an anderer Stelle einzelne Werte aus einem Tabellenblatt,
Beispiel:
INDIREKT($D$1 & "!G1011")
hier ist das Problem, dass in der Zelle links davon INDIREKT($D$1 & "!H1011") ausgegeben werden soll usw...
Leider kann ich die Formel jedoch nicht ohne weiteres nach links "ziehen", da "!G1011" ja als Text nicht angepasst wird.
Kann mir irgendjemand einen Tipp geben? Wäre sehr dankbar!
Viele Grüße,
Ari

Bild

Betrifft: AW: Alternative zu Indirekt-Funktion
von: Ari
Geschrieben am: 22.05.2015 16:33:20
Jetzt hab ich auch noch links und rechts verwechselt :-P, sorry, ich meinte rechts davon..

Bild

Betrifft: zu letzterem
von: WF
Geschrieben am: 22.05.2015 16:45:32
Hi,
schreibe statt =INDIREKT($D$1 & "!G1011")
=INDIREKT($D$1&"!"&ZEICHEN(64+SPALTE(G1))&1011)
funktioniert bis Spalte Z - danach geht auch; wird aber länger.
Salut WF

Bild

Betrifft: AW: Alternative zu Indirekt-Funktion
von: Daniel
Geschrieben am: 22.05.2015 16:40:38
Hi
für den ersten Fall wüsste ich jetzt auch keine alternative.
Indirekt ist volatil, aber leider die einzige möglichkeit um einen Zellbezug zu erstellen, bei dem Das Blatt variabel ist.
ggf kannst du die automatische Neuberechnung abschalten und nur im Bedarfsfall mit F9 neu berechnen lassen.
das zweite lässt sich recht einfach lösen:
verwende in der Indirekt-funktion nicht die A1-Addressierungsmethode, sondern die Z1S1-Schreibweise.
damit lassen sich dann auch relative Zellebezüge beschreiben, die dann mitwandern wenn man die Formel in eine andere Zelle zieht ohne dass man den Formeltext anpassen muss.
die Z1S1-Addresse ist so aufgebaut:
Z steht für Zeile, S steht für Spalte
danach folgt die Zeilen- und Spaltennummer.
Dies ergibt dann einen absoluten Zellbezug.
für relativen Zellbezüge schreibt man nach dem Z oder S den Versatzwert in Klammern hin:
Z(1)S(1) wäre also ein Zellbezug auf die Zelle die schräg rechts unter der Formelzelle liegt.
positive Werte sin dien Versatz nach rechts oder unten, negative Werte ein Versatz nach links oder oben.
bei einem Zellbezug auf die gleiche Zeile oder Spalte (0) kann man die Klammer auch weglassen: ZS1 wäre ein bezug auf die Spalte A in der gleichen Zeile.
damit die Indirekt-Funktion weiss, dass die Adressierung in Z1S1 erfolgt, setzt man den zweiten Parameter = FALSCH:

=Indirekt($D$1&"!Z1011S(1)";falsch)
Gruß Daniel

Bild

Betrifft: AW: Alternative zu Indirekt-Funktion
von: Ari
Geschrieben am: 22.05.2015 17:07:09
Vielen Dank für die Lösung des zweiten Themas. Das klappt!
Extrem schade, dass Excel keine Alternative zu der Indirekt-Funktion bietet...
Kennt irgendjemand einen "Workaround"?

Bild

Betrifft: AW: Alternative zu Indirekt-Funktion
von: Daniel
Geschrieben am: 22.05.2015 17:18:55
Hi
in dem Fall sehr schwierig.
vielleicht sowas, um die Volatilität zu vermeiden.
für das Beispiel sei angenommen, das zwirschen den drei Blättern TabA, TabB und TabC gewechselt werden soll:
dann statt INDIREKT($D$1 & "!G1011")
die Formel Wahl(Vergleich($D$1;{"TabA"."TabB"."TabC"};0);TabA!G1011;TabB!G1001;TabC!G1001)
wäre dann nicht mehr volatil, dafür musst du alle möglichen Zellbezüge in der Formel einzeln auflisten.
Gruß Daniel

Bild

Betrifft: 1 Alternative zu volatilen Fktt wie INDIREKT ...
von: Luc:-?
Geschrieben am: 23.05.2015 05:37:51
…wäre Schreiben und Verwenden einer nicht-volatilen UDF, Ari,
die so argumentiert wdn kann, dass die Volatilität verzichtbar wird. Davon konnte und wollte der Pgmmierer von INDIREKT nicht ausgehen, da die Fkt im Rahmen ihrer Aufgabe möglichst auch einfach zusammengesetzte Bezugstexte zuverlässig verarbeiten sollte. Das hätte aber auch nicht unbedingt Volatilität erfordert, wie sie im Falle von argumentlosen Fktt wie HEUTE() erforderlich ist. Es war also eine Ermessensfrage.
Allerdings wdn VBA-gestützte UDFs anscheinend auch öfter neu berechnet als nicht-volatile Xl-StandardFktt. Ob das aber generell so ist, ist strittig, denn sie wdn deutlich öfter bzw anders berechnet, wenn man sie im Pgm volatil setzt. Ob man das tun sollte, hängt dann aber von ihrer Aufgabe ab.
Morrn, Luc :-?

Besser informiert mit …

Bild

Betrifft: AW: 1 Alternative zu volatilen Fktt wie INDIREKT ...
von: Daniel
Geschrieben am: 23.05.2015 11:23:43
Hi
ob hier ein nicht-volatiles Indirekt sinnvoll ist, hängt davon, ob sich die Werte in dem Zellbereich, der durch das Indirekt beschrieben wird ändern oder diese mehr oder weniger fix sind.
Normalerweise versucht Excel Rechenzeit zu sparen und eine Formel nur dann neu zu berechnen, wenn sich in den Zellen, die von der Formel referenziert werden, eine Änderung ergeben hat.
Das Problem bei Formeln wie Indirekt oder auch Bereich.Verschieben ist, dass hier der eigentliche referenzierte Zellbereich gar nicht direkt abgelesen werden kann, sondern hierzu erstmal diese Funktionen berechnet werden müssen und da berechnet Excel gleich die ganze Formel mit.
bei einer nichtvolatilen Indirekt-Alternative würde dann keine Neuberechnung der Formel erfolgen, wenn in dem Zellbereich, der durch die Indirekt-Funktion erzeugt wird, eine Wertänderung stattfindet.
Wenn man damit leben kann, kann man auch eine nicht-volatile Indirektalternative einsetzen, ansonsten nicht.
Gruß Daniel

Bild

Betrifft: AW: Alternative zu Indirekt-Fkt: schnellere Fkt
von: Daniel
Geschrieben am: 23.05.2015 12:02:14
Hi
die andere Möglichkeit wäre, dass du deine SVerweise obtimierst bzw derern Verwendung reduzierst, so dass die ständige neuberenung kein Problem ist, weil sie sehr schnell geht.
beim SVerweis bieten sich hierzu zwei möglichkeiten an.
a) die Zeilenanzahl der Suchmatrix ist sehr gross
dann sollte man die Suchmatrix nach ihrer ersten Spalte aufsteigend sortieren um mit dem SVerweis mit 4. Parameter = WAHR arbeiten zu können.
Dieser ist insbesondere bei langen Tabellen wesentlich schneller als sein "falscher" Bruder
b) viele Sverweise mit gleichem Suchbegriff und geändertem SpaltenIndex
wenn du viele SVerweise hast, die die gleiche Suchmatrix und den gleichen Suchbegriff verwenden und bei denen nur der Spaltenindex (3. Parameter) sich ändert, dann kannst du das ganze so beschleunigen:
1. richte eine Hilfssspalte ein, in welcher du mit VERGLEICH die Zeilennummer der Fundstelle berechnest.
2. ersetze die SVerweise durch INDEX und verwende die Hilfsspalte mit der Zeilennummer.
Dann muss die zeitaufwendige Suche nicht jedesmal stattfinden, sondern immer nur 1x pro Suchbegriff.
da du eine sehr breite Suchmatrix hast und für den Suchwert die Spalte absolut gesetzt hast, liegt die Vermutung nahe, dass die Option b) dir helfen könnte.
das Formelwerk wäre (Hilfsspalte sein F):
für die Hilfsspalte (F250): =Vergleich($E250;INDIREKT($D$1 & "!$E$1:$E$451");0)
für die SVerweise =Index(INDIREKT($D$1 & "!$E$1:$AO$451");$F205;3)
Gruß Daniel

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Alternative zu Indirekt-Funktion"