Come calcolare la mediana in Excel ignorando zeri ed errori?
In molti compiti di analisi dei dati in Excel, calcolare con precisione la mediana è fondamentale per cogliere la tendenza centrale del proprio set di dati. Tuttavia, capita spesso che il set contenga zeri o valori di errore (come)#DIV/0!, #N/D, ecc.), che possono compromettere un calcolo diretto della mediana. Ad esempio, usando la formula standard =MEDIANA(intervallo), gli zeri vengono inclusi nel calcolo, mentre la presenza di celle non valide nell’intervallo genera un errore — con il rischio di ottenere risultati fuorvianti o di vedere il calcolo fallire del tutto, come illustrato di seguito.
Per risolvere questo problema, diverse soluzioni permettono di calcolare la mediana escludendo zeri ed errori, assicurando un’analisi precisa e affidabile. Questi approcci si adattano perfettamente a vari contesti — come la pulizia di dati da sondaggi, bilanci o misurazioni scientifiche — in cui è essenziale rimuovere zeri o valori errati per ottenere risultati significativi. Di seguito trovi guide pratiche passo-passo per ogni metodo disponibile in Excel, dalle formule dirette alle tecniche avanzate di automazione.
VBA: Mediana ignorando zeri ed errori (UDF)
Power Query: Mediana dopo aver filtrato zeri/errori
Mediana ignorando gli zeri
Quando l’intervallo contiene zeri che non si desidera includere nel calcolo della mediana—ad esempio valori mancanti rappresentati con 0—è possibile ricorrere a una formula matriciale per escluderli. Questa soluzione si rivela particolarmente utile nei set di dati in cui gli zeri fungono da segnaposto per informazioni non disponibili, piuttosto che da misurazioni reali.
Selezionare una cella in cui si desidera visualizzare la mediana (ad esempio, C2) e inserire la seguente formula:
=MEDIAN(IF(A2:A17<>0,A2:A17))Dopo aver inserito la formula, invece di premere semplicemente Invio, premi Ctrl + Maiusc + Invio per trasformarla in una formula matriciale (nelle parentesi graffe appariranno attorno alla formula nella Barra delle formule). In questo modo verranno considerati solo i valori non zero in A2:A17. Vedere lo screenshot:
Suggerimenti:
- Se si utilizza Excel 365 o Excel 2021 e versioni successive, basta semplicemente premere Invio, grazie al supporto per gli array dinamici.
- Assicurati che nell'intervallo sia presente almeno un valore numerico diverso da zero; in caso contrario, la formula restituirà l'errore #NUM!.
- Questa soluzione è perfetta per ripulire risposte a sondaggi, rapporti spese o dati di vendita da cui gli zeri devono essere esclusi nell’analisi.
Mediana ignorando gli errori
Valori di errore come #N/D, #DIV/0! o #VALORE! possono compromettere la funzione MEDIANA standard, bloccando la tua Analisi dei dati. Per calcolare in modo affidabile la mediana escludendo questi errori, utilizza la seguente formula matriciale.
Selezionare una qualsiasi cella in cui si desidera visualizzare il risultato e inserire la formula seguente:
=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))Dopo aver inserito la formula, premere Ctrl + Maiusc + Invio (a meno che non si utilizzi Excel 365, Excel 2021 o versioni successive, che supportano gli array dinamici). Questa formula include solo i valori in F2:F17 che sono numeri validi, ignorando completamente le celle contenenti errori.
Suggerimenti e avvertenze:
- Se tutte le celle contengono valori di errore, il risultato restituirà un errore #NUM!: assicurati che i dati includano almeno un numero valido.
- È possibile combinare criteri di esclusione—ad esempio, escludendo contemporaneamente zeri ed errori—annidando più condizioni.
- Questa formula si rivela particolarmente utile quando si lavora con dati importati, risultati di sondaggi o bilanci che potrebbero includere calcoli parziali o non riusciti.
VBA: Mediana ignorando zeri ed errori (UDF)
Per scenari in cui è necessario calcolare frequentemente la mediana escludendo sia gli zeri che gli errori, oppure quando si desidera una soluzione che eviti l’inserimento manuale di formule matriciali, è possibile ricorrere a una funzione VBA personalizzata (User-Defined Function, UDF). Questo approccio offre maggiore flessibilità, poiché la funzione personalizzata può integrare direttamente tutti i criteri di esclusione e può essere utilizzata come qualsiasi formula predefinita, risultando ideale per set di dati ampi o soggetti a frequenti aggiornamenti.
Configurazione dell’UDF:
- Fai clic sulla scheda Sviluppo in Excel. Se non è visibile, abilitala tramite File > Opzioni > Personalizza barra multifunzione.
- Fai clic su Visual Basic per aprire l’editor VBA.
- Nell’editor VBA, fai clic su Inserisci > Modulo per creare un nuovo modulo.
- Copiare e incollare il codice seguente nel modulo:
Function MedianIgnoreZeroError(rng As Range) As Variant
Dim cell As Range
Dim tempList() As Double
Dim count As Integer
count = 0
On Error Resume Next
xTitleId = "KutoolsforExcel"
For Each cell In rng
If IsNumeric(cell.Value) Then
If cell.Value <> 0 And Not IsError(cell.Value) Then
count = count + 1
ReDim Preserve tempList(1 To count)
tempList(count) = cell.Value
End If
End If
Next cell
On Error GoTo 0
If count = 0 Then
MedianIgnoreZeroError = CVErr(xlErrNum)
Else
MedianIgnoreZeroError = Application.WorksheetFunction.Median(tempList)
End If
End FunctionUtilizzo dell’UDF:
Dopo essere tornati in Excel, inserite semplicemente la formula =MedianIgnoreZeroError(A2:A17)in una qualsiasi cella (sostituendo)A2:A17 con il vostro intervallo di destinazione). A differenza delle formule matriciali, basta premere Invio: non è necessario usare Ctrl + Maiusc + Invio.
- Questo metodo si rivela particolarmente efficace con set di dati molto grandi, evita gli inconvenienti legati alle formule matriciali e può essere facilmente adattato per escludere altri valori indesiderati con ulteriori modifiche al codice.
- Se l’intervallo contiene solo zeri o errori, il risultato mostrerà #NUM!
- Se viene visualizzato l'errore #NOME?, assicurati che la macro VBA sia installata correttamente e che le macro siano abilitate nelle impostazioni di Excel.
Power Query: Mediana dopo aver filtrato zeri/errori
Power Query è uno strumento potente in Excel per importare, trasformare e analizzare dati — soprattutto quando l’obiettivo è ripulire e pre-elaborare set di dati ampi prima di eseguire calcoli come la mediana. Con Power Query puoi filtrare facilmente zeri ed errori, assicurandoti che nel calcolo siano inclusi solo numeri validi. Questo approccio è particolarmente vantaggioso se i tuoi dati di origine vengono aggiornati regolarmente o importati da sistemi esterni.
Passaggi per utilizzare Power Query per calcolare la mediana ignorando zeri ed errori:
- Seleziona una qualsiasi cella all’interno del tuo intervallo dati, quindi vai alla scheda Dati e fai clic su Da tabella/intervallo. Se i dati non sono già in formato tabella, Excel ti chiederà di crearne una: clicca su OK.
- Si aprirà la finestra dell’Editor di Power Query. Fare clic sulla freccia a discesa della colonna pertinente e deselezionare 0per filtrare i valori zero. (Per filtrare gli errori, fare clic con il pulsante destro sull’intestazione della colonna e scegliere)Rimuovi errori.)
- Una volta applicato il filtro, fai clic su Home > Chiudi e carica per inviare i dati ripuliti al foglio di lavoro.
- A questo punto, applica la formula standard
=MEDIANA()alla colonna contenente solo i valori filtrati, poiché i dati ora escludono tutti gli elementi indesiderati.
Questo metodo garantisce che i dati originali rimangano invariati, offre elevata ripetibilità con dati nuovi o aggiornati ed è particolarmente efficace per attività di reporting ricorrenti o quando si lavora con set di dati ampi o esterni. I flussi di lavoro di Power Query possono essere aggiornati con un solo clic ogni volta che il proprio Dati di origine cambia, riducendo al minimo interventi manuali e rischi di errore.
- Power Query è disponibile in Excel 2016 e versioni successive (oppure come componente aggiuntivo per Excel 2010 e 2013).
- Dopo la trasformazione, puoi eseguire calcoli sui dati puliti ottenuti, garantendo un’affidabilità superiore per le analisi successive.
In caso di risultati imprevisti, ricontrolla attentamente i passaggi di filtraggio in Power Query e assicurati che nei dati ripuliti siano rimasti valori numerici validi.
In sintesi, che si preferisca usare direttamente le formule matriciali, creare una soluzione VBA personalizzata per l’automazione o sfruttare Power Query per gestire flussi di lavoro più ampi, Excel offre diverse opzioni efficaci per calcolare la mediana ignorando zeri ed errori. Scegli il metodo più adatto alle dimensioni del tuo set di dati, alla frequenza di aggiornamento e alle tue preferenze operative per ottenere risultati affidabili e precisi.
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