Databázové funkce v Excelu - DSUMA / DSUM

V tomto článku si představíme funkci DSUMA / v anglických verzích DSUM. Jedná se o jednu z několika tzv. databázových funkcí, které fungují podobně. Jedná se o tyto funkce:

• DPRŮMĚR - Vrátí průměr vybraných položek databáze. 
• DPOČET - Spočítá buňky databáze obsahující čísla. 
• DPOČET2 - Spočítá buňky databáze, které nejsou prázdné. 
• DZÍSKAT - Extrahuje z databáze jeden záznam splňující zadaná kritéria. 
• DMAX - Vrátí maximální hodnotu z vybraných položek databáze. 
• DMIN - Vrátí minimální hodnotu z vybraných položek databáze. 
• DSOUČIN - Vynásobí hodnoty určitého pole záznamů v databázi, které splňují daná kritéria. DSMDOCH.VÝBĚR - Odhadne směrodatnou odchylku výběru vybraných položek databáze. 
• DSMODCH - Vypočte směrodatnou odchylku základního souboru vybraných položek databáze. 
• DSUMA - Sečte čísla v poli (sloupci) záznamů databáze, které splňují zadaná kritéria. 
• DVAR.VÝBĚR - Odhadne rozptyl výběru vybraných položek databáze. 
• DVAR - Vypočte rozptyl základního souboru vybraných položek databáze.

Příklad

Typické použití uvedených funkcí si ukážeme na DSUMA / DSUM.
Mám tabulku s několika auty a chci určit, kolik dohromady stojí Fiaty a Citroeny. (Od pohledu je už teď zřejmé, že je to 156 000).

Návod

Musíme si připravit tzv. kriteriální tabulku. Ta vyjadřuje, kterých řádků se má početní operace (v našem případě obyčejné sčítání) týkat. Jinými slovy cenu kterých aut budeme chtít sečíst.
V našem případě bude tabulka vypadat takto:

Záhlaví "Značka" je uvedené proto, že budeme filtrovat podle značky auta. Slova "Fiat" a "Citroen" logicky zastupují hodnoty, které se mají vyfiltrovat (jejichž cena se má posčítat).
Teď vložíme nebo zapíšeme funkci a sice takto:
=DSUM(A1:E10;"Cena";J1:J3)
resp. v české verzi:
=DSUMA(A1:E10;"Cena";J1:J3)

• A1:E10 proto, že v této oblasti je původní tabulka
• "Cena" proto, že právě součet cen je to, o co nám jde. Šlo by nahradit odkazem na buňku, v našem případě C1.
• J1:13 proto, že v této oblasti se nachází tabulka kritérií

Výsledek pak vypadá takto:

U oblasti si filtrovacími kritérii si uvědomte, že jejich logiku již možná znáte z používání rozšířených filtrů - je to stejný princip.

Funkce TABULKA(TABELOVAT) / TABLE

Funkce TABULKA(v některých českých verzích také TABELOVAT, anglicky TABLE) je docela specifická, například v tom, že dává smysl použít ji jen jako maticovou funkci .Nebo tím, že obecně neprovádí žádný výpočet, ale přiřazuje výsledky výpočtu k většímu množství čísel, a tím umožňuje modelovat různé scénáře. Nebo jinými slovy sledovat dopad změny jedné buňky nebo dvou buněk na skupinu hodnot. To zní dost nesrozumitelně a lépe to popsat neumím, tak takže použijeme příklad.

Příklad

Naše firma má možnost oslovit zákazníky s nabídkou nového produktu. Toto oslovení bude něco stát, a zvažujeme, jestli se do něj pustit.
Dopředu víme (modrá pole):

• Kolik zákazníků oslovíme (1500)
• Kolik toto oslovení celkově bude stát (200000)

Zatím přesně nevíme (oranžová pole):

• Kolik zákazníků z oslovených si výrobek koupí (pro začátek vyplníme třeba 20%)
• Kolik přesně na jednom výrobku vyděláme (pro začátek vyplníme třeba 500)

Výsledkem má být tabulka, kde budou ve sloupcích různé varianty zisku, v řádcích různé varianty procenta úspěšných oslovení, a v buňkách relevantní hodnoty výsledného zisku pro danou kombinaci. Zatím je ale prázdná.

Návod

Nejprve do levého rohu (A11) zapíšeme vzorec, od kterého se má výpočet odvíjet.
Výnosy z akce se budou počítat jako počet oslovených zákazníků * úspěšnost * zisk z jednoho prodaného výrobku, náklady jsou paušálně dané.
V našem případě je tedy vzorec:
=A2*A4*A6-A8

Vidíme nějaký výsledek, ale o ten nám teď nejde.
Teď přichází chvíle pro funkci Tabulka.
Nejprve označíme celou tabulku, a pak jdeme na Data / Citlivostní analýza / Tabulka dat.

Ve Vstupní buňce řádku nastavujeme, která buňka na vstupu se mění podle záhlaví sloupce. V našem případě je to buňka A6, protože v ní je odhad zisku na jeden výrobek, jehož různé možnosti jsou zapsané v záhlaví sloupců.
Do Vstupní buňky sloupce pak nastavíme buňku A4. V té je odhadované procento úspěchů při oslovování, jehož různé hodnoty jsou v záhlavích řádků.

