Come si calcola la media ponderata in Excel?

Immagina di avere un elenco della spesa come quello mostrato nello screenshot seguente. Mentre la funzione MEDIA di Excel restituisce semplicemente il prezzo medio senza tener conto del peso o della quantità, un approccio più accurato in questi casi è calcolare la media ponderata. Questo metodo rappresenta meglio il costo reale per unità, assegnando agli articoli con pesi o quantità maggiori un’influenza più significativa sul risultato finale.

Per calcolare il prezzo medio ponderato, utilizzare una combinazione delle funzioni MATR.SOMMA.PRODOTTOe SOMMAcome segue:

Selezionare una cella vuota, ad esempio F2, e inserire la seguente formula:

=SUMPRODUCT(C2:C18,D2:D18)/SUM(C2:C18)

quindi premere il tasto Invio per ottenere il risultato.

Nota: In questa formula, C2:C18 si riferisce alla colonna Peso e D2:D18 si riferisce alla colonna Prezzo. Adatta questi intervalli in base al layout dei tuoi dati. La funzione MATR.SOMMA.PRODOTTO moltiplica ciascun peso per il prezzo corrispondente e somma i risultati, mentre SOMMA calcola il totale dei pesi, restituendo così la corretta media ponderata. Assicurati di utilizzare intervalli della stessa lunghezza e di non avere dati mancanti o celle vuote nei tuoi dati, poiché ciò potrebbe causare errori di calcolo.

Se la media ponderata calcolata visualizza un numero di posizioni decimali eccessivo o insufficiente rispetto alle tue preferenze, seleziona la cella e fai clic sul pulsante Aumenta decimali o Riduci decimali nella scheda Homeper regolare le posizioni decimali visualizzate in base alle tue esigenze.

Se si verifica un errore come #VALORE!, verificare che ogni cella referenziata contenga un valore numerico e che gli intervalli siano coerenti. Evitare inoltre di includere righe di intestazione nell’intervallo di calcolo per garantire risultati precisi. Quando si lavora con set di dati più ampi, è consigliabile utilizzare intervalli denominati per maggiore chiarezza e facilità di manutenzione.

La formula precedente calcola il prezzo medio ponderato per tutti gli articoli. Nell’analisi pratica, tuttavia, potrebbe essere necessario calcolare la media ponderata per categorie specifiche, ad esempio per trovare il prezzo medio ponderato solo per le mele. In tali casi, è possibile migliorare la formula includendo una condizione basata sui criteri desiderati.

A tal fine, selezionare una cella vuota, ad esempio F8, e inserire la seguente formula:

=SUMPRODUCT((B2:B18="Apple")*C2:C18*D2:D18)/SUMIF(B2:B18,"Apple",C2:C18)

Quindi premere il tasto Invio per calcolare la media ponderata in base ai criteri specificati. Questa formula moltiplica ciascuna coppia peso-prezzo solo se l’articolo soddisfa la condizione («Mela» in questo caso), somma i risultati e li divide per la somma dei pesi relativi esclusivamente a quell’articolo.

Nota: Qui, B2:B18 è la colonna Frutta, C2:C18 è il Peso e D2:D18 è il Prezzo. Sostituisci “Mela” con un altro articolo, se necessario. Questo metodo funziona perfettamente per filtrare in base a un singolo criterio; se devi applicare più criteri contemporaneamente (ad esempio tipo di frutta e fornitore), potresti aver bisogno di una colonna di appoggio o di una formula più avanzata.

Dopo aver applicato la formula, potrebbe essere utile regolare il numero di decimali per una maggiore chiarezza. Seleziona la cella del risultato e utilizza i pulsanti Aumenta decimali o Riduci decimali nella scheda Home per modificare le posizioni decimali visualizzate.

Se la formula restituisce un risultato inaspettato, assicurati che i criteri trovino corrispondenze nell’intervallo di destinazione e verifica la presenza di celle vuote o valori testuali nelle colonne destinate a contenere dati numerici.

