VBA Genomsnitt - GENNOMSNITT, GENNEMSNITT, GENomsnitt

Denna handledning visar dig hur du använder Excel -genomsnittsfunktionen i VBA.

Funktionen Excel -GENNEMSNITT används för att beräkna ett medelvärde från en rad celler i ditt kalkylblad som har värden i dem. I VBA nås den med metoden WorksheetFunction.

GENNOMSNITT Arbetsblad Funktion

Objektet WorksheetFunction kan användas för att anropa de flesta Excel -funktioner som är tillgängliga i dialogrutan Infoga funktion i Excel. AVERAGE -funktionen är en av dem.

123	SubtestfunktionRange ("D33") = Application.WorksheetFunction.Average ("D1: D32")Avsluta Sub

Du kan ha upp till 30 argument i AVERAGE -funktionen. Var och en av argumenten måste referera till ett cellområde.

Detta exempel nedan kommer att producera genomsnittet av summan av cellerna B11 till N11

123	Deltest Genomsnitt ()Range ("O11") = Application.WorksheetFunction.Average (Range ("B11: N11"))Avsluta Sub

Exemplet nedan ger ett genomsnitt av summan av cellerna i B11 till N11 och summan av cellerna i B12: N12. Om du inte skriver applikationsobjektet antas det.

123	Deltest Genomsnitt ()Range ("O11") = WorksheetFunction.Average (Range ("B11: N11"), Range ("B12: N12"))Avsluta Sub

Tilldela en GENomsnittligt resultat till en variabel

Du kanske vill använda resultatet av din formel någon annanstans i koden istället för att skriva det direkt tillbaka till ett Excel -intervall. Om så är fallet kan du tilldela resultatet till en variabel som ska användas senare i din kod.

1234567

Sub AssignAverage ()Dim resultat Som heltal'Tilldela variabelnresult = WorksheetFunction.Average (intervall ("A10: N10"))'Visa resultatetMsgBox "Medelvärdet för cellerna i detta intervall är" & resultatAvsluta Sub

GENNOMSNITT med ett avståndsobjekt

Du kan tilldela en grupp celler till Range -objektet och sedan använda det Range -objektet med ArbetsbladFunktion objekt.

123456789

Sub TestAverageRange ()Dim rng As Range'tilldela cellintervalletStäll in rng = Range ("G2: G7")'använd intervallet i formelnRange ("G8") = WorksheetFunction.Average (rng)'släpp intervallobjektetStäll in rng = ingentingAvsluta Sub

GENNOMSNITT Multiple Range Objects

På samma sätt kan du beräkna genomsnittet av cellerna från flera intervallobjekt.

123456789101112

Sub TestAverageMultipleRanges ()Dim rngA As RangeDim rngB som intervall'tilldela cellintervalletStäll in rngA = Range ("D2: D10")Ställ in rngB = Range ("E2: E10")'använd intervallet i formelnRange ("E11") = WorksheetFunction.Average (rngA, rngB)'släpp intervallobjektetStäll in rngA = ingentingStäll in rngB = IngentingAvsluta Sub

Använda AVERAGEA

AVERAGEA -funktionen skiljer sig från AVERAGE -funktionen genom att den skapar ett medelvärde från alla celler i ett område, även om en av cellerna har text i den - den ersätter texten med en nolla och inkluderar den vid beräkning av genomsnittet. AVERAGE -funktionen ignorerar den cellen och tar inte med den i beräkningen.

123	Deltest GenomsnittA ()Range ("B8) = Application.WorksheetFunction.AverageA (Range (" A10: A11 "))Avsluta Sub

I exemplet nedan returnerar AVERAGE -funktionen ett annat värde till AVERAGEA -funktionen när beräkningen används på cellerna A10 till A11

Svaret för AVERAGEA -formeln är lägre än MEDEL -formeln eftersom det ersätter texten i A11 med en noll och därför i genomsnitt över 13 värden i stället för de 12 värden som GENomsnittet beräknar över.

Använda AVERAGEIF

Med AVERAGEIF -funktionen kan du genomsnittliga summan av ett cellintervall som uppfyller ett visst kriterium.

123	Undergenomsnitt Om ()Range ("F31") = WorksheetFunction.AverageIf (Range ("F5: F30"), "Savings", Range ("G5: G30"))Avsluta Sub

Proceduren ovan ger bara medelvärde för cellerna i intervallet G5: G30 där motsvarande cell i kolumn F har ordet "Besparingar" i den. Kriterierna du använder måste stå i citattecken.

Nackdelar med WorksheetFunction

När du använder ArbetsbladFunktion för att genomsnittliga värdena i ett intervall i kalkylbladet returneras ett statiskt värde, inte en flexibel formel. Det betyder att när dina siffror i Excel ändras, värdet som har returnerats av ArbetsbladFunktion kommer inte att förändras.

I exemplet ovan har proceduren TestAverage -proceduren skapat genomsnittet av B11: M11 och lagt svaret i N11. Som du kan se i formelfältet är detta resultat en siffra och inte en formel.

Om något av värdena därför ändras i intervallet (B11: M11), kommer resultaten i N11 INTE förändra.

Istället för att använda Arbetsblad Funktion. Genomsnitt, kan du använda VBA för att tillämpa AVERAGE -funktionen på en cell med Formel eller FormelR1C1 metoder.

Med hjälp av formelmetoden

Med formelmetoden kan du peka specifikt på ett cellintervall, t.ex.: B11: M11 enligt nedan.

123	Sub TestAverageFormula ()Område ("N11"). Formel = "= Genomsnitt (B11: M11)"Avsluta Sub

Använda FormulaR1C1 -metoden

FomulaR1C1 -metoden är mer flexibel eftersom den inte begränsar dig till ett angivet cellintervall. Exemplet nedan ger oss samma svar som ovan.

123	Sub TestAverageFormula ()Område ("N11"). Formel = "= Genomsnitt (RC [-12]: RC [-1])"Avsluta Sub

För att göra formeln mer flexibel kan vi dock ändra koden så här:

123	Sub TestCountFormula ()ActiveCell.FormulaR1C1 = "= Antal (R [-11] C: R [-1] C)"Avsluta Sub

Var du än befinner dig i ditt kalkylblad, kommer formeln att genomsnitta värdena i de 12 cellerna direkt till vänster om det och placera svaret i din ActiveCell. Området inuti AVERAGE -funktionen måste hänvisas till med syntaxen Rad (R) och Kolumn (C).

Båda dessa metoder gör att du kan använda dynamiska Excel -formler inom VBA.

Det kommer nu att finnas en formel i N11 istället för ett värde.