Formula generica
=SUMPRODUCT(SMALL(rng,(1,2,n)))
Sommario
Per sommare gli n valori più bassi in un intervallo, è possibile utilizzare una formula basata sulla funzione SMALL e sulla funzione SUMPRODUCT. Nella forma generica della formula (sopra), rng rappresenta un intervallo di celle che contengono valori numerici e n rappresenta il numero di valori più bassi da sommare. Nell'esempio mostrato, E5 contiene questa formula:
=SUMPRODUCT(SMALL(B4:B14,(1,2,3)))
che restituisce la somma dei tre valori più piccoli in B5: B14, 60
Spiegazione
Nella sua forma più semplice, SMALL restituirà il valore "n-esimo più piccolo" in un intervallo. Per esempio:
=SMALL(range,1) // smallest =SMALL(range,2) // 2nd smallest =SMALL(range,3) // 3rd smallest
Tuttavia, se fornisci una costante di matrice (ad es. Una costante nella forma (1,2,3)) a SMALL come secondo argomento, SMALL restituirà una matrice di risultati invece di un singolo risultato. Per esempio:
=SMALL(A1:A10,(1,2,3))
restituirà il primo, il secondo e il terzo valore più piccolo nell'intervallo A1: A10.
Lavorando dall'interno verso l'esterno nell'esempio mostrato, SMALL restituisce i 3 valori più piccoli nell'intervallo B5: B14:
=SMALL(B4:B14,(1,2,3))
Il risultato è un array come questo:
(10,20,30)
Questo array viene restituito direttamente alla funzione SUMPRODUCT, che somma i numeri e restituisce il totale:
SUMPRODUCT((10,20,30)) // returns 60
Formula di matrice con SUM
È comune utilizzare SUMPRODUCT come sopra perché può gestire gli array in modo nativo senza entrare come formula di array. Tuttavia, puoi anche scrivere una formula di matrice con la funzione SUM come questa:
(=SUM(SMALL(B4:B13,(1,2,3))))
Questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO, tranne in Excel 365.
Quando n diventa grande
Quando n diventa grande, diventa noioso creare manualmente la costante di matrice: 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, per SOMMARE gli ultimi 20 valori in un intervallo chiamato "rng" puoi scrivere una formula come questa:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:20"))))
Qui, INDIRETTO converte la stringa "1:20" nell'intervallo 1:20, che viene restituito direttamente a SMALL.
Variabile n
Per impostare la formula a dove n è una variabile in un'altra cella, puoi concatenare all'interno di INDIRETTO. Ad esempio, se A1 contiene N, puoi utilizzare:
=SUMPRODUCT(SMALL(rng,ROW(INDIRECT("1:"&A1))))
Ciò consente a un utente di modificare il valore di n direttamente sul foglio di lavoro.
