SUMPRODUCT-funktionen er faktisk opført under matematik- og trigonometrikategorien af Excel-funktioner. Fordi det primære formål med SUMPRODUCT er at beregne sumproduktet, ved de fleste ikke, at du faktisk kan bruge det til at slå værdier op. Faktisk kan du bruge denne alsidige funktion ganske effektivt i de fleste datamodeller.
SUMPRODUCT grundlæggende
SUMPRODUKT-funktionen er designet til at multiplicere værdier fra to eller flere dataområder og derefter lægge resultaterne sammen for at returnere summen af produkterne. Tag et kig på følgende figur for at se et typisk scenarie, hvor SUMPRODUKTET er nyttigt.
SUMPRODUCT, at få det samlede salg involverer multiplicering af pris” width=”458″/>
Uden SUMPRODUKTET involverer det samlede salg at gange prisen med enheder og derefter summere resultaterne.
Du ser en fælles analyse, hvor du skal bruge det samlede salg for årene 2011 og 2012. Som du kan se, for at få det samlede salg for hvert år, skal du først gange Pris med antallet af Enheder for at få totalen for hver Område. Så skal du summere disse resultater for at få det samlede salg for hvert år.
Med SUMPRODUCT-funktionen kan du udføre to-trinsanalysen med kun én formel. Følgende figur viser den samme analyse med SUMPRODUCT-formler. I stedet for at bruge 11 formler, kan du opnå den samme analyse med kun 3!
SUMPRODUCT-funktionen giver dig mulighed for at udføre den samme analyse med kun" width="535″/>
SUMPRODUKT-funktionen giver dig mulighed for at udføre den samme analyse med kun 3 formler i stedet for 11.
Syntaksen for SUMPRODUCT-funktionen er ret enkel:
SUMPRODUKT( Matrix1 , Matrix2 , …)
Array : Array repræsenterer en række data. Du kan bruge alt fra 2 til 255 arrays i en SUMPRODUCT-formel. Arrays ganges sammen og adderes derefter. Den eneste hårde og hurtige regel, du skal huske, er, at alle arrays skal have det samme antal værdier. Det vil sige, du kan ikke bruge SUMPRODUCT, hvis område X har 10 værdier og område Y har 11 værdier. Ellers får du #VALUE! fejl.
Et twist på SUMPRODUCT-funktionen
Det interessante ved SUMPRODUCT-funktionen er, at den kan bruges til at filtrere værdier fra. Tag et kig på følgende figur:
SUMPRODUCT-funktionen kan bruges til at filtrere data baseret på kriterier." width="535"/>
SUMPRODUKT-funktionen kan bruges til at filtrere data baseret på kriterier.
Formlen i celle E12 trækker summen af samlede enheder for kun den nordlige region. I mellemtiden trækker celle E13 de enheder, der er logget for den nordlige region i år 2011.
For at forstå, hvordan dette virker, skal du tage et kig på formlen i celle E12. Denne formel lyder SUMPRODUCT((C3:C10=“North“)*(E3:E10)).
I Excel evalueres SAND til 1, og FALSK evalueres til 0. Hver værdi i kolonne C, der er lig med Nord, evalueres til SAND eller 1. Hvor værdien ikke er nord, evalueres den til FALSK eller 0. Den del af formlen, der lyder (C3) :C10=“Nord“) opregner gennem hver værdi i området C3:C10, og tildeler en 1 eller 0 til hver værdi. Så internt oversættes SUMPRODUCT-formlen til
(1*E3)+(0*E4)+(0*E5)+(0*E6)+(1*E7)+(0*E8)+(0*E9)+(0*E10).
Dette giver dig svaret på 1628 fordi
(1*751)+(0*483)+(0*789)+(0*932)+(1*877)+(0*162)+(0*258)+(0*517)
svarer til 1628.
Anvendelse af SUMPRODUCT-formler i en datamodel
Som altid i Excel behøver du ikke at hårdkode kriterierne i dine formler. I stedet for eksplicit at bruge "North" i SUMPRODUCT-formlen, kan du referere til en celle, der indeholder filterværdien. Du kan forestille dig, at celle A3 indeholder ordet nord, i hvilket tilfælde du kan bruge (C3:C10=A3) i stedet for (C3:C10=“North“). På denne måde kan du dynamisk ændre dine filterkriterier, og din formel følger med.
Følgende figur viser, hvordan du kan bruge dette koncept til at trække data ind i en iscenesættelsestabel baseret på flere kriterier. Bemærk, at hver af SUMPRODUCT-formlerne vist her refererer til cellerne B3 og C3 for at filtrere på konto og produktlinje. Igen kan du tilføje rullelister til datavalidering til celler B3 og C3, så du nemt kan ændre kriterier.
SUMPRODUCT-funktionen kan bruges til at trække opsummerede tal fra d" width="535"/>
SUMPRODUKT-funktionen kan bruges til at trække opsummerede tal fra datalaget til iscenesættelsestabeller.