See õpetus näitab teile, kuidas kasutada VBA Exceli keskmist funktsiooni.
Funktsiooni Excel AVERAGE kasutatakse keskmise arvutamiseks teie töölehe vahemiku lahtritest, millel on väärtused. VBA on külastatud kasutades WorksheetFunction meetod.
KESKMINE töölehtFunktsioon
Objekti WorksheetFunction abil saab helistada enamikule Exceli funktsioonidele, mis on saadaval Exceli dialoogiboksis Lisa funktsioon. Funktsioon KESKMINE on üks neist.
| 123 | AlamkatsefunktsioonVahemik ("D33") = Application.WorksheetFunction.Average ("D1: D32")End Sub |
Sul on võimalus olla kuni 30 argumente keskmise funktsiooni. Iga argumendid tuleb viidata lahtrite.
See allpool toodud näide annab lahtrite B11 kuni N11 summa keskmise
| 123 | Alamkatse keskmine ()Vahemik ("O11") = Application.WorksheetFunction.Average (Vahemik ("B11: N11"))End Sub |
Allolev näide annab keskmiselt lahtrite B11 kuni N11 summa ja lahtrite B12: N12 summa. Kui te ei kirjuta objekti Application, eeldatakse seda.
| 123 | Alamkatse keskmine ()Vahemik ("O11") = Töölehe funktsioon. Keskmine (vahemik ("B11: N11"), vahemik ("B12: N12"))End Sub |
Määramine keskmine tulemus muutuja
Võib -olla soovite oma valemi tulemust kasutada mujal koodis, selle asemel, et seda otse Exceli vahemikku tagasi kirjutada. Sellisel juhul saate tulemuse määrata muutujale, et seda oma koodis hiljem kasutada.
| 1234567 | Alammääramine Keskmine ()Tuhm tulemus täisarvuna'Määrake muutujaresult = WorksheetFunction.Average (vahemik ("A10: N10"))'Näita tulemustMsgBox "Selle vahemiku rakkude keskmine on" & tulemusEnd Sub |
KESKMINE koos vahemiku objektiga
Objektile Range saate määrata lahtrirühma ja seejärel kasutada seda vahemiku objekti koos TöölehtFunktsioon objekti.
| 123456789 | AlamkatseAverageRange ()Dim rng As Range'määrake lahtrite vahemikMäära rng = vahemik ("G2: G7")'kasutage valemis vahemikkuVahemik ("G8") = WorksheetFunction.Average (rng)'vabastage vahemiku objektMäära rng = mitte midagiEnd Sub |
Kordarvude Range objektid
Samamoodi saate arvutada mitme vahemiku objekti lahtrite keskmist.
| 123456789101112 | AlamkatseAverageMultipleRanges ()Dim rngA As RangeDim rngB kui vahemik'määrake lahtrite vahemikSet rngA = Range ( "D2: D10")Määra rngB = vahemik ("E2: E10")'kasutage valemis vahemikkuVahemik ("E11") = WorksheetFunction.Average (rngA, rngB)'vabastage vahemiku objektMäära rngA = Mitte midagiMäära rngB = mitte midagiEnd Sub |
AVERAGEA kasutamine
Funktsioon AVERAGEA erineb funktsioonist AVERAGE selle poolest, et loob keskmise kõikidest vahemiku lahtritest, isegi kui ühes lahtris on tekst - see asendab teksti nulliga ja võtab selle keskmise arvutamisel arvesse. Funktsioon KESKMINE ignoreerib seda lahtrit ja ei võta seda arvesse.
| 123 | Alamkatse KeskmineA ()Vahemik ("B8) = Application.WorksheetFunction.AverageA (Vahemik (" A10: A11 "))End Sub |
Allolevas näites tagastab funktsioon KESKMINE funktsiooni AVERAGEA teistsuguse väärtuse, kui arvutust kasutatakse lahtritel A10 kuni A11
Vastus AVERAGEA valemile on madalam kui AVERAGE valem, kuna see asendab A11 teksti nulliga ja seega keskmised on üle 13 väärtuse, mitte need 12 väärtust, mida AVERAGE arvutab.
AVERAGEIF -i kasutamine
Funktsioon AVERAGEIF võimaldab teil keskmistada teatud kriteeriumidele vastava lahtrivahemiku summa.
| 123 | Alam keskmine Kui ()Vahemik ("F31") = WorksheetFunction.AverageIf (Vahemik ("F5: F30"), "Säästud", vahemik ("G5: G30"))End Sub |
Ülaltoodud protseduur keskmistab ainult lahtreid vahemikus G5: G30, kus veeru F vastavas lahtris on sõna „Säästud”. Teie kasutatavad kriteeriumid peavad olema jutumärkides.
Töölehe funktsiooni puudused
Kui kasutate TöölehtFunktsioon töölehe vahemiku väärtuste keskmistamiseks tagastatakse staatiline väärtus, mitte paindlik valem. See tähendab, et kui teie arvud Excelis muutuvad, siis väärtus, mille on tagastanud TöölehtFunktsioon ei muutu.
Ülaltoodud näites on protseduur TestAverage loonud B11: M11 keskmise ja pannud vastuse N11 -sse. Nagu näete valemiribal, on see tulemus joonis ja mitte valem.
Kui mõni väärtustest muutub seetõttu vahemikus (B11: M11), muutuvad N11 tulemused MITTE muutus.
Selle asemel, et kasutada TöölehtFunktsioon.Keskmine, saate VBA abil funktsiooni AVERAGE rakendada lahtrile, kasutades klahvi Valem või Valem R1C1 meetodeid.
Valemimeetodi kasutamine
Valemimeetod võimaldab teil osutada konkreetselt lahtrite vahemikule, nt: B11: M11, nagu allpool näidatud.
| 123 | Alamkatse Keskmine valem ()Vahemik ("N11"). Valem = "= Keskmine (B11: M11)"End Sub |
Kasutades meetodit FormulaR1C1
FomulaR1C1 meetod on paindlikum, kuna see ei piira teid teatud lahtrite vahemikuga. Allolev näide annab meile sama vastuse nagu ülaltoodud.
| 123 | Alamkatse Keskmine valem ()Vahemik ("N11"). Valem = "= Keskmine (RC [-12]: RC [-1])"End Sub |
Kuid valemi paindlikumaks muutmiseks võiksime koodi muuta järgmiselt:
| 123 | Alam TestCountFormula ()ActiveCell.FormulaR1C1 = "= Arv (R [-11] C: R [-1] C)"End Sub |
Kus iganes te oma töölehel asute, keskmistab valem seejärel sellest vasakul olevate 12 lahtri väärtused ja paneb vastuse teie ActiveCelli. Funktsiooni KESKMINE vahemikule tuleb viidata, kasutades rida (R) ja veerg (C) süntaksit.
Mõlemad meetodid võimaldavad teil VBA -s kasutada dünaamilisi Exceli valemeid.
Nüüd on N11 väärtuse asemel valem.
