Kako koristiti Excel SUMPRODUCT funkciju -

Sadržaj

Sažetak

Funkcija SUMPRODUCT množi raspone ili nizove i vraća zbroj proizvoda. Ovo zvuči dosadno, ali SUMPRODUCT je nevjerojatno svestrana funkcija koja se može koristiti za brojanje i zbrajanje poput COUNTIFS ili SUMIFS, ali uz veću fleksibilnost. Ostale funkcije mogu se lako koristiti unutar SUMPRODUCT-a za dodatno proširenje funkcionalnosti.

Svrha

Pomnožite, a zatim zbrojite nizove

Povratna vrijednost

Rezultat umnoženih i zbrojenih nizova

Sintaksa

= SUMPRODUCT (niz1, (niz2),…)

Argumenti

  • niz1 - prvi niz ili raspon koji se množe, a zatim zbrajaju.
  • niz2 - (nije obavezno) Drugi niz ili raspon za množenje, a zatim zbrajanje.

Verzija

Excel 2003

Napomene o upotrebi

Funkcija SUMPRODUCT radi s nizovima, ali za unos nije potrebna normalna sintaksa niza (Ctrl + Shift + Enter). Svrha funkcije SUMPRODUCT je množenje, a zatim zbrajanje nizova. Ako je isporučen samo jedan niz, SUMPRODUCT će jednostavno zbrojiti stavke u polju. Može se isporučiti do 30 nizova.

Kada se prvi put susretnete sa SUMPRODUCT-om, on se može činiti dosadnim, složenim, pa čak i besmislenim. No, SUMPRODUCT je nevjerojatno svestrana funkcija s mnogim namjenama. Budući da će graciozno i ​​bez prigovora obraditi nizove, možete ga koristiti za obradu raspona stanica na pametne, elegantne načine (pogledajte veze do primjera formula na ovoj stranici).

Evo nekoliko primjera kako bismo ilustrirali kako funkcionira SUMPRODUCT.

SUMPRODUCT za uvjetne iznose i brojeve

Pretpostavimo da imate neke podatke o narudžbi u A2: B6, s Državom u stupcu A, Prodaja u stupcu B:

A B
1 država Prodajni
2 UT 75
3 CO 100
4 TX 125
5 CO 125
6 TX 150

Korištenjem SUMPRODUCT-a možete izračunati ukupnu prodaju za Texas ("TX") pomoću ove formule:

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

A ukupnu prodaju u Teksasu ("TX") možete zbrojiti pomoću ove formule:

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

Napomena: Nemojte biti zbunjeni dvostrukim negativom. Ovo je uobičajeni trik koji se koristi u naprednijim Excelovim formulama za prisiljavanje TRUE i FALSE vrijednosti na 1 i 0. Pogledajte više u nastavku …

Za gornji primjer zbroja, ovdje je virtualni prikaz dva niza kako ih je prvi obradio SUMPRODUCT:

niz1 niz2
NETOČNO 75
NETOČNO 100
PRAVI 125
NETOČNO 125
PRAVI 150

Svaki niz ima 5 predmeta. Prvi niz sadrži vrijednosti TRUE / FALSE koje proizlaze iz izraza A2: A6 = "TX", a drugi niz sadrži sadržaj B2: B6. Svaka će se stavka u prvom polju pomnožiti s odgovarajućom stavkom u drugom polju. Međutim, u trenutnom stanju rezultat SUMPRODUCT-a bit će nula jer će se vrijednosti TRUE i FALSE tretirati kao nula. Stavke u array1 trebaju nam biti numeričke - treba ih "prisiliti" u jedinice i nule. Tu dolazi dvostruko negativan.

Korištenjem dvostrukog negativa - (dvostruko unarno, za vaše tehničke tipove) u mogućnosti smo prisiliti TRUE / FALSE na numeričke vrijednosti jedan i nula, kao što je prikazano u virtualnom prikazu ispod. Posljednji stupac "Proizvod" predstavlja rezultat množenja dva polja zajedno. Zbrojeni rezultat, 275, vrijednost je koju vraća SUMPRODUCT.

niz1 niz2 Proizvod
0 * 75 = 0
0 * 100 = 0
1 * 125 = 125
0 * 125 = 0
1 * 150 = 150
Iznos 275

Koristeći sintaksu kovrčavih zagrada za nizove, primjer izgleda ovako nakon prisile:

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

i ovako nakon množenja:

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

Ovaj primjer proširuje gornje ideje s više detalja.

SUMPRODUCT s ostalim funkcijama

SUMPRODUCT može izravno koristiti druge funkcije. Možda ćete vidjeti SUMPRODUCT koji se koristi s funkcijom LEN za brojanje ukupnih znakova u rasponu ili s funkcijama poput ISBLANK, ISTEXT itd. To obično nisu funkcije niza, ali kada im se da raspon, oni stvaraju "niz rezultata". Budući da je SUMPRODUCT izgrađen za rad s nizovima, on može izravno vršiti izračune na nizovima. Ovo može biti dobar način uštede prostora na radnom listu uklanjanjem potrebe za stupcem "pomoćnik".

Na primjer, pretpostavimo da imate 10 različitih tekstualnih vrijednosti u A1: A10 i želite brojati ukupan broj znakova za svih 10 vrijednosti. U stupac B možete dodati pomoćni stupac koji koristi ovu formulu: LEN (A1) za izračunavanje znakova u svakoj ćeliji. Tada biste mogli koristiti SUM za zbrajanje svih 10 brojeva. Međutim, pomoću SUMPRODUCT-a možete napisati formulu poput ove:

=SUMPRODUCT(LEN(A1:A10))

Kada se koristi s rasponom poput A1: A10, LEN će vratiti niz od 10 vrijednosti. Tada će SUMPRODUCT jednostavno zbrojiti sve vrijednosti i vratiti rezultat, bez potrebnog pomoćnog stupca.

Pogledajte dolje primjere mnogih drugih načina korištenja SUMPRODUCT-a.

Bilješke:

  1. SUMPRODUCT ne-numeričke stavke u nizovima tretira kao nule.
  2. Argumenti niza moraju biti iste veličine. U suprotnom, SUMPRODUCT će generirati #Vrijednost! vrijednost pogreške.
  3. Logički testovi unutar polja stvorit će TRUE i FALSE vrijednosti. U većini slučajeva morat ćete ih prisiliti na 1 i 0.
  4. SUMPRODUCT često može izravno koristiti rezultat drugih funkcija (vidi dolje primjere formula)

Zanimljivi članci...