Jak používat funkci Excel SUMPRODUCT v datových modelech

Funkce SUMPRODUCT je ve skutečnosti uvedena v kategorii matematických a trigonometrických funkcí aplikace Excel. Protože primárním účelem SUMPRODUCT je vypočítat součtový součin, většina lidí neví, že jej můžete skutečně použít k vyhledání hodnot. Ve skutečnosti můžete tuto všestrannou funkci poměrně efektivně využít ve většině datových modelů.

Základy SUMPRODUCT

Funkce SUMPRODUCT je navržena tak, aby vynásobila hodnoty ze dvou nebo více rozsahů dat a poté výsledky sečetla, aby se vrátil součet součinů. Podívejte se na následující obrázek, abyste viděli typický scénář, ve kterém je SUMPRODUCT užitečný.

SUMPRODUCT, získání celkových prodejů zahrnuje vynásobení ceny” width=”458″/>

Bez SUMPRODUCTU vyžaduje získání celkových prodejů vynásobení ceny jednotkami a následné sečtení výsledků.

Uvidíte běžnou analýzu, ve které potřebujete celkové prodeje za roky 2011 a 2012. Jak můžete vidět, chcete-li získat celkové prodeje za každý rok, musíte nejprve vynásobit cenu počtem jednotek, abyste získali součet za každý Kraj. Poté musíte tyto výsledky sečíst, abyste získali celkové prodeje za každý rok.

Pomocí funkce SUMPRODUCT můžete provést dvoukrokovou analýzu pouze pomocí jednoho vzorce. Následující obrázek ukazuje stejnou analýzu se vzorci SUMPRODUCT. Namísto použití 11 vzorců můžete provést stejnou analýzu pouze se 3!

Funkce SUMPRODUCT vám umožňuje provádět stejnou analýzu pouze s “ width=”535″/>

Funkce SUMPRODUCT umožňuje provádět stejnou analýzu pouze s 3 vzorci namísto 11.

Syntaxe funkce SUMPRODUCT je poměrně jednoduchá:

SUMPRODUCT( Array1 , Array2 , …)

Pole : Pole představuje rozsah dat. Ve vzorci SUMPRODUCT můžete použít kdekoli od 2 do 255 polí. Pole se vynásobí dohromady a poté se sečtou. Jediné pevné pravidlo, které si musíte zapamatovat, je, že všechna pole musí mít stejný počet hodnot. To znamená, že nemůžete použít SUMPRODUCT, pokud má rozsah X 10 hodnot a rozsah Y má 11 hodnot. V opačném případě získáte #HODNOTU! chyba.

Zvrat k funkci SUMPRODUCT

Zajímavostí funkce SUMPRODUCT je, že pomocí ní lze odfiltrovat hodnoty. Podívejte se na následující obrázek:

Funkci SUMPRODUCT lze použít k filtrování dat na základě kritérií.“ width=”535″/>

Funkci SUMPRODUCT lze použít k filtrování dat na základě kritérií.

Vzorec v buňce E12 získává součet celkových jednotek pouze pro oblast Sever. Mezitím buňka E13 stahuje jednotky přihlášené pro region Sever v roce 2011.

Chcete-li pochopit, jak to funguje, podívejte se na vzorec v buňce E12. Tento vzorec zní SUMPRODUCT((C3:C10=“North“)*(E3:E10)).

V Excelu se TRUE vyhodnotí jako 1 a FALSE se vyhodnotí jako 0. Každá hodnota ve sloupci C, která se rovná severu, se vyhodnotí jako TRUE nebo 1. Pokud hodnota není sever, vyhodnotí se jako NEPRAVDA nebo 0. Část vzorce, která čte (C3 :C10=“North“) vyčísluje každou hodnotu v rozsahu C3:C10, přičemž každé hodnotě přiřadí 1 nebo 0. Potom se interně vzorec SUMPRODUCT převede na

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

To vám dává odpověď 1628, protože

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

rovná se 1628.

Použití vzorců SUMPRODUCT v datovém modelu

Jako vždy v Excelu nemusíte kritéria ve vzorcích pevně zakódovat. Namísto explicitního použití „Sever“ ve vzorci SUMPRODUCT můžete odkazovat na buňku, která obsahuje hodnotu filtru. Můžete si představit, že buňka A3 obsahuje slovo Sever, v takovém případě můžete místo (C3:C10=“Sever“) použít (C3:C10=A3). Tímto způsobem můžete dynamicky měnit kritéria filtru a váš vzorec bude držet krok.

Následující obrázek ukazuje, jak můžete tento koncept použít k načtení dat do pracovní tabulky na základě více kritérií. Všimněte si, že každý ze zde zobrazených vzorců SUMPRODUCT odkazuje na buňky B3 a C3 pro filtrování podle účtu a produktové řady. Opět můžete do buněk B3 a C3 přidat rozevírací seznamy ověření dat, což vám umožní snadno měnit kritéria.

Funkci SUMPRODUCT lze použít k získání souhrnných čísel z d” width=”535″/>

Funkci SUMPRODUCT lze použít k vytažení souhrnných čísel z datové vrstvy do pracovních tabulek.