Come calcolare la media dei valori in una colonna in base a criteri specificati in un’altra colonna in Excel?
In molti scenari pratici di Excel, è spesso necessario calcolare la media dei valori di una colonna, raggruppati o filtrati in base ai corrispondenti valori di un’altra colonna. Ad esempio, si potrebbe voler determinare la media delle vendite per ciascun venditore o per ogni area geografica, come illustrato nello screenshot seguente. Questo tipo di calcolo è comunemente utilizzato nei report riassuntivi, nelle analisi delle prestazioni e nelle revisioni dei dati. In questo articolo, presenteremo diversi metodi efficaci per ottenere tale risultato, consentendoti di scegliere l’approccio più adatto alle tue esigenze e al tuo livello di competenza.
Calcolare la media in una colonna in base a valori uguali in un'altra colonna con formule
Calcolare la media in una colonna in base a valori uguali in un'altra colonna con Kutools per Excel
Calcolare la media per gruppo utilizzando Tabella pivot
Automatizzare il calcolo della media raggruppata con una macro VBA
Calcolare la media in una colonna in base a valori uguali in un'altra colonna con formule
Uno dei metodi più semplici per calcolare la media di un gruppo in base a un’altra colonna in Excel è utilizzare formule condizionali come MEDIA.SE o MEDIA.PIÙ.SE. Questo approccio si rivela particolarmente efficace quando si cercano risultati mirati in base a criteri specifici, ad esempio la media delle vendite di una singola città o di un determinato venditore.
1. Seleziona una cella vuota in cui visualizzare il risultato, quindi inserisci la formula seguente e premi Invio:
=AVERAGEIF(B2:B13,E2,C2:C13)
Spiegazione dei parametri: Nella formula sopra indicata, B2:B13 è l'intervallo contenente i criteri da verificare (ad esempio, città o venditore), E2 è il valore specifico con cui confrontare (ad esempio, «Owenton») e C2:C13 è l'intervallo contenente i valori numerici di cui si desidera calcolare la media.
Dopo aver premuto Invio, otterrai immediatamente la media per il gruppo specificato in E2 (ad esempio, la media delle vendite per «Owenton»).
Per calcolare la media per ciascun valore univoco nella colonna dei criteri, basta modificare il valore nella cella del criterio (E2) in base alle proprie esigenze oppure copiare la formula verso il basso se si dispone già di un elenco di valori univoci.
Suggerimento pratico: Per dataset di grandi dimensioni o con numerosi gruppi univoci, abbinare questa formula a un elenco di valori univoci (ottenuto con strumenti come «Rimuovi duplicati» o la funzione UNICI di Excel in Office 365 e Excel 2021) permette di calcolare simultaneamente tutte le medie di gruppo in modo più rapido. Assicurati che gli intervalli nella formula coprano tutti i dati previsti e rimangano correttamente allineati quando copi la formula.
Errori comuni e risoluzione dei problemi:
- Se si verifica un errore #DIV/0!, assicurati che il valore del criterio sia effettivamente presente nell’intervallo selezionato.
- Assicurati che l'intervallo numerico contenga esclusivamente numeri validi: celle con testo o vuote potrebbero compromettere il calcolo.
Calcolare la media in una colonna in base a valori uguali in un'altra colonna con Kutools per Excel
Se desideri calcolare automaticamente la media per tutti i valori univoci di una colonna senza dover inserire ripetutamente formule o applicare filtri manualmente, Kutools per Excel ti offre un metodo semplice e rapido per farlo. Questa soluzione è particolarmente vantaggiosa quando lavori con elenchi estesi o dataset complessi.
1. Seleziona l’intero intervallo di dati che include sia la colonna di raggruppamento sia quella numerica per cui desideri calcolare la media. Poi vai su Kutools > Unisci e Dividi > Unione avanzata righe.
2. Nella finestra di dialogo Combinare righe in base alla colonna, procedi come segue:
- Seleziona la colonna in base alla quale desideri raggruppare (ad esempio, Città o Venditore) e fai clic sul pulsante Chiave primaria per definirla come campo di raggruppamento.
- Seleziona la colonna numerica di cui vuoi calcolare la media, quindi fai clic su Calcola > Media.
Suggerimento: Per altre colonne (ad esempio, quelle con date), puoi specificare come combinare i relativi valori (ad esempio, unendoli con una virgola). - Fare clic su OK per eseguire l'operazione.
Kutools raggruppa immediatamente i dati in base alla chiave selezionata e restituisce la media di ciascun gruppo nella colonna numerica.
L'elaborazione batch di Kutools è perfetta per chi analizza regolarmente statistiche raggruppate, come report mensili, riepiloghi dipartimentali o altri calcoli su più gruppi. Essendo un componente aggiuntivo, Kutools non altera la struttura originale dei dati e offre una funzione di anteprima per verificare i raggruppamenti prima di applicare le modifiche.
Note e suggerimenti:
- Prima di eseguire lo strumento, assicurati che l’intervallo selezionato non contenga righe vuote.
- Se hai bisogno delle medie raggruppate in un’altra posizione, usa Copia e Incolla per trasferire i risultati dopo il calcolo.
- Nei dataset molto grandi, assicurati che le colonne di raggruppamento e quelle numeriche siano assegnate correttamente per evitare confusioni.
Kutools per Excel– Potenzia Excel con oltre 300 strumenti essenziali, rendendo il tuo lavoro più veloce e semplice, e sfrutta le funzionalità basate sull’IA per un’elaborazione dati più intelligente e una maggiore produttività.Scaricalo ora
Calcolare la media per gruppo utilizzando Tabella pivot
Le tabelle pivot offrono un modo potente e integrato per riepilogare, raggruppare e analizzare i dati – inclusa la media per gruppo – senza dover ricorrere a formule o componenti aggiuntivi esterni. Questa tecnica è ideale quando si desidera una vista interattiva di medie e totali suddivisi per categorie diverse ed è perfetta sia per dataset piccoli che per quelli molto grandi.
Come configurare una Tabella pivot per calcolare le medie di gruppo:
- Seleziona una qualsiasi cella del dataset, poi vai su Inserisci > Tabella pivot. Nella finestra di dialogo, scegli dove vuoi che la tabella pivot venga visualizzata (in un nuovo foglio o in un foglio di lavoro esistente) e fai clic su OK.
- Nel riquadro Campi tabella pivot, trascina la colonna in base alla quale desideri raggruppare nell'area Righe (ad esempio, «Città» o «Venditore»).
- Trascina la colonna numerica di cui desideri calcolare la media (ad esempio, «Vendite») nell'area Valori. Per impostazione predefinita, Excel potrebbe calcolare la somma; per modificarla, fai clic sul campo dei valori, seleziona Impostazioni campo e scegli Media.
La tabella pivot visualizzerà immediatamente il valore medio per ciascun gruppo. Potrai filtrare, ordinare e formattare il report in modo rapido e intuitivo, riducendo al minimo il rischio di errori legati alle formule.
Vantaggi: Interattività, gestione elegante di grandi volumi di dati e possibilità di riepilogare più statistiche contemporaneamente.
Svantaggi: Il risultato viene visualizzato in formato tabella pivot, non come elenco semplice; richiede un aggiornamento occasionale se i dati di origine cambiano.
Suggerimento: Facendo doppio clic su qualsiasi cella di riepilogo nella tabella pivot, si apre un nuovo foglio di lavoro con i dati sottostanti relativi a quel gruppo, facilitando la verifica dei dettagli o l’individuazione di eventuali discrepanze.
Problemi comuni:
- Se non è possibile calcolare la media, assicurati che il campo dei valori sia impostato su «Media» in Impostazioni campo.
- Controlla i dati di origine per individuare eventuali righe vuote o colonne aggiuntive che potrebbero compromettere il layout della tabella pivot.
Automatizzare il calcolo della media raggruppata con una macro VBA
Per chi deve calcolare frequentemente medie per numerosi gruppi o desidera generare automaticamente un riepilogo, una macro VBA può far risparmiare un notevole quantitativo di tempo manuale. VBA si rivela particolarmente utile quando la struttura dei dati rimane coerente o quando si vogliono produrre report riassuntivi ripetuti con un semplice clic.
Prima di iniziare, assicurati di salvare la cartella di lavoro e di abilitare le macro. Ecco come procedere:
1. Fai clic su Strumenti di sviluppo > Visual Basic per aprire l'editor VBA. Nell'editor, fai clic su Inserisci > Modulo per creare un nuovo modulo di codice. Incolla il codice riportato di seguito nel modulo:
Sub GroupAverageSummary()
Dim srcSheet As Worksheet
Dim dstSheet As Worksheet
Dim dict As Object
Dim groupCol As Range, valueCol As Range
Dim lastRow As Long
Dim i As Long
Dim groupKey As Variant
Dim sumArr As Object, countArr As Object
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set srcSheet = ActiveSheet
Set dict = CreateObject("Scripting.Dictionary")
' Prompt user to select group (criteria) column
Set groupCol = Application.InputBox("Select the group (criteria) column:", xTitleId, Type:=8)
If groupCol Is Nothing Then Exit Sub
' Prompt user to select value column
Set valueCol = Application.InputBox("Select the value column to average:", xTitleId, Type:=8)
If valueCol Is Nothing Then Exit Sub
Set sumArr = CreateObject("Scripting.Dictionary")
Set countArr = CreateObject("Scripting.Dictionary")
For i = 1 To groupCol.Rows.Count
groupKey = groupCol.Cells(i, 1).Value
If groupKey <> "" And IsNumeric(valueCol.Cells(i, 1).Value) Then
If Not dict.Exists(groupKey) Then
dict.Add groupKey, 0
sumArr.Add groupKey, 0
countArr.Add groupKey, 0
End If
sumArr(groupKey) = sumArr(groupKey) + valueCol.Cells(i, 1).Value
countArr(groupKey) = countArr(groupKey) + 1
End If
Next
' Output result to a new worksheet
Set dstSheet = Worksheets.Add
dstSheet.Name = "Group Average Summary"
dstSheet.Cells(1, 1).Value = "Group"
dstSheet.Cells(1, 2).Value = "Average"
i = 2
For Each groupKey In dict.Keys
dstSheet.Cells(i, 1).Value = groupKey
dstSheet.Cells(i, 2).Value = sumArr(groupKey) / countArr(groupKey)
i = i + 1
Next
End Sub2. Dopo aver inserito il codice, chiudi l'editor VBA. Torna a Excel, premi Alt+F8, seleziona GroupAverageSummary nell'elenco e fai clic su Esegui. La macro ti chiederà di selezionare la colonna del gruppo (criterio) e la colonna dei valori (numerici). Una volta effettuate le selezioni, genererà automaticamente un nuovo foglio di lavoro denominato «Group Average Summary», che mostra ciascun gruppo univoco con i relativi valori medi.
Note sui parametri e sul funzionamento:
- Assicurati che le colonne di raggruppamento e dei valori abbiano la stessa lunghezza e contengano dati validi, evitando selezioni parziali.
- Questa macro può essere personalizzata per gestire raggruppamenti più avanzati o statistiche riassuntive aggiuntive, in base alle tue esigenze.
- Se il foglio contiene già un riepilogo «Group Average Summary» Nome del foglio di lavoro, la macro creerà un nuovo foglio con il nome predefinito Nome del foglio di lavoro.
Risoluzione dei problemi:
- In caso di messaggi come «subscript out of range» o simili, assicurati che gli intervalli selezionati siano allineati correttamente e si trovino nello stesso foglio di lavoro.
- Per risultati ottimali, assicurati che la colonna dei valori contenga esclusivamente dati numerici: la macro ignorerà automaticamente le celle con testo o vuote all’interno dell’intervallo numerico.
Questa macro è perfetta per l’elaborazione in batch, la generazione automatica di report o ogni volta che devi riepilogare frequentemente set di dati nuovi o aggiornati.
Demo: Calcola la media in una colonna in base allo stesso valore in un'altra colonna con Kutools per Excel
Migliori Strumenti per la Produttività in Office
Potenzia le tue competenze in Excel con Kutools per Excel e sperimenta un’efficienza mai vista prima.Kutools per Excel offre oltre 300 funzionalità avanzate per aumentare la produttività e Risparmia tempo.Clicca qui per ottenere la funzionalità di cui hai più bisogno...
Office Tab Porta l'interfaccia a schede in Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e la lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede all’interno della stessa finestra, invece che in finestre separate.
- Aumenta la tua produttività del 50 % e risparmia centinaia di clic del mouse ogni giorno!
Tutti i componenti aggiuntivi di Kutools in un unico programma di installazione.
Kutools for Office è la suite che include componenti aggiuntivi per Excel, Word, Outlook e PowerPoint, oltre a Office Tab Pro: la soluzione ideale per i team che lavorano su diverse app di Office.
- Suite completa— componenti aggiuntivi per Excel, Word, Outlook e PowerPoint + Office Tab Pro
- Un unico programma di installazione, una sola licenza— configurazione in pochi minuti (pronto per MSI)
- Funziona meglio insieme— produttività ottimizzata tra le app di Office
- Prova gratuita di 30 giorni con tutte le funzionalità— nessuna registrazione, nessuna carta di credito
- Miglior rapporto qualità-prezzo— risparmia rispetto all’acquisto dei singoli componenti aggiuntivi