úterý 31. prosince 2013

Power Pivot - moje nové stránky zabývající se Excelem

Power Pivot se tváří jako obyčejný doplněk Excelu. Ve skutečnosti se ale jedná o velmi zajímavou technologii Business Intelligence.
Osobně Power Pivotu předpovídám slibnou budoucnost. Proto vytvářím nový web, zaměřený pouze na něj.
Některé kapitoly jsou zatím ve stavu "rozpracované", některé kapitoly jsou zatím ve stavu "ještě ani ne rozpracované". Nicméně i tak to je jeden z mála souvislých textů / návodů týkajících se Power Pivotu a myslím, že by to pro Vás jako pro čtenáře webu www.excelentnitriky.com mohlo být inspirativní čtení.
Předem díky za zpětnou vazbu.
Jo a také přeji pěkný příští rok!




sobota 28. prosince 2013

Řazení čísel pomocí funkce

Příklad

Potřebuji seřadit čísla v tabulce. Nechci ale (nebo nemohu) to udělat řazením - potřebuji použít funkci.

Návod

Připravím si někde sloupeček, kde budou čísla od jedničky do tolika, kolik je čísel. Pak do vedlejších sloupečku zapíšu funkci LARGE (řazení od největšího), resp. SMALL (řazení od nejmenšího).
Tato funkce bude mít v prvním parametru oblast, kde jsou původní neseřazená čísla, a ve druhém číslo z vedlejšího sloupce. Toto číslo vyjadřuje, kolikáté největší (nejmenší) číslo se právě na této pozici má zobrazit.


Pokud bych se chtěl obejít bez pomocného sloupečku, mohl by zápis funkce vypadat např. takto:
=LARGE(A:A;ŘÁDEK()-1)
V následujícím příkladu je v praxi použita kombinace funkce SMALL a SVYHLEDAT pro seřazení jmen závodníků podle času, kterého dosáhli:
Zápis funkce pak může vypadat např. takto:
=SVYHLEDAT(SMALL($A$2:$A$7;ŘÁDEK()-1);A:B;2;0)

čtvrtek 7. listopadu 2013

Scoring v Excelu

Funkce FORECAST

Do článku o funkcích pro regresní analýzu jsem doplnil popis funkce FORECAST. Funkce FORECAST slouží k tomu, aby se, na základě lineární regresní závislosti, dopočetla hodnota závislé proměnné (y) na základě hodnoty nezávislé proměnné (x).
Funkce FORECAST má syntaxi FORECAST(hodnota závislé proměnné;oblast s hodnotami x;, oblast s hodnotami y).
Jinými slovy. Dejme tomu, že řeším závislost počtu prodaných kopečků zmrzliny na průměrné denní teplotě. Mám údaje o tom, jak se při kterých teplotách zmrzlina prodávala dříve, a teď zjišťuji, kolik kopečků se prodá při denní teplotě 22 stupňů.
Pak je nezávislou proměnnou teplota a závislou počet kopečků a FORECAST se zapíše takto:

  • =FORECAST(22;oblast s údaji o prodaných kopečcích z minulosti;oblast s údaji o teplotě z minulosti)

Více v článku.

neděle 3. listopadu 2013

Data mining - vysledování závislostí - doplňování dat do tabulky podle vzoru

Dohledání hodnot na základě části textu

Příklad

Představte si, že potřebujete propojit dvě tabulky funkcí SVYHLEDAT (VLOOKUP). Neznáte ale přesné hodnoty - znáte jen části textů, které jsou v dohledávané tabulce. Asi takto:

Řešení

Řešením je kombinace několika funkcí, které dají dohromady tento vzorec:
=INDEX(F:F;POZVYHLEDAT(CONCATENATE("*";A3;"*");E:E;0);0)

  • Funkce CONCATENATE obalí hodnotu z buňky A3 do hvězdiček. To proto, že hvězdička nahrazuje jakýkoliv symbol nebo skupinu symbolů. 
  • Funkce POZVYHLEDAT (MATCH), která ji obaluje, tedy nebude hledat konkrétní hodnotu z buňky A3, ale cokoliv, co hodnotu z A3 obsahuje.
  • V tomto případě by fungoval i jednodušší zápis =SVYHLEDAT(A:A;E:F;2;0). Jeho fungování by ale bylo, jak vyplývá z logiky funkce SVYHLEDAT, omezené jen na situaci, kdy tabulka, ze které čerpám, má hodnoty, ze kterých se páruje, v prvním sloupci.
  • Kombinace funkcí INDEX a POZVYHLEDAT je popsaná tady.

Takto vypadá výsledek:


sobota 2. listopadu 2013

Mapy v Power View

Jako celkem otrlého uživatele Excelu mě jen tak nějaká nová funkce nezaskočí. Co mě ale nadchlo hodně, je způsob prezentace dat v doplňku Power View.
Představte si, že máte jednoduchou tabulku, například s tržbami. Tabulka vypadá takto:
 
S pomocí Power View z ní můžete během pár vteřin udělat interaktivní mapu, ve které uvidíte, jak se kde které produkty sledované firmy prodávaly:

Návod na tvorbu mapových grafů čtěte zde

neděle 20. října 2013

Změna licencování doplňku PowerPivot

Před časem jsem si v jednom příspěvku postěžoval na to, že PowerPivot je v rámci Office 2013 obsažen pouze v nejdražších verzích. 
Nyní musím toto tvrzení trochu upravit. Microsoft od minulého týdne uvolnil PowerPivot i pro samostatně (bez zbytku Office) prodávaný Excel. Takže pokud PowerPivot chcete, nemusíte si kupovat nejdražší verzi celých Office, ale stačí koupit si levné (případně žádné) Office a k nim Excel.
Je to trochu posun, nicméně stále mi vadí že:
  • Na stránkách Microsoftu není nikde (nebo alespoň já jsem to nenašel) srozumitelně uvedené, které verze Office PowerPivot obsahují. A že těch verzí je hodně a licenční politika Microsoftu je pěkně zamotaná. Jediným zdrojem informací jsou tedy blogy a fóra. 
  • Samostatný Excel stojí pro USA 100 USD (Amazon) a pro ČR (Microsoft Store) 3399 Kč, tedy skoro jednou tolik. WTF?

