Ši pamoka parodys, kaip naudoti „Excel“ vidurkio funkciją VBA.
Funkcija „Excel AVERAGE“ naudojama apskaičiuoti vidurkį iš jūsų darbalapio diapazono langelių, kuriuose yra reikšmių. VBA jis pasiekiamas naudojant „WorksheetFunction“ metodą.
VIDUTINĖ darbalapio funkcija
„WorksheetFunction“ objektu galima iškviesti daugumą „Excel“ funkcijų, kurios pasiekiamos „Excel“ dialogo lange Įterpti funkciją. Funkcija AVERAGE yra viena iš jų.
| 123 | Papildoma testavimo funkcijaDiapazonas ("D33") = Application.WorksheetFunction.Average ("D1: D32")Pabaiga Sub |
Funkcijoje AVERAGE galite turėti iki 30 argumentų. Kiekvienas argumentas turi būti susijęs su langelių diapazonu.
Šis toliau pateiktas pavyzdys parodys ląstelių B11 – N11 sumos vidurkį
| 123 | Antrasis testas Vidutinis ()Diapazonas ("O11") = Application.WorksheetFunction.Average (Diapazonas ("B11: N11"))Pabaiga Sub |
Žemiau pateiktas pavyzdys parodys B11 – N11 ląstelių ir B12: N12 ląstelių sumos vidurkį. Jei neįvesite programos objekto, jis bus laikomas.
| 123 | Antrasis testas Vidutinis ()Diapazonas ("O11") = WorksheetFunction.Average (Diapazonas ("B11: N11"), diapazonas ("B12: N12"))Pabaiga Sub |
KIEKVIENO rezultato priskyrimas kintamajam
Galbūt norėsite savo formulės rezultatą naudoti kitoje kodo vietoje, o ne rašyti tiesiai atgal į „Excel“ diapazoną. Tokiu atveju galite priskirti rezultatą kintamajam, kuris bus naudojamas vėliau jūsų kode.
| 1234567 | Sub AssignAverage ()Neryškus rezultatas kaip sveikasis skaičius'Priskirkite kintamąjįrezultatas = darbo lapasFunkcija.Average (diapazonas ("A10: N10"))'Parodyk rezultatą„MsgBox“ „Šio diapazono ląstelių vidurkis yra“ ir rezultatasPabaiga Sub |
VIDUTINIS su diapazono objektu
Galite priskirti langelių grupę objektui „Range“, o tada naudoti tą diapazono objektą su Darbo lapas Funkcija objektas.
| 123456789 | Antrasis testasAverageRange ()Dim rng As Range“priskirti ląstelių diapazonąNustatyti rng = Diapazonas („G2: G7“)'naudokite diapazoną formulėjeDiapazonas ("G8") = WorksheetFunction.Average (rng)“paleiskite diapazono objektąNustatyti rng = niekoPabaiga Sub |
VIDUTINIS Kelių diapazonų objektai
Panašiai galite apskaičiuoti langelių vidurkį iš kelių diapazono objektų.
| 123456789101112 | Antrasis testasAverageMultipleRanges ()Dim rngA As DiapazonasDim rngB kaip diapazonas“priskirti ląstelių diapazonąNustatyti rngA = Diapazonas („D2: D10“)Nustatykite rngB = Diapazonas („E2: E10“)'naudokite diapazoną formulėjeDiapazonas („E11“) = WorksheetFunction.Average (rngA, rngB)“paleiskite diapazono objektąNustatyti rngA = NiekoNustatyti rngB = niekoPabaiga Sub |
Naudojant AVERAGEA
Funkcija AVERAGEA skiriasi nuo funkcijos AVERAGE tuo, kad ji sukuria vidurkį iš visų diapazono langelių, net jei vienoje iš langelių yra tekstas - ji pakeičia tekstą nuliu ir įtraukia jį skaičiuojant vidurkį. Funkcija AVERAGE ignoruoja tą langelį ir neįtraukia į skaičiavimus.
| 123 | Antrasis bandymasAverage (()Diapazonas ("B8) = Application.WorksheetFunction.AverageA (Diapazonas (" A10: A11 "))Pabaiga Sub |
Žemiau pateiktame pavyzdyje funkcija AVERAGE grąžina kitą AVERAGEA funkcijos reikšmę, kai skaičiavimas naudojamas ląstelėse A10 - A11
Atsakymas į AVERAGEA formulę yra mažesnis už AVERAGE formulę, nes jis pakeičia A11 tekstą nuliu, todėl vidurkiai viršija 13 verčių, o ne 12 verčių, kurias skaičiuoja AVERAGE.
Naudojant AVERAGEIF
Funkcija AVERAGEIF leidžia apskaičiuoti ląstelių, atitinkančių tam tikrus kriterijus, sumą.
| 123 | Vidutinis vidurkisJei ()Diapazonas ("F31") = WorksheetFunction.AverageIf (Diapazonas ("F5: F30"), "Taupymas", diapazonas ("G5: G30"))Pabaiga Sub |
Atliekant aukščiau aprašytą procedūrą, bus tik apskaičiuoti tik G5: G30 diapazono langeliai, kuriuose atitinkamoje F stulpelio ląstelėje yra žodis „Taupymas“. Jūsų naudojami kriterijai turi būti kabutėse.
„WorksheetFunction“ trūkumai
Kai naudojate Darbo lapas Funkcija norint apskaičiuoti savo darbalapio diapazono vertes, grąžinama statinė vertė, o ne lanksti formulė. Tai reiškia, kad pasikeitus „Excel“ skaičiams, vertė, kurią grąžino Darbo lapas Funkcija nepasikeis.
Anksčiau pateiktame pavyzdyje procedūra „TestAverage“ sukūrė B11: M11 vidurkį ir atsakymą įtraukė į N11. Kaip matote formulės juostoje, šis rezultatas yra figūra, o ne formulė.
Jei kuri nors iš verčių pasikeičia diapazone (B11: M11), rezultatai N11 pasikeis NE keistis.
Užuot naudoję Darbo lapas Funkcija. Vidutinis, galite naudoti VBA, kad pritaikytumėte AVERAGE funkciją langeliui naudodami Formulė arba Formulė R1C1 metodus.
Naudojant formulės metodą
Formulės metodas leidžia konkrečiai nurodyti langelių diapazoną, pvz .: B11: M11, kaip parodyta žemiau.
| 123 | Antrasis testasAverageFormula ()Diapazonas ("N11"). Formulė = "= Vidutinis (B11: M11)"Pabaiga Sub |
Naudojant „FormulaR1C1“ metodą
„FomulaR1C1“ metodas yra lankstesnis, nes neapsiriboja tam tikru ląstelių diapazonu. Žemiau pateiktas pavyzdys suteiks mums tą patį atsakymą, kaip ir aukščiau.
| 123 | Antrasis testasAverageFormula ()Diapazonas ("N11"). Formulė = "= Vidutinis (RC [-12]: RC [-1])"Pabaiga Sub |
Tačiau, kad formulė būtų lankstesnė, kodą galėtume pakeisti taip:
| 123 | Sub TestCountFormula ()ActiveCell.FormulaR1C1 = "= Skaičius (R [-11] C: R [-1] C)"Pabaiga Sub |
Kad ir kur esate savo darbalapyje, formulė apskaičiuos vidutiniškai 12 langelių, esančių kairėje, vertes, ir atsakymą įdės į „ActiveCell“. Funkcijos AVERAGE diapazonas turi būti nurodytas naudojant eilutės (R) ir stulpelio (C) sintaksę.
Abu šie metodai leidžia naudoti dinamines „Excel“ formules VBA.
Dabar vietoj reikšmės N11 bus formulė.
