Co je dobré vědět
- Chcete-li vypočítat více položek na základě buněk jinde a zachovat konstantní řádek nebo sloupec, použijte absolutní odkaz na buňku.
- V této rovnici je absolutní odkaz na buňku A$12:=+B2+B2+A$12.
- Symbol $ „udržuje“řádek nebo sloupec konstantní i při kopírování nebo vyplnění sloupce nebo řádku stejným vzorcem.
Tento článek vysvětluje, jak používat absolutní odkaz na buňku v aplikaci Excel 2010 a novějších, aby se vzorce automaticky neměnily podle toho, kolik řádků nebo sloupců jste zaplnili.
Co je absolutní odkaz na buňku v Excelu?
Existují dva typy způsobů odkazování na buňku uvnitř vzorce v Excelu. Můžete použít relativní odkaz na buňku nebo absolutní odkaz na buňku.
- Relativní odkaz na buňku: Adresa buňky, která neobsahuje symbol $ před souřadnicemi řádku nebo sloupce. Tento odkaz automaticky aktualizuje sloupec nebo buňku vzhledem k původní buňce, když vyplníte dolů nebo napříč.
- Absolutní odkaz na buňku: Adresa buňky, která obsahuje symbol $ buď před souřadnicemi řádku nebo sloupce. To "udrží" referenční řádek nebo sloupec konstantní i při vyplnění sloupce nebo řádku stejným vzorcem.
Odkazování na buňky se může těm, kteří jsou v něm noví, jevit jako abstraktní pojem.
Jak používat absolutní odkaz na buňku v Excelu
Existuje několik různých způsobů použití absolutní reference v Excelu. Metoda, kterou použijete, závisí na tom, kterou část reference chcete zachovat konstantní: sloupec nebo řádek.
-
Vezměte si například tabulku, ve které chcete vypočítat skutečnou daň z obratu pro více položek na základě referenčních buněk jinde v listu.
Image -
V první buňce byste zadali vzorec pomocí standardních relativních odkazů na buňky pro ceny položek. Ale abyste do výpočtu vložili správnou daň z obratu, použili byste absolutní odkaz pro řádek, ale ne pro buňku.
Image -
Tento vzorec odkazuje na dvě buňky různými způsoby. B2 je buňka nalevo od první buňky daně z obratu vzhledem k pozici této buňky. Referenční číslo A$12 ukazuje na skutečné číslo daně z prodeje uvedené v A12. Symbol $ zachová odkaz na řádek 12 konstantní bez ohledu na to, kterým směrem vyplníte sousední buňky. Vyplňte všechny buňky pod touto první, abyste to viděli v akci.
Image -
Vidíte, že když vyplníte sloupec, buňka ve druhém sloupci používá relativní odkaz na buňky pro hodnoty nákladů ve sloupci B, čímž se sloupec zvýší tak, aby odpovídal sloupci daně z obratu. Odkaz na A12 však zůstává konstantní kvůli symbolu $, který udržuje odkaz na řádek stejný. Nyní vyplňte sloupec D počínaje buňkou C2.
Image -
Nyní můžete vidět, že vyplnění řádku vpravo používá správnou státní daň z obratu (Indiana), protože odkaz na sloupec je relativní (posouvá se o jednu doprava stejně jako buňka, kterou vyplňujete). Relativní odkaz na náklady se však nyní také posunul o jednu doprava, což je nesprávné. Můžete to opravit tak, že původní vzorec v C2 bude odkazovat na sloupec B s absolutním odkazem.
Image -
Umístěním symbolu $ před "B" jste vytvořili absolutní referenci pro sloupec. Nyní, když vyplníte vpravo, uvidíte, že odkaz na sloupec B pro cenu zůstává stejný.
Image -
Nyní, když vyplníte sloupec D dolů, uvidíte, že všechny relativní a absolutní odkazy na buňky fungují přesně tak, jak jste zamýšleli.
Image -
Absolutní odkazy na buňky v Excelu jsou zásadní pro řízení toho, na které buňky se odkazuje při vyplňování sloupců nebo řádků. Použijte symbol $, jakkoli potřebujete v odkazu na buňku, aby byl odkaz na řádek nebo sloupec konstantní, aby bylo možné odkazovat na správná data ve správné buňce. Když takto zkombinujete relativní a absolutní odkazy na buňky, nazývá se to smíšený odkaz na buňku.
Používáte-li smíšený odkaz, je důležité zarovnat sloupec nebo řádek zdrojových dat se sloupcem nebo řádkem, do kterého vzorec zadáváte. Pokud jste odkaz na řádek označili za relativní, pak mějte na paměti, že když vyplníte do strany, číslo sloupce zdrojových dat se bude zvyšovat spolu se sloupcem buňky vzorce. Toto je jedna z věcí, která novým uživatelům komplikuje relativní a absolutní adresování, ale jakmile se to naučíte, budete mít mnohem větší kontrolu nad natahováním zdrojových datových buněk do odkazů na buňky vzorce.
Použití absolutních odkazů na buňku k připnutí reference jedné buňky
Dalším přístupem k použití absolutního odkazování na buňky je jeho aplikování na sloupec i řádek, aby se vzorec v podstatě „připnul“k použití pouze jedné jediné buňky bez ohledu na to, kde se nachází.
Pomocí tohoto přístupu můžete vyplnit do strany nebo dolů a odkaz na buňku zůstane vždy stejný.
Použití absolutního odkazu na buňku ve sloupci i v řádku je účinné pouze v případě, že odkazujete pouze na jedinou buňku ve všech vyplňovaných buňkách.
-
Pomocí stejného příkladu tabulky výše můžete odkazovat pouze na sazbu daně jednoho státu přidáním symbolu $ do sloupce i odkazu na řádek.
Image -
To způsobí, že sloupec "A" i řádek "12" zůstanou konstantní, bez ohledu na to, jakým směrem vyplníte buňky. Chcete-li to vidět v akci, vyplňte celý sloupec pro daň z prodeje ME poté, co aktualizujete vzorec absolutním odkazem na sloupec a řádek. Vidíte, že každá vyplněná buňka vždy používá absolutní referenci $A$12. Nezmění se ani sloupec ani řádek.
Image -
Použijte stejný vzorec pro sloupec daně z obratu v Indianě, ale tentokrát použijte absolutní odkaz pro sloupec i řádek. V tomto případě je to $B$12.
Image -
Vyplňte tento sloupec a znovu uvidíte, že odkaz na B12 se v žádné buňce nemění díky absolutnímu odkazu pro sloupec i řádek.
Image -
Jak vidíte, existuje několik způsobů, jak můžete použít absolutní odkazy na buňky v Excelu k provedení podobných úkolů. Absolutní reference vám poskytují flexibilitu udržovat konstantní odkazy na buňky, i když vyplňujete velký počet sloupců nebo řádků.
Můžete procházet relativními nebo absolutními odkazy na buňky tak, že zvýrazníte odkaz a poté stisknete F4 Pokaždé, když stisknete F4, absolutní odkaz bude aplikován buď na sloupec, tj. řádek, sloupec i buňku nebo žádný z nich. Toto je jednoduchý způsob, jak upravit vzorec, aniž byste museli zadávat symbol $.
Kdy byste měli použít absolutní odkazy na buňky
V téměř každém odvětví a oboru existuje mnoho důvodů, proč byste mohli chtít v Excelu používat absolutní odkazy na buňky.
- Použití pevných multiplikátorů (jako je cena za jednotku) ve velkém seznamu položek.
- Při projekci ročních cílů zisku použijte jedno procento pro každý rok.
- Při vytváření faktur použijte absolutní odkazy, které odkazují na stejnou daňovou sazbu pro všechny položky.
- Pomocí absolutních odkazů na buňky ve správě projektu odkazujte na pevné sazby dostupnosti pro jednotlivé zdroje.
- Pomocí relativních odkazů na sloupce a absolutních odkazů na řádky porovnejte výpočty sloupců v odkazovaných buňkách s hodnotami sloupců v jiné tabulce.
Pokud pro sloupce nebo řádky používáte kombinaci relativních a absolutních odkazů, musíte se ujistit, že pozice sloupců nebo řádků zdrojových dat odpovídá sloupci nebo řádku cílových buněk (kde znovu zadejte vzorec).
