Vzdálenost dvou měst v Excelu – funkce LAMBDA a datový typ zeměpis

V Excelu 365 jsou na kartě Data dostupné nově tzv. Datové typy, v tuto chvíli Akcie a Zeměpis. Datový typ je možnost, jak hodnotu v buňce proměnit na balíček informací o této hodnotě, např. k názvu města získáme informace o lokalitě, počtu obyvatel či časové zóně. Tyto informace se propojí online s databází na internetu a o vše se postará sám Excel.

Datový typ Zeměpis

Ukážeme si na příkladu dvou měst, jak zjistit jejich zeměpisnou šířku a délku a poté z těchto informací vypočítáme vzájemnou přímou vzdálenost. Nejprve do dvou zvolených buněk zadáme dvě města, např. New York a Sydney. Buňky označíme a na kartě data je převedeme na datový typ Zeměpis. Po chvíli se nám u měst zobrazí ikona s mapou, to je známka, že k propojení došlo. Následně lze získat do vedlejších buněk geografické informace. Tyto informace si ještě před tím můžeme přečíst v ikoně s mapou. Cest je několik, ikona vpravo nahoře nabízí, co by nás mohlo zajímat a vloží to do nejbližší prázdné buňky vpravo, zvolme vlastnost latitude což je zeměpisná šířka. Další údaj, který budeme potřebovat, je zeměpisná délka longitude, tu zkusíme do vedlejšího sloupce napsat vzorcem. Napíši =kliknu na buňku s městem, dále napíši . a Excel našeptá, jaké vlastnosti lze nyní získat. Tyto vzorce se dají zkopírovat za pravý dolní rok i na další města.

Z těchto údajů se dá vypočítat jejich vzájemná vzdálenost, a to pomocí kosinové věty. Musím se přiznat, že sám bych vzorec asi nevymyslel tak rychle, tak jsem sáhl po internetu a jeho tvar je tento. Vypadá to možná šíleně, ale jde jen o to dosadit do správných funkcí naše zjištěné souřadnice a bude to fungovat.

Kosínová věta

V rovnici vidíme funkce ARCCOS, SIN a COS, a místa, kam je třeba dosadit zeměpisnou délku resp. šířku prvního a druhého města. Hodnota R je poloměr koule, v našem případě dosadíme poloměr Země. Rovnice předpokládá, že zem. šířka a délka je v radiánech, ale datový typ nám je dá ve stupních. Všechny naše souřadnice pro tento vzorec je tedy ještě třeba přepočítat na radiány funkcí RADIANS, upravená rovnice by tedy vypadala takto.

Vzorec CZ:
=ARCCOS(SIN(RADIANS(sirka1))*SIN(RADIANS(sirka2))+COS(RADIANS(sirka1))*COS(RADIANS(sirka2))*COS(RADIANS(delka2)-RADIANS(delka1)))*R

Vzorec EN: =ACOS(SIN(RADIANS(sirka1))*SIN(RADIANS(sirka2))+COS(RADIANS(sirka1))*COS(RADIANS(sirka2))*COS(RADIANS(delka2)-RADIANS(delka1)))*R

Nyní už jen tento, trochu šílený, vzorec vložme do buňky, na správná místa dosadím odkazy na buňky zem. šířky a délky jednotlivých měst. A máme vzdálenost, neuvěřitelné že?

Vlastní funkce LAMBDA

Asi souhlasíte, že tvořit takovýto vzorec se nám asi opakovaně nechce. Pojďme si tedy ještě ukázat další vychytávku, a tou je nová funkce LAMBDA. Tato nová funkce umožňuje vytvářet vlastní uživatelské funkce bez programování ve VBA! Vytvoříme si tedy svou vlastní funkci, do které jen dosadíme buňky se zeměpisnými souřadnicemi a o zbytek se postará LAMBDA.

Vlastní funkci si nejprve připravíme v buňce. LAMBDA funguje tak, že si nejprve definujeme, jaké parametry bude naše funkce používat a pojmenujeme si je. Dále zadáme výpočet, který má funkce provést s našimi parametry, sem je třeba dosadit naší kosinovou větu a na místa pro zeměpisné souřadnice umístíme nově pojmenované parametry. Parametr r nechávám jako možnost měnit poloměr koule, kdybychom chtěli někdy v budoucnu počítat vzdálenosti bodů na jiných planetách.:)

Tento vzorec je nyní třeba zkopírovat a aby naše funkce měla jméno, definujeme název na kartě Vzorce > Definovat název. Právě název naší funkce umisťujeme sem a vzorec vložíme do pole odkaz na. A je to. Nyní když v našem sešitu použiji funkci VZDALENOST, nabídnou se mi i parametry, do kterých dosadíme souřadnice a také poloměr Země.

Můžeme zkusit změřit vzdálenost i mezi českými městy, třeba Brno a Ostrava. Když se podívám na internet a vyhledám jejich vzdálenost, sedí to přesně! Když zadáme Prahu, bohužel nám to nebude fungovat, protože z nějakého neznámého důvodu se pro Prahu nenačítají tyto souřadnice, i když další údaje ano, třeba i jméno primátora. No, není to dokonalé, každopádně kdybychom zadali souřadnice ručně, tak by to fungovalo také.

Tipy pro datový typ

Ještě pár rad, pokud bude zeměpisné jméno nepoznáno, zobrazí se u něj otazník. Když na něj klikneme, aktivuje se podokno Výběr dat kde můžeme naše hledání upřesnit. Stejně tak lze toto okno vyvolat u nalezeného místa pravou myší > Datový typ > Změnit. Na světe se mohou některá místa jmenovat stejně, tímto bychom mohli upřesnit správné umístění. Informace se mohou aktualizovat také pravou myší > Datový typ > Aktualizovat. Pro zrušení datového typu, čímž bychom ale přišly o všechny zjištěné údaje, klikneme na možnost Převést na text.