Come fare la somma o la media quando ci sono errori

Le funzioni SOMMA e MEDIA sono strumenti essenziali in Excel per eseguire calcoli numerici rapidi e precisi. Tuttavia, quando nel tuo dataset sono presenti celle con errori come #DIV/0!, #N/D o #VALORE!, queste funzioni possono restituire a loro volta un errore anziché il risultato atteso. Questo non solo interrompe il flusso di lavoro, ma può anche portare a decisioni errate basate su dati inaccurati.

Immagina di dover calcolare il totale delle vendite trimestrali, ma a causa di un errore in una cella, l’intera somma risulta compromessa. Questo scenario è comune quando si lavora con grandi quantità di dati provenienti da varie fonti, dove gli errori possono facilmente infiltrarsi.

In questo articolo, esploreremo diverse tecniche per gestire e ignorare le celle con errori durante il calcolo di somme e medie.

Soluzione 1 – funzione AGGREGA

Supponiamo di avere i seguenti valori nelle celle A1:A5:

• A1: 50
• A2: 75
• A3: #DIV/0! (errore)
• A4: 100
• A5: 125

Calcolare la somma ignorando gli errori

Per sommare i valori ignorando la cella con errore, utilizza la formula:

=AGGREGA(9;6;A1:A5)

• 9 indica la funzione SOMMA.
• 6 specifica di ignorare i subtotali e gli errori.

Passo dopo passo:

1. Seleziona la cella dove vuoi visualizzare la somma (ad esempio, B1).
2. Digita la formula: =AGGREGA(9;6;A1:A5).
3. Premi Invio. Il risultato sarà 350, poiché la funzione ha ignorato l’errore in A3.

Calcolare la media ignorando gli errori

Per calcolare la media dei valori validi:

=AGGREGA(1;6;A1:A5)

1 indica la funzione MEDIA.

Passo dopo passo:

1. Seleziona la cella dove vuoi visualizzare la media (ad esempio, B2).
2. Digita la formula: =AGGREGA(1;6;A1:A5).
3. Premi Invio. Il risultato sarà 87,5, che è la media dei quattro valori numerici (50, 75, 100, 125).

Perché funziona?

La funzione AGGREGA, con l’opzione 6, ignora automaticamente le celle che contengono errori o subtotali. Questo significa che gli errori presenti nell’intervallo non influenzeranno il risultato finale del calcolo.

Utilizzare AGGREGA con altri calcoli

AGGREGA non si limita a somme e medie. Ecco alcuni esempi di altre funzioni utili:

Massimo valore ignorando errori:

=AGGREGA(4;6;A1:A5)

Minimo valore ignorando errori:

=AGGREGA(5;6;A1:A5)

Deviazione standard ignorando errori:

=AGGREGA(7;6;A1:A5)

La funzione AGGREGA: capiamone di più

La funzione AGGREGA è stata introdotta a partire da Excel 2010 e offre una grande flessibilità per eseguire calcoli statistici e matematici. La sua sintassi generale è la seguente:

=AGGREGA(funzione; opzioni; matrice1; [matrice2]; ...)

• funzione: un numero da 1 a 19 che specifica quale operazione eseguire (ad esempio, 9 per SOMMA, 1 per MEDIA).
• opzioni: un numero che indica quali elementi ignorare durante il calcolo (ad esempio, 6 per ignorare errori e righe nascoste).
• matrice1, matrice2, …: gli intervalli o le matrici su cui eseguire il calcolo.

Ecco una tabella delle funzioni disponibili:

E le opzioni per ignorare elementi:

Soluzione 2 – funzioni SOMMA.SE e MEDIA.SE

Le funzioni SOMMA.SE e MEDIA.SE sono strumenti potenti che ti permettono di sommare o calcolare la media di un intervallo di celle in base a specifici criteri. Sebbene siano comunemente utilizzate per filtrare dati secondo determinate condizioni, possono essere impiegate anche per escludere le celle con errori dai tuoi calcoli.

Metodo con colonna di supporto

1. Creare una colonna di supporto: In una colonna adiacente ai dati originali, utilizza una formula per verificare la presenza di errori e restituire solo i valori validi.Esempio: supponendo che i tuoi dati siano nelle celle A1, inserisci questa formula in B1 e copiala fino a B5:scssCopia codice=SE(VAL.ERRORE(A1);0;A1) Questa formula controlla se la cella A1 contiene un errore. Se sì, restituisce 0; altrimenti, restituisce il valore di A1.
2. Somma dei valori validi: Ora puoi utilizzare la funzione SOMMA sulla colonna di supporto B1:scssCopia codice=SOMMA(B1:B5) Questo calcolerà la somma ignorando le celle con errori, poiché nella colonna di supporto gli errori sono stati sostituiti con 0.

