Preuzmite primjer radne knjige
Ovaj vodič prikazuje kako se koristi Excel SUMPRODUCT funkcija u Excelu.
PREGLED Pregled funkcija
Funkcija SUMPRODUCT množi nizove brojeva i zbraja rezultirajući niz.
Da biste koristili funkciju SUMPRODUCT Excel radnog lista, odaberite ćeliju i upišite:
(Obratite pažnju na to kako se pojavljuju unosi formule)
Funkcija SUMPRODUCT Sintaksa i ulazi:
| 1 | = SUMPRODUCT (niz1, niz2, niz3) |
niz1 - Nizovi brojeva.
Što je funkcija SUMPRODUCT?
Funkcija SUMPRODUCT jedna je od moćnijih funkcija u Excelu. To je ime, moglo bi vas navesti da vjerujete da je namijenjeno samo osnovnim matematičkim izračunima, ali može se koristiti za mnogo više.
Nizovi
SUMPRODUCT zahtijeva unose niza.
Dakle, prvo, što podrazumijevamo pod "nizom"? Niz je jednostavna skupina stavki (npr. Brojevi) raspoređenih u određenom redoslijedu, baš kao i niz ćelija. Dakle, da imate brojeve 1, 2, 3 u ćelijama A1: A3, Excel bi ovo pročitao kao niz {1,2,3}. Zapravo, možete unijeti {1,2,3} izravno u Excel formule i ona će prepoznati niz.
U nastavku ćemo govoriti više o nizovima, ali prvo pogledajmo jednostavan primjer.
Osnovna matematika
Pogledajmo osnovni primjer SUMPRODUCT -a, koristeći ga za izračun ukupne prodaje.
Imamo tablicu proizvoda i želimo izračunati ukupnu prodaju. Doći ćete u iskušenje da samo dodate novi stupac, uzmete prodatu količinu * cijenu, a zatim zbrojite novi stupac. Umjesto toga, možete jednostavno upotrijebiti funkciju SUMPRODUCT. Prođimo kroz formulu:
| 1 | = PODIZVOD (A2: A4, B2: B4) |
Funkcija će učitati raspone brojeva u nizove, pomnožiti ih jedan s drugim, a zatim zbrojiti rezultate:
| 1234 | = SUMPRODUCT ({100, 50, 10}, {6, 7, 5})= SUMPRODUCT ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUCT ({600, 350, 50}= 1000 |
Funkcija SUMPRODUCT nam je uspjela pomnožiti sve brojeve i izvršiti zbrajanje.
Prosječne težine
Drugi slučaj u kojem je korisno koristiti SUMPRODUCT je kada trebate izračunati ponderirani prosjek. To se najčešće događa pri rješavanju školskih zadaća, pa razmotrimo sljedeću tablicu.
Možemo vidjeti koliko kvizovi, testovi i domaće zadaće vrijede za ukupnu ocjenu, kao i koliki je trenutni prosjek za svaku pojedinu stavku. Ukupnu ocjenu tada možemo izračunati pisanjem
| 1 | = PODIZVOD (B2: B4, C2: C4) |
Naša funkcija ponovno množi svaku stavku u nizovima prije zbrajanja zbroja. Ovo funkcionira tako
| 123 | = PODIZVOD ({30%, 50%, 20%}, {73%, 90%, 95%})= PODIZVOD ({22%, 45%, 19%})= 86% |
Više stupaca
Još jedno mjesto koje bismo mogli koristiti je SUMPRODUCT s još više stupaca koje je potrebno pomnožiti jedan s drugim. Pogledajmo primjer gdje moramo izračunati volumen u komadima drvne građe.
Umjesto stvaranja pomoćnog stupca za izračun ukupne prodaje za svaki redak, to možemo učiniti s jednom formulom. Naša formula će biti
| 1 | = PODIZVOD (B2: B5, C2: C5, D2: D5) |
Prve stavke svakog niza množit će se jedna s drugom (npr. 4 * 2 * 1 = 8). Zatim, 2. (4 * 2 * 2 = 16) i 3rditd. Sve u svemu, ovo će proizvesti niz proizvoda koji izgledaju kao {8, 16, 16, 32). Tada bi ukupni volumen bio zbroj tog niza, 72.
Jedan kriterij
U redu, dodajmo još jedan sloj složenosti. Vidjeli smo da SUMPRODUCT može rukovati nizovima brojeva, ali što ako želimo provjeriti kriterije? Pa, također možete stvoriti nizove za Booleove vrijednosti (Boolean Values su vrijednosti koje su TRUE ili FALSE).
Na primjer, uzmite osnovni niz {1, 2, 3}. Izradimo odgovarajući niz koji označava je li svaki broj veći od 1. Ovaj niz bi izgledao kao {FALSE, TRUE, TRUE}.
To je iznimno korisno u formulama jer možemo lako pretvoriti TRUE / FALSE u 1/0. Pogledajmo primjer.
Pomoću donje tablice želimo izračunati "Koliko je prodanih jedinica bilo crveno?"
To možemo učiniti pomoću ove formule:
| 1 | = PODIZVOD (A2: A4, -(B2: B4 = "Crveno")) |
"Drži se! Što ima simbol dvostrukog minusa? " Ti kažeš. Sjećate li se kako sam rekao da možemo pretvoriti iz True/False u 1/0? To činimo prisiljavajući računalo na matematičku operaciju. U ovom slučaju kažemo "uzmi negativnu vrijednost, a zatim opet negativnu". Kad to ispišemo, naš će se niz promijeniti ovako:
| 123 | {Tačno, Istina, Netačno}{-1, -1, 0}{1, 1, 0} |
Dakle, natrag na potpunu formulu SUMPRODUCT, učitat će se u naše nizove, a zatim pomnožiti, ovako
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= SUMPRODUCT ({100, 50, 0})= 150 |
Zapazite kako je 3rd stavka postala 0, jer sve pomnoženo s 0 postaje nula.
Više kriterija
U našu funkciju možemo učitati do 255 polja, pa zasigurno možemo učitati više kriterija. Pogledajmo ovaj veći stol gdje smo dodali Mjesec prodaje.
Ako želimo znati koliko je prodanih artikala bilo crveno i bili u mjesecu veljači, mogli bismo formulu napisati poput
| 1 | = PODIZVOD (A2: A4, -(B2: B4 = "Crveno"), -(C2: C4 = "Feb")) |
Računalo bi tada procijenilo naše nizove i pomnožilo se. Već smo govorili o tome kako se True/False nizovi mijenjaju u 1/0, pa ću zasad preskočiti taj korak.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCT ({0, 50, 0})= 50 |
U našem smo primjeru imali samo jedan redak koji je odgovarao svim kriterijima, ali s stvarnim podacima možda ste imali dodane više redaka.
Složeni kriteriji
U redu, do ovog trenutka možda nećete biti impresionirani jer su svi naši primjeri mogli biti izvedeni pomoću drugih funkcija poput SUMIF ili COUNTIF. Sada ćemo učiniti nešto te druge funkcije ne može čini. Ranije je naša kolona Mjesec imala stvarne nazive mjeseci. Što ako je umjesto toga imala datume?
Ne možemo sada napraviti SUMIF, jer SUMIF ne može podnijeti kriterije koji su nam potrebni. SUMPRODUCT nas ipak može nositi s manipuliranjem nizom i dubljim testiranjem. Već smo manipulirali nizovima kad smo preveli Točno/Lažno u 1/0. Ovim nizom ćemo manipulirati funkcijom MONTH. Evo potpune formule koju ćemo koristiti
| 1 | = PODIZVOD (A2: A4, -(B2: B4 = "Crveno"), -(MJESEC (C2: C4) = 2)) |
Pogledajmo 3rd rasporediti pobliže. Prvo, naša formula će izdvojiti broj mjeseca iz svakog datuma u C2: C4. To će nam dati {1, 2, 2}. Zatim provjeravamo je li ta vrijednost jednaka 2. Sada naš niz izgleda kao {False, True, True}. Ponovno radimo dvostruki minus i imamo {0, 1, 1}. Sada smo se vratili na slično mjesto koje smo imali u primjeru 3, a naša formula će nam moći reći da je u veljači prodano 50 jedinica koje su bile crvene.
Dvostruki minus vs množenje
Ako ste već vidjeli funkciju SUMPRODUCT u upotrebi, mogli ste vidjeti malo drugačiji zapis. Umjesto da koristite dvostruki minus, možete pisati
| 1 | = PODIZVOD (A2: A4*(B2: B4 = "Crveno")*(MJESEC (C2: C4) = 2)) |
Formula će i dalje raditi na isti način, samo ručno govorimo računalu da želimo pomnožiti nizove. SUMPRODUCT je to ionako namjeravao učiniti, pa nema promjene u načinu na koji matematika funkcionira. Izvođenje matematičke operacije pretvara naše True/False u 1/0 iste. Dakle, čemu razlika?
Većinom to nije previše važno, a svodi se na preferencije korisnika. Postoji barem jedan slučaj gdje je potrebno množenje.
Kada koristite SUMPRODUCT, računalo očekuje da svi argumenti (niz1, niz2 itd.) Budu iste veličine. To znači da imaju isti broj redaka ili stupaca. Međutim, možete napraviti ono što je poznato kao dvodimenzionalni izračun niza s SUMPRODUCT -om koji ćemo vidjeti u sljedećem primjeru. Kad to učinite, nizovi su različitih veličina, pa moramo zaobići tu provjeru "sve iste veličine".
Dvije dimenzije
Svi prethodni primjeri imali su naše nizove u istom smjeru. SUMPRODUCT se može nositi sa stvarima koje se odvijaju u dva smjera, kao što ćemo vidjeti u sljedećoj tablici.
Evo naše tablice prodanih jedinica, ali podaci se preuređuju tamo gdje kategorije idu na vrhu. Ako želimo saznati koliko je artikala bilo crveno i u kategoriji A, možemo napisati
| 1 | = SUMPRODUCT ((A2: A4 = "Crveno")*(B1: C1 = "A")*B2: C4) |
Što se ovdje događa?? Ispostavilo se da ćemo se množiti u dva različita smjera. To je teže zamisliti samo napisanom rečenicom, pa imamo nekoliko slika koje će nam pomoći. Prvo će se naši kriteriji retka (je li crveni?) Pomnožiti u svakom retku u nizu.
| 1 | = SUMPRODUCT ((A2: A4 = "CRVENI")*B2: C4) |
Zatim će se kriteriji stupca (je li to kategorija A?) Pomnožiti svaki stupac
| 1 | = SUMPRODUCT ((A2: A4 = "Crveno")*(B1: C1 = "A")*B2: C4) |
Nakon što oba kriterija odrade svoj posao, preostale su samo nule 5 i 10. SUMPRODUCT će nam kao odgovor dati ukupno 15.
Sjećate li se kako smo razgovarali o nizovima koji moraju biti iste veličine, osim ako radite dvije dimenzije? To je djelomično bilo točno. Ponovno gleda nizove koje smo koristili u formuli. The visina dva naša niza su isti, a širina dva naša niza su ista. Dakle, još uvijek morate biti sigurni da će se stvari ispravno postaviti, ali to možete učiniti u različitim dimenzijama.
Dvije dimenzije i kompleks
Često nam se prezentiraju podaci koji nisu u najboljem izgledu prikladnom za naše formule. Mogli bismo ga pokušati ručno preurediti ili možemo biti pametniji s formulama. Razmotrimo sljedeću tablicu.
Ovdje imamo podatke za naše artikle i prodaju pomiješane za svaki mjesec. Kako bismo saznali koliko je stvari Bob prodao tijekom cijele godine?
Da bismo to učinili, upotrijebit ćemo dvije dodatne funkcije: SEARCH i ISNUMBER. Funkcija SEARCH omogućit će nam da tražimo ključnu riječ „stavke“ unutar ćelija zaglavlja. Izlaz iz ove funkcije ide ili brojem ili greškom (ako ključna riječ nije pronađena). Zatim ćemo koristiti ISNUMBER za pretvaranje da izlaz u naše Booleove vrijednosti. Naša formula će izgledati kao u nastavku.
Već bi trebao biti prilično upoznat s prvim nizom. Stvorit će izlaz poput {0, 1, 0, 1}. Sljedeći niz kriterija o kojem smo upravo govorili. Stvorit će se broj za sve ćelije s "stavkama" u njima, a pogreška za ostale {5, #N/A !, 5, #N/A!}. ISNUMBER zatim pretvara ovo u logičko {True, False, True, False}. Zatim, kada pomnožimo, zadržat će se samo vrijednosti iz prvog i trećeg stupca. Nakon što se svi nizovi pomnože jedan s drugim, jedini brojevi koji nisu nula bit će oni koji su ovdje istaknuti:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob")*(ISNUMBER (PRETRAGA ("Stavke", B1: E1))*B2: E5)) |
SUMPRODUCT će sve to zbrojiti i dobivamo konačni rezultat od 29.
POVRATAK Or
Pojavljuju se mnoge situacije u kojima bismo htjeli sažeti vrijednosti ako naš stupac kriterija ima jednu vrijednost ILI drugu vrijednost. To možete postići u SUMPRODUCT -u dodavanjem dva polja kriterija jedan protiv drugog.
U ovom primjeru želimo zbrajati jedinice prodane i za crvenu i za plavu.
Naša formula će izgledati ovako
| 1 | = PODIZVOD (A2: A7, (B2: B7 = "Crveno")+(B2: B7 = "Plavo")) |
Pogledajmo red kriterija Red. Proizvest će niz koji izgleda ovako: {1, 1, 0, 0, 0, 0}. Niz plavih kriterija izgledat će kao {0, 0, 1, 0, 1, 0}. Kad ih zbrojite, novi niz izgledat će kao {1, 1, 1, 0, 1, 0}. Možemo vidjeti kako su se dva niza spojila u jedan niz kriterija. Funkcija će tada pomnožiti to s našim prvim nizom i dobit ćemo {100, 50, 10, 0, 75, 0}. Uočite da su vrijednosti za Green poništene. Posljednji korak SUMPRODUCT -a je zbrajanje svih brojeva kako bismo došli do rješenja 235.
Ovdje jedna riječ opreza. Budite oprezni kada se nizovi kriterija međusobno ne isključuju. U našem primjeru vrijednosti u stupcu B mogle bi biti ili crvene ili plave, ali znali smo da nikada ne mogu biti oboje. Razmislite da li smo napisali ovu formulu:
| 1 | = PODIZVOD (A2: A7, (A2: A7> = 50)+(B2: B7 = "Plavo")) |
Namjera nam je pronaći plave predmete koji su prodani ili su bili u količini većoj od 50. Međutim, ti uvjeti nisu isključivi jer jedan redak može imati preko 50 u stupcu A i budi Plava. To bi dovelo do toga da prvi niz kriterija izgleda kao {1, 1, 0, 1, 1, 0}, a drugi niz kriterija je {0, 0, 1, 0, 1, 0}. Njihovim zbrajanjem nastalo je {1, 1, 1, 1, 2, 0}. Vidite li kako sada imamo 2? Ako se ostavi na miru, SUMPRODUCT bi na kraju udvostručio vrijednost u tom retku, promijenivši 75 u 150, a dobili bismo pogrešan rezultat. Da bismo to ispravili, postavljamo vanjsku provjeru kriterija u naš niz, na sljedeći način:
| 1 | = PODIZVOD (A2: A7, -((A2: A7> = 50)+(B2: B7 = "Plavo")> 0)) |
Sada, nakon što se dva unutarnja polja kriterija zbroje, provjerit ćemo je li rezultat veći od 0. Time ćemo se riješiti 2 koja smo imali prije, a umjesto toga imat ćemo niz poput {1, 1, 1 , 1, 1, 0} koji će dati točan rezultat.
PODIZVOD Točno
Većina funkcija u Excelu ne razlikuje velika i mala slova, ali ponekad moramo biti u mogućnosti pretražiti imajući u vidu osjetljivost na velika i mala slova. Kad je željeni rezultat numerički, to možemo postići uporabom EXACT unutar funkcije SUMPRODUCT. Razmotrite sljedeću tablicu:
Želimo pronaći rezultat za stavku “ABC123”. Normalno, funkcija EXACT će usporediti dvije stavke i vratiti Booleov izlaz koji navodi jesu li dvije stavke točno isto. Međutim, budući da smo unutar SUMPRODUCT -a, naše će računalo znati da imamo posla s nizovima i moći će usporediti jednu stavku sa svakom stavkom u nizu. Naša formula će izgledati ovako
| 1 | = SUMPRODUCT (-TOČNO ("ABC123", A2: A5), B2: B5) |
Funkcija EXACT tada će provjeriti svaku stavku u A2: A5 kako bi provjerila odgovara li vrijednosti i veličini slova. Time će se dobiti niz koji izgleda kao {0, 1, 0, 0}. Kada se pomnoži s B2: B5, niz postaje {0, 2, 0, 0}. Nakon konačnog zbrajanja dobivamo rješenje 2.
SUMPRODUCT u Google tablicama
Funkcija SUMPRODUCT radi potpuno isto u Google tablicama kao i u Excelu:
SAŽETAK Primjeri u VBA
Također možete koristiti funkciju SUMPRODUCT u VBA. Vrsta: application.worksheetfunction.sumproduct (niz1, niz2, niz3)
Izvršavanje sljedećih VBA izraza
| 1 | Raspon ("B10") = Application.WorksheetFunction.SumProduct (Raspon ("A2: A7"), Raspon ("B2: B7")) |
će proizvesti sljedeće rezultate
Za argumente funkcije (niz1 itd.) Možete ih unijeti izravno u funkciju ili definirati varijable koje ćete umjesto toga koristiti.
