Obsah
Máte v Exceli tabuľky s údajmi, ktorých veľkosť sa dá meniť, čiže počet riadkov (stĺpcov) sa môže v priebehu práce zväčšovať alebo zmenšovať? Ak sa veľkosť tabuľky „vznáša“, budete musieť tento moment neustále sledovať a opravovať:
- odkazy vo vzorcoch prehľadov, ktoré odkazujú na našu tabuľku
- počiatočné rozsahy kontingenčných tabuliek, ktoré sú zostavené podľa našej tabuľky
- počiatočné rozsahy grafov zostavených podľa našej tabuľky
- rozsahy pre rozbaľovacie ponuky, ktoré používajú našu tabuľku ako zdroj údajov
To všetko vás v celku nenechá nudiť 😉
Oveľa pohodlnejšie a správnejšie bude vytvoriť dynamický „gumový“ rozsah, ktorý sa veľkosťou automaticky prispôsobí skutočnému počtu riadkov a stĺpcov údajov. Na implementáciu existuje niekoľko spôsobov.
Metóda 1. Inteligentná tabuľka
Zvýraznite svoj rozsah buniek a vyberte z karty Domov – Formátovať ako tabuľku (Domov – Formátovať ako tabuľku):
Ak nepotrebujete pruhovaný dizajn, ktorý sa pridáva do tabuľky ako vedľajší efekt, môžete ho vypnúť na zobrazenej karte Konštruktér (dizajn). Každá takto vytvorená tabuľka dostane názov, ktorý možno na rovnakom mieste na karte nahradiť pohodlnejším Konštruktér (dizajn) v teréne Názov tabuľky (názov tabuľky).
Teraz môžeme použiť dynamické odkazy na našu „inteligentnú tabuľku“:
- Tabuľka 1 – odkaz na celú tabuľku okrem riadku hlavičky (A2:D5)
- Tabuľka1[#All] – odkaz na celú tabuľku (A1:D5)
- Tabuľka1 [Peter] – odkaz na stĺpec rozsahu bez prvej hlavičky bunky (C2:C5)
- Tabuľka1[#Headers] – odkaz na „hlavičku“ s názvami stĺpcov (A1:D1)
Takéto odkazy fungujú skvele vo vzorcoch, napríklad:
= SUM (Tabuľka1 [Moskva]) – výpočet sumy pre stĺpec „Moskva“
or
=VPR(F5;Tabuľka 1;3;0) – vyhľadajte v tabuľke mesiac z bunky F5 a zadajte zaň petrohradskú sumu (čo je VLOOKUP?)
Takéto prepojenia možno úspešne použiť pri vytváraní kontingenčných tabuliek výberom na karte Vložiť – kontingenčná tabuľka (Vložiť – kontingenčná tabuľka) a zadanie názvu inteligentnej tabuľky ako zdroja údajov:
Ak vyberiete fragment takejto tabuľky (napríklad prvé dva stĺpce) a vytvoríte diagram akéhokoľvek typu, pri pridávaní nových riadkov sa automaticky pridajú do diagramu.
Pri vytváraní rozbaľovacích zoznamov nemožno použiť priame odkazy na prvky inteligentnej tabuľky, ale toto obmedzenie jednoducho obídete pomocou taktického triku – použite funkciu NEPRIAME (NEPRIAME), ktorý zmení text na odkaz:
Tie. odkaz na inteligentnú tabuľku vo forme textového reťazca (v úvodzovkách!) sa zmení na plnohodnotný odkaz a rozbaľovací zoznam to bežne vníma.
Metóda 2: Dynamický pomenovaný rozsah
Ak je premena údajov na inteligentnú tabuľku z nejakého dôvodu nežiaduce, môžete použiť o niečo komplikovanejšiu, ale oveľa jemnejšiu a všestrannejšiu metódu – vytvorte v Exceli dynamický pomenovaný rozsah, ktorý odkazuje na našu tabuľku. Potom, ako v prípade inteligentnej tabuľky, môžete názov vytvoreného rozsahu voľne použiť v ľubovoľných vzorcoch, zostavách, grafoch atď. Začnime jednoduchým príkladom:
úloha: vytvorte dynamický pomenovaný rozsah, ktorý bude odkazovať na zoznam miest a automaticky sa roztiahne a zmenší pri pridávaní nových miest alebo ich odstraňovaní.
Budeme potrebovať dve vstavané funkcie Excelu dostupné v akejkoľvek verzii − POICPOZ (ZÁPAS) na určenie poslednej bunky rozsahu a INDEX (INDEX) na vytvorenie dynamického prepojenia.
Nájdenie poslednej bunky pomocou MATCH
MATCH(hľadaná_hodnota, rozsah, typ_zhody) – funkcia, ktorá vyhľadá danú hodnotu v rozsahu (riadok alebo stĺpec) a vráti poradové číslo bunky, kde bola nájdená. Napríklad vzorec MATCH(“March”;A1:A5;0) vo výsledku vráti číslo 4, pretože slovo “Marec” sa nachádza vo štvrtej bunke v stĺpci A1:A5. Posledný argument funkcie Match_Type = 0 znamená, že hľadáme presnú zhodu. Ak tento argument nie je zadaný, funkcia sa prepne do režimu vyhľadávania pre najbližšiu najmenšiu hodnotu – presne to sa dá úspešne použiť na nájdenie poslednej obsadenej bunky v našom poli.
Podstata triku je jednoduchá. MATCH hľadá bunky v rozsahu zhora nadol a teoreticky by sa mal zastaviť, keď nájde najbližšiu najmenšiu hodnotu k danej hodnote. Ak ako požadovanú hodnotu zadáte hodnotu, ktorá je zjavne väčšia ako ktorákoľvek dostupná v tabuľke, potom MATCH dosiahne úplný koniec tabuľky, nenájde nič a uvedie poradové číslo poslednej vyplnenej bunky. A potrebujeme to!
Ak sú v našom poli iba čísla, potom môžeme zadať číslo ako požadovanú hodnotu, ktorá je samozrejme väčšia ako ktorákoľvek z hodnôt v tabuľke:
Pre záruku môžete použiť číslo 9E + 307 (9 krát 10 na mocninu 307, teda 9 s 307 nulami) – maximálne číslo, s ktorým Excel v princípe dokáže pracovať.
Ak sú v našom stĺpci textové hodnoty, potom ako ekvivalent najväčšieho možného čísla môžete vložiť konštrukciu REPEAT(“i“, 255) – textový reťazec pozostávajúci z 255 písmen „i“ – posledné písmeno abeceda. Keďže Excel pri vyhľadávaní skutočne porovnáva kódy znakov, akýkoľvek text v našej tabuľke bude technicky „menší“ ako takýto dlhý riadok „yyyyyyyyy“:
Vygenerujte odkaz pomocou INDEXU
Teraz, keď poznáme pozíciu posledného neprázdneho prvku v tabuľke, zostáva vytvoriť odkaz na celý náš sortiment. Na to použijeme funkciu:
INDEX(rozsah; číslo_riadka; číslo_stĺpca)
Udáva obsah bunky z rozsahu podľa čísla riadka a stĺpca, teda napríklad funkcia =INDEX(A1:D5;3;4) v našej tabuľke s mestami a mesiacmi z predchádzajúcej metódy dá 1240 – obsah z 3. riadku a 4. stĺpca, teda bunky D3. Ak je tam len jeden stĺpec, jeho číslo možno vynechať, tj vzorec INDEX(A2:A6;3) dá na poslednej snímke „Samara“.
A je tu jedna nie celkom zrejmá nuansa: ak INDEX nie je len zadaný do bunky za znamienkom =, ako obvykle, ale je použitý ako posledná časť odkazu na rozsah za dvojbodkou, potom sa už neuvádza obsah bunky, ale jej adresu! Vzorec ako $A$2:INDEX($A$2:$A$100;3) teda poskytne odkaz na rozsah A2:A4 na výstupe.
A tu prichádza funkcia MATCH, ktorú vložíme do INDEXU, aby sme dynamicky určili koniec zoznamu:
=$A$2:INDEX($A$2:$A$100; MATCH(REP(“ja”;255);A2:A100))
Vytvorte pomenovaný rozsah
Ostáva to všetko zbaliť do jediného celku. Otvorte kartu vzorec (vzorce) A kliknite na ikonu Správca mien (Správca mien). V okne, ktoré sa otvorí, kliknite na tlačidlo vytvoriť (Nový), do poľa zadajte názov nášho rozsahu a vzorec Rozsah (Referencia):
Zostáva kliknúť ďalej OK a hotový rozsah možno použiť v akýchkoľvek vzorcoch, rozbaľovacích zoznamoch alebo grafoch.
- Použitie funkcie VLOOKUP na prepojenie tabuliek a vyhľadávacích hodnôt
- Ako vytvoriť automaticky vyplnený rozbaľovací zoznam
- Ako vytvoriť kontingenčnú tabuľku na analýzu veľkého množstva údajov