Esempio pratico

Supponiamo i seguenti dati:

• A1: 50
• A2: 75
• A3: #DIV/0! (errore)
• A4: 100
• A5: 125

Passaggi:

1. In B1, inserisci la formula di supporto:scssCopia codice=SE(VAL.ERRORE(A1);0;A1) Risultati in B1:
   • B1: 50
   • B2: 75
   • B3: 0
   • B4: 100
   • B5: 125
2. Calcola la somma:scssCopia codice=SOMMA(B1:B5) → Risultato: 350

Utilizzo di MEDIA e SE per escludere gli errori

Per calcolare la media senza considerare le celle con errori, puoi seguire un approccio simile utilizzando una colonna di supporto o una formula matriciale.

Metodo con formula matriciale

Puoi utilizzare una formula matriciale che esclude le celle con errori:

=MEDIA(SE(VAL.ERRORE(A1:A5);"";A1:A5))

Nota: Dopo aver inserito la formula, premi Ctrl + Maiusc + Invio per confermarla come formula matriciale nelle versioni di Excel precedenti al 365.

Spiegazione:

• SE(VAL.ERRORE(A1);””;A1): Crea un array che contiene solo i valori validi, sostituendo gli errori con stringhe vuote.
• MEDIA(…): Calcola la media dei valori nell’array, ignorando le stringhe vuote.

Esempio pratico

Usando i dati in A1:

1. Inserisci la formula matriciale:
   =MEDIA(SE(VAL.ERRORE(A1:A5);"";A1:A5))
2. Premi Ctrl + Maiusc + Invio.Risultato: 87,5

Questo è la media dei valori validi (50, 75, 100, 125), ignorando l’errore in A3.

Utilizzo della funzione SOMMA.SE con funzioni logiche

Se desideri evitare l’uso di una colonna di supporto, puoi incorporare la logica direttamente nella funzione SOMMA utilizzando una formula matriciale:

=SOMMA(SE(VAL.ERRORE(A1:A5);0;A1:A5))

E per la media:

=MEDIA(SE(VAL.ERRORE(A1:A5);"";A1:A5))

Ricorda di confermare queste formule come matriciali con Ctrl + Maiusc + Invio se utilizzi una versione di Excel precedente al 365.

Perché gli errori nelle celle causano problemi nelle funzioni SOMMA e MEDIA

Comportamento standard di Excel

In Excel, le funzioni SOMMA e MEDIA sono progettate per elaborare intervalli di celle contenenti valori numerici. Quando queste funzioni incontrano una cella con un errore, come #DIV/0! o #N/D, restituiscono a loro volta un errore invece del risultato numerico atteso. Questo accade perché gli errori interrompono il processo di calcolo, impedendo alla funzione di aggregare correttamente i dati.

Tipi comuni di errori

Ecco alcuni degli errori più frequenti che possono apparire nelle celle di Excel:

• #DIV/0!: Si verifica quando un numero viene diviso per zero.
• #N/D: Indica che un valore necessario non è disponibile.
• #VALORE!: Appare quando il tipo di dati non è corretto per l’operazione richiesta.
• #NOME?: Compare quando Excel non riconosce il testo in una formula.
• #RIF!: Indica un riferimento di cella non valido, spesso a causa dell’eliminazione di celle.
• #NUM!: Si presenta quando c’è un problema con un numero in una formula o funzione.
• #NULL!: Indica un’intersezione non valida di aree di celle.

Esempio pratico

Supponiamo di avere un elenco di numeri nelle celle A1:

• A1: 50
• A2: 75
• A3: #DIV/0!
• A4: 100
• A5: 125

Se tenti di calcolare la somma di questi valori usando la formula =SOMMA(A1:A5), Excel restituirà #DIV/0! invece del totale atteso di 350. Questo perché l’errore presente in A3 interrompe il calcolo della funzione SOMMA.

Allo stesso modo, se provi a calcolare la media con =MEDIA(A1:A5), otterrai lo stesso errore #DIV/0!, impedendoti di ottenere la media dei valori numerici disponibili.

Perché accade questo?

Le funzioni SOMMA e MEDIA non sono in grado di ignorare automaticamente le celle con errori. Quando incontrano un errore in qualsiasi cella dell’intervallo specificato, l’intera funzione fallisce, restituendo l’errore stesso.

Questo comportamento è progettato per segnalare immediatamente la presenza di dati problematici che potrebbero influenzare l’accuratezza dei calcoli.