Kako uporabljati funkcijo Excel SUMPRODUCT -

Kazalo

Povzetek

Funkcija SUMPRODUCT pomnoži obsege ali nize in vrne vsoto izdelkov. To se sliši dolgočasno, toda SUMPRODUCT je neverjetno vsestranska funkcija, ki jo lahko uporabljamo za štetje in seštevanje, na primer COUNTIFS ali SUMIFS, vendar z večjo prilagodljivostjo. Druge funkcije je mogoče enostavno uporabiti znotraj SUMPRODUCT za nadaljnjo razširitev funkcionalnosti.

Namen

Pomnožite, nato seštejte polja

Vrnjena vrednost

Rezultat pomnoženih in seštetih nizov

Sintaksa

= SUMPRODUCT (polje1, (polje2),…)

Argumenti

  • array1 - prva matrika ali obseg, ki se pomnoži, nato dodaj.
  • array2 - (neobvezno) Drugo polje ali obseg, ki ga želite pomnožiti, nato dodajte.

Različica

Excel 2003

Opombe o uporabi

Funkcija SUMPRODUCT deluje z nizi, vendar za vnos ni potrebna običajna sintaksa matrike (Ctrl + Shift + Enter). Namen funkcije SUMPRODUCT je množenje, nato seštevanje nizov. Če je na voljo samo ena matrika, bo SUMPRODUCT preprosto sešteval elemente v matriki. Na voljo je do 30 nizov.

Ko se prvič srečate s SUMPRODUCT, se vam zdi dolgočasen, zapleten in celo nesmiseln. Toda SUMPRODUCT je neverjetno vsestranska funkcija s številnimi načini uporabe. Ker bo z matricami ravnal elegantno in brez pritožb, ga lahko uporabite za obdelavo obsegov celic na pametne in elegantne načine (glejte povezave do primerov formul na tej strani).

Tu je nekaj pogostih primerov, da ponazorimo, kako deluje SUMPRODUCT.

PUMPRODUCT za pogojne vsote in štetja

Predpostavimo, da imate nekaj podatkov o naročilu v A2: B6, z državo v stolpcu A, prodaja v stolpcu B:

A B
1. Država Prodaja
2. UT 75
3. CO 100
4. TX 125
5. CO 125
6. TX 150

S pomočjo SUMPRODUCT lahko s to formulo preštejete skupno prodajo v Teksasu ("TX"):

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

Skupno prodajo za Teksas ("TX") lahko seštejete s to formulo:

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

Opomba: dvojni negativ naj vas ne zmede. To je pogost trik, ki se uporablja v naprednejših Excelovih formulah za vsiljevanje TRUE in FALSE vrednosti v 1 in 0. Več si oglejte spodaj …

Za zgornji primer vsote je tukaj virtualna predstavitev dveh nizov, kot jih je prvi obdelal SUMPRODUCT:

matrika1 polje2
NAPAKA 75
NAPAKA 100
PRAV 125
NAPAKA 125
PRAV 150

Vsako polje ima 5 elementov. Prvo polje vsebuje vrednosti TRUE / FALSE, ki izhajajo iz izraza A2: A6 = "TX", drugo polje pa je vsebina B2: B6. Vsak element v prvem polju bo pomnožen z ustreznim elementom v drugem polju. Vendar bo v trenutnem stanju rezultat SUMPRODUCT nič, ker bosta TRUE in FALSE vrednosti obravnavani kot nič. Elementi v matriki1 morajo biti številski - jih je treba "prisiliti" v enote in ničle. Tu pride dvojni negativ.

Z uporabo dvojnega negativa - (dvojno unarno, za vaše tehnične tipe) lahko prisilimo TRUE / FALSE v številske vrednosti ena in nič, kot je prikazano v spodnji virtualni predstavitvi. Zadnji stolpec "Izdelek" predstavlja rezultat množenja obeh nizov. Povzeti rezultat, 275, je vrednost, ki jo vrne SUMPRODUCT.

matrika1 polje2 Izdelka
0 * 75 = 0
0 * 100 = 0
1. * 125 = 125
0 * 125 = 0
1. * 150 = 150
Vsota 275

Če uporabimo sintakso kodrastih oklepajev za polja, je primer videti tako po prisili:

=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širja zgornje ideje z več podrobnostmi.

SUMPRODUCT z drugimi funkcijami

SUMPRODUCT lahko neposredno uporablja druge funkcije. Morda boste videli SUMPRODUCT, ki se uporablja s funkcijo LEN za štetje skupnih znakov v obsegu ali s funkcijami, kot so ISBLANK, ISTEXT itd. To običajno niso funkcije matrike, ko pa dobijo obseg, ustvarijo "matriko rezultatov". Ker je SUMPRODUCT zasnovan za delo z nizi, lahko neposredno izvaja izračune na nizih. To je lahko dober način za prihranek prostora na delovnem listu z odpravo potrebe po stolpcu »pomočnik«.

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

=SUMPRODUCT(LEN(A1:A10))

Če se LEN uporablja v obsegu, kot je A1: A10, vrne polje 10 vrednosti. Nato SUMPRODUCT preprosto sešteje vse vrednosti in vrne rezultat, pri čemer ni potreben pomožni stolpec.

Spodaj si oglejte primere mnogih drugih načinov uporabe SUMPRODUCT.

Opombe:

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

Zanimive Članki...