Potřebujete v seznamu hodnot, ve kterém se vyskytují mezery, najít vždy první hodnotu? Na první pohled se zdá, že jde o zapeklitý úkol. Naštěstí má tento problém ovšem poměrně jednoduché řešení. Pomocí dvou jednoduchých funkcí budete schopni v seznamu hodnot vždy lokalizovat první hodnotu. K řešení tohoto úkolu využijeme kombinaci funkcí POSUN a POZVYHLEDAT.
Pro ty z vás, kteří upřednostňují textový návod před videem, je pod videem uvedený i podrobný návod.
Excelový soubor ke stažení:
Jak najít první hodnotu v seznamu?
Ve cvičném Excelu máme tabulku s fakturami a fakturačními částkami, které byly vydané pro tři klienty. Naším úkolem je sečíst první tři nenulové faktury v seznamu u každého klienta. Počet faktur, jejichž částky chceme sečíst je uvedený v buňce I8.
K vyřešení tohoto úkolu musíme překonat dva problémy:
- V seznamu se vyskytují mezery
- První fakturační částka začíná u každého klienta na jiném řádku
Chtěli bychom vymyslet řešení pomocí funkcí tak, abychom nemuseli hledat první fakturu ručně a vše sčítat manuálně.
Krok 1: Najít první nenulovou hodnotu u každého klienta
Začneme tím, že v první tabulce najdeme pro každého klienta první nenulovou hodnotu v seznamu. K určení správného řádku poslouží funkce POZVYHLEDAT, anglicky funkce MATCH, která vrátí číslo řádku nebo sloupce, na kterém se nachází hledaná hodnota.
Základem funkce POZVYHLEDAT bude, že u každého klienta hledáme hodnoty, které se nerovnají nule. Začneme tedy v buňce I4 pro klienta Škoda Auto. Zápis bude vypadat následovně
= POZVYHLEDAT(PRAVDA; C$4:C$35<>0; 0)
Výraz C4:C35<>0 znamená, kde se hodnoty nerovnají nule. Pokud si tento zápis zkusíte cvičně napsat mimo tabulku, tak se na řádcích vrátí série PRAVD a NEPRAVD. Na řádcích, kde je uvedená nepravda nemáme žádné částky, naopak na řádcích, kde je pravda máme fakturační částky uvedené.
První parametr funkce POZVYHLEDAT je, co hledáme. Nehledáme žádné konkrétní číslo, ale hledáme první PRAVDU. Napíšeme tedy PRAVDA. Zde využijeme toho, že vyhledávací funkce POZVYHLEDAT vždy najde pouze první hodnotu, která odpovídá zadání. Zde se nám tato vlastnost funkce POZVYHLEDAT hodí.
Druhý parametr funkce POZVYHLEDAT je prohledat, tedy, kde hledanou hodnotu hledáme. Označíme tedy sloupec faktur u prvního klienta s podmínkou, že se hodnoty nerovnají nule. Následuje parametr shoda, v tomto případě hledáme přesnou shodu. Funkci potvrdíme a vrátilo se číslo řádku, na kterém se vyskytuje první nenulová hodnota.
Pokud bychom chtěli znát výši první faktury, tak stačí funkci POZVYHLEDAT zabalit do funkce INDEX.
Krok 2: Dynamicky sečíst prvních X hodnot
Kdykoliv se v Excelu potřebujeme dynamicky posouvat nebo vytvářet dynamické rozpětí, tak nám k tomu poslouží funkce POSUN, anglicky funkce OFFSET. Funkci POSUN potřebujeme, jelikož v každém sloupci začínám nenulová hodnota na jiném řádku.
= POSUN/OFFSET(odkaz; řádky; sloupce; [výška];[šířka])
Zápis funkce včetně funkce POZVYHLEDAT napíšeme do druhé tabulky. Nicméně v reálném případě byste mohli obě funkce spojit již v buňce I4. Do buňky I11 napíšeme funkci POSUN/OFFSET, kde jako první parametr funkce odkaz označíme buňku C3, což bude náš styčný bod pro funkci POSUN. Parametr řádky ve funkci POSUN určuje, na který řádek se má funkce POSUN posunout. Parametrem řádky ve funkci POSUN je tedy celá funkce POZVYHLEDAT, kterou jsme vytvořili v prvním kroku. Následuje parametr sloupce. Jelikož se nechceme posouvat se sloupci, tak vyplníme nulu. Poslední parametr, který musíme vyplnit je nepovinný parametr výška, kde označíme, kolik buněk má funkce POSUN označit od řádku. V našem případě chceme označit počet buněk, které máme uvedené v buňce I8.
Funkci POSUN tedy musíme zabalit do funkce SUMA, aby se částky sečetli.
Celou funkci musíme potvrdit stisknutím kláves CTRL+SHIFT+ENTER, jelikož se jedná o maticový vzorec. Jediní, kdo mohou pro potvrzení funkce použít klávesu ENTER, jsou předplatitelé služby Office 365 a Microsoft 2021.
Pokud by vás zajímalo, jak dynamicky vyhledávat v seznamu s mezera, tak se podívejte na navazující video První hodnota v seznamu s dynamickým vyhledáváním.
