Absolutní reference vs relativní reference Excel vysvětleno

18. prosince 2021 391 Pohledy Absolutní reference vs. relativní reference Excel

Procházet témata příspěvků

Co je absolutní reference v Excelu?

Absolutní odkaz v aplikaci Excel umožňuje odkazovat na konkrétní řádek nebo sloupec, i když kopírujete do jiných buněk. To znamená, že když zkopírujete buňku nebo přetáhnete buňky, vzorce se NEZMĚNÍ s měnícími se buňkami.

Co je to Relativní referenční Excel?

Na druhou stranu relativní odkaz na buňku znamená, že se buňky automaticky mění. Když zkopírujete buňky do nových buněk, přetáhněte je, vzorec se změní s novými buňkami.

V tomto tutoriálu se naučíte, jak:

  • Jak používat absolutní referenci v Excelu
  • Jak používat Relativní referenční Excel
  • Rozdíly mezi těmito dvěma s příklady
  • Jak převést relativní odkazy na absolutní odkazy v Excelu

Představujeme Absolute Reference v Excelu

Dovolte mi zahájit tento tutoriál jednoduchým příkladem.

Absolutní referenční Excel vs. Relativní referenční Excel

Viz obrázek výše. Řekněme, že chcete přidat buňku B1 do C3 a zobrazit výsledek v buňce D3. Poté přidejte buňku B1 do buňky C4 a výsledek zobrazte v buňce D4 a tak dále. V buňce D3 vytvoříte vzorec níže:

|_+_|

Tím získáte požadovaný výsledek: 100 plus 10 se rovná 110.

Absolutní referenční Excel vs. Relativní referenční Excel

Obecně platí, že chcete-li replikovat stejný vzorec v buňkách D4 až D7, měli byste jednoduše přetáhnout buňky D4 až D7). To vám bohužel nepřinese požadovaný výsledek.

Výsledek replikace vzorce v buňce D3 přetažením přes buňky D4 až D7 je uveden níže:

Absolutní referenční Excel vs. Relativní referenční Excel

Zvýrazněné výsledky jsou chybné. Pamatujte, že jsme chtěli sečíst B1 až C4. Evidentně to nefungovalo!

Co se pokazilo?

Když jste přetáhli vzorec v buňce D3 přes D4 až D7, Excel použil relativní odkaz na buňku B1. Jednoduše přidal B2 k C4. Také přidal B3 k C4 a tak dále.

Absolutní referenční Excel vs. Relativní referenční Excel

Jak můžete vidět z obrázku výše, výsledkem v D4 je součet B2 a C4. Jak jsem řekl dříve, Excel vytvořil relativní referenci. Toto je výchozí nastavení, ale není to to, čeho jste chtěli dosáhnout.

Jak problém vyřešíte?

Chcete-li vyřešit výše uvedený problém, vytvořte místo toho absolutní odkaz v aplikaci Excel. Chcete-li to provést, zvýrazněte vzorec v buňce D3. Poté přidejte znak dolaru ($) před a za písmeno buňky, D.

Vzorec se nyní změní, jak je uvedeno níže:

|_+_|

Viz obrázek níže.

Přidáním znaku dolaru ($), jak je znázorněno, nutíte Excel odkazovat na buňku B1.

