Úvod do dynamických rozsahov
Funkcia VLOOKUP sa často používa na vyhľadanie informácií uložených v tabuľkách v programe Excel. Ak napríklad máme zoznam mien ľudí a ich veku:
A potom môžeme v blízkej bunke použiť funkciu VLOOKUP na určenie Paulovho veku:
Zatiaľ je to celkom štandardné. Čo sa však stane, ak do zoznamu potrebujeme pridať ďalšie mená? Zjavnou myšlienkou by bolo upraviť rozsah vo VLOOKUP. V skutočne komplexnom modeli však môže existovať niekoľko odkazov na VLOOKUP. To znamená, že by sme museli zmeniť každú referenciu - za predpokladu, že by sme vedeli, kde sú.
Excel však ponúka alternatívny spôsob - nazývaný dynamický rozsah. Toto je rozsah, ktorý automaticky rozširuje aktualizácie. Je to perfektné, ak sa vaše zoznamy neustále rozširujú (napr. Údaje o predajoch z mesiaca na mesiac).
Na nastavenie dynamického rozsahu potrebujeme názov rozsahu - preto ho budeme nazývať AGE_DATA. Prístup k nastavovaniu dynamických rozsahov sa medzi Excelom 2007 a staršími verziami Excelu líši:
V programe Excel 2007 kliknite na „Definovať meno“ pod vzorcami:
V starších verziách Excelu kliknite na „Vložiť“ a potom na Mená.
Do rozbaľovacieho poľa zadajte názov nášho dynamického rozsahu - ktorý je „VEKOVÉ ÚDAJE“:
Do poľa označeného „Odkazuje na“ musíme zadať rozsah našich údajov. To sa dosiahne pomocou funkcie OFFSET. Má to 5 argumentov:
= OFFSET (referencia, riadky, stĺpce, výška, šírka)
- Referencia je adresa HORNÉHO ĽAVÉHO rohu nášho sortimentu - v tomto prípade bunky B5
- Riadky sú počet riadkov z TOP Vľavo, ktorými chceme, aby bol tento rozsah - čo bude v tomto prípade 0
- Cols je počet riadkov z TOP LEFT, ktoré chceme, aby bol tento rozsah - čo bude v tomto prípade 0
- Výška rozsahu - pozri nižšie
- Šírka rozsahu - to sú 2, v našom rozsahu sú dva stĺpce (meno osôb a ich vek)
Teraz sa výška rozsahu bude musieť líšiť v závislosti od počtu záznamov v našej tabuľke (čo je v súčasnosti 7).
Samozrejme, chceme spôsob, akým sa počítajú riadky v našej tabuľke, ktoré sa automaticky aktualizujú - takže jedným zo spôsobov, ako to urobiť, je použiť funkciu COUNTA. To len spočíta počet prázdnych buniek v rozsahu. Pretože sú naše mená v stĺpci B, počet záznamov v našich údajoch je COUNTA (B: B).
Všimnite si toho, že keby ste to vložili do bunky, dostali by ste hodnotu 8 - pretože obsahuje názvy hlavičiek. Že je to však nepodstatné.
Do poľa „Odkazuje na“ teda uvádzame:
= OFFSET ($ B $ 5,0,0, grófstvo (B: B), 2)
A kliknite na tlačidlo OK. Náš dynamický rozsah je teraz vytvorený.
Teraz sa vráťte k vzorcom VLOOKUP a nahraďte rozsah $ B: 4: $ C11 názvom nášho nového dynamického rozsahu AGE_DATA, aby sme mali:
Doteraz sa nič nezmenilo. Ak však do našej tabuľky pridáme niekoľko ďalších mien:
A v cele, kde sme mali Paula, ho nahraďte novým menom, ako napríklad Pedro (ktoré nebolo v pôvodnom zozname):
A vidíme, že Excel automaticky vrátil Pedrov vek, aj keď sme nezmenili vzorce VLOOKUP. Namiesto toho sa rozsah dynamického rozsahu rozšíril o ďalšie názvy.
Dynamické rozsahy sú veľmi užitočné vtedy, keď narastá objem dát - obzvlášť keď sú požadované tabuľky VLOOKUP a PIVOT.
