Proč podle Excelu v dubnu umřete na COVID aneb riziko bezmyšlenkovitého prokládání křivek

NezařazenéLeave a comment

Proč podle Excelu v dubnu umřete na COVID aneb riziko bezmyšlenkovitého prokládání křivek

Pojďme si udělat přesný odhad budoucnosti vývoje počtu nakažených, a podívat se, proč je to nesmysl.

Tento graf zachycuje počet nakažených nemocí COVID19 v ČR. Prvním dnem je první březen 2020, kdy se objevily první případy.

Pokud do grafu zkusíte proložit různé typy křivek a zobrazit jejich hodnoty spolehlivosti, pak exponenciální křivka se ukáže jako perfektně vyhovující. Nejen že na první pohled parádně zapadne do grafu, ale ještě má mimořádně vysokou hodnotu spolehlivosti 0,9854.

Co to znamená? Pokud tuto křivku použijeme pro odhad budoucího vývoje (a podle čísel to je zcela oprávněné), pak za další tři dny bude 900 nakažených:

Za deset dní pak 14 000:

No a kolem poloviny dubna jsme na 10 milionech, tedy na počtu obyvatel ČR.

Jistě, můžeme se utěšovat tím, že smrtnost je v jednotkách procent a neumírá se hned po nakažení, přesto se podle této křivky dožijí května jen ti nejštastnější (kteří umřou někdy mezi dvěma květnovými svátky).

Jaké z tohoto plyne ponaučení?

Odhadovat budoucnost jen z čísel, bez zohlednění další informací, je nesmysl. V našem případě se dá čekat, že zafungují některá vládní opatření (byť zatím působí asi stejně systematicky jako skupina opilých makaků skládajících puzzle), že se někteří lidé vyléčí atd.

Nové „Dynamic array“ funkce v Excelu

NezařazenéLeave a comment

Nezdá se to, ale v Excelu se děje malá revoluce. Do Excelu 365 přichází skupina funkcí, která se chová úplně jinak než jsme zvyklí.

Dosud to fungovalo tak, že když jste zapsali do buňky vzorec, objevil se v ní výsledek. Nové funkce fungují tak, že do buňky zapíšete vzorec, a někde "kolem" se objeví výsledky. To ovšem přináší netušené nové možnosti.

Tyhle tři nové funkce budou asi nejpoužívanější. 

Funkce FILTER

Funkce FILTER ukáže tabulku odfiltrovanou podle jednoho ze sloupečků.

Funkce SORT

Funkce SORT umí tabulku seřadit vzestupně nebo sestupně.

Funkce UNIQUE

Funkce UNIQUE odebere, jak byste asi čekali, duplicitní hodnoty.

Funkce XLOOKUP

XLOOKUP propojuje dvě tabulky - podobně jako SVYHLEDAT, ale šikovněji.

Další dynamické funkce

V tomto článku nejsou zmíněné další funkce tohoto typu, které se Vám mohou někdy hodit: RANDARRAY, SORBY nebo SEQUENCE,

Top 10 nejnavštěvovanějších stránek na Exceltown.com

NezařazenéLeave a comment

Top 10 nejnavštěvovanějších stránek na Exceltown.com

Tak takhle to vypadalo s návštěvností návodů na Exceltown.com?

Některé mě nepřekvapují (SVYHLEDAT, kontingenční tabulky...) jiné hodně (hlavně vítěžné LOOKUPVALUE). Je také vidět, že uživatelé s angličtinou mají dost jiné chutě než ti s češtinou...

  1. LOOKUPVALUE – assigning of values from other table without relation (DAX – Power Pivot, Power BI)
  2. SUMMARIZE – groupping in data models (DAX – Power Pivot, Power BI)
  3. SVYHLEDAT / VLOOKUP – funkce pro propojování více tabulek
  4. Základní návod pro vytvoření kontingenční tabulky
  5. Procenta a jejich počítání v Excelu
  6. Makro v Excelu – základní základ
  7. SAMEPERIODLASTYEAR – comparison with previous year (DAX – Power Pivot, Power BI)
  8. Jak zapisovat znaky < > & $ * ^
  9. Lineární regrese v Excelu
  10. GROUPBY – aggregations in data model using DAX (DAX – Power Pivot, Power BI)

Docela těžká Power BI hádanka

Nezařazené4 komentáře

Docela těžká Power BI hádanka

Dovolím si nabídnout jeden úkol na rozluštění. Vypadá jednoduše, ale zase tak triviální není.

Vyjdeme ze situace, kdy máme MS SQL server s tabulkou, která obsahuje 10 sloupců a cca 80 000 000 řádků (je tedy docela dlouhá).

