Tento tutoriál vám ukáže, jak používat průměrnou funkci Excelu ve VBA.
Funkce Excel AVERAGE se používá k výpočtu průměru z buněk rozsahu v listu, které obsahují hodnoty. Ve VBA je k němu přistupováno pomocí metody WorksheetFunction.
PRŮMĚR Funkce pracovního listu
Objekt WorksheetFunction lze použít k volání většiny funkcí aplikace Excel, které jsou k dispozici v dialogovém okně Vložit funkci v aplikaci Excel. Funkce AVERAGE je jednou z nich.
| 123 | Dílčí testovací funkceRozsah ("D33") = Application.WorksheetFunction.Average ("D1: D32")End Sub |
Ve funkci PRŮMĚR můžete mít až 30 argumentů. Každý z argumentů musí odkazovat na rozsah buněk.
Tento příklad níže vytvoří průměr součtu buněk B11 až N11
| 123 | Dílčí testovací průměr ()Rozsah ("O11") = Application.WorksheetFunction.Average (Range ("B11: N11"))End Sub |
Níže uvedený příklad vytvoří průměrný součet buněk v B11 až N11 a součet buněk v B12: N12. Pokud nezadáte objekt Application, bude se předpokládat.
| 123 | Dílčí testovací průměr ()Rozsah ("O11") = WorksheetFunction.Average (Range ("B11: N11"), Range ("B12: N12"))End Sub |
Přiřazení PRŮMĚRNÉHO výsledku proměnné
Možná budete chtít použít výsledek vzorce jinde v kódu, než abyste ho psali přímo zpět do rozsahu Excelu. Pokud tomu tak je, můžete výsledek přiřadit proměnné, kterou použijete později v kódu.
| 1234567 | Dílčí přiřazeníPrůměr ()Dim výsledek jako celé číslo"Přiřaďte proměnnou."result = WorksheetFunction.Average (Range ("A10: N10"))„Ukaž výsledekMsgBox "Průměr buněk v tomto rozsahu je" & resultEnd Sub |
PRŮMĚR s objektem dosahu
K objektu Range můžete přiřadit skupinu buněk a poté použít tento Range objekt s Pracovní list Funkce objekt.
| 123456789 | Dílčí testovací průměrRange ()Dim rng As Range'přiřaďte rozsah buněkNastavit rng = rozsah ("G2: G7")'použijte rozsah ve vzorci.'Rozsah ("G8") = WorksheetFunction.Average (rng)'uvolněte předmět dosahuNastavit rng = NicEnd Sub |
PRŮMĚRNÉ objekty s více rozsahy
Podobně můžete vypočítat průměr buněk z více objektů rozsahu.
| 123456789101112 | Dílčí testovací průměrMultipleRanges ()Dim rngA jako rozsahDim rngB jako rozsah'přiřaďte rozsah buněkNastavit rngA = rozsah ("D2: D10")Nastavit rngB = rozsah ("E2: E10")'použijte rozsah ve vzorci.'Rozsah ("E11") = WorksheetFunction.Average (rngA, rngB)'uvolněte předmět dosahuNastavit rngA = NicNastavit rngB = NicEnd Sub |
Pomocí AVERAGEA
Funkce AVERAGEA se liší od funkce AVERAGE v tom, že vytváří průměr ze všech buněk v rozsahu, i když jedna z buněk obsahuje text - nahradí text nulou a zahrne ji do výpočtu průměru. Funkce PRŮMĚR by tuto buňku ignorovala a nezohlednila ji ve výpočtu.
| 123 | Dílčí testovací průměrA ()Rozsah ("B8) = Application.WorksheetFunction.AverageA (rozsah (" A10: A11 "))End Sub |
V níže uvedeném příkladu funkce AVERAGE vrací funkci AVERAGEA jinou hodnotu, když je výpočet použit na buňkách A10 až A11
Odpověď na vzorec AVERAGEA je nižší než vzorec AVERAGE, protože nahrazuje text v A11 nulou, a proto průměruje více než 13 hodnot, než 12 hodnot, nad kterými AVERAGE vypočítává.
Pomocí AVERAGEIF
Funkce AVERAGEIF vám umožňuje průměrovat součet rozsahu buněk, které splňují určitá kritéria.
| 123 | Podprůměrný If ()Rozsah ("F31") = WorksheetFunction.AverageIf (Range ("F5: F30"), "Savings", Range ("G5: G30"))End Sub |
Výše uvedený postup bude průměrovat pouze buňky v rozsahu G5: G30, kde odpovídající buňka ve sloupci F obsahuje slovo „Úspory“. Kritéria, která používáte, musí být v uvozovkách.
Nevýhody funkce WorksheetFunction
Když použijete Pracovní list Funkce pro průměrování hodnot v rozsahu v listu je vrácena statická hodnota, nikoli flexibilní vzorec. To znamená, že když se vaše čísla v Excelu změní, hodnota, která byla vrácena Pracovní list Funkce nezmění.
Ve výše uvedeném příkladu procedura Procedura TestAverage vytvořila průměr B11: M11 a odpověď vložila do N11. Jak vidíte na řádku vzorců, tento výsledek je obrázek a ne vzorec.
Pokud se tedy některá z hodnot změní v rozsahu (B11: M11), výsledky v N11 se změní NE změna.
Místo použití Pracovní list Funkce. Průměr, můžete použít VBA k použití funkce AVERAGE na buňku pomocí Vzorec nebo Vzorec R1C1 metody.
Použití metody vzorce
Metoda vzorce vám umožňuje konkrétně ukázat na řadu buněk, např.: B11: M11, jak je uvedeno níže.
| 123 | Dílčí test Průměrný vzorec ()Rozsah ("N11"). Vzorec = "= Průměr (B11: M11)"End Sub |
Použití metody FormulaR1C1
Metoda FomulaR1C1 je flexibilnější v tom, že vás neomezuje na nastavený rozsah buněk. Následující příklad nám dá stejnou odpověď jako ten výše.
| 123 | Dílčí test Průměrný vzorec ()Rozsah ("N11"). Vzorec = "= Průměr (RC [-12]: RC [-1])"End Sub |
Aby byl vzorec flexibilnější, mohli bychom kód upravit tak, aby vypadal takto:
| 123 | Dílčí testovací součet ()ActiveCell.FormulaR1C1 = "= Počet (R [-11] C: R [-1] C)"End Sub |
Ať jste kdekoli v listu, vzorec pak průměruje hodnoty ve 12 buňkách přímo nalevo od něj a odpověď umístí do vašeho ActiveCell. Na rozsah uvnitř funkce PRŮMĚR je třeba odkazovat pomocí syntaxe Řádek (R) a Sloupec (C).
Obě tyto metody vám umožňují používat ve VBA vzorce Dynamic Excel.
Nyní bude místo hodnoty vzorec v N11.
