Ez az oktatóanyag megmutatja, hogyan kell használni az Excel Átlag funkciót a VBA -ban.
Az Excel ÁTLAG függvény egy átlag kiszámítására szolgál a munkalap tartománycelláiból, amelyekben vannak értékek. A VBA -ban a WorksheetFunction módszerrel érhető el.
ÁTLAG munkalapFunkció
A WorksheetFunction objektum a legtöbb Excel -funkció meghívására használható, amelyek az Excel Funkció beszúrása párbeszédpaneljén érhetők el. Ezek közé tartozik az ÁTLAG funkció.
| 123 | Sub TestFunctionTartomány ("D33") = Application.WorksheetFunction.Average ("D1: D32")End Sub |
Az ÁTLAG függvényben legfeljebb 30 argumentum áll rendelkezésre. Minden érvnek cellatartományra kell vonatkoznia.
Ez az alábbi példa a B11 -N11 cellák összegének átlagát adja meg
| 123 | Sub TestAverage ()Tartomány ("O11") = Application.WorksheetFunction.Average (tartomány ("B11: N11"))End Sub |
Az alábbi példa a B11 – N11 cellák összegének és a B12: N12 cellák összegének átlagát adja. Ha nem írja be az Application objektumot, akkor azt feltételezi.
| 123 | Sub TestAverage ()Tartomány ("O11") = WorksheetFunction.Average (tartomány ("B11: N11"), tartomány ("B12: N12"))End Sub |
ÁTLAGOS eredmény hozzárendelése egy változóhoz
Érdemes a képlet eredményét máshol használni a kódban, nem pedig közvetlenül visszaírni egy Excel tartományba. Ebben az esetben az eredményt hozzárendelheti egy változóhoz, amelyet később használhat a kódban.
| 1234567 | Hozzárendelés átlaga ()Halvány eredmény Egész számként'Rendelje hozzá a változóteredmény = WorksheetFunction.Average (tartomány ("A10: N10"))'Mutasd az eredménytMsgBox "Az ebben a tartományban lévő cellák átlaga" & resultEnd Sub |
ÁTLAG egy tartomány objektummal
Cellacsoportot rendelhet a Range objektumhoz, majd a Range objektumot a MunkalapFunkció tárgy.
| 123456789 | Sub TestAverageRange ()Dim rng mint tartomány'rendelje hozzá a cellák tartományátRng = tartomány beállítása ("G2: G7")'használja a tartományt a képletbenTartomány ("G8") = WorksheetFunction.Average (rng)'engedje el a tartományobjektumotSet rng = SemmiEnd Sub |
ÁTLAGOS Több tartományú objektumok
Hasonlóképpen, több tartomány objektumból is kiszámíthatja a cellák átlagát.
| 123456789101112 | Sub TestAverageMultipleRanges ()Dim rngA As RangeDim rngB mint tartomány'rendelje hozzá a cellák tartományátÁllítsa be az rngA = tartományt ("D2: D10")Állítsa be az rngB = tartományt ("E2: E10")'használja a tartományt a képletbenTartomány ("E11") = WorksheetFunction.Average (rngA, rngB)'engedje el a tartományobjektumotSet rngA = SemmiSet rngB = SemmiEnd Sub |
Az AVERAGEA használata
Az AVERAGEA függvény abban különbözik az ÁTLAG függvénytől, hogy átlagot hoz létre a tartomány összes cellájából, még akkor is, ha az egyik cella szöveget tartalmaz - a szöveget nullával helyettesíti, és ezt az átlag kiszámításakor is figyelembe veszi. Az ÁTLAG függvény figyelmen kívül hagyja ezt a cellát, és nem veszi figyelembe a számításban.
| 123 | Rész tesztAverageA ()Tartomány ("B8) = Application.WorksheetFunction.AverageA (tartomány (" A10: A11 "))End Sub |
Az alábbi példában az ÁTLAG függvény más értéket ad vissza az AVERAGEA függvénynek, ha a számítást az A10 - A11 cellákra használják.
Az AVERAGEA képletre adott válasz alacsonyabb, mint az ÁTLAG képlet, mivel az A11 szövegét nullával helyettesíti, és ezért 13 -nál több átlagot jelent, nem pedig azt a 12 értéket, amelyen az ÁTLAG számol.
Az AVERAGEIF használatával
Az AVERAGEIF függvény lehetővé teszi, hogy átlagolja egy bizonyos feltételeknek megfelelő cellatartomány összegét.
| 123 | Átlag alatti Ha ()Tartomány ("F31") = WorksheetFunction.AverageIf (Tartomány ("F5: F30"), "Takarék", Tartomány ("G5: G30"))End Sub |
A fenti eljárás csak a G5: G30 tartomány celláit fogja átlagolni, ahol az F oszlop megfelelő cellájában a „Takarék” szó szerepel. Az Ön által használt kritériumoknak idézőjelek között kell lenniük.
A WorksheetFunction hátrányai
Amikor a MunkalapFunkció a munkalap egy tartományának átlagolásához statikus érték kerül visszaadásra, nem rugalmas képlet. Ez azt jelenti, hogy amikor az Excel számai megváltoznak, akkor a MunkalapFunkció nem fog változni.
A fenti példában a TestAverage eljárás létrehozta a B11: M11 átlagát, és a választ N11 -be tette. Amint a képletsávban látható, ez az eredmény egy ábra és nem képlet.
Ha valamelyik érték ezért változik a tartományban (B11: M11), akkor az N11 eredményei változnak NEM változás.
Ahelyett, hogy a MunkalapFunkció.Átlagos, a VBA segítségével alkalmazhatja az ÁTLAG funkciót egy cellára a Képlet vagy KépletR1C1 mód.
A képlet módszer használatával
A képlet módszer lehetővé teszi, hogy konkrétan mutasson egy cellatartományra, például: B11: M11, amint az alább látható.
| 123 | Sub TestAverageFormula ()Tartomány ("N11"). Képlet = "= Átlag (B11: M11)"End Sub |
A FormulaR1C1 módszer használatával
A FomulaR1C1 módszer rugalmasabb, mivel nem korlátozza a cellák meghatározott tartományára. Az alábbi példa ugyanazt a választ adja, mint a fenti.
| 123 | Sub TestAverageFormula ()Tartomány ("N11"). Képlet = "= Átlag (RC [-12]: RC [-1])"End Sub |
A képlet rugalmasabbá tétele érdekében azonban módosíthatjuk a kódot így:
| 123 | Sub TestCountFormula ()ActiveCell.FormulaR1C1 = "= Szám (R [-11] C: R [-1] C)"End Sub |
Bárhol is van a munkalapon, a képlet ezután átlagolja a közvetlenül tőle balra lévő 12 cella értékeit, és a választ az ActiveCell -be helyezi. Az ÁTLAG funkcióban lévő tartományra a Sor (R) és az Oszlop (C) szintaxis segítségével kell hivatkozni.
Mindkét módszer lehetővé teszi a dinamikus Excel képletek használatát a VBA -n belül.
Most az N11 képlet lesz az érték helyett.