Power BI desktop (nebo obdobně Power Query v Excelu) se do této tabulky na MS SQL připojuje a provádí tři kroky - filtruje, řadí data, seskupuje data a odebírá poslední řádek. Vše je uloženo i načítáno jako texty.

Pokud jsou kroky seřazené takto (filtrování / seskupení / seřazení / odebrání posledního řádku):

trvá načítání cca 40 vteřin.

Pokud jsou seřazené takto (odebrání posledního řádku / filtrování / seskupení / seřazení):

trvá načítání desítky minut až hodiny (resp. neměl jsem trpělivost to měřit...:).

Otázka zní - proč tomu tak je a kde se bere tak dramatický rozdíl? Napíše někdo do diskuse správnou odpověď?

Poznámky:

  • Oba dotazy jsou zcela totožné, rozdíl je pouze v pořadí kroků.
  • Ano, vím že výsledky dotazů budou maličko odlišné - protože trochu záleží na tom, kdy se poslední řádek odebere. Pojďme to ale přejít a věnovat se rychlosti.
  • Kódy M vypadají takto pro rychlé načtení:
    • let
      Source = Sql.Databases("DESKTOP-8L3P34M"),
      pbi = Source{[Name="pbi"]}[Data],
      dbo_combined = pbi{[Schema="dbo",Item="combined"]}[Data],
      #"Filtered Rows" = Table.SelectRows(dbo_combined, each [Key] <> "Key"),
      #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Key"}, {{"Count", each Table.RowCount(_), type number}}),
      #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Ascending}, {"Key", Order.Ascending}}),
      #"Removed Bottom Rows" = Table.RemoveLastN(#"Sorted Rows",1)
      in
      #"Removed Bottom Rows"
  • a takto pro pomalé:
    • let
      Source = Sql.Databases("DESKTOP-8L3P34M"),
      pbi = Source{[Name="pbi"]}[Data],
      dbo_combined = pbi{[Schema="dbo",Item="combined"]}[Data],
      #"Removed Bottom Rows" = Table.RemoveLastN(dbo_combined,1),
      #"Filtered Rows" = Table.SelectRows(#"Removed Bottom Rows", each [Key] <> "Key"),
      #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Key"}, {{"Count", each Table.RowCount(_), type number}}),
      #"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Count", Order.Ascending}, {"Key", Order.Ascending}})
      in
      #"Sorted Rows"
  • Server je na stejném počítači jako Power BI Desktop, do kterého načítáme. Připojení klasicky importuje data - není to DirectQuery.

Tak kdo to dá? :) 

Co se v Excelu NEučit, protože to za to nestojí?

Nezařazené2 komentáře

Co se v Excelu NEučit, protože to za to nestojí?

V Excelu je spousta témat, která když se naučíte, zjednoduší vám práci.

V tomto článku je ale přehled věcí, které jsou podle mě celkem na nic - nebo přesněji, jejich využití je zcela minimální.

Čím začít? Tak třeba...

Maticové vzorce. 

Skoro všechno, co se s nimi počítá, se dá počítat jednodušeji - např. pomocí SUMIFSu, COUNTIFSu. Pokud tyto funkce nestačí, může na řadu přijít DAX, kde se dá spočítat opravdu všechno a troufnu si tvrdit že jednodušeji než maticí.

Jedinou výjimkou je asi funkce TRANSPOZICE - tam nevím o žádném způsobu, jak ji nahradit pokud chcete vzorečkem "převracet" tabulku.

Formuláře

V Excelu můžete mít formuláře jako ActiveX prvky, formuláře jako sadu ovládacích prvků umístěnou na listu nebo formuláře jako formuláře - automatický nástroj pro vyplňování tabulky (ty jsou tak schované, že o nich nejspíš ani nevíte). 

A ani jedno z toho nejde moc dobře používat. První dva proto, že jsou hrozně komplikované na tvorbu, třetí proto že nic neumí. Nakonec bude vždycky rychlejší zapisovat údaje přímo do tabulky. A nebo, když už, použít spíš online formulář.

Scénáře

Scénáře umožňují vytvořit přednastavené situace, měnit je a sledovat dopad na výsledek.

Ale upřímně - obdobného výsledku se dá často dosáhnout mnohem jednodušeji pomocí vzorečků s podmínkami.

Souhrny

Souhrny umožňují seskupit data v tabulce a vysčítat je. 

Proč tedy souhrny skoro nikdo nepoužívá?

Protože tabulku s výsledky musíte na začátku seřadit a pak pořád udržovat seřazenou.

A protože kontingenční tabulky jsou mnohem snadnější a rychlejší.

