Acest tutorial vă va arăta cum să utilizați funcția Excel mediu în VBA.
Funcția AVERAGE Excel este utilizată pentru a calcula o medie dintr-un interval de celule din foaia dvs. de lucru care are valori în ele. În VBA, se accesează folosind metoda WorksheetFunction.
Foaie de lucru MEDIE Funcție
Obiectul WorksheetFunction poate fi utilizat pentru a apela majoritatea funcțiilor Excel disponibile în caseta de dialog Insert Function din Excel. Funcția MEDIE este una dintre ele.
| 123 | Sub TestFunctionRange ("D33") = Application.WorksheetFunction.Average ("D1: D32")Sfârșitul Sub |
Puteți avea până la 30 de argumente în funcția MEDIE. Fiecare dintre argumente trebuie să se refere la o gamă de celule.
Acest exemplu de mai jos va produce media sumei celulelor B11 la N11
| 123 | Sub TestAverage ()Range ("O11") = Application.WorksheetFunction.Average (Range ("B11: N11"))Sfârșitul Sub |
Exemplul de mai jos va produce o medie a sumei celulelor din B11 la N11 și suma celulelor din B12: N12. Dacă nu tastați obiectul Application, acesta va fi presupus.
| 123 | Sub TestAverage ()Range ("O11") = WorksheetFunction.Average (Range ("B11: N11"), Range ("B12: N12"))Sfârșitul Sub |
Atribuirea unui rezultat MEDIU unei variabile
Poate doriți să utilizați rezultatul formulei dvs. în altă parte în cod, mai degrabă decât să o scrieți direct înapoi într-un interval Excel. Dacă acesta este cazul, puteți atribui rezultatul unei variabile pentru a o utiliza ulterior în cod.
| 1234567 | Sub AssignAverage ()Diminuează rezultatul ca întreg'Atribuiți variabilaresult = WorksheetFunction.Average (Range ("A10: N10"))'Arată rezultatulMsgBox "Media pentru celulele din acest interval este" & rezultatSfârșitul Sub |
MEDIE cu un obiect Range
Puteți atribui un grup de celule obiectului Range și apoi utilizați acel obiect Range cu Foaie de lucruFuncție obiect.
| 123456789 | Sub TestAverageRange ()Dim rng As Range'atribuiți gama de celuleSetați rng = Range ("G2: G7")'utilizați intervalul din formulăRange ("G8") = WorksheetFunction.Average (rng)'eliberați obiectul de gamăSet rng = NothingSfârșitul Sub |
MEDIE Obiecte cu intervale multiple
În mod similar, puteți calcula media celulelor din mai multe obiecte Range.
| 123456789101112 | Sub TestAverageMultipleRanges ()Dim rngA As RangeDim rngB ca Range'atribuiți gama de celuleSetați rngA = Range ("D2: D10")Setați rngB = Range ("E2: E10")'utilizați intervalul din formulăRange ("E11") = WorksheetFunction.Average (rngA, rngB)'eliberați obiectul de gamăSetați rngA = NimicSetați rngB = NimicSfârșitul Sub |
Folosind AVERAGEA
Funcția AVERAGEA diferă de funcția AVERAGE prin aceea că creează o medie din toate celulele dintr-un interval, chiar dacă una dintre celule are text în ea - înlocuiește textul cu un zero și îl include în calcularea mediei. Funcția MEDIE ar ignora acea celulă și nu o va lua în calcul.
| 123 | Sub TestAverageA ()Range ("B8) = Application.WorksheetFunction.AverageA (Range (" A10: A11 "))Sfârșitul Sub |
În exemplul de mai jos, funcția MEDIE returnează o valoare diferită funcției AVERAGEA atunci când calculul este utilizat pe celulele A10 până la A11
Răspunsul pentru formula AVERAGEA este mai mic decât formula MEDIE, deoarece înlocuiește textul din A11 cu un zero și, prin urmare, are o medie peste 13 valori, mai degrabă decât cele 12 valori pe care le calculează MEDIA.
Folosind AVERAGEIF
Funcția AVERAGEIF vă permite să mediați suma unui interval de celule care îndeplinesc un anumit criteriu.
| 123 | Sub medie dacă ()Range ("F31") = WorksheetFunction.AverageIf (Range ("F5: F30"), "Economii", Range ("G5: G30"))Sfârșitul Sub |
Procedura de mai sus va face doar o medie a celulelor din intervalul G5: G30 unde celula corespunzătoare din coloana F are cuvântul „Economii”. Criteriile pe care le utilizați trebuie să fie între ghilimele.
Dezavantaje ale WorksheetFunction
Când utilizați Foaie de lucruFuncție pentru a medie valorile dintr-un interval din foaia dvs. de lucru, se returnează o valoare statică, nu o formulă flexibilă. Aceasta înseamnă că atunci când cifrele dvs. din Excel se schimbă, valoarea care a fost returnată de Foaie de lucruFuncție nu se va schimba.
În exemplul de mai sus, procedura TestAverage procedura a creat media de B11: M11 și a pus răspunsul în N11. După cum puteți vedea în bara de formule, acest rezultat este o cifră și nu o formulă.
Dacă, prin urmare, oricare dintre valori se schimbă în Gama (B11: M11), rezultatele în N11 vor fi NU Schimbare.
În loc să utilizați Foaie de lucruFuncție. Medie, puteți utiliza VBA pentru a aplica funcția MEDIE unei celule folosind Formulă sau FormulaR1C1 metode.
Folosind metoda Formula
Metoda formulă vă permite să indicați în mod specific către o gamă de celule, de exemplu: B11: M11 așa cum se arată mai jos.
| 123 | Sub TestAverageFormula ()Interval ("N11"). Formula = "= Media (B11: M11)"Sfârșitul Sub |
Folosind metoda FormulaR1C1
Metoda FomulaR1C1 este mai flexibilă în sensul că nu vă restrânge la un interval set de celule. Exemplul de mai jos ne va oferi același răspuns ca cel de mai sus.
| 123 | Sub TestAverageFormula ()Interval ("N11"). Formula = "= Media (RC [-12]: RC [-1])"Sfârșitul Sub |
Cu toate acestea, pentru a face formula mai flexibilă, am putea modifica codul astfel:
| 123 | Sub TestCountFormula ()ActiveCell.FormulaR1C1 = "= Număr (R [-11] C: R [-1] C)"Sfârșitul Sub |
Oriunde vă aflați în foaia de lucru, formula va face apoi media valorilor din cele 12 celule direct în stânga acesteia și va plasa răspunsul în ActiveCell. Gama din interiorul funcției AVERAGE trebuie menționată folosind sintaxa Rând (R) și Coloană (C).
Ambele metode vă permit să utilizați formule dinamice Excel în cadrul VBA.
Acum va exista o formulă în N11 în loc de o valoare.
