Tämä opetusohjelma näyttää, kuinka voit käyttää Excelin keskiarvo -toimintoa VBA: ssa.
Excel AVERAGE -toimintoa käytetään laskemaan keskiarvo laskentataulukon alueen soluista, joissa on arvoja. VBA: ssa sitä käytetään WorksheetFunction -menetelmällä.
KESKIMÄÄRÄINEN laskentataulukko Toiminto
WorksheetFunction -objektia voidaan käyttää useimpien Excel -toimintojen kutsumiseen, jotka ovat käytettävissä Excelin Lisää toiminto -valintaikkunassa. KESKI -toiminto on yksi niistä.
| 123 | AlatestitoimintoAlue ("D33") = Application.WorksheetFunction.Average ("D1: D32")End Sub |
KESKI -funktiossa voi olla enintään 30 argumenttia. Jokaisen argumentin on viitattava solualueeseen.
Tämä alla oleva esimerkki tuottaa solujen B11 - N11 summan keskiarvon
| 123 | Alatesti Keskiarvo ()Alue ("O11") = Application.WorksheetFunction.Average (Alue ("B11: N11"))End Sub |
Alla oleva esimerkki tuottaa B11: n ja N11: n solujen summan ja B12: N12: n solujen summan keskiarvon. Jos et kirjoita sovellusobjektia, se oletetaan.
| 123 | Alatesti Keskiarvo ()Alue ("O11") = WorksheetFunction.Average (Alue ("B11: N11"), alue ("B12: N12"))End Sub |
KESKIMÄÄRÄisen tuloksen määrittäminen muuttujaan
Saatat haluta käyttää kaavan tulosta muualla koodissa sen sijaan, että kirjoittaisit sen suoraan takaisin Excel -alueelle. Jos näin on, voit määrittää tuloksen muuttujalle käytettäväksi myöhemmin koodissasi.
| 1234567 | OsamääritysAverage ()Dim -tulos kokonaislukuna'Määritä muuttujaresult = WorksheetFunction.Average (alue ("A10: N10"))'Näytä tulosMsgBox "Tämän alueen solujen keskiarvo on" & resultEnd Sub |
KESKIMÄÄRÄ alueobjektin kanssa
Voit määrittää Range -objektille soluryhmän ja käyttää sitten Range -objektia LaskentataulukkoToiminto esine.
| 123456789 | AlatestiAverageRange ()Dim rng as Range'määritä solualueAseta rng = alue ("G2: G7")'käytä kaavaaAlue ("G8") = laskentataulukon toiminto.Average (rng)'vapauta etäisyysobjektiAseta rng = ei mitäänEnd Sub |
KESKIMÄÄRÄINEN Usean alueen kohteita
Samoin voit laskea solujen keskiarvon useista alueobjekteista.
| 123456789101112 | AlatestiKeskimääräinenMultipleRanges ()Dim rngA As RangeDim rngB kuin alue'määritä solualueAseta rngA = alue ("D2: D10")Aseta rngB = alue ("E2: E10")'käytä kaavaaAlue ("E11") = laskentataulukon toiminto.Average (rngA, rngB)'vapauta etäisyysobjektiAseta rngA = ei mitäänAseta rngB = ei mitäänEnd Sub |
AVERAGEAn käyttö
AVERAGEA -funktio eroaa AVERAGE -funktiosta siinä, että se luo keskiarvon kaikista alueen soluista, vaikka yhdessä soluista olisi tekstiä - se korvaa tekstin nollalla ja ottaa sen huomioon keskiarvon laskemisessa. KESKI -funktio ohittaa kyseisen solun eikä ota sitä huomioon laskennassa.
| 123 | Alatesti KeskiarvoA ()Alue ("B8) = Application.WorksheetFunction.AverageA (Alue (" A10: A11 "))End Sub |
Alla olevassa esimerkissä KESKI -funktio palauttaa eri arvon AVERAGEA -funktiolle, kun laskutoimitusta käytetään soluissa A10 - A11
AVERAGEA -kaavan vastaus on pienempi kuin KESKI -kaava, koska se korvaa A11: n tekstin nollalla, ja siksi keskiarvot ovat yli 13 arvoa sen 12 arvon sijaan, joita AVERAGE laskee.
AVERAGEIFin käyttäminen
AVERAGEIF -funktion avulla voit laskea tiettyjen kriteerien täyttävän solualueen summan keskiarvon.
| 123 | Keskiarvon alapuolella Jos ()Alue ("F31") = WorksheetFunction.AverageIf (Alue ("F5: F30"), "Säästöt", Alue ("G5: G30"))End Sub |
Yllä oleva menettely laskee vain solut alueella G5: G30, jossa sarakkeen F vastaavassa solussa on sana "Säästöt". Käytettävien kriteerien on oltava lainausmerkeissä.
WorksheetFunction -toiminnon haitat
Kun käytät LaskentataulukkoToiminto laskentataulukon alueen arvojen keskiarvoksi palautetaan staattinen arvo, ei joustava kaava. Tämä tarkoittaa sitä, että kun Excelin luvut muuttuvat, arvo on palauttanut LaskentataulukkoToiminto ei muutu.
Yllä olevassa esimerkissä TestAverage -menettely on luonut B11: M11: n keskiarvon ja kirjoittanut vastauksen N11: een. Kuten kaavapalkista näet, tämä tulos on luku eikä kaava.
Jos jokin arvo muuttuu siksi alueella (B11: M11), tulokset N11 muuttuvat EI muuttaa.
Käyttämisen sijasta Työkirja Toiminto Keskimääräinen, voit käyttää VBA: ta käyttääksesi AVERAGE -toimintoa soluun käyttämällä Kaava tai KaavaR1C1 menetelmiä.
Kaavamenetelmän käyttäminen
Kaavamenetelmän avulla voit osoittaa erityisesti solualueelle, esimerkiksi: B11: M11, kuten alla on esitetty.
| 123 | AlatestiKeskimääräinen kaava ()Alue ("N11"). Kaava = "= Keskiarvo (B11: M11)"End Sub |
Käyttämällä FormulaR1C1 -menetelmää
FomulaR1C1 -menetelmä on joustavampi, koska se ei rajoita sinua tiettyyn solualueeseen. Alla oleva esimerkki antaa meille saman vastauksen kuin yllä oleva.
| 123 | AlatestiKeskimääräinen kaava ()Alue ("N11"). Kaava = "= Keskiarvo (RC [-12]: RC [-1])"End Sub |
Kaavan joustavuuden parantamiseksi voisimme kuitenkin muuttaa koodia tältä:
| 123 | Sub TestCountFormula ()ActiveCell.FormulaR1C1 = "= Laske (R [-11] C: R [-1] C)"End Sub |
Missä tahansa olet laskentataulukossasi, kaava keskittää sitten sen vasemmalla puolella olevien 12 solun arvot ja sijoittaa vastauksen ActiveCell -laitteeseesi. KESKI -toiminnon sisällä olevaan alueeseen on viitattava rivin (R) ja sarakkeen (C) syntaksilla.
Molempien näiden menetelmien avulla voit käyttää dynaamisia Excel -kaavoja VBA: ssa.
N11: ssä on nyt kaava arvon sijaan.
