Ta vadnica vam bo pokazala, kako uporabljati funkcijo Excel Average v VBA.
ExcelOVA POVPREČNA funkcija se uporablja za izračun povprečja iz celic obsega na vašem delovnem listu, ki vsebujejo vrednosti. V VBA do njega dostopate z metodo WorksheetFunction.
POVPREČNA Funkcija delovnega lista
Objekt WorksheetFunction lahko uporabite za klic večine Excelovih funkcij, ki so na voljo v pogovornem oknu Vstavi funkcijo v Excelu. Ena izmed njih je funkcija AVERAGE.
| 123 | Sub TestFunctionObseg ("D33") = Application.WorksheetFunction.Average ("D1: D32")End Sub |
V funkciji AVERAGE imate lahko do 30 argumentov. Vsak od argumentov se mora sklicevati na obseg celic.
Ta spodnji primer bo povzročil povprečje vsote celic B11 do N11
| 123 | Sub TestAverage ()Obseg ("O11") = Application.WorksheetFunction.Average (Range ("B11: N11"))End Sub |
Spodnji primer bo povzročil povprečje vsote celic v B11 do N11 in vsoto celic v B12: N12. Če ne vnesete predmeta aplikacije, bo predpostavljeno.
| 123 | Sub TestAverage ()Obseg ("O11") = Funkcija delovnega lista. Povprečje (Razpon ("B11: N11"), Obseg ("B12: N12"))End Sub |
Dodelitev povprečnega rezultata spremenljivki
Rezultat svoje formule boste morda želeli uporabiti drugje v kodi, namesto da bi ga zapisali neposredno v obseg Excel. V tem primeru lahko rezultat dodelite spremenljivki, ki jo boste uporabili pozneje v kodi.
| 1234567 | Sub AssignAverage ()Zatemni rezultat kot celo število'Dodelite spremenljivkoresult = WorksheetFunction.Average (obseg ("A10: N10"))'Pokaži rezultatMsgBox "Povprečje celic v tem območju je" & rezultatEnd Sub |
POVPREČNO z objektom obsega
Objektu Range lahko dodelite skupino celic, nato pa ta predmet Range uporabite z Funkcija delovnega lista predmet.
| 123456789 | Sub TestAverageRange ()Dim rng As Range'dodelite obseg celicNastavi rng = območje ("G2: G7")"uporabite obseg v formuliRazpon ("G8") = Funkcija delovnega lista. Povprečje (rng)'spustite predmet obsegaNastavi rng = NičEnd Sub |
POVPREČNO Več objektov obsega
Podobno lahko izračunate povprečje celic iz več objektov obsega.
| 123456789101112 | Sub TestAverageMultipleRanges ()Dim rngA As RangeZatemni rngB kot obseg'dodelite obseg celicNastavite rngA = območje ("D2: D10")Nastavi rngB = Razpon ("E2: E10")"uporabite obseg v formuliRazpon ("E11") = Funkcija delovnega lista. Povprečje (rngA, rngB)'spustite predmet obsegaNastavi rngA = NičNastavi rngB = NičEnd Sub |
Uporaba zdravila AVERAGEA
Funkcija AVERAGEA se od funkcije AVERAGE razlikuje po tem, da ustvari povprečje iz vseh celic v obsegu, tudi če ima ena od celic besedilo - besedilo nadomesti z ničlo in to vključi v izračun povprečja. Funkcija AVERAGE bi to celico prezrla in je ne bi upoštevala pri izračunu.
| 123 | Podvprečni povprečjeA ()Obseg ("B8) = Application.WorksheetFunction.AverageA (Range (" A10: A11 "))End Sub |
V spodnjem primeru funkcija AVERAGE vrne drugačno vrednost funkciji AVERAGEA, če se izračun uporablja za celice A10 do A11
Odgovor za formulo AVERAGEA je nižji od formule AVERAGE, saj besedilo v A11 nadomesti z ničlo, zato je povprečje več kot 13 vrednosti in ne 12 vrednosti, ki jih povprečje izračuna.
Uporaba AVERAGEIF
Funkcija AVERAGEIF vam omogoča povprečenje vsote obsega celic, ki izpolnjujejo določena merila.
| 123 | PodpovprečjeIf ()Obseg ("F31") = Funkcija delovnega lista.AverageIf (Razpon ("F5: F30"), "Prihranki", Obseg ("G5: G30"))End Sub |
Zgornji postopek bo povprečil le celice v območju G5: G30, kjer ima ustrezna celica v stolpcu F besedo "prihranki". Merila, ki jih uporabljate, morajo biti v narekovajih.
Slabosti delovanja delovnega lista
Ko uporabljate Funkcija delovnega lista za povprečje vrednosti v razponu na vašem delovnem listu se vrne statična vrednost, ne prilagodljiva formula. To pomeni, da ko se vaše številke v Excelu spremenijo, vrednost, ki jo je vrnil Funkcija delovnega lista se ne bo spremenilo.
V zgornjem primeru je postopek TestAverage postopek ustvaril povprečje B11: M11 in odgovor postavil v N11. Kot lahko vidite v vrstici s formulami, je ta rezultat številka in ne formula.
Če se torej katera koli vrednost spremeni v območju (B11: M11), se bodo rezultati v N11 spremenili NE spremeniti.
Namesto da uporabite Delovni list Funkcija. Povprečje, lahko z VBA uporabite funkcijo POVPREČNO za celico s pomočjo Formula ali Formula R1C1 metode.
Z uporabo metode formule
Metoda formule vam omogoča, da posebej pokažete na vrsto celic, na primer: B11: M11, kot je prikazano spodaj.
| 123 | Sub TestAverageFormula ()Razpon ("N11"). Formula = "= povprečje (B11: M11)"End Sub |
Z uporabo metode FormulaR1C1
Metoda FomulaR1C1 je bolj prilagodljiva, saj vas ne omejuje na določeno območje celic. Spodnji primer nam bo dal enak odgovor kot zgornji.
| 123 | Sub TestAverageFormula ()Razpon ("N11"). Formula = "= povprečje (RC [-12]: RC [-1])"End Sub |
Da pa bi bila formula bolj prilagodljiva, bi lahko kodo spremenili takole:
| 123 | Sub TestCountFormula ()ActiveCell.FormulaR1C1 = "= Štetje (R [-11] C: R [-1] C)"End Sub |
Kjer koli ste na svojem delovnem listu, bo formula nato povprečila vrednosti v 12 celicah neposredno levo od njega in odgovor postavila v vašo ActiveCell. Na obseg znotraj funkcije AVERAGE se je treba sklicevati s sintakso vrstice (R) in stolpca (C).
Obe metodi vam omogočata uporabo formul Dynamic Excel v VBA.
Namesto vrednosti bo zdaj v N11 formula.
