Überlauf in Matrix-Formel verhindern

[Binary91]

Hallo,

ich habe folgendes Problem:
Aus einer Datentabelle mit 100 Zeilen und 20 Spalten möchte ich mittels MTRANS() alle Daten in Blöcken á 20 Zeilen in eine einzige Spalte transferrieren. Diese Spalte hat somit 20x100=2000 Zeilen. Ich überprüfe anhand eines Divisors die Blockgrenzen, sodass immer an der korrekten Stelle ein neuer Zeilenblock beginnt. Das Problem nun: MTRANS() generiert mir einen Überlauf, sodass ich nicht per Copy-Paste die Formel über alle 2000 Zeilen ziehen kann. Selbst, wenn ich mittels einer WENN-DANN-Abfrage versuche, MTRANS() nur auf die entsprechenden Blockgrenzen zu legen, wird der Überlauf angezeigt. Gibt es da eine elegante Lösung, wie ich das Problem lösen kann oder muss ich tatsächlich alle 100 Blockgrenzen manuell mit der Formel befüllen?

Hier der Code (In Spalte $A steht in jeder 20.sten Zeile ein Wert):

Code:

=WENN($A1<>"";MTRANS(BEREICH.VERSCHIEBEN(N_MatrixKalkulationÜber;VERGLEICH($A1;T_Kalkulationen[Kalkulationen];0)-1;0;1;SPALTEN(N_MatrixKalkulationÜber)));"")

[LCohen]

#ÜBERLAUF! ist ein Fehler, den es nicht gibt. Denn gespillte Formeln werden nur dort angewendet, wo dahinter (unten und/oder rechts) nichts mehr kommt.

"Formel ziehen" und #ÜBERLAUF! passen sowieso nicht in einen Satz. Das ist völlig unmöglich. Das ist wie Fahnenflucht:

"Wir ziehen (mit erprobten Formeln, z.B. fürs Überleben) in den Krieg und #LAUFEN ÜBER!"

[Binary91]

Vielen Dank für Deinen Post. Leider werde ich noch nicht ganz schlau daraus   Gibt es nun eine Möglichkeit zur Abhilfe, oder kann ich eine Formel wie MTRANS() einfach nicht "ziehen", selbst in einer WENN-Verbindung?

[schauan]

Hallöchen,

so wie es ausschaut würden wir uns gerne mal anschauen, wie der Fehler genau ausschaut

[Binary91]

Ok, anbei eine Beispiel-Exceldatei. In der Spalte "Ohne Ziehen" sind die Daten für den ersten Datenblock korrekt aufgefüllt. Würde ich diese Formel nun manuell in die verbleibenden 99 Blockgrenz-Zellen kopieren, dann würde alles funktionieren. Das ist aber viel zu aufwändig. Stattdessen würde ich gerne einfach die Formel "ziehen" und MTRANS lediglich in jenen Zellen zur Anwendung bringen, welche an den Blockgrenzen sitzen. Leider funktioniert das nicht, da der #Überlauf!-Bereich dadurch überschrieben wird, denke ich.

Was meint ihr?

PS: Ich nutze natürlich MS 365.

[schauan]

Hallöchen,

1) die Formel gehört in Zeile 2, bis Zeile 21 kommt dann automatisch
2) markiere anschließend den Bereich Zeile 2:21 und ziehe dann

[rate mit]

Hallo,

deine Namen sind fortlaufend, dann könntest du diese Formel verwenden...

Code:

=INDEX(Rohdaten!$B$2:$U$101;KÜRZEN((ZEILE(A1)-1)/20)+1;REST(ZEILE(A1)-1;20)+1)

Kreuztabelle auflösen, d.h. in Datensatzform darstellen (herber.de)

[Binary91]

Hallöchen,

1) die Formel gehört in Zeile 2, bis Zeile 21 kommt dann automatisch
2) markiere anschließend den Bereich Zeile 2:21 und ziehe dann

Danke!!!   Super Sache, wusste ich gar nicht, dass man ganze Bereiche ziehen kann/muss. Vielen Dank, das war exakt die Lösung, die ich suchte.

Hallo,

deine Namen sind fortlaufend, dann könntest du diese Formel verwenden...

Gute Idee, aber nur in dieser Beispieldatei sind die Namen dementsprechend. Ich hatte mir auch bereits eine komplexe Formel unter der Anwendung von INDRIEKT überlegt, aber die Lösung oben ist exakt die, die ich benötige.

Danke euch.

LG, guten Rutsch

[schauan]

Hallöchen,

und
3) Die Formel sollte bei 20 Zeilen so aussehen: =WENN($A2<>"";MTRANS(BEREICH.VERSCHIEBEN(Rohdaten!$B$2:$U$2;(ZEILE()-2)/20;0;1;20));"")

[Cadmus]

Oder der Office Version mal ein Upgrade gönnen und PowerQuery benutzen. Da sind das nur ein paar Klicks zum Entpivotieren.