1. Nestandardní techniky práce s vyhledávacími funkcemi

Excel Asistent Magazín 05/2003 Funkce pro vyhledávání v seznamech 2/2 – postupy, které vám možná zamlčeli Jiří Číhař, Dataspectrum http:// //www.dataspectrum.cz // ( nedílnou součástí tohoto magazínu je i soubor ExcelAsistentMagazin052003.xls obsahující příklady použité v tomto magazínu. Tento soubor si můžete stáhnout z adresy http://www.dataspectrum.cz/excelmag/download/eam0503x.zip)

ExcelAsistentMagazín je určen k volnému šíření. Pokud Vás jeho obsah zaujal, zašlete jej prosím svým kolegům a přátelům.

EXCEL ASISTENT MAGAZÍN 05/2003............................................................................................ 1 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19.

NESTANDARDNÍ TECHNIKY PRÁCE S VYHLEDÁVACÍMI FUNKCEMI ................................................. 2 SEZNAMME SE S FUNKCEMI POSUN A NEPŘÍMÝ.ODKAZ........................................................... 2 FUNKCE POSUN – ZDATNÝ POMOCNÍK PŘI PROCHÁZENÍ SEZNAMŮ ............................................... 2 NEPŘÍMÝ.ODKAZ – IDEÁLNÍ NÁSTROJ PRO MANIPULACI S ODKAZY V NAŠICH FUNKCÍCH ......... 4 VYHLEDÁVÁNÍ HODNOT V ZAVŘENÉM SEŠITU ................................................................................ 5 VYHLEDÁVACÍ FUNKCE V KOMBINACI S MATICOVÝMI VZORCI ...................................................... 6 DALŠÍ KOUZLA S OZNAČENÍM SLOUPCŮ .......................................................................................... 7 VYHLEDÁVÁNÍ VLEVO ..................................................................................................................... 7 NALEZENÍ NEJBLIŽŠÍ HODNOTY MÍSTO HODNOTY PŘIBLIŽNÉ .......................................................... 8 VYHLEDÁVÁNÍ S ROZLIŠOVÁNÍM VELKÝCH A MALÝCH PÍSMEN ................................................... 9 VYHLEDÁVÁNÍ S VYUŽITÍM ZÁSTUPNÝCH ZNAKŮ ....................................................................... 10 VNOŘENÉ VYHLEDÁVACÍ FUNKCE ............................................................................................... 11 REVERZNÍ VYHLEDÁVÁNÍ ............................................................................................................ 13 VYHLEDÁVACÍ FUNKCE VYHLEDAT – POUŽÍT NEBO ZAPOMENOUT? ....................................... 14 VYHLEDÁVACÍ FUNKCE VRACÍ CHYBU #N/A – CO MÁM DĚLAT?................................................. 16 VYHLEDÁVACÍ FUNKCE V PROSTŘEDÍ VBA ................................................................................ 18 PRŮVODCE VYHLEDÁVÁNÍM ........................................................................................................ 19 NĚKOLIK PŘÍKLADŮ NA DOMÁCÍ CVIČENÍ.................................................................................... 22 CO NÁS ČEKÁ V PŘÍŠTÍM POKRAČOVÁNÍ ...................................................................................... 24

1 www.dataspectrum.cz

1.

Nestandardní techniky práce s vyhledávacími funkcemi

V minulém pokračování magazínu jsme se seznámili s vyhledávacími funkcemi, které nám umožňují efektivně zpracovávat seznamy a databáze uložené v excelských listech. Naučili jsme se používat v běžných úlohách funkce SVYHLEDAT, VVYHLEDAT, POZVYHLEDAT a INDEX. V závěru minulé části jme se rozcházeli s příslibem věnovat toto pokračování ne příliš známým technikám se zaměřením na • vyhledávání dat v uzavřeném sešitu • vnoření vyhledávacích funkcí • použití maticových konstant ve vyhledávacích vzorcích a maticový zápis vyhledávacích vzorců • použití zástupných znaků při hledání hodnot • nalezení nejbližší hodnoty místo hodnoty přibližné • použití vyhledávacích funkcí v kódech jazyka VBA • rozšíření možností vyhledávacích funkcí pomocí funkce NEPŘÍMÝ.ODKAZ • zrychlení procesu vyhledávání dat • využití průvodce vyhledáváním V tomto čísle ExcelAsistentMagazínu se nebudeme vracet k základním pojmům a technikám probíraným v první části, a zaměříme se na představení příkladů, v nichž se naučíme používat vyhledávací funkce nestandardním ( a nadstandardním ) způsobem. Protože ovládnutí vyhledávacích funkcí patří mezi nejdůležitější a nejoceňovanější dovednosti pokročilého uživatele aplikace Excel, budeme se i v dalších pokračováních ExcelAsistentMagazínu k této problematice příležitostně vracet.

2.

Seznamme se s funkcemi POSUN a NEPŘÍMÝ.ODKAZ

Funkce POSUN a NEPŘÍMÝ.ODKAZ jsou často používány jako doplněk k ostatním vyhledávcím funkcím v případech, kdy potřebujeme vytvářet složitější řešení založené na těchto funkcích. V následujících řádcích si tyto nové funkce představíme a naučíme se je používat na několika příkladech.

3.

Funkce POSUN – zdatný pomocník při procházení seznamů

