Come calcolare la media di celle non contigue escludendo gli zeri in Excel?
Durante l’utilizzo di dati in Excel, capita spesso di dover calcolare la media di un insieme di numeri in cui non tutte le celle rilevanti sono adiacenti e si desidera escludere Valori zero (che potrebbero rappresentare dati mancanti o valori azzerati). Ad esempio, in un foglio vendite o inventario, alcuni articoli potrebbero avere scorte a zero o nessuna vendita in determinate date; in questi casi, il semplice calcolo della media potrebbe non fornire un’indicazione fedele dell’andamento dei dati, poiché gli zeri ne alterano il risultato. Escluderli permette di analizzare soltanto i dati attivi.
Immagina di avere una tabella di frutta come nell’immagine seguente e di voler calcolare la media dei valori nella colonna **Quantità**, escludendo gli zeri. I metodi seguenti ti illustrano diversi approcci pratici per ottenere questo risultato in Excel, offrendoti flessibilità a seconda che i tuoi dati siano distribuiti in celle non adiacenti, sparsi su più righe o colonne, oppure quando preferisci sfruttare funzionalità integrate di Excel o formule semplici ed efficaci.
- Calcolare la media di celle non adiacenti escludendo gli zeri con una formula
- Calcolare la media di celle non adiacenti escludendo gli zeri con una macro VBA
- Calcolare la media di celle non adiacenti escludendo gli zeri utilizzando una funzione predefinita di Excel
Calcolare la media di celle non adiacenti escludendo gli zeri con una formula
Questo metodo utilizza una formula matriciale per calcolare rapidamente la media di celle non adiacenti (ad esempio, ogni altra cella in una riga o colonna), escludendo eventuali zeri tra le celle selezionate. È applicabile quando le celle di interesse seguono uno schema a intervalli fissi. Risulta particolarmente efficiente con dati periodici, come valori registrati ogni altro giorno, o dati distanziati a intervalli regolari ma non posizionati consecutivamente.
Limitazioni: questo approccio è ideale quando le celle non adiacenti sono disposte a intervalli regolari. Per selezioni arbitrarie o manuali, consulta il metodo basato su formula descritto di seguito.
1. Seleziona una cella vuota in cui desideri visualizzare il risultato e inserisci la formula matriciale:
=AVERAGE(IF(MOD(COLUMN(C2:G2)-COLUMN(C2),2)=0,IF(C2:G2,C2:G2)))Quindi premi contemporaneamente i tasti Ctrl + Shift + Invio(per le versioni precedenti di Excel; in Excel 365 o 2019, è sufficiente premere)Invio, poiché supportano le matrici dinamiche).
Nota: nella formula, C2 e G2 rappresentano la prima e l’ultima cella dell’intervallo di destinazione. Il numero "2" imposta il passo tra le colonne; modifica questo valore se le tue celle non adiacenti sono separate da un numero diverso di colonne. Adatta l’intervallo (C2:G2) e il passo (2) in base alla struttura dei tuoi dati.
Suggerimenti:
- Assicurati che l’intervallo selezionato includa tutte le celle non adiacenti che desideri mediare.
- Se la selezione dei dati non segue intervalli regolari, utilizza la soluzione basata su formula descritta di seguito per celle arbitrarie.
- Questa formula ignora automaticamente le celle vuote e i valori zero, offrendoti la media reale dei dati significativi.
- Per evitare errori, assicurati che i riferimenti di cella siano corretti e di aver premuto la combinazione di tasti richiesta per le formule matriciali nelle versioni precedenti di Excel.
Calcolare la media di celle non adiacenti escludendo gli zeri con una macro VBA
Se devi calcolare rapidamente la media di un gruppo di celle non adiacenti escludendo gli zeri, e la tua selezione non segue uno schema fisso—ad esempio, desideri scegliere manualmente celle specifiche sparse su diverse righe e colonne—una macro VBA rappresenta una soluzione flessibile ed efficace. Il codice analizzerà le celle selezionate e ne calcolerà la media, ignorando quelle contenenti zero.
Questo metodo è particolarmente utile per:
- Elaborazione rapida di ampie o irregolari selezioni di dati.
- Salva la logica personalizzata per riutilizzarla su set di dati simili.
- Oltre i limiti delle formule tradizionali del foglio di lavoro per selezioni complesse.
1. Fai clic su Sviluppo > Visual Basic; nella finestra che si apre, fai clic su Inserisci > Modulo e incolla il seguente codice nel modulo:
Sub AverageNonAdjacentExcludeZero()
Dim rng As Range
Dim cell As Range
Dim SumVal As Double
Dim CountVal As Long
Dim SelectedRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set SelectedRng = Application.Selection
Set SelectedRng = Application.InputBox("Select non-adjacent cells to average (exclude zeros)", xTitleId, Type:=8)
On Error Resume Next
SumVal = 0
CountVal = 0
For Each cell In SelectedRng
If IsNumeric(cell.Value) And cell.Value <> 0 Then
SumVal = SumVal + cell.Value
CountVal = CountVal + 1
End If
Next
If CountVal > 0 Then
MsgBox "Average (excluding zeros): " & SumVal / CountVal, vbInformation, xTitleId
Else
MsgBox "No non-zero numeric cells selected.", vbExclamation, xTitleId
End If
End Sub2. Fai clic sul pulsante 
**Esegui** oppure premi F5 nell’editor VBA. Apparirà una finestra di dialogo che ti chiederà di selezionare le celle. Usa il mouse e tieni premuto il tasto Ctrl per fare clic e selezionare le celle non adiacenti da mediare. Fai clic su OK e la macro mostrerà il risultato, escludendo eventuali zeri.
Calcolare la media di celle non adiacenti escludendo gli zeri utilizzando una funzione predefinita di Excel
Excel offre anche alcuni metodi integrati per gestire questa operazione con poca o nessuna formula, ideali per calcoli rapidi occasionali o ispezioni visive.
Utilizza la Barra di stato:
- Tieni premuto Ctrl e usa il mouse per selezionare manualmente ogni cella non adiacente da mediare, saltando quelle con valori zero.
- Una volta effettuata la selezione, guarda la barra di stato di Excel in basso a destra della finestra: il valore della media verrà visualizzato automaticamente se sono selezionate più di una cella.
- Questo metodo è ideale per controlli visivi rapidi e set di dati di piccole dimensioni, ma non restituisce alcun risultato direttamente in una cella del foglio di lavoro.
Nella scelta del metodo più adatto, considera le dimensioni dei dati, quanto sono sparse le celle selezionate e se hai bisogno di una formula riutilizzabile o di un semplice controllo visivo. In caso di errori (ad esempio)#DIV/0!), assicurati che tra le celle selezionate sia presente almeno un valore diverso da zero. Prima di eseguire nuove macro con VBA, salva sempre la cartella di lavoro per evitare perdite accidentali di dati.
Seleziona rapidamente righe o colonne a intervalli (non adiacenti) in Intervallo limitato in Excel:
La funzionalità Kutools per Excel di Seleziona Righe/Colonne a Intervalli ti consente di selezionare facilmente righe o colonne a intervalli regolari all’interno di un intervallo definito in Excel, come mostrato nell’immagine seguente. Questa opzione è particolarmente utile prima di applicare formule o macro, poiché ti aiuta a selezionare esattamente le celle da elaborare.
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
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