Come Sum N superiore o inferiore Valori con criteri
Nell’articolo precedente abbiamo imparato come sommare i valori N superiori o inferiori.
In questo articolo proviamo a sommare i valori N superiori o inferiori con un criterio.
Somma dei valori TOP N con criteri
Come risolvere il problema?
Per questo articolo ci sarà richiesto di usare link: / summing-excel-sumproduct-function [SUMPRODUCT function]. Ora creeremo una formula da queste funzioni. Qui ci viene fornito un intervallo e un criterio. Dobbiamo ottenere i primi 5 valori nell’intervallo e ottenere la somma dei valori in base ai criteri dati.
_Formula generica: _
= SUMPRODUCT ( LARGE ( (list = criteria) * (range), { 1 , 2 , .... ,n } } )
elenco: elenco di criteri Criteri: criteri per la corrispondenza intervallo: intervallo di valori valori: numeri separati utilizzando le virgole come se si desidera trovare i primi 3 valori, utilizzare \ {1, 2, 3}.
Esempio:
Qui abbiamo i valori del set di dati da A1: D50.
In primo luogo, dobbiamo trovare i primi cinque valori utilizzando la funzione GRANDE che corrisponde alla città “Boston” e quindi eseguire l’operazione di somma su quei 5 valori. Ora useremo la seguente formula per ottenere la somma _Usa la formula: _
= SUMPRODUCT ( LARGE ( ( City = "Boston" ) * (quantity) , { 1 , 2 , 3 , 4 , 5 } ) )
_Spiegazione: _
-
“Boston” della città corrisponde alla gamma della città menzionata. Ciò restituisce un array di true e false.
-
link: / logical-formas-excel-large-function [The LARGE]restituisce i primi 5 valori numerici dall’intervallo di quantità e restituisce l’array alla funzione SUMPRODUCT.
SUMPRODUCT \ {193, 149, 138, 134, 123} * La funzione SUMPRODUCT ottiene un array dei primi 5 valori, con un array dei primi 5 numeri che restituiscono la SOMMA di quei numeri.
Qui l’intervallo di città e quantità viene fornito come intervallo denominato. Premere Invio per ottenere la SOMMA dei primi 5 numeri.
Come puoi vedere nell’istantanea sopra, la somma è 737. La somma dei valori 193 + 149 + 138 + 134 + 123 = 737.
Puoi controllare i valori sopra nel set di dati utilizzando l’opzione di filtro Excel.
Applica il filtro all’intestazione Città e quantità e fai clic sul pulsante freccia nell’intestazione della città che appare. Segui i passaggi come mostrato di seguito.
Passi:
-
Seleziona la cella di intestazione della città. Applica il filtro usando la scorciatoia Ctrl + Maiusc + L. Fare clic sulla freccia che appare come opzione di filtro.
-
Seleziona l’opzione (Seleziona tutto).
-
Seleziona solo la città di Boston.
-
Seleziona subito l’intestazione della quantità.
-
Ordina l’elenco dal più grande al più piccolo e puoi visualizzare tutti i primi 5 valori che abbiamo calcolato utilizzando la formula.
Come puoi vedere nella gif sopra, tutti i 5 valori che corrispondono ai criteri dati. Questo significa anche che la formula funziona bene per ottenere il conteggio di questi valori
GRANDI numeri N
Il processo di cui sopra viene utilizzato per calcolare la somma di alcuni numeri dall’alto. Ma per calcolare n (grande) numero di valori in un lungo intervallo.
Usa la formula:
= SUMPRODUCT ( LARGE ( ( City = "Boston" ) * (quantity), ROW ( INDIRECT ( "1:10" ) )
Qui generiamo la somma dei primi 10 valori ottenendo un array da 1 a 10 \ {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} utilizzando le funzioni RIGA E INDIRETTO di Excel.
Qui abbiamo la somma dei primi 10 numeri che risulta in 1147.
Somma degli ultimi N valori con criteri
Come risolvere il problema?
Per questo articolo ci sarà richiesto di usare link: / summing-excel-sumproduct-function [SUMPRODUCT function]. Ora creeremo una formula da queste funzioni. Qui ci viene fornito un intervallo e abbiamo bisogno di 5 valori in fondo nell’intervallo e ottenere la somma dei valori.
Formula generica:
{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , { 1 , 2 , 3 , 4 , 5 } ) ) }
Intervallo: intervallo di valori Valori: numeri separati da virgole come se desideri trovare gli ultimi 3 valori, usa \ {1, 2, 3}.
Esempio:
Tutto ciò potrebbe creare confusione da capire. Quindi, testiamo questa formula eseguendola nell’esempio mostrato di seguito.
Qui abbiamo un intervallo di valori da A1: D50.
Qui l’intervallo Città e quantità viene fornito utilizzando lo strumento Excel intervallo denominato.
In primo luogo, dobbiamo trovare gli ultimi cinque valori utilizzando la funzione SMALL che corrisponde ai criteri e quindi eseguire l’operazione di somma su quei 5 valori. Ora useremo la seguente formula per ottenere la somma Usa la formula:
{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , { 1 , 2 , 3 , 4 , 5 } ) ) }
NON utilizzare manualmente le parentesi graffe. Parentesi graffe applicate utilizzando Ctrl Maiusc + Invio al posto di Invio.
Spiegazione:
-
link: / statistics-formas-excel-small-function [SMALL]
funzione con IF funzione restituisce gli ultimi 5 valori numerici che corrispondono a Città “Boston” e restituisce l’array alla funzione SOMMA.
SOMMA (\ {23, 27, 28, 28, 30}))
-
La funzione SOMMA ottiene l’array degli ultimi 5 valori, che ha un array degli ultimi 5 numeri restituisce la SOMMA di quei numeri usati con CTRL + MAIUSC + INVIO.
Qui l’intervallo di città e quantità viene fornito come intervallo denominato. Premi Ctrl + Maiusc + Invio per ottenere la SOMMA degli ultimi 5 numeri poiché si tratta di una formula di matrice.
Come puoi vedere nell’istantanea sopra, la somma è 136.
Il processo precedente viene utilizzato per calcolare la somma di alcuni numeri dal basso. Ma per calcolare n (grande) numero di valori in un lungo intervallo.
Usa la formula:
{ = SUM ( SMALL ( IF ( City = "Boston" , quantity ) , ROW ( INDIRECT ("1:10") ) ) ) }
NON utilizzare manualmente le parentesi graffe. Utilizza Ctrl + Maiusc + Invio al posto di Invio.
Qui generiamo la somma degli ultimi 10 valori ottenendo un array da 1 a 10 \ {1; 2; 3; 4; 5; 6; 7; 8; 9; 10} utilizzando le funzioni RIGA E INDIRETTO di Excel.
Qui abbiamo la somma degli ultimi 10 numeri che risulteranno in 155.
Ecco alcune note osservative mostrate di seguito.
Note:
-
La formula funziona solo con i numeri.
-
La formula funziona solo quando non sono presenti duplicati nella tabella di ricerca. La funzione SUMPRODUCT considera i valori non numerici (come il testo abc) e i valori di errore (come #NUM!, #NULL!) Come valori nulli.
-
La funzione SUMPRODUCT considera il valore logico TRUE come 1 e False come 0.
-
L’array di argomenti deve essere della stessa lunghezza della funzione.
Spero che questo articolo su come restituire la somma dei primi 5 valori o degli ultimi 5 valori con criteri in Excel sia esplicativo. Trova altri articoli sulle funzioni SUMPRODUCT qui. Per favore condividi la tua domanda qui sotto nella casella dei commenti. Ti assisteremo.
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 `link: / tips-excel- caratteri jolly [Come usare i caratteri jolly in Excel *] `: Conta le celle che corrispondono alle frasi usando i caratteri jolly in Excel
Articoli popolari
link: / keyboard-formula-shortcuts-50-excel-scorciatoie-per-aumentare-la-produttività [50 scorciatoia Excel per aumentare la produttività]
link: / excel-generali-how-to-edit-a-dropdown-list-in-microsoft-excel [Edit a dropdown list]
link: / nome-intervallo-excel-riferimento-assoluto-in-excel [Riferimento assoluto in Excel]
link: / tips-conditional-formatting-with-if-statement [If with conditional formatting]
link: / logical-formas-if-function-with-wildcards [If with wildcards]
link: / lookup-formas-vlookup-by-date-in-excel [Vlookup per data]
link: / tips-inch-to-feet [Converti pollici in piedi e pollici in Excel 2016]
link: / excel-text-editing-and-format-join-first-and-last-name-in-excel [Join first and last name in excel]
link: / counting-count-cells-which-match-a-or-b [Conta le celle che corrispondono ad A o B]