Prenesite primer delovnega zvezka
Ta vadnica prikazuje, kako uporabljati Excel SUMPRODUCT funkcija v Excelu.
PODROČJE Pregled funkcije
Funkcija SUMPRODUCT pomnoži matrike števil in sešteje nastalo matriko.
Če želite uporabiti funkcijo delovnega lista Excel SUMPRODUCT, izberite celico in vnesite:
(Upoštevajte, kako so prikazani vnosi formule)
Funkcija SUMPRODUCT Sintaksa in vhodi:
| 1 | = SUMPRODUCT (niz1, niz2, niz3) |
matrika1 - Niz števil.
Kaj je funkcija SUMPRODUCT?
Funkcija SUMPRODUCT je ena močnejših funkcij v Excelu. To ime bi vas lahko prepričalo, da je namenjeno le osnovnim matematičnim izračunom, vendar ga lahko uporabite za veliko več.
Nizovi
SUMPRODUCT zahteva vnose nizov.
Torej, najprej, kaj mislimo z "nizom"? Niz je preprosta skupina elementov (npr. Številk), razporejenih v določenem vrstnem redu, tako kot obseg celic. Če bi torej imeli številke 1, 2, 3 v celicah A1: A3, bi Excel to prebral kot matriko {1,2,3}. Pravzaprav lahko vnesete {1,2,3} neposredno v formule Excel in ta bo prepoznal matriko.
Spodaj bomo govorili več o nizih, najprej pa poglejmo preprost primer.
Osnovna matematika
Poglejmo osnovni primer izdelka SUMPRODUCT, ki ga uporablja za izračun skupne prodaje.
Imamo svojo tabelo izdelkov in želimo izračunati skupno prodajo. Mika vas, da preprosto dodate nov stolpec, vzamete prodano količino * ceno in nato povzamete nov stolpec. Namesto tega lahko preprosto uporabite funkcijo SUMPRODUCT. Pojdimo skozi formulo:
| 1 | = PODROČJE (A2: A4, B2: B4) |
Funkcija bo naložila obsege števil v matrike, jih množila drug proti drugemu in nato povzela rezultate:
| 1234 | = PODROČJE ({100, 50, 10}, {6, 7, 5})= PODROČJE ({100 * 6, 50 * 7, 10 * 5})= PODROČJE ({600, 350, 50}= 1000 |
Funkcija SUMPRODUCT nam je uspela pomnožiti vse številke in opraviti seštevanje.
Povprečna teža
Drug primer, ko je koristno uporabiti SUMPRODUCT, je, ko morate izračunati tehtano povprečje. To se najpogosteje pojavlja pri šolskih opravilih, zato razmislimo o naslednji tabeli.
Vidimo lahko, koliko so kvizi, testi in domače naloge vredni skupne ocene, pa tudi, kakšno je trenutno povprečje za vsako posamezno postavko. Skupno oceno lahko nato izračunamo s pisanjem
| 1 | = PODROČJE (B2: B4, C2: C4) |
Naša funkcija znova pomnoži vsak element v matrikah, preden sešteje celoto. To deluje tako
| 123 | = PODROČJE ({30%, 50%, 20%}, {73%, 90%, 95%})= PODROČJE ({22%, 45%, 19%})= 86% |
Več stolpcev
Drug kraj, ki bi ga lahko uporabili, je SUMPRODUCT s še več stolpci, ki jih je treba pomnožiti drug proti drugemu. Poglejmo primer, kjer moramo izračunati prostornino v kosih lesa.
Namesto, da bi ustvarili pomožni stolpec za izračun celotne prodaje za vsako vrstico, lahko to storimo z eno samo formulo. Naša formula bo
| 1 | = PODROČJE (B2: B5, C2: C5, D2: D5) |
Prvi elementi vsakega polja se množijo drug proti drugemu (npr. 4 * 2 * 1 = 8). Nato 2. (4 * 2 * 2 = 16) in 3rditd. Na splošno bo to ustvarilo paleto izdelkov, ki so videti kot {8, 16, 16, 32). Potem bi bila skupna prostornina vsota tega niza, 72.
Eno merilo
V redu, dodajmo še eno plast kompleksnosti. Videli smo, da lahko SUMPRODUCT obravnava niz številk, kaj pa, če želimo preveriti merila? No, lahko ustvarite tudi matrike za Boolean vrednosti (Boolean Values so vrednosti, ki so TRUE ali FALSE).
Vzemite na primer osnovni niz {1, 2, 3}. Ustvarimo ustrezno matriko, ki označuje, ali je vsako število večje od 1. Ta matrika bi izgledala kot {FALSE, TRUE, TRUE}.
To je pri formulah zelo koristno, saj lahko TRUE / FALSE enostavno pretvorimo v 1/0. Poglejmo primer.
S spodnjo tabelo želimo izračunati "Koliko prodanih enot je bilo rdečih?"
To lahko storimo s to formulo:
| 1 | = PODROČJE (A2: A4, -(B2: B4 = "rdeče")) |
"Počakaj! Kaj je tam s simbolom dvojnega minus? " Ti rečeš. Se spomnite, kako sem rekel, da lahko pretvorimo iz True/False v 1/0? To naredimo tako, da računalnik prisilimo v matematično operacijo. V tem primeru govorimo »vzemite negativno vrednost in nato znova vzemite negativno«. Če to izpišemo, se bo naša matrika spremenila tako:
| 123 | {True, True, False}{-1, -1, 0}{1, 1, 0} |
Torej, nazaj k celotni formuli SUMPRODUCT, se bo naložila v naše matrike in nato pomnožila, takole
| 123 | = PODROČJE ({100, 50, 10}, {1, 1, 0})= PODROČJE ({100, 50, 0})= 150 |
Upoštevajte, kako 3rd postavka je postala 0, ker vse, pomnoženo z 0, postane nič.
Več meril
V svojo funkcijo lahko naložimo do 255 nizov, zato lahko zagotovo naložimo več kriterijev. Poglejmo to večjo tabelo, kamor smo dodali Mesec prodanih.
Če želimo vedeti, koliko prodanih artiklov je bilo rdečih in februarja, bi lahko formulo zapisali tako
| 1 | = PODROČJE (A2: A4, -(B2: B4 = "rdeča"), -(C2: C4 = "februar")) |
Računalnik bi nato ocenil naše matrike in se pomnožil. Že smo obravnavali, kako se polja True/False spremenijo v 1/0, zato bom ta korak za zdaj preskočil.
| 123 | = PODROČJE ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= PODROČJE ({0, 50, 0})= 50 |
V našem primeru smo imeli samo eno vrstico, ki je ustrezala vsem kriterijem, toda z resničnimi podatki ste morda imeli skupaj dodane več vrstic.
Kompleksna merila
V redu, do te točke morda ne boste navdušeni, ker bi lahko vse naše primere naredili z drugimi funkcijami, kot sta SUMIF ali COUNTIF. Zdaj bomo naredili nekaj teh drugih funkcij ne more naredi. Prej je naš stolpec Mesec imel dejanska imena mesecev. Kaj pa, če bi namesto tega imeli datume?
SUMIF zdaj ne moremo narediti, ker SUMIF ne more obravnavati potrebnih meril. Kljub temu lahko SUMPRODUCT z nami manipulira z matriko in opravi globlji preizkus. Z matrikami smo že manipulirali, ko smo prevedli True/False v 1/0. S to matriko bomo manipulirali s funkcijo MONTH. Tu je celotna formula, ki jo bomo uporabili
| 1 | = PODROČJE (A2: A4, -(B2: B4 = "rdeča"), -(MESEC (C2: C4) = 2)) |
Poglejmo si 3rd postavimo natančneje. Najprej bo naša formula izvlekla številko meseca iz vsakega datuma v C2: C4. To nam bo dalo {1, 2, 2}. Nato preverimo, ali je ta vrednost enaka 2. Zdaj je naša matrika videti kot {False, True, True}. Spet naredimo dvojni minus in imamo {0, 1, 1}. Zdaj smo spet na podobnem mestu, kot smo ga imeli v primeru 3, in naša formula nam bo lahko povedala, da je bilo februarja prodanih 50 enot, ki so bile rdeče barve.
Dvojni minus v primerjavi z množenjem
Če ste že videli funkcijo SUMPRODUCT v uporabi, ste morda videli nekoliko drugačen zapis. Namesto da uporabite dvojni minus, lahko pišete
| 1 | = PODROČJE (A2: A4*(B2: B4 = "rdeča")*(MESEC (C2: C4) = 2)) |
Formula bo še vedno delovala na enak način, računalniku samo ročno povemo, da želimo množiti matrike. SUMPRODUCT je to vseeno naredil, zato ni nobene spremembe v tem, kako matematika deluje. Izvajanje matematične operacije pretvori naše True/False v 1/0 enako. Torej, zakaj razlika?
V večini primerov to ni preveč pomembno in gre za želje uporabnikov. Obstaja vsaj en primer, kjer je potrebno pomnožiti.
Ko uporabljate SUMPRODUCT, računalnik pričakuje, da bodo vsi argumenti (niz1, niz2 itd.) Enaki. To pomeni, da imajo enako število vrstic ali stolpcev. Lahko pa naredite, kar je znano, kot izračun dvodimenzionalne matrike s SUMPRODUCT, ki ga bomo videli v naslednjem primeru. Ko to storite, so matrike različnih velikosti, zato moramo to oznako »vse enake velikosti« zaobiti.
Dve dimenziji
Vsi prejšnji primeri so imeli naše matrike v isti smeri. SUMPRODUCT lahko obravnava stvari v dveh smereh, kot bomo videli v naslednji tabeli.
Tukaj je naša tabela prodanih enot, vendar so podatki preurejeni tam, kjer se kategorije uvrščajo na vrh. Če želimo izvedeti, koliko predmetov je bilo rdečih in v kategoriji A, lahko zapišemo
| 1 | = POVEZAVA ((A2: A4 = "rdeča")*(B1: C1 = "A")*B2: C4) |
Kaj se dogaja tukaj ?? Izkazalo se je, da se bomo množili v dveh različnih smereh. Vizualizacijo tega je težje narediti le s pisnim stavkom, zato imamo v pomoč nekaj slik. Prvič, naša merila vrstice (je rdeča?) Se bodo pomnožila v vsaki vrstici v matriki.
| 1 | = POVEZAVA ((A2: A4 = "RDEČA")*B2: C4) |
Nato se bodo merila za stolpec (ali gre za kategorijo A?) Pomnožila za vsak stolpec
| 1 | = POVEZAVA ((A2: A4 = "rdeča")*(B1: C1 = "A")*B2: C4) |
Ko oba merila opravita svoje delo, ostaneta samo nič in nič še 5 in 10. V nadaljevanju nam bo VODIK dal 15 skupaj.
Se spomnite, kako smo govorili o tem, da morajo biti matrike enake velikosti, če ne delate dveh dimenzij? To je bilo delno pravilno. Ponovno pogleda matrike, ki smo jih uporabili v formuli. The višina dveh naših nizov sta enaka in premer dveh naših nizov sta enaka. Torej se morate še vedno prepričati, da se bodo stvari pravilno uvrstile, vendar lahko to storite v različnih dimenzijah.
Dve dimenziji in kompleks
Velikokrat so nam predstavljeni podatki, ki niso v najboljši postavitvi, primerni za naše formule. Lahko bi ga poskušali ročno preurediti ali pa smo s svojimi formulami pametnejši. Poglejmo naslednjo tabelo.
Tu imamo podatke za naše izdelke in prodajo, pomešane za vsak mesec. Kako bi ugotovili, koliko predmetov je Bob prodal za celo leto?
Za to bomo uporabili dve dodatni funkciji: SEARCH in ISNUMBER. Funkcija SEARCH nam bo omogočila, da v celicah glave poiščemo našo ključno besedo "postavke". Izhod te funkcije bo posledica številke ali napake (če ključne besede ni mogoče najti). Nato bomo za pretvorbo uporabili številko ISNUMBER to izhod v naše Boolean vrednosti. Naša formula bo videti kot spodaj.
S prvo matrico bi se morali že precej spoznati. Ustvaril bo izhod, kot je {0, 1, 0, 1}. Naslednji niz kriterijev, o katerem smo pravkar govorili. Ustvaril bo številko za vse celice z "elementi" v njih, za ostale pa napako {5, #N/A !, 5, #N/A!}. Številka ISNUMBER nato to pretvori v logično vrednost {True, False, True, False}. Potem, ko pomnožimo, bomo ohranili vrednosti le iz prvega in tretjega stolpca. Ko se vsa polja množijo drug proti drugemu, bodo edine številke, ki niso nič, označene tukaj:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob")*(ISNUMBER (ISKANJE ("Predmeti", B1: E1))*B2: E5)) |
SUMPRODUCT bo vse to sešteval in dobili bomo končni rezultat 29.
POVZETEK Or
Pojavljajo se številne situacije, ko bi radi povzeli vrednosti, če ima naš stolpec meril eno vrednost ALI drugo vrednost. To lahko dosežete v SUMPRODUCT tako, da med seboj dodate dva niza meril.
V tem primeru želimo dodati enote, prodane tako za rdečo kot modro.
Naša formula bo videti tako
| 1 | = PODROČJE (A2: A7, (B2: B7 = "rdeča")+(B2: B7 = "modra")) |
Oglejmo si niz kriterijev Red. Izdelal bo matriko, ki izgleda tako: {1, 1, 0, 0, 0, 0}. Niz modrih meril bo videti kot {0, 0, 1, 0, 1, 0}. Ko jih dodate skupaj, bo nova matrika videti kot {1, 1, 1, 0, 1, 0}. Vidimo lahko, kako sta se obe matriki združili v eno samo matriko kriterijev. Funkcija bo nato to pomnožila z našim prvim nizom in dobili bomo {100, 50, 10, 0, 75, 0}. Upoštevajte, da so bile vrednosti za zeleno izničene. Zadnji korak POVEZAVE je seštevanje vseh številk, da dosežemo rešitev 235.
Tu je treba opozoriti. Bodite previdni, ko se nizov meril ne izključujeta. V našem primeru so lahko vrednosti v stolpcu B rdeče ali modre, vendar smo vedeli, da nikoli ne more biti oboje. Razmislite, če bi zapisali to formulo:
| 1 | = PODROČJE (A2: A7, (A2: A7> = 50)+(B2: B7 = "Modro")) |
Naš namen je najti modre predmete, ki so bili prodani ali jih je bilo v količini več kot 50. Vendar ti pogoji niso izključni, saj je lahko ena vrstica v stolpcu A več kot 50 in bodi Modra. Tako bi bila prva matrika kriterijev videti kot {1, 1, 0, 1, 1, 0}, druga matrika pa je {0, 0, 1, 0, 1, 0}. Če jih dodate skupaj, dobite {1, 1, 1, 1, 2, 0}. Ali vidite, kako imamo zdaj tam 2? Če bi pustili pri miru, bi SUMPRODUCT podvojil vrednost v tej vrstici, spremenil bi 75 v 150, in dobili bi napačen rezultat. Če želite to popraviti, v naš niz postavimo zunanje preverjanje meril:
| 1 | = PODROČJE (A2: A7, -((A2: A7> = 50)+(B2: B7 = "Modro")> 0)) |
Zdaj, ko sta dva notranja niza meril združena, bomo preverili, ali je rezultat večji od 0. Tako se znebimo 2, ki smo jih imeli prej, in namesto tega bomo imeli matriko, kot je {1, 1, 1 , 1, 1, 0}, ki bo dala pravilen rezultat.
PODROČJE Natančno
Večina funkcij v Excelu ne razlikuje med velikimi in malimi črkami, včasih pa moramo biti sposobni iskati glede na občutljivost velikih in malih črk. Ko je želeni rezultat številčen, lahko to dosežemo z uporabo EXACT znotraj funkcije SUMPRODUCT. Razmislite o naslednji tabeli:
Želimo najti oceno za postavko »ABC123«. Običajno funkcija EXACT primerja dva elementa in vrne logični izhod, ki navaja, ali sta ta dva elementa točno enako. Ker pa smo znotraj PODROČJA, bo naš računalnik vedel, da imamo opravka z matrikami, in bo lahko primerjal en element z vsakim elementom v matriki. Naša formula bo videti tako
| 1 | = PODROČJE (-TOČNO ("ABC123", A2: A5), B2: B5) |
Funkcija EXACT bo nato preverila vsak element v A2: A5 in preverila, ali se ujema z vrednostjo in velikimi črkami. Tako dobimo matriko, ki je videti kot {0, 1, 0, 0}. Ko pomnožimo z B2: B5, matrika postane {0, 2, 0, 0}. Po končnem seštevanju dobimo rešitev 2.
SUMPRODUCT v Google Preglednicah
Funkcija SUMPRODUCT deluje v Google Preglednicah popolnoma enako kot v Excelu:
POVZETEK Primeri v VBA
V VBA lahko uporabite tudi funkcijo SUMPRODUCT. Vrsta: application.worksheetfunction.sumproduct (polje1, polje2, polje3)
Izvajanje naslednjih stavkov VBA
| 1 | Obseg ("B10") = Application.WorksheetFunction.SumProduct (obseg ("A2: A7"), obseg ("B2: B7")) |
bo prinesel naslednje rezultate
Za argumente funkcije (matrika1 itd.) Jih lahko vnesete neposredno v funkcijo ali pa določite spremenljivke, ki jih želite uporabiti.
