Formel verändert sich unbeabsichtigt automatisch

[winlang]

Ich habe folgendes Problem: In einer Exceldatei mit mehreren Registerkarten (zwischen denen auch mehrfache past©-Aktionen beim Erstellen stattgefunden haben) soll in einer Registerkarte ein Mittelwert in Zelle B37 mit folgender Formel berechnet werden "=MITTELWERT(B2:B17)". Das funktioniert solange auch zuverlässig, wenn ich entsprechende Werte in die Zellen von B2 bis B17 eingebe. Sobald ich jedoch in die nachfolgenden Zellen B18, B19 usw. weitere Werte eingebe (für andere Berechnungen), verändert sich die Formel in Zelle B37  (selbständig und unerwünscht)  in "=MITTELWERT(B2:B18)" , =MITTELWERT(B2:B19) usw. . Was ist da los? Wie finde ich den Fehler bzw. wie kann ich ihn abstellen?
Kann mir jemand einen Tipp geben?

[RPP63]

Moin!
Wenn Du einen Bereich zuverlässig fixieren willst, solltest Du den Bereich mittels $$ festlegen:

Code:

=MITTELWERT(B$2:B$17)

Gruß Ralf

[shift-del]

Moin

Verschiebe die Formel nach C37.

[slowboarder]

Hi
normalerweise erweitert Excel die Zellbezüge ja nicht, wenn du Liste einfach weiter schreibst.
das passiert, soweit mir bisher bekannt nur:
- wenn du Zellen oder Zeilen zwischen drin (also zwischen Zeile 2 und 17) einfügst oder löschst
- die Liste eine formatierte Tabelle ist.
aber es passiert offensichtlich auch, wenn die Formel in der selben Spalte steht, das war mir bisher nicht bekannt. Das scheint tatsächlich ein Feature zu sein, dass sich Microsoft ausgedacht hat.

Daher der Vorschlag von Detlef, die Formel in eine andere Spalte zu verschieben.

sollte das nicht möglich sein, gibt es mehrere Varianten, den Bezug auf die Zeile 2-17 zu fixieren

=Mittelwert(Indirekt("B2:B17"))
=Mittelwert(Indirekt("Z2S:Z17S";0)
=Mittelwert(Bereich.Verschieben(B2;0;0;16;1))
=Mittelwert(Index(B:B;2):Index(B:B;17))

bei der ersten Indirekt-Funktion ist der Bezug immer auf B2:B17, egal was du machst, auch wenn du die Formel in eine andere Spalte kopierst
bei den anderen und der zweiten Indirekt-Funktion kannst du die Formel in eine andere Spalte kopieren und der Bezug wandert dann in diese Spalten mit.

Gruß Daniel

[shift-del]

aber es passiert offensichtlich auch, wenn die Formel in der selben Spalte steht, das war mir bisher nicht bekannt. Das scheint tatsächlich ein Feature zu sein, dass sich Microsoft ausgedacht hat.

Entscheidend ist dass die Zelle mit der Formel unterhalb der Werte steht. Wenn die Formel oberhalb der Werte steht passiert nichts.

[slowboarder]

Entscheidend ist dass die Zelle mit der Formel unterhalb der Werte steht. Wenn die Formel oberhalb der Werte steht passiert nichts.

das automatische Erweitern passiert nur, wenn folgende Dinge gleichzeitig zutreffen:
- die Formel steht in der selben Spalte
- die Formel steht unterhalb der Zellen des Bezugs
- zwischen Formelzelle und Bezugszelle befinden sich nur Leerzellen

dh es würde auch schon ausreichen, einfach ein Leerzeichen in einer der Zellen dazwischen einzufügen um das automatische fortschreiben zu verhindern.
oder man passt einmalig den Zellbezug an, nachdem man den ersten zusätzlichen Wert angefügt hat, bei den weiteren verändert sich der Bezug dann nicht mehr
oder man macht es sich zur Angewohnheit, zwischen zwei unterschiedlichen Listen immer eine Leerzelle Abstand zu halten

Gruß Daniel

[Fred11]

Hallo winlang,

Excel versucht halt intelligent zu sein und geht davon aus, dass wenn die Liste erweitert wird, die neuen Werte auch im Formelergebnis berücksichtigt werden sollen.

Dieses Verhalten kann man in den Optionen (de-) aktivieren.
Wenn in den Optionen (=> erweitert) der Haken bei "Datenbereichsformate und -formeln erweitern" entfernt wird, sollte der Zellbezug nicht mehr automatisch angepasst werden.
   


Gruß
Fred