Excel Bezug + X zu neuen Zelle?
Guten Morgen, wie kann ich einen Bezug mit Zahl X addieren und mit der Summe die neue Zelle auswählen als Quelle?
Beispiel: Zelle A1 - Zelle A500 brauch ich einen Mittelwert von Zelle A10-A20, danach von A40-A50, danach von A60-A70, sprich es ist immer +20 wo er die nächsten 10 wieder als Mittelwert nutzen soll.
Formel:
=MITTELWERT(A10:A20)
=MITTELWERT(A40:A50)
=MITTELWERT(A60:A70)
Danke
4 Antworten
Sprich, die erste Zelle ist C46, von da aus müssen immer 96+ und dann die nächsten 51 Zellen als Mittelwert genommen werden.
Wenn ich DAS mache, komme ich aber auf:
=MITTELWERT(Lastgang!C46:C97)
=MITTELWERT(Lastgang!C142:C193)
=MITTELWERT(Lastgang!C238:C289)
DAS würdest Du erreichen mit der Formel:
=MITTELWERT(INDEX(Lastgang!C:C;46+96*(ZEILE(A1)-1)):INDEX(Lastgang!C:C;97+96*(ZEILE(A1)-1)))
und runterkopieren.
Check nochmal Deine Angaben. Ggf. kannst Du noch etwas rumexperimentieren, indem Du die 96 in der Formel änderst.
Hilft Dir das?
Hey Oubyi, das klappt auf jeden Fall schon mal aber komisch ist das es nicht ganz aufgeht bei der letzten Rechnung und das die Tage irgendwie verschoben sind. Denn am Sonnt ist der Lastgang höher als er sein sollte. Das sieht so aus als wenn es der Montag ist. Anbei mal die Datei wo du besser siehst, wie ich das meine. Danke für die Formel. Mega Gut :)
https://filehorst.de/d/ebfFAamu
Du könntest folgendes machen:
in einer Hilfsspalte sämtliche Zeilen durchnummerieren und ... die Zeilen, aus denen Du den Mittelwert ziehen willst, benennen und per =Summewenns die Werte ermitteln.
Nach Deinen Angaben handelt es sich bei z.B. A10 bis A20 nicht um 10 sondern um 11 Werte. D.h. die Formel müsste entsprechend angepasst werden =(Formel)/11 oder der Bezug auf z.B. A10:A19 usw. begrenzt.
Willst Du die Spalte A beibehalten, kannst Du die Nummerierung auch in einer anderen Spalte vornehmen.
Entweder indem du die Ergebnisse immer an der gleichen Stelle relativ zu den Daten anzeigen lässt. Also z.B. dass der Mittelwert für A10-A20 in der Zelle B10 steht, der Mittelwert für A30-A40 in der Zelle B30 etc. Dann kannst du einfach das Feld mit der Formel und 19 leere Felder markieren, kopieren und bis unten einfügen.
Oder du nimmst wirklich einen berechneten Zellenbezug, also wenn du z.B. in B1:B25 die Werte 10, 30, 50, 70 etc. drinstehen hast, kannst du in C1 diese Formel schreiben und runterziehen:
=MITTELWERT(INDIREKT("A"+B1+":A"+(B1+10)))
Hey daCypher, irgendwie klappt das nicht. Ich weiß nicht ob das Problem ist, das es ein anderes Blatt innerhalb der Mappe ist?
So sieht meine Formel jetzt aus
=MITTELWERT(Lastgang!C46:C97)
=MITTELWERT(Lastgang!C143:C194)
=MITTELWERT(Lastgang!C239:C290)
Sprich, die erste Zelle ist C46, von da aus müssen immer 96+ und dann die nächsten 51 Zellen als Mittelwert genommen werden.
Achso. Der Name von dem Blatt muss dann natürlich auch mit in die Indirekt-Funktion rein. Also du machst z.B. ab B1 die Anfangszahl der Zelle, auf die du dich beziehst, also 46, 143, 239 etc. (ich weiß nicht, ob du dich da vertippt oder verrechnest hast, aber von 46 bis 143 sind +97. Von 143 bis 239 nur +96)
Damit kannst du dann die Indirekt-Funktion füttern
=MITTELWERT(INDIREKT("Lastgang!C" & B1 & ":C" & (B1 + 51)))
Wenn du die Hilfsspalte vermeiden willst, kannst du es auch Matrixformel umbauen. Komischerweise funktioniert die Formel nur als manuelle "CSE"-Formel, obwohl die neueren Excel-Versionen eigentlich automatisch Matrixformeln verstehen.
Du kannst dir eine dieser beiden Formeln aussuchen:
{=MITTELWERT(BEREICH.VERSCHIEBEN(Lastgang!C46:C97;96*SEQUENZ(25;1;0;1);0))}
oder
{=MITTELWERT(INDIREKT("Lastgang!C"&SEQUENZ(25;1;46;96)&":C"&SEQUENZ(25;1;97;96)))}
Die erste Zahl in der Sequenz-Funktion gibt dabei an, wie viele Bereiche es gibt. Also die Funktionen haben als Beispiel beide 25 Bereiche (würde also bis Lastgang!C2401 gehen).
Damit die Formeln funktionieren, musst du 25 Zellen markieren, die Formel ohne die geschweiften Klammern in den Bearbeitungsbereich kopieren und dann Strg+Shift+Enter drücken. Dadurch sollten die geschweiften Klammern automatisch erscheinen und die 25 Zellen gefüllt werden.
Dafür kannst du BEREICH.VERSCHIEBEN verwenden.
Wenn du z. B. in B3 den Mittelwert von A10:A20 haben willst, in B4 den von A40:A50 etc., dann schreibst du in B3
=MITTELWERT(BEREICH.VERSCHIEBEN($A$10;(ZEILE()-ZEILE(B$3))*10;1;ZEILE($A$20)-ZEILE($A$10)+1;1))
(hab im MOment leider keine Zeit, das zu prüfen, schicke es aber schon mal ab)
Hab mich vertan in der Verschiebung; richtig wäre:
=MITTELWERT(BEREICH.VERSCHIEBEN($A$10;(ZEILE()-ZEILE(B$3))*10;0;ZEILE($A$20)-ZEILE($A$10)+1;1))
Dann kannst du die Formel "herunterziehen"
hat sich erledigt, ich war ein zu weit gesprungen von A1 zu A3 und hab A2 vergessen, passt perfekt. Danke