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.

Slučování souborů s Power Query

Snad každý uživatel Excelu občas naráží na jednu věc, kterou tata aplikace prostě neumí dobře řešit, a to je slučování dat z více souborů. Příklad - mám v několika souborech vypsané odpracované hodiny více zaměstnanců, a chci je mít v jednom souboru. Pokud jsou ve všech souborech stejné sloupce, vypadá to jako triviální úkol. Ale není.

Existuje několik cest, jak toho dosáhnout, ale všechny mají svá negativa.

1. Je možné data prostě zkopírovat. Na jednorázové zkopírování pár souborů jistě nejšikovnější cesta, na desítky souborů nebo časté slučování aktualizovaných verzí stejných souborů nepoužitelné.
2. Je možné napsat a použít makro. To funguje, ale ne každému se chce kvůli tak banální věci vytvářet a používat relativně složitý kód VBA.
3. Je možné použít Power Query, vytvořit více dotazů tyto dotazy spojit. K tomu se možná dostaneme v nějakém pozdějším návodu.
4. Také je možné použít Power Query, načíst soubory z adresáře a importovat data. To je cesta popsaná v tomto návodu. Nevýhoda je, že tento postup aktuálně nefunguje pro slučování souborů xlsx (!), ale pouze pro csv. (k tomuto tématu více např. zde). 

Sloučení souborů z adresáře pomocí Power Query

Před začátkem tohoto postupu potřebujete mít nainstalovaný doplněk Power Query. Ten můžete stáhnout zde. Také si připravte alespoň dva soubory ve formátu csv, které budete chtít sloučit, které jsou v jednom adresáři, a které mají stejný počet souborů. Naše ukázkové soubory budou ze dvou souborů slučovat odpracované hodnoty dvou zaměstnanců.

Začneme tak, že v kartě Power Query vybereme From File a dále From Folder. 

Zobrazí se seznam všech souborů - v našem případě tedy jen dvou.

Klikneme na malé tlačítko vedle Content.

Tím se pod sebe sloučí tabulky z obou souborů. Klikneme na Apply and Close.

Vzniklá tabulka je aktivním propojením vstupních souborů. Kdykoliv se vstupní soubory změní a chceme tuto tabulku aktualizovat, stačí například kliknout pravým tlačítkem a vybrat Refresh.

Jedenáctá hádanka - letní cestovní

Řidič se chystá na dovolenou. Zjistil si, kde (na kolikátém kilometru) jsou po cestě benzinové pumpy a kolik na nich stojí benzín. Chce si naplánovat tankování tak, aby ho cesta vyšla co nejlevněji.

Plati, že:

• Řidič jede po jedné cestě a nemůže ji měnit
• Žádné jiné benzínky není možné využít
• Řidič nemusí stavět u všech benzínek
• Auto spotřebuje 7,5 litru benzínu na 100 km
• Do auta se vejde až 65 litrů benzínu
• Řidič je opatrný a nechce dojíždět k benzínce s prázdnou nádrží - vždy chce mít ještě nejméně 10 litrů rezervu
• Řidič vždy tankuje celé litry
• Před benzínkou na nultém kilometru může mít auto prázdnou nádrž

Otázka zní, u které benzínky natankovat kolik litrů benzínu. Nápověda - je možné se dostat na 15334 Kč celkové ceny, možná ještě méně.

Km cesty, kde je benzínka	Cena benzínu
0	34,00 Kč
493	39,10 Kč
872	36,70 Kč
1062	38,30 Kč
1505	41,90 Kč
1860	39,10 Kč
2134	42,00 Kč
2566	37,80 Kč
2697	38,50 Kč
3235	41,00 Kč
3705	35,00 Kč
3934	39,90 Kč
4164	41,00 Kč
4233	41,00 Kč
4359	40,50 Kč
4513	38,00 Kč
4700	35,20 Kč
4802	36,20 Kč
5037	39,70 Kč
5199	39,00 Kč

Ovládací prvek přepínač / radiobutton

Radiobutton znáte z  webových stránek. Používá se v situacích, kdy si uživatel má vybrat právě jednu možnost z několika.

Je to několik bílých koleček, kdy do jednoho z nich (ale vždy jen do jednoho) se kliknutím přidá černý puntík a tím se vybere požadovaná možnost.

Mimochodem - víte, jak vzniklo pojmenování "radiobutton"? Ve starých autorádiích (u nás hlavně pak později v kazeťácích) fungovala tlačítka tak, že když jste jedno zmáčkli, ostatní se uvolnila. Tak šla vybrat pouze jedna možnost - jako u radiobuttonu.

Příklad

Potřebuji spočítat cenu pro zákazníka. S tím, že někteří z nich mají nárok na slevu, někteří ne. Jde o to, aby se v šedém poli zobrazila sleva pouze u toho zákazníka, který bude mít radiobutton zaškrtnutý. U ostatních pak bude v šedém poli nula. Uznávám, že je to jednoduchý, až hloupý, příklad, ale snad to pro vysvětlení logiky nevadí.

Návod

Vyplním si podle obrázku sloupce A a B, přičemž A3 je rozdílem A1 a A2. Pak v kartě Vývojář (jak ji zobrazit je popsané zde) přes Vložit vložím dva radiobuttony.

Přes pravé tlačítko radibobuttonům upravím popisky, aby se jmenovaly nějak smysluplně.

A teď přijde to zajímavé. Kliknu pravým tlačítkem na jeden z radiobuttonů na Formát ovládacího prvku.

Pak v kartě Ovládací prvek, v řádku Propojení s buňkou, zaškrtnu nějakou zatím prázdnou buňku, a potvrdím tlačítkem OK.