čtvrtek 10. října 2013

Publikace interaktivních tabulek na webu prostřednictvím SkyDrive

Příklad

Potřebuji dostat na web interaktivní tabulku. V tomto případě tabulku pro výpočet BMI indexu. Tabulku, do které si každý uživatel může zapsat svoji výšku a váhu, a ve třetím řádku se mu zobrazí hodnota BMI a ve čtvrtém řádku informace o tom, jestli je v normě nebo není.
Tabulka v příkladu je ke stažení tady. Jedná se o klasický excelovský dokument, a po vložení na stránky bude vypadat takto:



Schválně vyzkoušejte, že když změníte údaje ve žlutých buňkách, změní se výsledky.
Vytvořit takovouto kalkulačku by byla, bez SkyDrive a Excelu, docela makačka. Zřejma by bylo nutné znát JavaScript a šikovně ho použít.
Nám to ale zabere jen pár minut a JavaScript nebudeme potřebovat.

Návod

Dokument vytvořím (nebo stáhnou odsud) a nahraji na SkyDrive, což je popsané tady.
Pak jdu do webového úložiště SkyDrive. Pokud jste přihlášeni, mělo by stačit zadat do prohlížeče:
https://skydrive.live.com/
V úložišti vidíte všechny své soubory.


Rozklikněte ten, který chcete sdílet. Otevře se ve webovém prohlížeči.


V tomto stavu mohu dokument upravovat, ale s řadou omezení oproti standardnímu Excelu.
Kliknu na Soubor / Sdílet / Vložit.


V dialogu nahoře zapíšu (nebo v ukázce vpravo vyznačím), které buňky z Excelu se mají prezentovat na webu.
V dialogu uprostřed zaškrtnu, že návštěvník stránky může buňky měnit.
V dialogu dole se vytvoří kód, který označím a zkopíruji. Zkopírovaný text pak vložím do kódu stránky.


Výsledkem je pak toto:



 Jednoduché, ne?

středa 9. října 2013

Školení - středně pokročilý Excel v malé skupince

Chcete využít dvě dopoledne v první polovině listopadu k tomu, abyste se výrazně zlepšili v Excelu?
Nabízíme výuku  v malé skupince účastníků - do pěti lidí.
Kurs trvá 2x3 hodiny a pokrývá například tato témata:

  • Funkce VLOOKUP/SVYHLEDAT 
  • Funkce IF/KDYŽ a její kombinace s jinými funkcemi 
  • Funkce SUMIF, COUNTIF 
  • Kontingenční tabulky - od začátku po pokročilé funkce Úvod do maker 
  • ...

Rádi vás 5. a 11. listopadu přivítáme v příjemném prostředí školicího centra Lexis.
Vyučovat bude lektor Jiří Beran.
Pro podrobné informace o kursu, cenu a možnost přihlášení klikněte zde.

úterý 8. října 2013

Ukládání souborů Excelu na SkyDrive

Microsoft nabízí službu, která se jmenuje SkyDrive. SkyDrive je jakési webové úložiště - složka, ke které můžete přistupovat ze všech svých počítačů, které jsou přihlášené ke stejnému účtu.
SkyDrive zatím nemám úplně nastudované - ale zatím se mi zdá, že je to trochu nepraktičtější a trochu těžkopádnější kopie DropBoxu, který dlouhodobě nadšeně používám.
V tomto článku chci ukázat, jak na toto sdílené úložiště nahrávat soubory - protože v dalším článku chci ukázat jak takto publikované soubory využít při tvorbě webových stránek.
Poznámka na úvod - před tím, než začnu nahrávat, je ale nutné mít vytvořený účet Microsoft Live a přihlásit se k němu.
Dále pokračuji podle verze Office.

Ukládání souborů na SkyDrive v Office 2010

Vytvořím nebo otevřu soubor standardním způsobem.
Jdu na Soubor / Uložit a odeslat / Uložit na web



Pak si mohu vybrat konkrétní složku nebo Uložit jako - a vybrat přesné umístění.
Excel mi defaultně nabízí uložení do složky dokumentů. Já ale mohu v rámci Skydrive ukládat kamkoliv - všechen obsah vidím, pokud kliknu na kořenovou složku s nezapamatovatelným názvem.


Vyberu správné umístění a jméno, kliknu na "Uložit" a je hotovo.

Ukládání souborů na SkyDrive v Office 2013

V novějších Office je to snazší - tam už integrace se SkyDrive pokročila dále.
Jdu tedy na Uložit jako / SkyDrive uživatele a pak buď vyberu konkrétní složku, nebo jdu na Procházet, čímž se dostanu do stejného okna jako v předchozím případě.


úterý 1. října 2013

Kde najdete všechno

S jednou klientkou jsme během výuky řešili, na které kartě se nacházejí která tlačítka. A při té příležitostí jsem se dozvěděl zajímavou větu:
"Já když nemůžu něco najít, tak je to většinou v kartě "Data"."
Nejprve jsem se tomu usmál, ale pak jsem zapřemýšlel - a má to něco do sebe.
Na kartě "Data" jsou funkce, které jsou sice užitečné, ale nepoužívají se moc často. Proto, když už je potřebujeme, často nevíme přesně kam pro ně.
Jedná se například o tyto funkcionality:
Takže až budete hledat něco, o čem víte že v Excelu je, ale zrovna to nevidíte, zkuste Data...

PowerPivot - odlišnosti dvou verzí 2010

Jak jsem dnes po delším zkoumání zjistil, existují dvě verze PowerPivotu určené pro Excel 2010.
Jedna, starší, se mimo jiné liší tím, že neobsahuje tlačtka pro práci s relacemi:


Zatímco druhá, novější, tato tlačítka obsahuje:

Takže, pokud máte verzi bez tlačítek, nemá cenu hledat. Jediné, ale zato snadné, řešení je stáhnout novou verzi odtud:
A je po problému. Snad jen pozor na to, že soubory vytvořené v novější verzi zřejmě nejsou kompatibilní se starou verzí.

