Come calcolare la media corretta in presenza di valori mancanti in Excel?
Quando si lavora con dati in Excel, è comune imbattersi in intervalli che contengono valori mancanti o celle riempite con indicatori di errore come #N/D o #DIV/0!. Se si applica la funzione standard di Excel MEDIA a un intervallo di questo tipo, la formula restituisce un errore anziché ignorare le celle problematiche. Di conseguenza, i calcoli possono risultare non validi o inutilizzabili ogni volta che i dati sono incompleti o presentano errori, come illustrato nello screenshot seguente. Saper calcolare correttamente la media ignorando questi valori mancanti o errati è fondamentale, soprattutto nei report aziendali, nella ricerca accademica o in qualsiasi scenario che coinvolga set di dati ampi e complessi. Di seguito trovi soluzioni pratiche e istruzioni dettagliate per ottenere una media affidabile in queste situazioni, garantendo l’integrità della tua analisi dei dati.
Media con valori mancanti
Per calcolare una media in Excel ignorando i valori mancanti o le celle con errori, puoi utilizzare diverse formule progettate per escludere in modo intelligente le celle non numeriche e quelle contenenti errori. Questa funzionalità è fondamentale in contesti come l’analisi dei risultati di sondaggi (dove alcune risposte potrebbero essere incomplete), i dati sulle vendite nel tempo (con mesi privi di valori) o la registrazione di dati scientifici (in cui alcune misurazioni potrebbero mancare o risultare non valide). Di seguito trovi due soluzioni affidabili, complete di tutti i dettagli necessari.
Opzione 1: Funzione AGGREGA
La funzione AGGREGA è particolarmente adatta a situazioni in cui ci si aspettano errori o valori nascosti nel tuo intervallo dati, poiché ti permette di configurare esattamente quali tipi di dati ignorare. È ideale quando consolidi dati provenienti da più fonti o quando il set di dati viene aggiornato frequentemente con voci non uniformi.
1. Seleziona la cella in cui vuoi visualizzare la media e inserisci la seguente formula:
=AGGREGATE(1,6,A2:C2)Qui, 1 specifica l’operazione MEDIA e 6 indica a Excel di ignorare i valori di errore nell’intervallo A2:C2.
2. Dopo aver inserito la formula, premi Maiusc + Ctrl + Invio (per le versioni precedenti di Excel) oppure semplicemente Invio se utilizzi Excel 365 o Excel 2019, poiché queste versioni supportano gli array dinamici senza combinazioni di tasti aggiuntive.
Vantaggi: Facile da usare: ignora automaticamente gli errori e restituisce risultati accurati. Funziona perfettamente con intervalli che includono valori di errore.
Limitazioni: Disponibile solo da Excel 2010 in poi. Le versioni precedenti di Excel non supportano la funzione AGGREGA.
Opzione 2: Formula matriciale MEDIA con SE e VAL.NUMERO
In alternativa, puoi calcolare la media considerando esclusivamente i valori numerici — ignorando automaticamente errori e celle vuote — mediante una formula matriciale. Questo approccio è ideale quando devi includere soltanto numeri validi, come spesso richiesto nei report mensili, nei sistemi di valutazione o nei fogli finanziari.
1. Nella cella in cui vuoi visualizzare la media, digita questa formula:
=AVERAGE(IF(ISNUMBER(A2:C2),(A2:C2)))Questa formula esamina ogni cella in A2:C2, conserva solo i valori numerici e ne calcola la media.
2. Una volta inserita, tieni premuti i tasti Ctrl e Maiusc, quindi premi Invio per trasformarla in una formula matriciale nelle versioni precedenti di Excel (confermato dalla comparsa delle parentesi graffe nella Barra delle formule). In Excel 365 e Excel 2019, è sufficiente premere semplicemente Invio.
Suggerimento: Se i tuoi dati si estendono su più righe e hai bisogno di risultati per ciascuna di esse, usa il quadratino di riempimento (il piccolo quadrato nell’angolo in basso a destra della cella selezionata) per trascinare la formula verso il basso. In questo modo, i riferimenti si adatteranno automaticamente a ogni riga.
Vantaggi: Funziona con tutte le versioni di Excel, ignora valori non numerici ed errori ed è affidabile in svariati scenari, anche in presenza di errori o valori mancanti.
Limitazioni: Nelle versioni precedenti di Excel, richiede l’inserimento come formula matriciale, il che può risultare confusionario. Un errore di digitazione può causare un errore #VALORE! se inserita in modo non corretto.
Indipendentemente dal metodo scelto, assicurati che l’intervallo selezionato includa esclusivamente i dati desiderati: colonne o righe aggiuntive non richieste potrebbero compromettere l’accuratezza dei risultati. In caso di errori persistenti, ricontrolla il metodo di inserimento della formula — in particolare per le formule matriciali nelle versioni precedenti di Excel — verifica di aver selezionato correttamente l’intervallo e accertati che non siano presenti celle unite o nascoste, che potrebbero interferire con il calcolo.
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