Come calcolare la media ponderata in Excel?

Supponiamo di avere una lista della spesa come mostrato nello screenshot sottostante. Mentre la funzione MEDIA di Excel fornirebbe la media dei prezzi senza tener conto del peso o della quantità, un approccio più accurato in questi casi è calcolare la media ponderata. Questo riflette meglio il costo reale per unità dando agli elementi con pesi o frequenze maggiori un'influenza più forte sul risultato finale.

Per calcolare il prezzo medio ponderato, utilizza una combinazione delle funzioni SOMMA.PRODOTTO e SOMMA come segue:

Seleziona una cella vuota, come F2, inserisci la seguente formula:

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

e premi 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. Regola questi intervalli secondo necessità per il tuo layout dati. La funzione SOMMA.PRODOTTO moltiplica ciascun peso per il prezzo corrispondente e somma i risultati, mentre SOMMA totalizza i pesi—fornendo la media ponderata corretta. Assicurati di utilizzare intervalli di lunghezza uguale e verifica che non ci siano celle mancanti o vuote nei tuoi dati, poiché ciò potrebbe portare a errori di calcolo.

Se la media ponderata calcolata mostra troppi o troppo pochi decimali per le tue preferenze, seleziona la cella, quindi clicca su Aumenta Decimali pulsante o Riduci Decimali pulsante nella scheda Home per regolare i decimali visualizzati secondo necessità.

Se incontri un errore come #VALORE!, verifica che ogni cella referenziata contenga un valore numerico e che gli intervalli siano consistenti. Inoltre, evita di includere qualsiasi riga di intestazione nell'intervallo di calcolo per garantire risultati accurati. Quando lavori con dataset più grandi, considera l'uso di intervalli denominati per chiarezza e facilità di manutenzione.

La formula precedente calcola il prezzo medio ponderato per tutti gli elementi. Nell'analisi pratica, potresti voler calcolare la media ponderata per categorie specifiche, come trovare il prezzo medio ponderato solo per le mele. In questi casi, puoi migliorare la formula per includere una condizione basata sui tuoi criteri.

Per fare questo, seleziona una cella vuota, come F8, e inserisci la seguente formula:

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

Quindi premi il tasto Invio per calcolare la media ponderata che soddisfa i tuoi criteri specifici. Questa formula moltiplica ogni coppia di peso e prezzo solo se l'elemento soddisfa la condizione (“Mela” in questo caso), li somma e divide per la somma dei pesi solo per quell'elemento.

Nota: Qui, B2:B18 è la colonna Frutta, C2:C18 è il Peso, e D2:D18 è il Prezzo. Sostituisci “Mela” con un altro elemento se necessario. Questo metodo funziona bene per filtrare in base a una condizione; se hai bisogno di filtrare in base a criteri multipli (ad esempio, tipo di frutta e fornitore), potrebbe essere necessaria una colonna di supporto o una formula più avanzata.

Dopo aver applicato la formula, potresti voler regolare i decimali per maggiore chiarezza. Seleziona la cella del risultato e usa i pulsanti Aumenta Decimali o Riduci Decimali nella scheda Home per cambiare i decimali visualizzati.

Se la formula restituisce un risultato inaspettato, conferma che i criteri abbiano corrispondenze all'interno del tuo intervallo target e cerca eventuali celle vuote o voci di testo nelle colonne destinate a contenere dati numerici.

In alcune situazioni, potresti dover calcolare frequentemente medie ponderate su intervalli che cambiano dimensione, contengono valori mancanti o richiedono filtri flessibili, come applicare più criteri contemporaneamente. Invece di aggiornare manualmente le formule o gli intervalli, automatizzare il calcolo con una macro VBA può farti risparmiare tempo e ridurre le possibilità di errori—particolarmente utile quando si ha a che fare con grandi dataset o dati aggiornati regolarmente.

Ecco come creare e utilizzare una macro VBA per le medie ponderate:

1. Clicca su Sviluppatore > Visual Basic (oppure premi Alt + F11) per aprire la finestra dell'editor Microsoft Visual Basic for Applications. Successivamente, clicca su Inserisci > Modulo, quindi incolla 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. Premi F5 (oppure clicca su Pulsante Esegui) per eseguire.
Ti verrà chiesto di selezionare gli intervalli passo dopo passo (intervallo dei criteri—questo può essere saltato se non necessario, intervallo dei pesi e intervallo dei valori). Puoi anche inserire criteri specifici per filtrare il calcolo o lasciarlo vuoto per considerare tutti i dati. La macro supporta intervalli di dati dinamici, rendendola pratica se la tua tabella cresce o cambia regolarmente.

Infine, riceverai una finestra di messaggio con il risultato della media ponderata.

Consigli:

• Questo approccio automatizza l'analisi ripetitiva della media ponderata e può essere ulteriormente espanso per gestire ulteriori filtri o opzioni di output.
• Assicurati che gli intervalli selezionati abbiano la stessa lunghezza e che i tipi di dati siano coerenti.
• Includi la gestione degli errori di base come mostrato (ad esempio, nei casi in cui non vengono trovati pesi validi o la somma dei pesi è zero).
• Se vuoi applicarlo solo alle righe filtrate/visibili, puoi migliorare ulteriormente il codice con l'enumerazione delle celle speciali.

Se incontri problemi di autorizzazione o sicurezza delle macro, assicurati che le macro siano abilitate nelle impostazioni di Excel prima di eseguire il codice.

Intervallo medio con arrotondamento in Excel

Tasso medio di variazione in Excel

Calcola la media/tasso annuale di crescita composto in Excel

Media mobile/scorrevole in Excel

Totale in esecuzione/media in Excel