Definice této funkce říká: Vrátí odkaz na oblast, která obsahuje určený počet řádků a sloupců, od určité buňky nebo oblasti buněk. Vrácený odkaz může být jedna buňka nebo oblast buněk. Počet řádků a sloupců, které se mají vrátit, můžete určit. Syntaxe POSUN(odkaz;řádky;sloupce;výška;šířka) 2 www.dataspectrum.cz

Odkaz je původní odkaz, vůči kterému provádíte posun. Pokud odkaz neodkazuje na buňku nebo oblast sousedících buněk, vrátí funkce POSUN chybovou hodnotu #HODNOTA!. Řádky je počet řádků, o které se má posunout levá horní buňka nového odkazu (nahoru nebo dolů). Zadáte-li například číslo 5, levá horní buňka odkazu bude pět řádků pod levou horní buňkou původního odkazu. Můžete použít kladnou (dolů od původního odkazu) nebo zápornou hodnotu (nahoru od původního odkazu). Sloupce je počet sloupců vlevo nebo vpravo, o které se má posunout levá horní buňka výsledného odkazu vzhledem k původnímu odkazu. Zadáte-li například číslo 5, bude levá horní buňka odkazu o pět sloupců vpravo od levé horní buňky původního odkazu. Můžete použít kladnou (posun doprava od původního odkazu) i zápornou hodnotu (posun doleva od původního odkazu). Výška je požadovaná výška (počet řádků) výsledného odkazu. Výška je vždy kladné číslo. Šířka je požadovaná šířka (počet sloupců) výsledného odkazu. Šířka je vždy kladné číslo. Komentář • • •

Přesáhne-li takto posunutý odkaz okraje listu, vrátí funkce POSUN chybovou hodnotu #REF!. Vynecháte-li argument výška nebo šířka, implicitně se dosadí výška nebo šířka původního odkazu. Funkce POSUN ve skutečnosti žádné buňky nepřesunuje ani nemění označenou oblast; pouze vrátí hodnotu typu odkaz. Funkci POSUN lze použít ve spojení s libovolnou funkcí, která očekává argument typu odkaz. Například pomocí vzorce SUMA(POSUN(C2;1;2;3;1)) se vypočítá celková hodnota oblasti o třech řádcích a jednom sloupci, která je umístěna 1 řádek pod a 2 sloupce vpravo vzhledem k buňce C2.

Následující obrázek názorně ukazuje chování funkce:


4.

NEPŘÍMÝ.ODKAZ – ideální nástroj pro manipulaci s odkazy v našich funkcích

NEPŘÍMÝ.ODKAZ - vrátí odkaz určený textovým řetězcem. Odkazy jsou okamžitě vyhodnocovány a je zobrazován jejich obsah. Funkci NEPŘÍMÝ.ODKAZ použijte, pokud chcete změnit odkaz na buňku v rámci vzorce a nechcete měnit vzorec. Syntaxe NEPŘÍMÝ.ODKAZ(odkaz;a1) Odkaz je odkaz na buňku obsahující další odkaz typu A1 nebo R1C1, název definovaný jako odkaz nebo odkaz na buňku ve tvaru textového řetězce. Pokud hodnota argumentu odkaz neodpovídá platnému odkazu na buňku, vrátí funkce NEPŘÍMÝ.ODKAZ chybovou hodnotu #REF!.Pokud argument odkaz odkazuje na jiný sešit (externí odkaz), musí být daný sešit otevřen. V opačném případě vrátí funkce NEPŘÍMÝ.ODKAZ chybovou hodnotu #REF!. A1 je logická hodnota, která určuje typ odkazu. • •

Je-li hodnota argumentu a1 PRAVDA nebo je-li tento argument vynechán, je odkaz interpretován jako adresa typu A1. Je-li hodnota argumentu a1 NEPRAVDA, je odkaz interpretován jako adresa typu R1C1.

Funkce NEPŘÍMÝ.ODKAZ umožňuje adresovat i buňky v různých listech sešitu. Můžeme pak načítat hodnoty v různých listech prostým přepsáním názvu listu v buňce, na kterou se funkce odkazuje.


A 1 2 3 4 5 6 7 8 9 10 11 12

B test1 A1 1 =NEPŘÍMÝ.ODKAZ(B2&"!"&B3) =NEPŘÍMÝ.ODKAZ("test1!A1") test2 A1 50 =NEPŘÍMÝ.ODKAZ(B8&"!"&B9) =NEPŘÍMÝ.ODKAZ("test2!A1")

Při odkazování na list je důležité mezi označení listu a adresu buňky vložit znak vykřičník, jinak odkaz nebude vyhodnocen správně. V případě, že označení listu obsahuje mezeru, je nutné před a za tento název vložit apostrof ( ANSI kód 39). A 1 2 3 4 5

B test tři A1 200 =NEPŘÍMÝ.ODKAZ(ZNAK(39) & B2 & ZNAK(39) & "!"& B3) =NEPŘÍMÝ.ODKAZ( "'test tři'!A1")

5.

Vyhledávání hodnot v zavřeném sešitu

