Auswahlmöglichkeiten bei WAHL

Hallo,
ich habe z.B. das Tabellenblatt "Basis" und viele weitere Tabellenblätter (z.B. 2017, 2016, 2015, 2014 usw.).
In einer Zelle von Basis möchte ich z.B. per SVERWEIS ( könnte auch eine Index-Formel sein) einen Wert herausholen, der sich auf allen weiteren Tabellenblättern an gleicher Stelle befindet. Um welches Tabellenblatt es sich handelt, bestimmt ein Wert in einer anderen Zelle von Basis, in A1 wird aufgrund einer anderen Formel z.B. die Zahl 2 ermittelt.
Die Formel könnte also aussehen:
=sverweis(e1;wahl(a1;2017!c1:f20;2016!c1:f20;2015!c1:f20:2014!c1:c20);2;falsch).
In diesem Beispiel würde ich also den gewünschten Wert aus dem Tabellenblatt 2016! erhalten. Wie kann ich die Auswahlmöglichkeiten bei WAHL besser beschreiben? Hier sind es im Beispiel nur vier Möglichkeiten, doch bei vielen weiteren Tabellen wird die Formel arg lang, zumal es im Laufe der Zeit zu weiteren Tabellenblättern kommt.
Ich möchte nicht mit volatilen Funktion INDIREKT arbeiten, weil ich letztlich viele Sverweis bzw. indexformeln im Tabellenblatt Basis verwende und die vielen indirekt-Formeln ständig zu elenden zeitraubenden Neuberechnungen führen.
Gruß Rudolf

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo, Rudolf & Interessenten;
es dürfte kaum bekannt sein, dass man die Volatilität einer Xl-Fkt auch aushebeln kann, oft für die Dauer der Öffnung der die Fkt enthaltenden Datei (beim Öffnen u/o Schließen wird idR eine völlige Neuberechnung veranlasst). Dann reagiert die Fkt auch nicht auf die 2…3 Neuberechnungsmöglichkeiten per Mausklick bzw Taste [F9]. Dazu muss man die Xl-Fkt nur aus einer nicht-volatilen UDF (in einer ZellFml) heraus aufrufen. Das würde logisch erscheinen, wenn die Fkt INDIREKT hier nicht ggüber anderen volatilen Xl-Fktt wie bspw JETZT auch dabei aus dem Rahmen fallen und sich als besonders volatil erweisen würde, was ggf dem Umstand geschuldet ist, dass ihr üblicherweise Argumente mitgegeben wdn.
Trotzdem ist es möglich, mit einem Trick eine zeitweilige Nicht-Volatilität dieser Fkt zu erreichen, der aber spätestens mit erneutem Öffnen der Datei in seiner Wirkung aufgehoben wird (bei argumentlosen volatilen Xl-Fktt bleibt die Nicht-Volatilität natürlich erhalten!). Dazu müsste die im INDIREKT-Argument genannte Quelle einen ebenfalls mit einer solchen UDF erzeugten ErgebnisWert enthalten. Diese UDF wird vorübergehend (per Argument) auf nicht-volatil gestellt. Die darauf verweisende UDF mit internem INDIREKT wird dann natürlich keine ErgebnisÄnderung mehr zeigen. Wird die Quell-UDF dann wieder auf volatil gestellt, bleibt dieser Effekt bei der 2.UDF erhalten → sie reagiert nicht mehr auf Änderungen des Ergebnisses der 1., wird also nicht erneut berechnet.
Das habe ich unter Xl14/2010 bemerkt und es muss nicht unbedingt auch für andere, vor allem neuere Xl-Versionen gelten. Dazu habe ich folgende ZellFmln benutzt:
Quelle - O22: =FxGuide(8;"RandBetween";1;1;100) [Effekt tritt mit Arg=1|2 kaum mal auf.]
Ziel1: =FxGuide(8;"indirect";;"""O22""")
Ziel2: =FxGuide(9;"indirect";;"""R22C15""";0) [Relative R1C1-AdressAngaben wdn anscheinend nicht unterstützt!]
Hierbei wird mit dem 3.Argument der UDF FxGuide die Volatilität der UDF festgelegt. Ihr 1.Argument legt die Ausführungsmethode der Fkt, deren Name als 2.Argument notiert wurde, fest. Da INDIREKT nicht im WorksheetFunction-„Container“ (Arg1=2) enthalten und somit auch nicht nur per Application (Arg1=1) ausführbar ist, ist nur Evaluierung möglich (Arg1=8|9).*
Es sieht auch ganz so aus, als ob dieser Effekt hptsächlich der FmlText-Evaluierung geschuldet ist (wie auch manch anderer irregulärer Effekt). Als WorksheetFunction lässt sich die Volatilität argumentbehafteter Xl-Fktt anscheinend nur ausnahmsweise und sehr sporadisch unterdrücken, was auf Pgm-Schwächen des FmlText-Interpreters hindeuten könnte.
* Da die UDF (und damit dieser Gedankengang) noch sehr neu ist, konnte ich noch nicht ausreichend testen. Demnächst ist aber eine Publikation der UDF nebst ausführlicherer Beschreibung und ggf weiterer Erkenntnisse geplant.
Zu deinem eigentlichen Problem, Rudolf;
falls du Daniels Vorschlag nicht folgen kannst oder willst (ein Blatt pro Jahr kann durchaus übersichtlicher sein als alles auf nur einem Blatt zu halten), gäbe es noch die Möglichkeit, deine gezeigte Fml dadurch zu verkürzen, dass du die WAHL-Möglichkeiten in benannte Bezüge auslagerst. Wählst du deren Namen recht kurz, bleibt die Fml überschaubar, zB:
=SVERWEIS(E1;WAHL(A1;anno7;anno6;anno5;anno4;anno3;anno2;anno1;anno0);2;0)
für anno7: =2017!C1:F20, anno6: =2016!C1:F20 usw.
Da der DatenAbruf aber offensichtlich in Relation zu A1ff steht, könntest du auch (zusätzlich bzw alternativ) so etwas wie ein INDIREKT für die ganze Fml verwenden. Das wäre in einer benannten Fml mit der alten XLM-Fkt AUSWERTEN möglich (abgesehen mal von UDF-Lösungen wie TxEval mit engl FmlText in einer ZellFml), was aber (ebenso wie bei UDF-Verwendung, außer Letztere steht in einem AddIn allgemein zV) die Speicherung der Datei als .xlsm/b erforderte:
Zusätzlich → JDatenVweis: =AUSWERTEN("sverweis("&E1&";anno"&A1&";2;falsch)")
Alternativ → JDatenVweis: =AUSWERTEN("sverweis("&E1&";"&A1&"!c"&ZEILE($A1)&":f"&ZEILE($A20)&"2;falsch)")
In den beiden 1.Fällen enthalten A1ff fort- bzw rücklfd Nrn (man könnte hier aber statt A1 im Original auch ZEILE($A1) bzw beim Zusatz 8-ZEILE($A1) verwenden!), im 2. die 4stelligen Jahreszahlen.
AUSWERTEN ist wahrscheinlich (und TxEval ganz bestimmt) nicht volatil.
Feedback nicht unerwünscht! Morhn, Luc :-?
„Die Intelligenzmenge ist auf diesem Planeten eine Konstante, die Bevölkerung nimmt aber zu!“ Auch deshalb informieren mit …

Beliebteste Forumthreads (12 Monate)

2 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread