Přejít k hlavnímu obsahu

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.

Stáhněte si zdarma ukázkový soubor ukázka dokumentu


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
doc absolutní reference 3 1   doc absolutní reference 4 1

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í
  1. Dvojitým kliknutím na buňku se vzorcem vstoupíte do režimu úprav;
  2. Umístěte kurzor na odkaz na buňku, kterou chcete nastavit jako absolutní;
  3. 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;
  4. 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

přepínání absolutní reference f4 1

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

přepínání absolutní reference f4 2


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í.

Poznámky:

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.

doc absolutní reference 15 1

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.

doc absolutní reference 16 1

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é.

doc absolutní reference 17 1

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.

doc absolutní reference 18 1

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

doc absolutní reference 19 1

Nyní můžete přetáhnout rukojeť automatického plnění doprava nebo dolů, abyste získali všechny výsledky.

doc absolutní reference 20 1


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

🤖 Kutools AI asistent: Revoluční analýza dat založená na: Inteligentní provedení   |  Generovat kód  |  Vytvořte vlastní vzorce  |  Analyzujte data a generujte grafy  |  Vyvolejte funkce Kutools...
Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup    VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar    Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky    Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50+ Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

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...

Popis


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!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations