Excel Funktion: Summe(Bereich.Verschieben($B$5;(ZEILE()-ZEILE($E$5))*2;0;2))?

[excelschüler]

Hallo liebe Community,

seit Stunden versuche ich schon die folgende Funktion zu verstehen, aber ich bekomme es einfach nicht auf die Reihe. Ich habe die komplette Aufgabe samt Ergebnis mal als png in den Anhang gelegt. Hoffe es findet sich jemand der mir das erklärt.....

Kurz zur Aufgabe allgemein: Ich habe links meinen Datensatz und soll dann zuerst bei "first aggregation" aus dem Datensatz die Werte für a1, a2, a3 summieren. Dazu hat uns der Dozent sogar schon die notwendige Funktion mitgegeben, die wir einfach nur über copy/paste eingeben müssen. Die Aufgabe besteht eigentlich darin diese Funktion zu verstehen, damit wir sie in Zukunft bei ähnlichen Fragestellungen selbst anwenden können.

Diese Funktion lautet: Summe(Bereich.Verschieben($B$5;(ZEILE()-ZEILE($E$5))*2;0;2)).  

Zu der Bereich.Verschieben funktion gibt es ja diverse Youtube videos die ich mir alle schon angesehen habe und ich habe die Videos auch verstanden. Aber ich verstehe nicht wieso wir absolute Bezüge brauchen, was das Zeile () minus Zeile ($E$5) sein soll und das ganze noch mit 2 Multiplizieren. Ok 0 bedeutet das wir die Spalte des Anfangsbereichs nicht verändern. aber dann wieder Höhe 2?

Wenn jemand dann noch lust und energie hat würde ich mich auch freuen wenn mir jemand zum zweiten Teil der Aufgabe erklärt, wieso man noch diesen Teil (;"*"&G5&"*") hier braucht. Was machen die Anführungszeichen, * Zeichen und das &?

Also die Funktion in meiner Aufgabe scheint mir doch sehr anders zu sein als die die bei Youtube vorgestellt werden. Hoffe jemand kann mir das erklären....  

Liebe Grüße

[Schachspieler]

Hallo,

Ich zitiere mal die Excel-Hilfe

Durch die Funktion BEREICH.VERSCHIEBEN werden weder Zellen verschoben, noch wird die jeweilige Markierung aufgehoben. Es wird lediglich einen Bezug zurückgegeben. BEREICH.VERSCHIEBEN kann in Verbindung mit jeder Funktion eingesetzt werden, die als Argument einen Bezug erwartet. Die Formel SUMME(BEREICH.VERSCHIEBEN(C2;1;2;3;1)) berechnet das Gesamtergebnis eines 3 Zeilen und 1 Spalte umfassenden Bereichs, der sich 1 Zeile unterhalb und 2 Spalten rechts der Zelle C2 befindet.

Zeile() gibt die Zeilennr der akatuell aktiven Zelle zurück - Zeile($E$5) ergibt immer 5 wegen der
Wenn man in diese Formel beispielsweise in D7 eingibt, ergibt sich:
B$5;(7-5)*2=4
heißt von B5 aus 4 Zeilen nach unten, 0 Spalten nach rechts, d.h. identische Spalte = B9
Höhe=2: Größe des Zeilen-Bereichs von linker Ecke aus also B9 und B10
Breite: fehlt also selbe Breite wie Ursprungsbereich =B5=1, d.h. selbe Spalte
Ergebnis Summe(B9:B10)

prinzipiell halte ich Index für die bessere Funktion, da sie im Gegensatz zu Bereich.Verschieben nicht volatil ist (vereinfacht - Berechnung bei Veränderung der aktiven Zelle, auch wenn die Formel nicht von diesen Zellen betroffen ist).

Teil2
Es geht darum, welche Kriterien in D5:D14 gesucht werden; * ist ein Platzhalter, d.h. es werden alle Zellen gefunden, die "a" enthalten und vorne/hinten noch Text haben können. Und dies muss man als String zusammenbauen.

Übrigensl kommt man der Sache meistens mit der Formelauswertung (Formeln -> Formelasuwertung) auf die Schliche.

[Jockel]

Hallo liebe Community,

seit Stunden versuche ich schon die folgende Funktion zu verstehen, aber ich bekomme es einfach nicht auf die Reihe. Ich habe die komplette Aufgabe samt Ergebnis mal als png in den Anhang gelegt. Hoffe es findet sich jemand der mir das erklärt.....

...

Diese Funktion lautet: Summe(Bereich.Verschieben($B$5;(ZEILE()-ZEILE($E$5))*2;0;2)).  

... ich verstehe nicht wieso wir absolute Bezüge brauchen, was das Zeile () minus Zeile ($E$5) sein soll und das ganze noch mit 2 Multiplizieren. Ok 0 bedeutet das wir die Spalte des Anfangsbereichs nicht verändern. aber dann wieder Höhe 2?

Wenn jemand dann noch lust und energie hat würde ich mich auch freuen wenn mir jemand zum zweiten Teil der Aufgabe erklärt, wieso man noch diesen Teil (;"*"&G5&"*") hier braucht. Was machen die Anführungszeichen, * Zeichen und das &?

Also die Funktion in meiner Aufgabe scheint mir doch sehr anders zu sein als die die bei Youtube vorgestellt werden. Hoffe jemand kann mir das erklären....  

Liebe Grüße

Hallo, warum wir also ihr hier absolute Bezüge braucht..? Weil die Formel ziehbar sein soll... fertig! Warum Höhe 2? Weil es hier immer zwei Zellen sind die hier untereinander summiert werden sollen... Warum ..: =(ZEILE()-ZEILE($E$5))*2 --> gib das doch mal separat in eine Zelle und ziehe das nach unten... man könnte auch =ZEILE(A1)*2-2 schreiben ... Wenn du wissen willst was da passiert gib's in eine extra Zelle  ="*"&G5&"*" 

Ich denke da sind wir schon um Einiges weiter...!!!

[Jockel]

btw.: Hallo, die SUMMEWENNS() Formel ist eigentlich hier eine SUMMEWENN() ...

[excelschüler]

Hallo Schachspieler und Jockel,

danke für eure Antwoten!! Besonders deine Antwort Schachspieler hat mich heute um einiges weiter gebracht und ich kann sagen ich habe die Aufgabe verstanden und bin bereit mich an die nächste zu setzen :). Eigentlich gar nicht so schwer wenn man es einmal verstanden hat , aber vielleicht war ich gestern einfach ein bisschen zu müde dafür....

Liebe Grüße