In questo articolo, impareremo come utilizzare la funzione SE invece della funzione SUMPRODUCT e SUMIFS in Excel.
Scenario:
In parole semplici, quando si lavora con un lungo set di dati sparso, a volte è necessario trovare la somma dei numeri con alcuni criteri sopra. Ad esempio, trovare la somma degli stipendi in un particolare reparto o avere più criteri su data, nomi, reparto o anche numeri di dati come stipendi al di sotto del valore o quantità al di sopra del valore. Per questo di solito usi la funzione SUMPRODUCT o SUMIFS. Ma non ci crederesti, esegui la stessa funzione con la funzione di base di Excel SE funzione.
Come risolvere il problema?
Devi pensare a come sia possibile eseguire operazioni logiche su array di tabelle usando la funzione SE. SE la funzione in Excel è molto utile, ti aiuterà a svolgere alcune attività difficili in Excel o in qualsiasi altro linguaggio di codifica. La funzione SE verifica le condizioni sull'array corrispondenti ai valori richiesti e restituisce il risultato come array corrispondente alle condizioni True come 1 e False come 0.
Per questo problema, utilizzeremo le seguenti funzioni:
- Funzione SOMMA
- Funzione SE
Richiederemo queste funzioni di cui sopra e un certo senso di base del funzionamento dei dati. le condizioni logiche sugli array possono essere applicate utilizzando operatori logici. Questi operatori logici funzionano sia su testo che su numeri. Di seguito ecco la formula generica. { } parentesi graffe è lo strumento magico per eseguire formule di matrice con la funzione SE.
Formula generica:
| { = SOMMA ( SE ( (logical_1) * (logical_2) *… * (logical_n) , sum_array ) ) } |
Nota: per parentesi graffe ( { } ) Utilizzo Ctrl + Maiusc + Invio quando si lavora con matrici o intervalli in Excel. Questo genererà parentesi graffe sulla formula per impostazione predefinita. NON provare a codificare i caratteri delle parentesi graffe.
Logico 1: verifica la condizione 1 sull'array 1
Logico 2: verifica la condizione 2 sull'array 2 e così via
sum_array: array, l'operazione sum viene eseguita
Esempio :
Tutti questi potrebbero essere fonte di confusione da capire. Quindi, testiamo questa formula eseguendola nell'esempio mostrato di seguito. Qui abbiamo i dati dei prodotti consegnati in diverse città insieme ai corrispondenti campi di categoria e quantità. Qui abbiamo i dati e dobbiamo trovare la quantità di cookie inviati a Boston dove la quantità è maggiore di 40.
La tabella dei dati e la tabella dei criteri sono mostrate nell'immagine sopra. A scopo di comprensione abbiamo utilizzato intervalli denominati per gli array utilizzati. Gli intervalli denominati sono elencati di seguito.
Qui :
Città definita per l'array A2:A17.
Categoria definita per l'array B2:A17.
Quantità definita per l'array C2:C17.
Ora sei pronto per ottenere il risultato desiderato utilizzando la formula seguente.
Usa la formula:
| { = SOMMA ( SE ( (Città="Boston") * (Categoria="Cookie") * (Quantità>40) , Quantità)) } |
Spiegazione :
- Città ="Boston" : controlla che i valori nell'intervallo di città corrispondano a "Boston".
- Categoria="Cookie": controlla i valori nell'intervallo di categorie in modo che corrispondano a "Cookie".
- Quantità > 40 : controlla i valori in Intervallo quantità a ma
- La quantità è una matrice in cui è richiesta la somma.
- La funzione IF controlla tutti i criteri e l'asterisco char (*) moltiplica tutti i risultati dell'array.
= SOMMA (SE ({0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 0; 1; 0; 0; 0}, {33; 87; 58; 38; 54; 51; 28; 36; 28; 44; 23; 27; 43; 42; 33; 30}))
- Ora la funzione SE restituisce solo le quantità corrispondenti agli 1 e il resto viene ignorato.
- La funzione SOMMA restituisce la SOMMA.
Ora la quantità corrispondente a 1 si somma solo per ottenere il risultato.
Come puoi vedere, viene restituita la quantità 43, ma ci sono tre ordini di cookie consegnati a "Boston" con quantità 38, 36 e 43. Avevamo bisogno di una somma di quantità in cui la quantità fosse superiore a 40. Quindi la formula restituisce solo 43. Ora usa altri criteri per ottenere la quantità SUM per città: "Los Angeles" e categoria: "Bar" e quantità inferiore a 50.
Usa la formula
| { = SOMMA ( SE ( ( Città = "Los Angeles") * (Categoria="Bar") * (Quantità < 50), Quantità ) ) } |
Come puoi vedere, la formula restituisce i valori 86 come risultato. Che è la somma di 2 ordini che soddisfano le condizioni con quantità 44 e 42. Questo articolo illustra come sostituire una formula SE nidificata con un SE singolo in una formula di matrice. Questo può essere usato per ridurre la complessità in formule complesse. Tuttavia, questo particolare problema potrebbe essere facilmente risolto con la funzione SUMIFS o SUMPRODUCT.
Utilizzo della funzione SUMPRODUCT:
La funzione SUMPRODUCT restituisce la somma dei valori corrispondenti nell'array. Quindi otterremo gli array per restituire 1s ai valori dell'istruzione True e 0s ai valori dell'istruzione False. Quindi l'ultima somma sarà corrispondente dove tutte le affermazioni sono vere.
Usa la formula:
| = SUMPRODUCT ( -- (Città = "Boston") , -- (Categoria = "Cookie") , -- (Quantità > 40) , Quantità ) |
-- : operazione utilizzata per convertire tutti i TRUE in 1 e i Falsi in 0.
La funzione SUMPRODUCT ricontrolla la SOMMA della quantità restituita dalla funzione SUM e SE spiegata sopra.
Allo stesso modo per il secondo esempio il risultato è lo stesso.
Come puoi vedere, la funzione SUMPRODUCT può eseguire la stessa attività.
Ecco tutte le note di osservazione sull'uso della formula.
Appunti:
- Il sum_array nella formula funziona solo con i numeri.
- Se la formula restituisce l'errore #VALORE, controlla che le parentesi graffe devono essere presenti nella formula come mostrato negli esempi nell'articolo.
- Negazione (--) char cambia i valori, TRUE o 1 in FALSE o 0 e FALSE o 0 in TRUE o 1 .
- Operazioni come uguale a ( = ), minore di uguale a ( <= ), più grande di ( > ) o non uguale a ( ) può essere eseguito all'interno di una formula applicata, solo con numeri.
Spero che questo articolo su Come utilizzare la funzione SE invece della funzione SUMPRODUCT e SUMIFS in Excel sia esplicativo. Trova altri articoli sulle formule di somma qui. Se ti sono piaciuti i nostri blog, condividilo con i tuoi fristarts 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 utilizzare la funzione SUMPRODUCT in Excel: restituisce la SOMMA dopo la moltiplicazione dei valori in più matrici in Excel.
SOMMA se la data è compresa tra : Restituisce la SOMMA dei valori tra date o periodi specificati in Excel.
Somma se la data è maggiore della data indicata: Restituisce la SOMMA dei valori dopo la data o il periodo specificati in Excel.
2 modi per sommare per mese in Excel: Restituisce la SOMMA dei valori all'interno di un determinato mese specifico in Excel.
Come sommare più colonne con condizione: Restituisce la SOMMA dei valori su più colonne con condizione in Excel.
Articoli popolari:
50 scorciatoie Excel per aumentare la produttività : Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
Come usare tla 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.