Denne vejledning viser dig, hvordan du bruger Excel -gennemsnitsfunktionen i VBA.
Funktionen GENNEMSNIT i Excel bruges til at beregne et gennemsnit fra en række celler i dit regneark, der har værdier i sig. I VBA åbnes den ved hjælp af metoden WorksheetFunction.
GENNEMSNIT Arbejdsark Funktion
WorksheetFunction -objektet kan bruges til at kalde de fleste Excel -funktioner, der er tilgængelige i dialogboksen Indsæt funktion i Excel. GENNEMSNIT -funktionen er en af dem.
| 123 | Sub TestFunktionOmråde ("D33") = Application.WorksheetFunction.Average ("D1: D32")Afslut Sub |
Du kan have op til 30 argumenter i GENNEMSNIT -funktionen. Hvert af argumenterne skal referere til en række celler.
Dette eksempel nedenfor vil producere gennemsnittet af summen af cellerne B11 til N11
| 123 | SubtestGennemsnit ()Range ("O11") = Application.WorksheetFunction.Average (Range ("B11: N11"))Afslut Sub |
Nedenstående eksempel vil producere et gennemsnit af summen af cellerne i B11 til N11 og summen af cellerne i B12: N12. Hvis du ikke skriver Application -objektet, antages det.
| 123 | SubtestGennemsnit ()Område ("O11") = ArbejdsarkFunktion.Gennemsnit (Område ("B11: N11"), Område ("B12: N12"))Afslut Sub |
Tildeling af et GENNEMSNIT -resultat til en variabel
Du vil måske bruge resultatet af din formel andre steder i koden frem for at skrive det direkte tilbage til et Excel -område. Hvis dette er tilfældet, kan du tildele resultatet til en variabel, der skal bruges senere i din kode.
| 1234567 | Sub AssignAverage ()Dim resultat Som heltal'Tildel variablenresult = WorksheetFunction.Average (Område ("A10: N10"))'Vis resultatetMsgBox "Gennemsnittet for cellerne i dette område er" & resultatAfslut Sub |
GENNEMSNIT med et rækkeviddeobjekt
Du kan tildele en gruppe celler til Range -objektet og derefter bruge dette Range -objekt med ArbejdsarkFunktion objekt.
| 123456789 | Sub TestAverageRange ()Dim rng Som område'tildele celleområdetIndstil rng = Range ("G2: G7")'brug området i formlenOmråde ("G8") = WorksheetFunction.Average (rng)'frigiv rækkeviddeobjektetIndstil rng = IntetAfslut Sub |
GENNEMSNIT Flere objekter i rækkevidde
På samme måde kan du beregne gennemsnittet af cellerne fra flere områdeobjekter.
| 123456789101112 | Sub TestAverageMultipleRanges ()Dim rngA As RangeDim rngB som område'tildele celleområdetIndstil rngA = Range ("D2: D10")Indstil rngB = Range ("E2: E10")'brug området i formlenOmråde ("E11") = WorksheetFunction.Average (rngA, rngB)'frigiv rækkeviddeobjektetIndstil rngA = IntetIndstil rngB = IntetAfslut Sub |
Brug af AVERAGEA
AVERAGEA -funktionen adskiller sig fra AVERAGE -funktionen ved, at den skaber et gennemsnit fra alle cellerne i et område, selvom en af cellerne har tekst i den - den erstatter teksten med et nul og inkluderer det ved beregning af gennemsnittet. GENNEMSNIT -funktionen ignorerer denne celle og indregner den ikke i beregningen.
| 123 | SubtestGennemsnitA ()Område ("B8) = Application.WorksheetFunction.AverageA (Range (" A10: A11 "))Afslut Sub |
I eksemplet herunder returnerer AVERAGE -funktionen en anden værdi til AVERAGEA -funktionen, når beregningen bruges på celler A10 til A11
Svaret på AVERAGEA -formlen er lavere end GENNEMSNIT -formlen, da den erstatter teksten i A11 med et nul, og derfor i gennemsnit over 13 værdier frem for de 12 værdier, Gennemsnittet beregner over.
Brug AVERAGEIF
Med AVERAGEIF -funktionen kan du gennemsnitlige summen af en række celler, der opfylder et bestemt kriterium.
| 123 | UndergennemsnitIf ()Range ("F31") = WorksheetFunction.AverageIf (Range ("F5: F30"), "Savings", Range ("G5: G30"))Afslut Sub |
Fremgangsmåden ovenfor vil kun gennemsnitlige cellerne i område G5: G30, hvor den tilsvarende celle i kolonne F har ordet 'Besparelser' i den. De kriterier, du bruger, skal stå i anførselstegn.
Ulemper ved regnearksfunktion
Når du bruger ArbejdsarkFunktion for at gennemsnitlige værdierne i et område i dit regneark returneres en statisk værdi, ikke en fleksibel formel. Det betyder, at når dine tal i Excel ændres, den værdi, der er returneret af ArbejdsarkFunktion vil ikke ændre sig.
I ovenstående eksempel har proceduren TestAverage -procedure skabt gennemsnittet af B11: M11 og sat svaret i N11. Som du kan se i formellinjen, er dette resultat en figur og ikke en formel.
Hvis nogen af værdierne derfor ændrer sig i området (B11: M11), vil resultaterne i N11 IKKE lave om.
I stedet for at bruge ArbejdsarkFunktion.Gennemsnitlig, kan du bruge VBA til at anvende GENNEMGANGSFunktionen på en celle ved hjælp af Formel eller FormelR1C1 metoder.
Ved hjælp af formelmetoden
Formelmetoden giver dig mulighed for at pege specifikt på en række celler, f.eks .: B11: M11 som vist nedenfor.
| 123 | Sub TestAverageFormula ()Område ("N11"). Formel = "= Gennemsnit (B11: M11)"Afslut Sub |
Brug af FormulaR1C1 -metoden
FomulaR1C1 -metoden er mere fleksibel, fordi den ikke begrænser dig til et bestemt antal celler. Nedenstående eksempel vil give os det samme svar som ovenstående.
| 123 | Sub TestAverageFormula ()Område ("N11"). Formel = "= Gennemsnit (RC [-12]: RC [-1])"Afslut Sub |
For at gøre formlen mere fleksibel kunne vi dog ændre koden til at se sådan ud:
| 123 | Sub TestCountFormula ()ActiveCell.FormulaR1C1 = "= Antal (R [-11] C: R [-1] C)"Afslut Sub |
Uanset hvor du er i dit regneark, vil formlen derefter gennemsnitsværdierne i de 12 celler direkte til venstre for det og placere svaret i din ActiveCell. Området inden for AVERAGE -funktionen skal refereres til ved hjælp af rækken (R) og kolonnen (C) syntaks.
Begge disse metoder giver dig mulighed for at bruge dynamiske Excel -formler inden for VBA.
Der vil nu være en formel i N11 i stedet for en værdi.
