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.

Záludnost s desetinnou čárkou a tečkou

Představte si, že máte v buňkách tato čísla:

• 100,4 
• 1,004 
• 1,04

a jste v Excelu, který je ve standardních českých Windows, a tedy používá desetinné čárky (ne desetinné tečky).
Pak chcete tento soubor nahrát do nějakého systému, který vyžaduje csv s desetinnými tečkami.
Takže dáte Ctrl F (najít a nahradit) a nahradíte všechny čárky tečkami.
Takto bude vypadat výsledek:

• 100.4 
• 1.004 
• 1.4

První dvě čísla se změní na text, ale zůstanou v pořádku. Prostě se čárka nahradí tečkou.

Trochu děsívě se zachová poslední číslo - odstraní jednu nulu a z 1,04 udělá 1,4(!!!)

Nechápu proč, týká se to jen čísel s jednou nulou vpravo od desetinné čárky. 

Tohohle problému se snadno vyvarujete tím, že přepnete celé Windows z čárek na tečky (tady je návod pro změnu čárek a středníků jako oddělovačů - což se dělá prakticky stejně), ale problém je, že když o této chybě nevíte, nemáte téměř šanci ji ve velkých souborech dat odhalit - protože se mění jen sem tam nějaké číslo a jen o málo.
Uvedený problém mám vyzkoušený v Excelu verze 2010 CZ.

Řezný plán v Excelu

Vytvoření řezného plánu je docela zajímavá logická úloha. Na nedávné konzultaci jsme ji řešili s jedním zákazníkem, a vzešlo z toho jednoduché, nicméně vcelku funkční řešení, které by byla škoda nepublikovat.

Co je to řezný plán

Představte si, že máte tyče (dráty, prkna...) o určité délce (třeba 600 cm). A potřebujete z nich nařezat určité kusy (např. 2 x 230 cm, k tomu 1 x 30 cm, k tomu...) Pak je otázka, jak kusy rozvrhnout na jednotlivé tyče tak, abyste mohli koupit co nejméně tyčí. Jinými slovy aby vám toho po nařezání zbylo co nejméně. Je-li tyčí a kusů málo, je to hračka. Je-li jich hodně, je to běžnými výpočty prakticky neřešitelné.

Jak řešit v Excelu

Soubor ke stažení je zde.

Pro řešení použijeme doplněk Řešitel / Solver, ale ne úplně typickým způsobem.

V tomto návodu nechci vysvětlovat princip Řešitele, takže jen ve zkratce.

Příprava tabulky

1) Zadám délky tyčí (světle zelená oblast)

2) Zadám, kolik a jak dlouhých kusů chci nařezat (žlutá oblast)

3) V šedých buňkách bude řešitel dosazovat počty skutečně nařezaných kusů z určité tyče

Na ostatní výpočty se podívejte do vzorečků přímo v souboru - není tam nic složitého, méně známá je snad jen funkce SUMPRODUCT.

Optimalizuji (v tomto případě minimalizuji) buňku A17 - počet skutečně spotřebovaných (nebo přinejmenším načatých) tyčí.

(Obrázek můžete kliknutím zvětšit)

Nastavení řešitele

Takto nastavím řešitele:

• Podmínky říkají, že řádek 13 a 14 obsahují stejné hodnoty (nařezám tolik kusů, kolik jsem chtěl) a sloupec H musí být stejný nebo menší než sloupc A (součet délek všech kusů nařezaných ze jedné tyče nemůže být delší než délka tyče). Navíc počty nařezaných kusů musí být, logicky, celá čísla.
• Vybral jsem model Evolutionary (simplexovou tabulku nemohu použít protože se nejedná o lineární programování).

Výsledek

Spustím Řešitele, ten chvilku přemýšlí, a pak vyhodí třeba toto řešení:

Budou použité čtyři tyče - ty, u kterých je jednička v pravém sloupci (u mě, kde jsou všechny tyče stejně dlouhé, je samozřejmě jedno, kterou konkrétní použiji - jde jen o počet).

V šedé oblasti pak jsou počty konkrétních kusů, které z dané tyče nařežu.

Pokud máte nějaké robustnější excelové řešení řezného plánu, budu rád, když o něm napíšete do diskuse.

Použitý soubor ke stažení je zde.