Office: (Office 2010) Alternative zu volatile Funktionen (Blattübergreifend!)

Hallo liebe Excel-Gemeinde!

Ich möchte gerne die volatilen Funktionen nach Möglichkeit durch andere ersetzen. Jetzt habe ich auch schon einige ausgetauscht. Was mir noch fehlt, sind Ersatzformeln für doch so einfach zu nutzende Formeln, wie "indirekt...", die über Blattgrenzen hinweggehen und wo der Blattname über eine Zelle des gleichen Blattes zu nehmen ist.
Beispiel: Ich möchte vom Blatt "2" eine bestimmte Zelle auslesen. Jetzt habe ich derzeit eine INDIREKT-Formel genutzt. Hier kann ich nämlich auch den Blattnamen aus einer Zelle auslesen. *yelrotflmao
Ich weiß jedoch nicht, wie ich das mit Index (oder mit einer sonstigen NICHT-volatilen Funktion) hinbekomme!*frown.gif* *mad.gif*
Kann mir jemand helfen? *sos

Gruß
Walter

:)

 

Hallo Walter, kannst du bitte mal (d)eine (beispiel)datei posten..?

 

Hi

wenn die Blattnamen bekannt sind und auch nicht mehr geändert werden können, vielleicht so, in Zelle A1 steht der Blattname.

