Funzione SOMMA.SE. Primo di tre Eccellenza balena. Comincio la rassegna dei principali strumenti del nostro programma preferito di Microsoft. E, naturalmente, all'inizio voglio parlare della mia funzione più utilizzata ("formula"). In particolare, la funzione SUMMESLI. Se non hai idea di cosa sia e come usarlo, ti invidio! Per me è stata una vera scoperta.
Hai dovuto riassumere i dati su dipendenti o clienti da una grande tabella, scegliere quante entrate erano per l'una o l'altra nomenclatura? Hai filtrato per cognome/posizione e poi hai inserito i numeri a mano in celle separate? Forse su una calcolatrice? E se ci sono più di mille righe? Come calcolare rapidamente?
È qui che torna utile SUMMESLI!
Compito1. Esistono statistiche su merci, città e quali indicatori sono stati raggiunti per queste posizioni. È necessario calcolare:A quanto è stato venduto l'articolo 1 del prodotto?
Come funziona la funzione SOMMA.SE in Excel? Somma per valore
Prima di procedere alla soluzione del 1° problema, analizziamo in cosa consiste la funzione SOMMA.SE:
- Allineare. L'intervallo che contiene i termini di ricerca. Richiesto da compilare. Per la prima attività, la colonna Prodotto.
- Criterio. Può essere riempito con un numero (85), un'espressione (">85"), un riferimento di cella (B1), una funzione (oggi()). Definisce la condizione in base alla quale sommato (!). Tutte le condizioni di testo sono racchiuse tra virgolette ( « ) ">85". Richiesto da compilare. Per la prima colonna attività =Prodotto1
- Somma_intervallo. Celle da sommare se sono diverse dalle celle nell'intervallo. Per la prima attività, la colonna Entrate.
Quindi scriviamo la formula, avendo precedentemente inserito l'argomento della condizione nella cella F3
Non dimenticare di controllare;) Hai contato? Giusto? Grande!
Come funziona la funzione SOMMA.PIÙ.SE in Excel? Somma sui valori
Quelli. la selezione deve essere effettuata su due parametri. Per fare ciò, utilizzare la funzione SOMMA.SE per diverse condizioni: SOMMA.SE, in cui l'ordine di registrazione e il numero di argomenti vengono leggermente modificati.
Funzione SOMMAè probabilmente il più usato. Ma a volte hai bisogno di più flessibilità di quella che può fornire. Funzione SOMMA.SE sarà utile quando è necessario calcolare importi nozionali. Ad esempio, potresti voler ottenere la somma dei soli valori negativi in un intervallo di celle. In questo articolo viene illustrato come utilizzare la funzione SOMMA.SE Per sommatoria condizionale utilizzando un criterio.
Le formule si basano sulla tabella di fig. 118.1 e configurato per tenere traccia delle fatture. La colonna F contiene una formula che sottrae la data nella colonna E dalla data nella colonna D. Un numero negativo nella colonna F significa che il pagamento è in ritardo. Il foglio utilizza intervalli denominati corrispondenti alle etichette nella riga 1.
Riso. 118.1. Un valore negativo nella colonna F indica ritardato pagamento Somma solo valori negativi
La seguente formula restituisce la somma dei valori negativi nella colonna F. In altre parole, restituisce il numero totale di giorni scaduti per tutti i conti (per la tabella della Figura 118.1, la formula restituirà -63):
=SOMMA.SE(Differenza;" .
Funzione SOMMA.SE può prendere tre argomenti. Poiché si omette il terzo argomento, il secondo argomento (") si riferisce ai valori dell'intervallo Differenza.
Somma di valori basati su intervalli diversi
La seguente formula restituisce la somma degli importi delle fatture scadute (nella colonna C): =SOMMA.SE(differenza;" . Questa formula utilizza valori di intervallo Differenza per determinare se i valori dell'intervallo corrispondente contribuiscono alla somma Somma.
Somma i valori basati sul confronto del testo
La seguente formula restituisce l'importo totale della fattura per l'ufficio dell'Oregon: =SUMIF(Office,"=Oregon",Sum) .
L'uso del segno uguale è facoltativo. La seguente formula dà lo stesso risultato:
Per calcolare il totale delle fatture per tutti gli uffici ad eccezione dell'Oregon, inserisci la seguente formula:
=SOMMA.SE(Ufficio,"Oregon",Somma) .
Somma i valori basati sul confronto delle date
La formula seguente restituisce l'importo totale delle fatture in scadenza a partire dal 10 maggio 2010:
=SOMMA.SE(Data;">="&DATA(2010:5;10);Somma) .
Si noti che il secondo argomento della funzione SOMMA.SEè un'espressione. L'espressione utilizza la funzione DATA, che restituisce la data. Inoltre, l'operatore di confronto racchiuso tra virgolette viene concatenato (utilizzando l'operatore & ) con il risultato della funzione DATA.
La seguente formula restituisce l'importo totale delle fatture non ancora scadute (comprese le fatture odierne):
=SOMMA.SE(Data;">="&OGGI();Somma) .
La stomatite acuta negli adulti è una malattia di eziologia virale che si verifica sia negli adulti che nei bambini. Recentemente, la stomatite erpetica acuta è considerata una manifestazione di un'infezione primaria da herpes con il virus dell'herpes simplex nella cavità orale. Il periodo di incubazione della malattia dura 1-4 giorni. Il periodo prodromico è caratterizzato da un aumento dei linfonodi sottomandibolari (nei casi più gravi, cervicali). La temperatura corporea sale a 37-41 ° C, si notano debolezza, pallore della pelle, mal di testa e altri sintomi.
Le funzioni SOMMA.SE e SOMMA.PIÙ.SE ti aiutano a calcolare la somma dei valori in una colonna che corrispondono ai criteri specificati.
Si consideri, ad esempio, la seguente tabella. Indica il nome e il sesso dello studente, in quale classe sta studiando e il voto medio. Utilizzando la funzione SOMMA.SE risolviamo il seguente problema: calcoliamo la somma dei valori della colonna "Punteggio medio" per tutti i ragazzi e per tutte le ragazze. Il compito per la funzione SUMIFS è il seguente: calcolare la somma del punteggio medio per ragazzi e ragazze che studiano separatamente in 8a e 9a classe.
Prendere in considerazione argomenti per SOMMA.SE.
SOMMA.SE(intervallo, criterio, [intervallo di sommatoria])
Intervallo: qui è necessario specificare la colonna in cui i dati verranno confrontati con i criteri specificati. Nell'esempio, questo è Range D2:D26 .
Criteri: qui è necessario selezionare un valore dall'intervallo specificato in precedenza. Elencheremo prima i ragazzi, poi le ragazze.
Intervallo di sommatoriaè la colonna i cui valori devono essere sommati se appartengono ai Criteri selezionati. Ad esempio, se per l'intervallo selezionato D2:D26 è specificato il Criterio "ragazzo", nella colonna "Punteggio medio": C2:C26 verranno sommati tutti i valori relativi ai ragazzi.
Esempio di funzione SOMMA.SE.
Seleziona la cella G4 e inserisci "=". Scriviamo SUMIF in esso e apriamo la parentesi "(". Come intervallo da cui verranno selezionati i valori, seleziona D2: D26. Come criterio, devi selezionare uno dei valori in precedenza Intervallo specificato Abbiamo "ragazzo", quindi selezioniamo la cella D3 L'intervallo di sommatoria è la colonna in cui è indicato "Punteggio medio"... Seleziona le celle C2: C26... Metti ";" tra gli argomenti, a alla fine, chiudi la parentesi ")". Premere Invio".
Dalla tabella originale verranno riassunti tutti i valori GPA che si applicano ai ragazzi.
Ora estendiamo la formula su una colonna. Certo, puoi prescriverlo anche alle ragazze, ma se hai molti criteri diversi, ci vorrà molto tempo. Correggiamo gli intervalli per le colonne C e Din modo che durante la copia della formula non scorrano verso il basso.
Selezionare la cella G4 e nella "Barra della formula" dopo D2, D26, C2, C26 premere il tasto "F4". Negli indirizzi di cella appariranno i segni "$". Quindi, rendiamo assoluti i riferimenti. Per allungare la formula su una colonna, trascina l'angolo in basso a destra della cella, il cursore si trasformerà in un segno più nero.
Se sei interessato: assoluto, relativo, a un altro foglio o libro, leggi l'articolo su questo argomento.
Quindi, guarda la "barra della formula". I valori corretti vengono presi come argomenti per Range e Sum Range: D2:D26 , C2:C26 . Come Criteri, specificare l'indirizzo della cella desiderata. Poiché è necessario calcolare il punteggio medio per tutte le ragazze, nella colonna D selezioniamo l'indirizzo di qualsiasi cella che dice "ragazza": D4.
Ora considera argomenti per la funzione SOMMA.PIÙ.SE in Excel.
SOMMA.PIÙ.SE(intervallo di sommatoria, intervallo di condizioni1, condizione1, intervallo di condizioni2, condizione2)
Intervallo di sommatoriaè la colonna da cui verranno selezionati i valori se soddisfano una e la seconda condizione data. Nell'esempio, questo è C2:C26 .
Intervallo di condizioni1è il primo intervallo per il confronto. Devi selezionare il sesso dello studente, quindi l'intervallo è D2:D26 .
Condizione1: è necessario specificare il valore che verrà selezionato dall'intervallo di condizioni1. Siamo interessati ai ragazzi, quindi specificheremo la cella D3.
Intervallo di condizioni2- il secondo intervallo di confronto. Qui sceglieremo la classe in cui il bambino sta studiando. Di conseguenza, indichiamo l'intervallo B2:B26.
Condition2 è un valore specifico che ci interessa da Condition Range2. Per prima cosa dovrai selezionare la cella in cui è indicato "voto 8", quindi "voto 9".
Esempio di funzione SOMMA.PIÙ.SE.
Risolviamo il secondo problema. Seleziona la cella H8, inserisci "=", scrivi SUMIFS e apri la parentesi "(". Come intervallo di sommatoria, specifica la colonna con "Punteggio medio": C2: C26. L'intervallo della condizione 1 è il sesso dello studente: D2: D26, Condizione 1 sono ragazzi: cella D3... L'intervallo della condizione 2 è di classe: B2: B26, Condizione 2 è di classe 8: cella B3... Chiudi la parentesi, premi "Invio".
Nella barra della formula, fai subito riferimenti assoluti alle colonne: Classe, Punteggio medio, Piccolo / Dev.
Estendiamo la formula nelle celle H8:H11. Ora specifichiamo le celle corrette come Condizione1 e Condizione2.
La cella H9 è il punteggio medio per i ragazzi che frequentano la prima media. Pertanto, Condition1 per il primo intervallo "boy" : D3 , Condition2 per il secondo intervallo "9" : B6 .
Nella cella H10, consideriamo il punteggio medio delle ragazze che frequentano l'8° anno. Condizione1 - "ragazza", D4, Condizione2 - "8", B3.
Nella cella H11 consideriamo il punteggio medio delle ragazze che frequentano la nona classe. Condizione1 - "ragazza", D4, Condizione2 - "9", B2.
Pertanto, abbiamo calcolato la somma del punteggio medio per ragazzi e ragazze che studiano separatamente in 8a e 9a classe.
Verifichiamo ora che il risultato sia corretto. Calcoliamo la somma del "punteggio medio" per tutti i valori nella tabella originale, per l'attività 1 e per l'attività 2. La somma dovrebbe essere la stessa.
Con questi semplici esempi, abbiamo capito come funzionano la funzione SOMMA.SE e la funzione SOMMA.PIÙ.SE in Excel.
Ora esamineremo un'altra funzione di Excel molto comunemente usata: SOMMA.SE.
Diamo un'occhiata a un esempio di utilizzo della funzione SOMMA.SE. Supponiamo che nei dati di origine abbiamo una tabella per gli ordini in diverse città. Ogni riga visualizza il numero di ordini in una determinata città in certa data(quale data non ci interessa in questo problema).
Il nostro compito è calcolare la somma di tutti gli ordini per città per l'intero periodo. Questa attività può essere facilmente gestita dalla funzione di Excel - SOMMA.SE. Come suggerisce il nome, questa funzione importi itera i valori Se soddisfano determinati criteri.
SOMMA.SE(intervallo , criterio , sum_range)
Nel nostro esempio, dobbiamo compilare la seconda tabella a destra, la colonna per la quantità di ordini per città. Come puoi vedere dalla sintassi della funzione SOMMA.SE, abbiamo bisogno di tre argomenti.
allineare - questo è l'intervallo di confronto, ovvero l'array in cui verranno confrontati con i criteri. Nel nostro esempio, this A5: A504 , in modo che il nostro intervallo non si sposti trascinando la formula, lo renderemo assoluto inserendo il segno $. Noi abbiamo -$A$5:$A$504
criterio è un argomento, che può essere espresso come numero, espressione, testo, ecc., che determina quali celle sommare. Nel nostro esempio, i criteri sono le città.
sum_range - questo è l'intervallo da sommare se i criteri corrispondono. Nel nostro esempio, questo è l'intervallo: B5: B504 , che trasformeremo anche in assoluto $B$ 5:$B$504 per comodità
Esempio di utilizzo della funzione SOMMA.SE
Per risolvere il problema con questo esempio, nella cella F5, inserisci la seguente formula:
SOMMA.SE($A$5:$A$504 ;E5 ;$B$5:$B$504 )
La logica della funzione SOMMA.SE è la seguente: nell'intervallo $A$5:$A$504 alla ricerca di un criterio E5 (San Pietroburgo), se si trova San Pietroburgo, viene riassunto il numero di ordini da questa riga, ovvero dall'intervallo $ B $ 5: $ B $ 504
Questo è un semplice esempio di utilizzo della funzione SOMMA.SE, lo esamineremo più avanti questa funzione maggiori dettagli con esempi su come utilizzare questa funzione.
Speriamo che l'articolo ti sia stato utile. Se hai domande, assicurati di farle.
Ti saremo grati se clicchi +1 e/o mi piace in fondo a questo articolo o condividi con i tuoi amici utilizzando i pulsanti sottostanti.
Grazie per l'attenzione.
Durante l'elaborazione grandi tavoli a volte è necessario ottenere totali basati su dati situati in intervalli di celle che non consentono l'uso di funzioni di consolidamento dell'indirizzamento diretto o richiedono calcoli aggiuntivi. L'articolo descrive diverse opzioni per costruire modelli sull'esempio del problema economico del calcolo delle entrate lorde.
Per facilità di comprensione, considereremo tutti gli esempi di formule sulla base del problema economico più semplice: calcolare il ricavo totale per valori noti volume delle vendite e prezzi per diversi acquirenti.
Il problema presuppone che il numero di acquirenti e periodi non sia costante. Vuoi ottenere i totali per le vendite e le entrate. Ovviamente, l'applicazione di una funzione di somma semplice (SUM) non funzionerà nemmeno per ottenere totali di volume. Allo stesso modo, l'uso della funzione SUMPRODUCT “sulla fronte” non è adatto per il calcolo delle entrate, poiché gli intervalli di celle del moltiplicatore non sono interconnessi.
In pratica, la soluzione di tali problemi si riduce spesso alla scrittura di formule "non copiabili", oppure richiede la trasformazione delle formule finali dopo l'aggiunta dei dati (vedi foglio Esempio 1, celle C10, C11). Questo metodo è valido solo su piccole quantità di dati e viola i principi di base dell'ottimizzazione del lavoro nei fogli di calcolo.
Il secondo metodo standard per risolvere il problema è creare tabelle aggiuntive con calcoli intermedi.
Questo metodo consente di applicare le stesse formule (copiate) nelle celle risultanti e inoltre non richiede la modifica delle formule finali quando si aggiungono righe o colonne con dati. Queste due condizioni garantiscono la massima affidabilità dei calcoli durante lo sviluppo e il funzionamento del modello economico.
Si noti che il problema della correttezza delle formule totali durante l'aggiunta di righe viene risolto aggiungendo una riga di servizio vuota prima della riga totale (foglio Esempio2, righe 6 e 22). Senza una stringa vuota, quando un nuovo cliente viene aggiunto in fondo all'array, la funzione SOMMA non preleva automaticamente i nuovi dati. Le linee di servizio sono una tecnica "sicura" standard quando si lavora con un numero variabile di linee di dati. Se lo si desidera, possono essere nascosti utilizzando i metodi standard dell'interfaccia di Excel.
Gli svantaggi del metodo di risoluzione del problema attraverso l'aggiunta di calcoli intermedi includono:
- Ulteriori trasformazioni dei dati di origine, spesso difficili da implementare nella pratica (immagina che nel file di origine non ci siano 3, ma 3000 acquirenti).
- La complessità dell'aggiunta di informazioni: un nuovo acquirente deve essere aggiunto non a uno, ma a tre tavoli.
Proviamo a risolvere il problema senza utilizzare tabelle intermedie, ma mantenendo la copiabilità e la correttezza delle formule finali quando si aggiungono acquirenti e periodi.
Come notato sopra, le formule nelle celle C10:C11 risolvono correttamente il problema, ma complicano il lavoro con il modello. Voglio solo attirare l'attenzione sull'uso della funzione SOMMA con calcoli aggiuntivi nella cella C11.
Anche le restanti formule nell'intervallo D10:E11 sono corrette e allo stesso tempo soddisfano pienamente i requisiti del problema. Consideriamoli in modo più dettagliato.
Volume totale, kg: D10
SOMMA.SE($B3:$B9;$B$3;D3:D9)
La formula è molto semplice e ovvia da capire. La somma avviene secondo la condizione fissata nella cella $B$3. Invece di $B$3, potresti scrivere "volume, kg" direttamente nella funzione. Da un lato, questo rende la formula più leggibile, ma dall'altro è meno sicura, poiché quando si modifica il valore nelle celle di intestazione, ad esempio, da "volume, kg" a "volume, l", il funzione non funzionerà correttamente.
Va inoltre notato che la possibilità di utilizzare SOMMA.SE è spesso limitata a causa dell'ammissibilità del controllo per una sola condizione (solo in Excel 2007 è apparsa la funzione SOMMA.SE). In casi complessi, le formule con l'elaborazione dell'array sono indispensabili.
Volume totale, kg: E10
(=SOMMA(SE(MOD(RIGA(E3:E9)-RIGA(E2);2)=1;E3:E9)))
L'idea della formula è calcolare la somma solo in righe dispari di dati. La formula funziona con l'array di celle E3:E9 (le parentesi graffe sono l'attributo). Le righe dispari sono definite qui come resto uguale a 1 quando si divide il numero ordinale della riga dell'intervallo per 2. Inoltre, per mantenere l'integrità, i numeri vengono contati dall'intestazione della tabella ROW (E2). Questo ti permette di trasferire questo tavolo interamente in un altro posto sul foglio corrente o su un altro del modello.
Le formule di elaborazione dell'array vengono applicate premendo Ctrl+Maiusc+Invio
Entrate totali, rub: D11
SUMPRODUCT((D3:D9)*(D4:D10)*($B3:$B9=$B$3))
Uno degli usi non standard della funzione SUMPRODUCT. L'idea è di moltiplicare tre matrici di numeri. Il primo array è volumi, il secondo è prezzi e il terzo è un trigger che moltiplica i dati non necessari per zero.
Trasformiamo la formula selezionando il testo tra parentesi e premendo F9, otteniamo:
SUMPRODUCT((400;0.06;500;0.15;600;0.14;0)*(0.06;500;0.15;600;0.14;0;1500)*(VERO;FALSO ;VERO;FALSO;VERO;FALSO;FALSO))
Mettiamola in un modo più comprensibile:
| 400 | 0,06 | VERO | =24 |
| 0,06 | 500 | MENZOGNA | =0 |
| 500 | 0,15 | VERO | 75 |
| 0,15 | 600 | MENZOGNA | =0 |
| 600 | 0,14 | VERO | 84 |
| 0,14 | 0 | MENZOGNA | =0 |
| 0 | 1500 | MENZOGNA | =0 |
Quando si moltiplicano gli array, il valore logico FALSE viene convertito in 0 e TRUE in 1.
Entrate totali, sfregamento: E11
(=SOMMA(SE(MOD(RIGA(E3:E9)-RIGA(E2);2)=1;(E3:E9)*(E4:E10))))
La formula è molto simile alla formula per il calcolo dei volumi totali, che si trova nella cella E10 (vedi sopra), solo che c'è un'ulteriore moltiplicazione per l'intervallo con uno spostamento verso il basso (E4:E10) - chiamiamolo condizionalmente "fascia di prezzo ", anche se questo non è del tutto corretto nel significato . Durante l'elaborazione di un array, nel momento in cui la funzione condizionale determina che la stringa contiene volumi, avviene un'ulteriore moltiplicazione per una cella con lo stesso numero di serie nel suo array, ma dalla fascia di prezzo. Va notato che quando vengono utilizzati più array in una formula, devono avere la stessa dimensione. Per questo motivo, gli intervalli di elaborazione nelle formule "si fermano" alla riga 10, il che in realtà non è molto corretto. Nel nostro esempio, questo non porta a errori, ma a lavoro pratico si raccomanda di evitare tali situazioni. La soluzione più semplice qui potrebbe essere quella di aggiungere un'altra riga vuota prima dei totali.