pátek 27. září 2013

Strategický reporting

Tento článek je druhým dílem volného seriálu o reportingu. Tentokrát se hodně vzdálíme od Excelu a podíváme se spíše na to, co, proč a jak ve firmě reportovat - sledovat.
Ve firmách se totiž často potkáváme se situací, kdy se poctivě, podrobně, přehledně a s velkým úsilím sledují naprosto nepodstatné blbosti.
Souvisí to i s Excelem. Zaměstnanci firem jsou často schopni vytvořit skvělé tabulky a reporty. Nepřemýšlejí ale dostatečně o tom, jestli zkoumají přesně to, co je zkoumat třeba, a proto jejich úsilí přichází vniveč.

Co ve firmách sledovat / měřit / reportovat?

Kdysi jsem se zeptal jednoho z šéfů jedné z největších českých firem, podle čeho jeho firma vybírá ukazatele, které sleduje. Odpovědí mi bylo, že sledují to, pro co mají údaje. Na mojí otázku, jestli by neměli sledovat to, co sledovat potřebují, byl nechápavý pohled a upřímná slova "... my takhle neuvažujem. Sledujeme to, na co máme data." A opravdu. Firma měla (a dosud má) propracovaný systém sledování vytížení strojů, protože to se sleduje dobře. A naopak prakticky nesleduje kvalitu lidských zdrojů nebo vztahů se zákazníky. Protože to se zase sleduje špatně.
V tomto textu se zaměřím na to, jak by měla vypadat architektura reportingového systému firmy ne z technického, ale z podnikatelského pohledu.
Budu vycházet z metodiky Balanced Scorecard, proto ji stručně představím.

Balanced Scorecard

Balanced Scorecard je systém a metodika pro řízení strategie v organizaci a měření plnění této strategie.
Její původ sahá do osmdesátých let minulého století, hlavními tvůrci a popularizátory jsou američtí teoretici managementu Robert Kaplan a David Norton. Účelem jejich práce je nabídnout manažerům firem techniku, kterou by mohli komplexně a přitom jednoduše řídit celou organizaci směrem, kterým chtějí.
K tomu, aby na metodě pracovali, je zřejmě přiměla situace v řadě organizací, které žádnou strategii nemají, a pokud už ano, tak je jen formální a neobsahující kontrolu dodržování ani přesná měřítka pro tuto kontrolu.
Na metodice se mně osobně obzvláště libí jedna věc - nejedná se o nic převratného a o gejzír nových myšlenek, ale o setřídění a popis obecně platných, užitečných pravidel.
Strategie dle Balanced Scorecard je evidována ve velmi přehledné formě tzv. strategické mapy. Tato strategická mapa obsahuje:
  • Cíle firmy rozdělené do perspektiv (skupin). Obvykle se uvádějí čtyři základní perspektivy - finance, zákazníci, interní procesy a lidé.
  • Měřítka příslušející k cílům. Typicky se jedná o jedno až dvě měřítka ke každému cíli. Podle hodnot těchto měřítek se poznává, jestli se daří nebo nedaří naplňovat cíle.
  • Plán akcí vedoucích k naplnění cílů.

















Více o strategické mapě a jejích částech v dalších příspěvcích.


úterý 24. září 2013

Fígl s kombinací funkcí INDEX a POZVYHLEDAT (MATCH)

Nejspíše někdy používáte funkci VLOOKUP (SVYHLEDAT). Je to funkce velmi užitečná, má ale jednu nevýhodu, na kterou občas narazíte.

Příklad

V následujícím příkladu potřebuji doplnit k zaměstnancům jméno jejich nadřízeného. Na základě toho, do kterého oddělení zaměstnanec patří - protože každé oddělení má svého jednoho šéfa.



Mohl bych použít VLOOKUP. Ale to by musely sloupečky v tabulce vpravo mít obrácené pořadí. Funkce VLOOKUP totiž vyžaduje, aby v tabulce, na kterou se odkazuji, bylo v prvním sloupci to, podle čeho se obě tabulky propojují. Jinými slovy - tabulky jsou propojené přes název oddělení, proto v tabulce vpravo musí být sloupec s názvy oddělení vlevo od jména šéfa.

Návod

Sloupce bych mohl prohodit, ale ne vždy je to možné.
Proto použiji kombinaci funkcí INDEX a MATCH (POZVYHLEDAT), která mě toto umožní. Zápis do buňky C2, který pak roztáhnu, bude vypadat takto:
=INDEX(E:E;MATCH(B2;F:F;0))
Logika je taková, že:
  • Nejprve funkce MATCH zjistí, kolikátá je určitá hodnota ve sloupci. V našem případě zjistí, že hodnota "HR" je ve sloupci F na druhém místě. Výstupem vnořené funkce je tedy dvojka.
  • Pak funkce INDEX zjistí, co je na tomto místě v určitém sloupci. V našem případě zjistí, že na druhém místě je ve sloupci E hodnota "Hanka". A výstupem funkce, čili přiřazením nadřízeného pro Adélu, je správně "Hanka".
Vzorec roztáhnu a mám ošetřené všechny hodnoty / všechny zaměstnance.

Upozornění

Aby tato kombinace funkcí fungovala, musí být hodnoty v prohledávaném sloupci seřazené podle abecedy. Jinak se výsledky tváří, že fungují, ale nefungují.


pondělí 9. září 2013

Reporting

