Excel vba berechnen gleitender durchschnitt




Excel vba berechnen gleitender durchschnittMoving Average Dieses Beispiel lehrt, wie Sie den gleitenden Durchschnitt einer Zeitreihe in Excel berechnen. Ein gleitender Durchschnitt wird verwendet, um Unregelma?igkeiten (Spitzen und Taler) zu glatten, um Trends leicht zu erkennen. 1. Erstens, werfen wir einen Blick auf unsere Zeitreihe. 2. Klicken Sie auf der Registerkarte Daten auf Datenanalyse. Hinweis: Klicken Sie hier, um das Analyse-ToolPak-Add-In zu laden. 3. Wahlen Sie Verschiebender Durchschnitt aus, und klicken Sie auf OK. 4. Klicken Sie im Feld Eingabebereich auf den Bereich B2: M2. 5. Klicken Sie in das Feld Intervall und geben Sie 6 ein. 6. Klicken Sie in das Feld Ausgabebereich und wahlen Sie Zelle B3 aus. 8. Zeichnen Sie ein Diagramm dieser Werte. Erlauterung: Da wir das Intervall auf 6 setzen, ist der gleitende Durchschnitt der Durchschnitt der vorherigen 5 Datenpunkte und der aktuelle Datenpunkt. Als Ergebnis werden Spitzen und Taler geglattet. Die Grafik zeigt eine zunehmende Tendenz. Excel kann den gleitenden Durchschnitt fur die ersten 5 Datenpunkte nicht berechnen, da nicht genugend fruhere Datenpunkte vorhanden sind. 9. Wiederholen Sie die Schritte 2 bis 8 fur Intervall 2 und Intervall 4. Fazit: Je gro?er das Intervall, desto mehr werden die Spitzen und Taler geglattet. Je kleiner das Intervall, desto naher sind die gleitenden Mittelwerte zu den tatsachlichen Datenpunkten. Rolling Average Table Im Folgenden werden wir ein Programm in Excel VBA betrachten, das eine gleitende Durchschnittstabelle erzeugt. Platzieren Sie eine Befehlsschaltflache auf Ihrem Arbeitsblatt und fugen Sie die folgende Codezeile hinzu: Bereich (quotB3quot).Value WorksheetFunction. RandBetween (0, 100) Diese Codezeile gibt eine Zufallszahl zwischen 0 und 100 in Zelle B3 ein. Wir wollen, dass Excel VBA den neuen Aktienwert annimmt und an die erste Position der rollenden Durchschnittstabelle setzt. Alle anderen Werte sollten sich um eine Stelle bewegen und der letzte Wert sollte geloscht werden. Erstellen Sie ein Worksheet-Anderungsereignis. Code, der dem Worksheet Change Event hinzugefugt wird, wird von Excel VBA ausgefuhrt, wenn Sie eine Zelle in einem Arbeitsblatt andern. 2. Doppelklicken Sie im Projekt-Explorer auf Sheet1 (Sheet1). 3. Wahlen Sie in der linken Dropdown-Liste Arbeitsblatt aus. Wahlen Sie Andern aus der rechten Dropdown-Liste. Fugen Sie dem Worksheet Change Event die folgenden Codezeilen hinzu: 4. Deklarieren Sie eine Variable namens newvalue vom Typ Integer und zwei Bereiche (firstfourvalues ??und lastfourvalues). Dim newvalue Als Integer. Firstfourvalues ??As Range, lastfourvalues ??As Range 5. Das Worksheet Change-Ereignis hort alle Anderungen auf Sheet1. Wir wollen nur Excel VBA, etwas zu tun, wenn etwas in Zelle B3 andert. Um dies zu erreichen, fugen Sie die folgende Codezeile hinzu: If Target. Address quotB3quot Then 6. Wir initialisieren newvalue mit dem Wert von Zelle B3, firstfourvalues ??mit Range (quotD3: D6quot) und lastfourvalues ??mit Range (quotD4: D7quot). Newvalue Bereich (quotB3quot).Value Set firstfourvalues ??Bereich (quotD3: D6quot) Set lastfourvalues ??Bereich (quotD4: D7quot) 7. Jetzt kommt der einfache Trick. Wir mochten die Rolling-Average-Tabelle aktualisieren. Sie konnen dies erreichen, indem Sie die letzten vier Werte durch die ersten vier Werte der Tabelle ersetzen und den neuen Aktienwert an die erste Position setzen. Lastfourvalues. Value firstfourvalues. Value Bereich (quotD3quot).Value newvalue 8. Vergessen Sie nicht, die if-Anweisung zu schlie?en. 9. Geben Sie abschlie?end die Formel AVERAGE (D3: D7) in die Zelle D8 ein. 10. Testen Sie das Programm, indem Sie auf die Befehlsschaltflache klicken. Ich mochte einen gleitenden Durchschnitt der letzten, sagen wir 20, Zahlen einer Spalte berechnen. Ein Problem ist, dass einige Zellen der Spalte leer sein konnen, sie ignoriert werden sollten. Beispiel: Ein gleitender Durchschnitt der letzten drei ware (155167201) 3. Ive versucht, dies zu implementieren mit Mittelwert, Offset, Index, aber ich wei? einfach nicht, wie. Im ein wenig vertraut mit Makros, so wurde eine solche Losung gut funktionieren: MovingAverage (A13) Vielen Dank fur alle Tipps oder Losungen Geben Sie dies mit Controlshiftenter, um es eine Matrix-Formel. Hier finden Sie die letzten drei Werte. Wenn Sie mehr oder weniger wollen, andern Sie die beiden Instanzen von 3 in der Formel, was auch immer Sie wollen. Dieses Teil gibt die 4. hochste Zeilennummer aller Zellen zuruck, die einen Wert haben, oder 5 in Ihrem Beispiel, da die Zeilen 6, 8 und 9 die 1. bis 3. hochsten Zeilen mit einem Wert sind. Dieser Teil gibt 9 TRUEs oder FALSEs zuruck, basierend darauf, ob die Zeilennummer gro?er als die 4. gro?te ist. Diese multipliziert die Werte in A1: A9 mit diesen 9 WAHREN oder FALSE. TRUEs werden in 1 und FALSEs auf null konvertiert. Dies lasst eine SUM-Funktion wie folgt Da alle Werte uber 155 nicht das Zeilennummerkriterium erfullen, wird das Get mit Null multipliziert. Ich habe ein kurzes Skript in VBA geschrieben. Hopefull es tut, was Sie wollen. Hier sind Sie: 1) Ich habe Limit auf 360 Zellen gesetzt. Das bedeutet, dass das Skript nicht mehr als 360 Zellen sucht. Wenn Sie sie andern mochten, andern Sie den Anfangswert des Zahlers. 2) Das Skript gibt nicht gerundeten Durchschnitt zuruck. Andern Sie die letzte Zeile in MovingAverage Round (CDbl (tmp i), 2) 3) Die Verwendung ist genau wie Sie wollten, so geben Sie einfach MovingAverage (a13) in die Zelle. Kommentare sind willkommen.