Formula generica
=SUMPRODUCT(expression,range)
Sommario
Per filtrare i risultati di SUMPRODUCT con criteri specifici, è possibile applicare semplici espressioni logiche direttamente agli array nella funzione, invece di utilizzare la funzione IF. Nell'esempio mostrato, le formule in H5: H7 sono:
=SUMPRODUCT(--(color="red"),quantity,price) =SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price) =SUMPRODUCT(--(state="co"),--(color="blue"),quantity,price)
dove sono definiti i seguenti intervalli denominati:
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14
Se preferisci evitare gli intervalli denominati, utilizza gli intervalli immessi sopra come riferimenti assoluti. Le espressioni logiche in H6 e H7 possono essere combinate, come spiegato di seguito.
Spiegazione
Questo esempio illustra uno dei principali punti di forza della funzione SUMPRODUCT: la capacità di filtrare i dati con espressioni logiche di base invece della funzione IF. All'interno di SUMPRODUCT, il primo array è un'espressione logica da filtrare sul colore "rosso":
--(color="red")
Ciò si traduce in una matrice o valori TRUE FALSE, che vengono convertiti in uno e zero con l'operazione doppia negativa (-). Il risultato è questo array:
(1;0;1;0;0;0;1;0;0;0)
Notare che l'array contiene 10 valori, uno per ogni riga. Uno indica una riga in cui il colore è "rosso" e uno zero indica una riga con qualsiasi altro colore.
Successivamente, abbiamo altri due array: uno per la quantità e uno per il prezzo. Insieme a questo risultato dal primo array, abbiamo:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),quantity,price)
Espandendo gli array, abbiamo:
=SUMPRODUCT((1;0;1;0;0;0;1;0;0;0),(10;6;14;9;11;10;8;9;11;10),(15;18;15;16;18;18;15;16;18;16))
Il comportamento principale di SUMPRODUCT è moltiplicare, quindi sommare gli array. Poiché stiamo lavorando con tre array, possiamo visualizzare l'operazione come mostrato nella tabella seguente, dove la colonna del risultato è il risultato della moltiplicazione di array1 * array2 * array3 :
| array1 | array2 | array3 | risultato |
|---|---|---|---|
| 1 | 10 | 15 | 150 |
| 0 | 6 | 18 | 0 |
| 1 | 14 | 15 | 210 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 18 | 0 |
| 1 | 8 | 15 | 120 |
| 0 | 9 | 16 | 0 |
| 0 | 11 | 18 | 0 |
| 0 | 10 | 16 | 0 |
Si noti che array1 funziona come un filtro: qui i valori zero "annullano" i valori nelle righe in cui il colore non è "rosso". Rimettendo i risultati in SUMPRODUCT, abbiamo:
=SUMPRODUCT((150;0;210;0;0;0;120;0;0;0))
Che restituisce un risultato finale di 480.
Aggiunta di criteri aggiuntivi
È possibile estendere i criteri aggiungendo un'altra espressione logica. Ad esempio, per trovare le vendite totali in cui il colore è "Rosso" e lo stato è "TX", H6 contiene:
=SUMPRODUCT(--(state="tx"),--(color="red"),quantity,price)
Nota: SUMPRODUCT non fa distinzione tra maiuscole e minuscole.
Semplificare con un singolo array
I professionisti di Excel spesso semplificheranno un po 'la sintassi all'interno di SUMPRODUCT moltiplicando gli array direttamente all'interno di array1 in questo modo:
=SUMPRODUCT((state="tx")*(color="red")*quantity*price)
Questo funziona perché l'operazione matematica (moltiplicazione) forza automaticamente i valori VERO e FALSO dalle prime due espressioni in uno e zeri.