Lidé z IT si pod slovem reporting představují OLAP databáze. Lidé z účetnictví si jej spojují s účetními výkazy. Vedení globální společnosti si pod reportingem představuje barevné grafy, které mu umožňují rozhodovat, aniž by bylo třeba rozumět tomu, o čem se rozhoduje. Vedení lokálních poboček si pod reportingem představuje aktivitu, kdy vezme obchodní výsledky pobočky a předtím, než je předá na centrálu v Dortmundu, tak je přikrášlí způsobem, který nebude přímo napadnutelný.
Pravdu mají všichni a ještě mnozí další, reporting nemá žádnou všeobjímající a přitom srozumitelnou definici.
Nicméně ať už si reporting definujeme jakkoliv, jedno je jasné - tato aktivita je téměř vždy úplně nebo alespoň částečně řešena v aplikaci MS Excel. 
A řada zákazníků, kterým s Excelem pomáháme, po nás požaduje pomoc právě s reportingem - i když toto slovo často ani nepoužívají.
Proto má reporting místo i na tomto blogu. Dnešní příspěvek je tak úvodní kapitolou v seriálu, který by měl popsat základní principy reportování a způsoby, jak je dobře řešit nejen z hlediska Excelu.
Co to ten reporting je?
Jak už je zmíněné výše, jednoznačná definice neexistuje. Anglická wikipedia rozeznává reporting jako aktivitu vedoucí k reportu (trochu důkaz kruhem) a dále uvádí speciální odrůdu reportingu Business reporting
Ten nás bude zajímat nejvíc.
Nadále si pod reportingem představíme činnost, na jejímž počátku jsou neuspořádaná data, z nichž nelze vyčíst žádné smysluplné informace. Například extrakt z databáze nebo ze SAPu. A na konci pak informace, podle kterých se můžeme rozhodovat o řešení konkrétních problémů. Například krátká srozumitelná tabulka nebo graf.
V obchodní firmě tak mohu mít na začátku záznamy z pokladen o milionu prodaných položek. A na konci přehlednou tabulku nebo graf, ze kterého manažer snadno vyčte, jaké zboží má objednat na příští období.
Je asi zřejmé, že za takovou přeměnou je pořádný kurs práce. Řada návodů, které uvádíme na tomto blogu, je v takovémto procesu dobře použitelná, typicky se to týká kontingenčních tabulek nebo technologie PowerPivot.
Nový několikadílný seriálek by měl tyto informace dát to správného kontextu a doplnit k nim další praktické tipy.


pátek 6. září 2013

Refresh návodu VLOOKUP / SVYHLEDAT

Návod na použití VLOOKUP/SVYHLEDAT je druhým nejčtenějším návodem na tomto blogu. Takže si zasloužil kritické přečtení, odstranění nejasností a zpřehlednění.
Nová verze ke shlédnutí tady.

sobota 24. srpna 2013

Osmá hádanka - geografická (umístění vysílačů)

Vzdálenost dvou míst na zemi podle GPS souřadnic

Když potřebujete zjistit, jak jsou od sebe (vzdušnou čarou) vzdálena dvě místa na zeměkouli, je to docela potíž. Protože zeměkoule je, bohužel, kulatá. Kdyby byla placatá (a nebo kdyby šlo o relativně blízká místa kde se dá kulatost zanedbat), stačilo by použít pythagorovu větu - kdy rozdíly souřadnic jsou odvěsny a vzdálenost přepona. Pro kulatou zemi potřebujeme trochu složitější vzorec.
Nevymyslel jsem jej, jen našel na různých fórech, a vypadá takto v anglické verzi:
=ACOS(COS(RADIANS(90-A2))*COS(RADIANS(90-A3))+SIN(RADIANS(90-A2))*SIN(RADIANS(90-A3))*COS(RADIANS(B2-B3)))*6371
a takto v české verzi:
=ARCCOS(COS(RADIANS(90-A2))*COS(RADIANS(90-A3))+SIN(RADIANS(90-A2)) *SIN(RADIANS(90-A3))*COS(RADIANS(B2-B3)))*6371
  • A2 je první zeměpisná šířka, A3 délka druhého místa
  • B2 je první zeměpisná délka, B3 délka druhého místa
  • 6371 je poloměr země. Pokud jsou místa na jiné planetě, dosaďte příslušnou hodnotu...
  • Souřadnice jsou zadané ve stupních, minuty jsou převedeny na desetinná místa stupňů
  • Jižní zeměpisná šířka se převádí na záporné hodnoty
  • Západní šířka také
  • Proč se tady trigonometrické funkce používají zrovna takto chápu jen částečně, ale otestoval jsem a funguje  to :)
Vzoreček se dá použít např. v této excelovské hádance.

pátek 2. srpna 2013

Funkce POSUN/OFFSET

Kdo klikne, koupí, zalže a umře - o knize "Predictive Analytics: The Power to Predict Who Will Click, Buy, Lie, or Die"

Tenhle trochu bulvární titulek neuvádí zamyšlení o pravdě a smrtelnosti. Uvádí krátkou recenzi knihy "Predictive Analytics: The Power to Predict Who Will Click, Buy, Lie, or Die" od Erica Siegela. V Čechách zatím myslím nevyšla, ale jistě v dohledné době vyjde - zdá se, že v anglicky mluvících zemích je celkem populární.













