BEREICH.VERSCHIEBEN für schmaler werdenden Datenbereich

[ruppy]

Hallo zusammen,

folgende Problematik. Meine Daten sehen beispielsweise so aus:

A B C D E
1 1 1 1 1


Nun möchte ich in F1 eine Formel schreiben, die mir den Bereich A1:E1 ausgibt (mit neustem Excel einfach =A1:E1 bzw. =A1#).
Diese Formel möchte ich nach unten ziehen können, wobei die Ausgabe immer um eine Spalte schmaler werden soll, d.h. die Ergebnisse sollen sein:

F1: A1:E1
F2: A1:D1
F3: A1:C1
F4: A1:B2
F5: A1

Ich habe es mit BEREICH.VERSCHIEBEN probiert bekomme es aber nicht hin -.-

Hintergrund der Fragestellung:
Möchte einen Weg finden diese Bezüge dynamisch zu ermitteln, um sie dann in einer Formel verwenden zu können, d.h.
die Lösungsformel soll später als Argument einer anderen Funktion genutzt werden.

Um das Problem nachvollziehen zu können, habe ich eine Datei angehängt.

Freue mich über jeden Beitrag :)

BG ruppy

[shift-del]

Moin ruppy

Code:

=SEQUENZ(;SPALTEN(A2:E2)-ZEILEN(F$2:F2)+1;1;0)

[schauan]

Hallöchen,

hier mal ein Ansatz mit zwei Stolperstellen.

Arbeitsblatt mit dem Namen 'Beispiel'
	A	B	C	D	E	F	G	H	I	J	K
1	Daten					erwünschtes Ergebnis
2	1	2	3	4	5	1	2	3	4	5	$E$2
3						1	2	3	4	#NV	$D$2
4						1	2	3	#NV	#NV	$C$2
5						1	2	#NV	#NV	#NV	$B$2
6						1	1	1	1	1	$A$2

Zelle	Formel
F2	{=A$2:INDEX($A$2:$E$2;1;6-ZEILE(A1))}
K2	=ADRESSE(2;INDEX($A$2:$E$2;;6-ZEILE(A1)))

Achtung, Matrixformel enthalten!

Die geschweiften Klammern{} werden nicht eingegeben.

Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Verwendete Systemkomponenten: [Windows (32-bit) NT 10.00] MS Excel 2016

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

#NV bekommst Du mit WENNFEHLER weg.
Für die letzte Zeile kannst Du eventuell einen Vergleich einfügen und mit $A$2 vergleichen, eine Formel für die Adresse hab ich rechts danaben.

[ruppy]

Hi ihr beiden,

funzt beides. Super vielen Dank !

Eine Frage noch:

Da ihr beide nicht BEREICH.VERSCHIEBEN genutzt habt, gehe ich davon aus, dass diese Funktion dafür nicht geeignet ist?

BG ruppy

[schauan]

Hallöchen,

BEREICH.VERSCHIEBEN gehört in den Kreis alter Funktionen und sollte m.E. nur noch verwendet werden wenn uralte Excelversionen zu bedienen sind die INDEX nicht kennen, ist umständlicher und nicht so flexibel, braucht mehr Ressourcen, … Na ja und inzwischen gibt es ja auch neueres als INDEX. SEQUENZ hab ich mit meinem 2016 noch nicht.

[ruppy]

Hi schauhan,

danke für die Info.
Mir war lediglich bekannt, dass die Funktion volatil ist aber nicht, dass sie auch schon recht betagt ist.

Bzgl. neuer Funktionen: Schade, dass du diese nicht nutzen kannst. Gerade bei einem Power-User wie dir wären sie gut aufgehoben.

BG ruppy

[schauan]

Hallo ruppy,

Schade, dass du diese nicht nutzen kannst

Kommt noch. Ich hab ja schon die Lizenz zum … für 2019 und muss irgendwann mal installieren. :17: Im Moment hab ich aber noch viel mit 2016 zu tun.

[shift-del]

Sicher geht auch BEREICH.VERSCHIEBEN().

Code:

=A$2:INDEX(A$2:E$2;SPALTEN(A2:E2)-ZEILEN(F$2:F2)+1)
=BEREICH.VERSCHIEBEN(A$2;;;;SPALTEN(A2:E2)-ZEILEN(F$2:F2)+1)