Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

BEREICH.VERSCHIEBEN für schmaler werdenden Datenbereich
#1
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 BeitragSmile

BG ruppy


Angehängte Dateien
.xlsx   Beispiel.xlsx (Größe: 9,82 KB / Downloads: 7)
Antwortento top
#2
Moin ruppy

Code:
=SEQUENZ(;SPALTEN(A2:E2)-ZEILEN(F$2:F2)+1;1;0)
Wir sehen uns!
... shift-del
Hinweise zu meiner Hilfe in Foren
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
[-] Folgende(r) 1 Benutzer sagt Danke an shift-del für diesen Beitrag:
  • ruppy
Antwortento top
#3
Hallöchen,

hier mal ein Ansatz mit zwei Stolperstellen.

Arbeitsblatt mit dem Namen 'Beispiel'
ABCDEFGHIJK
1Datenerwünschtes Ergebnis
21234512345$E$2
31234#NV$D$2
4123#NV#NV$C$2
512#NV#NV#NV$B$2
611111$A$2

ZelleFormel
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.
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
[-] Folgende(r) 1 Benutzer sagt Danke an schauan für diesen Beitrag:
  • ruppy
Antwortento top
#4
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
Antwortento top
#5
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.
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
Antwortento top
#6
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
Antwortento top
#7
Hallo ruppy,

Zitat: 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.
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
Antwortento top
#8
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)
Wir sehen uns!
... shift-del
Hinweise zu meiner Hilfe in Foren
Excel Super-Funktionen: VERWEIS(), INDEX(), WAHL()
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste