Ausgangslage:
Es liegen zwei- oder sogar dreidimensionale Daten (in den Blättern Pub_1, Pub_2, ...) vor, die sich für bestimmte Auswertungen (Teilergebnis, Pivot, Auto- oder Spezialfilter) nicht eignen. Besser wäre eine Darstellung wie im Blatt Liste:
A B C D E 1 Bier Wein Saft 2 Kasse A 123,50 98,00 3 Kasse B 78,50 61,00 42,00 4 A B C D E 5 1 Milch Bier Wein 6 2 Kasse D 5,00 176,40 5,00 7 3 Kasse B 161,00 23,00 8 4 A B C D E \Pub_1/ 5 1 Kasse Getränk Umsatz Pub 6 2 Kasse A Bier 123,50 Pub_1 7 3 Kasse A Wein 98,00 Pub_1 8 4 Kasse A Saft 0,00 Pub_1 \Pub_2/ 5 Kasse B Bier 78,50 Pub_1 6 Kasse B Wein 61,00 Pub_1 7 Kasse B Saft 42,00 Pub_1 8 Kasse D Milch 5,00 Pub_2 9 Kasse D Bier 176,40 Pub_2 \Liste/ A B C 1 ZE 2 maximale Zeilen pro Blatt (hier Anzahl der Kassen) 2 SP 3 maximale Spalten pro Blatt (hier Anzahl Getränke) 3 QZ nicht zu berücksichtigende Kopfzeilen der Blätter 4 QS nicht zu berücksichtigende Vorspalten der Blätter 5 ZZ 1 einzuplanende Zeilen vor Liste (z.B. für Feldnamen) 6 ZT einzuplanende Spalten vor Liste 7 NA Pub_ Rumpfname, falls NU nicht gewählt (NU=0) ist 8 NU 1 Positionsnummer des ersten einzubeziehenden Blatts \Parameter/
Lösung:
Zunächst ist der Formel =INDEX(ARBEITSMAPPE.ZUORDNEN(1);GANZZAHL((ZEILE()-1-ZZ)/ZE/SP)+NU)
mittels Einfügen Name Definieren der Name "MP" zuzuweisen,
anschließend der Formel =TEIL(MP;FINDEN("]";MP)+1;99) der Name "BL".
Auf Parameter!A1:B8 wenden Sie Name Erstellen [x] von links an.
Im Blatt "Liste" tragen Sie dann ab A1 (hier im Beispiel wegen ZZ=1 statt dessen: A2) die folgende Formel ein:
=WENN(SPALTE()-ZT<4;INDIREKT(WENN(NU;BL;NA&GANZZAHL((ZEILE()-1-ZZ)/ZE/SP)+1)&"!"
&ADRESSE((REST(GANZZAHL((ZEILE()+SP-1-ZZ)/SP)-1;ZE)+1)*(SPALTE()-ZT<>2)+1+QZ;
(REST(ZEILE()+SP-1-ZZ;SP)+1)*(SPALTE()-ZT>1)+1+QS));
WENN(NU;BL;GANZZAHL((ZEILE()-1-ZZ)/ZE/SP)+1))
Bemerkungen:
Die Formel ist mit 254 Zeichen so lang, da sie für alle Spalten der gewünschten Liste gleich anwendbar sein sollte. Damit kann sie - abgesehen von Formatierungen - allein von den 8 Parametern (s.o.) aus gesteuert werden, die sich für eine direkte Anschaulichkeit auch auf dem Blatt "Liste" befinden können (z.B. ab Spalte F).
Da die Dimension Blatt für die Formel die äußerste Klammerung bildet, benötigt man keine Anzahl der Blätter. Die Liste wird allein durch Ausfüllen der Formel nach unten bestimmt; #BEZUG! zeigt dann das Ende an (kann problemlos gelöscht werden, da nur eine Formel zum Wiederbefüllen zu berücksichtigen ist).
Das bedeutet gleichzeitig, dass auch nur ein Blatt ausreicht (2-Dimensionalität als eingeschlossener Spezialfall, in der Datei auch mit eigener Formel dargestellt). Bei 3 Dimensionen kann ausgewählt werden zwischen
a) gleichförmig benannten, aber in der Reihenfolge unwichtigen Blättern (NU=0), oder
b)Excel 4+ Blättern mit Namen ohne Systematik, die aber hintereinander stehen müssen (NU>0).
Die Namen MP und BL müssen übrigens nur vergeben werden, wenn b) angewendet werden soll. Das wahrt für andere Tabellenkalkulationen ohne benannte Formeln zumindest die Möglichkeit a). Aus dem gleichen Grund wurden sich wiederholende Formelteile wie GANZZAHL((ZEILE()-1-ZZ)/ZE/SP)+1 nicht separat benannt.
Für Interessierte noch einmal zur Formel: SPALTE() wird allein zur Regelung der Dimensionen verwendet. Bei spezialisierten einzelnen Formeln für jede Spalte wäre sie unnötig. Ohne sie ist aber keine Parametersteuerung in diesem Umfang möglich.