Jak zmrazit buňku ve vzorci aplikace Excel

Uživatelé se často potýkají s potřebou připnout buňku do vzorce. Vyskytuje se například v situacích, kdy chcete zkopírovat vzorec, ale tak, aby se odkaz neposouval nahoru a dolů o stejný počet buněk, jako byl zkopírován z původního umístění.

V tomto případě můžete opravit odkaz na buňku v aplikaci Excel. A to lze provést několika způsoby najednou. Podívejme se blíže na to, jak tohoto cíle dosáhnout.

Co je odkaz na Excel

List se skládá z buněk. Každý z nich obsahuje specifické informace. Jiné buňky jej mohou použít ve výpočtech. Jak ale pochopí, odkud data získat? Pomáhá jim vytvářet odkazy.

Každý odkaz označuje buňku s jedním písmenem a jedním číslem. Písmeno představuje sloupec a číslo představuje řádek. 

Existují tři typy vazeb: absolutní, relativní a smíšené. Druhý je standardně nastaven. Absolutní odkaz je takový, který má pevnou adresu sloupce i sloupce. Smíšené je tedy takové, kde je pevný buď samostatný sloupec nebo řádek.

Metoda 1

Chcete-li uložit adresy sloupců i řádků, postupujte takto:

1. Klikněte na buňku obsahující vzorec.
2. Klikněte na řádek vzorců pro buňku, kterou potřebujeme.
3. Stiskněte tlačítko F4.

V důsledku toho se odkaz na buňku změní na absolutní. Pozná se podle charakteristického znaku dolaru. Pokud například kliknete na buňku B2 a poté kliknete na F4, bude odkaz vypadat takto: $B$2.

Co znamená znak dolaru před částí adresy na buňku?

1. Pokud je umístěn před písmeno, znamená to, že odkaz na sloupec zůstává stejný bez ohledu na to, kam byl vzorec přesunut.
2. Pokud je znak dolaru před číslem, znamená to, že řetězec je připnutý. 

Metoda 2

Tato metoda je téměř stejná jako předchozí, pouze musíte dvakrát stisknout F4. pokud bychom měli například buňku B2, pak se z ní stane B$2. Jednoduše řečeno, tímto způsobem se nám podařilo opravit linku. V tomto případě se změní písmeno sloupce.

Velmi pohodlné je to například v tabulkách, kde je potřeba v dolní buňce zobrazit obsah druhé buňky shora. Místo toho, abyste takový vzorec dělali mnohokrát, stačí opravit řádek a nechat změnit sloupec.

Metoda 3

Toto je zcela stejné jako předchozí metoda, pouze musíte třikrát stisknout klávesu F4. Pak bude absolutní pouze odkaz na sloupec a řádek zůstane pevný.

Metoda 4

Předpokládejme, že máme absolutní odkaz na buňku, ale zde bylo nutné, aby byl relativní. Chcete-li to provést, stiskněte klávesu F4 tolikrát, aby v odkazu nebyly žádné znaky $. Pak se stane relativním a když vzorec přesunete nebo zkopírujete, změní se adresa sloupce i adresa řádku.

Připínací buňky pro velký rozsah

Vidíme, že výše uvedené metody nepředstavují vůbec žádné potíže při provádění. Úkoly jsou ale specifické. A například co dělat, když máme najednou několik desítek vzorců, jejichž vazby je třeba převést na absolutní. 

Bohužel standardní metody Excelu tohoto cíle nedosáhnou. K tomu je potřeba použít speciální addon s názvem VBA-Excel. Obsahuje mnoho dalších funkcí, které vám umožní provádět běžné úkoly s Excelem mnohem rychleji.

Obsahuje více než sto uživatelsky definovaných funkcí a 25 různých maker a je také pravidelně aktualizován. Umožňuje vám zlepšit práci s téměř jakýmkoliv aspektem:

1. Buňky.
2. Makro
3. Funkce různých typů.
4. Odkazy a pole.

Tento doplněk umožňuje zejména opravit odkazy ve velkém počtu vzorců najednou. Chcete-li to provést, musíte provést následující kroky:

1. Vyberte rozsah.
2. Otevřete kartu VBA-Excel, která se zobrazí po instalaci. 
3. Otevřete nabídku „Funkce“, kde se nachází možnost „Zamknout vzorce“.
   

   

4. Dále se zobrazí dialogové okno, ve kterém je třeba zadat požadovaný parametr. Tento doplněk umožňuje připnout sloupec a sloupec samostatně, společně a také odstranit již existující připnutí pomocí balíčku. Po výběru požadovaného parametru pomocí odpovídajícího přepínače musíte potvrdit své akce kliknutím na „OK“.

Příklad

Vezměme si příklad, aby to bylo jasnější. Řekněme, že máme informace, které popisují náklady na zboží, jeho celkové množství a tržby z prodeje. A my stojíme před úkolem, aby tabulka na základě množství a nákladů automaticky určila, kolik peněz se nám podařilo vydělat, aniž bychom odečítali ztráty.

