Formula generica
=SUMPRODUCT(LARGE(rng,(1,2,N)))
Sommario
Per sommare i primi valori in un intervallo, puoi utilizzare una formula basata sulla funzione GRANDE, racchiusa all'interno della funzione SUMPRODUCT. Nella forma generica della formula (sopra), rng rappresenta un intervallo di celle che contengono valori numerici e N rappresenta l'idea dell'ennesimo valore.
Nell'esempio, la cella attiva contiene questa formula:
=SUMPRODUCT(LARGE(B4:B13,(1,2,3)))
Spiegazione
Nella sua forma più semplice, LARGE restituirà il valore "Nth più grande" in un intervallo. Ad esempio, la formula:
=LARGE(B4:B13, 2)
restituirà il 2 ° valore più grande nell'intervallo B4: B13 che, nell'esempio sopra, è il numero 9.
Tuttavia, se fornisci una "costante di matrice" (ad es. Una costante nella forma (1,2,3)) a LARGE come secondo argomento, LARGE restituirà un array di risultati invece di un singolo risultato. Quindi, la formula:
=LARGE(B4:B13,(1,2,3))
restituirà il 1 °, 2 ° e 3 ° valore più grande nell'intervallo B4: B13. Nell'esempio sopra, dove B4: B13 contiene i numeri 1-10, il risultato di LARGE sarà l'array (8,9,10). SUMPRODUCT quindi somma i numeri in questo array e restituisce un totale, che è 27.
SUM invece di SUMPRODUCT
SUMPRODUCT è una funzione flessibile che consente di utilizzare riferimenti di cella per k all'interno della funzione LARGE.
Tuttavia, se stai usando una semplice costante di array hard-coded come (1,2,3) puoi semplicemente usare la funzione SUM:
=SUM(LARGE(B4:B13,(1,2,3)))
Nota che devi inserire questa formula come formula di matrice se utilizzi riferimenti di cella e non una costante di matrice per k all'interno di LARGE.
Quando N diventa grande
Quando N diventa grande, diventa noioso creare manualmente la costante di matrice: se si desidera sommare i primi 20 o 30 valori in un elenco di grandi dimensioni, la digitazione di una costante di matrice con 20 o 30 elementi richiederà molto tempo. In questo caso, è possibile utilizzare una scorciatoia per creare la costante di matrice che utilizza le funzioni ROW e INDIRECT.
Ad esempio, se vuoi SOMMARE i primi 20 valori in un intervallo chiamato "rng" puoi scrivere una formula come questa:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:20"))))
Variabile N
Con dati insufficienti, una N fissa può causare errori. In questo caso, puoi provare una formula come questa:
=SUMPRODUCT(LARGE(rng,ROW(INDIRECT("1:"&MIN(3,COUNT(rng))))))
Qui, usiamo MIN con COUNT per sommare i primi 3 valori o il conteggio dei valori, se inferiore a 3.
