Come funziona la somma se la formula. Esempi di utilizzo della funzione sommata in Excel

La funzione SOMMA.SE viene utilizzata quando è necessario sommare i valori in un intervallo che soddisfano un criterio specificato.

Descrizione della funzione

La funzione SOMMA.SE viene utilizzata quando è necessario sommare i valori in un intervallo che soddisfano un criterio specificato. Ad esempio, in una colonna di numeri, devi solo sommare valori maggiori di 5. A tale scopo, puoi utilizzare la seguente formula:
\u003d SUMIF (B2: B25, "\u003e 5") In questo esempio, i valori sommati vengono confrontati con il criterio. Se necessario, la condizione può essere applicata a un intervallo e i valori corrispondenti di un altro intervallo possono essere sommati. Ad esempio, la formula:
\u003d SOMMA.SE (B2: B5; "Ivan"; C2: C5) somma solo quei valori del range C2: C5 per i quali i corrispondenti valori del range B2: B5 sono uguali a “Ivan”.
Un altro esempio di utilizzo della funzione è mostrato nella figura seguente:

Un esempio di utilizzo di SUMIF per calcolare i profitti con una condizione

Sintassi

\u003d SUMIF (intervallo, condizione, [intervallo_somma])

argomenti

condizione dell'intervallo intervallo_somma

Argomento richiesto. L'intervallo di celle da giudicare in base ai criteri. Le celle in ogni intervallo devono contenere numeri, nomi, matrici o riferimenti numerici. Le celle vuote e le celle contenenti valori di testo vengono ignorate.

Argomento richiesto. Condizione sotto forma di numero, espressione, riferimento di cella, testo o funzione che determina le celle da sommare. Ad esempio, una condizione può essere rappresentata come: 32, "\u003e 32", B5, "32", "mele" o TODAY ().

