Last ned eksemplet på arbeidsbok
Denne opplæringen viser hvordan du bruker Excel SUMPRODUCT -funksjon i Excel.
SUMPRODUCT Funksjon Oversikt
SUMPRODUCT -funksjonen multipliserer matriser med tall og summerer den resulterende matrisen.
For å bruke SUMPRODUCT Excel -regnearkfunksjonen, velg en celle og skriv:
(Legg merke til hvordan formelinngangene vises)
SUMPRODUCT -funksjon Syntaks og innganger:
| 1 | = SUMPRODUCT (array1, array2, array3) |
matrise1 - Matriser med tall.
Hva er SUMPRODUCT -funksjonen?
SUMPRODUCT -funksjonen er en av de kraftigere funksjonene i Excel. Det er navnet, kan få deg til å tro at det bare er ment for grunnleggende matematiske beregninger, men det kan brukes til så mye mer.
Matriser
SUMPRODUCT krever innspill av matriser.
Så først, hva mener vi med "array"? En matrise er enkel en gruppe elementer (eks. Tall) arrangert i en bestemt rekkefølge, akkurat som et utvalg av celler. Så hvis du hadde tallene 1, 2, 3 i cellene A1: A3, ville Excel lese dette som matrise {1,2,3}. Faktisk kan du skrive inn {1,2,3} direkte i Excel -formler, og det vil gjenkjenne matrisen.
Vi skal snakke mer om matriser nedenfor, men la oss først se på et enkelt eksempel.
Grunnleggende matematikk
La oss se på et grunneksempel på SUMPRODUCT, og bruke det til å beregne totalt salg.
Vi har vårt produktbord, og vi vil beregne det totale salget. Du blir fristet til å bare legge til en ny kolonne, ta mengden solgt * pris og deretter oppsummere den nye kolonnen. I stedet kan du imidlertid bare bruke SUMPRODUCT -funksjonen. La oss gå gjennom formelen:
| 1 | = SUMPRODUCT (A2: A4, B2: B4) |
Funksjonen vil laste tallområdene inn i matriser, multiplisere dem mot hverandre, og deretter summere resultatene:
| 1234 | = SUMPRODUCT ({100, 50, 10}, {6, 7, 5})= SUMPRODUKT ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUCT ({600, 350, 50}= 1000 |
SUMPRODUCT Funciton klarte å multiplisere alle tallene for oss OG gjøre summeringen.
Vektlagt gjennomsnitt
Et annet tilfelle der det er nyttig å bruke SUMPRODUCT, er når du må beregne et veid gjennomsnitt. Dette skjer oftest når det gjelder skolearbeid, så la oss se på tabellen nedenfor.
Vi kan se hvor mye spørrekonkurranser, tester og lekser er verdt i forhold til den totale karakteren, samt hva gjennomsnittet er for hvert enkelt element. Vi kan beregne totalkarakteren da ved å skrive
| 1 | = SUMPRODUCT (B2: B4, C2: C4) |
Vår funksjon multipliserer igjen hvert element i matrisene før summen summeres. Dette fungerer slik
| 123 | = SUMPRODUCT ({30%, 50%, 20%}, {73%, 90%, 95%})= SUMPRODUCT ({22%, 45%, 19%})= 86% |
Flere kolonner
Et annet sted vi kan bruke SUMPRODUCT er med enda flere kolonner som alle må multipliseres mot hverandre. La oss se på et eksempel der vi må beregne volum i tømmerstykker.
I stedet for å lage en hjelperkolonne for å beregne det totale salget for hver rad, kan vi gjøre dette med en enkelt formel. Vår formel blir
| 1 | = SUMPRODUCT (B2: B5, C2: C5, D2: D5) |
Hver arrays første elementer multipliseres mot hverandre (f.eks. 4 * 2 * 1 = 8). Deretter den andre (4 * 2 * 2 = 16) og 3rd, etc. Samlet sett vil dette produsere en rekke produkter som ser ut som {8, 16, 16, 32). Da vil det totale volumet være summen av det arrayet, 72.
Ett kriterium
Ok, la oss legge til et nytt kompleksitetslag. Vi har sett at SUMPRODUCT kan håndtere matriser med tall, men hva om vi ønsker å sjekke kriterier? Vel, du kan også opprette matriser for boolske verdier (boolske verdier er verdier som er SANN eller FALSE).
Ta for eksempel en grunnleggende matrise {1, 2, 3}. La oss lage en tilsvarende matrise som angir om hvert tall er større enn 1. Denne matrisen vil se ut som {FALSE, TRUE, TRUE}.
Dette er ekstremt nyttig i formler, fordi vi enkelt kan konvertere TRUE / FALSE til 1 / 0. La oss se på et eksempel.
Ved å bruke tabellen nedenfor, vil vi beregne "Hvor mange solgte enheter var røde?"
Vi kan gjøre det med denne formelen:
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Red")) |
"Vent litt! Hva er det med det dobbelte minus -symbolet der? " du sier. Husker du hvordan jeg sa at vi kunne konvertere fra True/False til 1/0? Vi gjør dette ved å tvinge datamaskinen til å utføre en matematisk operasjon. I dette tilfellet sier vi "ta den negative verdien, og ta den negative igjen". Når vi skriver det ut, kommer matrisen vår til å endre seg slik:
| 123 | {Sant, sant, usant}{-1, -1, 0}{1, 1, 0} |
Så tilbake til hele SUMPRODUCT -formelen, den kommer til å lastes inn i matrisene våre og deretter multiplisere, slik som dette
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= SUMPRODUCT ({100, 50, 0})= 150 |
Legg merke til hvordan 3rd elementet ble 0, fordi alt multiplisert med 0 blir null.
Flere kriterier
Vi kan laste inn opptil 255 matriser i funksjonen vår, så vi kan sikkert laste inn flere kriterier. La oss se på dette større bordet der vi har lagt til den solgte måneden.
Hvis vi vil vite hvor mange solgte varer som var røde og var i februar måned, kunne vi skrive formelen vår som
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Red"), -(C2: C4 = "Feb")) |
Datamaskinen ville deretter evaluere våre matriser og multiplisere på tvers. Vi har allerede dekket hvordan True/False -arrays blir endret til 1/0, så jeg kommer til å hoppe over det trinnet for nå.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCT ({0, 50, 0})= 50 |
Vi hadde bare én rad i eksemplet vårt som samsvarte med alle kriteriene, men med virkelige data kan du ha flere rader du trengte lagt sammen.
Komplekse kriterier
Ok, så langt er du kanskje ikke imponert fordi alle våre eksempler kunne ha blitt gjort ved hjelp av andre funksjoner som SUMIF eller COUNTIF. Nå skal vi gjøre noe med de andre funksjonene kan ikke gjøre. Tidligere hadde vår månedskolonne de faktiske navnene på måneder. Hva om den i stedet hadde datoer?
Vi kan ikke gjøre en SUMIF nå, fordi SUMIF ikke kan håndtere kriteriene vi trenger. SUMPRODUCT kan imidlertid håndtere oss med å manipulere matrisen, og gjøre en dypere test. Vi har allerede manipulert matriser når vi har oversatt det sanne/usanne til 1/0. Vi kommer til å manipulere denne matrisen med MONTH -funksjonen. Her er hele formelen vi skal bruke
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Red"), -(MÅNED (C2: C4) = 2)) |
La oss se på 3rd rekke nærmere. Først skal formelen trekke ut månedsnummeret fra hver dato i C2: C4. Dette gir oss {1, 2, 2}. Deretter sjekker vi om verdien er lik 2. Nå ser matrisen vår ut som {False, True, True}. Vi gjør det doble minuset igjen, og vi har {0, 1, 1}. Vi er nå tilbake på et lignende sted som vi hadde i eksempel 3, og formelen vår vil kunne fortelle oss at det var 50 enheter solgt i februar som var røde.
Dobbel minus vs. multiplisering
Hvis du har sett SUMPRODUCT -funksjonen i bruk før, har du kanskje sett en litt annen notasjon. I stedet for å bruke et dobbelt minus, kan du skrive
| 1 | = SUMPRODUCT (A2: A4*(B2: B4 = "Red")*(MÅNED (C2: C4) = 2)) |
Formelen kommer fortsatt til å fungere på samme måte, vi forteller bare datamaskinen manuelt at vi vil multiplisere matrisene. SUMPRODUCT skulle gjøre dette uansett, så det er ingen endring i hvordan matematikken fungerer. Å utføre matematikkoperasjonen konverterer vår sanne/usanne til 1/0 det samme. Så hvorfor forskjellen?
Mesteparten av tiden spiller det ingen rolle for mye, og det kommer ned på brukerens preferanser. Det er imidlertid minst ett tilfelle der multiplisering er nødvendig.
Når du bruker SUMPRODUCT, forventer datamaskinen at alle argumentene (array1, array2, etc.) har samme størrelse. Dette betyr at de har samme antall rader eller kolonner. Du kan imidlertid gjøre det som er kjent som en todimensjonal matriseberegning med SUMPRODUCT som vi ser i neste eksempel. Når du gjør det, har matrisene forskjellige størrelser, så vi må omgå den "alle samme størrelsen" -sjekken.
To dimensjoner
Alle de foregående eksemplene hadde våre matriser i samme retning. SUMPRODUCT kan håndtere ting som går i to retninger, som vi ser i neste tabell.
Her er tabellen over solgte enheter, men dataene blir omorganisert der kategorier går over toppen. Hvis vi vil finne ut hvor mange varer som var røde og i kategori A, kan vi skrive
| 1 | = SUMPRODUCT ((A2: A4 = "Rød")*(B1: C1 = "A")*B2: C4) |
Hva skjer her?? Det viser seg at vi kommer til å multiplisere i to forskjellige retninger. Å visualisere dette er vanskeligere å gjøre med bare en skrevet setning, så vi har noen bilder som kan hjelpe oss. For det første kommer radkriteriene våre (er det rødt?) Til å multiplisere på tvers av hver rad i rekken.
| 1 | = SUMPRODUCT ((A2: A4 = "RØD")*B2: C4) |
Deretter skal kolonnekriteriene (er det kategori A?) Multiplisere nedover hver kolonne
| 1 | = SUMPRODUCT ((A2: A4 = "Rød")*(B1: C1 = "A")*B2: C4) |
Etter at begge disse kriteriene har gjort jobben sin, er det bare 5 og 10. SUMPRODUCT gir oss totalt 15 som vårt svar.
Husker du hvordan vi snakket om at matrisene måtte ha samme størrelse med mindre du gjør to dimensjoner? Det var delvis riktig. Ser igjen på matrisene vi brukte i formelen vår. De høyde av to av våre matriser er det samme, og bredde av to av våre matriser er like. Så du må fortsatt sørge for at ting kommer til å stemme riktig, men du kan gjøre det i forskjellige dimensjoner.
To dimensjoner og komplekse
Mange ganger blir vi presentert med data som ikke er i det beste oppsettet som passer for våre formler. Vi kan prøve å omorganisere det manuelt, eller vi kan være smartere med formlene våre. La oss vurdere følgende tabell.
Her har vi dataene for våre varer og salg blandet sammen for hver måned. Hvordan skulle vi finne ut hvor mange varer Bob har solgt hele året?
For å gjøre dette bruker vi to ekstra funksjoner: SØK og ISNUMBER. SEARCH -funksjonen lar oss lete etter søkeordet vårt "elementer" i topptekstcellene. Utdataene fra denne funksjonen kommer enten til et tall eller en feil (hvis nøkkelordet ikke blir funnet). Deretter bruker vi ISNUMBER til å konvertere at utgang til våre boolske verdier. Formelen vår kommer til å se ut som nedenfor.
Du bør være ganske kjent med den første serien nå. Det kommer til å lage en utgang som {0, 1, 0, 1}. Det neste kriteriesystemet vi nettopp snakket om. Det kommer til å opprette et tall for alle cellene med "Elementer" i dem, og en feil for de andre {5, #N/A !, 5, #N/A!}. ISNUMBER konverterer deretter dette til booleske {True, False, True, False}. Når vi multipliserer, beholder det bare verdier fra den første og tredje kolonnen. Etter at alle matrisene har multiplisert seg mot hverandre, er de eneste tallene som ikke er null, de som er markert her:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob")*(ISNUMBER (SØK ("Elementer", B1: E1))*B2: E5)) |
SUMPRODUCT vil deretter legge alt sammen, og vi får vårt endelige resultat på 29.
SUMPRODUCT Or
Mange situasjoner oppstår der vi ønsker å kunne oppsummere verdier hvis kriteriekolonnen vår har én verdi ELLER en annen verdi. Du kan oppnå dette i SUMPRODUCT ved å legge til to kriteriekonstruksjoner mot hverandre.
I dette eksemplet ønsker vi å legge opp enheter som selges for både rød og blå.
Formelen vår vil se slik ut
| 1 | = SUMPRODUCT (A2: A7, (B2: B7 = "Red")+(B2: B7 = "Blue")) |
La oss se på den røde kriterien. Det vil produsere en matrise som ser slik ut: {1, 1, 0, 0, 0, 0}. Den blå kriteriene vil se ut som {0, 0, 1, 0, 1, 0}. Når du legger dem sammen, ser den nye matrisen ut som {1, 1, 1, 0, 1, 0}. Vi kan se hvordan de to matrisene har blandet seg til en enkelt kriterieserie. Funksjonen vil deretter multiplisere det med vår første matrise, og vi får {100, 50, 10, 0, 75, 0}. Legg merke til at verdiene for Green er nullstilt. Det siste trinnet i SUMPRODUCT er å legge alle tallene sammen for å nå vår løsning på 235.
Ett ord av forsiktighet her. Vær forsiktig når kriteriene ikke utelukker hverandre. I vårt eksempel kan verdiene i kolonne B enten være rød eller blå, men vi visste at det aldri kunne være begge deler. Vurder om vi hadde skrevet denne formelen:
| 1 | = SUMPRODUCT (A2: A7, (A2: A7> = 50)+(B2: B7 = "Blue")) |
Vår intensjon er å finne blå varer som ble solgt eller var i en mengde på mer enn 50. Disse betingelsene er imidlertid ikke eksklusive, da en enkelt rad kan være begge over 50 i kolonne A og være blå. Dette vil resultere i at det første kriteriesystemet ser ut som {1, 1, 0, 1, 1, 0}, og det andre kriteriet er {0, 0, 1, 0, 1, 0}. Ved å legge dem sammen ble det produsert {1, 1, 1, 1, 2, 0}. Ser du hvordan vi har en 2 der inne nå? Hvis den blir alene, vil SUMPRODUCT ende opp med å doble verdien i den raden, endre 75 til en 150, og vi får feil resultat. For å korrigere dette, legger vi en ytre kriteriekontroll på matrisen vår, slik:
| 1 | = SUMPRODUCT (A2: A7, -((A2: A7> = 50)+(B2: B7 = "Blue")> 0)) |
Nå, etter at de to indre kriteriene er lagt sammen, sjekker vi om resultatet er større enn 0. Dette blir kvitt de 2 vi hadde før, og i stedet har vi en matrise som {1, 1, 1 , 1, 1, 0} som gir riktig resultat.
SUMPRODUCT Eksakt
De fleste funksjonene i Excel er ikke store og små bokstaver, men noen ganger må vi være i stand til å lete etter store og små bokstaver. Når ønsket resultat er numerisk, kan vi oppnå dette ved å bruke EKSAKT inne i SUMPRODUCT -funksjonen. Vurder følgende tabell:
Vi ønsker å finne poengsummen for element “ABC123”. Normalt vil EXAKT -funksjonen sammenligne to elementer og returnere en boolsk utgang som angir om de to elementene er nøyaktig det samme. Siden vi imidlertid er inne i et SUMPRODUCT, vil datamaskinen vår vite at vi har å gjøre med matriser og vil kunne sammenligne ett element med hvert element i en matrise. Formelen vår vil se slik ut
| 1 | = SUMPRODUCT (-EXACT ("ABC123", A2: A5), B2: B5) |
EKSAKT -funksjonen vil deretter sjekke hvert element i A2: A5 for å se om det samsvarer med verdi og store bokstaver. Dette vil produsere en matrise som ser ut som {0, 1, 0, 0}. Når det multipliseres mot B2: B5, blir matrisen {0, 2, 0, 0}. Etter den siste summeringen får vi vår løsning på 2.
SUMPRODUCT i Google Regneark
SUMPRODUCT -funksjonen fungerer nøyaktig det samme i Google Regneark som i Excel:
SUMPRODUCT Eksempler i VBA
Du kan også bruke SUMPRODUCT -funksjonen i VBA. Type: application.worksheetfunction.sumproduct (array1, array2, array3)
Utfører følgende VBA -utsagn
| 1 | Range ("B10") = Application.WorksheetFunction.SumProduct (Range ("A2: A7"), Range ("B2: B7")) |
vil gi følgende resultater
For funksjonsargumentene (array1, etc.) kan du enten skrive dem inn direkte i funksjonen, eller definere variabler som skal brukes i stedet.