Důležitá rada
Všimněte si, že přidáte absolutní odkaz (zahrnutím znaku dolaru ($) do první buňky.

Nyní, když replikujete vzorec v buňce D3 přetažením přes buňky D4 až D7, budete mít požadovaný výsledek. Viz obrázek níže.

Bingo! Přidání absolutní reference v Excelu udělalo kouzlo! Excel jednoduše odkazoval na stejnou buňku ve všech buňkách.

Trochu více o relativních odkazech v Excelu

Než vás provedu některými příklady absolutních odkazů v Excelu, dovolte mi diskutovat o Relativním odkazu.

Jsou situace, kdy potřebujete relativní odkaz.

Řekněme, že máte dva sloupce se sadou čísel, jak je uvedeno níže. Vhodnější bude použití relativního odkazu v Excelu.

V tomto příkladu sečtete A3 a B3 v C3. Chcete-li replikovat vzorec v buňkách C4 až C7, zvýrazněte vzorec v buňce C3 a poté přetáhněte do buňky C7.

Zde je výsledek.

V tomto případě jsou výsledky podle očekávání. Přetažením vzorce v buňce C3 přes buňky C4 až C7 Excel použil relativní odkaz k replikaci vzorce. V tomto případě nemusíme ve vzorci udržovat konstantní odkaz na buňku.

Reálný příklad absolutní reference a relativní reference v Excelu

V této sekci vám chci ukázat skutečnou aplikaci absolutního a relativního referenčního excelu v podnikání. Chcete-li začít, prohlédněte si tabulku Excel na tomto obrázku níže:

Řekněme, že podnikáte nebo pracujete pro firmu a potřebujete určit prodejní cenu na základě ziskové přirážky a daně z obratu. Řekněme, že jste také povinni sečíst všechny náklady, prodejní cenu plus daň, zisk a daň z obratu.

Budete potřebovat znalost jak absolutní reference v Excelu, tak relativní reference v Excelu.

Na základě toho, co jste se v tomto tutoriálu dosud naučili, můžete vidět, že zisková přirážka a daň z obratu budou odkazovány pomocí absolutní reference. Na druhou stranu vytvoření vzorce pro součty bude vyžadovat relativní odkaz.

Vysvětlení mého příkladu listu Excel

Pro začátek si vysvětlíme, jak jsem dospěl k výsledkům v excelovém listu výše. Začněme buňkou D7.

Zde je snímek obrazovky.

Buňka má následující vzorec:

|_+_|

Abychom vám pomohli porozumět zdánlivě složitému vzorci, rozepíšu jej níže:

  • První část vzorce, C7+C7*$C, vypočítává zisk produktu A. Toho je dosaženo přičtením nákladové ceny v C7 k ziskové marži (C7*$C). Všimněte si, že C7 má relativní odkaz.
  • Zisková marže se vypočítá vynásobením nákladové ceny absolutní hodnotou ziskové marže v buňce C4.
  • Abych určil konečnou cenu produktu, musím nyní přidat daň z obratu.
Důležitá rada
Daň z prodeje (nebo daň z přidané hodnoty v některých zemích) se obvykle vypočítává tak, že se k vaší prodejní ceně připočte procento daně z obratu.

Jak jsem zahrnul přirážku k dani z obratu

  • Na základě výše uvedeného tipu bude konečná prodejní cena (prodejní cena produktu) + (prodejní cena produktu) vynásobená přirážkou daně z prodeje. Tak jsem dospěl ke vzorci ((C7+C7 $C))+((C7+C7 $C))*$C
  • (C7+C7 4 $ C$) je prodejní cena produktu. ((C7+C7 $C))*$C je přirážka k prodejní ceně.
  • U této druhé části výpočtu konečné prodejní ceny si všimněte, že jsem také odkazoval na absolutní hodnotu daně z prodeje v buňce C5
  • Abych replikoval vzorec v buňce D7, jednoduše jsem zvýraznil vzorec v D7 a přetáhl jsem do buňky D11.
  • Stejná logika platí pro vzorec, který jsem použil při výpočtu zisku (sloupec E). Vzorec v buňce E7 je C7*$C. Použije se relativní reference buňky s cenou produktu, C7, vynásobená absolutní referencí ziskové přirážky, buňka C4.
  • Na druhou stranu ve sloupci DPH se F vypočítá pouze pomocí relativních hodnot. Vzorec v buňce C7 je D7-C7-E7 (konečná prodejní cena mínus pořizovací cena, mínus zisk).

Doufám, že můžete vidět přínos těchto znalostí v reálné obchodní situaci.

Jak převést relativní odkaz na absolutní odkaz v aplikaci Excel pomocí F4

Mým posledním úkolem v tomto tutoriálu je ukázat vám, jak rychle převést relativní referenční vzorec na absolutní hodnotu pomocí F4. Tato znalost je důležitá při vytváření vzorce pomocí relativní hodnoty. Potom musíte vzorec převést na absolutní hodnoty.

Abych to demonstroval, dovolte mi ukázat vám, jak jsem vypočítal zisk v buňce E7.

  • Nejprve jsem do buňky přidal znaménko rovná se. Poté jsem vybral buňku ceny produktu C7 a přidal znaménko násobení (*). Dále jsem vybral buňku ziskové marže C4. Jak můžete vidět výše, C7 i C4 mají své relativní hodnoty.
  • Chcete-li převést buňku C4 na absolutní hodnotu v Excelu, jednoduše jsem držel myš vedle písmene buňky v řádku vzorců a poté stiskl F4. Znak dolaru ($) byl přidán automaticky. Viz snímek obrazovky níže.

To je vše. Absolutní referenční Excel vs. Relativní referenční Excel.

Doufám, že vám tato zóna S pomohla.

Případně nám laskavě poskytněte zpětnou vazbu pomocí formuláře Zanechat odpověď na konci této stránky.

Další e-maily a produktivní S zóny najdete na naší stránce E-maily a vysvětlení produktivity.