Importante! Tutte le condizioni di testo e le condizioni con segni logici e matematici devono essere racchiuse tra virgolette doppie ("). Se la condizione è un numero, non è necessario utilizzare le virgolette.

Argomento facoltativo. Celle da cui vengono aggiunti valori se differiscono dalle celle specificate come intervallo. Se intervallo_somma viene omesso, Microsoft Excel somma le celle specificate nell'argomento intervallo (le stesse celle a cui si applica la condizione).

Osservazioni

È possibile utilizzare caratteri jolly nell'argomento condizione: punto interrogativo (?) E asterisco (*). Il punto interrogativo corrisponde a qualsiasi carattere singolo e l'asterisco corrisponde a qualsiasi sequenza di caratteri. Se vuoi trovare direttamente un punto interrogativo (o un asterisco), devi mettere una tilde (~) davanti ad esso.
La funzione SOMMA.SE restituisce risultati errati se viene utilizzata per associare stringhe più lunghe di 255 caratteri alla stringa #VALORE !.
L'argomento intervallo_somma potrebbe non avere le stesse dimensioni dell'argomento intervallo. Quando si determinano le celle effettive da sommare, la cella in alto a sinistra dell'argomento intervallo_somma viene utilizzata come cella iniziale, quindi vengono sommate le celle nella parte dell'intervallo ridimensionate dall'argomento intervallo.

Aggiungiamo i valori nelle righe, i cui campi soddisfano due criteri contemporaneamente (Condizione AND). Considera i criteri di testo, numerici e di data. Analizziamo la funzione SUMIFS ( ), Versione inglese SUMIFS ().

Prendiamo una tabella con due colonne (campi) come tabella di origine: testo " Frutta"E numerico" Quantità in magazzino"(Vedi file di esempio).

Problema1 (1 criterio di testo e 1 numerico)

Troviamo il numero di scatole di merce con un certo Frutta E, quale Scatole rimanenti in magazzino non inferiore al minimo. Ad esempio, il numero di scatole con merci pesche (cellula D 2 ), che hanno il resto delle scatole a magazzino\u003e \u003d 6 (cellula E 2 ) ... Dovremmo ottenere il risultato 64. Il conteggio può essere implementato da una varietà di formule, ne daremo diverse (vedi file di esempio Testo e numero del foglio):

1. =SUMIFN (B2: B13; A2: A13; D2; B2: B13; "\u003e \u003d" ed E2)

Sintassi della funzione: SUMIF (sum_interval; condition_interval1; condition1; condition_interval2; condition2 ...)

B2: B13 Sum_Interval - celle per la somma, inclusi nomi, array o riferimenti contenenti numeri. I valori e il testo vuoti vengono ignorati.
A2: A13e B2: B13 condition_interval1; condition_interval2; ... rappresentano da 1 a 127 intervalli in cui viene verificata la condizione corrispondente.
D2e "\u003e \u003d" & E2 Condizione1; condition2; ... sono da 1 a 127 condizioni sotto forma di un numero, un'espressione, un riferimento di cella o un testo che determinano le celle che verranno sommate.

L'ordine degli argomenti è diverso nelle funzioni SUMIF () e SUMIF (). L'argomento SUMIFS () summation_interval è il primo argomento e in SUMIF () il terzo. Quando si copiano e si modificano queste funzioni simili, assicurarsi che gli argomenti siano nell'ordine corretto.

2.altra opzione \u003d SUMPRODUCT ((A2: A13 \u003d D2) * (B2: B13); - (B2: B13\u003e \u003d E2))
Diamo uno sguardo più da vicino all'utilizzo della funzione SUMPRODUCT ():

Il risultato della valutazione di A2: A13 \u003d D2 è un array (FALSE: FALSE: FALSE: FALSE: TRUE: TRUE: TRUE: FALSE: FALSE: FALSE: FALSE: FALSE) Il valore TRUE corrisponde al valore della colonna E criterio, ad es. parola pesche... La matrice può essere visualizzata evidenziando in A2: A13 \u003d D2 e \u200b\u200bquindi premendo;
Il risultato del calcolo di B2: B13 è (3: 5: 11: 98: 4: 8: 56: 2: 4: 6: 10: 11), cioè solo valori da una colonna B ;
Il risultato della moltiplicazione elementare degli array (A2: A13 \u003d D2) * (B2: B13) è (0: 0: 0: 0: 4: 8: 56: 0: 0: 0: 0: 0). Moltiplicando un numero per FALSE si ottiene 0; e per il valore TRUE (\u003d 1) si ottiene il numero stesso;
Analizziamo la seconda condizione: il risultato del calcolo - (B2: B13\u003e \u003d E2) è un array (0: 0: 1: 1: 0: 1: 1: 0: 0: 1: 1: 1). I valori nella colonna " Numero di scatole in magazzino»Che soddisfano il criterio\u003e \u003d E2 (cioè\u003e \u003d 6) corrispondono a 1;
Inoltre, la funzione SUMPRODUCT () moltiplica gli elementi delle matrici in coppie e somma i prodotti risultanti. Otteniamo - 64.

3. Un altro utilizzo della funzione SUMPRODUCT () è la formula \u003d SUMPRODUCT ((A2: A13 \u003d D2) * (B2: B13) * (B2: B13\u003e \u003d E2)).

Se necessario, le date possono essere inserite direttamente nella formula \u003d SUMIFS (B6: B17; A6: A17; "\u003e \u003d 15/01/2010"; A6: A17; "<=06.03.2010")

Per visualizzare i criteri di selezione in una stringa di testo, utilizzare la formula \u003d "Volume delle vendite per il periodo da" & TEXT (D6; "dd.MM.yy") & "a" & TEXT (E6; "dd.MM.yy")

Usato nell'ultima formula.

Task4 (mese)

Modifichiamo leggermente la condizione del problema precedente: trova le vendite totali per il mese (vedi il file di esempio Mese del foglio).

Le formule sono costruite in modo simile all'attività 3, ma l'utente non inserisce 2 date, ma il nome del mese (si presume che i dati nella tabella siano entro 1 anno).

Per risolvere il 3 ° compito, la tabella con il filtro automatico configurato ha questo aspetto (vedi il file di esempio Foglio 2 Date).

In precedenza, la tabella deve essere convertita e includere la riga Totali.

Per sommare i valori che soddisfano un determinato criterio (condizione), utilizzare la funzione SUMIF () , Versione inglese SUMIF ().

Sintassi delle funzioni

SUMIF(Gamma; Condizione; [Sum_range])

Gamma - l'intervallo di celle in cui vengono cercati i valori corrispondenti all'argomento Condizione... L'intervallo può contenere numeri, date, valori di testo o riferimenti ad altre celle. Nel caso in cui l'altro argomento sia Sum_range - viene omesso, quindi l'argomento Gamma è anche l'intervallo in cui viene eseguita la somma dei valori che soddisfano l'argomento Condizione (in questo caso Gamma deve contenere numeri).

Condizione - un criterio sotto forma di numero, espressione o testo che determina quali celle devono essere aggiunte. Ad esempio, l'argomento Condizione può essere espresso come 32, "mele" o "\u003e 32".

Sum_range - un intervallo di celle contenente numeri che verranno sommati, se le celle corrispondenti specificate nell'argomento Gammacorrisponde all'argomento Condizione. Sum_range - argomento opzionale. Se viene omesso, la somma verrà eseguita sull'intervallo di celle specificato nell'argomento Gamma.

Esempi di

Considera il caso in cui l'argomento Sum_range omesso. In questo caso, la somma verrà eseguita sull'intervallo di celle specificato nel primo argomento Gamma (cioè deve contenere numeri). Cercherà anche i valori corrispondenti all'argomento Condizione, che verrà poi riassunto. Lascia che sia un intervallo B5: B15 , vedi file di esempio.

Risolviamo i compiti:

trova la somma di tutti i numeri maggiori o uguali a 10. Risposta: 175. Formula: \u003d SOMMA.SE (B5: B15, "\u003e \u003d 10")
trova la somma di tutti i numeri minori o uguali a 10. Risposta: 42. Formula: \u003d SOMMA.SE (B5: B15, "<=10")
trova la somma di tutti i numeri positivi in \u200b\u200bun intervallo. Formula: \u003d SOMMA.SE (B5: B15, "\u003e 0")... Una versione alternativa che utilizza la funzione SUMPRODUCT () è simile a questa: \u003d SUMPRODUCT ((B5: B15) * (B5: B15\u003e 0))

Modulo attività termini(criterio) è abbastanza flessibile. Ad esempio, nella formula \u003d SOMMA.SE (B5: B15; D7 e D8) criterio<=56 задан через ссылку D7&D8 : в D7 contiene un valore di testo<=, а в D8 - numero 56 (vedi figura sotto). L'utente, ad esempio, può facilmente modificare il criterio utilizzando nella cella D7 ... Formula equivalente \u003d SOMMA.SE (B5: B15, "<=56") или \u003d SOMMA.SE (B5: B15, "<="&56) o \u003d SOMMA.SE (B5: B15, "<="&D8) o \u003d SOMMA.SE (B5: B15; D7 e 56).

Buon pomeriggio, cari abbonati e visitatori del sito del blog. Abbastanza recentemente, abbiamo scoperto la formula CERCA.VERT e subito dopo ho deciso di scrivere di un'altra funzione di Excel molto utile - SUMIF... Entrambe queste funzioni sono in grado di "collegare" dati da diverse fonti (tabelle) da un campo chiave e in determinate condizioni sono intercambiabili. Allo stesso tempo, ci sono differenze significative nel loro scopo e utilizzo.

Se lo scopo di CERCA.VERT è semplicemente quello di "estrarre" dati da una posizione in Excel a un'altra, SUMIF viene utilizzato per sommare i dati numerici in base a un determinato criterio. Lo scopo principale di questa formula è riassumere i dati in base ad alcune condizioni. Qualcosa di simile è scritto nella guida. Tuttavia, questo strumento funzionerà anche per altri scopi. Ad esempio, utilizzo spesso la formula SOMMA.SE invece di CERCA.VERT, poiché non genera errori # N / D quando non riesce a trovare qualcosa. E questo facilita e velocizza notevolmente il lavoro.

La funzione SUMIF può essere adattata con successo per risolvere un'ampia varietà di problemi. Pertanto, in questo articolo, considereremo non 1 (uno), ma 2 (due) esempi. Il primo sarà associato alla somma secondo un determinato criterio, il secondo - con i dati "pull", cioè in alternativa a CERCA.VERT.

Esempio di somma utilizzando la funzione SUMIF

La tabella contiene le posizioni, le loro quantità e l'appartenenza a un particolare gruppo di merci (prima colonna). Consideriamo per ora un utilizzo semplificato di SUMIF, quando dobbiamo calcolare l'importo solo per quelle posizioni, i cui valori corrispondono a una certa condizione. Ad esempio, vogliamo sapere quante prime posizioni sono state vendute, ad es. quelli il cui valore supera le 70 unità. Non è molto comodo e veloce cercare tali beni con gli occhi e quindi riassumere manualmente, quindi la funzione SUMIF è molto appropriata qui.

Prima di tutto, seleziona la cella in cui verrà calcolato l'importo. Successivamente, chiamiamo la procedura guidata delle funzioni. Questa è l'icona fx sulla barra della formula. Quindi, cerca la funzione SUMIF nell'elenco e fai clic su di essa. Si apre una finestra di dialogo, in cui, per risolvere questo particolare problema, è necessario compilare solo due (primi) campi dei tre proposti.

Ecco perché ho chiamato questo esempio semplificato. Perché 2 (due) su 3 (tre)? Perché il nostro criterio è nella stessa gamma di sommatoria.

Il campo "Intervallo" indica l'area della tabella Excel in cui si trovano tutti i valori originali, da cui è necessario selezionare qualcosa e quindi aggiungere. Di solito è impostato con il mouse.

Il campo "Criterio" indica la condizione in base alla quale la formula selezionerà. Nel nostro caso indichiamo "\u003e 70". Se non inserisci le virgolette, verranno completate da sole. Ma in generale, un tale criterio dovrebbe essere tra virgolette.

Non compiliamo l'ultimo campo "Sum_range", poiché è già indicato nel primo campo.

Pertanto, la funzione SOMMA.SE accetta un criterio e inizia a selezionare tutti i valori dell'intervallo specificato che soddisfano il criterio specificato. Successivamente, vengono aggiunti tutti i valori selezionati. Ecco come funziona l'algoritmo della funzione.

Dopo aver compilato i campi richiesti nella procedura guidata di funzione, premere il grande pulsante "Invio" sulla tastiera o fare clic su "Ok" nella finestra della procedura guidata. Successivamente, il valore calcolato dovrebbe apparire al posto della funzione di input. Nel mio esempio, sono risultati 224 pezzi. Cioè, il valore totale delle merci vendute per un importo di oltre 70 pezzi era di 224 pezzi. (Questo può essere visto nell'angolo inferiore sinistro della finestra della procedura guidata anche prima di fare clic su "ok"). È tutto. Questo era un esempio semplificato in cui il criterio e l'intervallo di sommatoria sono nella stessa posizione.

Ora diamo un'occhiata a un esempio in cui il criterio non corrisponde all'intervallo di sommatoria. Questa situazione è molto più comune. Considera gli stessi dati condizionali. Supponiamo di aver bisogno di conoscere l'importo non più o meno di un valore, ma l'importo di un gruppo specifico di beni, ad esempio il gruppo G.

Per fare ciò, seleziona di nuovo la cella con il calcolo futuro dell'importo e chiama la Procedura guidata delle funzioni. Ora, nella prima finestra, indichiamo l'intervallo che contiene il criterio in base al quale verrà effettuata la selezione per la somma, ovvero, nel nostro caso, si tratta di una colonna con i nomi dei gruppi di prodotti. Inoltre, scriviamo il criterio stesso manualmente, lasciando la voce "gruppo D" nel campo corrispondente, o semplicemente indichiamo con il mouse la cella con il criterio richiesto. L'ultima casella è l'intervallo in cui si trovano i dati riepilogati. Lo indichiamo con il mouse, ma agli amanti della stampa non è vietato digitare manualmente.

Il risultato sarà la quantità di merci vendute dal gruppo D - 153 pezzi.

Quindi, abbiamo esaminato come calcolare un importo per un criterio specifico. Tuttavia, più spesso il problema sorge quando è necessario calcolare più importi per diversi criteri. Non potrebbe essere più facile! Ad esempio, dobbiamo scoprire la quantità di prodotti venduti per ogni gruppo. Cioè, sono interessato a 4 (quattro) valori per 4 (quattro) gruppi (A, B, C e D). Per questo, un elenco di gruppi viene solitamente creato da qualche parte negli spazi aperti di Excel sotto forma di una tabella separata. È chiaro che i nomi dei gruppi devono corrispondere esattamente ai nomi dei gruppi nella tabella di origine. Aggiungiamo immediatamente la riga finale, dove la somma è ancora zero.

Quindi la formula per il primo gruppo viene prescritta ed estesa a tutti gli altri. Qui devi solo prestare attenzione alla relatività dei collegamenti. L'intervallo con i criteri e l'intervallo di sommatoria devono essere collegamenti assoluti, in modo che quando trascini la formula, non "scendono", ma il criterio stesso, in primo luogo, devi specificare con il mouse (e non scrivere manualmente) e in secondo luogo, dovrebbe essere un collegamento relativo, quindi come ogni somma ha il proprio criterio di somma.

I campi compilati della Creazione guidata funzione con tale calcolo avranno un aspetto simile a questo.

Come puoi vedere, per il primo gruppo A, la quantità di merce venduta era di 161 pezzi (angolo in basso a sinistra della figura). Ora premi invio e trascina la formula verso il basso.

Tutti gli importi sono stati calcolati e il loro totale complessivo è 535, che coincide con il totale nei dati originali. Ciò significa che tutti i valori sono stati riassunti, non è stato perso nulla. L'ultimo caso d'uso di SUMIF personalmente mi viene in mente abbastanza spesso.

Un esempio di utilizzo della funzione SUMIF per confrontare i dati

Come ho notato più di una volta, la funzione SUMIF può essere utilizzata in modo ragionevole per associare i dati. In effetti, se aggiungi un valore, ottieni quel valore stesso. Questa proprietà aritmetica non è un peccato da usare per sempre. È anche possibile che i progettisti di Excel non siano consapevoli di un uso così barbaro del loro frutto.

In breve, SUMIF è facile da adattare per l'associazione dei dati come alternativa a VLOOKUP. Perché usare SUMIF se VLOOKUP esiste? Lasciatemi spiegare. Innanzitutto, SUMIF, a differenza di CERCA.VERT, è insensibile al formato dei dati e non genera un errore dove meno te lo aspetti; in secondo luogo, SUMIF al posto degli errori dovuti alla mancanza di valori secondo un dato criterio dà 0 (zero), che permette di calcolare i totali dell'intervallo con la formula SUMIF senza gesti inutili. Tuttavia, c'è uno svantaggio. Se un qualsiasi criterio viene ripetuto nella tabella desiderata, verranno sommati i valori corrispondenti, il che non è sempre un "pull-up". Meglio stare in guardia. D'altra parte, questo è spesso necessario: estrarre i valori in un determinato luogo e allo stesso tempo aggiungere le posizioni duplicate. Hai solo bisogno di conoscere le proprietà della funzione SUMIF e usarla secondo il manuale di istruzioni.

Ora diamo un'occhiata a un esempio di come la funzione SOMMA.SE risulta essere più adatta per estrarre dati rispetto a CERCA.VERT. Lascia che i dati del tuo esempio siano le vendite di alcuni prodotti a gennaio. Vogliamo sapere come sono cambiati a febbraio. È conveniente fare un confronto nella stessa piastra, avendo precedentemente aggiunto un'altra colonna a destra e riempita con i dati per febbraio. Da qualche parte in un altro file Excel ci sono le statistiche di febbraio per l'intero assortimento, ma vogliamo analizzare esattamente queste posizioni, per le quali dobbiamo estrarre i valori necessari da un file di grandi dimensioni con le statistiche di vendita per tutti i prodotti nella nostra tabella. Innanzitutto, proviamo a utilizzare la formula CERCA.VERT. Useremo il codice del prodotto come criterio. Il risultato è nella figura.

Si vede chiaramente che una posizione non è stata serrata e invece di un valore numerico viene visualizzato l'errore # N / A. Molto probabilmente, questo prodotto semplicemente non era in vendita a febbraio, e quindi è assente dal database di febbraio. Di conseguenza, l'errore # N / D viene visualizzato in totale. Se non ci sono molte posizioni, il problema non è grande, è sufficiente rimuovere manualmente l'errore e l'importo verrà ricalcolato correttamente. Tuttavia, il numero di righe può essere misurato in centinaia e contare sulle regolazioni manuali non è la decisione giusta. Ora usiamo la formula SOMMA.SE invece di CERCA.VERT.

Il risultato è lo stesso, solo che al posto dell'errore # N / D SOMMA.SE dà zero, il che consente di calcolare normalmente l'importo (o un altro indicatore, ad esempio, la media) nella riga finale. Questa è l'idea principale per cui a volte SUMIF dovrebbe essere utilizzato al posto di CERCA.VERT. Con un gran numero di posizioni, l'effetto sarà ancora più evidente.

In realtà, è spesso necessario lavorare con grandi quantità di dati che sono molte volte più grandi delle dimensioni del monitor. È molto difficile individuare e tenere traccia degli errori. Ma tutti gli stipiti compaiono nei calcoli successivi. Non è conveniente smontare tali esempi utilizzando gli screenshot nell'articolo, quindi ti invito a guardare un tutorial video appositamente registrato sulla funzione SUMIF.

Ecco un'altra lezione su SUMIF