Come uso condizionato Summing per particolari condizioni
In questo articolo impareremo come utilizzare la somma condizionale per condizioni specifiche.
Scenario:
In parole semplici, quando si lavora con un set di dati lungo e disperso, a volte è necessario trovare la somma dei numeri con alcuni criteri su di essa. Ad esempio, trovare la somma degli stipendi in un particolare dipartimento o avere più criteri su data, nomi, dipartimento o persino numeri di dati come stipendi al di sotto del valore o quantità al di sopra del valore. Per questo di solito si utilizza la funzione SUMPRODUCT o SUMIFS. Ma non ci crederesti, esegui la stessa funzione con la funzione IF di base di Excel.
Come risolvere il problema?
Devi pensare a come sia possibile eseguire operazioni logiche su array di tabelle utilizzando la funzione IF. SE la funzione in Excel è molto utile, ti consentirà di svolgere alcune attività difficili in Excel o in qualsiasi altro linguaggio di codifica. La funzione IF 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:
-
link: / math-and-trig-excel-sum-function [SUM function] -
link: / tips-if-condition-in-excel [IF function]
Richiederemo queste funzioni di cui sopra e un senso di base del funzionamento dei dati. le condizioni logiche sugli array possono essere applicate utilizzando operatori logici. Questi operatori logici lavorano sia sul testo che sui numeri. Di seguito ecco la formula generica. \ {} parentesi graffe è lo strumento magico per eseguire formule di matrice con la funzione SE.
Formula generica:
|
\{ =
(logical_1) (logical_2) … (logical_n) , sum_array ) ) }* |
Nota: per le parentesi graffe (\ {}) utilizzare 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 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, viene eseguita l’operazione sum
Esempio:
Tutto ciò potrebbe creare 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 campi delle categorie e alle quantità corrispondenti. Qui abbiamo i dati e dobbiamo trovare la quantità di cookie inviata a Boston dove la quantità sia maggiore di 40.
La tabella dei dati e la tabella dei criteri sono mostrate nell’immagine sopra. Per scopi di comprensione abbiamo utilizzato intervalli denominati per gli array utilizzati. Gli intervalli denominati sono elencati di seguito.
Qui:
Città definita per array A2: A17.
Categoria definita per l’array B2: A17.
Quantità definita per array C2: C17.
Ora sei pronto per ottenere il risultato desiderato utilizzando la formula seguente.
Spiegazione:
-
Città = “Boston”: controlla i valori nell’intervallo di città in modo che corrispondano a “Boston”.
-
Categoria = “Cookie”: controlla i valori nell’intervallo di categorie affinché corrispondano a “Cookie”.
-
Quantità> 40: controlla i valori in Intervallo quantità su ma. La quantità è un array in cui è richiesta la somma.
-
La funzione IF controlla tutti i criteri e il carattere asterisco (*) 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 le pause vengono ignorate.
-
La funzione SOMMA restituisce 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 biscotti consegnati a “Boston” con quantità 38, 36 e 43. Avevamo bisogno di una somma della quantità dove la quantità fosse superiore a 40. Quindi la formula restituisce solo 43. Ora usa altri criteri per ottenere la quantità SOMMA per città: “Los Angeles” e categoria: “bar” e quantità inferiore a 50.
Usa la formula
|
\{ =
“Los Angeles”) (Category=”Bars”) (Quantity < 50), Quantity ) ) } |
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.
Puoi usare link: / logical-formas-excel-nested-if-function [nidificato IF], link: / summing-excel-sumifs-function [SUMIFS]
o link: / summing-excel-sumproduct-function [SUMPRODUCT]
funzione in Excel per risolvere lo stesso problema. Possiamo ricontrollare il risultato usando la funzione SUMPRODUCT
Uso della funzione SUMPRODUCT:
La funzione SUMPRODUCT restituisce la somma dei valori corrispondenti nella matrice. Quindi faremo in modo che gli array restituiscano 1s ai valori dell’istruzione True e 0s ai valori dell’istruzione False. Quindi l’ultima somma corrisponderà a dove tutte le affermazioni sono True.
Usa la formula:
|
= ( — (City = “Boston”) , — (Category = “Cookies”) , — (Quantity > 40) , Quantity ) |
-: operazione usata per convertire tutti i TRUE in 1 e False in 0.
La funzione SUMPRODUCT ricontrolla la SOMMA della quantità restituita dalla funzione SOMMA 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 operazione.
Ecco tutte le note osservative riguardanti l’utilizzo della formula.
Note:
-
Il sum_array nella formula funziona solo con i numeri.
-
Se la formula restituisce l’errore #VALORE, verificare che le parentesi graffe siano presenti nella formula come mostrato negli esempi nell’articolo.
-
Il carattere negativo (-) 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 (⇐), maggiore di (>) o non uguale a (<>) possono essere eseguite all’interno di una formula applicata, solo con numeri.
Spero che questo articolo su Come utilizzare la somma condizionale per condizioni specifiche in Excel sia esplicativo. Trova altri articoli sulle formule di somma qui. Se ti sono piaciuti i nostri blog, condividilo con i tuoi amici su Facebook. Puoi anche seguirci su Twitter e Facebook. Ci piacerebbe sentire la tua opinione, facci sapere come possiamo migliorare, integrare o innovare il nostro lavoro e renderlo migliore per te. Scrivici a [email protected]
Articoli correlati:
link: / summing-excel-sumproduct-function [Come usare la funzione SUMPRODUCT in Excel]: Restituisce la SOMMA dopo la moltiplicazione dei valori in più array in Excel.
link: / summing-sum-if-date-is-between [SUM se la data è compresa tra]: Restituisce la SOMMA dei valori tra date date o periodo in Excel.
link: / summing-sum-if-date-is-greater-than-given-date [Sum se la data è maggiore della data specificata]: * Restituisce la SOMMA dei valori dopo la data o il periodo specificati in Excel.
link: / summing-2-ways-to-sum-by-month-in-excel [2 modi per sommare per mese in Excel]: * Restituisce la SOMMA dei valori all’interno di un dato mese specifico in Excel.
link: / summing-how-to-sum-multiple-columns-with-condition [How to Sum Multiple Columns with Condition]: * Restituisce la SOMMA dei valori su più colonne con condizione in Excel.
Articoli popolari:
link: / tips-if-condition-in-excel [Come utilizzare la funzione IF in Excel]: L’istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è TRUE o restituisce un altro valore specifico se FALSE .
link: / formule-e-funzioni-introduzione-di-vlookup-funzione [Come usare la funzione CERCA.VERT in Excel]: Questa è una delle funzioni più utilizzate e popolari di Excel che viene utilizzata per cercare valori da intervalli diversi e lenzuola.
link: / excel-formula-and-function-excel-sumif-function [Come usare la funzione SUMIF in Excel]: Questa è un’altra funzione essenziale del dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.
link: / tips-countif-in-microsoft-excel [Come usare la funzione CONTA.SE in Excel]: Conta i valori con le condizioni usando questa straordinaria funzione. Non è necessario filtrare i dati per contare valori specifici. La funzione Countif è essenziale per preparare la tua dashboard.