Excel blattübergreifend?
Ich habe folgendes Problem:
- eine Tabelle mit Blatt1 und Blatt2
Blatt 1 besteht aus 10 Spaten A-K und 400 Zeilen
Ich möchte nun, das Blatt1 "A1" in Blatt2 "A1" steht.
Jetzt soll er in Blatt 2 "A2" den wert von Blatt 1 "A1" nur 20 Zeilen tiefer übernehmen und dass immer so weiter, also erst in A2 20 Zeilen tiefer (also den Wert aus Blatt1 Zelle A21) dann in A3 40 Zeilen tiefer (also den Wert aus Blatt1 Zelle A41 und so weiter.
Jetzt wird es für mich schwierig!
Wenn ich jetzt Blatt 2 Kopiere und zu Blatt 3 mache und auf Blatt 3 in die Zelle A1 den Wert von Blatt 1 "B1" einfüge dann soll Dann soll sich nach unten hin alles von Selbst von der Blatt1 A Spalte auf die Blatt 1 B Spalte ändern.
Wie mache ich das am Besten alles?
Vielen Dank
3 Antworten
Das ist sogar relativ einfach möglich, einfacher als man denken mag, mittels Formeln kannst du einfach die Ausgabe eines Datenbereichs um X Zellen Abstand erweitern:
=WENN(REST(ZEILE(A2)-1;$E$2+1)=0;INDEX($A:$A;(ZEILE(A2)-1)/($E$2+1)+1);"")
Kurz erklärt:
E2 ist jene Zelle in der Abstand definiert werden muss, in deinem Falle wäre das 19. (Zu Demonstrationszwecken bei mir 3 und 5)
REST: ermittelt innerhalb der WENN Bedingung, ob eine Zeilennummer der aktuellen Zelle / den Abstand + Die Datenzelle (also 20) Teilbar ist, ist dies der Fall ist der Abstand um 1 überschritten, es wird also der WAHR Teil der WENN Formel ausgegeben, ist dies nicht der Fall bleibt die Zelle leer.
INDEX: Index gibt nacheinander den passenden Datensatz aus, in diesem Falle also aller 20 (in meinem Falle 4 bzw. 6) Zeilen eine Zeile höher des Original-Datensatzes von Blatt1#
Im Anschluss muss diese heruntergezogen werden, bist zu der Zeile bis wohin sie gebraucht wird (Also Anzahl der Datensätze x 20)
In Zelle D2 habe ich noch eine dynamische Array Funktion verwendet, erst ab Excel 2021 verwendbar. Da sich innerhalb einer dynamischen Array Funktion die Ausgabe Zeile nicht ändert, da die gesamte Formel in einer Zelle steht, muss diese etwas angepasst werden.
=LET(Abstand;SEQUENZ(ANZAHL2(A2:A100)*($E$2+1);;0);WENN(REST(Abstand;$E$2+1)=0;INDEX($A2:$A100;Abstand/($E$2+1)+1);"") )
LET: gibt die Fähigkeit quasi Variablen zu definieren
Abstand: ist die erste Variable und wird durch die Anschließende Seqzenzfunktion definiert
SEQUENZ: Gibt eine Reihe von Zahlen aus, Sequenz(3) würde z.B. 1, 2, 3 in 3 untereinander liegende Zeilen schreiben. Im obigem Fall wird die Anzahl der Datensätze (in meinem Falle 4) multipliziert mit dem definierten Abstand +1, bei dir 19 (bei mir 3 bzw. 5) es ergibt also 80 (bei mir 16 bzw. 20).
Die Restfunktion funktioniert wieder identisch wie vorherige
Die Indexfunktion funktioniert auch identisch, nur das diese nicht einfach jede Datensatz eine höher geht, sondern die Sequenzausgabe berücksichtigt, aber das Ergebnis ist das gleich.
- - -
Welche Formel solltest du verwenden:
Hast du Excel 2019 und älter stellt sich diese Frage nicht, hast du hingegen 2021 und neuer würde ich immer zur 2 Variante Greifen, denn der Vorteil liegt auf der Hand, diese ist dynamisch und passt sich deinem Eingabebereich und deinem definierten Abstand an.
Dazu habe ich im Bild 2 den Abstand auf 5 geändert
Hier siehst du hat sich die Formel ohne mein zu tun nun über 20 Zeilen erstreckt, auch die Formel in C2 tut dies, aber nur wenn sie weit genug runter gezogen wurde, und gibt 0en aus, wenn mehr Zeilen abgefragt sind, als Datensätze verfügbar sind (das lässt sich noch Abfangen).
Da so wie ich dich verstehe das ganze aber eh nur eine Kopierhilfe ist kannst du im Anschluss den gesamten Bereich markieren strg+c und strg+shift+v drücken und die Formeln wurden in Werte umgewandelt und sind in der Zelle fest verankert.
Pro Tipp, anstelle die nachfolgen Spalten mit Funktionen wie
=index+Vergleich / Sverweis / Xverweis abzuholen kannst du in der 2 Variante auch einfach diese Spalte mit einbeziehen:
Dazu habe ich Index durch Zeilenwahl ersetzt und A2:A100 durch A2:B100 ersetzt , dies bindet alle Spalten in definieren Bereich mit ein, mit Index geht das nicht so ohne weiteres. Du könntest hier also auch A2:K100 schreiben, es sollte nur recht genug platz sein, andernfalls erhältst du #Überlauf (daher musste ich auch den Abstand in E2 in E1 verschieben).
Ich würde mich über Rückmeldung freuen.
Füge am Besten beide Blätter zusammen, das löst alle Probleme
Du könntest folgendes machen.
In einer Hilfsspalte die Zeilenzahl um jeweils 20 erhöhen
... und die Werte der (dann) Spalte B per Xverweis ... oder auch per SVerweis in Blatt 2 abholen