statt Indirekt("'"&A1&"'!A:A) um auf Spalte A der gewählten Tabelle zu verweisen nimmst du:

Wahl(Vergleich(A1;{"Tabelle1"."Tabelle2"."Tabelle3"};0);Tabelle1!$A:$A;Tabelle2!$A:$A;Tabelle3!$A:$A)

Gruß Daniel

 

Hallo Daniel,

Indirekt ist eine volatile Funktion. Das heißt mit dieser eingesetzten Funktion und in großer Anzahl wird Excel sehr lahm. Deswegen möchte ich stattdessen "index(...)" einsetzen.

Gruß
Walter

 

Hallo Frank,

anbei sende ich dir eine Beispieldatei. Die Formel der Zelle "D6" ist das Problem. Diese Index-Formel möchte dynamisieren, so dass immer die Zelle (mit dem Blattnamen) ausgelesen wird.
Manch einer wird nun sagen, Excel berechnet noch in nicht merklicher Zeit das Ergebnis für Zelle D5. Richtig! Wenn ich jetzt mehrere tausend solcher Formeln habe ist's nicht mehr so lustig*frown.gif* . Hinzu kommt, dass die Originaldatei mehrere Verschachtelungen enthält, die die Datei sicherlich auch nicht zum Renner machen.

Kannst du mir oder jemand anderer helfen?

Gruß
Walter

 

Hallo Walter,

Daniel hat Dir doch schon einen prima Vorschlag unterbreitet. Was spricht dagegen, diesen zu nutzen?

Wenn der Blattname "1" unbelegt ist:
Code:

Grüße
EarlFred

 

Hallo EarlFred,

dagegen spricht nur, dass dann die alternativen Blattnamen fix sind. Aber die sind in der Musterdatei einfach durchnummeriert von 1 bis 15. Erst später im wirklichen Einsatz erhalten sie die endgültigen Namen, können sich aber im Laufe der Zeit wieder ändern.

Schön wäre...
z.B. =index("'"&a6&"'!A1:F10";b6;c6)

Gruß
Walter

 

Hallo Walter,

eine wichtige Information.

Dann wirst Du um Indirekt oder VBA nicht umhinkommen.

Grüße
EarlFred

 

hallo, dann arbeite doch mit BereichsNamen... wenn sich der5 BlattName ändert ändern sich doch auch die Bezüge...

 

gelöscht wegen doppelpost...

 

Hallo Walter,

es gibt nun mal auch einen guten Grund für INDIREKT, ansonsten bräuchte es die Funktion nicht zu geben. Wenn Du nicht irgendetwas noch verändern kannst in Deiner Aufgabenstellung (ich denke da an "alle ersetzen...."), dann gibt es für Dich nur die Lösung mit INDIREKT (oder VBA)!

Aber ich verstehe die Panikmache in einschlägigen Excelerklärungen nicht wirklich. INDIREKT ist zwar eine volatile Funktion, was aber auch nur heißt, dass sie jedesmal neu berechnet werden muss. Eine andere Funktion, wie INDEX, muss aber auch berechnet werden. Zumindest beim ersten Mal. Aber auch bei jedem weiteren Berechnen von Excel wird INDEX nicht zwangsläufig nicht mehr berechnet, nur weil dort keine Eingabe gemacht wurde (woran das liegt weiss ich allerdings nicht).
Ich selber benutze eine Datei mit ca. 160.000 komplizierten INDIREKT-Formeln, die sich wiederum auf hunderttausende Zellen beziehen und zusätzlich noch mit SUMMENPRODUKT oder SVERWEIS aufgebaut sind. Die Rechenzeit liegt zwar bei 90 sec. aber dieselben Formeln mit INDEX benötigen auch 90 sec.!

Probiere es doch selber mal in Deiner Datei aus. Wenn sich der Blattname bei Dir nicht ändert, funktionieren beide Formeln. Also lasse die Stoppuhr entscheiden.

LG Ingo

 

Hallo Frank,

das nutzt doch nicht mehr, als wenn er direkt den Tabellennamen angibt:

=INDEX('Meine erster Blattname'!A1:F10;B6;C6)

LG Ingo

 

hallo Ingo, ich meinte ja auch mehrere Bereichsnamen... z.b.

Arbeitsblatt mit dem Namen 'Basis'
*	A	B	C	D
4	Auf welchem Blättern stehen die Daten	Zeile	Spalte	Formeln
5	2	4	4	D13

Name	Bezug
B_Name1	='35'!$A:$F
B_Name2	=ganz_anders!$A:$F

Zelle	Formel
D5	=INDEX(WAHL(A5;B_Name1;B_Name2);B5;C5)

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Arbeitsblatt mit dem Namen '35'
*	A	B	C	D	E	F
1	A1	B1	C1	D1	E1	F1
2	A2	B2	C2	D2	E2	F2
3	A3	B3	C3	D3	E3	F3
4	A4	B4	C4	D4	E4	F4
5	A5	B5	C5	D5	E5	F5
6	A6	B6	C6	D6	E6	F6
7	A7	B7	C7	D7	E7	F7
8	A8	B8	C8	D8	E8	F8
9	A9	B9	C9	D9	E9	F9
10	A10	B10	C10	D10	E10	F10

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

Arbeitsblatt mit dem Namen 'ganz_anders'
*	A	B	C	D	E	F
1	A10	B10	C10	D10	E10	F10
2	A11	B11	C11	D11	E11	F11
3	A12	B12	C12	D12	E12	F12
4	A13	B13	C13	D13	E13	F13
5	A14	B14	C14	D14	E14	F14
6	A15	B15	C15	D15	E15	F15
7	A16	B16	C16	D16	E16	F16
8	A17	B17	C17	D17	E17	F17
9	A18	B18	C18	D18	E18	F18
10	A19	B19	C19	D19	E19	F19

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

 

\@Frank
schön gelöst! Er muss dann allerdings mit dem Index arbeiten, nicht mit dem Namen.

@Ingo
Mach mal den Test:
CodeModul eines Worksheets
Code:

Trage in C1 folgende Formel ein:
Code:

Der Zähler in A1 springt auf 1, wie erwartet.
Ändere den Wert in B1. Der Zähler erhöht sich auf 2.
Ändere den Wert in B2. Der Zähler zuckt nicht. Klar, geht die Formal schließlich nix an, die bezieht sich ja nicht auf B2.

Ändere die Formel in C1 in
Code:

Huch!
Ändere den Wert in B1. Doppelhuch!
Ändere den Wert in B2. Dreifachhuch!

Grüße
EarlFred

 

hallo EarlFred, das habe ich nicht verstanden... der TE will doch mit dem INDEX() arbeiten. und vorhandene BlattNamen (warum auch immer) umbenennen... und zwischen denen auswählen können... ... oder habe ich da was missverstanden...