Ako zmraziť bunku vo vzorci programu Excel

Používatelia často čelia potrebe pripnúť bunku vo vzorci. Vyskytuje sa napríklad v situáciách, keď chcete skopírovať vzorec, ale tak, aby sa prepojenie nepohybovalo nahor a nadol o rovnaký počet buniek, ako bolo skopírované z pôvodného umiestnenia.

V tomto prípade môžete opraviť odkaz na bunku v Exceli. A to sa dá urobiť niekoľkými spôsobmi naraz. Poďme sa bližšie pozrieť na to, ako tento cieľ dosiahnuť.

Čo je odkaz na Excel

List sa skladá z buniek. Každý z nich obsahuje špecifické informácie. Ostatné bunky ho môžu použiť pri výpočtoch. Ako však pochopia, odkiaľ majú údaje získať? Pomáha im vytvárať odkazy.

Každý odkaz označuje bunku s jedným písmenom a jedným číslom. Písmeno predstavuje stĺpec a číslo predstavuje riadok. 

Existujú tri typy odkazov: absolútne, relatívne a zmiešané. Druhý je štandardne nastavený. Absolútna referencia je taká, ktorá má pevnú adresu stĺpca aj stĺpca. Zmiešané je teda také, kde je pevný buď samostatný stĺpec alebo riadok.

Metóda 1

Ak chcete uložiť adresy stĺpcov aj riadkov, postupujte takto:

1. Kliknite na bunku obsahujúcu vzorec.
2. Kliknite na riadok vzorcov pre bunku, ktorú potrebujeme.
3. Stlačte tlačidlo F4.

V dôsledku toho sa odkaz na bunku zmení na absolútny. Dá sa rozpoznať podľa charakteristického znaku dolára. Ak napríklad kliknete na bunku B2 a potom kliknete na F4, odkaz bude vyzerať takto: $B$2.

Čo znamená znak dolára pred časťou adresy na bunku?

1. Ak je umiestnený pred písmenom, znamená to, že odkaz na stĺpec zostáva rovnaký bez ohľadu na to, kam bol vzorec presunutý.
2. Ak je znak dolára pred číslom, znamená to, že reťazec je pripnutý. 

Metóda 2

Táto metóda je takmer rovnaká ako predchádzajúca, iba musíte dvakrát stlačiť F4. ak by sme napríklad mali bunku B2, potom sa z nej stane B$2. Jednoducho povedané, týmto spôsobom sa nám podarilo opraviť linku. V tomto prípade sa zmení písmeno stĺpca.

Je to veľmi výhodné napríklad v tabuľkách, kde potrebujete zobraziť obsah druhej bunky zhora v spodnej bunke. Namiesto toho, aby ste takýto vzorec robili mnohokrát, stačí opraviť riadok a nechať zmeniť stĺpec.

Metóda 3

Je to úplne rovnaké ako predchádzajúca metóda, iba musíte trikrát stlačiť kláves F4. Potom bude absolútny iba odkaz na stĺpec a riadok zostane pevný.

Metóda 4

Predpokladajme, že máme absolútny odkaz na bunku, ale tu bolo potrebné urobiť ho relatívnym. Ak to chcete urobiť, stlačte kláves F4 toľkokrát, aby v odkaze neboli žiadne znaky $. Potom sa stane relatívnym a keď vzorec presuniete alebo skopírujete, zmení sa adresa stĺpca aj adresa riadka.

Pripnutie buniek pre veľký rozsah

Vidíme, že vyššie uvedené metódy nepredstavujú vôbec žiadne ťažkosti pri vykonávaní. Ale úlohy sú špecifické. A napríklad, čo robiť, ak máme naraz niekoľko desiatok vzorcov, ktorých väzby je potrebné zmeniť na absolútne. 

Žiaľ, štandardné metódy Excelu tento cieľ nedosiahnu. Ak to chcete urobiť, musíte použiť špeciálny doplnok s názvom VBA-Excel. Obsahuje mnoho ďalších funkcií, ktoré vám umožňujú vykonávať bežné úlohy s Excelom oveľa rýchlejšie.

Obsahuje viac ako sto užívateľsky definovaných funkcií a 25 rôznych makier a je tiež pravidelne aktualizovaný. Umožňuje vám zlepšiť prácu s takmer akýmkoľvek aspektom:

1. Bunky.
2. Makro.
3. Funkcie rôznych typov.
4. Odkazy a polia.

Tento doplnok vám predovšetkým umožňuje opraviť odkazy vo veľkom počte vzorcov naraz. Ak to chcete urobiť, musíte vykonať nasledujúce kroky:

1. Vyberte rozsah.
2. Otvorte kartu VBA-Excel, ktorá sa zobrazí po inštalácii. 
3. Otvorte ponuku „Funkcie“, kde sa nachádza možnosť „Zamknúť vzorce“.
   

   

4. Ďalej sa zobrazí dialógové okno, v ktorom musíte zadať požadovaný parameter. Tento doplnok vám umožňuje pripnúť stĺpec a stĺpec oddelene, spolu a tiež odstrániť už existujúce pripnutie pomocou balíka. Po výbere požadovaného parametra pomocou príslušného prepínača musíte potvrdiť svoje akcie kliknutím na „OK“.

Príklad

Uveďme si príklad, aby to bolo jasnejšie. Povedzme, že máme informácie, ktoré popisujú náklady na tovar, jeho celkové množstvo a tržby z predaja. A my stojíme pred úlohou, aby tabuľka na základe množstva a nákladov automaticky určila, koľko peňazí sa nám podarilo zarobiť bez odpočítania strát.

