Kombinace SVYHLEDAT a NEPŘIMÝ.ODKAZ

Úvodem

Mám fiktivní společnost, které poskytuje pojistky na kosmická a nadpřirozená vozidla. Zákazník si vybere druh vozidla a následně zadá jeho hmotnost. Na základě hmotnosti a druhu vozidla se přiřadí velikost pojistky (kolik bude osoba platit).

Původní stav, hodnoty se přiřazovaly ručně, ale z důvodu oblíbenosti "fiktivní firmy" je vozidel stále víc a operátor přiřazující platby nestíhá a dělá chyby. Jak tedy eliminovat? Ukážeme si na tomto praktickém příkladu.

Jde o modelový příklad a jakákoliv podobnost se skutečnou firmou co vytváří pojistky je náhodná. Ve skutečnosti hodnota pojistky (pojištění), může zaviset na úplně jiných faktorech než na hmotnosti vozidla.

Co máte k dispozici

Tabulku, kde zadá typ vozidla (zde sem tam dělá v zadání překlep) následně zadá jeho hmotnost a ručně přiřadí hodnotu roční pojistné čátky z tabulky, kde jsou uvedeny typy vozidel, rozsah hmotnosti pro které danná částka platí. Při ručním přepisování ale dochází k překlepům :( .Proto ukažu jak můžete zautomatizovat.

Aneb jak vypadala původní tabulka, které byla doplněna o ověření dat, ať uživatel nemůže udělat v zadání překlep (což ale není součásti tohoto článku). Zde se zaměříme na funkci SVYHLEDAT a NEPŘÍMÝ.ODKAZ

Jak tedy budeme postupovat:

• Upravit data - ať lze využít ve funkcích
• Vytvořit pomocnou tabulku - pro funkci NEPŘÍMÝ.ODKAZ
• Vytvoření funkce pro velikost pojistky - což je cíl
• Ke stažení

Upravit data

Pro praktickou činnost je potřeba vytvořit ze zdrojové tabulky jednotlivé sekce pro dané vozidla. Tak aby v prvním sloupci byla hmotnosti (neboli nějaké číslo, může se jednat i o objem, nebo úplně jinou veličinu). No a v druhém sloupečku bude cena. Opět může obsahovat i další sloupce s poznámkou, přirážkou atd.

Neboli výsledek po přepsaní. Pozor při jaké hodnotě se "láme", nebo-li zda dražší pojistka je už od celočíselné hodnoty např. 2.000 kg, nebo až od hodnoty 2.001 kg....

V ukázce je rozné, protože tka chtěl "zákazník" ;)

Výsledek může vypadat

Mohlo by být pod sebou, ale vím, že společnost půjde i do pojišťování těžších lodí, případně bude sazebník upravovat, tam mám "rozhozeno" do samostatných sekcí takhle v sloupcích.

Vytvořit pomocnou tabulku

Máte vytvořeny jednotlivé sekce pro kosmické lodě a další plavidla, ale teď budete potřebovat tabulku, které se funkci SVYHLEDAT řekne v které oblasti se má hledat (nebo-li funkci SVYHLEDAT se následně pomocí nepřímého odkazu přiřadí správná oblast v argumentu tabulka).

V tabulce půjde jen o seznam typů vozidel (létajicí talíř, ponorka, ...) a oblasti kde se data pro daná vozidla nacházejí.

Výsledek může vypadat

Vytvoření funkce pro velikost pojistky

Vše je připraveno a jen vytvoříte funkci pro přiřazení velikosti pojistky:

=IFERROR(SVYHLEDAT(C26;NEPŘÍMÝ.ODKAZ(SVYHLEDAT(B26;$M$9:$N$12;2;NEPRAVDA));2;PRAVDA);"zkontroluj data")

Nelekejte se vysvětlím:

=*** SVYHLEDAT(B26;$M$9:$N$12;2;NEPRAVDA) ***

Najde na základě zvoleného vozidla ve které oblasti bude nadřazená funkce SVYHLEDAT hledat.

=*** NEPŘÍMÝ.ODKAZ( *** kde se bude hledat *** ) ***

Nepřímý odkaz zajistí že do nadřazené funkce se onen odkaz vloží a tato funkce v argumentu tabulka pochopí jako byste vložily ručně např. U9:W13

= *** SVYHLEDAT(C26; *** jakoby vložen přímo odkaz U9:W13 *** ;2;PRAVDA) ****

Neboli, nahradíte ruční vkládaní vhodné oblasti.

=IFERROR(*** pokud by skončila chybou *** ;"zkontroluj data")

Ještě jsme doplnil o IFERROR, pokud by bylo vloženo neexistující vozidlo (ač nemůže být díky podmíněnému formátování), případně nějaká jiná komplikace.

Výsledek může vypadat

Určitě to nebylo komplikované, pokud se vám přesto nedaří, hotová ukázka je v následující kapitole.

Ke stažení

Soubor Spojení SVYHLEDAT a NEPŘÍMÝ.ODKAZ pro dynamické hledání ke stažení zdarma. Soubor nevyužívá makra.

Tipy na vylepšení

Vylepšit můžete o věk, počet kilometrů bez nehod, vzdálenost od Země, atd. Klidně si s příkladem dále hrajte a upravujte jak je libo.

Závěrem

Jedno z možných řešení, určitě lze úloha řešit i jinak, ale v řešení jsem byl omezen jistými okolnostmi. Navíc jde o ukázkový demonstrační příklad. Proto jakákoliv podobnost se skutečnou firmou je náhodná. Prostě v reklamě jsme četl něco o pojištění aut, tak mě napadlo ukázku namodelovat na tento příklad.

Sice skutečnost byla provedena pro úplně jiné data, ale pojistka je také super ;)

Máte nějaký super tip? Můžete zmínit v komentářích.

Článek byl aktualizován: 19.09.2020 11:00

Pavel Lasák - autor webu

Microsoft Office (Word, Excel, Google tabulky, PowerPoint) se věnuji od roku 2000 (od 2004 na této doméně) - V roce 2017 jsem od Microsoft získal prestižní ocenění MVP (zatím jsem jediný z ČR v kategorií Excel). Své vědomosti a zkušenosti dávám k dispozici i on-line ve videích pro SEDUO. Ve firmách školím a konzultuji, učím na MUNI. Tento web již tvořím přes 15 let. Zdarma je zde přes 1.000 návodu, tipů a triků, včetně přes 250 různých šablon, sešitů.

           

Komentáře zatím nejsou

Můžete být prvními co zanechají smysluplný komentář.