Odklepneme a je hotovo.
V tabulce teď máme různé hodnoty pro různé kombinace "žlutých" vstupů. Všimněte si, že v tabulce se objevila maticová funkce Tabulka / Table

Pro přehlednost ještě můžeme použít podmíněné formátování pomocí škál a takto vypadá výsledek:

Z výsledku poznáme třeba to, že aby akce byla zisková, musí být při úspěšnosti oslovení 20% zisk na zákazníka alespoň 700, při úspěšnosti 30% pak stačí 500. Při úspěšnosti 40% a více je pak akce zisková vždy.
Poznámky:

• Všimněte si, že výsledky v tabulce se nemění v případě, že přepisujete hodnoty ve vstupních "žlutých" buňkách. Ty hodnoty můžete klidně smazat - jsou tam víceméně jen k tomu, aby se lépe zapisoval vzorec.
• Funkci Table můžeme většinou docela šikovně nahradit šikovným použitím absolutních nebo smíšených odkazů. Je jen na vás, co si vyberete - jestli skousnete raději složitější smíšené vzorce nebo maticový vzorec. Do buňky B12 bychom v takovém případě zapsali =$A$2*$A12*B$11-$A$8 a poroztahovali.

Jak zkřížit Excel s jazykem R

Jazyk R je programovací jazyk pro statistickou analýzu dat. Tento blog se ale týká Excelu, proto nás v souvislosti s jazykem R bude zajímat jen doplněk, který umožňuje docela šikovně využívat R pro analýzu dat uložených v Excelu. Doplněk se jmenuje RExcel (což je vzhledem k názvům obou zainteresovaných programů poměrně logický název...). Jde o to, že R je v některých oblastech silnější analytický nástroj než Excel, ale Excel zase umožňuje pohodlnější práci s daty. Tak proč si nevzít to lepší z obou programů.
Použití si ukážeme na modelovém příkladu základní lineární regresní analýzy. Tu samozřejmě umíme udělat přímo v Excelu, ale teď si chceme ukázat jen samotné propojení Excelu s R.

Instalace

Než začnu pracovat s RExcelem, musím nainstalovat:

• R (http://cran.r-project.org/bin/windows/base/)
• RExcel (http://rcom.univie.ac.at/download.html#RExcel)

Po instalaci pak v Excelu pod Soubor / Možnosti / Doplňky ve spodní části rámečku ještě zaškrtnout nejprve "Přejít" a pak zaškrtnout RExcel.

Tím se stanou dvě věci. Zaprvé přibude nová záložka Add-inn s jediným tlačítkem RExcel, zadruhé přibude několik položek do menu pravého tlačítka (což se ale projevuje jen při připojeném R, viz dále).

Ukázka lineární regrese

Budeme zkoumat, kolik zmrzliny se prodává ve stánku se zmrzlinou, v závislosti na venkovní teplotě. Dá se totiž očekávat, že čím větší teplo, tím více se zmrzliny prodá.
Výsledkem má být graf a dvojice koeficientů lineární závislosti. Fakt, že závislost vůbec lineární být nemusí, tentokrát taktně přejdeme.

Takto vypadají naše data:

Nejprve se připojíme k R stiskem tlačítka Connect R.

Pak načteme oba datové sloupce. Nejprve označíme hodnoty s teplotami a přes pravé tlačítko vybereme Put R Var.

V následujícím dialogu buď můžeme napsat název této skupiny dat, nebo jej přes "Get from cell" dotáhnout z buňky A1.

Teď úplně stejně přetáhneme data o prodejích, a to do proměnné pojmenované 

Prodana_zmrzlina_kg.

Teď jsou tedy oba sloupce uložené ve vektorech jazyka R.

Můžeme to ověřit třeba tak, že v R napíšeme názvy proměnných a vidíme, že opravdu obsahují, co mají.

> Max_denni_teplota 

 [1] 23 25 26 27 20 28 30 21 14 12

> Prodana_zmrzlina_kg

 [1] 32 35 30 33 28 34 27 20  4  6

Teď chceme vidět graf závislosti. V R zapíšeme:

> plot( Prodana_zmrzlina_kg,Max_denni_teplota)
a tím vytvoříme graf:

Z obou sloupců vytvoříme lineární model takto:
> Moje_regrese=lm(Prodana_zmrzlina_kg~Max_denni_teplota)
Tento model pak zavoláme:
> Moje_regrese
a zobrazí se výsledek:
Call:
lm(formula = Prodana_zmrzlina_kg ~ Max_denni_teplota)
Coefficients:
      (Intercept)  Max_denni_teplota  
          -12.571              1.658  
Tedy rovnice lineární regrese je y = 1,658 * x - 12,571. Jinými slovy když vynásobím teplotu číslem 1,658 a odečtu 12,571, dostanu odhad prodaného množství pro tento den.
Přetahování dat mezi Excelem a R je samozřejmě oboustranné, příkazy s "Put" přesouvají data z Excelu do R a naopak příkazy "Get" znamená přesun z R do Excelu.