V našom príklade na to musíte zadať vzorec =B2*C2. Je to celkom jednoduché, ako vidíte. Na jej príklade je veľmi jednoduché opísať, ako môžete opraviť adresu bunky alebo jej jednotlivého stĺpca alebo riadka. 

Samozrejme, v tomto príklade môžete skúsiť potiahnuť vzorec nadol pomocou značky automatického dopĺňania, ale v tomto prípade sa bunky automaticky zmenia. Takže v bunke D3 bude ďalší vzorec, v ktorom budú čísla nahradené 3. Ďalej, podľa schémy – D4 – vzorec bude mať tvar = B4 * C4, D5 – podobne, ale s číslo 5 a tak ďalej.

Ak je to potrebné (vo väčšine prípadov sa ukáže), potom nie sú žiadne problémy. Ak však potrebujete opraviť vzorec v jednej bunke, aby sa pri ťahaní nezmenil, bude to o niečo zložitejšie. 

Predpokladajme, že musíme určiť príjem v dolároch. Dajme to do bunky B7. Poďme trochu nostalgicky a naznačme cenu 35 rubľov za dolár. Preto, aby bolo možné určiť príjem v dolároch, je potrebné rozdeliť sumu v rubľoch výmenným kurzom dolára.

Takto to vyzerá v našom príklade.

Ak sa podobne ako v predchádzajúcej verzii pokúsime predpísať vzorec, potom zlyháme. Podobne sa vzorec zmení na vhodný. V našom príklade to bude takto: =E3*B8. Odtiaľto môžeme vidieť. že prvá časť vzorca sa zmenila na E3 a túto úlohu sme si stanovili, ale nemusíme meniť druhú časť vzorca na B8. Preto musíme odkaz zmeniť na absolútny. Môžete to urobiť bez stlačenia klávesu F4, len vložením znaku dolára.

Potom, čo sme zmenili odkaz na druhú bunku na absolútnu, bola chránená pred zmenami. Teraz ho môžete bezpečne pretiahnuť pomocou rukoväte automatického dopĺňania. Všetky fixné údaje zostanú rovnaké bez ohľadu na polohu vzorca a neviazané údaje sa budú flexibilne meniť. Vo všetkých bunkách sa príjem v rubľoch popísaný v tomto riadku vydelí rovnakým výmenným kurzom dolára.

Samotný vzorec bude vyzerať takto:

=D2/$ B$7

Pozor! Naznačili sme dva znaky dolára. Týmto spôsobom ukážeme programu, že treba opraviť stĺpec aj riadok.

Odkazy na bunky v makrách

Makro je podprogram, ktorý vám umožňuje automatizovať akcie. Na rozdiel od štandardnej funkčnosti Excelu vám makro umožňuje okamžite nastaviť konkrétnu bunku a vykonať určité akcie v niekoľkých riadkoch kódu. Užitočné pre dávkové spracovanie informácií, napríklad ak nie je možné nainštalovať doplnky (napríklad sa používa firemný počítač, nie osobný).

Najprv musíte pochopiť, že kľúčovým konceptom makra sú objekty, ktoré môžu obsahovať iné objekty. Za elektronickú knihu (čiže dokument) zodpovedá objekt Workbooks. Zahŕňa objekt Sheets, ktorý je kolekciou všetkých listov otvoreného dokumentu. 

Bunky sú teda objektom bunky. Obsahuje všetky bunky konkrétneho listu.

Každý objekt je kvalifikovaný s argumentmi v zátvorkách. V prípade buniek sa na ne odkazuje v tomto poradí. Číslo riadku je uvedené ako prvé, potom nasleduje číslo stĺpca alebo písmeno (oba formáty sú prijateľné).

Napríklad riadok kódu obsahujúci odkaz na bunku C5 by vyzeral takto:

Pracovné zošity(“Zošit2.xlsm”).Hárok(”Zoznam2”).Cells(5, 3)

Pracovné zošity(“Zošit2.xlsm”).Hárok(”Zoznam2”).Cells(5, “C”)

K bunke môžete pristupovať aj pomocou objektu Uprataný. Vo všeobecnosti je určený na uvedenie odkazu na rozsah (ktorého prvky, mimochodom, môžu byť tiež absolútne alebo relatívne), ale môžete jednoducho zadať názov bunky v rovnakom formáte ako v dokumente programu Excel.

V tomto prípade bude riadok vyzerať takto.

Pracovné zošity(“Zošit2.xlsm”).Hárok(”Zoznam2”).Rozsah(”C5”)

Môže sa zdať, že táto možnosť je výhodnejšia, ale výhodou prvých dvoch možností je, že môžete použiť premenné v zátvorkách a uviesť odkaz, ktorý už nie je absolútny, ale niečo ako relatívny, ktorý bude závisieť od výsledkov výpočty.

Makrá sa teda dajú efektívne využívať v programoch. V skutočnosti všetky odkazy na bunky alebo rozsahy tu budú absolútne, a preto sa s nimi môžu tiež opraviť. Je pravda, že to nie je také pohodlné. Použitie makier môže byť užitočné pri písaní zložitých programov s veľkým počtom krokov v algoritme. Vo všeobecnosti je oveľa pohodlnejší štandardný spôsob používania absolútnych alebo relatívnych odkazov. 

Závery

Zistili sme, čo je odkaz na bunku, ako funguje a na čo slúži. Pochopili sme rozdiel medzi absolútnymi a relatívnymi referenciami a prišli sme na to, čo je potrebné urobiť, aby sa jeden typ zmenil na iný (jednoduchými slovami, opraviť adresu alebo ju odopnúť). Zistili sme, ako to môžete urobiť okamžite s veľkým počtom hodnôt. Teraz máte flexibilitu používať túto funkciu v správnych situáciách.