Tento tutoriál vám ukáže, ako používať funkciu Priemer Excelu vo VBA.
Funkcia Excel AVERAGE sa používa na výpočet priemeru z buniek rozsahu v hárku, ktoré obsahujú hodnoty. Vo VBA je k nemu prístup pomocou metódy WorksheetFunction.
PRIEMERNÝ pracovný listFunkcia
Objekt WorksheetFunction je možné použiť na vyvolanie väčšiny funkcií programu Excel, ktoré sú k dispozícii v dialógovom okne Vložiť funkciu v programe Excel. Funkcia AVERAGE je jednou z nich.
| 123 | Funkcia čiastkového testuRozsah ("D33") = Application.WorksheetFunction.Average ("D1: D32")Koniec pod |
Vo funkcii PRIEMERNÁ môžete mať až 30 argumentov. Každý z argumentov musí odkazovať na určitý rozsah buniek.
Tento príklad nižšie poskytne priemer súčtu buniek B11 až N11
| 123 | Dielčí testovaný priemer ()Rozsah ("O11") = Application.WorksheetFunction.A priemer (rozsah ("B11: N11"))Koniec pod |
Nasledujúci príklad vytvorí priemer súčtu buniek v B11 až N11 a súčtu buniek v B12: N12. Ak nezadáte objekt Application, bude sa predpokladať.
| 123 | Dielčí testovaný priemer ()Rozsah ("O11") = Pracovný listFunkcia. Priemer (rozsah ("B11: N11"), rozsah ("B12: N12"))Koniec pod |
Priradenie PRIEMERNÉHO výsledku premennej
Výsledok svojho vzorca môžete chcieť použiť inde v kóde, než ho písať priamo späť do programu Excel. Ak je to tak, môžete výsledok priradiť k premennej, ktorá sa má použiť neskôr vo vašom kóde.
| 1234567 | Sub AssignAverage ()Stmaviť výsledok ako celé číslo„Priraďte premennúvýsledok = WorksheetFunction.A priemer (rozsah ("A10: N10"))„Ukáž výsledokMsgBox „Priemer pre bunky v tomto rozsahu je“ & výsledokKoniec pod |
PRIEMERNE s objektom rozsahu
K objektu Range môžete priradiť skupinu buniek a potom použiť tento objekt Range s príponou Pracovný listFunkcia predmet.
| 123456789 | Sub TestAverageRange ()Dim rng As Range'priradiť rozsah buniekNastaviť rng = rozsah ("G2: G7")„použite rozsah vo vzorciRozsah ("G8") = WorksheetFunction.Average (rng)'uvoľnite predmet dosahuNastaviť rng = ničKoniec pod |
PRIEMERNÉ viacrozsahové objekty
Podobne môžete vypočítať priemer buniek z viacerých objektov rozsahu.
| 123456789101112 | Sub TestAverageMultipleRanges ()Dim rngA ako rozsahDim rngB ako rozsah'priradiť rozsah buniekNastaviť rngA = rozsah ("D2: D10")Nastaviť rngB = rozsah ("E2: E10")„použite rozsah vo vzorciRozsah ("E11") = WorksheetFunction.Average (rngA, rngB)'uvoľnite predmet dosahuNastaviť rngA = ničNastaviť rngB = ničKoniec pod |
Použitie AVERAGEA
Funkcia AVERAGEA sa líši od funkcie AVERAGE v tom, že vytvára priemer zo všetkých buniek v rozsahu, aj keď jedna z buniek obsahuje text - nahradí text nulou a zahrnie ju do výpočtu priemeru. PRIEMERNÁ funkcia by túto bunku ignorovala a nezapočítavala by ju do výpočtu.
| 123 | Dielčí testovaný priemerA ()Rozsah ("B8) = Application.WorksheetFunction.AverageA (rozsah (" A10: A11 "))Koniec pod |
V nižšie uvedenom príklade funkcia AVERAGE vracia funkcii AVERAGEA inú hodnotu, ak sa výpočet používa v bunkách A10 až A11
Odpoveď na vzorec AVERAGEA je nižšia ako priemerný vzorec, pretože nahrádza text v A11 nulou, a preto priemeruje viac ako 13 hodnôt, a nie 12 hodnôt, pre ktoré priemer vypočítava.
Použitie AVERAGEIF
Funkcia AVERAGEIF vám umožňuje priemerovať súčet rozsahu buniek, ktoré spĺňajú určité kritériá.
| 123 | Podpriemerný priemer Ak ()Rozsah ("F31") = WorksheetFunction.AverageIf (rozsah ("F5: F30"), "úspory", rozsah ("G5: G30"))Koniec pod |
Vyššie uvedený postup bude priemerovať iba bunky v rozsahu G5: G30, kde v zodpovedajúcej bunke v stĺpci F je slovo „Úspory“. Kritériá, ktoré použijete, musia byť v úvodzovkách.
Nevýhody funkcie WorksheetFunction
Keď použijete Pracovný listFunkcia na priemerovanie hodnôt v rozsahu v hárku sa vráti statická hodnota, nie flexibilný vzorec. To znamená, že keď sa vaše údaje v Exceli zmenia, hodnota, ktorú vrátil súbor Pracovný listFunkcia sa nezmení.
Vo vyššie uvedenom príklade procedúra TestAverage vytvorila priemer B11: M11 a odpoveď vložila do N11. Ako vidíte na paneli vzorcov, tento výsledok je údaj a nie vzorec.
Ak sa teda ktorákoľvek z hodnôt zmení v rozsahu (B11: M11), výsledky v N11 sa zmenia NIE zmeniť.
Namiesto použitia Pracovný list Funkcia. Priemer, môžete použiť VBA na aplikovanie funkcie AVERAGE na bunku pomocou Vzorec alebo Formula R1C1 metódy.
Použitie metódy vzorca
Metóda vzorca vám umožňuje konkrétne poukázať na rozsah buniek, napr .: B11: M11, ako je uvedené nižšie.
| 123 | Čiastkový test priemerov ()Rozsah ("N11"). Vzorec = "= priemer (B11: M11)"Koniec pod |
Použitie metódy FormulaR1C1
Metóda FomulaR1C1 je flexibilnejšia v tom, že vás neobmedzuje na určitý rozsah buniek. Nasledujúci príklad nám poskytne rovnakú odpoveď ako vyššie.
| 123 | Čiastkový test priemerov ()Rozsah ("N11"). Vzorec = "= priemer (RC [-12]: RC [-1])"Koniec pod |
Aby bol však vzorec flexibilnejší, mohli by sme kód zmeniť tak, aby vyzeral takto:
| 123 | Vzorec čiastkového testu ()ActiveCell.FormulaR1C1 = "= Počet (R [-11] C: R [-1] C)"Koniec pod |
Nech ste kdekoľvek v pracovnom hárku, vzorec potom spriemeruje hodnoty v 12 bunkách priamo naľavo od neho a odpoveď vloží do vášho ActiveCell. Na rozsah vo funkcii AVERAGE je potrebné odkazovať pomocou syntaxe riadka (R) a stĺpca (C).
Obe tieto metódy vám umožňujú používať vzorce Dynamic Excel vo VBA.
Teraz bude namiesto hodnoty v N11 vzorec.
