Descărcați exemplul de registru de lucru
Acest tutorial arată cum să utilizați fișierul Funcția Excel SUMPRODUCT în Excel.
Prezentare generală a funcției SUMPRODUCT
Funcția SUMPRODUCT Multiplică matrici de numere și însumează matricea rezultată.
Pentru a utiliza funcția Foaie de lucru SUMPRODUCT Excel, selectați o celulă și tastați:
(Observați cum apar datele introduse de formulă)
Funcția SUMPRODUCT Sintaxă și intrări:
| 1 | = SUMPRODUCT (matrice1, matrice2, matrice3) |
matrice1 - Matrice de numere.
Ce este funcția SUMPRODUCT?
Funcția SUMPRODUCT este una dintre cele mai puternice funcții din Excel. Numele său, s-ar putea să vă facă să credeți că este destinat numai calculelor matematice de bază, dar poate fi folosit pentru mult mai mult.
Matrice
SUMPRODUCT necesită intrări de matrice.
Deci, mai întâi, ce înțelegem prin „matrice”? Un tablou este simplu un grup de articole (ex. Numere) dispuse într-o ordine specifică, la fel ca o gamă de celule. Deci, dacă ați avea numerele 1, 2, 3 în celulele A1: A3, Excel ar citi acest lucru ca matrice {1,2,3}. De fapt, puteți introduce {1,2,3} direct în formulele Excel și va recunoaște matricea.
Vom vorbi mai multe despre tablouri mai jos, dar mai întâi să vedem un exemplu simplu.
Matematică de bază
Să vedem un exemplu de bază al SUMPRODUCT, folosindu-l pentru a calcula vânzările totale.
Avem tabelul nostru de produse și dorim să calculăm vânzările totale. Veți fi tentați să adăugați doar o nouă coloană, să luați prețul cantității vândute * și apoi să rezumați noua coloană. În schimb, puteți folosi funcția SUMPRODUCT. Să parcurgem formula:
| 1 | = SUMPRODUCT (A2: A4, B2: B4) |
Funcția va încărca intervalele de numere în matrice, le va multiplica una împotriva celeilalte și apoi va însuma rezultatele:
| 1234 | = SUMPRODUCT ({100, 50, 10}, {6, 7, 5})= SUMPRODUCT ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUCT ({600, 350, 50}= 1000 |
Funcitonul SUMPRODUCT a reușit să multiplice toate numerele pentru noi ȘI să facă suma.
Medie ponderată
Un alt caz în care este util să utilizați SUMPRODUCT este atunci când trebuie să calculați o medie ponderată. Acest lucru se întâmplă cel mai adesea atunci când ne ocupăm de sarcinile școlare, deci să luăm în considerare următorul tabel.
Putem vedea cât valorează testele, testele și temele pentru nota generală, precum și media medie actuală pentru fiecare articol. Putem calcula nota generală atunci scriind
| 1 | = SUMPRODUCT (B2: B4, C2: C4) |
Funcția noastră înmulțește din nou fiecare element din matrice înainte de a însuma totalul. Acest lucru funcționează așa
| 123 | = SUMPRODUCT ({30%, 50%, 20%}, {73%, 90%, 95%})= SUMPRODUCT ({22%, 45%, 19%})= 86% |
Coloane multiple
Un alt loc pe care l-am putea folosi SUMPRODUCT este cu și mai multe coloane care trebuie să fie multiplicate una împotriva celeilalte. Să vedem un exemplu în care trebuie să calculăm volumul în bucăți de cherestea.
În loc să creăm o coloană de ajutor pentru a calcula vânzarea totală pentru fiecare rând, putem face acest lucru cu o singură formulă. Formula noastră va fi
| 1 | = SUMPRODUCT (B2: B5, C2: C5, D2: D5) |
Primele elemente ale fiecărei matrice se vor înmulți unul împotriva celuilalt (de exemplu, 4 * 2 * 1 = 8). Apoi, al doilea (4 * 2 * 2 = 16) și 3rd, etc. În general, aceasta va produce gama de produse care arată ca {8, 16, 16, 32). Apoi volumul total ar fi suma acelei matrice, 72.
Un criteriu
Bine, să adăugăm un alt strat de complexitate. Am văzut că SUMPRODUCT poate gestiona matrici de numere, dar ce se întâmplă dacă dorim să verificăm criteriile? Ei bine, puteți crea și tablouri pentru valori booleene (valorile booleene sunt valori ADEVĂRATE sau FALSE).
De exemplu, luați o matrice de bază {1, 2, 3}. Să creăm o matrice corespunzătoare care să indice dacă fiecare număr este mai mare de 1. Această matrice ar arăta ca {FALSE, TRUE, TRUE}.
Acest lucru este extrem de util în formule, deoarece putem converti cu ușurință TRUE / FALSE în 1 / 0. Să vedem un exemplu.
Folosind tabelul de mai jos, vrem să calculăm „Câte unități vândute au fost roșii?”
Putem face acest lucru, cu această formulă:
| 1 | = SUMPRODUCT (A2: A4, - (B2: B4 = "Roșu")) |
"Stai asa! Ce este simbolul dublu minus acolo? " tu spui. Amintiți-vă cum am spus că putem converti de la Adevărat / Fals în 1/0? Facem acest lucru forțând computerul să facă o operație matematică. În acest caz, spunem „luați valoarea negativă și apoi luați din nou negativul”. Scriind asta, matricea noastră se va schimba astfel:
| 123 | {Adevărat, adevărat, fals}{-1, -1, 0}{1, 1, 0} |
Deci, înapoi la formula SUMPRODUCT completă, se va încărca în matricile noastre și apoi se va înmulți, astfel
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= SUMPRODUCT ({100, 50, 0})= 150 |
Rețineți cum 3rd elementul a devenit 0, deoarece orice înmulțit cu 0 devine zero.
Criterii multiple
Putem încărca până la 255 de matrice în funcția noastră, deci putem încărca cu siguranță mai multe criterii. Să ne uităm la acest tabel mai mare în care am adăugat Luna vândută.
Dacă vrem să știm câte articole vândute au fost roșii și am fost în luna februarie, am putea scrie formula noastră ca
| 1 | = SUMPRODUCT (A2: A4, - (B2: B4 = "Roșu"), - (C2: C4 = "Feb")) |
Computerul ne-ar evalua apoi matricile și s-ar multiplica. Am prezentat deja modul în care matricele True / False se schimbă în 1/0, așa că voi ignora acest pas pentru moment.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCT ({0, 50, 0})= 50 |
Am avut un singur rând în exemplul nostru care corespundea tuturor criteriilor, dar cu date reale, este posibil să fi avut mai multe rânduri de care aveați nevoie adăugate împreună.
Criterii complexe
Bine, până în acest moment, este posibil să nu fiți impresionat, deoarece toate exemplele noastre ar fi putut fi realizate folosind alte funcții, cum ar fi SUMIF sau COUNTIF. Acum vom face ceva pentru aceste alte funcții nu pot do. Anterior, coloana noastră Luna avea numele reale ale lunilor. Ce se întâmplă dacă în schimb avea întâlniri?
Nu putem face un SUMIF acum, deoarece SUMIF nu poate face față criteriilor de care avem nevoie. SUMPRODUCT deși ne poate descurca manipulând matricea și făcând un test mai profund. Am manipulat deja matrici atunci când am tradus Adevăratul / Falsul în 1/0. Vom manipula această matrice cu funcția LUNĂ. Iată formula completă pe care o vom folosi
| 1 | = SUMPRODUCT (A2: A4, - (B2: B4 = "Roșu"), - (LUNĂ (C2: C4) = 2)) |
Să ne uităm la 3rd aranjează mai îndeaproape. În primul rând, formula noastră va extrage numărul lunii din fiecare dată în C2: C4. Acest lucru ne va oferi {1, 2, 2}. Apoi, verificăm dacă această valoare este egală cu 2. Acum matricea noastră arată ca {False, True, True}. Facem din nou minusul dublu și avem {0, 1, 1}. Ne-am întors într-un loc similar pe care l-am avut în exemplul 3, iar formula noastră ne va putea spune că au fost vândute 50 de unități în februarie, care erau roșii.
Dublu minus vs. multiplicare
Dacă ați mai văzut funcția SUMPRODUCT în uz, este posibil să fi văzut o notație ușor diferită. În loc să folosiți un minus dublu, puteți scrie
| 1 | = SUMPRODUCT (A2: A4 * (B2: B4 = "Roșu") * (LUNĂ (C2: C4) = 2)) |
Formula va funcționa în continuare în același mod, doar îi spunem manual computerului că dorim să înmulțim matricile. SUMPRODUCT avea să facă asta oricum, deci nu există nicio schimbare în modul în care funcționează matematica. Efectuarea operației matematice convertește adevăratul / falsul în 1/0 la fel. Deci, de ce diferența?
De cele mai multe ori, nu contează prea mult și se reduce la preferințele utilizatorului. Există, însă, cel puțin un caz în care este necesară multiplicarea.
Când utilizați SUMPRODUCT, computerul se așteaptă ca toate argumentele (array1, array2 etc.) să aibă aceeași dimensiune. Aceasta înseamnă că au același număr de rânduri sau coloane. Cu toate acestea, puteți face ceea ce se cunoaște sub numele de calcul matricial bidimensional cu SUMPRODUCT pe care îl vom vedea în exemplul următor. Când faceți acest lucru, matricile au dimensiuni diferite, așa că trebuie să ocolim această verificare „toate de aceeași dimensiune”.
Două dimensiuni
Toate exemplele anterioare aveau matricile noastre în aceeași direcție. SUMPRODUCT poate gestiona lucrurile care merg în două direcții, așa cum vom vedea în tabelul următor.
Iată tabelul nostru de unități vândute, dar datele sunt reamenajate în cazul în care categoriile merg în partea de sus. Dacă vrem să aflăm câte articole au fost roșii și din categoria A, putem scrie
| 1 | = SUMPRODUCT ((A2: A4 = "Roșu") * (B1: C1 = "A") * B2: C4) |
Ce se intampla aici?? Se pare că ne vom înmulți în două direcții diferite. Vizualizarea acestui lucru este mai greu de făcut doar cu o propoziție scrisă, așa că avem câteva imagini care să ne ajute. În primul rând, criteriile noastre de rând (este roșu?) Se va înmulți pe fiecare rând din matrice.
| 1 | = SUMPRODUCT ((A2: A4 = "RED") * B2: C4) |
Apoi, criteriile coloanei (este categoria A?) Se va înmulți în jos fiecare coloană
| 1 | = SUMPRODUCT ((A2: A4 = "Roșu") * (B1: C1 = "A") * B2: C4) |
După ce ambele criterii și-au făcut treaba, singurele non-zerouri rămase sunt 5 și 10. SUMPRODUCT ne va da apoi marele total de 15 ca răspuns.
Vă amintiți cum am vorbit despre matricile care trebuie să aibă aceeași dimensiune dacă nu faceți două dimensiuni? Acest lucru a fost parțial corect. Se uită din nou la matricile pe care le-am folosit în formula noastră. The înălţime din două dintre matricele noastre este același și lăţime din două dintre matricile noastre sunt aceleași. Deci, trebuie totuși să vă asigurați că lucrurile se vor alinia corect, dar o puteți face în diferite dimensiuni.
Două dimensiuni și complexe
De multe ori ni se prezintă date care nu sunt în cel mai bun aspect adecvat pentru formulele noastre. Am putea încerca să îl rearanjăm manual sau putem fi mai deștepți cu formulele noastre. Să luăm în considerare următorul tabel.
Aici avem datele pentru articolele și vânzările noastre amestecate pentru fiecare lună. Cum ne-am gândi să aflăm câte articole a vândut Bob pe tot parcursul anului?
Pentru a face acest lucru, vom folosi două funcții suplimentare: SEARCH și ISNUMBER. Funcția CĂUTARE ne va permite să căutăm cuvântul nostru cheie „articole” în celulele antetului. Ieșirea din această funcție va fi realizată fie printr-un număr, fie printr-o eroare (dacă cuvântul cheie nu este găsit). Apoi, vom folosi ISNUMBER pentru a efectua conversii acea ieșire în valorile noastre booleene. Formula noastră va arăta ca mai jos.
Ar trebui să fii familiarizat cu prima matrice până acum. Va crea o ieșire precum {0, 1, 0, 1}. Următoarea matrice de criterii despre care tocmai am vorbit. Va crea un număr pentru toate celulele cu „Elemente” în ele și o eroare pentru celelalte {5, # N / A !, 5, # N / A!}. Numărul ISNUMBER îl convertește apoi în boolean {Adevărat, fals, adevărat, fals}. Atunci când ne înmulțim, vom păstra doar valorile din prima și a treia coloană. După ce toate matricile se înmulțesc unele cu altele, singurele numere diferite de zero pe care le vom avea sunt cele evidențiate aici:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob") * (ISNUMBER (CĂUTARE ("Elemente", B1: E1)) * B2: E5)) |
SUMPRODUCT le va adăuga apoi pe toate și vom obține rezultatul final de 29.
SUMPRODUCT Or
Există multe situații în care am dori să putem rezuma valori dacă coloana noastră de criterii are o valoare SAU o altă valoare. Puteți realiza acest lucru în SUMPRODUCT adăugând două matrici de criterii unul împotriva celuilalt.
În acest exemplu, dorim să adăugăm unități vândute atât pentru roșu, cât și pentru albastru.
Formula noastră va arăta astfel
| 1 | = SUMPRODUCT (A2: A7, (B2: B7 = "Roșu") + (B2: B7 = "Albastru")) |
Să vedem matricea de criterii roșii. Acesta va produce o matrice care arată astfel: {1, 1, 0, 0, 0, 0}. Matricea de criterii albastre va arăta ca {0, 0, 1, 0, 1, 0}. Când le adăugați împreună, noua matrice va arăta ca {1, 1, 1, 0, 1, 0}. Putem vedea cum cele două matrice s-au amestecat într-o singură matrice de criterii. Funcția va multiplica acest lucru cu prima noastră matrice și vom primi {100, 50, 10, 0, 75, 0}. Observați că valorile pentru Green au fost reduse la zero. Ultimul pas al SUMPRODUCT este adăugarea tuturor numerelor împreună pentru a ajunge la soluția noastră de 235.
Un cuvânt de precauție aici. Aveți grijă când matricile de criterii nu se exclud reciproc. În exemplul nostru, valorile din coloana B ar putea fi roșu sau albastru, dar știam că nu ar putea fi niciodată ambele. Luați în considerare dacă am scris această formulă:
| 1 | = SUMPRODUCT (A2: A7, (A2: A7> = 50) + (B2: B7 = "Albastru")) |
Intenția noastră este să găsim articole albastre care au fost vândute sau au fost într-o cantitate mai mare de 50. Cu toate acestea, aceste condiții nu sunt exclusive, întrucât un singur rând ar putea fi peste 50 în coloana A și fii albastru. Aceasta ar avea ca rezultat ca primul tablou de criterii să arate ca {1, 1, 0, 1, 1, 0}, al doilea tablou de criterii fiind {0, 0, 1, 0, 1, 0}. Adăugarea acestora a generat {1, 1, 1, 1, 2, 0}. Vedeți cum avem un 2 acolo acum? Dacă ar fi lăsat singur, SUMPRODUCT ar ajunge să dubleze valoarea din acel rând, schimbând 75 la 150 și am obține rezultatul greșit. Pentru a corecta acest lucru, plasăm o verificare a criteriilor externe asupra matricei noastre, astfel:
| 1 | = SUMPRODUCT (A2: A7, - ((A2: A7> = 50) + (B2: B7 = "Albastru")> 0)) |
Acum, după ce cele două matrice de criterii interioare au fost adăugate, vom verifica dacă rezultatul este mai mare de 0. Acest lucru scapă de cele 2 pe care le-am avut înainte și, în schimb, vom avea o matrice precum {1, 1, 1 , 1, 1, 0} care va produce rezultatul corect.
SUMPRODUCT Exact
Majoritatea funcțiilor din Excel nu sunt sensibile la majuscule, dar uneori trebuie să putem efectua o căutare având în vedere sensibilitatea la majuscule. Când rezultatul dorit este numeric, putem realiza acest lucru folosind EXACT în funcția SUMPRODUCT. Luați în considerare următorul tabel:
Vrem să găsim scorul pentru articolul „ABC123”. În mod normal, funcția EXACT va compara două elemente și va returna o ieșire booleană care să indice dacă cele două elemente sunt exact la fel. Cu toate acestea, întrucât ne aflăm într-un SUMPRODUCT, computerul nostru va ști că avem de-a face cu tablouri și va putea compara un articol cu fiecare articol dintr-o matrice. Formula noastră va arăta astfel
| 1 | = SUMPRODUCT (- EXACT ("ABC123", A2: A5), B2: B5) |
Funcția EXACT va verifica apoi fiecare element din A2: A5 pentru a vedea dacă se potrivește cu valoarea și cu majuscule. Aceasta va produce o matrice care arată ca {0, 1, 0, 0}. Când este înmulțit cu B2: B5, matricea devine {0, 2, 0, 0}. După suma finală, obținem soluția noastră de 2.
SUMPRODUCT în Foi de calcul Google
Funcția SUMPRODUCT funcționează exact la fel în Foi de calcul Google ca în Excel:
SUMPRODUCT Exemple în VBA
De asemenea, puteți utiliza funcția SUMPRODUCT în VBA. Tip: application.worksheetfunction.sumproduct (array1, array2, array3)
Executarea următoarelor instrucțiuni VBA
| 1 | Range ("B10") = Application.WorksheetFunction.SumProduct (Range ("A2: A7"), Range ("B2: B7")) |
va produce următoarele rezultate
Pentru argumentele funcției (matrice1 etc.), le puteți introduce direct în funcție sau puteți defini variabile de utilizat.
