Excel SUMPRODUCT funkcija

Funkcija SUMPRODUCT pomnoži obsege ali matrike skupaj in vrne vsoto produktov. Sliši se dolgočasno, vendar je SUMPRODUCT neverjetno vsestranska funkcija, ki jo je mogoče uporabiti za štetje in seštevanje, na primer COUNTIFS ali SUMIFS, vendar z večjo prilagodljivostjo. Druge funkcije je mogoče preprosto uporabiti v SUMPRODUCT -u, da še dodatno razširite funkcionalnost.

• matrika1 - Prvi niz ali obseg za množenje, nato seštevanje.
• matrika 2 - [neobvezno] Drugi niz ali obseg za množenje in nato seštevanje.

Funkcija SUMPRODUCT deluje z matrikami, vendar za vnos ne potrebuje običajne sintakse matrike (Ctrl + Shift + Enter). Namen funkcije SUMPRODUCT je množenje, nato seštevanje nizov. Če je na voljo samo eno polje, bo SUMPRODUCT preprosto sešteval elemente v matriki. Na voljo je do 30 nizov.

Ko prvič naletite na PODROČJE, se vam lahko zdi dolgočasno, zapleteno in celo nesmiselno. Toda SUMPRODUCT je neverjetno vsestranska funkcija z veliko uporabami. Ker bo z matrikami ravnal elegantno in brez pritožb, ga lahko uporabite za pametno in elegantno obdelavo obsegov celic (glejte povezave do primerov formul na tej strani).

Za ponazoritev delovanja SUMPRODUCT -a je nekaj pogostih primerov.

SUMPRODUCT za pogojne vsote in štetja

Predpostavimo, da imate nekaj podatkov o naročilu v A2: B6, stanje v stolpcu A, prodaja v stolpcu B:

Z uporabo SUMPRODUCT lahko šteti skupna prodaja za Teksas ('TX') s to formulo:

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

In lahko vsota skupna prodaja za Teksas ('TX') s to formulo:

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

Opomba: Naj vas dvojni negativ ne zmoti. To je pogost trik, ki se uporablja v naprednejših Excelovih formulah, da se vrednosti TRUE in FALSE izsilijo v enote in 0. Več si oglejte spodaj ...

formula za pretvorbo minut v decimalke

Za zgornji primer vsote je tukaj navidezna predstavitev dveh nizov, ki jih je prvi obdelal SUMPRODUCT:

Vsak niz ima 5 elementov. Prva matrika vsebuje vrednosti TRUE / FALSE, ki izhajajo iz izraza A2: A6 = 'TX', druga matrika pa je vsebina B2: B6. Vsak element v prvem nizu bo pomnožen z ustreznim elementom v drugem nizu. Vendar bo v trenutnem stanju rezultat SUMPRODUCT enak nič, ker se vrednosti TRUE in FALSE obravnavata kot nič. Predmeti v matriki1 morajo biti numerični - treba jih je 'prisiliti' v enote in ničle. Tu nastopi dvojno negativno.

Z uporabo dvojnega negativa - (dvojno enotno, za vas tehnične vrste) lahko potrdimo TRUE/FALSE v številske vrednosti ena in nič, kot je prikazano v spodnji virtualni predstavitvi. Zadnji stolpec 'Produkt' predstavlja rezultat množenja obeh nizov skupaj. Sešteti rezultat 275 je vrednost, ki jo vrne SUMPRODUCT.

Z uporabo sintakse kodrastih oklepajev za matrike je primer po prisili videti takole:

kako narediti formulo if v

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

in tako po množenju:

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

Ta primer razširi zgornje zamisli z več podrobnostmi.

SUMPRODUCT z drugimi funkcijami

SUMPRODUCT lahko neposredno uporablja druge funkcije. Morda boste videli, da se SUMPRODUCT uporablja s funkcijo LEN za štetje skupnih znakov v obsegu ali s funkcijami, kot so ISBLANK, ISTEXT itd. To običajno niso funkcije niza, vendar ko dobijo obseg, ustvarijo 'matriko rezultatov'. Ker je SUMPRODUCT zgrajen za delo z matrikami, lahko neposredno izvaja izračune v matrikah. To je lahko dober način za prihranek prostora na delovnem listu, tako da odpravite potrebo po stolpcu 'pomočnik'.

Recimo, da imate v A1: A10 10 različnih besedilnih vrednosti in želite šteti skupno število znakov za vseh 10 vrednosti. V stolpec B lahko dodate stolpec pomočnik, ki uporablja to formulo: LEN (A1) za izračun znakov v vsaki celici. Nato lahko uporabite SUM za seštevanje vseh 10 številk. Vendar pa lahko z uporabo SUMPRODUCT napišete tako formulo:

 
= SUMPRODUCT ( LEN (A1:A10))

Ko se uporablja z obsegom, kot je A1: A10, bo LEN vrnil niz 10 vrednosti. Nato SUMPRODUCT preprosto sešteje vse vrednosti in vrne rezultat, ne da bi bil potreben pomožni stolpec.

Spodaj si oglejte primere številnih drugih načinov uporabe SUMPRODUCT.

Opombe:

1. SUMPRODUCT obravnava neštevilske elemente v matrikah kot ničle.
2. Argumenti niza morajo biti enake velikosti. V nasprotnem primeru bo SUMPRODUCT ustvaril #VALUE! vrednost napake.
3. Logični testi znotraj nizov bodo ustvarili vrednosti TRUE in FALSE. V večini primerov jih boste želeli prisiliti na 1 in 0.
4. SUMPRODUCT lahko pogosto neposredno uporabi rezultat drugih funkcij (glej primere formul spodaj)