Note: The other languages of the website are Google-translated. Back to English

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

    typ 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ší kancelářské nástroje produktivity

Kutools for Excel Vyřeší většinu vašich problémů a zvýší vaši produktivitu o 80 %

  • Opakované použití: Rychle vložte složité vzorce, grafy a cokoli, co jste dříve používali; Šifrovat buňky s heslem; Vytvořte seznam adresátů a posílat e-maily ...
  • Super Formula Bar (snadno upravit více řádků textu a vzorce); Rozložení pro čtení (snadno číst a upravovat velké množství buněk); Vložit do filtrovaného rozsahu...
  • Sloučit buňky / řádky / sloupce bez ztráty dat; Rozdělit obsah buněk; Zkombinujte duplicitní řádky / sloupce... Zabraňte duplicitním buňkám; Porovnat rozsahy...
  • Vyberte možnost Duplikovat nebo Jedinečný Řádky; Vyberte prázdné řádky (všechny buňky jsou prázdné); Super hledání a fuzzy hledání v mnoha sešitech; Náhodný výběr ...
  • Přesná kopie Více buněk beze změny odkazu na vzorec; Automaticky vytvářet reference do více listů; Vložte odrážky, Zaškrtávací políčka a další ...
  • Extrahujte text, Přidat text, Odebrat podle pozice, Odebrat mezeru; Vytváření a tisk mezisoučtů stránkování; Převod mezi obsahem buněk a komentáři...
  • Super filtr (uložit a použít schémata filtrů na jiné listy); Rozšířené řazení podle měsíce / týdne / dne, frekvence a dalších; Speciální filtr tučnou kurzívou ...
  • Kombinujte sešity a pracovní listy; Sloučit tabulky na základě klíčových sloupců; Rozdělte data do více listů; Dávkový převod xls, xlsx a PDF...
  • Více než 300 výkonných funkcí. Podporuje Office / Excel 2007-2021 a 365. Podporuje všechny jazyky. Snadné nasazení ve vašem podniku nebo organizaci. Plné funkce 30denní bezplatná zkušební verze. 60denní záruka vrácení peněz.
karta kte 201905

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!
officetab dno
Komentáře (0)
Zatím žádné hodnocení. Buďte první, kdo ohodnotí!
Zatím zde nejsou žádné komentáře

Sociální sítě

Copyright © 2009 - www.extendoffice.com. | Všechna práva vyhrazena. Poháněno ExtendOffice. | |. | Sitemap
Microsoft a logo Office jsou ochranné známky nebo registrované ochranné známky společnosti Microsoft Corporation ve Spojených státech a / nebo jiných zemích.
Chráněno Sectigo SSL