Hoe de Excel SUMPRODUCT-functie te gebruiken in gegevensmodellen - dummies

De functie SOMPRODUCT wordt ook vermeld in de categorie wiskunde en trigonometrie van Excel-functies. Omdat SUMPRODUCT het primaire doel is om het somproduct te berekenen, weten de meeste mensen niet dat u het daadwerkelijk kunt gebruiken om waarden op te zoeken. In feite kunt u deze veelzijdige functie heel effectief gebruiken in de meeste datamodellen.

SUMPRODUCT basics

De functie SOMPRODUCT is ontworpen om waarden uit twee of meer reeksen gegevens te vermenigvuldigen en de resultaten vervolgens samen toe te voegen om de som van de producten te retourneren. Bekijk de volgende afbeelding om een ​​typisch scenario te bekijken waarin de SOMPRODUCT nuttig is.

SOMPRODUCT, voor de totale verkoop moet u de prijs vermenigvuldigen met "width =" 458 ">

Zonder de SOMPRODUCT wordt voor de totale verkoop de prijs per eenheid vermenigvuldigd en vervolgens de resultaten opgeteld.

U ziet een algemene analyse waarbij u de totale omzet voor de jaren 2011 en 2012 nodig heeft. Zoals u kunt zien, moet u om de totale verkoop voor elk jaar te krijgen eerst de prijs vermenigvuldigen met het aantal eenheden om het totaal voor elk jaar te krijgen. Regio. Vervolgens moet u die resultaten optellen om de totale verkoop voor elk jaar te krijgen.

Met de SUMPRODUCT-functie kunt u de tweestapsanalyse uitvoeren met slechts één formule. De volgende afbeelding toont dezelfde analyse met SUMPRODUCT-formules. In plaats van 11 formules te gebruiken, kunt u dezelfde analyse uitvoeren met slechts 3!

SUMPRODUCT-functie stelt u in staat dezelfde analyse uit te voeren met alleen "width =" 535 "> > met de functie SOMPRODUCT kunt u dezelfde analyse uitvoeren met slechts 3 formules in plaats van 11.

De syntaxis van de functie SOMPRODUCT is vrij eenvoudig:

SOMPRODUCT (

Array1 , Array2 , …) Array

: Array vertegenwoordigt een gegevensbereik. U kunt overal van 2 tot 255 arrays gebruiken in een SOMPRODUCT-formule. De arrays worden samen vermenigvuldigd en vervolgens toegevoegd. De enige hard-en-snel regel die je moet onthouden is dat alle matrices hetzelfde aantal waarden moeten hebben. Dat wil zeggen dat u de SOMPRODUCT niet kunt gebruiken als bereik X 10 waarden heeft en bereik Y 11 waarden heeft. Anders krijg je de #VALUE! fout.

Een draai aan de SOMPRODUCT-functie

Het interessante van de functie SOMPRODUCT is dat deze kan worden gebruikt om waarden weg te filteren. Bekijk de volgende afbeelding:

De functie SOMPRODUCT kan worden gebruikt om gegevens te filteren op basis van criteria. "Width =" 535 ">

De functie SOMPRODUCT kan worden gebruikt om gegevens te filteren op basis van criteria.

De formule in cel E12 trekt de som van de totale eenheden voor alleen de regio Noord. Ondertussen trekt cel E13 de gelogde eenheden voor de regio Noord in het jaar 2011.

Om te begrijpen hoe dit werkt, bekijk de formule in cel E12. Die formule leest SOMPRODUCT ((C3: C10 = "North") * (E3: E10)).

In Excel, TRUE evalueert naar 1 en FALSE evalueert naar 0. Elke waarde in kolom C die gelijk is aan North evalueert naar TRUE of 1. Als de waarde niet North is, wordt deze geëvalueerd als FALSE of 0. Het deel van de formule dat leest (C3: C10 = "Noorden") geeft een opsomming van elke waarde in het bereik C3: C10, waarbij aan iedere waarde een 1 of 0 wordt toegekend. Intern vertaalt de SUMPRODUCT-formule naar

(1 * E3) + (0 * E4) + (0 * E5) + (0 * E6) + (1 * E7) + (0 * E8) + (0 * E9) + (0 * E10).

Dit geeft je het antwoord van 1628 omdat

(1 * 751) + (0 * 483) + (0 * 789) + (0 * 932) + (1 * 877) + (0 * 162) + (0 * 258) + (0 * 517)

is gelijk aan 1628.

SUMPRODUCT-formules toepassen in een gegevensmodel

Zoals altijd in Excel, hoeft u de criteria in uw formules niet hard te coderen. In plaats van 'Noord' expliciet in de SOMPRODUCT-formule te gebruiken, kunt u verwijzen naar een cel die de filterwaarde bevat. Je kunt je voorstellen dat cel A3 het woord North bevat, in welk geval je kunt gebruiken (C3: C10 = A3) in plaats van (C3: C10 = "North"). Op deze manier kunt u uw filtercriteria dynamisch wijzigen en blijft uw formule bij.

De volgende afbeelding illustreert hoe u dit concept kunt gebruiken om gegevens naar een verzameltabel te halen op basis van meerdere criteria. Merk op dat elk van de SUMPRODUCT-formules die hier worden getoond verwijst naar cellen B3 en C3 om te filteren op account en productlijn. Nogmaals, u kunt vervolgkeuzelijsten voor gegevensvalidatie toevoegen aan cellen B3 en C3, zodat u eenvoudig criteria kunt wijzigen.

SUMPRODUCT-functie kan worden gebruikt om samengevatte getallen te trekken uit de d "width =" 535 ">

De SUMPRODUCT-functie kan worden gebruikt om samengevatte getallen uit de gegevenslaag naar stagingtabellen te trekken.