In questo articolo impareremo come calcolare la media per i numeri che soddisfano i criteri specificati in Excel.
Scenario:
Quando si lavora con i dati, dove è necessario ottenere la media dei numeri con valori di criteri. Perché i valori dei criteri creano una differenza nel risultato, la funzione excel dovrebbe considerarlo zero e prendere il risultato. Un valore zero può fare la differenza nella media dei numeri. La media dei numeri è la somma dei numeri richiesti divisa per il conteggio dei numeri. Dove lo zero non contribuisce alla somma ma aumenta il conteggio. Capire come risolvere il problema.
Come risolvere il problema?
Qui dobbiamo trovare la media dei numeri con criteri. Criteri sta ignorando i valori dei criteri. Se hai solo numeri positivi o negativi puoi usare la funzione AVERAGEIF
Sintassi della formula (numeri positivi):
| =MEDIA.SE(valori, ">=0") |
La formula sopra include zeri ma ignora i valori dei criteri. Se vuoi ignorare gli zeri e i valori dei criteri, usa semplicemente i criteri come "> 0".
Allo stesso modo puoi ottenere la media dei valori negativi inclusi gli zeri usando la formula:
Sintassi della formula (numeri negativi):
| =MEDIA.SE(valori, "<=0") |
La formula sopra include zeri ma ignora i valori dei criteri. Se vuoi ignorare gli zeri e i valori dei criteri, usa semplicemente i criteri come "> 0"
Ma il problema di considerare i numeri ignorando i criteri non è risolto. Potresti pensare che, prendendo la media dei due risultati precedenti (media dei numeri positivi e media dei numeri negativi), otterrai il valore richiesto. Non funziona bene finché il conteggio dei numeri positivi non è uguale al conteggio dei numeri negativi. Per questo problema utilizzeremo la funzione AGGREGATA. La funzione AGGREGATA è il pacchetto di funzioni matematiche. Aiuta in generale a risolvere questo tipo di problemi. Per decomprimere questo pacchetto per la media dei valori ignorando i criteri. Usa la formula sottostante.
Sintassi della formula (per tutti i numeri):
| =AGGREGATO(1, 6, valori) |
1 : Argomento sui numeri medi
6 : ignorare i valori dei criteri
Esempio :
Tutti questi potrebbero essere fonte di confusione da capire. Facciamo un esempio e capiamo come utilizzare queste formule in un esempio. Qui abbiamo un elenco di valori per i quali è richiesta la media.
Per prima cosa calcoliamo la media dei numeri positivi. Per questo useremo quanto segue per i dati.
Usa la formula:
| =MEDIA.SE(B3:B15;">=0") |
Spiegazione
- I criteri sono numeri che sono maggiori o uguali a zero.
- La funzione AVERAGEIF considera solo i numeri che soddisfano i criteri e restituisce la media dei numeri
Qui la formula restituisce 58,6 considerando solo i valori positivi dove il conteggio dei valori positivi è 7. Allo stesso modo puoi ottenere la media dei numeri negativi.
Usa la formula:
| =MEDIA.SE(B3:B15;"<=0") |
Spiegazione
- I criteri sono i numeri minori o uguali a zero.
- La funzione AVERAGEIF considera solo i numeri che soddisfano i criteri e restituisce la media dei numeri.
Per i numeri negativi la media è -21,0 inclusi gli zeri che ignorano i valori dei criteri. Il conteggio per i numeri considerati è 4.
Qui in questo esempio il conteggio dei numeri negativi non è uguale al conteggio dei numeri positivi. Quindi la media dei 2 risultati di cui sopra non produrrà la media di tutti i numeri. Utilizzare la formula seguente per questi casi.
Usa la formula:
| =AGGREGATO(1, 6, B3:B15) |
Spiegazione:
- Viene fornito l'argomento 1 per utilizzare la funzione Media.
- L'argomento 6 viene fornito per ignorare i valori dei criteri.
- La funzione AGGREGATO restituisce la media di tutti i numeri nell'array B3:B15 ignorando i valori dei criteri.
La media di tutti i numeri che ignorano tutti i valori dei criteri risulta essere 36,2. Qui il conteggio dei numeri è 9. La funzione AGGREGATO è il modo migliore per risolvere questi problemi, poiché queste funzioni sono funzioni integrate di Excel.
Se stai cercando di ottenere la media dei numeri utilizzando la funzione MEDIA di base, devi utilizzare la formula seguente per ottenere la media dei numeri ignorando i criteri.
Usa la formula:
| { =MEDIA(SE(IScriterio(B3:B15),"",B3:B15)) } |
Nota : non utilizzare le parentesi graffe manualmente. Basta usare Ctrl + Maiusc + Invio al posto di accedere per ottenere il risultato.
Spiegazione:
- La funzione IF controlla la condizione con la funzione IScriteria sull'array B3:B15.
- La funzione IScriteria restituisce True per il valore dove value è un criterio e False dove no.
- La funzione SE restituisce un valore vuoto se il valore è un criterio e restituisce il valore corrispondente se il valore non è un criterio.
- La funzione AVERAGE restituisce la media dei valori restituiti.
- Le parentesi graffe fanno funzionare queste funzioni logiche per un array.
Come puoi vedere la formula funziona bene e restituisce lo stesso risultato. Ma usa la funzione AGGREGATA, è facile da usare per varie funzioni matematiche con diversi criteri incorporati.
Se non hai alcun valore zero nell'array. Puoi utilizzare lo zero restituito tra tutti i valori dei criteri e utilizzare la formula come prendere la media dei numeri ignorando gli zeri qui. Ma questa formula non funziona con i valori dei criteri.
Ecco tutte le note di osservazione sull'uso della formula.
Appunti:
- La funzione AGGREGATO è la funzione migliore da utilizzare per questi problemi.
- La funzione restituisce #VALORE! criteri se nessun valore corrisponde ai criteri forniti.
- Sono accettati criteri diversi all'interno della formula, ad esempio uguale a (=), minore di (<) , minore o uguale a (=) , maggiore di (>) e diverso da ().
In questo modo, abbiamo imparato a calcolare la media per i numeri che soddisfano i criteri specificati in Excel. Puoi utilizzare queste funzioni in Excel 2016, 2013 e 2010. Trova altri articoli sulla formulazione matematica con criteri diversi. Se ti è piaciuto il nostro blog, condividilo con i tuoi amici su Facebook. E puoi anche seguirci su Twitter e Facebook. Ci piacerebbe avere tue notizie, facci sapere come possiamo migliorare, completare o innovare il nostro lavoro e renderlo migliore per te. Scrivici al sito di posta elettronica
Come evidenziare le celle sopra e sotto il valore medio: evidenziare i valori che sono al di sopra o al di sotto del valore medio utilizzando la formattazione condizionale in Excel.
Ignora zero nella media dei numeri: calcola la media dei numeri nell'array ignorando gli zeri usando la funzione AVERAGEIF in Excel.
Calcola media ponderata: trova la media dei valori con peso diverso usando la funzione SUMPRODUCT in Excel.
Differenza media tra le liste: calcolare la differenza in media di due liste diverse. Ulteriori informazioni su come calcolare la media utilizzando la formula della media matematica di base.
Numeri medi se non vuoti in Excel: estrai la media dei valori se la cella non è vuota in Excel.
MEDIA dei primi 3 punteggi in una lista in excel: Trova la media dei numeri con criteri come 3 numeri più alti dall'elenco in Excel
Articoli popolari:
Come usare la funzione SE in Excel : l'istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è VERA o restituisce un altro valore specifico se FALSO.
Come utilizzare la funzione CERCA.VERT in Excel : questa è una delle funzioni più utilizzate e popolari di excel che viene utilizzata per cercare il valore da diversi intervalli e fogli.
Come utilizzare la funzione CONTA.SE in Excel : Conta i valori con le condizioni utilizzando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.
Come utilizzare la funzione SOMMA.SE in Excel : Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.