Velkou výhodou vyhledávacích funkcí je jejich schopnost vracet hodnoty i ze zavřeného sešitu. To znamená, že máme možnost vytvořit vzorec, který se odkazuje na seznam v jiném sešitu – a pak tento sešit zavřít. Funkce dokáže vracet hodnoty i nadále a reagovat na změny parametrů. Sešit přitom vůbec nemusí být otevřen a ani si jej funkce neotevírají, vnitřním mechanismem Excelu dokáží pracovat se seznamy tak, jako by sešit zůstal otevřený. Pokud potřebujeme sešit, na který se vyhledávací funkce odkazuje, přemístit, musíme jej nejdříve otevřít v Excelu a pak jej přemístit přímo z prostředí Excelu. Pokud bychom sešit přemisťovali jiným způsobem ( např. pomocí průzkumníka) došlo by k roztržení vazby a funkce by přestala vracet hodnoty z tohoto sešitu


Funkce NEPŘÍMÝ.ODKAZ nás však zklame – v případě zavřených sešitů ji použít nemůžeme.

6.

Vyhledávací funkce v kombinaci s maticovými vzorci

Problematice maticových vzorců bylo věnováno celé 3. číslo ExcelAsistentMagazínu. V tomto odstavci tedy budeme předpokládat, že čtenář je obeznámen s problematikou tohoto typu vzorců a na tomto místě pouze připomenu, že jednou z předností maticových vzorců je jejich schopnost nahrazovat více vzorců klasických ( tj. nematicových) a tím urychlovat a zjednodušovat výpočty. Způsob použití si představíme na konkrétním příkladu: Úkolem je vypočítat celkový prodej konkrétního výrobku. Standardní postup spočívá v použití dvou funkcí SVYHLEDAT – jedné pro nalezení jednotkové ceny, druhé pro nalezení počtu prodaných kusů a následného vynásobení těchto dvou hodnot. Maticový vzorec nám umožní provést výpočet v rámci jediného vzorce za předpokladu, že SVYHLEDAT vrátí obě hledané hodnoty ( vynásobení zajistí funkce SOUČIN). Místo jediného označení sloupce zadáme dvojici hodnot uzavřenou ve složených závorkách, čímž dáme Excelu najevo, že má pracovat s maticí hodnot.


Část vzorce {2;3} zapíšeme „ručně“ – složené závorky např. pomocí CTRL+ALT+B a CTRL+ALT+N. Vnější složené závorky doplní Excel poté, co potvrdíme vložení maticového vzorce kombinací kláves CTRL+SHIFT+ENTER. Pomocí částečného přepočtu vzorce ( vybereme část vzorce v řádku vzorců a stiskneme F9) vidíme, že vnitřní část SVYHLEDAT(A15;A1:C5;{2;3};0) vrací matici {100;50}, která je použita jako argument pro funkci SOUČIN.

7.

Další kouzla s označením sloupců

V předchozím příkladu jsme si ukázali, že nemusíme zadávat označení sloupce vždy ve formě jediného čísla. Označení číslem s sebou nese záludnost, protože se nám může v případě rozsáhlejší tabulky stát, že se při počítání sloupců spleteme. Pomoc je v tom, že namísto pořadového čísla sloupce zadáme jeho označení formou písmen uvnitř funkce SLOUPEC.

.

8.

Vyhledávání vlevo

Jedním z omezení funkcí SVYHLEDAT a VVYHLEDAT je neschopnost vracet hodnoty vlevo od klíčové hodnoty. Následující příklad nelze pomocí funkce SVYHLEDAT vyřešit. Zápis funkce je chybný a proto neobdržíme požadovaný výsledek: 7 www.dataspectrum.cz

Řešení však samozřejmě existuje – je založeno na kombinaci funkcí POSUN a POZVYHLEDAT.

funkce POZVYHLEDAT vrátí číslo řádku, na kterém se vyskytuje hledaná hodnota 58241 – v našem případě to je číslo 3. POSUN vrátí odkaz na buňku, která je umístěna od základní buňky B1 2 řádky dolů a jeden sloupec vlevo. 2 řádky dolů získáme tak, že použijeme hodnotu vrácenou funkcí POZVYHLEDAT sníženou o 1 – to proto, že POSUN počítá řádky nikoliv od základní buňky, ale od řádku ležícím pod ní ( hodnotu 1 má „jeden řádek pod základní buňkou“) Řešení je možné založit i na použití funkce VYHLEDAT – nejdříve se však musíme naučit znát její chování ( viz další text)

9.

Nalezení nejbližší hodnoty místo hodnoty přibližné

Vyhledávací funkce obecně dokáží mimo přesné hodnoty nalézt i největší nižší hodnotu k hodnotě hledané. Podmínkou je vzestupné seřazení seznamu a specifikování argumentu pro shodu na hodnotu různou od nuly. V některých případech však můžeme potřebovat nalézt místo největší nžší hodnoty hodnotu nejbližší, tedy hodnotu, která se od hodnoty hledané liší co nejméně. Co mám na mysli? 1 2 3 4 5 6 7

A Data

B 11 6 15 7 12 8

C

D

vyhledávaná hodnota: nejvyšší nižší: nejbližší:

14 12 15

Vyhledávací funkce SVYHLEDAT by po setřídění dat vrátila při nastavení argumentu shoda na hodnotu 1 honotu 12 – je to NEJVĚTŠÍ NIŽŠÍ hodnota k hodnotě 14. Pokud chceme nalézt hodnotu nejbližší, můžeme postupovat více způsoby: 1. dvkrát použít funkci POZVYHLEDAT, která umí nalézt jak největší nižší hodnotu, tak i nejnižší větší hodnotu k hodnotě vyhledávané. Musíme však data setřídit jednou vzestupně a ve druhém případě sestupně. Je pak třeba nalézt obě hodnoty a pak je


