In questo articolo impareremo come sommare gli N valori più grandi in un intervallo in Excel.
Scenario:
In parole semplici, mentre si lavora con numeri di dati, a volte viene data una condizione, ad esempio quando è necessario ottenere la somma dei numeri dei primi 5 numeri dell'array o della parte inferiore dell'array. È possibile eseguire facilmente la soluzione a questo problema utilizzando le funzioni di Excel come spiegato di seguito.
Somma dei valori TOP N
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 assegnato un intervallo e dobbiamo superare i 5 valori nell'intervallo e ottenere la somma dei valori.
Formula generica:
| = SUMPRODUCT ( LARGE ( range, { 1 , 2 ,… . ,n } } ) |
range : range di valori
Valori : numeri separati da virgole come se desideri trovare i primi 3 valori, usa { 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:A23.
Qui l'intervallo viene fornito utilizzando lo strumento excel dell'intervallo denominato.
In primo luogo, dobbiamo trovare i primi cinque valori utilizzando la funzione LARGE 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 ( intervallo, { 1 , 2 , 3 , 4 , 5 } ) ) |
Spiegazione:
- La funzione GRANDE restituisce i primi 5 valori numerici e restituisce l'array alla funzione SUMPRODUCT.
= SUMPRODOTTO ( { 149 , 123 , 100 , 87 , 85 } ) )
- La funzione SUMPRODUCT ottiene l'array dei primi 5 valori, che ha un array dei primi 5 numeri restituisce la SOMMA di quei numeri.
Qui l'intervallo è dato come intervallo denominato. Premi Invio per ottenere la SOMMA dei primi 5 numeri.
Come puoi vedere nell'istantanea sopra, quella somma è 544. La somma dei valori 149 + 123 + 100 + 87 + 85 = 544.
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 ( LARGE ( range, ROW ( 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 ottenuto la somma dei primi 10 numeri { 149 ; 123 ; 100 ; 87; 85; 82; 61; 58; 54; 51 }) che risulta in 850.
Somma dei valori N inferiori
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:
| = SUMPRODUCT ( SMALL ( range, { 1 , 2 ,… . ,n } } ) |
range : range 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:A23.
Qui l'intervallo viene fornito utilizzando lo strumento excel dell'intervallo denominato.
In primo luogo, dobbiamo trovare i Cinque valori inferiori utilizzando la funzione PICCOLO e quindi eseguire l'operazione di somma su quei 5 valori. Ora useremo la seguente formula per ottenere la somma
Usa la formula:
| = SUMPRODUCT ( PICCOLO ( intervallo, { 1 , 2 , 3 , 4 , 5 } ) ) |
Spiegazione:
- La funzione PICCOLO restituisce i 5 valori numerici inferiori e restituisce l'array alla funzione SUMPRODUCT.
= SUMPRODOTTO ( { 23 , 27 , 28 , 28 , 30 } ) )
- La funzione SUMPRODUCT ottiene la matrice dei 5 valori inferiori, che ha una matrice di 5 numeri inferiori restituisce la SOMMA di quei numeri.
Qui l'intervallo è dato come intervallo denominato. Premi Invio per ottenere la SOMMA degli ultimi 5 numeri.
Come puoi vedere nell'istantanea sopra, la somma è 136. La somma dei valori 23 + 27 + 28 + 28 + 30 = 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:
| = SUMPRODUCT ( SMALL ( range, ROW ( INDIRETTO ( "1:10" ) ) |
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 ottenuto la somma degli ultimi 10 numeri che risulta in 307.
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 altrimenti la funzione.
Spero che questo articolo su Come sommare gli N valori più grandi in un intervallo in Excel sia esplicativo. Trova altri articoli sul calcolo dei valori e le relative formule di Excel qui. 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:
Come usare la funzione SE in Excel : l'istruzione IF in Excel controlla la condizione e restituisce un valore specifico se la condizione è VERA o restituisce un altro valore specifico se FALSO.
Come utilizzare la 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 SOMMA.SE in Excel : Questa è un'altra funzione essenziale della dashboard. Questo ti aiuta a riassumere i valori su condizioni specifiche.
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.