Všimněte si, že když teď budete střídavě zaškrtávat radiobuttony, bude se v buňce, kterou jste vybrali, měnit jednička na dvojku a naopak. Je zajímavé, že stačilo propojení s buňkou vybrat u jednoho z radiobuttonů - druhý se přizpůsobí.
Tím jsou tedy radiobuttony samy o sobě hotové. My teď ale potřebujeme zajistit, aby se to, co je zaškrtnuté, promítlo do výpočtu slevy.
Do buňky B2, kde se má objevit případná sleva, napíšu tento vzoreček (za D1 dosaďte buňku, kterou jste vybrali jako propojenou s ovládacím prvkem).
=KDYŽ(D1=2;B1*10%;0)
Teď, když budete klikat na radiobuttony, bude se zákazníkovi buď zobrazovat nebo nezobrazovat cena. Povedlo se nám tak vytvořit jakousi jednoduchou kalkulačku.

Poznámky

• Pokud chcete, aby uživatel mohl vybrat i více možností než jednu, použijte zaškrtávátko / checkbox.
• Vložení více skupin radiobuttonů na stránku je trochu ošidné, protože jsme viděli, že přiřazení propojené buňky jednoho z nich se promítne do všech. Řešením je vložit další ovládací prvek, Skupinový rámeček, a radiobuttony rozdělit do rámečků. Pak budou stejnou propojenou buňku sdílet vždy jen radiobuttony jednoho rámečku.

Lync -- představení a základní návod

Microsoft Lync je aplikace, která navazuje na Windows Messenger. Jedná se o vcelku jednoduchý program, který umožňuje instant messaging (jako ICQ, Skype...), dále hlasové hovory (Skype) a pak sdílení plochy (TeamViewer).
Lync je produktem Microsoftu stejně jako Skype. Nejedná se tedy o konkurenci, ale o trochu jinak zaměřený produkt. Zatímco Skype je určen více pro individuální uživatele, Lync je určen pro firmy.
Ovládání Lyncu je intuitivní a podobné obdobným aplikacím, proto si zde popíšeme jen tři základní činnosti - tvorbu distribučních seznamů pro kontakty, sdílení plochy a předání ovládání počítače.

Tvorba seznamů pro kontakty

Pokud se nechcete v dlouhém seznamu kontaktů časem ztrácet, je vhodné kontakty zařazovat do skupin kontaktů. Aplikace Lync má přednastavené dvě skupiny kontaktů ihned po instalaci. Skupinu Oblíbené a skupinu Ostatní kontakty.

Ty nám ovšem nemusí stačit, proto si můžeme vytvořit vlastní skupiny kontaktů.

Vlastní skupinu nejrychleji vytvoříme tak, že na jednu ze stávajících skupin (např. skupinu Ostatní kontakty) klikneme pravým tlačítkem myši a z nabídky, která se zobrazí, vybereme možnost „Vytvořit novou skupinu“.

Po kliknutí se nová skupina umístí do seznamu skupin pod název „Nová skupina“ a můžeme ji ihned libovolně přejmenovat.

Skupinu pojmenujeme například „Kolegové“ a potvrdíme klávesou Enter. Tím je skupina připravena k dalšímu použití.

 

Sdílení plochy

Chceme-li s libovolným uživatelem sdílet svoji plochu, postupujeme následovně:

Vybereme uživatele,  se kterým chceme plochu sdílet a dvojklikem myší vyvoláme chatovací okno.

V chatovacím okně ve spodní části najedeme myší na ikonku obrazovky (čtvrtá zleva). Po najetí se nám zobrazí další možnosti. Z nabízených možností sdílení vybereme možnost „Plocha“

Po kliknutí  program Lync ještě upozorní, že uživatel uvidí vše, co se na vaší obrazovce děje. Toto hlášení musíte potvrdit stiskem tlačítka OK. Dodejme jen, že uživatel se bude moci pouze dívat, ne však počítač ovládat.

Ihned po potvrzení hlášení se Váš počítač přepne do režimu sdílení. To poznáte podle žlutého okraje, který se objeví  kolem celé obrazovky. V horní části obrazovky se také zobrazí panel, kterým můžeme sdílení dále ovládat.

Na horním panelu v jeho levé části se prozatím zobrazuje text :Připojování… . připojení totiž není automatické a druhý uživatel, kterému jsme sdílení povolili, jej musí potvrdit.

Druhý uživatel je na své obrazovce informován o tom, že mu někdo chce sdílet plochu. Vlevo dole se objeví modré okno, ve kterém uživatel potvrdí příjem sdílení plochy kliknutím na tlačítko přijmout.

Od této chvíle vidí uživatel vaší obrazovku tak, jak jí vidíte vy. Režim sdílení můžete kdykoliv ukončit. Stačí kliknout na horním panelu na tlačítko „Ukončit prezentaci“.

Předání řízení

Režim sdílení vaší plochy je možné rozšířit tak, aby jiný uživatel měl přímý přístup k vaší ploše a mohl ovládat Vás počítač. K tomu slouží tlačítko „předání řízení“ které najdeme na horním šedém panelu.

Po kliknutí se rozbalí menu s možností předat sdílení konkrétnímu účastníkovi.

Řízení nepředáváte úplně, ale budete jej mít s vybraným uživatelem společně, např tedy pohybovat kurzorem myši budete moci oba dva najednou.

Stále máte ale hlavní kontrolu nad počítačem vy, neboť režim řízení můžete kdykoliv ukončit kliknutím na tlačítko „předat řízení“ a dále na „Opět převzít řízení“.

V tomto okamžiku vás druhý uživatel může opět pouze sledovat.