porovnat proti hodnotě vyhledávané s cílem zvolit z této dvojice tu s nejnižším absolutním rozdílem vzhledem k vyhledávané hodnotě. 2. použití vzorce {=INDEX(A2:A7;POZVYHLEDAT(SMALL(ABS(A2:A7D2);1);ABS(A2:A7-D2);0))} 3. použití kombinace vyhledávacích funkcí a funkce ABS vracející absolutní hodnotu v kombinaci s maticovým vzorcem ve tvaru {=NEPŘÍMÝ.ODKAZ(ODKAZ(ŘÁDEK(A2:A7)+POZVYHLEDAT(MIN(ABS(D 2-A2:A7));ABS(D2-A2:A7);0)-1;SLOUPEC(A2:A7)))} Vzorec vyhledává minimální rozdíl mezi hodnotami v seznamu a vyhledávanou hodnotou. Pak určí číslo řádku a sloupce, ve kterém se nachází tato minimální hodnota a pomocí NEPŘÍMÝ.ODKAZ ji zapíše. 1. ABS(D2-A2:A7) vrací absolutní rozdíl mezi hledanou hodnotou 14 a hodnotami ve sloupci A: {3|8|1|7|2|6} 2. MIN(ABS(D2-A2:A7)) vrací minimum z matice absolutních rozdílů: 1 3. POZVYHLEDAT(MIN(ABS(D2-A2:A7));ABS(D2-A2:A7);0) vrací pořadové číslo mimima v matici abolutních rozdílů: 3 4. ŘÁDEK(A2:A7) vrací čísla řádků obsahující data ve sloupci A: {2|3|4|5|6|7} 5. ŘÁDEK(A2:A7)+POZVYHLEDAT(MIN(ABS(D2-A2:A7));ABS(D2-A2:A7);0)1) vrací matici představující čísla řádků od řádku obsahujícího nalezené minimum v počtu řádků obsahujících data:{4|5|6|7|8|9} 6. SLOUPEC(A2:A7) vrací číslo sloupce obsahujícího data: 1 7. ODKAZ(…) vrací matici adres oblasti buněk počínaje nalezeným minimem : {"$A$4"|"$A$5"|"$A$6"|"$A$7"|"$A$8"|"$A$9"} 8. NEPŘÍMÝ.ODKAZ vrací matici hodnot s počátkem v nalezeném minimu: {15|7|12|8|0|0}. 9. Vzhledem k tomu, že maticový vzorec je zapsán do jediné buňky, je z přdchozí matice vrácena pouze 1. hodnota : 15

10.

Vyhledávání s rozlišováním velkých a malých písmen

Vyhledávací funkce SVYHLEDAT, VVYHLEDAT, VYHLEDAT nerozlišují velká a malá písmena. Pro tyto funkce není žádný rozdíl mezi výrazy „JIRKA“, „jirka“ nebo „jiRka“. Pokud potřebujeme zohlednit velikost písmen, musíme sáhnout k maticovým vzorcům a použít funkci STEJNÉ, která ověřuje zda dvojice řetězců je stejná, a to i s ohledem na velikost písmen.


11.

Vyhledávání s využitím zástupných znaků

