VBA Gjennomsnitt - GJENNOMSNITT, GJENNOMSNITT, GJENNOMSNITT

Denne opplæringen viser deg hvordan du bruker Excel Average -funksjonen i VBA.

Excel -gjennomsnittsfunksjonen brukes til å beregne et gjennomsnitt fra en cellecelle i regnearket som har verdier i dem. I VBA får du tilgang til den ved hjelp av metoden WorksheetFunction.

GJENNOMSNITT Arbeidsark Funksjon

WorksheetFunction -objektet kan brukes til å ringe de fleste Excel -funksjonene som er tilgjengelige i dialogboksen Sett inn funksjon i Excel. AVERAGE -funksjonen er en av dem.

123	Sub TestFunksjonOmråde ("D33") = Application.WorksheetFunction.Average ("D1: D32")Slutt Sub

Du kan ha opptil 30 argumenter i gjennomsnittsfunksjonen. Hvert av argumentene må referere til en rekke celler.

Dette eksemplet nedenfor vil produsere gjennomsnittet av summen av cellene B11 til N11

123	Deltest Gjennomsnitt ()Range ("O11") = Application.WorksheetFunction.Average (Range ("B11: N11"))Slutt Sub

Eksemplet nedenfor vil produsere et gjennomsnitt av summen av cellene i B11 til N11 og summen av cellene i B12: N12. Hvis du ikke skriver Application -objektet, antas det.

123	Deltest Gjennomsnitt ()Range ("O11") = WorksheetFunction.Average (Range ("B11: N11"), Range ("B12: N12"))Slutt Sub

Tilordne et GJENNOMSNITT resultat til en variabel

Det kan være lurt å bruke resultatet av formelen din andre steder i koden i stedet for å skrive det direkte tilbake til et Excel -område. Hvis dette er tilfelle, kan du tilordne resultatet til en variabel som skal brukes senere i koden.

1234567

Sub AssignAverage ()Dim resultat som heltall'Tilordne variabelenresult = WorksheetFunction.Average (Område ("A10: N10"))'Vis resultatetMsgBox "Gjennomsnittet for cellene i dette området er" og resultatSlutt Sub

GJENNOMSNITT med et rekkeviddeobjekt

Du kan tilordne en gruppe celler til Range -objektet, og deretter bruke det Range -objektet med Arbeidsark Funksjon gjenstand.

123456789

Sub TestAverageRange ()Dim rng As Range'tilordne celleområdetSett rng = Range ("G2: G7")'bruk området i formelenOmråde ("G8") = WorksheetFunction.Average (rng)'slipp rekkeviddeobjektetSett rng = ingentingSlutt Sub

GJENNOMSNITT Flere objekter i rekkevidde

På samme måte kan du beregne gjennomsnittet av cellene fra flere rekkeviddeobjekter.

123456789101112

Sub TestAverageMultipleRanges ()Dim rngA As RangeDim rngB som område'tilordne celleområdetSett rngA = Range ("D2: D10")Sett rngB = Range ("E2: E10")'bruk området i formelenOmråde ("E11") = WorksheetFunction.Average (rngA, rngB)'slipp rekkeviddeobjektetSett rngA = IngentingSett rngB = IngentingSlutt Sub

Bruker AVERAGEA

AVERAGEA -funksjonen skiller seg fra AVERAGE -funksjonen ved at den skaper et gjennomsnitt fra alle cellene i et område, selv om en av cellene har tekst i den - den erstatter teksten med en null og inkluderer den ved beregning av gjennomsnittet. GJENNOMSNITT -funksjonen ville ignorere den cellen og ikke ta den med i beregningen.

123	Deltest GjennomsnittA ()Område ("B8) = Application.WorksheetFunction.AverageA (Range (" A10: A11 "))Slutt Sub

I eksemplet nedenfor returnerer AVERAGE -funksjonen en annen verdi til AVERAGEA -funksjonen når beregningen brukes på cellene A10 til A11

Svaret for AVERAGEA -formelen er lavere enn GJENNOMSNITTEL -formelen ettersom den erstatter teksten i A11 med en null, og derfor gjennomsnitt over 13 verdier i stedet for de 12 verdiene som gjennomsnittet beregner over.

Bruker AVERAGEIF

AVERAGEIF -funksjonen lar deg gjennomsnittlig summen av et celleområde som oppfyller visse kriterier.

123	Del gjennomsnitt Hvis ()Range ("F31") = WorksheetFunction.AverageIf (Range ("F5: F30"), "Savings", Range ("G5: G30"))Slutt Sub

Fremgangsmåten ovenfor vil bare gjennomsnittliggjøre cellene i område G5: G30 der den tilsvarende cellen i kolonne F har ordet ‘Savings’ i den. Kriteriene du bruker må stå i anførselstegn.

Ulemper med regnearkfunksjon

Når du bruker Arbeidsark Funksjon For å gjennomsnittlig verdiene i et område i regnearket, returneres en statisk verdi, ikke en fleksibel formel. Dette betyr at når tallene i Excel endres, vil verdien som er returnert av Arbeidsark Funksjon vil ikke endre seg.

I eksemplet ovenfor har prosedyren TestAverage -prosedyren laget gjennomsnittet av B11: M11 og satt svaret i N11. Som du kan se i formellinjen, er dette resultatet en figur og ikke en formel.

Hvis noen av verdiene derfor endres i området (B11: M11), vil resultatene i N11 IKKE endring.

I stedet for å bruke Arbeidsark Funksjon. Gjennomsnitt, kan du bruke VBA til å bruke gjennomsnittsfunksjonen på en celle ved hjelp av Formel eller FormelR1C1 metoder.

Ved hjelp av formelmetoden

Formelmetoden lar deg peke spesifikt på et celleområde, for eksempel: B11: M11 som vist nedenfor.

123	Sub TestAverageFormula ()Område ("N11"). Formel = "= Gjennomsnitt (B11: M11)"Slutt Sub

Bruke FormulaR1C1 -metoden

FomulaR1C1 -metoden er mer fleksibel ved at den ikke begrenser deg til et angitt celleområde. Eksemplet nedenfor gir oss det samme svaret som det ovenfor.

123	Sub TestAverageFormula ()Område ("N11"). Formel = "= Gjennomsnitt (RC [-12]: RC [-1])"Slutt Sub

For å gjøre formelen mer fleksibel, kan vi imidlertid endre koden slik at den ser slik ut:

123	Sub TestCountFormula ()ActiveCell.FormulaR1C1 = "= Antall (R [-11] C: R [-1] C)"Slutt Sub

Uansett hvor du er i regnearket, vil formelen deretter gjennomsnittet verdiene i de 12 cellene direkte til venstre for det og plassere svaret i ActiveCell. Området inne i AVERAGE -funksjonen må refereres til ved bruk av rad (R) og kolonne (C) syntaks.

Begge disse metodene lar deg bruke dynamiske Excel -formler i VBA.

Det vil nå være en formel i N11 i stedet for en verdi.