Questo tutorial ti mostrerà come utilizzare la funzione Media di Excel in VBA.
La funzione MEDIA di Excel viene utilizzata per calcolare una media da un intervallo di celle nel foglio di lavoro che contengono valori. In VBA, si accede utilizzando il metodo WorksheetFunction.
Foglio di lavoro MEDIA Funzione
L'oggetto WorksheetFunction può essere utilizzato per chiamare la maggior parte delle funzioni di Excel disponibili nella finestra di dialogo Inserisci funzione in Excel. La funzione MEDIA è una di queste.
| 123 | Funzione di test secondarioRange("D33") = Application.WorksheetFunction.Average("D1:D32")Fine sottotitolo |
Puoi avere fino a 30 argomenti nella funzione MEDIA. Ciascuno degli argomenti deve fare riferimento a un intervallo di celle.
Questo esempio di seguito produrrà la media della somma delle celle da B11 a N11
| 123 | Media del test secondario()Range("O11") = Application.WorksheetFunction.Average(Range("B11:N11"))Fine sottotitolo |
L'esempio seguente produrrà una media della somma delle celle da B11 a N11 e la somma delle celle in B12:N12. Se non si digita l'oggetto Application, verrà assunto.
| 123 | Media del test secondario()Intervallo ("O11") = Funzione foglio di lavoro. Media (Intervallo ("B11: N11"), Intervallo ("B12: N12"))Fine sottotitolo |
Assegnare un risultato AVERAGE a una variabile
Potresti voler utilizzare il risultato della tua formula altrove nel codice anziché scriverlo direttamente in un intervallo di Excel. In questo caso, puoi assegnare il risultato a una variabile da utilizzare successivamente nel codice.
| 1234567 | Media Assegnazione secondaria()Risultato debole come numero intero'Assegna la variabilerisultato = WorksheetFunction.Average(Range("A10:N10"))'Mostra il risultatoMsgBox "La media delle celle in questo intervallo è " & risultatoFine sottotitolo |
MEDIA con un oggetto intervallo
È possibile assegnare un gruppo di celle all'oggetto Range e quindi utilizzare quell'oggetto Range con il pulsante Foglio di lavoroFunzione oggetto.
| 123456789 | Sub TestAverageRange()Dim rng come intervallo'assegna l'intervallo di celleImposta rng = Intervallo ("G2: G7")'usa l'intervallo nella formulaIntervallo ("G8") = Funzione foglio di lavoro. Media (rng)'rilascia l'oggetto intervalloImposta rng = NienteFine sottotitolo |
MEDIA Oggetti a intervallo multiplo
Allo stesso modo, puoi calcolare la media delle celle da più oggetti intervallo.
| 123456789101112 | Sub TestMediaMultipleRange()Dim rngA come intervalloDim rngB come intervallo'assegna l'intervallo di celleImposta rngA = Intervallo ("D2: D10")Imposta rngB = Intervallo ("E2: E10")'usa l'intervallo nella formulaIntervallo ("E11") = Funzione foglio di lavoro. Media (rngA, rngB)'rilascia l'oggetto intervalloImposta rngA = NienteImposta rngB = NienteFine sottotitolo |
Utilizzo di AVERAGEA
La funzione AVERAGEA differisce dalla funzione AVERAGE in quanto crea una media da tutte le celle in un intervallo, anche se una delle celle contiene del testo - sostituisce il testo con uno zero e lo include nel calcolo della media. La funzione MEDIA ignorerebbe quella cella e non la fattorizzerebbe nel calcolo.
| 123 | Sub TestMediaA()Range("B8) = Application.WorksheetFunction.AverageA(Range("A10:A11"))Fine sottotitolo |
Nell'esempio seguente, la funzione MEDIA restituisce un valore diverso alla funzione MEDIA quando il calcolo viene utilizzato sulle celle da A10 a A11
La risposta per la formula AVERAGEA è inferiore alla formula AVERAGE in quanto sostituisce il testo in A11 con uno zero e quindi calcola la media su 13 valori anziché sui 12 valori su cui calcola la AVERAGE.
Utilizzo di AVERAGEIF
La funzione AVERAGEIF consente di calcolare la media della somma di un intervallo di celle che soddisfano un determinato criterio.
| 123 | Sotto media se()Range("F31") = WorksheetFunction.AverageIf(Range("F5:F30"), "Risparmio", Range("G5:G30"))Fine sottotitolo |
La procedura sopra farà la media solo delle celle nell'intervallo G5: G30 in cui la cella corrispondente nella colonna F contiene la parola "Risparmio". I criteri utilizzati devono essere tra virgolette.
Svantaggi di WorksheetFunction
Quando usi il Foglio di lavoroFunzione per fare la media dei valori in un intervallo nel foglio di lavoro, viene restituito un valore statico, non una formula flessibile. Ciò significa che quando le tue cifre in Excel cambiano, il valore che è stato restituito dal Foglio di lavoroFunzione non cambierà.
Nell'esempio sopra, la procedura TestAverage ha creato la media di B11:M11 e ha inserito la risposta in N11. Come puoi vedere nella barra della formula, questo risultato è una cifra e non una formula.
Se uno qualsiasi dei valori cambia quindi nell'intervallo (B11:M11 ), i risultati in N11 saranno NON modificare.
Invece di usare il Foglio di lavoroFunzione.Media, puoi usare VBA per applicare la funzione AVERAGE a una cella usando il Formula o FormulaR1C1 metodi.
Usando il metodo delle formule
Il metodo della formula consente di puntare in modo specifico a un intervallo di celle, ad esempio: B11: M11 come mostrato di seguito.
| 123 | Sub TestMediaFormula()Intervallo ("N11"). Formula = "= Media (B11: M11)"Fine sottotitolo |
Utilizzo del metodo FormulaR1C1
Il metodo FomulaR1C1 è più flessibile in quanto non ti limita a un determinato intervallo di celle. L'esempio seguente ci darà la stessa risposta di quello sopra.
| 123 | Sub TestMediaFormula()Intervallo("N11").Formula = "=Media(RC[-12]:RC[-1])"Fine sottotitolo |
Tuttavia, per rendere la formula più flessibile, potremmo modificare il codice in questo modo:
| 123 | Sub TestCountFormula()ActiveCell.FormulaR1C1 = "=Count(R[-11]C:R[-1]C)"Fine sottotitolo |
Ovunque ti trovi nel tuo foglio di lavoro, la formula farà quindi la media dei valori nelle 12 celle direttamente a sinistra di essa e posizionerà la risposta nel tuo ActiveCell. L'Intervallo all'interno della funzione MEDIA deve essere riferito utilizzando la sintassi Riga (R) e Colonna (C).
Entrambi questi metodi consentono di utilizzare formule Excel dinamiche all'interno di VBA.
Ora ci sarà una formula in N11 invece di un valore.