V našem příkladu k tomu musíte zadat vzorec =B2*C2. Je to docela jednoduché, jak vidíte. Na jejím příkladu je velmi snadné popsat, jak můžete opravit adresu buňky nebo jejího jednotlivého sloupce nebo řádku. 

V tomto příkladu můžete samozřejmě zkusit přetáhnout vzorec dolů pomocí značky automatického vyplňování, ale v tomto případě se buňky automaticky změní. Takže v buňce D3 bude další vzorec, kde budou čísla nahrazena 3. Dále, podle schématu – D4 – vzorec bude mít tvar = B4 * C4, D5 – podobně, ale s číslo 5 a tak dále.

Pokud je to nutné (ve většině případů se to ukáže), pak nejsou žádné problémy. Pokud ale potřebujete opravit vzorec v jedné buňce, aby se při přetahování neměnil, bude to poněkud obtížnější. 

Předpokládejme, že potřebujeme určit dolarové příjmy. Dáme to do buňky B7. Pojďme trochu nostalgicky a naznačíme cenu 35 rublů za dolar. Proto, aby bylo možné určit příjem v dolarech, je nutné vydělit částku v rublech směnným kurzem dolaru.

Zde je návod, jak to vypadá v našem příkladu.

Pokud se podobně jako v předchozí verzi pokusíme předepsat vzorec, pak selžeme. Podobně se vzorec změní na vhodný. V našem příkladu to bude takto: =E3*B8. Odtud můžeme vidět. že se první část vzorce změnila na E3 a tento úkol jsme si dali, ale druhou část vzorce na B8 měnit nemusíme. Proto musíme referenci proměnit v absolutní. Můžete to udělat bez stisknutí klávesy F4, pouze vložením znaku dolaru.

Poté, co jsme změnili odkaz na druhou buňku na absolutní, byla chráněna před změnami. Nyní jej můžete bezpečně přetáhnout pomocí rukojeti automatického plnění. Všechna pevná data zůstanou stejná bez ohledu na polohu vzorce a nezavázaná data se budou flexibilně měnit. Ve všech buňkách bude příjem v rublech popsaný v tomto řádku vydělen stejným směnným kurzem dolaru.

Samotný vzorec bude vypadat takto:

=D2/$ B$7

Pozor! Naznačili jsme dva znaky dolaru. Tímto způsobem ukážeme programu, že je potřeba opravit sloupec i řádek.

Odkazy na buňky v makrech

Makro je podprogram, který umožňuje automatizovat akce. Na rozdíl od standardní funkce Excelu vám makro umožňuje okamžitě nastavit konkrétní buňku a provést určité akce v několika řádcích kódu. Užitečné pro dávkové zpracování informací, například pokud není možné instalovat doplňky (například se používá firemní počítač, nikoli osobní).

Nejprve musíte pochopit, že klíčovým konceptem makra jsou objekty, které mohou obsahovat další objekty. Za elektronickou knihu (tedy dokument) je zodpovědný objekt Workbooks. Zahrnuje objekt Listy, což je kolekce všech listů otevřeného dokumentu. 

V souladu s tím jsou buňky objektem Cells. Obsahuje všechny buňky konkrétního listu.

Každý objekt je označen argumenty v závorkách. V případě buněk se na ně odkazuje v tomto pořadí. Číslo řádku je uvedeno jako první, následuje číslo sloupce nebo písmeno (přijatelné jsou oba formáty).

Řádek kódu obsahující odkaz na buňku C5 by například vypadal takto:

Sešity(“Sešit2.xlsm”).Listy(”Seznam2”).Cells(5, 3)

Sešity(“Sešit2.xlsm”).Listy(”Seznam2”).Cells(5, “C”)

K buňce můžete také přistupovat pomocí objektu Uklidit. Obecně je určen k odkazování na rozsah (jehož prvky mimochodem mohou být také absolutní nebo relativní), ale můžete jednoduše zadat název buňky ve stejném formátu jako v dokumentu aplikace Excel.

V tomto případě bude čára vypadat takto.

Sešity(“Sešit2.xlsm”).Listy(”Seznam2”).Rozsah(”C5”)

Může se zdát, že tato možnost je pohodlnější, ale výhodou prvních dvou možností je, že můžete použít proměnné v závorkách a uvést odkaz, který již není absolutní, ale něco jako relativní, což bude záviset na výsledcích výpočty.

Makra lze tedy efektivně využívat v programech. Ve skutečnosti všechny odkazy na buňky nebo rozsahy zde budou absolutní, a proto s nimi lze také opravit. Pravda, není to tak pohodlné. Použití maker může být užitečné při psaní složitých programů s velkým počtem kroků v algoritmu. Obecně je mnohem pohodlnější standardní způsob použití absolutních nebo relativních odkazů. 

Závěry

Zjistili jsme, co je odkaz na buňku, jak funguje, k čemu slouží. Pochopili jsme rozdíl mezi absolutními a relativními referencemi a přišli jsme na to, co je třeba udělat, aby se jeden typ změnil na jiný (jednoduše řečeno, opravit adresu nebo ji uvolnit). Přišli jsme na to, jak to můžete udělat okamžitě s velkým množstvím hodnot. Nyní máte možnost tuto funkci používat ve správných situacích.