Come iniziare a utilizzare COUNTIF, SUMIF e AVERAGEIF in Excel

Imparare Microsoft Excel significa aggiungere sempre più formule e funzioni al tuo cinturone. Combina abbastanza di questi e puoi fare praticamente qualsiasi cosa con un foglio di calcolo.

In questo tutorial, imparerai come utilizzare tre potenti formule di Excel: SUMIF, COUNTIF, e AVERAGEIF.

Qui abbiamo un elenco di transazioni per il mese con alcuni dati sulle spese.

Nello screenshot qui sopra, puoi vedere che abbiamo un elenco di transazioni sul lato sinistro. Se voglio tenere d'occhio la mia spesa, posso usare queste tre formule per monitorarlo. 

Sul lato destro, il Mangiare fuori spesa box usa le tre formule per aiutarmi a tenere traccia delle mie spese:

• COUNTIF - Utilizzato per contare il numero di volte in cui "Ristorante" appare nell'elenco.
• SUMIF - Calcola la spesa totale per gli articoli etichettati "Ristorante".
  
• AVERAGIEF - Calcola le mie spese di "ristorante" nella lista.
  

Più genericamente, ecco cosa fanno ognuna di quelle formule per te e come potresti usarle a tuo vantaggio:

• SUMIF - Aggiungi valori se viene soddisfatta una condizione, ad esempio sommando tutti gli acquisti da una categoria.
• COUNTIF - Conta il numero di elementi che soddisfano una condizione, ad esempio il numero di volte in cui un nome appare in un elenco.
• AVERAGEIF - Valori medi condizionali; ad esempio, potresti mediare i tuoi voti solo per gli esami.

Queste formule ti consentono di aggiungere la logica al tuo foglio di calcolo. Diamo un'occhiata a come utilizzare ciascuna formula.

COUNTIF, SUMIF e AVERAGEIF in Excel (tutorial video rapido)

Screencast sono uno dei modi migliori per guardare e apprendere una nuova abilità, tra cui iniziare con queste tre formule chiave in Microsoft Excel. Guarda il video qui sotto per vedere come lavoro in Excel. Assicurati di scaricare la cartella di lavoro di esempio gratuita da utilizzare con questo tutorial.

 

Se preferisci imparare con istruzioni passo-passo scritte, continua a leggere. Condividerò suggerimenti su come utilizzare queste formule e le idee sul perché sono utili.

Come utilizzare SUMIF in Excel

Utilizzare la scheda intitolata SUMIF nella cartella di lavoro di esempio gratuita per questa sezione del tutorial.

Pensa a SUMIF come un modo per aggiungere valori che soddisfano una regola. Possiamo aggiungere un elenco di valori appartenenti a una determinata categoria o tutti i valori maggiori o minori di un importo specifico.

Ecco come funziona la formula SUMIF:

= SUMIF (celle da controllare, cosa controllare, somma delle celle che soddisfano le regole)

Torniamo all'esempio di spesa del mio ristorante per imparare la formula SUMIF. Di seguito, mostro un elenco delle mie transazioni per il mese.

Ho un elenco di transazioni e utilizzerò SUMIF per aiutarmi a tenere d'occhio le mie spese.

Voglio sapere due cose:

1. Il totale di ciò che ho speso nei ristoranti per il mese.
2. Tutti gli acquisti superiori a $ 50 per il mese, da qualsiasi categoria.

Invece di aggiungere manualmente i dati, possiamo scrivere due SUMIF formule per automatizzare il processo. Metterò i risultati nel verde Spese del ristorante scatola sul lato destro. Diamo un'occhiata a come.

Spesa totale per il ristorante

Per trovare la spesa totale del ristorante, riassumerò tutti i valori con il tipo di spesa "Ristorante", che si trova nella colonna B.

Ecco la formula che userò per questo esempio:

= SUMIF (B2: B17, "ristorante", C2: C17)

Si noti che ogni sezione è separata da una virgola. Questa formula fa tre cose chiave:

• Esamina le celle da B2 a B17 per la categoria di spesa
• Usa "Ristorante" per i criteri di cosa riassumere
• Utilizza i valori nelle celle da C2 a C17 per sommare gli importi

In questo esempio, sto riassumendo tutti i valori del tipo di spesa "Ristorante".

Quando premo invio, Excel calcola il totale delle spese del mio ristorante. Utilizzando SUMIF, è facile creare queste statistiche rapide per aiutarti a monitorare i dati di determinati tipi.

Acquisti superiori a $ 50

Abbiamo controllato una categoria specifica, ma ora riassumiamo tutti i valori che sono più grande di un importo da qualsiasi categoria. In questo caso, voglio trovare tutti gli acquisti superiori a $ 50.

Scriviamo una semplice formula per trovare la somma di tutti gli acquisti superiori a $ 50:

= SUMIF (C2: C17, "> 50")

In questo caso, la formula è un po 'più semplice: dal momento che stiamo riassumendo gli stessi valori che stiamo testando (da C2 a C17), abbiamo solo bisogno di specificare quelle celle. Quindi, aggiungeremo una virgola e "> 50" solo per sommare i valori superiori a $ 50.

Riassumi tutti gli acquisti superiori a $ 50 con una semplice formula di Excel.

