Nalezení (ne/vyplněné) hodnoty v českém Excelu

Pro nalezení vyplněné, nevyplněné nebo jakéholiv hodnoty v tabulce můžete na internetu najít řadu návodů… v angličtině. Problém je, že český excel má přeložené hodnoty a používá jiné oddělovače, takže anglický vzorec v české verzi nefunguje. Jak tedy vzorec přeložit?

Základní vzorec

Například na stránkách ExcelJet můžete najít následující vzorec pro nalezení poslední vyplněné hodnoty v řádce:

=XLOOKUP(TRUE,B5:B16<>"",B5:B16,,,-1)

Problémů je v něm hned několik:

1. Hodnota TRUE v české verzi neexistuje.
2. Oddělovač čárka v české verzi odděluje desetinná místa.
3. Stránka uvádí trochu jiné parametry než současná (2024+) verze Excelu.

Popis funkce XLOOKUP

Podle českého návodu má funkce XLOOKUP následující parametry:

=XLOOKUP(hledaná_hodnota, pole_hledání, pole_vrácení, [hodnota_pro_nenalezeno], [režim_porovnávání], [režim_hledání]) 

Poznámka: ve verzi Excel 2019 nebo starší existuje funkce LOOKUP s jinými parametry.

Jádro problému (ne)vyplněné buňky

Jádro vzorce leží v druhém parametru range<>"", který hledá vyplněné a nevyplněné buňky. V anglické verzi vzorec vrátí seznam hodnot TRUE pro vyplněné buňky a FALSE pro nevyplněné.

Jelikož je ale v české verzi TRUE a FALSE přeloženo na PRAVDA a NEPRAVDA, musíte vzorec upravit tak, aby první parametr by PRAVDA místo TRUE. Díky tomu vám vzorec správně najde první nebo poslední vyplněnou hodnotu. Pokud byste naopak chtěli najít první nebo poslední buňku, která vyplněná není, hledejte naopak NEPRAVDA.

Pro nalezení poslední vyplněné hodnoty v řádce tedy použijte český vzorec:

=XLOOKUP(PRAVDA;B2:ZZ2<>"";B2:ZZ2;"Nenalezeno";0;-1)

Režim hledání

Poslední parametr určuje režim hledání. Zjednodušeně hodnota 1 znamená hledání od začátku do konce (tedy zhora dolu nebo zleva doprava) a hodnota -1 otáčí hledání od konce (zdola nahoru nebo zprava doleva).

Režim porovnávání

Předposlední parametr určuje, jak se má najít buňka podle zadané hodnoty.

Parametr 0 znamená přesnou shodu. Pokud tedy hledáte například hodnotu 5, najde se pouze první nebo poslední buňka, která obsahuje hodnotu 5. Tuto hodnotu musíte použít pokud hledáte PRAVDA nebo NEPRAVDA ve vzorci.

Hodnota 1 určuje, že se má najít buňka, která obsahuje danou hodnotu nebo jakoukoliv vyšší. Při hledání 5 tedy najde první buňku obsahující hodnoty 5, 6, 7, 8, atd. Naopak hodnota -1 určuje stejnou nebo menší hodnotu, takže hledání 5 bude odpovídat hodnotám 5, 4, 3, 2, 1, 0, -1, -2, atd.

Toto se hodí v případě, že hledáte hodnotu, která v tabulce přímo není, ale vztahuje se na ní nějaký rozsah. Např. pokud v tabulce věku hledáme 16 let, stačí vám najít hodnotu 12 označující rozsah 12 – 18 pro „teenager“:

=XLOOKUP(16;B1:ZZ1;B2:ZZ2;"Nenalezeno";-1;-1)

Druhý vs. třetí parametr

Druhý parametr funkce označuje oblast, kde se má hledat hodnota (z prvního parametru) a volitelně může obsahovat i výpočet (viz výše pro nalezení vylněných buněk).

Třetí parametr naopak označuje hodnotu, která se vrátí, přičemž se počítá s indexem nalezené hodnoty. Pokud tedy první parametr odpovídá páté buňce druhého parametru, vrátí se pátá buňky ze třetího parametru.

To je šikovné v případě, že nechcete zobrazit přímo nalezenou hodnotu, ale naopak přidruženou hodnotu nebo naopak jméno sloupce nebo řádky (např. pokud hledáte nevyplněnou buňku.

Udaná hodnota pro hledaný výraz – např. hledáme, jak dlouho zaměstnace pracuje pro firmu (8 let) v seznamu bonusů (řádek B), a chceme zobrazit výši jeho prémií (řádek C); pokud pro danou dobu prémie nejsou definovány, hledáme nižší hodnotu (-1 v 5. parametru); pokud není nalezeno (pracuje příliš krátce), vrátíme 0Kč (4. parametr):

=XLOOKUP(8;B2:ZZ2;C2:ZZ2;0;-1;1)

Vrátí 5000Kč, protože zaměstnanec pracující pro firmu 8 let má nákor na 5-letý bonus.

Chceme nalézt poslední nevyplněný řádek (sloupec 2) a zobrazit její název (sloupec 1):

=XLOOKUP(NEPRAVDA;B2:B65535<>"";A2:A65535;"Vše vyplněno";0;-1)