Je to první recenze knihy na tomto blogu, nicméně myslím že sem patří - kniha se totiž věnuje analýze dat, a to je to, k čemu často používáme i Excel.
Knihu jsem začínal číst s docela velkým očekáváním. Analýza dat je obor, který mě baví a má podle mě obrovskou budoucnost, která se zatím teprve začíná rýsovat. Přitom relevantní literatury je jako šafránu. Respektive literatura existuje, ale většinou pojednává téma čistě z hlediska výpočtů a matematiky, přičemž se nevěnuje aplikacím v byznysu.
A podle toho, jak je kniha uváděna a docela hodně propagována, jsem čekal že se v ní dozvím základní principy prediktivní analýzy popsané způsobem srozumitelným pro člověka zvyklého pracovat s daty, ale současně člověka bez hlubokého matematického nebo statistického vzdělání.
Kniha má necelých tři sta stránek, sedm kapitol a nějaké ty úvody a dodatky navíc.
Autor píše velmi čtivě. Má vytříbený, vtipný jazyk a umí, alespoň zpočátku, zaujmout.
Začnete číst a máte pocit, že jste na správné stopě. Projdete úvod a první kapitoly, ve kterých se dozvídáte, kde se prediktivní analýza používá (pod prediktivní analýzu tady spadá např. odhad rizika, strojové učení, modelování atd.). Pak pomalu začnete mít pocit, že jde o mlácení prázdné slámy - autor stále uvádí příklady použití prediktivních analýz, ale s přibývajícími stránkami ubývá vtipnost a přibývá roztahanost.
Pak dojdete ke čtvrté kapitole, a najednou to začne být docela zajímavé. Autor srozumitelně vysvětluje některé postupy používané v prediktivní analýze, např. rozhodovací stromy.
Jenže pak se to zase vrátí do vyjetých kolejí, k příkladům použití, studiím, výhodám, budoucnosti...
Celkový závěr?
Kniha podle mého názoru přináší zajímavé informace, především o možnostech použití prediktivní analýzy. Na druhou stranu - těch informací je málo a jsou podané velmi ukecaně, zdlouhavě a jsou zabalené do několika vrstev sebepropagace tématu, knihy i autora.
V knize se mnohokrát dozvíte, k čemu se prediktivní analýza používá. A dozvíte se to na docela zábavných historkách podaných docela zábavným způsobem. Já jsem si ale dělal naději, že se dozvím, alespoň v základech, JAK to celé funguje. A na to autor téměř úplně rezignoval.
Na této stránce:
http://www.goodreads.com/book/show/15941378-predictive-analytics
jsou zajímavé reakce čtenářů. Je pravda, že řada z nich je pozitivních a v některých bodech s nimi souhlasím. Nejvíce bych se ale podepsal pod názor, že kniha má "Low signal to noise ratio..."
Docela by mě zajímalo, jestli knihu četl i někdo z čtenářů tohoto článku a pokud ano, tak co si o ní myslíte?

středa 31. července 2013

Dvoustý příspěvek

Tak tento týden jsem na tento blog umístil dvoustý příspěvek. Člověk se ani nenaděje, začne psát a najednou je toho tolik
Před pár lety jsem musel povinně vytvořit nějaké internetové stránky na jeden předmět na VŠ. Vytvořil jsem tohle http://beran_jiri.sweb.cz/ a nenapadlo mě nic lepšího, než tam umístit pár návodů na Excel, který jsem v té době při škole v různých firmách učil. Postupem času jsem došel k tomu, že psát stránky v HTML je zbytečná otročina a přesunul se na blogger a doménu www.excelentitriky.com.
Pak jsem na stránky kromě návodů přidal i nabídku doučování, která si časem vydobyla vlastní stránky www.vyuka-excelu.cz. A pak se to nějak zvrtlo, přibral jsem kolegu a najednou máme řadu spokojených zákazníků.
Nicméně už mi trochu dochází fantazie s tím, jaké návody na blog dávat. Tak kdybyste přišli na něco, co Vám tady chybí a co by se Vám hodilo pro práci, dejte vědět.
Jinak děkuji za pozornost a čtěte tento blog dál :)



Práce s hierarchickými daty - má někdo zkušenost a poradí?

Nedávno jsem potřeboval vytvořit úkol podobný tomuto. Týká se práce s daty, která jsou řazena hierarchicky - v tomto příkladě se jedná o organizační strukturu pracovníků firmy.
Nenašel jsem ale žádné jednoduché řešení. Uměl bych asi napsat makro, ale raději bych našel řešení bez makra.
Je někdo schopný s tímto poradit?
V příkladu mám tabulku zaměstnanců. V jednom sloupci je jejich jméno, ve druhém jméno jejich nadřízeného a ve třetím mzda. Mohlo by jít i o dvě tabulky, jednu s identifikací nadřízeného a druhou s mzdou pracovníka - to je jedno.
Jde o to, že potřebuji sečíst mzdy všech podřízených určitého pracovníka - tak, jak je to ve druhé tabulce.
I když jsem to tehdy nakonec nemusel řešit, je to docela zajímavý oříšek který se mi nedaří rozlousknout. Poradí někdo? Hledám samozřejmě řešení, které je použitelné i pro rozsáhlé tabulky a nevyžaduje žádné "manuální" zásahy.
Nevadilo by, pokud by řešení bylo limitované např. pěti úrovněmi, jednalo se o data s malým počtem uzlů.



pátek 19. července 2013

Nový kurs pro firmy - nejpraktičtější postupy v Excelu

Excel už školíme několik let. A během této doby si všímáme toho, že některá témata jsou pro naše studenty stále mimořádně atraktivní - postupy, které jsou velmi dobře použitelné v podnikové praxi.
Proto jsme se rozhodli vytvořit nový dvoudenní kurs pro firmy, zaměřený právě na takováto témata - vysvětlovaná na praktických příkladech.
Pro více informací o tomto kursu klikněte zde.
Cena kursu je 7500 Kč - což např. při 8 účastnících vychází cca na 940 Kč za dvoudenní kurs.

sobota 6. července 2013

Nahrazování znaků v Excelu

Při práci s Excelem se docela často dostanete do situace, kdy potřebujete nahrazovat nějaký text jiným. V tomto příkladu nahrazují čárky tečkami, nicméně velmi podobně to funguje s jakýmikoliv textovými řetězci. Mám v zásadě dvě možnosti - buď použít Najít / nahradit, nebo funkci SUBSTITUTE / DOSADIT.

Najít / nahradit

Dřevní, ale často velmi efektivní metoda.
Prostě stisknete Ctrl + F a vyberete, co za co se má nahradit.
V tomto případě mám sloupec datumů, kde jsou dny a měsíce oddělené čárkou místo tečky.


Mně se ale lépe pracuje s tečkami. Abych čárky na tečky změnil, použiji Ctrl + F, nastavím že se mění čárky na tečky a takto vypadá výsledek:

Najít / nahradit je velmi užitečná funkce, má ale jednu zásadní nevýhodu - funguje jednorázově. Tedy kdybych např. do tabulky uvedené nahoře přidal další datum s čárkami, tak se mi na tečky už nezmění do doby, než znovu použiji Najít / nahradit.
Pokud mi toto vadí, pomůže mi funkce SUBSTITUTE / DOSADIT.

SUBSTITUTE / DOSADIT

