Formula generica
(=SUM(SMALL(IF(range1=criteria,range2),(1,2,3,N))))
Sommario
Per sommare gli ultimi n valori in un intervallo corrispondente ai criteri, è possibile utilizzare una formula di matrice basata sulla funzione SMALL, racchiusa all'interno della funzione SUM. Nella forma generica della formula (sopra), intervallo1 rappresenta l'intervallo di celle rispetto ai criteri , intervallo2 contiene valori numerici da cui vengono recuperati i valori inferiori e N rappresenta "nth".
Nell'esempio, la cella attiva contiene questa formula:
=SUM(SMALL(IF(color=E5,value),(1,2,3)))
Dove il colore è l'intervallo denominato B5: B12 e il valore è l'intervallo denominato C5: C12.
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO.
Spiegazione
Nella sua forma più semplice, SMALL restituisce il valore "N-esimo più piccolo" in un intervallo con questa costruzione:
=SMALL (range,N)
Quindi, ad esempio:
=SMALL (C5:C12,2)
restituirà il secondo valore più piccolo nell'intervallo C5: C12, che è 5 nell'esempio mostrato.
Tuttavia, se fornisci una "costante di matrice" (ad esempio una costante nella forma (1,2,3)) a SMALL come secondo argomento, SMALL restituirà un array di risultati invece di un singolo risultato. Quindi, la formula:
=SMALL (C5:C12, (1,2,3))
restituirà il 1 °, 2 ° e 3 ° valore più piccolo C5: C12 in un array come questo: (4,5,7).
Quindi, il trucco qui è filtrare i valori in base al colore prima che venga eseguito SMALL. Lo facciamo con un'espressione basata sulla funzione IF:
IF(color=E5,value)
Questo costruisce l'array di valori inseriti in SMALL. In sostanza, solo i valori associati al colore rosso vengono inseriti nell'array. Dove il colore è "rosso", l'array contiene un numero e dove il colore non è rosso, l'array contiene FALSE:
SMALL((12;FALSE;10;FALSE;8;4;FALSE;FALSE),(1,2,3)))
La funzione SMALL ignora i valori FALSE e restituisce i 3 valori più piccoli nella matrice: (4,8,10). La funzione SOMMA restituisce il risultato finale, 22.
