Nell'articolo precedente abbiamo imparato come sommare i valori N superiori o inferiori. In questo articolo proviamo a riassumere i valori N superiori o inferiori con un criterio.
Somma dei valori TOP N con criteri
Come risolvere il problema?
Per questo articolo ci verrà richiesto di utilizzare la funzione SUMPRODUCT. Ora faremo una formula con queste funzioni. Qui ci viene dato un intervallo e un criterio. Dobbiamo ottenere i primi 5 valori nell'intervallo e ottenere la somma dei valori in base ai criteri forniti.
Formula generica:
= SUMPRODUCT ( LARGE ( (lista = criteri) * (intervallo), { 1 , 2 ,… . ,n } } )
elenco: elenco criteri
Criteri: criteri da abbinare
gamma: range di valori
valori: numeri separati da virgole come se desideri trovare i primi 3 valori, usa { 1 , 2 , 3 }.
Esempio:
Qui abbiamo i valori del set di dati da A1: D50.
Innanzitutto, dobbiamo trovare i primi cinque valori utilizzando la funzione LARGE 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 ( GRANDE ( ( Città = "Boston" ) * (quantità) , { 1 , 2 , 3 , 4 , 5 } ) )
Spiegazione:
- Il "Boston" della città corrisponde all'intervallo della città menzionato. Questo restituisce un array di true e false.
- La funzione GRANDE restituisce i primi 5 valori numerici dall'intervallo di quantità e restituisce l'array alla funzione SUMPRODUCT.
= SOMMAPRODOTTO { 193 , 149 , 138 , 134 , 123 }
- La funzione SUMPRODUCT ottiene un array dei primi 5 valori, con un array dei primi 5 numeri che restituisce la SOMMA di quei numeri.
Qui l'intervallo Città e quantità è indicato come intervallo denominato. Premi Invio per ottenere la SOMMA dei primi 5 numeri.
Come puoi vedere nell'istantanea sopra, quella somma è 737. La somma dei valori 193 + 149 + 138 + 134 + 123 = 737.
È possibile controllare i valori precedenti nel set di dati utilizzando l'opzione di filtro excel. Applica il filtro all'intestazione Città e quantità e fai clic sul pulsante freccia sull'intestazione della città che appare. Segui i passaggi come mostrato di seguito.
Passaggi:
- Seleziona la cella di intestazione 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 ora 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 indicati. Ciò significa anche che la formula funziona correttamente per ottenere il conteggio di questi valori
GRANDI N numeri
Il processo di cui sopra viene utilizzato per calcolare la somma di alcuni numeri dall'alto. Ma calcolare per n (grande) numero di valori in un intervallo lungo.
Usa la formula:
= SUMPRODUCT ( GRANDE ( ( Città = "Boston" ) * (quantità), RIGA ( INDIRETTO ( "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 di Excel RIGA & INDIRETTO.
Qui abbiamo la somma dei primi 10 numeri che risulta in 1147.
Somma dei valori N inferiori con criteri
Come risolvere il problema?
Per questo articolo ci verrà richiesto di utilizzare la funzione SUMPRODUCT. Ora faremo una formula con queste funzioni. Qui ci viene dato un intervallo e abbiamo bisogno di 5 valori inferiori nell'intervallo e ottenere la somma dei valori.
Formula generica:
{ = SOMMA ( PICCOLO ( SE ( Città = "Boston" , quantità ) , { 1 , 2 , 3 , 4 , 5 } ) ) }
Intervallo: intervallo di valori
Valori: numeri separati da virgole come se si desidera trovare i 3 valori inferiori, utilizzare { 1 , 2 , 3 }.
Esempio:
Tutti questi potrebbero essere fonte di 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 di città e quantità viene fornito utilizzando lo strumento excel dell'intervallo denominato.
Innanzitutto, dobbiamo trovare i cinque valori inferiori utilizzando la funzione PICCOLO 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:
{ = SOMMA ( PICCOLO ( SE ( Città = "Boston" , quantità ) , { 1 , 2 , 3 , 4 , 5 } ) ) }
NON usare le parentesi graffe manualmente. Bretelle ricci applicate utilizzando il Ctrl + Maiusc + Invio al posto di solo accedere.
Spiegazione:
- La funzione PICCOLO con la funzione SE restituisce i 5 valori numerici inferiori che corrispondono a City "Boston" e restituisce l'array alla funzione SOMMA.
= SOMMA ( { 23 , 27 , 28 , 28 , 30 } ) )
- La funzione SOMMA ottiene la matrice dei 5 valori inferiori, che ha una matrice di 5 numeri inferiori restituisce la SOMMA di quei numeri utilizzati con CTRL + MAIUSC + INVIO.
Qui l'intervallo Città e quantità è indicato come intervallo denominato. premere Ctrl + Maiusc + Invio per ottenere la SOMMA dei 5 numeri inferiori poiché questa è una formula di matrice.
Come puoi vedere nell'istantanea sopra, la somma è 136.
Il processo di cui sopra viene utilizzato per calcolare la somma di alcuni numeri dal basso. Ma calcolare per n (grande) numero di valori in un intervallo lungo.
Usa la formula:
{ = SOMMA ( PICCOLO ( SE ( Città = "Boston" , quantità ) , RIGA ( INDIRETTO ("1:10") ) ) ) }
NON usare le parentesi graffe manualmente. Usa Ctrl + Maiusc + Invio invece di usare Invio.
Qui generiamo la somma dei 10 valori inferiori ottenendo un array da 1 a 10 { 1; 2; 3; 4; 5; 6; 7; 8 ; 9; 10 } utilizzando le funzioni di Excel RIGA & INDIRETTO.
Qui abbiamo la somma degli ultimi 10 numeri che risulterà in 155.
Di seguito sono riportate alcune note osservative.
Appunti:
- La formula funziona solo con i numeri.
- La formula funziona solo quando non ci sono 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 null.
- 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. Si prega di condividere la tua domanda qui sotto nella casella dei commenti. Ti assisteremo.
Se ti è piaciuto il nostro blog, condividilo con i tuoi amici 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
Come usare i caratteri jolly in Excel : Conta le celle che corrispondono alle frasi usando i caratteri jolly in Excel
Articoli popolari
50 scorciatoie Excel per aumentare la produttività
Modifica un elenco a discesa
Riferimento assoluto in Excel
Se con la formattazione condizionale
Se con caratteri jolly
Vricerca per data
Converti pollici in piedi e pollici in Excel 2016
Unisci nome e cognome in excel
Conta le celle che corrispondono ad A o B