Ovaj će vam vodič pokazati kako koristiti funkciju Excel prosjek u VBA -i.
Excel PROSJEČNA funkcija koristi se za izračun prosjeka iz ćelija raspona na vašem radnom listu koje imaju vrijednosti. U VBA -u pristupa mu se pomoću metode WorksheetFunction.
PROSJEČNA Funkcija radnog lista
Objekt WorksheetFunction može se koristiti za pozivanje većine Excel funkcija koje su dostupne u dijaloškom okviru Umetni funkciju u Excelu. Funkcija PROSJEK je jedna od njih.
| 123 | Funkcija podtestaRaspon ("D33") = Application.WorksheetFunction.Average ("D1: D32")Kraj podm |
U funkciji PROSJEČNO možete imati do 30 argumenata. Svaki od argumenata mora se odnositi na raspon ćelija.
Ovaj donji primjer će proizvesti prosjek zbroja ćelija B11 do N11
| 123 | Podprosječni prosjek ()Raspon ("O11") = Application.WorksheetFunction.Average (Raspon ("B11: N11"))Kraj podm |
Primjer u nastavku proizvest će prosjek zbroja ćelija u B11 do N11 i zbroj ćelija u B12: N12. Ako ne upišete objekt Application, to će se pretpostaviti.
| 123 | Prosjek podtesta ()Raspon ("O11") = Funkcija radnog lista. Prosjek (Raspon ("B11: N11"), Raspon ("B12: N12"))Kraj podm |
Dodjeljivanje PROSJEČNOG rezultata varijabli
Možda ćete htjeti koristiti rezultat svoje formule na drugom mjestu u kodu, a ne pisati ga izravno u raspon programa Excel. U tom slučaju rezultat možete dodijeliti varijabli koja će se kasnije koristiti u vašem kodu.
| 1234567 | Pododredi prosjek ()Zatamni rezultat kao cijeli broj'Dodijelite varijabluresult = WorksheetFunction.Average (Raspon ("A10: N10"))'Pokaži rezultatMsgBox "Prosjek stanica u ovom rasponu je" & rezultatKraj podm |
PROSJEČNO s objektom raspona
Objektu Range možete dodijeliti skupinu ćelija, a zatim koristiti taj objekt Range s Funkcija radnog lista objekt.
| 123456789 | Sub TestAverageRange ()Dim rng As Range'dodijeliti raspon ćelijaPostavite rng = raspon ("G2: G7")'upotrijebite raspon u formuliRaspon ("G8") = Funkcija radnog lista. Prosjek (rng)'otpustite objekt rasponaPostavite rng = NištaKraj podm |
PROSJEČNO Više objekata raspona
Slično, možete izračunati prosjek ćelija iz više objekata raspona.
| 123456789101112 | PodispitSrednjiMultipleRanges ()Dim rngA As RangeDim rngB kao raspon'dodijeliti raspon ćelijaPostavite rngA = Raspon ("D2: D10")Postavite rngB = Raspon ("E2: E10")'upotrijebite raspon u formuliRaspon ("E11") = Funkcija radnog lista. Prosjek (rngA, rngB)'otpustite objekt rasponaPostavite rngA = NištaPostavite rngB = NištaKraj podm |
Korištenje AVERAGEA -e
Funkcija AVERAGEA razlikuje se od funkcije AVERAGE po tome što stvara prosjek iz svih ćelija u rasponu, čak i ako jedna od ćelija ima tekst u sebi - zamjenjuje tekst nulom i uključuje to u izračun prosjeka. Funkcija AVERAGE zanemarila bi tu ćeliju i ne bi je uključila u izračun.
| 123 | Prosjek podtestaA ()Raspon ("B8) = Application.WorksheetFunction.AverageA (Raspon (" A10: A11 "))Kraj podm |
U donjem primjeru funkcija AVERAGE vraća različitu vrijednost funkciji AVERAGEA kada se izračun koristi na ćelijama A10 do A11
Odgovor za formulu AVERAGEA niži je od formule AVERAGE jer zamjenjuje tekst u A11 nulom, pa stoga daje prosjek preko 13 vrijednosti, a ne 12 vrijednosti za koje AVERAGE izračunava.
Korištenje AVERAGEIF -a
Funkcija AVERAGEIF omogućuje vam da procijenite zbroj raspona ćelija koji zadovoljavaju određene kriterije.
| 123 | PodprosjekIf ()Raspon ("F31") = Funkcija radnog lista.AverageIf (Raspon ("F5: F30"), "Ušteda", Raspon ("G5: G30"))Kraj podm |
Gore navedeni postupak samo će dati prosjek ćelija u rasponu G5: G30 gdje odgovarajuća ćelija u stupcu F sadrži riječ 'Ušteda'. Kriteriji koje koristite moraju biti pod navodnicima.
Nedostaci rada radnog lista
Kada koristite Funkcija radnog lista za prosječne vrijednosti u rasponu na vašem radnom listu, vraća se statička vrijednost, a ne fleksibilna formula. To znači da kada se vaše brojke u Excelu promijene, vrijednost koju je vratio Funkcija radnog lista neće se promijeniti.
U gornjem primjeru, postupak TestAverage procedure stvorio je prosjek B11: M11 i stavio odgovor u N11. Kao što možete vidjeti na traci s formulama, ovaj rezultat je brojka, a ne formula.
Ako se neka od vrijednosti promijeni u rasponu (B11: M11), rezultati u N11 će se promijeniti NE promijeniti.
Umjesto da koristite Funkcija radnog lista.Prosjek, možete upotrijebiti VBA za primjenu PROSJEČNE funkcije na ćeliju pomoću Formula ili Formula R1C1 metodama.
Korištenjem metode formule
Metoda formule omogućuje vam da posebno pokažete na niz ćelija, na primjer: B11: M11 kao što je prikazano u nastavku.
| 123 | Pod TestAverageFormula ()Raspon ("N11"). Formula = "= Prosjek (B11: M11)"Kraj podm |
Korištenjem metode FormulaR1C1
Metoda FomulaR1C1 fleksibilnija je po tome što vas ne ograničava na zadani raspon ćelija. Primjer u nastavku dat će nam isti odgovor kao i prethodni.
| 123 | Pod TestAverageFormula ()Raspon ("N11"). Formula = "= Prosjek (RC [-12]: RC [-1])"Kraj podm |
Međutim, kako bismo formulu učinili fleksibilnijom, kôd bismo mogli promijeniti tako da izgleda ovako:
| 123 | Pod TestCountFormula ()ActiveCell.FormulaR1C1 = "= Broj (R [-11] C: R [-1] C)"Kraj podm |
Gdje god se nalazili na svom radnom listu, formula će zatim prosječiti vrijednosti u 12 ćelija izravno lijevo od njega i smjestiti odgovor u vašu ActiveCell. Raspon unutar funkcije AVERAGE mora se pozvati pomoću sintakse Redak (R) i Stupac (C).
Obje ove metode omogućuju vam korištenje Dynamic Excel formula unutar VBA.
U N11 sada će umjesto vrijednosti biti formula.
