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:

http://www.microsoft.com/en-us/download/details.aspx?id=29074

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í.

Nastavení typu oddělovače - středníky versus čárky

Možná jste se s takovýmto problémem už setkali - třeba po instalaci nového počítače.
Chtěli jste napsat funkci v Excelu, a ta nefungovala - protože jste chtěli oddělovat parametry středníkem, jak jste zvyklí, ale Excel vyžadoval čárku.
Jinými slovy toto nefunguje:
=PRŮMĚR(B1;C1;D1)
ale toto funguje:
=PRŮMĚR(B1,C1,D1)
Tento problém je způsobený tím, že v některých státech se používá jako oddělovač parametrů čárka (USA) a někde středník (ČR). U těch prvních se obvykle současně používá jako oddělovač desetinných míst tečka - zatímco u nás čárka.
Řešení není těžké, jen bychom ho marně hledali přímo v Excelu. Je totiž třeba nastavit celé Windows - protože nastavení oddělovacích symbolů je stejné pro všechny aplikace v systému.
Začneme tak, že jdeme na Control panel (Ovládací panel). Ve Windows 8 je tato volba mezi aplikacemi:

Ve Windows 7 přímo ve Start menu:

Další postup je víceméně stejný v obou verzích Windows, printscreeny jsou z osmiček. Vyberu, že chci změnit číselné formáty:

Změním oddělovač seznamu z čárky na středník, a obvykle také oddělovač desetinných míst z tečky na čárku.

Potvrdím a je hotovo.
Mimochodem - záměna oddělovacích znamének je často důvodem, proč vám nefunguje vzorec, který jste si zkopírovali z nějakého amerického fóra nebo návodu. Jsou na něm totiž jako oddělovače použité čárky, zatímco váš český Excel chce středníky.

Ošetření chyb v Excelu

Theodore Roosevelt:
Člověk, který nikdy nedělá chyby, je člověk, který nikdy nedělá nic. 

V jednom z minulých článků jsou popsané druhy chyb (a chybových hlášek), se kterými se v Excelu potkáváme.
V tomto článku se budeme věnovat tomu, jak tyto chyby ošetřit. Ošetření chyb je důležité:
- abychom chyby nepřenášeli z jednoho vzorce do navazujících
- abychom detekovali chyby v datech
- aby naše tabulky vypadaly k světu a nebyly zaplácané podivnými chybovými hláškami
Ošetřením chyby je myšlené to, že chybovou hlášku převedu na srozumitelné varování o chybě nebo na jakýkoliv jiný text, číslo nebo vzorec.

• Funkce, která má na výstupu buď výsledek vzorce, nebo definovanou chybovou hlášku - ta je výstupem, pokud je výstupem vzorce chyba.
  Takováto funkce je v Excelu pouze jedna - IFERROR (česky také IFERROR).
• Funkce, které mají na výstupu TRUE/FALSE (PRAVDA/NEPRAVDA).
  Takovéto funkce jsou v Excelu tři:

1. JE.CHYBA (ISERR) - vrátí hodnotu PRAVDA, pokud je v závorce (parametru funkce) jakákoliv chybná hodnota kromě jedné výjimky - #N/A
2. JE.CHYBHODN (ISERROR) - vrátí hodnotu PRAVDA, pokud je v závorce (parametru funkce) jakákoliv chybná hodnota včetně - #N/A. Od předchozí se tedy liší pouze zachycením #N/A. To je důležité např. pro použití v kombinaci s funkcí SVYHLEDAT/VLOOKUP, kdy si nesmíme plést název této funkce s podobným názvem funkce předchozí.
3. JE.NEDEF (ISNA) - vrátí hodnotu PRAVDA, pokud je v závorce (parametru funkce) chybná hodnota typu #N/A. 

Takže funkce JE.CHYBA (ISERR) a JE.NEDEF (ISNA) dohromady detekují stejné typy chyb, jako samotná funkce JE.CHYBHODN (ISERROR).

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.

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í.

Fígl s kombinací funkcí INDEX a 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í.

Druhy chyb v Excelu

"Vždycky otevřeně přiznej chybu. Ostatní přestanou dávat pozor a umožní ti udělat další."

Mark Twain

V Excelu se nám často stane, že výsledkem vzorce je chyba. Např. pokud dělíme nulou nebo pokud vyhledáváme v oblasti hodnotu, která tam není.
Tyto chyby je obvykle třeba buď odstranit, nebo alespoň ošetřit. Dnes se podíváme na druhy těchto chyba příště na to, jak se s nimi poprat.
Excel rozeznává následující typy chyb a chybových hlášek:

• #NULL! (anglicky také #NULL!)
  Vzniká v situacích, kdy chybí ve vzorečku něco důležitého - např. znaménko. Např. v tomto vzorečku =A1+A2+A3+A4 A5 sčítám hodnoty od A1 do A5, ale před A5 jsem zapomněl napsat plus. 
• #DIV/0! (anglicky také #DIV/0!)
  Tato chyba vyjadřuje, že ve výrazu se dělí nulovou hodnotou - což, jak známo, matematicky nelze. 
• #HODNOTA! (anglicky #VALUE!)
  Značí práci s chybným datovým typem. Například pokud násobíte dvě buňky a v jedné z nich není číslo, ale text.
• #REF! (anglicky #REF!)
  Ve vzorci se odkazuji na oblast sešitu, která byla odstraněna. Např. na odstraněný list, řádek nebo sloupec.
• #NÁZEV? (anglicky #NAME?)
  Použitý špatný název funkce - např. místo "KDYŽ" použito "KDYŽX". Vyskytuje se často, když v anglické verzi použijete český název funkce nebo naopak.
• #ČÍSLO! (anglicky také #NUM!)
  Vzniká, pokud je výsledkem příliš velké nebo příliš malé číslo - tak velké nebo malé, že s ním Excel neumí pracovat. Což se nestává často.
• #N/A (anglicky také #N/A)
  Vzniká při použití vyhledávacích funkcí v případě, že ty nic nenajdou. Např. pokud funkcí SVYHLEDAT / VLOOKUP nenajde hodnotu, kterou jsme zadali do prvního parametru.
• #NAČÍTÁNÍ_DAT (anglicky #GETTING_DATA)
  Chyba vzniká v případě, že se čeká na data ze zdroje, který je zatím neposkytl. Její zobrazení je často jen dočasné.

Funkce INDEX

Funkce INDEX (v češtině i v angličtině nazvaná stejně), má velmi široké použití. A je přitom velmi jednoduchá.

Funkce INDEX hledá hodnoty v buňce, která je na definovaném místě v pořadí v rámci sloupce, řádku nebo oblasti.

Srozumitelněji – funkce INDEX vyhledá např. hodnotu ve třetí buňce ve sloupci. Nebo hodnotu ze druhé buňky z určitého řádku. Anebo, pro definovanou obdélníkovou oblast, hodnotu na průsečíku čtvrtého řádku a druhého sloupce.
Obrázek to vysvětlí jasněji:

Funkce INDEX má svůj protějšek – funkci MATCH (POZVYHLEDAT), která naopak vrátí pořadí určité hodnoty ze seznamu. 

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.

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.