Office - Word, Excel und Co. 9.744 Themen, 41.408 Beiträge

Verfolgung starten Optionen

Excel 2013: Beim Einfügen von Zeilen verändern sich Formlen

pappnasen / 4 Antworten / Baumansicht Nickles

Ich habe eine Tabelle mit mehren Spalten. In der obersten Zeile unter den Überschriften ist immer das aktuelle Datum mit drei Werten (B2, C2, D2)
Täglich füge ich oben neue Zellen mit dem neusten Datum ein.

Nun brauche ich Mittelwerte über 7, 14 und 30 Tage.

Die Formeln sehen so aus:

=RUNDEN(MITTELWERT($B$2:$D$8);0)  '7 Tage
=RUNDEN(MITTELWERT($B$2:$D$15);0)  '14 Tage
=RUNDEN(MITTELWERT($B$2:$D$31);0)  '30 Tage

Das sieht erst mal gut aus. Aber wenn ich oben neue Zellen einfüge, dann wandern die Zellbezüge mit.

=RUNDEN(MITTELWERT($B$3:$D$9);0)
=RUNDEN(MITTELWERT($B$3:$D$16);0)
=RUNDEN(MITTELWERT($B$3:$D$32);0)

Wie kann ich das verhindern?

Das Dollarzeichen interessiert ihn nicht die Bohne.
Der Zugriff soll immer auf den gleichen Zellbereich sein.

bei Antwort benachrichtigen
mawe2 pappnasen „Excel 2013: Beim Einfügen von Zeilen verändern sich Formlen“
Optionen

In welchen Zellen stehen die Formeln für die Mittelwerte?

bei Antwort benachrichtigen
pappnasen mawe2 „In welchen Zellen stehen die Formeln für die Mittelwerte?“
Optionen

F3, G3, H3

Ich habe das jetzt provisorisch mit VBA gelöst.
Gefällt mir aber nicht so richtig.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   
    Dim Bereich As Range
    Dim Zahl As Double


    Set Bereich = Tabelle9.Range("B2:D8")
    Zahl = Application.WorksheetFunction.Sum(Bereich) / 21
    Zahl = Application.WorksheetFunction.Round(Zahl, 0)
    Tabelle9.Range("F3").Value = Zahl

    Set Bereich = Tabelle9.Range("B2:D15")
    Zahl = Application.WorksheetFunction.Sum(Bereich) / 42
    Zahl = Application.WorksheetFunction.Round(Zahl, 0)
    Tabelle9.Range("G3").Value = Zahl
   
    Set Bereich = Tabelle9.Range("B2:D31")
    Zahl = Application.WorksheetFunction.Sum(Bereich) / 90
    Zahl = Application.WorksheetFunction.Round(Zahl, 0)
    Tabelle9.Range("H3").Value = Zahl
   
 End Sub

bei Antwort benachrichtigen
mawe2 pappnasen „F3, G3, H3 Ich habe das jetzt provisorisch mit VBA gelöst. Gefällt mir aber nicht so richtig. Private Sub ...“
Optionen
Ich habe das jetzt provisorisch mit VBA gelöst. Gefällt mir aber nicht so richtig.

Was stört Dich an Deiner VBA-Lösung?

Du könntest stattdessen an anderer Stelle (z.B. auf einem anderen Tabellenblatt) eine Hilfstabelle anlegen, in die Du die in Frage kommenden 90 Werte aus dem (konstanten) Tabellenbereich der Haupttabelle mittlels der INDIREKT-Funktion übernimmst.

Fügst Du nun in der Haupttabelle neue Zellen ein, übernimmt die Hilfstabelle trotzdem immer genau den Zellbereich für die obersten 30 Tage.

Auf diese Hilfstabelle kannst Du dann die RUNDEN-MITTELWERT-Formeln anwenden und diese Ergebnisse wieder in der Haupttabelle abbilden.

Dies wäre eine VBA-freie Lösung.

Gruß, mawe2

bei Antwort benachrichtigen
pappnasen mawe2 „Was stört Dich an Deiner VBA-Lösung? Du könntest stattdessen an anderer Stelle z.B. auf einem anderen Tabellenblatt ...“
Optionen
Was stört Dich an Deiner VBA-Lösung?

Sie VBA-Lösung arbeitet schon, aber sie ist nicht korrekt.

Ich will nur die Mittelwerte haben, wenn in den Zellen was drinsteht.

Das heißt ich müsste vorher prüfen, wie viele Zellen leer sind.

Aber ich habe das Projekt schon wieder eingestampft, da es mir zu aufwendig ist, jeden Tag Daten einzutragen.

Danke fürs Lesen.

bei Antwort benachrichtigen