Na získanie poslednej hodnoty v dynamickom zozname použijeme v programe Microsoft Excel 2010/2013 možnosť Overenie údajov spolu s funkciami OFFSET a COUNTA.
COUNTA: Vráti počet buniek, ktoré obsahujú hodnoty.
Syntax funkcie „COUNTA“: = COUNTA (hodnota1, hodnota2, hodnota3 ….)
Príklad: V rozsahu A1: A5 bunky A2, A3 a A5 obsahujú hodnoty a bunky A1 a A4 sú prázdne. Vyberte bunku A6 a napíšte vzorec-
= COUNTA (A1: A5) funkcia sa vráti 3
OFFSET: Vráti odkaz na rozsah, ktorý je posunutý počtom riadkov a stĺpcov z iného rozsahu alebo bunky.
Syntax funkcie OFFSET: = OFFSET (referencia, riadky, stĺpce, výška, šírka)
Referencia:- Toto je bunka alebo rozsah, z ktorého chcete kompenzovať.
Riadky a stĺpce na presun: - Koľko riadkov chcete posunúť počiatočný bod a oba môžu byť kladné, záporné alebo nulové.
Výška a šírka: - Toto je veľkosť rozsahu, ktorý chcete vrátiť. Toto je voliteľné pole.
Ukážme si to na porozumení funkcie Offset v Exceli.
Máme údaje v rozsahu A1: D10. Stĺpec A obsahuje kód produktu, stĺpec B obsahuje množstvo, stĺpec C obsahuje náklady na výrobok a stĺpec D obsahuje celkové náklady. Musíme vrátiť hodnotu bunky C5 v bunke E2.
Postupujte podľa nižšie uvedených krokov.
- Vyberte bunku E2 a napíšte vzorec.
- = OFFSET (A1,4,2,1,1)a stlačte kláves Enter na klávesnici.
- Funkcia vráti hodnotu bunky C5.
V tomto prípade potrebujeme získať hodnotu z bunky C5 do E2. Naša referenčná bunka je prvou bunkou v rozsahu, ktorý je A1 a C5, je o 4 riadky nižšie a 2 stĺpce napravo od A1. Vzorec je teda = OFFSET (A1,4,2,1,1) alebo = OFFSET (A1,4,2) (pretože 1,1 je voliteľné).
Teraz si vezmime príklad na získanie poslednej hodnoty v dynamickom zozname.
Názvy krajín máme v rozsahu. Ak teraz pridáme ďalší zoznam, tento zoznam by mal byť v rozbaľovacom zozname k dispozícii automaticky.
Na prípravu dynamického zoznamu musíme vytvoriť vzorec, ktorý v stĺpci načíta poslednú hodnotu a automaticky sa aktualizuje po pridaní nového čísla.
Postupujte podľa nižšie uvedených krokov:-
- Vyberte bunku B2.
- Prejdite na kartu Údaje a zo skupiny Dátové nástroje vyberte položku Overenie údajov.
- Zobrazí sa dialógové okno „Overenie údajov“. Na karte „Nastavenia“ v rozbaľovacom zozname Povoliť vyberte „Vlastné“.
- Aktivuje sa pole vzorcov.
- Do tohto poľa napíšte vzorec.
- = OFFSET (A: A, 1,0, COUNTA (A: A) -1,1).
- Kliknite na OK.
- V tejto fáze je poslednou aktualizovanou bunkou A11.
- Ak chcete skontrolovať, či validácia údajov funguje správne, zadajte do bunky A12 názov mesta.
Hneď ako do A12 pridáte záznam, bude pridaný do rozbaľovacieho zoznamu.
Toto je spôsob, akým môžete vytvoriť dynamický zoznam a automaticky do neho vkladať nové položky v programe Microsoft Excel 2010 a 2013.
