Come utilizzare la funzione SOMMA e IF, invece, per la funzione MATR.SOMMA.PRODOTTO o SOMMA.PIÙ.SE in Excel
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 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, reparto o persino numeri di dati come stipendi inferiori al valore o quantità superiori al valore. Per questo di solito usi 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. \ {} * le parentesi graffe sono 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 inviati a Boston dove la quantità sia 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 la matrice 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.
Usa la formula:
|
\{ =
(City=”Boston”) (Category=”Cookies”) (Quantity>40) , Quantity)) }* |
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 in modo che 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 a 1 e le pause vengono ignorate.
-
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 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à SUM 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.
Questo articolo illustra come sostituire un link: / logical-formas-excel-nested-if-function [nidificato IF formula] con un singolo IF in una formula di matrice. Questo può essere utilizzato per ridurre la complessità nelle formule complesse. Tuttavia, questo particolare problema potrebbe essere facilmente risolto con link: / summing-excel-sumifs-function [SUMIFS] ʻor link: / summing-excel-sumproduct-function [SUMPRODUCT] `function.
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 funzione SE anziché la 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 amici su Facebook. Puoi anche seguirci su Twitter e Facebook. Ci piacerebbe sentire la tua opinione, facci sapere come possiamo migliorare, completare 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: / keyboard-formula-shortcuts-50-excel-shortcuts-to-aumentare-la-produttività [50 Excel Shortcut to Increase Your Productivity]: Diventa più veloce nel tuo compito. Queste 50 scorciatoie ti faranno lavorare ancora più velocemente su Excel.
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: / 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.
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.