VBA

Teď trochu kontroverze  - Visual Basic for Applications.

Bylo by to asi na delší dobu a je pravda, že některé důvody hrají pro VBA - především to, že stále existují situace, které prostě jinak nevyřešíte.

Proč tedy radím se VBA neučit?

Tak zaprvé je v některých firmách prostě zablokovaný z důvodů bezpečnosti. Za druhé nefunguje v cloudu a většina uživatelů už v cloudu pracovat chce.

Ale hlavně - abyste ve VBA napsali něco smysluplného, musíte ho fakt dobře umět a stejně to zabere nechutně moc času. Doporučuji naklikat to samé v Power Query (skoro vždycky to jde) a ušetřený čas strávit něčím příjemnějším.

KPI v Power Pivotu

A nakonec něco pro analytiky - KPIčka v Power Pivotu jsou fakt na nic.

Dají se jednoduše nahradit mírou a podmíněným formátováním, budete to mít jednodušší a o nic důležitého nepřijdete.

Co se tedy učit místo toho?

Power Query, Power Pivot (ano, bez KPIček...t nebo případně Power BI. Protože tihle kamarádi vám ušetří opravdu hodně času.

Pokud jste se se světem Power zatím nesetkali, doporučuji začít s Power Query - je nejrychleji (a opravdu velmi rychle) zvládnutelné.

Měli jsme tu šikovného kluka, co na všechno udělal makro…

NezařazenéLeave a comment

Měli jsme tu šikovného kluka, co na všechno udělal makro...

Na tenhle scénář narážím ve firmách odhadem tak jednou za měsíc.

Fáze 1 - Ve firmě se pracuje s mnoha Excely a je s tím moc práce

Fáze 2 - Najde se šikovný kluk, kterého baví makra. Postaví jeden nebo několik superhypervytuněných Excelů, které obsahují několik tlačítek, ActiveX prvků, formulářů a za tím kilometry VBA kódu. Je tam spousta propojení, vazeb, kontrol. A vcelku dobře to funguje. Občas to trochu zazlobí, ale tvůrce se trochu prohrabe v kódu, něco málo upraví a zase je všechno v pořádku.

Fáze 3 - ŠIkovný kluk odejde.

Fáze 4 - Pár týdnů to ještě všechno nějak funguje. Ale pak se někde přejmenuje soubor nebo sloupec a nefunguje nic. S VBA kódem nikdo neumí pracovat. V tom okamžiku jsme často osloveni my, jestli bychom ten soubor neuměli nějak zprovoznit a udržovat. A my řekneme, že neuměli, resp. uměli, ale bude to stát strašně moc času a tím pádem to nebude nejlevnější. A že by asi bylo levnější udělat to celé znovu - protože jestli je něco opravdu časově náročné, tak je to prohrabování cizího komplikovaného (a z 99% nezdokumentovaného nebo špatně zdokumentovaného) kódu.

Chcete také jednou zjistit, že vaše Excely nefungují a nikdo jim nerozumí? Pokud nechcete, doporučil bych:

  • Nevytvářet v Excelu monstrózní řešení - udělejte spíš několik relativně jednoduchých, dílčích nástrojů. Pro komplexní "velké" aplikace existují mnohem lepší technologie než Excel. Já vím, ono to láká, člověk si začne hrát s VBA, pak přidává další a další možnosti a nástroje... ale chce to nepodlehnout. 
  • Nenechat jednoho člověka, aby všemu rozuměl sám. Rizika jsou myslím zřejmá.
  • Používat VBA jen pokud je to nevyhnutelné - lepší jsou funkce a Power Query (já vím, ne vždy se dá VBA nahradit, ale velmi často ano...). Většina věcí, které se ve VBA vytváří hodně složitě, zdlouhavě a komplikovaně, se dá v Power Query naklikat za chvilku. A to, co jeden uživatel vytvoří, může jiný uživatel snadno a rychle pochopit a doladit - protože Power Query se naučí neskonale rychleji než VBA.

Změna syntaxe funkce LOOKUPVALUE

NezařazenéLeave a comment

Je celkem neobvyklé, aby se měnily syntaxe funkcí "za pochodu" - resp. v prostředí Microsoftu si na podobný případ nevzpomínám.

Funkce LOOKUPVALUE je výjimkou. S březnovou aktualizací Power BI dostala nový volitelný parametr - hodnotu, která určuje, co se má zobrazit při nenalezení shody.

Je to docela zajímavý princip - pokud by se tento argument dostal i do funkce SVYHLEDAT v Excelu, odpadly by komplikované kombinace s IFERRORem nebo IFNA.