Tato funkce nahrazuje ve vybraném textu určitý text jiným textem.
Pokud bych ji chtěl použít v předchozím případě, vypadal by zápis takto:
=DOSADIT(A1;",";".")

  • První argument je text, se kterým pracuji
  • Druhý argument je text, který se má najít
  • Třetí argument je text, kterým se má text ze druhého argumentu nahradit
  • Čtvrtý, nepovinný argument je číslo výskytu, na které se má výměna použít. Např. zápis DOSADIT("tadydadyda";"a";"X";2) vyhodí "tadydXdyda - protože se nahradilo druhé áčko velkým ikskem.


čtvrtek 27. června 2013

Školení u nového zákazníka

Tento týden jsme měli možnost školit u nového zákazníka - firmy Basys CS, s. r. o.
Jsme rádi, že můžeme uvést výsledky velmi příznivé zpětné vazby získané z dotazníků, které účastníci školení na konci vyplňovali.

pátek 31. května 2013

Podle jakých klíčových slov nacházíte tento blog?

Uvádím tabulku dvaceti klíčových slov, přes jejichž zadání se návštěvníci v květnu dostali na tento blog.
Výsledky nejsou asi překvapivé - jsou to víceméně kontingenční tabulky a Svyhledat na různé způsoby, s několika čestnými výjimkami z jiných témat.
Obzvláště mě ovšem zaujalo slovo na sedmé pozici a tímto se omlouvám všem, kteří jste se přes něj na můj blog dostali - zřejmě jste hledali něco jiného než toto:)
  1. kontingenční tabulka
  2. kontingenční tabulka návod
  3. excel makra
  4. excel zobrazí buňku ve které je dnešní datum
  5. kontingenční tabulka excel
  6. makra excel
  7. sexy
  8. svyhledat
  9. kontingenční tabulky
  10. vlookup
  11. makra v excelu
  12. kontingenčni tabulky
  13. access návod
  14. jak přičíst rok v excelu
  15. excel kontingenční tabulky
  16. funkce svyhledat
  17. hromadná korespondence word 2007
  18. excelentní triky
  19. excel kontingenční tabulka
  20. excel svyhledat


úterý 28. května 2013

Excel, Microsoft a Gartner

Společnost Gartner publikuje každý rok takzvané "Magic quadrants". Grafy, ve kterých hodnotí pro různá odvětví softwaru kvalitu hlavních hráčů. Dalo by se docela dlouho debatovat o tom, jestli je hodnocení přesné a osobně si nedělám ambice to posuzovat. Nicméně jedná se víceméně o jediné hodnocení tohoto typu, takže výsledky jsou docela uznávány.
Jeden z nejvíce sledovaných kvadrantů se týká business intelligence - zjednodušeně systémů pro analýzu dat.
A z pohledu Excelu (a z pohledu přihřívání polívčičky tohoto blogu) je docela zajímavé, že Microsoft se umístil na nejlepším místě žebříčku co se týká "Ability to execute", což bych přeložil jako "praktickou využitelnost".

A zajímavé je to právě kvůli Excelu. Autoři jej totiž už považují za součást BI od Microsoftu (přičemž u jiných výrobců se za BI považují spíše databázové a analytické servery), a právě Excel uvádějí jako jeden z důvodů vysokého umístění Microsoftu.
Za hlavní výhody Microsoftu oproti konkurenci Gartner považuje (dovoluji si přeložit a zkrátit):
  • Celkovou integrovanost BI řešení od MS SQL přes Excel a PowerPivot až k SharePointu
  • Relativní snadnost implementace oproti konkurentům
  • Konkurenceschopnou cena licencí
  • Rychle se zlepšující Excel, u kterého se očekávají další zajímavé pokroky v oblasti BI
  • Přístupnost pro méně technicky zdatné uživatele (i díky použití Excelu jako rozhraní mezi uživatelem a systémem)
Za problematická místa naopak Gartner považuje:
  • Nižší úroveň starších verzí MS SQL v porovnání s konkurencí (kriticky hodnotí verzi 2008, která ve srovnání s konkurencí zaostává).
  • Vysokou komplexitu celkového řešení (tedy zase provázanost MS SQL, Excelu a SharePointu), která přináší komplikovanost řešení (čili je to současně pozitivum i negativum).
  • Přílišné zaměření na koncové uživatele
  • Zaostávání v mobilních technologiích
Řada uživatelů si možná neuvědomuje, že Excel je (často samozřejmě ve spolupráci s dalšími systémy) velmi silným nástrojem pro analýzu dat. A i když existují na trhu v analýze dat i zvučnější jména (Oracle, SAS, IBM), Microsoft už je docela dobře dotahuje nebo předbíhá. A to právě (ale nejenom) díky výborné praktické využitelnosti, protože BI řešení od Microsoftu prostě fungují jednodušeji než u konkurence.
Dá se čekat, že analytické funkce budou přibývat a že budou současně chytřejší i jednodušší na použití. A že to, co dříve mohli dělat jen programátoři nebo databázoví specialisté, budou moci brzy dělat i pokročilejší uživatelé Excelu. Osobně jsem měl možnost pracovat s verzemi Excelu a MS SQL od 2000 přes 2005 a 2008 po 2012 a mohu potvrdit, že zlepšení se projevuje nejen v množství funkcí, ale ještě více v jejich dosažitelnosti pro uživatele.
Pokud tedy máte rádi Excel (nebo Vám alespoň není úplně odporný :) ) a něco v něm umíte, můžete se těšit na to, že své znalosti budete teď nebo v blízké budoucnosti umět využít i způsoby, o kterých se Vám zatím ani nesnilo.

středa 15. května 2013

Excel - příprava na pracovní pohovor