Questo esempio utilizza un segno maggiore del segno, ma per i punti bonus: prova a riassumere tutti i piccoli acquisti, come tutti gli acquisti di $ 20 o meno.

Come utilizzare COUNTIF in Excel

Utilizza la scheda intitolata COUNTIF nella cartella di lavoro di esempio gratuita per questa sezione del tutorial.

Mentre SUMIF è usato per aggiungere valori che soddisfano una determinata condizione, COUNTIF conterà il numero di volte in cui qualcosa appare in un dato insieme di dati.

Ecco il formato generale per COUNTIF formula:

= COUNTIF (celle da contare, criteri da contare)

Utilizzando lo stesso set di dati, contiamo due informazioni chiave:

• Il numero di acquisti di vestiti che ho fatto in un mese
• Il numero di acquisti $ 100 o superiore

Numero di acquisti di abbigliamento

Il mio primo COUNTIF esaminerà il tipo di spesa e contò il numero di acquisti di "Abbigliamento" nelle mie transazioni.

La formula finale sarà:

= COUNTIF (B2: B17, "Abbigliamento")

Quella formula guarda la colonna "Tipo di spesa", conta il numero di volte in cui vede l'abbigliamento e le conteggia. Il risultato è 2.

La formula COUNTIF conta il numero di spese contrassegnate come "Abbigliamento" e le conteggia.

Numero di $ 100 + acquisti

Ora, contiamo il numero di transazioni che erano $ 100 o più nella mia lista. 

Ecco la formula che userò:

= COUNTIF (C2: C17, "> 100")

Questa è una semplice formula in due parti: basta puntare Excel nell'elenco dei dati da contare e la regola da contare. In questo caso, stiamo controllando le celle da C2 a C17, per tutti i valori superiori a $ 100. 

Contare il numero di transazioni $ 100 + con questa formula COUNTIF di Excel.

Come utilizzare AVERAGEIF in Excel

Utilizzare la scheda denominata AVERAGEIF nella cartella di lavoro di esempio gratuita per questa sezione del tutorial.

Infine, diamo un'occhiata a come usare un AVERAGEIF formula. A questo punto, non dovrebbe sorprendere che AVERAGEIF possa essere utilizzato per la media di valori specifici, in base a una condizione che forniremo a Excel.

Il formato per una formula AVERAGEIF è:

= AVERAGEIF (celle da controllare, cosa controllare, media delle celle che soddisfano le regole)

Il formato della formula AVERAGEIF è più simile alla formula SUMIF.

Usiamo la formula AVERAGEIF per calcolare due statistiche chiave sulla mia spesa:

1. La media delle mie spese di ristorante.
2. La media di tutte le spese inferiori a $ 25.

Media degli acquisti del ristorante

Media delle spese del mio ristorante, Scriverò una formula AVERAGEIF per calcolare la media di tutti gli importi in base alla categoria.

= AVERAGEIF (B2: B17, "ristorante", C2: C17)

Ci sono tre parti per questa formula, ciascuna separata da una virgola:

• B2: B17 specifica le celle per il controllo di una condizione. Poiché il tipo di spesa è specificato in questa colonna.
• "Ristorante" dà alla formula qualcosa da cercare.
• Infine, C2: C17 sono le celle per calcolare la media nel nostro calcolo.

Qui sto usando un AVERAGEIF per fare una media della spesa del mio viaggio annuale al ristorante.

Alla fine, Excel fa la media delle spese dei miei viaggi al ristorante. Ho dato quella formula.

Puoi anche provare questa formula sostituendo "Ristorante" con un'altra categoria, come "Abbigliamento".

Media delle spese Meno di $ 25

Se tengo d'occhio i miei acquisti più piccoli e desidero conoscere la mia media, posso scrivere un AVERAGEIF per tutti gli acquisti meno di un importo.

Ecco la formula che userò per farlo:

= AVERAGEIF (C2: C17,"<25")

Questa semplice formula controlla solo i valori nella colonna C e fa la media di tutti i valori superiori a $ 25.

Semplice formula Excel di Excel per spese inferiori a $ 25.

Riassegna e continua a imparare

In questo tutorial, hai imparato come usare tre formule matematiche condizionali per rivedere i tuoi dati. Che si tratti di sommare, contare o calcolare la media dei dati, queste funzioni sono competenze avanzate di Excel che è possibile utilizzare.

Per tutte le formule "IF" in questo tutorial, il takeaway principale è che è possibile applicare condizioni ai calcoli in Excel.

L'apprendimento genera apprendimento. Ecco altri tre tutorial di Excel per continuare a crescere:

• Oltre a COUNTIF, SUMIF e AVERAGEIF, esistono anche istruzioni generali "IF" che possono essere utilizzate per altre condizioni. Guarda il nostro tutorial su Come utilizzare le dichiarazioni IF semplici.
• Impara a utilizzare le date e le ore di Excel in combinazione con queste formule per lavorare con i valori basati sulla data.
• La funzione CERCA.VERT Excel può essere utilizzata per far corrispondere i valori di più elenchi. Ulteriori informazioni su Come utilizzare la funzione CERCA.VERT Excel in questo tutorial.

Riesci a pensare ad altri modi per utilizzare queste formule per aggiungere logica e condizioni al tuo foglio di calcolo? Fatemi sapere con un commento qui sotto.