Excel Vba Gleitende Mittelfunktion

Moving Average Dieses Beispiel lehrt, wie Sie den gleitenden Durchschnitt einer Zeitreihe in Excel berechnen. Ein gleitender Durchschnitt wird verwendet, um Unregelmäßigkeiten (Spitzen und Täler) zu glätten, 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. Wählen Sie Verschiebender Durchschnitt aus, und klicken Sie auf OK. 4. Klicken Sie in das Feld Eingabebereich und wählen Sie den Bereich B2: M2. 5. Klicken Sie in das Feld Intervall und geben Sie 6 ein. 6. Klicken Sie in das Feld Ausgabebereich und wählen Sie Zelle B3 aus. 8. Zeichnen Sie ein Diagramm dieser Werte. Erläuterung: Da wir das Intervall auf 6 setzen, ist der gleitende Durchschnitt der Durchschnitt der letzten 5 Datenpunkte und der aktuelle Datenpunkt. Als Ergebnis werden Spitzen und Täler geglättet. Die Grafik zeigt eine zunehmende Tendenz. Excel kann den gleitenden Durchschnitt für die ersten 5 Datenpunkte nicht berechnen, da nicht genügend frühere Datenpunkte vorhanden sind. 9. Wiederholen Sie die Schritte 2 bis 8 für Intervall 2 und Intervall 4. Fazit: Je größer das Intervall, desto mehr werden die Spitzen und Täler geglättet. Je kleiner das Intervall ist, desto näher sind die gleitenden Mittelwerte bis zu den eigentlichen Datenpunkten. Custom Average Funktion Im Folgenden werden wir ein Programm in Excel VBA anschauen, das eine benutzerdefinierte Funktion erstellt, die den Durchschnitt eines zufällig ausgewählten Bereichs ohne einen oder mehrere Werte berechnet Die Ausreißer sind und nicht gemittelt werden sollten. Benutzerdefinierte Funktionen müssen in einem Modul platziert werden. 1. Öffnen Sie den Visual Basic-Editor, und klicken Sie auf Einfügen, Modul. 2. Fügen Sie die folgende Codezeile hinzu: Funktion CUSTOMAVERAGE (rng Als Bereich, niedriger als Integer. Upper Als Integer) Der Name unserer Funktion ist CUSTOMAVERAGE. Der Teil zwischen den Klammern bedeutet, dass wir Excel VBA einen Bereich und zwei Integer-Variablen als Eingabe geben. Wir nennen unseren Bereich rng, eine Integer-Variable, die wir niedriger nennen, und eine Integer-Variable, die wir oben aufrufen, aber Sie können beliebige Namen verwenden. 3. Als nächstes deklarieren wir ein Range-Objekt und zwei Variablen vom Typ Integer. Wir rufen die Range-Objektzelle auf. Eine Integer-Variable nennen wir total und eine Integer-Variable, die wir count zählen. Dim-Zelle As Range, total As Integer. Count As Integer 4. Wir wollen jede Zelle in einem beliebig ausgewählten Bereich überprüfen (dieser Bereich kann beliebig groß sein). In Excel VBA können Sie dazu die For Each Next-Schleife verwenden. Fügen Sie die folgenden Codezeilen hinzu: Für jede Zelle In rng Hinweis: rng und Zelle sind zufällig hier ausgewählt, können Sie beliebige Namen verwenden. Denken Sie daran, diese Namen in den Rest des Codes verweisen. 5. Als nächstes prüfen wir für jeden Wert in diesem Bereich, ob er zwischen den beiden Werten (untere und obere) fällt. Wenn true, erhöhen wir die Gesamtzahl um den Wert der Zelle und wir erhöhen die Anzahl mit 1. Fügen Sie der Loop die folgenden Codezeilen hinzu. Wenn cell. Value gt niedriger und cell. Value lt upper Dann Gesamtzahl der cell. Value-Zähler 1 End If 6. Um das Ergebnis dieser Funktion (den gewünschten Durchschnitt) zurückzugeben, fügen Sie die folgende Codezeile außerhalb der Schleife hinzu. CUSTOMAVERAGE Gesamtzahl 7. Vergessen Sie nicht, die Funktion zu beenden. Fügen Sie die Zeile: 8. Nun können Sie diese Funktion wie jede andere Excel-Funktion verwenden, um den Mittelwert der Zahlen zu berechnen, die zwischen zwei Werten fallen. Als eine Prüfung können Sie alle Werte löschen, die kleiner als 10 und höher als 30 sind, und die Standard-Durchschnittsfunktion in Excel verwenden, um zu sehen, ob Excel denselben Durchschnitt berechnet wie unsere benutzerdefinierte durchschnittliche Funktion. Unsere benutzerdefinierte durchschnittliche Funktion funktioniert Anmerkung: diese Funktion ist nur in dieser Arbeitsmappe verfügbar. Ich möchte einen gleitenden Durchschnitt der letzten, sagen 20, Zahlen einer Spalte berechnen. Ein Problem ist, dass einige Zellen der Spalte leer sein können, sie sollten ignoriert werden. Beispiel: Ein gleitender Durchschnitt der letzten drei wäre (155167201) 3. Ive versucht, dies zu implementieren mit Mittelwert, Offset, Index, aber ich weiß einfach nicht, wie. Im ein wenig vertraut mit Makros, so würde eine solche Lösung gut funktionieren: MovingAverage (A13) Vielen Dank für alle Tipps oder Lösungen Geben Sie dies mit Controlshiftenter, um es eine Matrix-Formel. Hier finden Sie die letzten drei Werte. Wenn Sie mehr oder weniger wollen, ändern Sie die beiden Instanzen von 3 in der Formel, was auch immer Sie wollen. Dieser Teil gibt die 4. höchste Zeilennummer aller Zellen zurück, die einen Wert haben, oder 5 in Ihrem Beispiel, da die Zeilen 6, 8 und 9 die 1. bis 3. höchsten Zeilen mit einem Wert sind. Dieser Teil gibt 9 TRUEs oder FALSEs zurück, basierend darauf, ob die Zeilennummer größer als die 4. größ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 lässt eine SUM-Funktion wie folgt Weil alle Werte über 155 nicht das Zeilennummerkriterium erfüllen, 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 ändern möchten, ändern Sie den Anfangswert des Zählers. 2) Das Skript gibt nicht gerundeten Durchschnitt zurück. Ändern 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.