Pro řadu pracovních pozic je zvládnutí Excelu naprosto klíčové. Z čehož plyne, že je znalost Excelu po uživatelích požadována i při přijímacích testech.
Už několik let připravujeme uchazeče na taková přijímací řízení. A docela často se v různých obdobách bohužel opakuje jeden telefonát:
"Dobrý den, našel jsem vás na webu a chtěl bych si domluvit konzultaci Excelu. Byl jsem minulý týden na pohovoru a neprošel jsem, protože jsem nezvládl test Excelu. A protože mám pozítří další pohovor, rád bych se domluvil na konzultaci dnes nebo zítra..."
Z takového přístupu plynou pro uživatele dvě nepříjemné věci. 
  • Zaprvé, Excel se uchazeč o zaměstnání učí až když přišel o jedno zajímavé místo nebo více takových. Taková šance se už nemusí opakovat a je to obrovská škoda.
  • Zadruhé, Excel se uchazeč učí těsně předtím, než se zúčastní dalšího výběrového řízení. A za jeden nebo dva dny se nedá naučit všechno. Dá se sice leccos vysvětlit, pochopit a procvičit, ale jen těžko i  zažít. Takže uchazeč pak sice něco umí, ale není schopen to použít ve stresu při pohovoru. A to je také obrovská škoda.
    Jsme sice opravdu flexibilní a konzultace si standardně domlouváme ze dne na den nebo i z hodiny na hodinu, ale některé věci prostě chtějí svůj čas.
Takže moje opravdu dobrá rada zní - učte se Excel včas. Je docela možné, že Vám to zachrání kariéru nebo umožní rozjet novou. 
Samozřejmě budeme nejraději, když si za průvodce Excelem provedete nás a dáte nám možnost rozšířit řadu těch, kterým jsme se získáním práce pomohli. Ale i když si vyberete jinou cestu (jiného lektora, knihu, internet - každému vyhovuje něco jiného), začněte včas a neodkládejte to.
Konkurence na pracovním trhu už je opravdu tvrdá a přijímací řízení bývají mnohem méně idylická než jak by se zdálo z letáků personálních agentur :) 




pátek 10. května 2013

Rozdíl mezi směrodatnou odchylkou a směrodatnou odchylkou

Musím říct, že rozdíl těchto dvou položek v menu není na první pohled úplně patrný :)

Výsledky jsou mírně odlišné, proto se asi liší způsob zpracování vstupů - ale nešlo by to lépe popsat?

čtvrtek 9. května 2013

Šestá hádanka - detektivní (kartel benzínek)

Omezení doplňku PowerPivot v Office 2013

V minulých příspěvcích na tomto blogu jsem popisoval práci s doplňkem PowerPivot. Myslím si, že je to věc, která se Microsoftu opravdu povedla a která přináší i běžným uživatelům možnost využívat pokročilé techniky Business Intelligence.
V Office 2010 to fungovalo tak, že jste si doplněk stáhli a pak, do kterékoliv verze, jednoduše nainstalovali.
Předpokládal jsem, že tak nějak to bude fungovat i ve verzi 2013. V Microsoftu si ovšem asi řekli, že je třeba zákazníky po čase zase trochu naštvat. A od verze 2013 povolili PowerPivot pouze k verzi Office Professional Plus.
Jinými slovy pokud máte Office pro domácnost (3500 Kč) nebo pro podnikatele (6800 Kč), tak máte smůlu. Pro použití PowerPivotu si musíte koupit Office pro profesionály (14000 Kč). Za to dostanete jako bonus několik dalších aplikací, které nejspíš v životě nebudete potřebovat...
PowerPivot je hodně zajímavý pro každého, kdo se chce vážněji zabývat zpracováním dat v Excelu. Pokud je to i Váš případ a nechcete utrácet za nejdražší verzi Office, doporučuji velmi pečlivě zvážit přechod na novou verzi.

úterý 7. května 2013

Chcete být sexy? Naučte se pracovat s kontingenční tabulkou.

Myslíte si, že abyste byli sexy, je třeba dobře vypadat?
Pak nejspíše žijete v minulém století. Podle tohoto článku v Harward Business Review je v našem století tím nejvíce sexy ten, kdo umí analyzovat data.
Takže - klidně jezte, nesportujte a kašlete na svůj vzhled, ale kontingenční tabulky se učte :)

sobota 13. dubna 2013

Komentáře v Excelu

Příklad

Do tabulky potřebuji přidat informaci, která bude popisovat buňku. Například potřebuji uživateli poradit, co má do buňky napsat.
Třeba takto:

Návod

Jdu do buňky, kam se má komentář vložit. 
V kartě Revize kliknu na Nový komentář. Do textu doplním, co se má uživateli zobrazovat. Tento text se pak zobrazí každému, kdo do buňky klikne. Buňka, ve které je komentář, má v pravém horním rohu malý trojúhelníček.
Popisovat funkci tlačítek Upravit komentář, Odstranit, Předchozí, Další by bylo urážkou inteligence čtenáře tohoto textu :)

čtvrtek 11. dubna 2013

Měli byste zájem o výuku přes internet?

Na webu s nabídkou výuky Excelu nabízíme také výuku přes internet. Domníváme se, že to může být pro řadu našich zákazníků zajímavé. Zvažovali byste takovouto výuku?

úterý 9. dubna 2013

Třetí lekce za polovinu ceny

Chcete-li se Excel dobře naučit, je duben vhodný měsíc. Pokud si s námi do konce května domluvíte tři lekce, budete mít tu třetí jen za polovinu ceny.

úterý 2. dubna 2013

Nový test

Chcete-li vyzkoušet, jak na tom v Excelu jste, můžete k tomu využít tento test. Do testu jsem dal to, co považuji za nejdůležitější.
Test je zaměřený spíše na základní a mírně pokročilé znalosti.
Test ke stažení:
Ostatní testy:
http://www.excelentnitriky.com/p/otestujte-se.html

čtvrtek 28. března 2013

Kontingenční tabulky konečně pohromadě

V horní liště přibylo tlačítko, které odkazuje na všechny návody týkající se kontingenčních tabulek. Je to proto, že se podle návštěvnosti jedná o velmi důležité téma. Doufám, že je to takto pro návštěvníky stránek přehlednější.


pátek 8. března 2013

Co dál po tabulkách? Relační databáze!

