Excel absolutní reference (jak vytvořit a používat)
Při odkazování na buňku ve vzorci v Excelu je výchozím typem odkazu relativní odkaz. Tyto odkazy se změní, když je vzorec zkopírován do jiných buněk na základě jejich relativního sloupce a řádku. Pokud chcete zachovat konstantní odkaz bez ohledu na to, kde je vzorec zkopírován, musíte použít absolutní odkaz.
- Co je absolutní reference
- Jak dělat absolutní reference
- Použijte absolutní odkaz s příklady
- Vypočítejte procento z celkového počtu
- Vyhledejte hodnotu a vraťte se k odpovídající odpovídající hodnotě
- 2 kliknutí pro dávkové vytvoření absolutních odkazů na buňky pomocí Kutools
- Relativní odkaz a smíšený odkaz
- Věci, které si pamatujete
Stáhněte si zdarma ukázkový soubor
Video: Absolutní reference
Co je absolutní reference
Absolutní odkaz je typ odkazu na buňku v aplikaci Excel.
Ve srovnání s relativním odkazem, který se změní na základě jeho relativní polohy při kopírování vzorce do jiných buněk, zůstane absolutní odkaz konstantní bez ohledu na to, kam se vzorec zkopíruje nebo přesune.
Absolutní odkaz se vytvoří přidáním znaku dolaru ($) před odkazy na sloupce a řádky ve vzorci. Chcete-li například vytvořit absolutní odkaz pro buňku A1, měli byste ji reprezentovat jako $A$1.
Absolutní odkazy jsou užitečné, když chcete odkazovat na pevnou buňku nebo rozsah ve vzorci, který bude zkopírován do více buněk, ale nechcete, aby se odkaz měnil.
Například rozsah A4:C7 obsahuje ceny produktů a chcete získat splatnou daň každého produktu na základě daňové sazby v buňce B2.
Pokud ve vzorci použijete relativní odkaz, jako je „=B5*B2“, vrátí se některé nesprávné výsledky, když přetáhnete úchyt automatického vyplňování dolů, abyste použili tento vzorec. Protože odkaz na buňku B2 se změní vzhledem k buňkám ve vzorci. Nyní je vzorec v buňce C6 "=B6*B3" a vzorec v buňce C7 je "=B7*B4"
Pokud však použijete absolutní odkaz na buňku B2 se vzorcem "=B5*$B$2", zajistíte, že sazba daně zůstane stejná pro všechny buňky, když vzorec přetáhnete dolů pomocí úchytu automatického vyplňování, jsou výsledky správné.
Použití relativní reference | Použití absolutní reference | |
Jak dělat absolutní reference
Chcete-li v aplikaci Excel vytvořit absolutní odkaz, musíte před odkazy na sloupce a řádky ve vzorci přidat znaky dolaru ($). Existují dva způsoby, jak vytvořit absolutní referenci:
Ručně přidejte znaky dolaru do odkazu na buňku
Při psaní vzorce do buňky můžete ručně přidat znaky dolaru ($) před odkazy na sloupce a řádky, které chcete nastavit jako absolutní.
Chcete-li například přidat čísla v buňce A1 a B1 a učinit obě absolutními, zadejte vzorec jako "=$A$1+$B$1". To zajistí, že odkazy na buňky zůstanou konstantní, když vzorec zkopírujete nebo přesunete do jiných buněk.
Nebo pokud chcete změnit odkazy v existujícím vzorci v buňce na absolutní, můžete buňku vybrat a poté přejít na řádek vzorců a přidat znaky dolaru ($).
Pomocí zkratky F4 převedete relativní odkaz na absolutní
- Dvojitým kliknutím na buňku se vzorcem vstoupíte do režimu úprav;
- Umístěte kurzor na odkaz na buňku, kterou chcete nastavit jako absolutní;
- Stisknout F4 stiskněte na klávesnici pro přepínání typů odkazů, dokud se před odkazy na sloupce i řádky nepřidají znaky dolaru;
- Stisknout vstoupit pro ukončení režimu úprav a použití změn.
Klávesa F4 může přepínat referenci mezi relativní referencí, absolutní referencí a smíšenou referencí.
A1 → $A$1 → A$1 → $A1 → A1
Pokud chcete, aby byly všechny odkazy ve vzorci absolutní, vyberte celý vzorec v řádku vzorců a stiskněte F4 pro přepínání typů odkazů, dokud se před odkazy na sloupce i řádky nepřidají znaky dolaru.
A1+B1 → $A$1+$B$1 → A$1+B$1 → $A1+$B1 → A1+B1
Použijte absolutní odkaz s příklady
Tato část obsahuje 2 příklady, které ukazují, kdy a jak používat absolutní odkazy ve vzorci aplikace Excel.
Příklad 1 Vypočítejte procento z celku
Předpokládejme, že máte rozsah dat (A3:B7) obsahující prodeje každého ovoce a buňka B8 obsahuje celkovou částku prodeje těchto druhů ovoce, nyní chcete vypočítat procento prodeje každého ovoce z celkového počtu.
Obecný vzorec pro výpočet procenta z celku:
Percentage = Sale/Amount
Pomocí relativního odkazu ve vzorci získáte procento prvního ovoce takto:
=B4/B8
Když přetáhnete rukojeť automatického plnění dolů, abyste vypočítali procento jiného ovoce, #DIV/0! chyby budou vráceny.
Vzhledem k tomu, že když přetáhnete úchyt automatického vyplňování, abyste zkopírovali vzorec do buněk níže, relativní odkaz B8 se automaticky upraví na jiné odkazy na buňky (B9, B10, B11) na základě jejich relativních pozic. A buňka B9, B10 a B11 jsou prázdné (nuly), když je dělitel nula, vzorec se vrátí k chybě.
Chcete-li chyby opravit, musíte v tomto případě nastavit odkaz na buňku B8 jako absolutní ($B$8) ve vzorci, aby se nezměnil, když vzorec kamkoli přesunete nebo zkopírujete. Nyní je vzorec aktualizován na:
=B4/$B$8
Poté přetáhněte rukojeť automatického plnění dolů, abyste vypočítali procento jiného ovoce.
Příklad 2 Vyhledejte hodnotu a vraťte se k odpovídající odpovídající hodnotě
Za předpokladu, že chcete vyhledat seznam jmen v D4:D5 a vrátit jim odpovídající platy na základě jmen zaměstnanců a odpovídajících ročních platů uvedených v rozsahu (A4:B8).
Obecný vzorec pro vyhledávání je:
=VLOOKUP(lookup_value, table_range, column_index, logical)
Pokud použijete relativní odkaz ve vzorci k vyhledání hodnoty a vrácení odpovídající hodnoty shody takto:
=VLOOKUP(D4,A4:B8,2,FALSE)
Poté přetáhněte úchyt automatického plnění dolů a vyhledejte hodnotu níže, vrátí se chyba.
Když přetáhnete úchyt výplně dolů, abyste zkopírovali vzorec do buňky níže, odkazy ve vzorci se automaticky upraví o jeden řádek dolů. V důsledku toho se odkaz na rozsah tabulky A4:B8 změní na A5:B9. Protože "Lisa: nelze nalézt v rozsahu A5:B9, vzorec vrátí chybu.
Chcete-li se vyhnout chybám, použijte ve vzorci absolutní odkaz $A$4:$B$8 namísto relativního odkazu A4:B8:
=VLOOKUP(D4,$A$4:$B$8,2,FALSE)
Poté přetáhněte rukojeť automatického plnění dolů, abyste získali plat Lisy.
2 kliknutí pro dávkové vytvoření absolutních odkazů na buňky pomocí Kutools
Ať už se rozhodnete psát ručně, nebo použijete zkratku F4, v Excelu můžete změnit pouze jeden vzorec najednou. Pokud chcete, aby odkazy na buňky ve stovkách vzorců byly v Excelu absolutní, Převést odkazy nástroj Kutools pro Excel vám může pomoci zvládnout úlohu pomocí 2 kliknutí.
Vyberte buňky vzorce, u kterých chcete, aby byly odkazy na buňky absolutní, klepněte Kutools > více (fx) > Převést odkazy. Pak zvolte Absolutně a klepněte na tlačítko Ok or Podat žádost. Nyní byly všechny odkazy na buňky vybraných vzorců převedeny na absolutní.
-
Funkce Převést odkazy změní všechny odkazy na buňky ve vzorci.
-
Chcete-li používat funkci Převést odkazy, měli byste nejprve nainstalovat Kutools pro Excel. Kliknutím stáhnete a získáte 30denní bezplatnou zkušební verzi.
Relativní odkaz a smíšený odkaz
Kromě absolutní reference existují další dva typy reference: relativní reference a smíšená reference.
Relativní odkaz je výchozí typ odkazu v aplikaci Excel, který neobsahuje znaky dolaru ($) před odkazy na řádky a sloupce. A když vzorec s relativními odkazy zkopírujete nebo přesunete do jiných buněk, odkazy se automaticky změní na základě jejich relativní polohy.
Když například zadáte vzorec do buňky, jako je "=A1+1", potom přetáhněte úchyt automatického vyplňování dolů a vyplňte tento vzorec do další buňky, vzorec se automaticky změní na "=A2+1".
Smíšené reference se skládá jak z absolutní reference, tak z relativní reference. Jinými slovy smíšený odkaz používá znak dolaru ($) k opravě řádku nebo sloupce, když je vzorec zkopírován nebo vyplněn.
Vezměte si jako příklad násobilku, v řádcích a sloupcích jsou uvedena čísla od 1 do 9, která budete vzájemně násobit.
Chcete-li začít, můžete použít vzorec "=B3*C2" v buňce C3 a vynásobit 1 v buňce B3 číslem (1) v prvním sloupci. Když však přetáhnete úchyt automatického vyplňování doprava, abyste vyplnili ostatní buňky, všimnete si, že všechny výsledky jsou nesprávné, kromě prvního.
Je to proto, že když vzorec zkopírujete doprava, pozice řádku se nezmění, ale pozice sloupce se změní z B3 na C3, D3 atd.. Výsledkem je, že vzorce v pravých buňkách (D3, E3, atd.) změňte na "=C3*D2", "=D3*E2" a tak dále, pokud ve skutečnosti chcete, aby byly "=B3*D2", "=B3*E2" a tak dále.
V tomto případě musíte přidat znak dolaru ($), abyste uzamkli odkaz na sloupec „B3“. Použijte níže uvedený vzorec:
=$B3*C2
Když nyní vzorec přetáhnete doprava, výsledky jsou správné.
Poté musíte vynásobit číslo 1 v buňce C2 čísly v řádcích níže.
Když vzorec zkopírujete dolů, pozice sloupce buňky C2 se nezmění, ale pozice řádku se změní z C2 na C3, C4 atd. V důsledku toho se vzorce v buňkách níže změní na "=$B4C3", "=$B5C4" atd., což povede k nesprávným výsledkům.
Chcete-li tento problém vyřešit, změňte „C2“ na „C$2“, aby se odkaz na řádek neměnil při přetahování úchytu automatického vyplňování dolů pro vyplnění vzorců.
=$B3*C$2
Nyní můžete přetáhnout rukojeť automatického plnění doprava nebo dolů, abyste získali všechny výsledky.
Věci, které si pamatujete
-
Souhrn odkazů na buňky
Styl Příklad Shrnutí Absolutní reference $ A $ 1 Nikdy neměňte, když je vzorec zkopírován do jiných buněk Relativní reference A1 Jak odkaz na řádek, tak na sloupec se změní na základě relativní pozice, když je vzorec zkopírován do jiných buněk Smíšené reference $A1/A$1
Odkaz na řádek se změní, když je vzorec zkopírován do jiných buněk, ale odkaz na sloupec je pevný/Odkaz na sloupec se změní, když je vzorec zkopírován do jiných buněk, ale odkaz na řádek je pevný; -
Obecně platí, že absolutní odkazy se při přesunutí vzorce nikdy nemění. Absolutní odkazy se však automaticky upraví při přidání nebo odebrání řádku nebo sloupce z horní nebo levé strany listu. Například ve vzorci "=$A$1+1", když vložíte řádek do horní části listu, vzorec se automaticky změní na "=$A$2+1".
-
Projekt F4 lze přepínat mezi relativní referencí, absolutní referencí a smíšenou referencí.
Nejlepší nástroje pro produktivitu v kanceláři
Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času. Kliknutím sem získáte funkci, kterou nejvíce potřebujete...
Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci
- Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
- Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
- Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
Obsah
- Video: Absolutní reference
- Co je absolutní reference
- Jak dělat absolutní reference
- Použijte absolutní odkaz s příklady
- Vypočítejte procento z celkového počtu
- Vyhledejte hodnotu a vraťte se k odpovídající odpovídající hodnotě
- 2 kliknutí pro dávkové vytvoření absolutních odkazů na buňky pomocí Kutools
- Relativní odkaz a smíšený odkaz
- Věci, které si pamatujete
- Související články
- Nejlepší kancelářské nástroje produktivity
- Komentáře