Potrebujete vyhľadať hodnoty v rozsiahlych tabuľkách? Alebo potrebujete vedieť aká hodnota (alebo trebárs veličina, obchodník, športový klub…) dosahuje najväčšiu, alebo najmenšiu hodnotu v nejakom riadku (mesiaci, meste…)?
Vyhľadanie hodnoty v tabuľke
Na vyhľadanie hodnoty v tabuľke slúži funkcia
INDEX(oblasť; číslo_riadka; [číslo_stĺpca])
kde:
– oblasť je oblasť (tabuľka s hodnotami – bez hlavičiek riadkov a stĺpcov)
– číslo riadka/stĺpca určuje poradové číslo stĺpca/riadka v ktorých chcete vyhľadávať.
Ako ale vyhľadať hodnotu podľa hodnôt v hlavičkách? Napríklad ako vyhľadať vzdialenosť medzi Čadcou a Trebišovom v tabuľke vzdialeností? Jednoducho – poradové číslo stlpca/riadku kde sa nachádzajú Čadca a Trebišov zistíte pomocou funkcie MATCH:
MATCH(vyhľadávaná_hodnota; pole_vyhľadávania; [typ_zhody])
kde:
vyhľadávaná_hodnota – hodnota ktorú chceme nájsť (napr. Čadca)
pole_vyhľadávania – oblasť v ktorej sa má hľadať (napr. zoznam miest)
typ_zhody – nepovinný ale dôležitý údaj – v našom prípade treba zadať 0 – vyhľadá sa presná hodnota
Funkcia Match teda v zozname miest (modrý stĺpec v príklade) nájde Čadcu ako 4. v poradí – to je ale zároveň riadok v ktorom sa nachádza hľadaná hodnota.
To isté urobíme pre zoznam v červenom riadku a hotovo. Stačí už len hodnôt nájdené funkciou Match dosadiť do funkcie Index. Výsledný vzorec potom vyzerá takto:
=INDEX(C6:AO44;MATCH(B2;B6:B44;0);MATCH(B3;C5:AO5;0))
Vyhľadanie parametra podľa hľadanej hodnoty
Potrebujete napríklad zistiť ktorá hodnota (veličina, obchodník, mužstvo…) dosahuje najväčšie hodnoty?
Tu je riešenie trochu zložitejšie, ale len na prvý pohľad. Pomôže Vám nerozlučná trojica funkcií MATCH, ADDRESS a INDIRECT.
Všetko nájdete nižšie v príklade, v každom prípade veľmi jednoduchý náčrt ako 3 funkcie spolupracujú (ich spoluprácu môžete v Exceli využiť často):
MATCH – nájde poradie hľadanej hodnoty v oblasti (napr. hlavičke tabuľky) – viď príklad vyššie (riadok 12, stĺpec F=4)
ADDRESS – nájde adresu bunky/oblasti ktorú nájdete podľa MATCH (riadok 12 v tabuľke + 6 – riadky nad tabuľkou; stĺpec 4 v tabuľke + 2 – stĺpce pred tabuľkou) = ($F$18)
INDIRECT – nájde hodnotu v bunke určenej funkciou ADDRESS – hodnota v bunke F18 (99)
A teraz konkrétne k príkladu z obrázka. Ako vyhľadať názov stĺpca v ktorom je maximálna hodnota pre Parameter 12? Pomôže “jednoduchý” 🙂 vzorec:
=INDIRECT(ADDRESS(ROW(C6);MATCH(MAX(INDIRECT((ADDRESS(MATCH(B3;B7:B24;0)+6;3)&”:”&ADDRESS(O6;14))));INDIRECT((ADDRESS(MATCH(B3;B7:B24;0)+6;3)&”:”&ADDRESS(MATCH(B3;B7:B24;0)+6;14)));0)+2))
Že kto sa v tom má vyznať?
Nuž nebude to také zložité ak si to rozoberieme na drobné, ako vidno na obrázku nižšie
Takže po rade:
=MATCH(B3;B7:B24;0)+6
MATCH nájde riadok v ktorom máme hľadať maximálnu hodnotu (hľadá v oblasti parameter 1 – X) , kde “+6” zohľadňuje prvých 6 riadkov nepoužitých vo funkcii MATCH
=(ADDRESS(O6;3)&”:”&ADDRESS(O6;13))
“O6” vo vzorci je hodnota 18, vypočítaná vyššie – teda riadok kde hľadáme, stĺpec je fixne 3 – tu začíname hľadať, výsledkom je potom C18. Druhá časť je identická len stĺpec je 13 = posledný stĺpec oblasti, kde hľadáme – teda M18. &”:”& spája 2 nájdené bunky do oblasti v ktorej hľadáme – C18:M18.
=MAX(INDIRECT(O7))
INDIRECT zabezpečí výber hodnôt v oblasti definovanej vyššie. MAX potom nájde v tejto oblasti najvyššiu hodnotu.
=INDIRECT(ADDRESS(ROW($C$6);MATCH(O8;INDIRECT(O7);0)+2))
rozbité na časti: MATCH(O8;INDIRECT(O7);0) nájde maximálnu hodnotu (“O8” = 99) v oblasti “C18:M18” / ADDRESS(ROW($C$6);MATCH(O8;INDIRECT(O7);0)+2) – vyhľadá bunku v ktorej je názov kategórie, hodnoty – ROW($C$6) – riadok bunky C6 – teda VŽDY riadok 6 v ktorom sú hľadané popisy. Druhá časť nájde stĺpec s maximom. Indirect potom už len vráti hodnotu nájdenej bunky.
Ak je to ešte stále zložité, stiahnite si priložený súbor – určite sa zorientujete.
V animácii v úvode článku vidíte okrem vyhľadania i grafické zvýraznenie vyhľadávaných buniek. Ako pomôcť grafickej prehľadnosti vašich dát sa dočítate v článku Podmienené formátovanie.
