VBA-Mittelwert – AVERAGE, AVERAGEA, AVERAGEIF

In diesem Tutorial zeigen wir Ihnen, wie Sie die Excel-Mittelwertfunktion in VBA verwenden können.

Die Excel-Funktion MITTELWERT (Engl. AVERAGE) wird verwendet, um einen Mittelwert aus einem Zellenbereich, der Werte enthält, in Ihrem Arbeitsblatt zu berechnen. In VBA wird sie über die Methode WorksheetFunction aufgerufen.

AVERAGE – WorksheetFunction

Das WorksheetFunction-Objekt kann verwendet werden, um die meisten Excel-Funktionen aufzurufen, die im Dialogfenster „Funktion einfügen“ in Excel verfügbar sind. Die AVERAGE-Funktion ist eine von ihnen.

Sub TestFunktion()
  Range("O11") = Application.WorksheetFunction.Average("D1:D32")
End Sub

Sie können bis zu 30 Argumente in der AVERAGE-Funktion verwenden. Jedes der Argumente muss auf einen Zellenbereich verweisen.

Das folgende Beispiel ergibt den Mittelwert der Zellen B11 bis N11.

Sub MittelwertTest()
   Range("O11") = Application.WorksheetFunction.Average(Range("B11:N11"))
End Sub

Das folgende Beispiel ergibt den Gesamtmittelwert der Zellen B11 bis N11 und B12 bis N12. Wenn Sie das Anwendungsobjekt nicht eingeben, wird es angenommen.

Sub MittelwertTest()
  Range("O11") = WorksheetFunction.Average(Range("B11:N11"),Range("B12:N12")) 
End Sub

AVERAGE-Ergebnis einer Variablen zuweisen

Möglicherweise möchten Sie das Ergebnis Ihrer Formel an anderer Stelle im Code verwenden, anstatt es direkt in einen Excel-Bereich zurückzuschreiben. In diesem Fall können Sie das Ergebnis einer Variablen, die Sie später in Ihrem Code verwenden können, zuweisen.

Sub MittelwertZuweisen()
   Dim ergebnis As Integer
'Die Variable zuweisen   
   ergebnis = WorksheetFunction.Average(Range("A10:N10"))
'Das Ergebnis anzeigen
   MsgBox "Der Mittelwert der Zellen in diesem Bereich beträgt " & ergbnis
End Sub

 

AVERAGE mit einem Bereichsobjekt

Sie können eine Gruppe von Zellen dem Range-Objekt zuweisen und dieses dann mit dem WorksheetFunction-Objekt verwenden.

Sub MittelwertTest_EinBereich()
   Dim bereich As Range
'Den Zellenbereich zuweisen
   Set bereich = Range("G2:G7")
'Den Bereich in der Formel verwenden
   Range("G8") = WorksheetFunction.Average(bereich)
'Das Bereichsobjekt freigeben
  Set bereich = Nothing
End Sub

AVERAGE – Mehrere Range-Objekte

Auf ähnliche Weise können Sie den Mittelwert der Zellen aus mehreren Range-Objekten berechnen.

Sub MittelwertTest_MehrereBereiche() 
   Dim bereichA As Range 
   Dim bereichB as Range
'Den Zellenbereich zuweisen 
   Set bereichA = Range("D2:D10") 
   Set bereichB = Range("E2:E10")   
'Den Bereich in der Formel verwenden 
Range("E11") = WorksheetFunction.Average(bereichA, bereichB)
 'Das Bereichsobjekt freigeben
  Set bereichA = Nothing 
  Set bereichB = Nothing
End Sub

AVERAGEA verwenden

Die Funktion AVERAGEA unterscheidet sich von der Funktion AVERAGE darin, dass sie einen Mittelwert aus allen Zellen in einem Bereich bildet, auch wenn eine der Zellen Text enthält. Sie ersetzt den Text durch eine Null und berücksichtigt ihn bei der Berechnung des Mittelwerts. Die AVERAGE-Funktion würde diese Zelle ignorieren und sie nicht in die Berechnung einbeziehen.