Vyhledávací funkce obecně dokáží pracovat se zástupnými znaky ve standardní logice: ? zastupuje jakýkoliv JEDEN znak * zastupuje LIBOVOLNÝ počet znaků ( libovolný znamená i žádný!) ˜x zastupuje literál x, kde x může být jakýkoliv znak ( pomocí znaku tilda ˜ lze tedy vyhledávat i znaky ? nebo * Následující obrázek ukazuje použití zástupných znaků ve funkcích SVYHLEDAT a VVYHLEDAT:

Použití zástupných znaků však nemusí vracet očekávané výsledky v případě, že nehledáme přesnou shodu – čili, když argument shody nemá hodnotu 0.

Očekávali bychom, že funkce vrátí opět hodnotu 44, protože hledaná hodnota „H??t“ v argumentu klíče odpovídá řetězci „Haut“. Vysvětlení, proč funkce vrátila hodnotu 55 10 www.dataspectrum.cz

spojenou s řetězcem „Aut“ spočívá v uvědomnění si faktu, že 4.argument funkce SVYHLEDAT se v případě nastavení na hodnotu 1 chová tak, že vyhledává NEJVĚTŠÍ NIŽŠÍ HODNOTU VZESTUPNĚ SEŘAZENÉHO SEZNAMU. Pokud si tedy seznam seřadíme, dostaneme následující podobu: ID1 at Aut H??t Had Haut Hu? Hut

ID2 11 55 22 44 66 33

Vidíme, že největší nižší hodnota k H??t je řetězec Aut, což odpovídá předchozímu výsledku. Z předchozího příkladu je patrné, že vyhledávání pomocí zástupných znaků funguje pouze v případě, že hledáme přesnou hodnotu ( argument shody vyhledávací funkce je nastaven na hodnotu 0). Pokud hledáme přibližnou hodnotu, pak Excel interpretuje zástupné znaky jako znaky reálné a vrátí největší nižší hodnotu vzestupně setříděného seznamu. Poznámka k použití znaku tilda ˜. Jednomu z klientů, který používal vyhledávací funkce k procházení seznamu importovaného z podnikového systému, nevracela funkce SVYHLEDAT správné hodnoty vzhledem k tomu, že zmíněný systém vkládal před hodnoty uložené v jednom ze sloupců znak tilda ˜. Řešení problému spočívalo ve zdvojení tohoto znaku v řetězci předávaného funkci – tedy místo SVYHLEDAT(„˜ABC“,A1:K1000;3;0) bylo třeba zapsat funkci v podobě SVYHLEDAT(„˜˜ABC“,A1:K1000;3;0)

12.

Vnořené vyhledávací funkce

Vyhledávací funkce můžeme vcelku bez problémů vnořovat do sebe, v případě odkazu na oblast buněk však musíme vnitřní funkci zapouzdřit do funkce NEPŘÍMÝ.ODKAZ. Následující příklad ukazuje způsob, jak vnořit dvě vyhledávací funkce do sebe.


Vnořená funkce SVYHLEDAT(2001;D1:E4;2;0) vrací adresu oblasti, ve které vnější funkce má vyhledávat hodnotu „Jiří“. Tato vnořená funkce musí být zapouzdřena do NEPŘÍMÝ.ODKAZ, aby předaná hodnota adresy oblasti byla interpretována jako adresa.


13.

Reverzní vyhledávání

Pod pojmem reverzní vyhledávání rozumím postup, kdy ze zdané hodnoty v oblasti dat hledáme odpovídající hodnotu ve sloupci nebo řádku s popisem kategorií. Představme si úlohu, kdy je naším úkolem nalézt v tabulce prodejů jméno prodejce, jehož prodej dosáhl hodnoty 392: prodej za jednotlivé měsíce leden únor březen Jan 359 743 349 Pavel 742 601 636 Mirek 756 306 772 Jiří 597 390 308 Hana 425 715 410 Jitka 514 638 392 Kateřina 451 335 692

V tomto případě nám použití vyhledávacích funkcí mnoho nepomůže, protož nevíme, ve kterém sloupci se hodnota 392 bude nacházet. Řešením je součinnost funkce NEPŘÍMÝ.ODKAZ a maticového vzorce E A B C D 1 prodej za jednotlivé měsíce 2 leden únor březen 3 Jan 359 743 349 4 Pavel 742 601 636 5 Mirek 756 306 772 6 Jiří 597 390 308 7 Hana 425 715 410 8 Jitka 514 638 392 9 Kateřina 451 335 692 10 11 prodej 392 12 prodejce Jitka {=NEPŘÍMÝ.ODKAZ("A" & SUMA((A1:D9=B11)*ŘÁDEK(A1:D9)))}

Vzorec {=NEPŘÍMÝ.ODKAZ("A" & SUMA((A1:D9=B11)*ŘÁDEK(A1:D9)))} pracuje následujícím způsobem: A1:D9=B11 změní oblast dat na tabulku pravdivostních hodnot A 1 2 3 4 5 6 7 8 9

Jan Pavel Mirek Jiří Hana Jitka Kateřina

B C prodej za jednotlivé měsíce leden únor NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA

D březen NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA NEPRAVDA PRAVDA NEPRAVDA


E

(A1:D9=B11)*ŘÁDEK(A1:D9) vynásobí hodnoty v předchozí tabulce čísly řádků A 1 2 3 4 5 6 7 8 9

Jan Pavel Mirek Jiří Hana Jitka Kateřina

B C prodej za jednotlivé měsíce leden únor 0 0 0 0 0 0 0 0 0 0 0 0 0 0

E

D březen 0 0 0 0 0 8 0

SUMA((A1:D9=B11)*ŘÁDEK(A1:D9)) sečte hodnoty v tabulce – výsledkem je 8 ("A" & SUMA((A1:D9=B11)*ŘÁDEK(A1:D9))) vytvoří řetězec „A8“ NEPŘÍMÝ.ODKAZ vrátí odkaz ( přečte hodnotu) na buňku s adresou A8

14.

Vyhledávací funkce VYHLEDAT – použít nebo zapomenout?

Funkce VYHLEDAT patří mezi nejméně používané ve skupině vyhledávacích funkcí. Přesto je vybavena chováním, které ji předurčuje k tomu, aby se stala jedním z našich favoritů: 1. umožňuje rychlé vytváření konverzních tabulek 2. nevyžaduje , aby sloupec s klíčovými hodnotami byl umístěn v tabulce s návratovými hodnotami 3. je mimořádně rychlá Pokud při vyhledávání v rozsáhlých seznamech označíme rychlost odezvy funkcí SVYHLEDAT a VVYHLEDAT jako 1, pak kombinace POZVYHLEDAT + INDEX by mohla obdržet známku 3 ( přibližně 3x rychlejší), ale rychlost odezvy VYHLEDAT lze ocenit na 100. Samozřejmě záleží i na dalších podmínkách, nicméně pokud nám jde o rychlost odezvy, pak VYHLEDAT nemá konkurenci VYHLEDAT však vyžaduje vzestupné seřazení sloupce prohledávaných hodnot ( oblast 1. argumentu funkce). Ostatní vyhledávací funkce umožňují prohledat i neseřazený seznam – VYHLEDAT tuto možnost nepřipouští.

Rychlé vytváření konverzních tabulek Potřebujeme konvertovat hodnoty z 1. sloupce na hodnoty obsažené v příslušném řádku sloupce druhého: 1 2 3 4

první druhý třetí čtvrtý

Řešením je zapsat funkci =VYHLEDAT(A1;{1;2;3;4};{"první";"druhý";"třetí";“čtvrtý“}), která z buňky A1 14 www.dataspectrum.cz

načte hodnotu, porovná ji s hodnotami v první matici {1;2;3;4} a vrátí příslušnou hodnotou z matice druhé {"první";"druhý";"třetí";“čtvrtý“} Pokud by však hodnoty v prohledávaném seznamu ( čili v oblasti předané prvnímu argumentu) nebyly seřazeny vzestupně, dostaneme zcela podivné výsledky. =VYHLEDAT(A1;{2;4;1;3};{"druhý";"čtvrtý";"první";"třetí"}) vrátí tyto konverzní hodnoty: 1 2 3 4

#N/A druhý druhý čtvrtý

Oddělení sloupce s klíčovými hodnotami a sloupce s návratovými hodnotami Funkce VYHLEDAT umožňuje fyzicky oddělit oblasti vyhledávaných a návratových hodnot a dokonce je vůči sobě posunout. – viz obrázek:

Oblasti vyhledávaných a návratových hodnot nemusí být ani uloženy ve stejném listu nebo dokonce ve stejném sešitu. Využití rychlosti funkce VYHLEDAT při náhradě SVYHLEDAT, VVYHLEDAT v dalším odstavci nalezneme příklad vzorce obsahující funkci VYHLEDAT


15.

vyhledávací funkce vrací chybu #N/A – co mám dělat?

Pokud vyhledávací funkce v zadané oblasti nenalezne hledanou hodnotu, pak vrací jako výsledek hodnotu #N/A. V předchozím čísle ExcelAsistentMagazínu jsme si ukázali, jak nahradit tyto chybové hodnoty jiným, smysluplnějším textem. Pokud totiž používáme vyhledávací funkce v dokumentech určených pro další uživatele, pro prezentaci apod., není příliš žádoucí, aby se v buňkách objevovaly chybové hodnoty. Neznalý uživatel pak neví, o jakou chybu se jedná a v důsledku může být snížena důvěryhodnost našeho dokumentu. Proto je vhodné chybové hodnoty odstranit, případně nahradit jinou hodnotou. Řešení je založeno na zapouzdření vyhledávací funkce do funkce KDYŽ, která otestuje návratovou hodnotu: =KDYŽ(JE.NEDEF(SVYHLEDAT();"NENALEZENO";SVYHLEDAT()) Pokud vyhledávací funkce nenalezne požadovanou hodnotu, vrátí hodnotu chyby #N/A. Tato hodnota je předána funkci JE.NEDEF, která pak vrátí hodnotu PRAVDA a výsledkem funkce KDYŽ je řetězec „NENALEZENO“. Pokud funkce SVYHLEDAT nalezla požadovanou hodnotu, je výsledkem funkce JE.NEDEF hodnota NEPRAVDA a funkce KDYŽ předá výpočet svému třetímu argumentu, který opět obsahuje funkci SVYHLEDAT. Dojde tedy k opětovnému vyhodnocení funkce SVYHLEDAT. Tento postup je často používán – bohužel na úkor výpočetní rychlosti. Při auditech excelských dokumentů se s ním pravidelně setkávám, autoři jej považují za spolehlivý a účelný. Je zajímavé, že i v případě velice rozsáhlých seznamů ( tisíce buněk, často obsahujících další funkce), kdy rychlost odezvy je nízká, autoři setrvávají na vžitém postupu a nesnaží se nalézt alternativy. Tato situace je tristní zejména ve společnostech, které investují nemalé částky do výpočetních systému ( především společnosti z oblasti finančnictví) a pak používají dostupné prostředky nejméně efektivním způsobem. Jaké tedy existují alternativy k výše popsané metodě? V podstatě jde o eliminaci duplicitního vyhodnocování vyhledávací funkce nebo její nahrazení funkcí rychlejší. ( pro potřeby příkladu předpokládejme, že hledáme hodnotu „abc“ v oblasti A1:A100 a chceme vrátit příslušnou hodnotu ve sloupci D) =KDYŽ(COUNTIF(A1:A100 ; “abc“ )=0;"";SVYHLEDAT(“abc“;A1:D100;4;0)) nebo =KDYŽ(COUNTIF(A1:A100; “abc“)=0;""; INDEX(D1:D100;POZVYHLEDAT(“abc“;A1:A100;0))) nebo ( v případě vzestupně seřazeného seznamu ) 16 www.dataspectrum.cz

=KDYŽ(COUNTIF(A1:A100; “abc“)=0;""; VYHLEDAT(“abc“;A1:A100;D1:D100))) nebo použití následující uživatelské funkce errortrap 'Autor:Harlan Grove ([email protected]) 'způsob použití '=errortrap(vzorec,#N/A) nebo =errortrap(vzorec,co_mám_zobrazit_při_chybě,#N/A) 'Lze zpracovat i více chybových hodnot - pokud chceme např. nahradit #N/A, #NULL!, '#REF! and #NAME? 'hodnotou -1, pak funkci voláme takto =errortrap(vzorec,-1,#N/A,#NULL!,#REF!,#NAME?) Function errortrap(v As Variant, ParamArray e() As Variant) As Variant Dim i As Long, m As Long, n As Long, t As Variant errortrap = v If Not IsError(v) Then Exit Function 'žádná chyba nebyla nalezena n = UBound(e) If Not IsError(e(0)) Then m=1 t = e(0) Else m=0 t = "" End If If n < m Then 'žádné další argumenty, vyhovuje všem typům chyby errortrap = t Exit Function End If For i = m To n 'testuj daný typ chyby If v = e(i) Then errortrap = t Exit Function End If Next i End Function Použití této metody ukazuje obrázek:


