Download eksemplet på projektmappe
Denne vejledning viser, hvordan du bruger Excel SUMPRODUCT -funktion i Excel.
SUMPRODUCT Funktionsoversigt
SUMPRODUCT -funktionen multiplicerer arrays med tal og summerer det resulterende array.
Hvis du vil bruge SUMPRODUCT Excel -regnearksfunktionen, skal du vælge en celle og skrive:
(Bemærk hvordan formelindgangene vises)
SUMPRODUCT -funktion Syntaks og input:
| 1 | = SUMPRODUCT (array1, array2, array3) |
array1 - Opstillinger af tal.
Hvad er SUMPRODUCT -funktionen?
SUMPRODUCT -funktionen er en af de mere kraftfulde funktioner i Excel. Det er navn, kan få dig til at tro, at det kun er beregnet til grundlæggende matematiske beregninger, men det kan bruges til så meget mere.
Arrays
SUMPRODUCT kræver input af arrays.
Så hvad mener vi først med "array"? En matrix er simpel en gruppe af varer (eks. Numre) arrangeret i en bestemt rækkefølge, ligesom et cellecelleområde. Så hvis du havde tallene 1, 2, 3 i cellerne A1: A3, ville Excel læse dette som array {1,2,3}. Faktisk kan du indtaste {1,2,3} direkte i Excel -formler, og det vil genkende arrayet.
Vi taler mere om arrays nedenfor, men lad os først se på et enkelt eksempel.
Grundlæggende matematik
Lad os se på et grundlæggende eksempel på SUMPRODUCT og bruge det til at beregne det samlede salg.
Vi har vores tabel med produkter, og vi vil beregne det samlede salg. Du bliver fristet til bare at tilføje en ny kolonne, tage den solgte mængde * pris og derefter opsummere den nye kolonne. I stedet kan du dog blot bruge SUMPRODUCT -funktionen. Lad os gå igennem formlen:
| 1 | = SUMPRODUCT (A2: A4, B2: B4) |
Funktionen indlæser talintervallerne i arrays, flere dem mod hinanden og summerer derefter resultaterne:
| 1234 | = SUMPRODUCT ({100, 50, 10}, {6, 7, 5})= SUMPRODUCT ({100 * 6, 50 * 7, 10 * 5})= SUMPRODUCT ({600, 350, 50}= 1000 |
SUMPRODUCT Funciton var i stand til at gange alle tallene for os OG lave summeringen.
Vægtet gennemsnit
Et andet tilfælde, hvor det er nyttigt at bruge SUMPRODUCT, er, når du skal beregne et vægtet gennemsnit. Dette sker oftest, når vi beskæftiger os med skolearbejde, så lad os overveje følgende tabel.
Vi kan se, hvor meget quizzer, test og lektier er værd i forhold til den samlede karakter, samt hvad det nuværende gennemsnit er for hvert enkelt element. Vi kan beregne den samlede karakter derefter ved at skrive
| 1 | = SUMPRODUCT (B2: B4, C2: C4) |
Vores funktion multiplicerer igen hvert element i arrays før summen af det samlede beløb. Dette fungerer sådan
| 123 | = SUMPRODUCT ({30%, 50%, 20%}, {73%, 90%, 95%})= SUMPRODUCT ({22%, 45%, 19%})= 86% |
Flere kolonner
Et andet sted, vi kan bruge SUMPRODUCT, er med endnu flere kolonner, der alle skal multipliceres mod hinanden. Lad os se på et eksempel, hvor vi skal beregne volumen i tømmerstykker.
I stedet for at oprette en hjælperkolonne til at beregne det samlede salg for hver række, kan vi gøre dette med en enkelt formel. Vores formel bliver
| 1 | = SUMPRODUCT (B2: B5, C2: C5, D2: D5) |
Hver arrays første elementer multipliceres mod hinanden (f.eks. 4 * 2 * 1 = 8). Derefter 2. (4 * 2 * 2 = 16) og 3rdosv. Samlet set vil dette producere den vifte af produkter, der ligner {8, 16, 16, 32). Så ville det samlede volumen være summen af det array, 72.
Et kriterium
Okay, lad os tilføje endnu et lag af kompleksitet. Vi har set, at SUMPRODUCT kan håndtere arrays af tal, men hvad med, hvis vi vil kontrollere kriterier? Nå, du kan også oprette arrays for boolske værdier (boolske værdier er værdier, der er SANDE eller FALSKE).
Tag f.eks. Et grundlæggende array {1, 2, 3}. Lad os oprette en tilsvarende matrix, der angiver, om hvert tal er større end 1. Denne matrix ville ligne {FALSK, SAND, SAND}.
Dette er yderst nyttigt i formler, fordi vi let kan konvertere SAND / FALSK til 1 / 0. Lad os se på et eksempel.
Ved hjælp af nedenstående tabel vil vi beregne "Hvor mange solgte enheder var røde?"
Vi kan gøre det med denne formel:
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Rød")) |
"Hold fast! Hvad er der med det dobbelte minus -symbol der? " du siger. Kan du huske, hvordan jeg sagde, at vi kunne konvertere fra sandt/falsk til 1/0? Vi gør dette ved at tvinge computeren til at udføre en matematisk operation. I dette tilfælde siger vi "tag den negative værdi, og tag den negative igen". Når vi skriver det ud, ændrer vores array sig således:
| 123 | {True, True, False}{-1, -1, 0}{1, 1, 0} |
Så tilbage til den fulde SUMPRODUCT -formel, det kommer til at indlæse i vores arrays og derefter multiplicere, som dette
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0})= SUMPRODUCT ({100, 50, 0})= 150 |
Bemærk hvordan 3rd element blev et 0, fordi alt ganget med 0 bliver nul.
Flere kriterier
Vi kan indlæse op til 255 arrays i vores funktion, så vi kan helt sikkert indlæse flere kriterier. Lad os se på denne større tabel, hvor vi har tilføjet den solgte måned.
Hvis vi vil vide, hvor mange solgte varer der var røde og var i februar måned, kunne vi skrive vores formel som
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Rød"), -(C2: C4 = "Feb")) |
Computeren ville derefter evaluere vores arrays og formere sig på tværs. Vi har allerede dækket, hvordan True/False -arrays ændres til 1/0, så jeg kommer til at springe det trin over nu.
| 123 | = SUMPRODUCT ({100, 50, 10}, {1, 1, 0}, {0, 1, 1})= SUMPRODUCT ({0, 50, 0})= 50 |
Vi havde kun en række i vores eksempel, der matchede alle kriterier, men med rigtige data havde du muligvis flere rækker, som du havde brug for, blev tilføjet sammen.
Komplekse kriterier
Okay, indtil nu er du måske ikke imponeret, fordi alle vores eksempler kunne have været udført ved hjælp af andre funktioner som SUMIF eller COUNTIF. Nu skal vi gøre noget de andre funktioner ikke kan gøre. Tidligere havde vores månedskolonne de faktiske navne på måneder. Hvad hvis det i stedet havde datoer?
Vi kan ikke lave et SUMIF nu, for SUMIF kan ikke håndtere de kriterier, vi har brug for. SUMPRODUCT kan dog håndtere os med at manipulere arrayet og lave en dybere test. Vi har allerede manipuleret arrays, når vi har oversat sandt/falsk til 1/0. Vi kommer til at manipulere dette array med MONTH -funktionen. Her er den fulde formel, vi skal bruge
| 1 | = SUMPRODUCT (A2: A4, -(B2: B4 = "Rød"), -(MÅNED (C2: C4) = 2)) |
Lad os se på 3rd række nærmere. For det første vil vores formel udtrække månedstallet fra hver dato i C2: C4. Dette giver os {1, 2, 2}. Dernæst kontrollerer vi, om værdien er lig med 2. Nu ligner vores array {False, True, True}. Vi gør det dobbelte minus igen, og vi har {0, 1, 1}. Vi er nu tilbage på et lignende sted, som vi havde i eksempel 3, og vores formel vil kunne fortælle os, at der var 50 enheder solgt i februar, der var røde.
Dobbelt minus kontra multiplikation
Hvis du har set SUMPRODUCT -funktionen i brug før, har du muligvis set en lidt anden notation. I stedet for at bruge et dobbelt minus kan du skrive
| 1 | = SUMPRODUCT (A2: A4*(B2: B4 = "Rød")*(MÅNED (C2: C4) = 2)) |
Formlen fungerer stadig på samme måde, vi fortæller bare manuelt computeren, at vi vil multiplicere arraysne. SUMPRODUCT skulle alligevel gøre dette, så der er ingen ændring i, hvordan matematikken fungerer. Ved at udføre matematikoperationen konverteres vores Sandt/Falsk til 1/0 det samme. Så hvorfor forskellen?
For det meste betyder det ikke så meget, og det kommer til brugerens præference. Der er dog mindst et tilfælde, hvor multiplikation er nødvendig.
Når du bruger SUMPRODUCT, forventer computeren, at alle argumenterne (array1, array2 osv.) Har samme størrelse. Det betyder, at de har samme antal rækker eller kolonner. Du kan dog gøre det, der kendes som en todimensionel matrixberegning med SUMPRODUCT, som vi ser i næste eksempel. Når du gør det, er arraysne forskellige størrelser, så vi skal omgå det "alle samme størrelse" -kontrol.
To dimensioner
Alle de foregående eksempler havde vores arrays i samme retning. SUMPRODUCT kan håndtere ting, der går i to retninger, som vi ser i næste tabel.
Her er vores tabel med solgte enheder, men dataene omarrangeres, hvor kategorier går på tværs af toppen. Hvis vi vil finde ud af, hvor mange varer der var røde og i kategori A, kan vi skrive
| 1 | = SUMPRODUCT ((A2: A4 = "Rød")*(B1: C1 = "A")*B2: C4) |
Hvad sker der her ?? Det viser sig, at vi vil multiplicere i to forskellige retninger. Det er sværere at visualisere dette med bare en skrevet sætning, så vi har et par billeder, der kan hjælpe os. For det første vil vores række -kriterier (er det rødt?) Multiplicere på tværs af hver række i arrayet.
| 1 | = SUMPRODUCT ((A2: A4 = "RØD")*B2: C4) |
Dernæst vil søjlekriterierne (er det kategori A?) Formere sig ned for hver kolonne
| 1 | = SUMPRODUCT ((A2: A4 = "Rød")*(B1: C1 = "A")*B2: C4) |
Efter at begge disse kriterier har udført deres arbejde, er de eneste ikke-nuller tilbage 5 og 10. SUMPRODUCT vil derefter give os den samlede sum af 15 som vores svar.
Kan du huske, hvordan vi talte om, at arrays skulle have samme størrelse, medmindre du laver to dimensioner? Det var delvist korrekt. Ser igen på de arrays, vi brugte i vores formel. Det højde af to af vores arrays er den samme, og bredde af to af vores arrays er de samme. Så du skal stadig sørge for, at tingene kommer til at stemme korrekt, men du kan gøre det i forskellige dimensioner.
To dimensioner og kompleks
Mange gange præsenteres vi for data, der ikke er i det bedste layout, der er egnet til vores formler. Vi kunne prøve at omarrangere det manuelt, eller vi kan være klogere med vores formler. Lad os overveje følgende tabel.
Her har vi dataene for vores varer og salg blandet sammen for hver måned. Hvordan ville vi finde ud af, hvor mange varer Bob har solgt for hele året?
For at gøre dette bruger vi to ekstra funktioner: SØG og ISNUMBER. SEARCH -funktionen lader os lede efter vores søgeord "varer" i overskriftscellerne. Outputtet fra denne funktion går enten til et tal eller en fejl (hvis søgeordet ikke findes). Derefter bruger vi ISNUMBER til at konvertere at output i vores boolske værdier. Vores formel kommer til at se ud som nedenfor.
Du burde være temmelig fortrolig med det første array nu. Det vil skabe et output som {0, 1, 0, 1}. Det næste kriteriearray vi lige har talt om. Det vil oprette et tal for alle cellerne med "varer" i dem og en fejl for de andre {5, #N/A !, 5, #N/A!}. ISNUMBER konverterer derefter dette til boolsk {Sand, Falsk, Sand, Falsk}. Når vi så multiplicerer, bevarer det kun værdier fra den første og tredje kolonne. Når alle arrays multiplicerer mod hinanden, er de eneste tal uden nul, vi har, dem fremhævet her:
| 1 | = SUMPRODUCT ((A2: A5 = "Bob")*(ISNUMBER (SØG ("Elementer", B1: E1))*B2: E5)) |
SUMPRODUCT vil derefter tilføje dem alle sammen, og vi får vores endelige resultat på 29.
SUMPRODUCT Or
Mange situationer opstår, hvor vi gerne vil være i stand til at opsummere værdier, hvis vores kriteriekolonne har en værdi ELLER en anden værdi. Du kan opnå dette i SUMPRODUCT ved at tilføje to kriterierarrays mod hinanden.
I dette eksempel vil vi tilføje enheder, der sælges til både rød og blå.
Vores formel vil se sådan ud
| 1 | = SUMPRODUCT (A2: A7, (B2: B7 = "Rød")+(B2: B7 = "Blue")) |
Lad os se på matrixen med røde kriterier. Det vil producere en matrix, der ser sådan ud: {1, 1, 0, 0, 0, 0}. Det blå kriteriums array ser ud som {0, 0, 1, 0, 1, 0}. Når du tilføjer dem sammen, ser det nye array ud som {1, 1, 1, 0, 1, 0}. Vi kan se, hvordan de to arrays har blandet sig til et enkelt kriteries array. Funktionen vil derefter multiplicere det med vores første array, og vi får {100, 50, 10, 0, 75, 0}. Bemærk, at værdierne for Green er blevet nulstillet. Det sidste trin i SUMPRODUCT er at tilføje alle tallene sammen for at nå vores løsning på 235.
Et ord med forsigtighed her. Vær forsigtig med, når kriterierne ikke er gensidigt udelukkende. I vores eksempel kunne værdierne i kolonne B enten være rød eller blå, men vi vidste, at det aldrig kunne være begge dele. Overvej, om vi havde skrevet denne formel:
| 1 | = SUMPRODUCT (A2: A7, (A2: A7> = 50)+(B2: B7 = "Blue")) |
Vores hensigt er at finde blå varer, der blev solgt eller var i en mængde på mere end 50. Disse betingelser er dog ikke eksklusive, da en enkelt række både kunne være over 50 i kolonne A og være blå. Dette ville resultere i, at det første kriteries array ligner {1, 1, 0, 1, 1, 0}, og det andet kriteries array er {0, 0, 1, 0, 1, 0}. Ved at lægge dem sammen producerede {1, 1, 1, 1, 2, 0}. Kan du se, hvordan vi har en 2 derinde nu? Hvis det lades alene, ville SUMPRODUCT'en ende med at fordoble værdien i den række og ændre 75 til en 150, og vi ville få det forkerte resultat. For at korrigere dette foretager vi en ydre kriterietjek på vores array, sådan:
| 1 | = SUMPRODUCT (A2: A7, -((A2: A7> = 50)+(B2: B7 = "Blå")> 0)) |
Nu, efter at de to indre kriterier er blevet tilføjet, kontrollerer vi, om resultatet er større end 0. Dette slipper af med de 2, vi havde før, og i stedet har vi en matrix som {1, 1, 1 , 1, 1, 0} som giver det korrekte resultat.
SUMPRODUCT Præcis
De fleste funktioner i Excel er ikke store og små bogstaver, men nogle gange skal vi være i stand til at foretage et opslag med store og små bogstaver. Når det ønskede resultat er numerisk, kan vi opnå dette ved at bruge EXAKT inde i SUMPRODUCT -funktionen. Overvej følgende tabel:
Vi vil finde scoren for element "ABC123". Normalt sammenligner EXAKT -funktionen to emner og returnerer et boolsk output, der angiver, om de to elementer er Nemlig det samme. Men da vi er inde i et SUMPRODUCT, ved vores computer, at vi har at gøre med arrays og vil være i stand til at sammenligne et element med hvert element i en array. Vores formel vil se sådan ud
| 1 | = SUMPRODUCT (-PRÆCIS ("ABC123", A2: A5), B2: B5) |
Funktionen EXAKT kontrollerer derefter hvert element i A2: A5 for at se, om det matcher værdi og store og små bogstaver. Dette vil producere en matrix, der ligner {0, 1, 0, 0}. Når det multipliceres med B2: B5, bliver arrayet {0, 2, 0, 0}. Efter den sidste summering får vi vores løsning på 2.
SUMPRODUCT i Google Sheets
SUMPRODUCT -funktionen fungerer nøjagtig det samme i Google Sheets som i Excel:
SUMPRODUCT Eksempler i VBA
Du kan også bruge funktionen SUMPRODUCT i VBA. Type: application.worksheetfunction.sumproduct (array1, array2, array3)
Udførelse af følgende VBA -erklæringer
| 1 | Range ("B10") = Application.WorksheetFunction.SumProduct (Range ("A2: A7"), Range ("B2: B7")) |
vil frembringe følgende resultater
For funktionsargumenterne (array1 osv.) Kan du enten indtaste dem direkte i funktionen eller definere variabler, der skal bruges i stedet.
