Dringend Formel gesucht

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hi Christian,
Hängt das irgendwie mit dem "!B1" in der Formel zusammen?
ja, natürlich! Selbst schuld, wenn deine Musterdatei eine andere Spaltenstruktur hat als dein Template :-)
Also, ich geh mal für dich in die Formelanalyse. Wenn du den Grund für B1 verstehst, kannst du die Formel an deine eigenene Tabelle anpassen. Dazu zerlege ich die Formel schritt für Schritt, bis wir bei B1 angekommen sind:
=SUMME(BEREICH.VERSCHIEBEN(INDIREKT($B$5&"!B1");VERGLEICH(A9;INDIREKT($B$5&"!A:A");)-1;0;1;$B$4))
in B5 steht ja der Reiter-Name, sagen wir mal da steht "Einkauf". Dann löst sich die Formel folgendermaßen auf:
INDIREKT($B$5) = "Einkauf"
=SUMME(BEREICH.VERSCHIEBEN(Einkauf!B1;VERGLEICH(A9;Einkauf!A:A);)-1;0;1;$B$4))
in A9 steht "Personal". VERGLEICH(A9;Einkauf!A:A);) sucht im Reiter "Einkauf" nach der ZEILE der Position "Personal" und findet Zeile 6. Also:
VERGLEICH(A9;Einkauf!A:A);) = 6
=SUMME(BEREICH.VERSCHIEBEN(Einkauf!B1;6-1;0;1;$B$4))
Jetzt sind wir schon sehr nah dran. Summe ist klar, oder? Wir wollen ja da hin:
Summe(Einkauf!B6:F6)
Die Matrix "Einkauf!B6:F6" erstellt uns das BEREICH.VERSCHIEBEN, welches wir jetzt mal im Detail analysieren:
=BEREICH.VERSCHIEBEN(Referenz;Zeilen;Spalten;Höhe;Breite)
BEREICH.VERSCHIEBEN(Einkauf!B1;6-1;0;1;$B$4)
Also gilt:
Referenz = Einkauf!B1
Zeilen = 6-1 = 5
Spalten = 0
Höhe = 1
Breite = $B$4 = 5 (zur Erinnerung, das ist der Monat!)
die Matrix fängt an bei Referenz, also Einkauf!B1.
Von da aus geht sie 5 ZEILEN nach unten (6, minus eine da wir ja bereits in Zeile 1 sind! In Zeile 0 kann man leider nicht starten) und steht jetzt in Einkauf!B6. Schau mal nach, das ist der Januar-Wert von Personal in Einkauf!
Von dort aus wandern wir jetzt Null SPALTEN nach rechts. Ein Wunder, wir sind immer noch in Einkauf!B6. Jetzt definieren wir die HÖHE des Bereichs als Höhe = eine Zeile. Die Zelle B6 ist bereits eine Zeile hoch (alles andere wäre seltsam), also immer noch Einkauf!B6.
Jetzt kommt der große Moment! Wir suchen den Monat 5. Wir sind bereits in Monat1! Einfach die BREITE der Matrix auf 5 (für Monat 5) erweitern. Von unserer momentanen Position aus ergibt das eine Matrix der Höhe 1 und Breite 5, die von B6 ausgehend anfängt: es ist Einkauf!B6:F6 !
BEREICH.VERSCHIEBEN(Einkauf!B1;6-1;0;1;$B$4) = Einkauf!B6:F6
demnach ist:
SUMME(BEREICH.VERSCHIEBEN(Einkauf!B1;6-1;0;1;$B$4))
das gleiche wie:
SUMME(Einkauf!B6:F6)
und das ist genau die YTD-Formel, die wir suchen!
Zusammenfassung:
INDIREKT ermittelt die Blattnamen.
VERGLEICH sucht die Zeile
=$B$4 bringt die Breite in Monaten
B1 ist der Ausgangspunkt
Findest du nach der Erklärung heraus, gegen was du B1 in deiner Template tauschen musst?
Grüße,
Klaus M.vdT.

Hallo Christian,
danke für die positive Rückmeldung, es freut mich geholfen zu haben!
BEREICH.VERSCHIEBEN ist eine Zauberformel, wenn man ... ja, wenn man Bereiche verschieben will :-) Die meisten bereich.verschieben-Lösungen sehen auf den ersten Blick aus wie ein Overkill, da es meist in der Natur der Sache liegt jede Variable von bereich.verschieben mittels einer Formel zu ermitteln.
Auf den zweiten Blick, wenn man die Formel wieder zerlegt, wird es dann klarer :-) und keine Angst, auch ich habe diese Formel nicht einfach heruntergetippt, sondern natürlich mit Hilfsspalten entwickelt und erst final zusammengeführt (die Hilfsspalten haben dann auch Überschriften, das macht es viel verständlicher in der Entwicklung).
An dieser Stelle muss ich ja nochmal relativieren: Eigentlich hatte ich mir vorgenommen, keine Formeln mit Bereich.Verschieben mehr zu kreieren *1) und als alternative das wesentlich schnellere (und kompliziertere) Konstrukt Index():Index() zu nutzen *2), aber da in der hiesigen Formel "eh" das volatile INDIREKT vorkommen musste, hat das auch keinen Unterschied mehr gemacht.
Grüße,
Klaus M.vdT.
Zum Nachlesen der Hintergründe:
*1) http://www.online-excel.de/excel/singsel.php?f=171
*2) http://www.online-excel.de/excel/singsel.php?f=180

Beliebteste Forumthreads (12 Monate)

5 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

5
Beiträge zum Forumthread

Beiträge zu diesem Forumthread