16.

Vyhledávací funkce v prostředí VBA

Vyhledávací funkce je samozřejmě možné a často i vhodné začlenit do našich programů zapsaných v VBA. V následující tabulce nalezneme převod mezi českými názvy používanými v uživatelském prostředí a anglickými názvy, které využíváme v kódech VBA POSUN SVYHLEDAT VVYHLEDAT VYHLEDAT POZVYHLEDAT INDEX NEPŘÍMÝ.ODKAZ

OFFSET VLOOKUP HLOOKUP LOOKUP MATCH INDEX INDIRECT

Pro použití těchto funkcí je dobré vědět, že se na ně můžeme odkazovat dvěmi růtnými způsoby s poněkud odlišným výsledným chováním: Application.Match nebo Application.WorksheetFunction.Match Doporučuji používat první způsob – důvodem je chování funkce v případě, že nenalezne hledanou hodnotu. Pro tento případ je vhodné použít kód


If IsError(Application.Match(Var, SourceRange, 0)) Then ... Pokud totiž voláme vyhledávací funkci prostřednictvím objektu Application, vrací tato funkce v případě neúspěšného vyhledávání chybovou hodnotu typu Variant, kterou pak můžme testovat pomocí funkce IsError. Pokud bychom volali funkci prostřednictvím objektu WorksheetFunction, pak by v případě neúspěšného vyhledávání došlo ke vzniku běhové chyby VBA, kterou bychom museli ošetřit standardními technikami ( pomocí On Error GoTo a vytvořením kódu ošetřujícího tuto chybu) Typické použití vyhledávací funkce je patrné v následujícím příkladu – funkce GetRowNum vrací číslo řádku, ve kterém se v rámci sloupce A nachází hodnota myVal: Function GetRowNum(myVal) GetRowNum = Application.Match(myVal, Range("A:A"), 0) If IsError(GetRowNum) Then GetRowNum = 0 End Function

