Scarica la cartella di lavoro di esempio
Questo tutorial mostra come usare il Funzione SUMPRODOTTO di Excel in Excel.
SUMPRODUCT Panoramica delle funzioni
La funzione SUMPRODUCT Moltiplica array di numeri e somma l'array risultante.
Per utilizzare la funzione del foglio di lavoro Excel SUMPRODUCT, seleziona una cella e digita:
(Notare come appaiono gli input della formula)
SUMPRODUCT funzione Sintassi e input:
| 1 | =SOMMA.PRODOTTO(matrice1,matrice2,matrice3) |
array1 - Matrici di numeri.
Che cos'è la funzione SUMPRODUCT?
La funzione SUMPRODUCT è una delle funzioni più potenti all'interno di Excel. È il nome, potrebbe farti credere che sia pensato solo per calcoli matematici di base, ma può essere usato per molto di più.
matrici
SUMPRODUCT richiede input di array.
Quindi, per prima cosa, cosa intendiamo per "array"? Un array è semplicemente un gruppo di elementi (es. numeri) disposti in un ordine specifico, proprio come un intervallo di celle. Quindi, se avessi i numeri 1, 2, 3 nelle celle A1: A3, Excel lo leggerebbe come array {1,2,3}. In effetti, puoi inserire {1,2,3} direttamente nelle formule di Excel e riconoscerà l'array.
Di seguito parleremo di più degli array, ma prima diamo un'occhiata a un semplice esempio.
Matematica di base
Diamo un'occhiata a un esempio di base di SUMPRODUCT, utilizzandolo per calcolare le vendite totali.
Abbiamo la nostra tabella dei prodotti e vogliamo calcolare le vendite totali. Sei tentato di aggiungere semplicemente una nuova colonna, prendere la quantità venduta * prezzo e quindi sommare la nuova colonna. Invece, tuttavia, puoi semplicemente utilizzare la funzione SUMPRODUCT. Esaminiamo la formula:
| 1 | =SOMMA.PRODOTTO(A2:A4;B2:B4) |
La funzione caricherà gli intervalli di numeri in array, li moltiplicherà l'uno contro l'altro e quindi sommerà i risultati:
| 1234 | =SOMMAPRODOTTO({100, 50, 10}, {6, 7, 5})=SOMMAPRODOTTO({100 * 6, 50 * 7, 10 * 5})=SOMMAPRODOTTO({600, 350, 50}=1000 |
Il SUMPRODUCT Funciton è stato in grado di moltiplicare tutti i numeri per noi E fare la somma.
Media ponderata
Un altro caso in cui è utile utilizzare SUMPRODUCT è quando è necessario calcolare una media ponderata. Questo si verifica più spesso quando si ha a che fare con i compiti scolastici, quindi consideriamo la seguente tabella.
Possiamo vedere quanto valgono i quiz, i test e i compiti per il voto complessivo, nonché qual è la media attuale per ogni particolare elemento. Possiamo quindi calcolare il voto complessivo scrivendo
| 1 | =SOMMA.PRODOTTO(B2:B4, C2:C4) |
La nostra funzione moltiplica nuovamente ogni elemento negli array prima di sommare il totale. Funziona così
| 123 | =SOMMA.PRODOTTO({30%, 50%, 20%}, {73%, 90%, 95%})=SOMMAPRODOTTO({22%, 45%, 19%})=86% |
Colonne multiple
Un altro posto in cui potremmo usare SUMPRODUCT è con ancora più colonne che devono essere tutte moltiplicate l'una contro l'altra. Diamo un'occhiata a un esempio in cui dobbiamo calcolare il volume in pezzi di legname.
Invece di creare una colonna di supporto per calcolare la vendita totale per ogni riga, possiamo farlo con una singola formula. La nostra formula sarà
| 1 | =SOMMA.PRODOTTO(B2:B5, C2:C5, D2:D5) |
I primi elementi di ogni array si moltiplicheranno l'uno contro l'altro (ad esempio, 4 * 2 * 1 = 8). Quindi, il 2° (4 * 2 * 2 = 16) e 3rd, ecc. Nel complesso, questo produrrà la serie di prodotti che assomigliano a {8, 16, 16, 32). Quindi il volume totale sarebbe la somma di quell'array, 72.
Un criterio
Ok, aggiungiamo un altro livello di complessità. Abbiamo visto che SUMPRODUCT può gestire array di numeri, ma che dire se volessimo controllare i criteri? Bene, puoi anche creare array per valori booleani (i valori booleani sono valori che sono VERO o FALSO).
Ad esempio, prendi un array di base {1, 2, 3}. Creiamo un array corrispondente che indichi se ogni numero è maggiore di 1. Questo array assomiglierebbe a {FALSE, TRUE, TRUE}.
Questo è estremamente utile nelle formule, perché possiamo facilmente convertire VERO / FALSO in 1 / 0. Diamo un'occhiata a un esempio.
Utilizzando la tabella sottostante, vogliamo calcolare "Quante unità vendute erano rosse?"
Possiamo farlo, con questa formula:
| 1 | =SOMMA.PRODOTTO(A2:A4, --(B2:B4="Rosso")) |
"Aspettare! Cosa c'è con il simbolo del doppio meno lì?" tu dici. Ricordi come ho detto che potevamo convertire da Vero/Falso in 1/0? Lo facciamo costringendo il computer a fare un'operazione matematica. In questo caso, stiamo dicendo "prendi il valore negativo e poi prendi di nuovo il negativo". Scrivendolo, il nostro array cambierà in questo modo:
| 123 | {Vero, Vero, Falso}{-1, -1, 0}{1, 1, 0} |
Quindi, tornando alla formula SUMPRODUCT completa, verrà caricata nei nostri array e quindi moltiplicata, in questo modo
| 123 | =SOMMA.PRODOTTO({100, 50, 10}, {1, 1, 0})=SOMMA.PRODOTTO({100, 50, 0})=150 |
Nota come il 3rd elemento è diventato uno 0, perché qualsiasi cosa moltiplicata per 0 diventa zero.
Criteri multipli
Possiamo caricare fino a 255 array nella nostra funzione, quindi possiamo certamente caricare più criteri. Diamo un'occhiata a questa tabella più grande in cui abbiamo aggiunto il mese venduto.
Se vogliamo sapere quanti articoli venduti erano rossi e fossimo nel mese di febbraio, potremmo scrivere la nostra formula like
| 1 | =SOMMA.PRODOTTO(A2:A4, --(B2:B4="Rosso"), --(C2:C4="Feb")) |
Il computer valuterebbe quindi i nostri array e li moltiplicherebbe. Abbiamo già spiegato come gli array True/False vengono modificati in 1/0, quindi per ora salterò questo passaggio.
| 123 | =SOMMA.PRODOTTO({100, 50, 10}, {1, 1, 0}, {0, 1, 1})=SOMMA.PRODOTTO({0, 50, 0})=50 |
Avevamo solo una riga nel nostro esempio che corrispondeva a tutti i criteri, ma con dati reali, potresti aver avuto più righe che avevi bisogno di aggiungere insieme.
Criteri complessi
Ok, fino a questo punto, potresti non essere impressionato perché tutti i nostri esempi avrebbero potuto essere eseguiti utilizzando altre funzioni come SOMMA.SE o CONTA.SE. Ora faremo qualcosa con le altre funzioni non posso fare. In precedenza, la nostra colonna Mese aveva i nomi effettivi dei mesi. E se invece avesse delle date?
Non possiamo fare un SUMIF ora, perché SUMIF non può gestire i criteri di cui abbiamo bisogno. Tuttavia, SUMPRODUCT può gestirci manipolando l'array e facendo un test più approfondito. Abbiamo già manipolato gli array quando abbiamo tradotto True/False in 1/0. Manipoleremo questo array con la funzione MONTH. Ecco la formula completa che useremo
| 1 | =SOMMA.PRODOTTO(A2:A4, --(B2:B4="Rosso"), --(MESE(C2:C4)=2)) |
Diamo un'occhiata al 3rd matrice più da vicino. Innanzitutto, la nostra formula estrarrà il numero del mese da ciascuna data in C2: C4. Questo ci darà {1, 2, 2}. Successivamente, controlliamo se quel valore è uguale a 2. Ora il nostro array sembra {False, True, True}. Facciamo di nuovo il doppio meno e abbiamo {0, 1, 1}. Ora siamo tornati in un punto simile a quello dell'Esempio 3 e la nostra formula sarà in grado di dirci che a febbraio sono state vendute 50 unità rosse.
Doppio meno contro moltiplicazione
Se hai già visto in uso la funzione SUMPRODUCT, potresti aver visto una notazione leggermente diversa. Invece di usare un doppio meno, puoi scrivere
| 1 | =SOMMA.PRODOTTO(A2:A4*(B2:B4="Rosso")*(MESE(C2:C4)=2)) |
La formula continuerà a funzionare allo stesso modo, stiamo solo dicendo manualmente al computer che vogliamo moltiplicare gli array. SUMPRODUCT lo avrebbe fatto comunque, quindi non c'è alcun cambiamento nel modo in cui funziona la matematica. L'esecuzione dell'operazione matematica converte il nostro Vero/Falso in 1/0 allo stesso modo. Allora, perché la differenza?
Il più delle volte, non importa troppo e dipende dalle preferenze dell'utente. C'è almeno un caso in cui è necessario moltiplicare.
Quando si utilizza SUMPRODUCT, il computer si aspetta che tutti gli argomenti (array1, array2, ecc.) abbiano la stessa dimensione. Ciò significa che hanno lo stesso numero di righe o colonne. Tuttavia, puoi fare ciò che è noto come un calcolo di array bidimensionale con SUMPRODUCT che vedremo nel prossimo esempio. Quando lo fai, gli array sono di dimensioni diverse, quindi dobbiamo ignorare il controllo "tutti della stessa dimensione".
Due dimensioni
Tutti gli esempi precedenti avevano i nostri array che andavano nella stessa direzione. SUMPRODUCT può gestire le cose in due direzioni, come vedremo nella prossima tabella.
Ecco la nostra tabella delle unità vendute, ma i dati vengono riorganizzati in base alle categorie in alto. Se vogliamo scoprire quanti articoli erano Rossi e nella categoria A, possiamo scrivere
| 1 | =SOMMA.PRODOTTO((A2:A4="Rosso")*(B1:C1="A")*B2:C4) |
Che cosa sta succedendo qui?? Si scopre che moltiplicheremo in due direzioni diverse. Visualizzare questo è più difficile da fare con solo una frase scritta, quindi abbiamo alcune immagini per aiutarci. Innanzitutto, i nostri criteri di riga (è rosso?) Si moltiplicheranno su ogni riga dell'array.
| 1 | =SOMMA.PRODOTTO((A2:A4="ROSSO")*B2:C4) |
Successivamente, i criteri della colonna (è la categoria A?) Moltiplicano per ogni colonna
| 1 | =SOMMA.PRODOTTO((A2:A4="Rosso")*(B1:C1="A")*B2:C4) |
Dopo che entrambi i criteri hanno svolto il loro lavoro, gli unici diversi da zero rimasti sono 5 e 10. SUMPRODUCT ci darà quindi il totale complessivo di 15 come risposta.
Ricordi come abbiamo parlato degli array che devono essere della stessa dimensione a meno che tu non stia facendo due dimensioni? Questo era parzialmente corretto. Riguarda gli array che abbiamo usato nella nostra formula. Il altezza di due dei nostri array è lo stesso, e il larghezza di due dei nostri array sono gli stessi. Quindi, devi ancora assicurarti che le cose si allineino correttamente, ma puoi farlo in diverse dimensioni.
Due dimensioni e complesso
Molte volte ci vengono presentati dati che non sono nel miglior layout adatto alle nostre formule. Potremmo provare a riorganizzarlo manualmente o possiamo essere più intelligenti con le nostre formule. Consideriamo la seguente tabella.
Qui abbiamo i dati per i nostri articoli e le vendite mescolati insieme per ogni mese. Come faremmo a scoprire quanti articoli Bob ha venduto per l'intero anno?
Per fare ciò, utilizzeremo due funzioni aggiuntive: CERCA e VAL.NUMERO. La funzione SEARCH ci permetterà di cercare la nostra parola chiave "elementi" all'interno delle celle di intestazione. L'output di questa funzione verrà visualizzato con un numero o un errore (se la parola chiave non viene trovata). Quindi, useremo ISNUMBER per convertire Quello output nei nostri valori booleani. La nostra formula sarà simile a quella di seguito.
Dovresti avere abbastanza familiarità con il primo array ormai. Creerà un output come {0, 1, 0, 1}. Il prossimo array di criteri di cui abbiamo appena parlato. Creerà un numero per tutte le celle che contengono "Elementi" e un errore per le altre {5, #N/A!, 5, #N/A!}. ISNUMBER quindi lo converte in booleano {True, False, True, False}. Quindi, quando moltiplichiamo, manterrà solo i valori della prima e della terza colonna. Dopo che tutti gli array si sono moltiplicati l'uno contro l'altro, gli unici numeri diversi da zero che avremo sono quelli evidenziati qui:
| 1 | =SOMMA.PRODOTTO((A2:A5="Bob")*(VAL.NUMERO(RICERCA("Articoli",B1:E1))*B2:E5)) |
Il SUMPRODUCT li sommerà e otterremo il nostro risultato finale di 29.
SUMPRODOTTO Oppure
Si verificano molte situazioni in cui vorremmo essere in grado di riassumere i valori se la nostra colonna dei criteri ha un valore OPPURE un altro valore. Puoi farlo in SUMPRODUCT aggiungendo due array di criteri l'uno contro l'altro.
In questo esempio, vogliamo sommare le unità vendute sia per il Rosso che per il Blu.
La nostra formula sarà simile a questa
| 1 | =SOMMA.PRODOTTO(A2:A7, (B2:B7="Rosso")+(B2:B7="Blu")) |
Diamo un'occhiata all'array dei criteri rossi. Verrà prodotto un array simile a questo: {1, 1, 0, 0, 0, 0}. L'array dei criteri blu sarà simile a {0, 0, 1, 0, 1, 0}. Quando li aggiungi insieme, il nuovo array sarà simile a {1, 1, 1, 0, 1, 0}. Possiamo vedere come i due array si sono fusi insieme in un unico array di criteri. La funzione quindi lo moltiplicherà per il nostro primo array e otterremo {100, 50, 10, 0, 75, 0}. Notare che i valori per il verde sono stati azzerati. Il passaggio finale del SUMPRODUCT è sommare tutti i numeri per raggiungere la nostra soluzione di 235.
Una parola di cautela qui. Fai attenzione quando gli array di criteri non si escludono a vicenda. Nel nostro esempio, i valori nella colonna B potrebbero essere Rosso o Blu, ma sapevamo che non avrebbero mai potuto essere entrambi. Considera se avessimo scritto questa formula:
| 1 | =SOMMA.PRODOTTO(A2:A7, (A2:A7>=50)+(B2:B7="Blu")) |
Il nostro intento è quello di trovare gli articoli Blu che sono stati venduti o erano in quantità superiore a 50. Tuttavia, queste condizioni non sono esclusive, poiché una singola riga potrebbe essere superiore a 50 nella colonna A e essere blu. Ciò comporterebbe il primo array di criteri come {1, 1, 0, 1, 1, 0}, il secondo array di criteri {0, 0, 1, 0, 1, 0}. La loro somma ha prodotto {1, 1, 1, 1, 2, 0}. Vedi come abbiamo un 2 lì dentro adesso? Se lasciato da solo, SUMPRODUCT finirebbe per raddoppiare il valore in quella riga, cambiando 75 in 150 e otterremmo il risultato sbagliato. Per correggere ciò, inseriamo un controllo dei criteri esterni sul nostro array, in questo modo:
| 1 | =SOMMA.PRODOTTO(A2:A7, --((A2:A7>=50)+(B2:B7="Blu")>0)) |
Ora, dopo che i due array di criteri interni sono stati aggiunti insieme, controlleremo se il risultato è maggiore di 0. Questo elimina il 2 che avevamo prima e invece avremo un array come {1, 1, 1 , 1, 1, 0} che produrrà il risultato corretto.
SUMPRODOTTO Esatto
La maggior parte delle funzioni in Excel non fa distinzione tra maiuscole e minuscole, ma a volte è necessario essere in grado di eseguire una ricerca tenendo presente la distinzione tra maiuscole e minuscole. Quando il risultato desiderato è numerico, possiamo farlo utilizzando EXACT all'interno della funzione SUMPRODUCT. Considera la seguente tabella:
Vogliamo trovare il punteggio per l'elemento "ABC123". Normalmente, la funzione EXACT confronterà due elementi e restituirà un output booleano che indica se i due elementi sono Esattamente lo stesso. Tuttavia, poiché siamo all'interno di un SUMPRODUCT, il nostro computer saprà che abbiamo a che fare con array e sarà in grado di confrontare un elemento con ogni elemento in un array. La nostra formula sarà simile a questa
| 1 | =SOMMA.PRODOTTO(--ESATTO("ABC123", A2:A5), B2:B5) |
La funzione EXACT controllerà quindi ogni elemento in A2:A5 per vedere se corrisponde a valore e caso. Questo produrrà un array simile a {0, 1, 0, 0}. Quando moltiplicato per B2:B5, l'array diventa {0, 2, 0, 0}. Dopo la sommatoria, otteniamo la nostra soluzione di 2.
SUMPRODUCT in Fogli Google
La funzione SUMPRODUCT funziona esattamente allo stesso modo in Fogli Google come in Excel:
SUMPRODUCT Esempi in VBA
Puoi anche utilizzare la funzione SUMPRODUCT in VBA. Tipo: application.worksheetfunction.sumproduct(array1,array2,array3)
Esecuzione delle seguenti istruzioni VBA
| 1 | Range("B10") = Application.WorksheetFunction.SumProduct(Range("A2:A7"), Range("B2:B7")) |
produrrà i seguenti risultati
Per gli argomenti della funzione (array1, ecc.), puoi inserirli direttamente nella funzione o definire le variabili da utilizzare al loro posto.
