Come utilizzare la funzione SUMPRODUCT di Excel -

Sommario

Sommario

La funzione SUMPRODUCT moltiplica insieme intervalli o matrici e restituisce la somma dei prodotti. Sembra noioso, ma SUMPRODUCT è una funzione incredibilmente versatile che può essere utilizzata per contare e sommare come COUNTIFS o SUMIFS, ma con maggiore flessibilità. Altre funzioni possono essere facilmente utilizzate all'interno di SUMPRODUCT per estendere ulteriormente le funzionalità.

Scopo

Moltiplica, quindi somma gli array

Valore di ritorno

Il risultato di array multipli e sommati

Sintassi

= SUMPRODUCT (array1, (array2), …)

argomenti

array1 - Il primo array o intervallo da moltiplicare, quindi aggiungere.
array2 - (opzionale) Il secondo array o intervallo da moltiplicare, quindi aggiungere.

Versione

Excel 2003

Note sull'utilizzo

La funzione SUMPRODUCT funziona con gli array, ma non richiede la normale sintassi degli array (Ctrl + Maiusc + Invio) per entrare. Lo scopo della funzione SUMPRODUCT è moltiplicare, quindi sommare, gli array. Se viene fornito un solo array, SUMPRODUCT sommerà semplicemente gli elementi nell'array. Possono essere forniti fino a 30 array.

Quando incontri per la prima volta SUMPRODUCT, può sembrare noioso, complesso e persino inutile. Ma SUMPRODUCT è una funzione incredibilmente versatile con molti usi. Poiché gestirà gli array con grazia e senza lamentele, è possibile utilizzarlo per elaborare intervalli di celle in modi intelligenti ed eleganti (vedere i collegamenti agli esempi di formule in questa pagina).

Per illustrare come funziona SUMPRODUCT, ecco alcuni esempi comuni.

SUMPRODUCT per somme e conteggi condizionali

Supponiamo di avere alcuni dati dell'ordine in A2: B6, con Stato nella colonna A, Vendite nella colonna B:

	UN	B
1	Stato	I saldi
2	UT	75
3	CO	100
4	TX	125
5	CO	125
6	TX	150

Utilizzando SUMPRODUCT, puoi contare le vendite totali per il Texas ("TX") con questa formula:

=SUMPRODUCT(--(A2:A6="TX"))

E puoi sommare le vendite totali per il Texas ("TX") con questa formula:

=SUMPRODUCT(--(A2:A6="TX"),B2:B6)

Nota: non lasciarti confondere dal doppio negativo. Questo è un trucco comune utilizzato nelle formule Excel più avanzate per forzare i valori VERO e FALSO in 1 e 0. Vedi di più sotto …

Per l'esempio di somma sopra, ecco una rappresentazione virtuale dei due array come prima elaborati da SUMPRODUCT:

array1	array2
FALSO	75
FALSO	100
VERO	125
FALSO	125
VERO	150

Ogni matrice ha 5 elementi. La prima matrice contiene i valori VERO / FALSO che risultano dall'espressione A2: A6 = "TX" e la seconda matrice è il contenuto di B2: B6. Ogni elemento nel primo array verrà moltiplicato per l'elemento corrispondente nel secondo array. Tuttavia, nello stato corrente, il risultato di SUMPRODUCT sarà zero perché i valori TRUE e FALSE verranno considerati zero. Abbiamo bisogno che gli elementi in array1 siano numerici - devono essere "forzati" in uno e zeri. È qui che entra in gioco il doppio negativo.

Utilizzando il doppio negativo - (doppio unario, per voi tipi tecnici) siamo in grado di forzare il VERO / FALSO nei valori numerici uno e zero, come mostrato nella rappresentazione virtuale sotto. L'ultima colonna "Prodotto" rappresenta il risultato della moltiplicazione dei due array. Il risultato sommato, 275, è il valore restituito da SUMPRODUCT.

array1		array2		Prodotto
0	*	75	=	0
0	*	100	=	0
1	*	125	=	125
0	*	125	=	0
1	*	150	=	150
Somma				275

Utilizzando la sintassi delle parentesi graffe per gli array, l'esempio è simile al seguente dopo la coercizione:

=SUMPRODUCT((0,0,1,0,1),(75,100,125,125,150))

e così dopo la moltiplicazione:

=SUMPRODUCT((0,0,125,0,150))

Questo esempio espande le idee sopra con maggiori dettagli.

SUMPRODUCT con altre funzioni

SUMPRODUCT può utilizzare direttamente altre funzioni. Potresti vedere SUMPRODUCT utilizzato con la funzione LEN per contare i caratteri totali in un intervallo, o con funzioni come ISBLANK, ISTEXT, ecc. Normalmente non sono funzioni di matrice, ma quando viene assegnato un intervallo, creano un "array di risultati". Poiché SUMPRODUCT è progettato per funzionare con gli array, è in grado di eseguire calcoli direttamente sugli array. Questo può essere un buon modo per risparmiare spazio in un foglio di lavoro, eliminando la necessità di una colonna "helper".

Ad esempio, supponi di avere 10 diversi valori di testo in A1: A10 e di voler contare i caratteri totali per tutti i 10 valori. È possibile aggiungere una colonna helper nella colonna B che utilizza questa formula: LEN (A1) per calcolare i caratteri in ogni cella. Quindi potresti usare SUM per sommare tutti i 10 numeri. Tuttavia, utilizzando SUMPRODUCT, puoi scrivere una formula come questa:

=SUMPRODUCT(LEN(A1:A10))

Se utilizzato con un intervallo come A1: A10, LEN restituirà un array di 10 valori. Quindi SUMPRODUCT sommerà semplicemente tutti i valori e restituirà il risultato, senza bisogno di colonne helper.

Vedi esempi sotto molti altri modi per usare SUMPRODUCT.

Appunti:

SUMPRODUCT considera gli elementi non numerici nelle matrici come zeri.
Gli argomenti della matrice devono avere la stessa dimensione. Altrimenti, SUMPRODUCT genererà un #VALORE! valore di errore.
I test logici all'interno degli array creeranno valori TRUE e FALSE. Nella maggior parte dei casi, ti consigliamo di forzarli a 1 e 0.
SUMPRODUCT può spesso utilizzare direttamente il risultato di altre funzioni (vedere esempi di formule di seguito)