Dieses Tutorial zeigt Ihnen, wie Sie die Excel Average-Funktion in VBA verwenden.
Die Excel AVERAGE-Funktion wird verwendet, um einen Durchschnitt aus einem Bereich von Zellen in Ihrem Arbeitsblatt zu berechnen, die Werte enthalten. In VBA wird mit der WorksheetFunction-Methode darauf zugegriffen.
AVERAGE WorksheetFunction
Das WorksheetFunction-Objekt kann verwendet werden, um die meisten Excel-Funktionen aufzurufen, die im Dialogfeld Funktion einfügen in Excel verfügbar sind. Die AVERAGE-Funktion ist eine davon.
| 123 | UntertestfunktionRange("D33") = Application.WorksheetFunction.Average("D1:D32")End Sub |
Sie können bis zu 30 Argumente in der AVERAGE-Funktion haben. Jedes der Argumente muss sich auf einen Zellbereich beziehen.
Dieses Beispiel unten ergibt den Durchschnitt der Summe der Zellen B11 bis N11
| 123 | Unter TestDurchschnitt()Range("O11") = Application.WorksheetFunction.Average(Range("B11:N11"))End Sub |
Das folgende Beispiel ergibt einen Durchschnitt der Summe der Zellen in B11 bis N11 und der Summe der Zellen in B12:N12. Wenn Sie das Anwendungsobjekt nicht eingeben, wird es angenommen.
| 123 | Unter TestDurchschnitt()Range("O11") = WorksheetFunction.Average(Range("B11:N11"),Range("B12:N12"))End Sub |
Zuweisen eines MITTELWERT-Ergebnisses zu einer Variablen
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 zuweisen, die später in Ihrem Code verwendet wird.
| 1234567 | Sub AssignAverage()Dim-Ergebnis As Integer'Variable zuweisenresult = WorksheetFunction.Average(Range("A10:N10"))'Ergebnis anzeigenMsgBox "Der Durchschnitt für die Zellen in diesem Bereich ist " & ErgebnisEnd Sub |
MITTELWERT mit einem Bereichsobjekt
Sie können dem Range-Objekt eine Gruppe von Zellen zuweisen und dieses Range-Objekt dann mit dem ArbeitsblattFunktion Objekt.
| 123456789 | Sub TestAverageRange()Dim rng As Range'Zellbereich zuweisenSetze rng = Bereich("G2:G7")'benutze den Bereich in der FormelRange("G8") = WorksheetFunction.Average(rng)'Entferne das EntfernungsobjektSetze rng = NichtsEnd Sub |
DURCHSCHNITTLICHE Objekte mit mehreren Reichweiten
Ebenso können Sie den Durchschnitt der Zellen aus mehreren Bereichsobjekten berechnen.
| 123456789101112 | UntertestAverageMultipleRanges()Dim rngA als BereichDim rngB als Bereich'Zellbereich zuweisenSetze rngA = Range("D2:D10")Setze rngB = Range("E2:E10")'benutze den Bereich in der FormelRange("E11") = WorksheetFunction.Average(rngA, rngB)'Entferne das EntfernungsobjektSetze rngA = NichtsSetze rngB = NichtsEnd Sub |
Verwenden von AVERAGEA
Die AVERAGEA-Funktion unterscheidet sich von der AVERAGE-Funktion dadurch, dass sie einen Durchschnitt aus allen Zellen in einem Bereich erstellt, auch wenn eine der Zellen Text enthält - sie ersetzt den Text durch eine Null und berücksichtigt diesen bei der Berechnung des Durchschnitts. Die Funktion MITTELWERT würde diese Zelle ignorieren und nicht in die Berechnung einbeziehen.
| 123 | Unter TestDurchschnittA()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 für die Zellen A10 bis A11 verwendet wird
Die Antwort für die AVERAGEA-Formel ist niedriger als die AVERAGE-Formel, da sie den Text in A11 durch eine Null ersetzt und daher über 13 Werte anstatt über die 12 Werte, über die der AVERAGE berechnet, mittelt.
Verwenden von AVERAGEIF
Die AVERAGEIF-Funktion ermöglicht es Ihnen, die Summe eines Bereichs von Zellen zu mitteln, die ein bestimmtes Kriterium erfüllen.
| 123 | Unter DurchschnittIf()Range("F31") = WorksheetFunction.AverageIf(Range("F5:F30"), "Savings", Range("G5:G30"))End Sub |
Das obige Verfahren berechnet nur den Durchschnitt der Zellen im Bereich G5:G30, wobei die entsprechende Zelle in Spalte F das Wort "Einsparungen" enthält. Die von Ihnen verwendeten Kriterien müssen in Anführungszeichen stehen.
Nachteile von WorksheetFunction
Wenn Sie das verwenden ArbeitsblattFunktion Um die Werte in einem Bereich in Ihrem Arbeitsblatt zu mitteln, wird ein statischer Wert zurückgegeben, keine flexible Formel. Das bedeutet, wenn sich Ihre Zahlen in Excel ändern, wird der Wert, der von der ArbeitsblattFunktion wird sich nicht ändern.
Im obigen Beispiel hat die Prozedur TestAverage den Durchschnitt von B11:M11 gebildet und die Antwort in N11 eingegeben. Wie Sie in der Bearbeitungsleiste sehen können, handelt es sich bei diesem Ergebnis um eine Zahl und nicht um eine Formel.
Wenn sich daher einer der Werte im Bereich (B11:M11) ändert, werden die Ergebnisse in N11 NICHT Veränderung.
Anstatt die ArbeitsblattFunktion.Durchschnitt, können Sie VBA verwenden, um die AVERAGE-Funktion auf eine Zelle anzuwenden, indem Sie die Formel oder FormelR1C1 Methoden.
Verwenden der Formelmethode
Mit der Formelmethode können Sie gezielt auf einen Zellbereich zeigen, zB: B11:M11, wie unten gezeigt.
| 123 | Unter TestDurchschnittsFormel()Range("N11").Formula = "=Durchschnitt(B11:M11)"End Sub |
Verwenden der FormelR1C1-Methode
Die FomulaR1C1-Methode ist flexibler, da sie Sie nicht auf einen bestimmten Zellbereich einschränkt. Das folgende Beispiel gibt uns die gleiche Antwort wie das obige.
| 123 | Unter TestDurchschnittsFormel()Range("N11").Formula = "=Durchschnitt(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:
| 123 | Unter TestCountFormula()ActiveCell.FormulaR1C1 = "=Anzahl(R[-11]C:R[-1]C)"End Sub |
Wo auch immer Sie sich in Ihrem Arbeitsblatt befinden, die Formel mittelt dann die Werte in den 12 Zellen direkt links davon und platziert die Antwort in Ihrer ActiveCell. Auf den Bereich innerhalb der AVERAGE-Funktion muss mit der Zeilen- (R) und Spalte (C)-Syntax verwiesen werden.
Mit beiden Methoden können Sie dynamische Excel-Formeln in VBA verwenden.
In N11 gibt es jetzt eine Formel anstelle eines Wertes.
