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.

Sedmá hádanka - strategická (řízení portfolia zákazníků)

Představte si firmu, která si chce vybrat nejvýše 10 perspektivních klientů a s těmi udržovat dlouhodobé vztahy. Na výběr má z dvaceti potenciálních klientů, kteří přicházejí v úvahu - a je důležité vybrat je tak, aby nakonec přinášeli co nejvíce peněz.

• U každého zákazníka lze čekat v budoucnu určitý zisk z prodejů výrobků. Tento zisk jsme schopni odhadnout a je vypsaný v tabulce dole. 
• Udržování sítě zákazníků ale firmu stojí určité prodejní náklady - protože v každé zemi, kde je alespoň jeden zákazník, musí firma udržovat pobočku. Jedna standardní pobočka bude stát (za stejné období, za které jsou odhadované zisky) 6 000 000 Kč. V případě, že bude obsluhovat alespoň tři klienty, se bude jednat o posílenou pobočku a ta bude stát 15 000 000 Kč.

Otázka tedy zní:

• Které zákazníky si vybereme a kolik korun s nimi v příštím období vyděláme po odečtení nákladů za provoz poboček? 

Časová náročnost úkolu je do pěti minut.
Tato hádanka by šla možná vyřešit i ručně, resp. odhadem. Chceme ale najít řešení, které by bylo možné použít i na podstatně větší množství klientů a zemí, a kde bude stačit měnit vstupní parametry (informace o zákaznících a nákladech poboček) a zjišťovat dopady na výsledek.
Další hádanky si můžete vyzkoušet tady.

Informace o klientech:

Číslo zákazníka	Odhad tržeb příští rok	Země
1	8 000 000 Kč	ČR
2	5 000 000 Kč	SR
3	7 000 000 Kč	ČR
4	4 000 000 Kč	Polsko
5	5 000 000 Kč	Polsko
6	2 000 000 Kč	Německo
7	7 000 000 Kč	Švýcarsko
8	6 000 000 Kč	Německo
9	4 000 000 Kč	ČR
10	4 000 000 Kč	SR
11	4 000 000 Kč	ČR
12	1 000 000 Kč	SR
13	2 000 000 Kč	Německo
14	7 000 000 Kč	Německo
15	4 000 000 Kč	ČR
16	1 000 000 Kč	SR
17	4 000 000 Kč	ČR
18	1 000 000 Kč	SR
19	2 000 000 Kč	Německo
20	7 000 000 Kč	Německo

Funkce POSUN/OFFSET

Existuje jedna funkce, která umožňuje odkazovat se na buňky nepřímo.
Její fungování se popisuje docela složitě, ale na příkladě je to snad zřejmé.

• V buňce I4 se odkazuji na buňku, která je od buňky B3 sedm řádků dolů a dva řádky doprava. Tedy prvním parametrem funkce je původní buňka, druhým parametrem je posun dolů (nebo nahoru při záporné hodnotě parametru) a třetím parametrem je posun doprava (nebo doleva při záporné hodnotě parametru).
• V buňce I5 sčítám hodnoty buněk v určité oblasti. Jedná se o oblast, která začíná od buňky B3 (první parametr) dva řádky dolů a tři sloupce doprava (druhý a třetí parametr) a je velká dva krát dvě buňky (čtvrtý a pátý parametr).
  V tomto případě má funkce pět parametrů oproti třem parametrům v předchozím případě. V případě, že se odkazuji na oblast, musím funkci POSUN/OFFSET použít pouze jako "obalenou" v jiné agregační funkci. Tak, aby z celé oblasti mohlo vypadnout jen jedno číslo. 

Je zřejmé, že tato funkce nemá žádné použití sama o sobě - v příkladu bych místo =OFFSET(B3;7;2) mohl napsat =D10. Přínos funkce ale oceníme v případě, že se poloha odkazované buňky nějakým způsobem mění - a pak mohu např. z jiné buňky zadané jaké druhý nebo třetí parametr odkazovat, ze které buňky se hodnota bere.

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?

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

SUBTOTAL - funkce důležitá pro filtrování

V tomto příspěvku se trochu podíváme na funkci SUBTOTAL (česky také SUBTOTAL).
Microsoft nám v tom dělá trochu lingvistický nepořádek - v anglické verzi používá název SUBTOTAL jak pro funkci, které se věnujeme v tomto příspěvku, tak pro to, co se česky nazývá Souhrn.
Funkce SUBTOTAL se používá v kombinaci s automatickým filtrem.

Příklad

Mám tabulku s daty, např. v bazaru.
Chtěl bych používat filtr a současně sledovat některé charakteristiky dat. Například chci filtrovat podle barvy a sledovat celkové ceny za jednotlivé barvy.
Ještě jinými slovy - až vyfiltruji modrá auta, chci vidět celkovou cenu modrých aut, a až to změním na červenou, tak cenu červených aut.

Návod

V tabulce použiji standardní filtr.
Pak do jedné z buněk zapisuji funkci SUBTOTAL. Je důležité zapisovat ji do buňky, která se nebude při filtrování "schovávat" - v mém případě tedy píši do prvního řádku.
Funkce má dva parametry.

• Prvním je číslo, které určuje funkci použitou pro sečtení/zprůměrování/jinou agregaci dat. Např.:
• 1 - Průměr
• 2 - Počet
• 9 - Součet

Čísla si samozřejmě nemusím pamatovat - mohu využít rozbalovací nápovědu Excelu.

• Druhým parametrem je sloupec, který se má počítat.

Zápis celé funkce v našem případě vypadá takto:
=SUBTOTAL(9;D:D)
Devítka proto, že jde o součet, D znamená sloupec který se sčítá.

Důležité je, že oproti standardnímu součtu se tento součet při používání filtru mění.