Přeskočit navigaci | Přeskočit na novinky
     

Jste zde: Úvodní stránka » excel » funkce-vyhledavaci » Kombinace-SVYHLEDAT-NEPRIMY-ODKAZ
Microsoft Excel logo

Kombinace SVYHLEDAT a NEPŘIMÝ.ODKAZ

Videokurzy Excel

Ve spolupráci se SEDUO jsem vytvořil několik videokurzů:

Využít SYHLEDAT v přibližné shodě na základě výběru zdrojové tabulky, využitím funkce NEPŘIMÝ.ODKAZ. aneb přiřazování údajů z několika tabulek.

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

Data pro Excel

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

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

Data pro Excel

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

Data pro Excel

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

Výsledek

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

Microsoft Excel VBA - stahuj logo

Ke stažení

Soubor Spojení SVYHLEDAT a NEPŘÍMÝ.ODKAZ pro dynamické hledání soubor ve formátu *.xlsx 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: 21.06.2018 19:41

Odměna

Ušetřil vám tento web čas, peníze? Pomohl vyřešit problém? Jste ochotni poskytnout symbolickou odměnu na další rozvoj? Vybrte si formu odměny, která vám vyhovuje.


Pavel Lasák - autor webu

Pavel Lasák

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

   Pavel Lasák LinkedIn Profil    Pavel Lasák Google+ Profil    Pavel Lasák facebook Profil    Pavel Lasák twitter Profil


Komentáře zatím nejsou

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







Sdílejte

Pomohl Vám návod?
Sdílejte na Facebooku, G+
LinkedIn...

Nové články


TOPlist Licence Creative Commons webarchiv rss XML

Stránky o MS Office (Excel) produktu společnosti Microsoft. Neslouží jako technická podpora.
| Email na autora: pavel.lasak@gmail.com | Copyright © : Pavel Lasák 2004 - 2018 | 554

Tento web zatím neprošel jazykovou korekturou. Beta verze redakčního systému.