Následující kód umožňuje ověřit, zda hodnota zapsaná do buňky ve sloupci A je obsažena v seznamu. Kód je nutné vložit přímo do listu, ve kterém chceme ověřovat data. Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim val As Long Dim MojeData As Variant MojeData = Array( "Jirka", "Jitka", "Martina", "Barbora") On Error Resume Next If Not Intersect(Target, Range("A:A")) Is Nothing Then val = Application.WorksheetFunction.Match(Target.Value, MojeData, 0) If val > 0 Then MsgBox "Hodnota " & Target.Value & " je obsažena v seznamu" Else MsgBox "Hodnota " & Target.Value & " NENÍ obsažena v seznamu", vbCritical + vbOKOnly End If End If End Sub

17.

Průvodce vyhledáváním

Microsoft připravil uživatelům pomůcku nazvanou „Průvodce vyhledáváním“, která usnadňuje vytváření vzorců založených na vyhledávacích funkcích. Od této pomůcky nemůžeme očekávat řešení sofistikovaných problémů, ale v jednodušších případech nebo pro potřeby seznámení se s vyhledávacími funkcemi může být užitečná.


Podmínky pro úspěšné použití pomůcky jsou dvě: 1. musíme ji mít nainstalovanou 2. databáze, ve které chceme vyhledávat hodnoty, musí mít důsledně pojmenovány sloupce i řádky Instalace Pomůcka je uložena ve formě doplňku Excelu s názvem „Lookup.xla“ a pokud byla řádně nainstalována, je dostupná pomocí Nástroje > Průvodce > Vyhledávání… V případě, že touto cestou doplněk dostupný není, je třeba zjistit, zda byl nainstalován a zaregistrován. Pokud jej nalezneme v přehledu doplňků ( Nástroje > Doplňky), je třeba kliknutím do zaškrtávacího pole doplněk zaregistrovat. Pokud v seznamu není, pokusíme se jej vyhledat pomocí okna dostupného tlačítkem Procházet…

Nebudeme – li ani poté úspěšní, nezbývá než doplněk nainstalovat ze zdrojového CD Microsoft Office. Příklad využití doplňku Průvodce vyhledáváním Doplněk použijeme k vytvoření vyhledávacího vzorce nad tabulkou plateb: 1 2 3 4 5 6 7 8 9 10

A B C D E datum platby firma A firma B firma C firma D 9945 7000 4350 9200 1.8.2003 3.8.2003 10000 5050 4400 9012 10350 5325 4300 9150 5.8.2003 7.8.2003 10212 5500 4312 9350 9.8.2003 10150 5325 4375 9575 11.8.2003 10075 5020 4412 9512 10125 4875 4450 9325 13.8.2003 10175 4800 4412 9400 15.8.2003 10025 5000 4375 9450 17.8.2003


