Ako používať funkciu Excel SUMPRODUCT v dátových modeloch

Funkcia SUMPRODUCT je v skutočnosti uvedená v kategórii matematických a trigonometrických funkcií Excelu. Pretože primárnym účelom SUMPRODUCT je vypočítať súčet súčinu, väčšina ľudí nevie, že ho môžete skutočne použiť na vyhľadávanie hodnôt. V skutočnosti môžete túto všestrannú funkciu pomerne efektívne využiť vo väčšine dátových modelov.

Základy SUMPRODUCT

Funkcia SUMPRODUCT je navrhnutá tak, aby vynásobila hodnoty z dvoch alebo viacerých rozsahov údajov a potom sčítala výsledky, aby sa vrátil súčet produktov. Pozrite sa na nasledujúci obrázok, aby ste videli typický scenár, v ktorom je SUMPRODUCT užitočný.

SUMPRODUCT, získanie celkového predaja zahŕňa vynásobenie ceny” width=”458″/>

Bez SUMPRODUCT, získanie celkového predaja zahŕňa vynásobenie ceny jednotkami a následné sčítanie výsledkov.

Vidíte bežnú analýzu, v ktorej potrebujete celkový predaj za roky 2011 a 2012. Ako vidíte, ak chcete získať celkový predaj za každý rok, musíte najprv vynásobiť cenu počtom jednotiek, aby ste získali súčet za každý región. Potom musíte tieto výsledky sčítať, aby ste získali celkový predaj za každý rok.

Pomocou funkcie SUMPRODUCT môžete vykonať dvojstupňovú analýzu len s jedným vzorcom. Nasledujúci obrázok znázorňuje rovnakú analýzu so vzorcami SUMPRODUCT. Namiesto použitia 11 vzorcov môžete rovnakú analýzu vykonať iba s 3!

Funkcia SUMPRODUCT vám umožňuje vykonať rovnakú analýzu len s ”šírkou=”535″/>

Funkcia SUMPRODUCT vám umožňuje vykonávať rovnakú analýzu len s 3 vzorcami namiesto 11.

Syntax funkcie SUMPRODUCT je pomerne jednoduchá:

SUMPRODUCT ( Pole1 , Pole2 , …)

Pole : Pole predstavuje rozsah údajov. Vo vzorci SUMPRODUCT môžete použiť kdekoľvek od 2 do 255 polí. Polia sa vynásobia a potom sa sčítajú. Jediné pevné pravidlo, ktoré si musíte zapamätať, je, že všetky polia musia mať rovnaký počet hodnôt. To znamená, že nemôžete použiť SUMPRODUCT, ak rozsah X má 10 hodnôt a rozsah Y má 11 hodnôt. V opačnom prípade získate #HODNOTU! chyba.

Zvrat k funkcii SUMPRODUCT

Zaujímavosťou funkcie SUMPRODUCT je, že pomocou nej možno odfiltrovať hodnoty. Pozrite sa na nasledujúci obrázok:

Funkciu SUMPRODUCT je možné použiť na filtrovanie údajov na základe kritérií. width=”535″/>

Funkciu SUMPRODUCT je možné použiť na filtrovanie údajov na základe kritérií.

Vzorec v bunke E12 získava súčet celkových jednotiek len pre oblasť Sever. Bunka E13 medzitým sťahuje jednotky zaznamenané pre región Sever v roku 2011.

Ak chcete pochopiť, ako to funguje, pozrite sa na vzorec v bunke E12. Tento vzorec znie SUMPRODUCT((C3:C10=“Sever“)*(E3:E10)).

V Exceli sa TRUE vyhodnotí ako 1 a FALSE sa vyhodnotí ako 0. Každá hodnota v stĺpci C, ktorá sa rovná Sever, sa vyhodnotí ako TRUE alebo 1. Ak hodnota nie je Sever, vyhodnotí sa ako NEPRAVDA alebo 0. Časť vzorca, ktorá znie (C3 :C10=“North“) vymenúva každú hodnotu v rozsahu C3:C10, pričom každej hodnote priraďuje 1 alebo 0. Potom sa interne vzorec SUMPRODUCT preloží do

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

To vám dáva odpoveď 1628, pretože

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

rovná sa 1628.

Aplikácia vzorcov SUMPRODUCT v dátovom modeli

Ako vždy v Exceli, kritériá vo vzorcoch nemusíte pevne zadávať. Namiesto explicitného použitia „Sever“ vo vzorci SUMPRODUCT môžete odkazovať na bunku, ktorá obsahuje hodnotu filtra. Môžete si predstaviť, že bunka A3 obsahuje slovo Sever, v takom prípade môžete použiť (C3:C10=A3) namiesto (C3:C10=“Sever“). Týmto spôsobom môžete dynamicky meniť kritériá filtrovania a váš vzorec bude držať krok.

Nasledujúci obrázok ukazuje, ako môžete použiť tento koncept na stiahnutie údajov do prípravnej tabuľky na základe viacerých kritérií. Všimnite si, že každý z tu zobrazených vzorcov SUMPRODUCT odkazuje na bunky B3 a C3 na filtrovanie podľa účtu a produktového radu. Opäť môžete do buniek B3 a C3 pridať rozbaľovacie zoznamy overenia údajov, čo vám umožní jednoducho meniť kritériá.

Funkciu SUMPRODUCT je možné použiť na získanie súhrnných čísel z d” width=”535″/>

Funkciu SUMPRODUCT možno použiť na získanie súhrnných čísel z dátovej vrstvy do pracovných tabuliek.