Deze zelfstudie laat u zien hoe u de Excel-gemiddelde-functie in VBA gebruikt.
De Excel GEMIDDELDE functie wordt gebruikt om een gemiddelde te berekenen uit een bereikcellen in uw werkblad die waarden bevatten. In VBA is het toegankelijk via de methode WorksheetFunction.
GEMIDDELDE werkbladfunctie
Het object WorksheetFunction kan worden gebruikt om de meeste Excel-functies aan te roepen die beschikbaar zijn in het dialoogvenster Functie invoegen in Excel. De GEMIDDELDE functie is er een van.
| 123 | SubtestfunctieRange("D33") = Application.WorksheetFunction.Average("D1:D32")Einde sub |
U kunt maximaal 30 argumenten hebben in de functie GEMIDDELDE. Elk van de argumenten moet verwijzen naar een celbereik.
Dit voorbeeld hieronder geeft het gemiddelde van de som van de cellen B11 tot N11
| 123 | Subtestgemiddelde()Range("O11") = Application.WorksheetFunction.Average(Range("B11:N11"))Einde sub |
Het onderstaande voorbeeld levert een gemiddelde op van de som van de cellen in B11 tot N11 en de som van de cellen in B12:N12. Als u het Application-object niet typt, wordt ervan uitgegaan.
| 123 | Subtestgemiddelde()Range("O11") = WerkbladFunctie.Average(Bereik("B11:N11"),Bereik ("B12:N12"))Einde sub |
Een GEMIDDELDE resultaat toewijzen aan een variabele
Misschien wilt u het resultaat van uw formule ergens anders in de code gebruiken in plaats van het rechtstreeks terug te schrijven naar een Excel-bereik. Als dit het geval is, kunt u het resultaat toewijzen aan een variabele om later in uw code te gebruiken.
| 1234567 | Sub AssignAverage()Dim resultaat Als geheel getal'Wijs de variabele toe'resultaat = WerkbladFunctie.Average(Bereik("A10:N10"))'Toon het resultaat'MsgBox "Het gemiddelde van de cellen in dit bereik is " & resultaatEinde sub |
GEMIDDELDE met een bereikobject
U kunt een groep cellen aan het Range-object toewijzen en dat Range-object vervolgens gebruiken met de WerkbladFunctie object.
| 123456789 | Sub TestAverageRange()Dim rng als bereik'het bereik van cellen toewijzen'Stel rng = Bereik ("G2:G7") in'gebruik het bereik in de formule'Bereik ("G8") = Werkbladfunctie. Gemiddelde (rng)'laat het bereikobject los'Stel rng = Niets inEinde sub |
GEMIDDELDE Objecten met meerdere afstanden
Op dezelfde manier kunt u het gemiddelde van de cellen uit meerdere bereikobjecten berekenen.
| 123456789101112 | SubtestAverageMultipleRanges()Dim rngA als bereikDim rngB als bereik'het bereik van cellen toewijzen'Stel rngA = Bereik ("D2:D10") inStel rngB = Bereik ("E2:E10") in'gebruik het bereik in de formule'Bereik ("E11") = Werkbladfunctie.Gemiddeld (rngA, rngB)'laat het bereikobject los'Stel rngA = Niets inStel rngB = Niets inEinde sub |
GEMIDDELDEA gebruiken
De functie GEMIDDELDEA verschilt van de functie GEMIDDELDE doordat deze een gemiddelde maakt van alle cellen in een bereik, zelfs als een van de cellen tekst bevat - deze vervangt de tekst door een nul en neemt die op bij het berekenen van het gemiddelde. De functie GEMIDDELDE negeert die cel en houdt er geen rekening mee in de berekening.
| 123 | Sub TestGemiddeldeA()Range("B8) = Application.WorksheetFunction.AverageA(Range("A10:A11"))Einde sub |
In het onderstaande voorbeeld retourneert de functie GEMIDDELDE een andere waarde naar de functie GEMIDDELDEA wanneer de berekening wordt gebruikt op de cellen A10 tot A11
Het antwoord voor de AVERAGEA-formule is lager dan de AVERAGE-formule, omdat het de tekst in A11 vervangt door een nul, en daarom gemiddeld over 13 waarden in plaats van de 12 waarden waarover de AVERAGE berekent.
AVERAGEIF . gebruiken
Met de functie AVERAGEIF kunt u het gemiddelde nemen van de som van een reeks cellen die aan bepaalde criteria voldoen.
| 123 | SubgemiddeldeAls()Range("F31") = WorksheetFunction.AverageIf(Range("F5:F30"), "Savings", Range("G5:G30"))Einde sub |
De bovenstaande procedure zal alleen het gemiddelde nemen van de cellen in het bereik G5:G30 waar de corresponderende cel in kolom F het woord 'Besparingen' bevat. De criteria die u gebruikt, moeten tussen aanhalingstekens staan.
Nadelen van werkbladfunctie
Wanneer u de WerkbladFunctie om de waarden in een bereik in uw werkblad te middelen, wordt een statische waarde geretourneerd, geen flexibele formule. Dit betekent dat wanneer uw cijfers in Excel veranderen, de waarde die is geretourneerd door de WerkbladFunctie zal niet veranderen.
In bovenstaand voorbeeld heeft de procedure TestAverage procedure het gemiddelde van B11:M11 gecreëerd en het antwoord in N11 gezet. Zoals u in de formulebalk kunt zien, is dit resultaat een cijfer en geen formule.
Als een van de waarden daarom verandert in het bereik (B11:M11 ), zullen de resultaten in N11 NIET verandering.
In plaats van de WerkbladFunctie.Gemiddeld, kunt u VBA gebruiken om de GEMIDDELDE functie op een cel toe te passen met behulp van de Formule of FormuleR1C1 methoden.
De formulemethode gebruiken
Met de formulemethode kunt u specifiek naar een celbereik verwijzen, bijvoorbeeld: B11:M11, zoals hieronder weergegeven.
| 123 | Sub TestAverageFormula()Bereik ("N11").Formule = "=Gemiddelde(B11:M11)"Einde sub |
De formuleR1C1-methode gebruiken
De FomulaR1C1-methode is flexibeler omdat deze u niet beperkt tot een vast bereik van cellen. Het onderstaande voorbeeld geeft ons hetzelfde antwoord als het bovenstaande.
| 123 | Sub TestAverageFormula()Bereik ("N11").Formule = "=Gemiddelde(RC[-12]:RC[-1])"Einde sub |
Om de formule echter flexibeler te maken, kunnen we de code aanpassen om er als volgt uit te zien:
| 123 | Sub TestCountFormula()ActiveCell.FormulaR1C1 = "=Aantal(R[-11]C:R[-1]C)"Einde sub |
Waar u ook bent in uw werkblad, de formule zal dan het gemiddelde nemen van de waarden in de 12 cellen direct links ervan en het antwoord in uw ActiveCell plaatsen. Er moet naar het bereik binnen de functie GEMIDDELDE worden verwezen met behulp van de syntaxis Rij (R) en Kolom (C).
Met beide methoden kunt u dynamische Excel-formules gebruiken binnen VBA.
Er komt nu een formule in N11 in plaats van een waarde.
