Formula generica
=SUMPRODUCT(LARGE((range=criteria)*(values),(1,2,3,N)))
Sommario
Per sommare i primi n valori in un intervallo corrispondente ai criteri, è possibile utilizzare una formula basata sulla funzione LARGE, racchiusa all'interno della funzione SUMPRODUCT. Nella forma generica della formula (sopra), l' intervallo rappresenta un intervallo di celle confrontate con i criteri , i valori rappresentano i valori numerici da cui vengono recuperati i valori superiori e N rappresenta l'idea dell'ennesimo valore.
Nell'esempio, la cella attiva contiene questa formula:
=SUMPRODUCT(LARGE((color=E5)*(value),(1,2,3)))
Dove il colore è l'intervallo denominato B5: B12 e il valore è l'intervallo denominato C5: C12.
Spiegazione
Nella sua forma più semplice, LARGE restituisce il valore "Nth più grande" in un intervallo con questa costruzione:
=LARGE (range,N)
Quindi, ad esempio:
=LARGE (C5:C12,2)
restituirà il secondo valore più grande nell'intervallo C5: C12, che è 12 nell'esempio mostrato.
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 (C5:C12, (1,2,3))
restituirà il 1 °, 2 ° e 3 ° valore più grande C5: C12 in un array come questo: (12,12,10)
Quindi, il trucco qui è filtrare i valori in base al colore prima che LARGE venga eseguito. Lo facciamo con l'espressione:
(color=E5)
Il che si traduce in una matrice di valori VERO / FALSO. Durante l'operazione di moltiplicazione, questi valori vengono convertiti in uno e zeri:
=LARGE((1;0;1;0;1;1;0;0)*(12;12;10;9;8;8;7;5),(1,2,3))
Quindi il risultato finale è che solo i valori associati al colore "rosso" sopravvivono all'operazione:
=SUMPRODUCT(LARGE((12;0;10;0;8;8;0;0),(1,2,3)))
e gli altri valori sono forzati a zero.
Nota: questa formula non gestirà il testo nell'intervallo di valori. Vedi sotto.
Gestione del testo nei valori
Se hai del testo ovunque negli intervalli di valori, la funzione GRANDE genererà un errore #VALORE e interromperà il funzionamento della formula.
Per gestire il testo nell'intervallo di valori, puoi aggiungere la funzione SE.ERRORE in questo modo:
=SUM(IFERROR(LARGE(IF((color=E5),value),(1,2,3)),0))
Qui, intercettiamo errori da LARGE causati da valori di testo e li sostituiamo con zero. L'utilizzo di IF all'interno di LARGE richiede che la formula sia inserita con control + shift + enter, quindi passiamo a SUM invece di SUMPRODUCT.
Nota: mi sono imbattuto in questa formula pubblicata dal fantastico Barry Houdini su stackoverflow.
