K načtení poslední hodnoty v dynamickém seznamu použijeme možnost Ověření dat spolu s funkcemi OFFSET a COUNTA v aplikaci Microsoft Excel 2010/2013.
COUNTA: Vrátí počet buněk, které obsahují hodnoty.
Syntaxe funkce „COUNTA“: = COUNTA (hodnota1, hodnota2, hodnota3….)
Příklad: V rozsahu A1: A5 obsahují buňky A2, A3 a A5 hodnoty a buňky A1 a A4 jsou prázdné. Vyberte buňku A6 a napište vzorec-
= COUNTA (A1: A5) funkce se vrátí 3
OFFSET: Vrátí odkaz na rozsah, který je odsazen od řady řádků a sloupců z jiného rozsahu nebo buňky.
Syntaxe funkce OFFSET: = OFFSET (reference, řádky, sloupce, výška, šířka)
Odkaz:- Toto je buňka nebo rozsah, od kterého chcete kompenzovat.
Řádky a sloupce k přesunu: - Kolik řádků chcete přesunout počáteční bod a oba mohou být kladné, záporné nebo nulové.
Výška a šířka: - Toto je velikost rozsahu, který chcete vrátit. Toto je nepovinné pole.
Uveďme si příklad, abychom porozuměli funkci Offset v Excelu.
Máme data v rozsahu A1: D10. Sloupec A obsahuje kód produktu, sloupec B obsahuje množství, sloupec C obsahuje náklady na produkt a sloupec D obsahuje celkové náklady. Musíme vrátit hodnotu buňky C5 v buňce E2.
Postupujte podle níže uvedených kroků.
- Vyberte buňku E2 a napište vzorec.
- = OFFSET (A1,4,2,1,1)a stiskněte klávesu Enter na klávesnici.
- Funkce vrátí hodnotu buňky C5.
V tomto případě potřebujeme získat hodnotu z buňky C5 do E2. Naše referenční buňka je první buňkou v rozsahu, který je A1 a C5 je o 4 řádky níže a 2 sloupce napravo od A1. Vzorec je tedy = OFFSET (A1,4,2,1,1) nebo = OFFSET (A1,4,2) (protože 1,1 je volitelný).
Nyní si vezmeme příklad k načtení poslední hodnoty v dynamickém seznamu.
Názvy zemí máme v řadě. Pokud nyní přidáme další země, tento seznam by měl být v rozevíracím seznamu k dispozici automaticky.
K přípravě dynamického seznamu musíme vytvořit vzorec, který načte poslední hodnotu ve sloupci a automaticky se aktualizuje po přidání nového čísla.
Postupujte podle níže uvedených kroků:-
- Vyberte buňku B2.
- Přejděte na kartu Data, ze skupiny Datové nástroje vyberte Ověření dat.
- Zobrazí se dialogové okno „Ověření dat“. Na kartě „Nastavení“ vyberte z rozevíracího seznamu Povolit „Vlastní“.
- Aktivuje se pole vzorců.
- Do tohoto pole napište vzorec.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Klikněte na OK.
- V této fázi je poslední aktualizovanou buňkou A11.
- Chcete -li zkontrolovat, zda ověření dat funguje správně, přidejte do buňky A12 název města.
Jakmile přidáte položku do A12, bude přidána do rozevíracího seznamu.
Tímto způsobem můžete vytvořit dynamický seznam a automaticky do něj naplnit nové položky v aplikaci Microsoft Excel 2010 a 2013.