Sub AverageA_Testen()
   Range("B8) = Application.WorksheetFunction.AverageA(Range("A10:A11")) 
End Sub

Im folgenden Beispiel gibt die AVERAGE-Funktion einen anderen Wert als die AVERAGEA-Funktion zurück, wenn die Berechnung auf die Zellen A10 bis A11 angewendet wird

Die Antwort der AVERAGEA-Formel ist niedriger als die von der AVERAGE-Formel, da sie den Text in A11 durch eine Null ersetzt und daher den Mittelwert über 13 Werte und nicht über die 12 Werte, über die die AVERAGE-Funktion rechnet, bildet.

Verwendung von AVERAGEIF

Mit der AVERAGEIF-Funktion können Sie den Mittelwert eines Bereichs von Zellen, die ein bestimmtes Kriterium erfüllen, bilden.

Sub MittelwertWenn()
   Range("F31") = WorksheetFunction.AverageIf(Range("F5:F30"), "Spareinlagen", Range("G5:G30"))
End Sub

Die obige Prozedur berechnet nur den Mittelwert der Zellen im Bereich G5:G30, wenn die entsprechende Zelle in Spalte F das Wort „Spareinlagen“ enthält. Das von Ihnen verwendete Kriterium muss in Anführungszeichen stehen.

 

Nachteile von WorksheetFunction

Wenn Sie WorksheetFunction verwenden, um den Mittelwert der Werte in einem Bereich Ihres Arbeitsblatts zu ermitteln, wird ein statischer Wert zurückgegeben und keine flexible Formel. Das bedeutet, dass sich der von der WorksheetFunction zurückgegebene Wert nicht ändert, wenn sich Ihre Zahlen in Excel ändern.

Im obigen Beispiel hat die Prozedur MittelwertTest den Mittelwert von B11:M11 erstellt und die Antwort in N11 abgelegt. Wie Sie in der Formelleiste sehen können, handelt es sich bei diesem Ergebnis um eine Zahl und nicht um eine Formel.

Wenn sich also einer der Werte im Bereich (B11:M11) ändert, wird sich das Ergebnis in N11 NICHT ändern.

Anstatt die WorksheetFunction.Average zu verwenden, können Sie VBA verwenden, um die AVERAGE-Funktion auf eine Zelle anzuwenden, indem Sie die Methoden Formula oder FormulaR1C1 verwenden.

Verwendung der Formelmethode

Mit der Formelmethode können Sie speziell auf einen Zellenbereich wie unten gezeigt verweisen (z. B. B11:M11).

Sub MittelwertFormelTest()
  Range("N11").Formula = "=Average(B11:M11)"
End Sub

 

Verwendung der FormulaR1C1-Methode

Die FomulaR1C1-Methode ist insofern flexibler, da sie Sie nicht auf einen bestimmten Zellenbereich beschränkt. Das folgende Beispiel liefert die gleiche Antwort wie das obige.

Sub MittelwertFormelTest()
   Range("N11").Formula = "=Average(RC[-12]:RC[-1])"
End Sub

Um die Formel jedoch flexibler zu gestalten, könnten wir den Code so ändern, dass er wie folgt aussieht:

Sub MittelwertFormelTest() 
   ActiveCell.FormulaR1C1 = "=Average(R[-11]C:R[-1]C)" 
End Sub

Es spielt keine Rolle, wo Sie sich in Ihrem Arbeitsblatt befinden, denn die Formel bildet anschließend den Mittelwert der Werte in den 12 Zellen direkt links von ihr und platziert die Antwort in Ihrer aktuellen Zelle (ActiveCell). Der Bereich innerhalb der AVERAGE-Funktion muss mit der Syntax Zeile (R) und Spalte (C) bezeichnet werden.

Mit diesen beiden Methoden können Sie dynamische Excel-Formeln in VBA verwenden.

In N11 befindet sich nun eine Formel anstelle eines Wertes.

Wichtiger Hinweis: Excel-Arbeitsblattfunktionen können nur unter Verwendung ihrer englischen Bezeichnungen im VBA-Code verwendet werden. Dies gilt auch bei der Anwendung der Methoden Formula und FormulaR1C1.