List v Excelu se skládá z buněk. Na tyto buňky lze odkazovat zadáním hodnoty řádku a hodnoty sloupce.
Například A1 by odkazovalo na první řádek (zadaný jako 1) a první sloupec (zadaný jako A). Podobně by B3 byl třetí řádek a druhý sloupec.
Síla Excelu spočívá v tom, že při vytváření vzorců můžete tyto odkazy na buňky použít v jiných buňkách.
Nyní existují tři druhy odkazů na buňky, které můžete použít v Excelu:
- Relativní odkazy na buňky
- Absolutní odkazy na buňky
- Reference smíšených buněk
Pochopení těchto různých typů odkazů na buňky vám pomůže pracovat se vzorci a ušetří čas (zejména při kopírování vzorců).
Co jsou to relativní odkazy na buňky v Excelu?
Dovolte mi, abych si na jednoduchém příkladu vysvětlil koncept relativních odkazů na buňky v Excelu.
Předpokládejme, že mám níže uvedenou sadu dat:
Chcete -li vypočítat součet pro každou položku, musíme vynásobit cenu každé položky množstvím této položky.
Pro první položku bude vzorec v buňce D2 B2* C2 (jak je uvedeno níže):
Nyní místo zadávání vzorce pro všechny buňky po jednom můžete jednoduše zkopírovat buňku D2 a vložit ji do všech ostatních buněk (D3: D8). Když to uděláte, všimnete si, že odkaz na buňku se automaticky upraví tak, aby odkazoval na odpovídající řádek. Například vzorec v buňce D3 se změní na B3*C3 a vzorec v D4 na B4*C4.
Tyto odkazy na buňky, které se samy upraví při kopírování buňky, se nazývají relativní odkazy na buňky v Excelu.
Kdy použít relativní odkazy na buňky v Excelu?
Relativní odkazy na buňky jsou užitečné, když musíte vytvořit vzorec pro rozsah buněk a vzorec musí odkazovat na relativní odkaz na buňku.
V takových případech můžete vytvořit vzorec pro jednu buňku a zkopírovat a vložit do všech buněk.
Co jsou absolutní odkazy na buňky v aplikaci Excel?
Na rozdíl od relativních odkazů na buňky se absolutní odkazy na buňky nemění, když vzorec zkopírujete do jiných buněk.
Předpokládejme například, že máte sadu dat, jak je uvedeno níže, kde musíte vypočítat provizi z celkového prodeje každé položky.
Provize je 20% a je uvedena v buňce G1.
Chcete -li získat částku provize za každý prodej položky, použijte následující vzorec v buňce E2 a zkopírujte pro všechny buňky:
= D2*$ G $ 1
Všimněte si, že v referenci buňky, která má provizi, jsou dva znaky dolaru ($) - $G$2.
Co dělá znak dolaru ($)?
Když je symbol dolaru přidán před číslo řádku a sloupce, činí jej absolutním (tj. Zabrání změně čísla řádku a sloupce při kopírování do jiných buněk).
Například ve výše uvedeném případě, když zkopíruji vzorec z buňky E2 do E3, změní se z = D2*$ G $ 1 na = D3*$ G $ 1.
Všimněte si toho, že zatímco D2 se mění na D3, $ G $ 1 se nemění.
Protože jsme přidali symbol dolaru před „G“ a „1“ v G1, nedovolilo by to při kopírování odkaz na buňku změnit.
Proto je odkaz na buňku absolutní.
Kdy použít v aplikaci Excel absolutní odkazy na buňky?
Absolutní odkazy na buňky jsou užitečné, pokud nechcete, aby se odkaz na buňku měnil při kopírování vzorců. To může být případ, kdy máte pevnou hodnotu, kterou musíte použít ve vzorci (například sazba daně, provize, počet měsíců atd.)
I když můžete tuto hodnotu také pevně kódovat ve vzorci (tj. Použít 20% místo $ G $ 2), mít ji v buňce a poté použít odkaz na buňku vám umožní ji v budoucnu změnit.
Pokud se například změní struktura vaší provize a vy nyní platíte 25% namísto 20%, můžete jednoduše změnit hodnotu v buňce G2 a všechny vzorce se automaticky aktualizují.
Co jsou to smíšené odkazy na buňky v Excelu?
Smíšené odkazy na buňky jsou o něco složitější než absolutní a relativní odkazy na buňky.
Mohou existovat dva typy odkazů na smíšené buňky:
- Řádek je uzamčen, zatímco sloupec se mění při kopírování vzorce.
- Sloupec je uzamčen, zatímco se řádek mění, když je vzorec zkopírován.
Podívejme se, jak to funguje na příkladu.
Níže je soubor dat, kde je třeba vypočítat tři úrovně provize na základě procentní hodnoty v buňce E2, F2 a G2.
Nyní můžete použít sílu smíšené reference k výpočtu všech těchto provizí pomocí jediného vzorce.
Do buňky E4 zadejte níže uvedený vzorec a zkopírujte pro všechny buňky.
= $ B4*$ C4*E $ 2
Výše uvedený vzorec používá oba druhy smíšených odkazů na buňky (jeden, kde je řádek uzamčen a druhý, kde je uzamčen sloupec).
Pojďme analyzovat každý odkaz na buňku a pochopit, jak funguje:
- $ B4 (a $ C4) - V tomto odkazu je znak dolaru přímo před notací sloupce, ale ne před číslem řádku. To znamená, že když zkopírujete vzorec do buněk vpravo, odkaz zůstane stejný, jako je sloupec pevný. Pokud například zkopírujete vzorec z E4 do F4, tento odkaz se nezmění. Když jej však zkopírujete, číslo řádku se změní, protože není uzamčeno.
- E $ 2 - V tomto odkazu je znak dolaru těsně před číslem řádku a notace sloupce nemá znak dolaru. To znamená, že když zkopírujete vzorec dolů do buněk, odkaz se nezmění, protože číslo řádku je uzamčeno. Pokud však vzorec zkopírujete doprava, abeceda sloupců se změní, protože není uzamčena.
Jak změnit referenci z relativní na absolutní (nebo smíšenou)?
Chcete -li změnit referenci z relativní na absolutní, musíte před znak sloupce a číslo řádku přidat znak dolaru.
Například A1 je relativní odkaz na buňku a stane se absolutním, když ho uděláte $ A $ 1.
Pokud máte jen několik odkazů na změnu, může být snadné tyto odkazy změnit ručně. Můžete tedy přejít na řádek vzorců a vzorec upravit (nebo vyberte buňku, stiskněte klávesu F2 a poté ji změňte).
Rychlejším způsobem je to však pomocí klávesové zkratky - F4.
Když vyberete odkaz na buňku (na řádku vzorců nebo v buňce v režimu úprav) a stisknete F4, odkaz se změní.
Předpokládejme, že máte v buňce odkaz = A1.
Zde je to, co se stane, když vyberete referenci a stisknete klávesu F4.
- Stiskněte jednou klávesu F4: Odkaz na buňku se změní z A1 na $ A $ 1 (stane se „absolutní“ z „relativního“).
- Stiskněte dvakrát klávesu F4: Odkaz na buňku se změní z A1 na A $ 1 (změní se na smíšený odkaz, kde je řádek uzamčen).
- Stiskněte třikrát klávesu F4: Odkaz na buňku se změní z A1 na $ A1 (změní se na smíšený odkaz, kde je sloupec uzamčen).
- Stiskněte čtyřikrát klávesu F4: Odkaz na buňku se znovu změní na A1.