Během konzultací nebo výuky se často setkávám se zákazníky, kteří už v Excelu umí vše, co potřebují, a přemýšlejí, co dál.
Logicky se chtějí zaměřit na makra a Visual Basic. Na první pohled to dává smysl. S pomocí maker se dají vytvářet zajímavé aplikace a uživatelé čekají, že jim tyto aplikace výrazně usnadní práci.
Makra rád učím a o jejich užitečnosti nepochybuji.
Osobně se ale domnívám, že je ještě jedna oblast, kam po tabulkách pokračovat, a je praktičtější. A tou oblastí jsou relační databáze. Osobně bych se na databáze zaměřil dříve než na makra z těchto důvodů:
  • Základní pochopení a využití relačních databází není těžké
  • Když se naučíte databáze, budete moci efektivněji využít své znalosti Excelu - třeba tak, že v Excelu budete využívat data z databází
  • Prakticky všechny systémy, se kterými se v práci potkáte, jsou na relačních databázích postavené. Pokud pochopíte databáze, pochopíte snadněji i ty systémy
  • Základy práce s databází mohou být v zaměstnání nebo u přijímacích pohovorů vaší významnou výhodou oproti ostatním uchazečům
  • Není těžké naučit se vytvářet makra. Naučit se ale vytvářet makra, která opravdu usnadní reálné pracovní úkoly, často těžké je a je to práce spíše pro programátory než pro běžné uživatele
Pokud se chcete s databázemi naučit, doporučuji začít s aplikací MS Access. Ta se sice v praxi moc nepoužívá, pro výuku je ale velmi vhodná, protože:
  • Není třeba složitě instalovat
  • Máte ji zřejmě na svém počítači
  • Základy, které se v Accessu naučíte, můžete dobře použít ve "velkých" systémech jako Oracle, MS SQL nebo DB2
Takže, až budete mít pocit, že je vám běžný Excel malý, zvažte, jestli se dále nenaučit něco o databázích - ať už sami, nebo s námi.


pátek 22. února 2013

Vložení znaku do textu

Příklad

Rychlý úkol: Potřebuji do textu na určitou pozici vložit určitý znak. Například potřebuji vložit mezeru mezi třetí a čtvrtý znak.

Návod

Použiji kombinaci funkcí CONCATENATE (ta spojuje texty), ZLEVA (česky LEFT) a funkce ČÁST (anglicky MID).
  • =CONCATENATE(ZLEVA(A1;3);" ";ČÁST(A1;4;1000))
Vysvětlení
  • CONCATENATE - spojuje části textu oddělené středníkem - tedy začátek, mezeru a zbytek
  • ZLEVA - zobrazí určitý počet znaků odleva (3 znaky)
  • ČÁST - zobrazí určitý počet znaků doprava od určitého znaku. V mém případě 1000 znaků (fakticky neomezeně znaků) od čtvrtého znaku


neděle 10. února 2013

Nový kurs Večerní škola Excelu

Chcete využít čtyři březnové čtvrtky k tomu, abyste se opravdu dobře naučili pracovat s Excelem? Pokud ano, je přímo pro Vás nový kurs Večerní škola Excelu.
Je určen pro uživatele, kteří nejsou úplní začátečníci, ale chtějí se v Excelu výrazně zlepšit.

čtvrtek 31. ledna 2013

Šablona životopisu

Tyto stránky často navštěvují lidé, kteří si chtějí zlepšit svoji kvalifikaci při hledání nové pracovní pozice. Možná se někomu z Vás bude hodit i šablona životopisu. Není nijak neobvyklá, ale dá se v ní napsat vcelku strukturovaný životopis za chvilku - třeba se Vám bude hodit.
Pokud máte nápad jak ji zlepšit, dejte vědět nebo rovnou pošlete upravenou verzi.
https://www.dropbox.com/s/mm3gsy2ypub7r9v/CV_sample_zivotopis.xlsx
Před posíláním doporučuji životopis uložit ve formátu PDF.





úterý 22. ledna 2013

Import dat z databáze do Excelu


Příklad

Potřebuji do Excelu importovat data ze standardní relační databáze. V našem případě použijeme pro jednoduchost MS Access, ale s jinou databází by se pracovalo podobně.
Databáze, ze které potřebuji importovat, je velmi jednoduchá. Skládá se ze dvou tabulek.
V jedné je seznam zaměstnanců - jejich ID, jména a ID oddělení, do kterého patří, ve druhé je seznam oddělení - ID oddělení a jeho název.
Tabulka se zaměstnanci:
Tabulka s odděleními:
Tabulky jsou tedy propojeny přes číslo oddělení, které tvoří primární klíč tabulky "Oddělení".
Do Excelu potřebuji dostat tuto tabulku:


Pokud bych zůstal v Accessu, definoval bych ji tímto dotazem:

SELECT Zaměstnanci.Jméno, Oddělení.[Název oddělení]
FROM Oddělení INNER JOIN Zaměstnanci ON Oddělení.ID = Zaměstnanci.[ID Oddělení];



Jak napsat jednoduché makro v jazyce Visual Basic for Applications (VBA)

čtvrtek 17. ledna 2013

Kdy Vás zajímá Excel?

Docela mě baví procházet statistiky návštěvnosti těchto stránek s návody na Excel. Je například zajímavé, jak se návštěvnost mění v průběhu roku a jak se mění v průběhu týdne.

Návštěvnost stránek s návody podle měsíců (2012)


Návštěvnost stránek s návody podle dní v týdnu (2012)


Návštěvnost stránek podle hodin (2012)


Co z toho plyne?

Že prázdniny, Vánoce ani víkendy si Excelem moc kazit nedáme - a to je dobrá zpráva :)
Nejméně lidí se o Excel zajímalo na Štědrý den, nejvíce pak 27.11. 
Listopad navrhuji vyhlásit měsícem Excelu, protože do první pětky se dostaly ještě listopad 26, 6, a 29. 
Nevadí nám ale nad tabulkami sedět do pozdních večerních, nebo dokonce ranních hodin. Děsím se pomyslet na to, o čem se po takovém večeru s Excelem čtenářům stránek v noci zdá...