Come calcolare la mediana ignorando gli zeri o gli errori in Excel?
In molte attività di analisi dei dati in Excel, il calcolo accurato della mediana è essenziale per comprendere la tendenza centrale del tuo set di dati. Tuttavia, a volte il tuo set di dati contiene zeri o valori di errore (come #DIV/0!, #N/Aecc.), che possono interferire con un calcolo diretto della mediana. Ad esempio, utilizzando la formula standard =MEDIAN(range) verranno inclusi gli zeri nel calcolo e restituirà un errore se sono presenti celle non valide nell'intervallo, potenzialmente portando a risultati fuorvianti o fallimenti di calcolo, come illustrato di seguito.
Per risolvere questo problema, diverse soluzioni possono aiutarti a calcolare la mediana escludendo gli zeri o gli errori, garantendo che la tua analisi sia sia accurata che robusta. Queste soluzioni sono adatte a vari scenari, come la pulizia dei dati di un sondaggio, report finanziari o misurazioni scientifiche dove gli zeri o gli errori devono essere rimossi per ottenere risultati significativi. Di seguito, troverai guide pratiche passo-passo per ogni metodo disponibile in Excel, che vanno 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 il tuo intervallo contiene zeri che non desideri considerare nel calcolo della mediana — come i valori mancanti rappresentati come 0 — puoi utilizzare una formula matriciale per escludere gli zeri. Questo è particolarmente utile nei dataset dove gli zeri sono segnaposto per dati non disponibili piuttosto che misurazioni reali.
Seleziona una cella in cui desideri visualizzare la mediana (ad esempio, C2) e inserisci la seguente formula:
=MEDIAN(IF(A2:A17<>0,A2:A17))Dopo aver inserito la formula, invece di premere solo Invio, premi Ctrl + Shift + Enter per renderla una formula matriciale (vedrai apparire delle parentesi graffe attorno alla formula nella barra della formula). Questo assicura che solo i valori non nulli in A2:A17 siano considerati per il calcolo della mediana. Vedi screenshot:
Suggerimenti:
- Se stai usando Excel 365 o Excel 2021 e versioni successive, premere Invio da solo è sufficiente, grazie al supporto delle matrici dinamiche.
- Assicurati che ci sia almeno un valore numerico non nullo nell'intervallo, altrimenti la formula restituirà un errore #NUM!.
- Questa soluzione è ideale per pulire le risposte ai sondaggi, i report delle spese o i dati di vendita dove gli zeri dovrebbero essere esclusi dall'analisi.
Mediana ignorando gli errori
I valori di errore come #N/A, #DIV/0!, o #VALUE! possono causare alla funzione mediana standard di restituire un errore, interrompendo la tua analisi dei dati. Per calcolare in sicurezza la mediana escludendo questi errori, puoi utilizzare la seguente formula matriciale.
Seleziona una qualsiasi cella in cui desideri visualizzare il tuo risultato e inserisci la formula sottostante:
=MEDIAN(IF(ISNUMBER(F2:F17),F2:F17))Dopo aver inserito la formula, premi Ctrl + Shift + Enter (a meno che tu non stia usando Excel 365/Excel 2021 o versioni successive, che consentono le matrici dinamiche). Questa formula include solo quei valori in F2:F17 che sono numeri genuini — ignorando completamente tutte le celle di errore.
Suggerimenti e precauzioni:
- Se tutte le celle sono valori di errore, il risultato restituirà un errore #NUM! — assicurati che i tuoi dati includano almeno un numero valido.
- Puoi combinare criteri di esclusione (ad esempio, escludendo sia zeri che errori) annidando condizioni.
- Questa formula è particolarmente utile quando si lavora con dati importati, risultati di sondaggi o dichiarazioni finanziarie che possono contenere calcoli parziali o falliti.
VBA: Mediana ignorando zeri ed errori (UDF)
Per scenari in cui hai bisogno frequentemente di calcolare la mediana ignorando sia zeri che errori, oppure richiedi una soluzione che eviti l'inserimento manuale di formule matriciali, puoi utilizzare una funzione VBA personalizzata (User-Defined Function, UDF). Questo approccio offre maggiore flessibilità perché la funzione personalizzata può incapsulare tutti i criteri di esclusione e può essere usata proprio come qualsiasi formula incorporata, rendendola adatta per grandi dataset o dataset aggiornati frequentemente.
Come impostare la UDF:
- Clicca sulla scheda Sviluppatore in Excel. Se non è disponibile, abilitala tramite File > Opzioni > Personalizza Barra Multifunzione.
- Clicca su Visual Basic per aprire l'editor VBA.
- Nell'editor VBA, clicca su Inserisci > Modulo per creare un nuovo modulo.
- Copia e incolla il seguente codice 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 FunctionCome utilizzare la UDF:
Dopo essere tornato su Excel, inserisci semplicemente la formula =MedianIgnoreZeroError(A2:A17) in qualsiasi cella (sostituisci A2:A17 con il tuo intervallo di destinazione). A differenza delle formule matriciali, devi solo premere Invio — non c'è bisogno di Ctrl + Shift + Enter.
- Questo metodo funziona bene per dataset molto grandi, evita le peculiarità delle formule matriciali e può essere adattato per ignorare altri valori indesiderati modificando ulteriormente il codice.
- Se l'intervallo contiene solo zeri o errori, il risultato mostrerà #NUM!
- Se ricevi un errore #NAME?, controlla 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 — specialmente quando il tuo obiettivo è pulire e pre-elaborare grandi dataset prima di eseguire calcoli come la mediana. Con Power Query, puoi facilmente filtrare gli zeri e gli errori, assicurandoti che solo numeri validi rimangano nel tuo calcolo. Questo approccio è particolarmente vantaggioso se i tuoi dati di origine sono regolarmente aggiornati o importati da sistemi esterni.
Passaggi per usare Power Query per calcolare la mediana ignorando zeri ed errori:
- Seleziona una qualsiasi cella all'interno del tuo intervallo di dati, poi vai alla scheda Dati e clicca Da Tabella/Intervallo. Se i tuoi dati non sono già in formato tabella, Excel ti chiederà di crearne una — clicca OK.
- La finestra dell'Editor di Power Query si aprirà. Clicca la freccia a discesa per la colonna rilevante e deseleziona 0 per filtrare i valori zero. (Per il filtro degli errori, clicca con il tasto destro sull'intestazione della colonna, scegli Rimuovi Errori.)
- Una volta filtrato, clicca Home > Chiudi & Carica per inviare i dati puliti nuovamente al tuo foglio di lavoro.
- Ora, applica la formula standard
=MEDIAN()alla colonna con i valori filtrati soltanto, poiché i dati ora escludono tutti gli elementi indesiderati.
Questo metodo garantisce che i tuoi dati originali rimangano invariati, offre una forte ripetibilità con nuovi o aggiornati dati ed è particolarmente efficace per compiti di reporting ricorrenti o quando si lavora con grandi dataset o dati esterni. I flussi di lavoro di Power Query possono essere aggiornati con un solo clic ogni volta che cambiano i tuoi dati di origine, minimizzando l'intervento manuale e il rischio di errori.
- Power Query è disponibile in Excel 2016 e versioni successive (o come componente aggiuntivo per Excel 2010 e 2013).
- Dopo la trasformazione, i calcoli possono essere eseguiti sui dati puliti risultanti, fornendo maggiore affidabilità per le analisi a valle.
Se si verificano risultati inaspettati, verifica nuovamente i passaggi di filtro in Power Query e conferma che rimangano valori numerici validi nei tuoi dati puliti.
In sintesi, che tu preferisca utilizzare direttamente formule matriciali, creare una soluzione VBA personalizzata per l'automazione o sfruttare Power Query per una maggiore automazione dei flussi di lavoro, Excel offre molteplici opzioni pratiche per calcolare la mediana ignorando zeri o errori. Scegli il metodo che meglio si adatta alla dimensione del tuo dataset, alla frequenza di aggiornamento e alle preferenze del flusso di lavoro per ottenere output affidabili e accurati.
I 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 oltre300 funzionalità avanzate per aumentare la produttività e farti risparmiare tempo. Clicca qui per ottenere la funzione di cui hai più bisogno...
Office Tab porta le schede su Office e rende il tuo lavoro molto più semplice
- Abilita la modifica e lettura a schede in Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
- Apri e crea più documenti in nuove schede della stessa finestra invece che in nuove finestre.
- Aumenta la produttività del50% e riduce centinaia di clic del mouse ogni giorno!
Tutti gli add-in Kutools. Un solo programma di installazione
La suite Kutools for Office include add-in per Excel, Word, Outlook & PowerPoint più Office Tab Pro, ideale per i team che lavorano su più app di Office.
- Suite tutto-in-uno — Add-in per Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Un solo programma di installazione, una sola licenza — configurazione in pochi minuti (pronto per MSI)
- Funzionano meglio insieme — produttività ottimizzata su tutte le app Office
- Prova completa30 giorni — nessuna registrazione, nessuna carta di credito
- Massimo risparmio — costa meno rispetto all’acquisto singolo degli add-in