In alcune situazioni potrebbe essere necessario calcolare frequentemente medie ponderate su intervalli la cui dimensione varia, che contengono valori mancanti o richiedono filtri flessibili, ad esempio l’applicazione simultanea di più criteri. Piuttosto che aggiornare manualmente formule o intervalli, automatizzare il calcolo tramite una macro VBA consente di risparmiare tempo e ridurre il rischio di errori, risultando particolarmente utile con set di dati ampi o aggiornati regolarmente.

Di seguito viene illustrato come creare e utilizzare una macro VBA per le medie ponderate:

1. Fare clic su Sviluppo > Visual Basic(oppure premere)Alt + F11) per aprire la finestra dell’editor Microsoft Visual Basic, Applications. Successivamente, fare clic su Inserisci > Modulo, quindi incollare il codice seguente nella nuova finestra del modulo:

Sub WeightedAverageVBA()
    Dim rngCriteria As Range
    Dim rngWeight As Range
    Dim rngValue As Range
    Dim criteriaStr As String
    Dim totalWeighted As Double
    Dim totalWeight As Double
    Dim i As Long
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rngCriteria = Application.InputBox("Select the range for criteria (optional, press Cancel to skip):", xTitleId, Type:=8)
    criteriaStr = Application.InputBox("Enter criteria for filtering (leave blank for all):", xTitleId, Type:=2)
    Set rngWeight = Application.InputBox("Select the Weight (numeric) range:", xTitleId, Type:=8)
    Set rngValue = Application.InputBox("Select the Value (e.g. Price) range:", xTitleId, Type:=8)
    
    totalWeighted = 0
    totalWeight = 0
    
    If rngCriteria Is Nothing Or criteriaStr = "" Then
        For i = 1 To rngWeight.Cells.Count
            If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                totalWeight = totalWeight + rngWeight.Cells(i).Value
            End If
        Next i
    Else
        For i = 1 To rngWeight.Cells.Count
            If rngCriteria.Cells(i).Value = criteriaStr Then
                If IsNumeric(rngWeight.Cells(i).Value) And IsNumeric(rngValue.Cells(i).Value) Then
                    totalWeighted = totalWeighted + rngWeight.Cells(i).Value * rngValue.Cells(i).Value
                    totalWeight = totalWeight + rngWeight.Cells(i).Value
                End If
            End If
        Next i
    End If
    
    If totalWeight = 0 Then
        MsgBox "Weighted average cannot be calculated: total weight is zero.", vbExclamation, xTitleId
    Else
        MsgBox "Weighted average: " & totalWeighted / totalWeight, vbInformation, xTitleId
    End If
End Sub

2. Premere F5(oppure fare clic sul) pulsante Esegui) per avviare l’esecuzione.
Verrà richiesto di selezionare gli intervalli passo dopo passo (intervallo criteri – che può essere saltato se non necessario –, intervallo pesi e intervallo valori). È anche possibile inserire criteri specifici per filtrare il calcolo oppure lasciarli vuoti per considerare tutti i dati. La macro supporta intervalli dinamici, risultando pratica qualora la tabella dovesse crescere o cambiare regolarmente.

Infine, verrà visualizzata una finestra di messaggio con il risultato della media ponderata.

Suggerimenti:

• Questo approccio automatizza l’analisi ripetitiva della media ponderata e può essere facilmente esteso per gestire filtri aggiuntivi o opzioni di output personalizzate.
• Assicurati che gli intervalli selezionati abbiano la stessa lunghezza e che i tipi di dati siano coerenti.
• Includere una gestione di base degli errori, come illustrato (ad esempio, quando non vengono trovati pesi validi o la somma dei pesi è zero).
• Se si desidera applicare il calcolo esclusivamente alle righe filtrate o visibili, è possibile ottimizzare ulteriormente il codice utilizzando un’enumerazione mirata delle celle.

In caso di problemi relativi ad autorizzazioni o alla sicurezza delle macro, assicurati di aver abilitato le macro nelle impostazioni di Excel prima di eseguire il codice.

Calcolare la media con arrotondamento in Excel

Calcolare la variazione media in Excel

Calcolare la crescita media annua/composta in Excel

Media mobile/scorrevole in Excel

Totale progressivo/media progressiva in Excel