Průvodce je rozdělen do 4. kroků: 1. určení oblasti dat 2. určení sloupce a řádku pro výběr 3. určení struktury návratových hodnot 4. určení buňky k zápisu vzorce Určení oblasti dat

Určení sloupce a řádku pro výběr

Určení struktury návratových hodnot


( v tomto bodě se můžeme rozhodnout, zda si přejeme zapsat pouze vzorec, nebo zapsat i kriteria použitá k výběru. Pokud zvolíme i zápis parametrů, máme možnost přepsáním hodnot v těchto buňkách dynamicky měnit výsledné hodnoty) Určení buňky k zápisu vzorce

Výsledkem práce průvodce je vytvoření vzorce ve tvaru: =INDEX($A$1:$E$10; POZVYHLEDAT(DATUMHODN("11.8.2003");$A$1:$A$10;); POZVYHLEDAT("firma C";$A$1:$E$1;))

18.

Několik příkladů na domácí cvičení

1. Výpočet výše daně V předchozím díle magazínu jsme řešili úlohu týkající se získání správné hodnoty sazby daně při zadané výši ročního příjmu:


Pokusme se tuto úlohu zkomplikovat. Úkolem není nalézt jen hodnotu sazby daně, ale přímo určit výši daňové povinnosti. Ze zadané výše příjmu a tabulky sazeb daně tedy máme určit částku, kterou musíme zaslat finančnímu úřadu. Tabulka: Příjem větší než... 0 2 501 5 001 20 001 35 001 50 001 70 001 100 001 >250 001

...a nižší než sazba 2 500 0% 5 000 1% 20 000 3% 35 000 7% 50 000 13% 70 000 19% 100 000 24% 250 000 27% 28%

Výše daně 0 25 475 1 525 3 475 7 275 14 475 54 975

2. ceník Následující úloha je opravdovým testem našich schopností. Šířka (m)

310

420

515

635

710

820

960

1150

Výška (m)

300 400 500 600 700 800 900 1000 1100 1200 1300 1400 1500

260

277

296

312

350

368

385

410

272

291

310

327

371

389

408

432

280

299

322

342

385

405

428

455

293

313

333

360

405

427

452

478

303

327

354

379

427

453

478

501

315

341

367

395

444

471

499

524

326

355

385

412

466

494

524

546

338

369

401

430

477

516

545

570

349

381

414

447

493

538

566

592

362

395

430

466

510

557

587

616

372

409

447

482

525

577

607

639

383

422

459

498

543

594

628

661

392

433

476

515

558

612

648

684


Tabulka představuje ceník okenních žaluzií, Naším cílem je nalézt vzorec, který po zadání šířky a výšky vrátí příslušnou cenu. Zadání je komplikované podmínkou, že klient si může objednat jakýkoliv rozměr – není omezen rozměry uvedenými v tabulce. Platí pravidlo, že pokud není nalzen přesný rozměr, je klientovi účtována cena většího rozměru. Pokud tedy např. klient požaduje žaluzii šířka x výška = 450 x 710, bude účtována cena žaluzie 515 x 800, tj. 367Kč. Řešení obou úloh naleznete od 20.7. ve Škole Excelu na webových stránkách http://www.dataspectrum.cz/pages/learning/learningmain.htm a v dalším čísle

ExcelAsistentMagazínu. Pokud mi zašlete úspěšné řešení na adresu [email protected] , rád je uveřejním v příštím čísle magazínu.

19. • • • • •

Co nás čeká v příštím pokračování vytváření názvů v listech Excelu dynamicky vytvářené názvy využití dynamicky vytvářených názvů pro tvorbu grafů použití názvů ve vzorcích použití názvů v prostředí VBA

( nedílnou součástí tohoto magazínu je i soubor ExcelAsistentMagazin052003.xls obsahující příklady použité v tomto magazínu. Tento soubor si můžete stáhnout z adresy http://www.dataspectrum.cz/excelmag/download/eam0503x.zip) Archív všech dosud publikovaných čísel ExcelAsistentMagazínu

http://www.dataspectrum.cz/excelmag/excelmagmain.htm

Škola Excelu – neustále se rozšiřující soubor řešených příkladů

http://www.dataspectrum.cz/pages/learning/learningmain.htm

ExcelAsistent - více než 100 funkcí pro efektivní práci v Excelu

http://www.dataspectrum.cz/pages/software/softwaremain.htm

Máte dotaz týkající se práce v prostředí Microsoft Excel?

mailto:[email protected]

Staňte se členem elektronické konference věnované výhradně Excelu

http://www.pandora.cz/conference/excel

Další číslo tohoto magazínu určeného pro pokročilé techniky práce v prostředí Microsoft Excel naleznete na adrese www.dataspectrum.cz od 10.8.2003.

Copyright © 2003 Jiří Číhař Dataspectrum Jiří Číhař Dataspectrum (http:/www.dataspectrum.cz) Těším se na setkání s Vámi v příštím měsíci. Pardubice 4.7.2003 Jiří Číhař 24 www.dataspectrum.cz

1. Nestandardní techniky práce s vyhledávacími funkcemi

Recommend Documents