Odkaz na buňku Excel (obsah)

  • Reference buněk v Excelu
  • Typy odkazů na buňky v Excelu
    • # 1 - Relativní reference buněk v Excelu
    • # 2- Absolutní reference buněk v Excelu
    • # 3- Smíšené odkazy na buňky v Excelu

Reference buněk v Excelu

Pokud budete požádáni o zmínku o jedné funkci Excelu, která skutečně dělá zážitek magickým při práci se vzorci, pravděpodobně by to byla skutečnost, že tvrdé kódování hodnot do vašich vzorců není nutné (ve skutečnosti to ani není doporučeno).

V Excelu se tabulky skládají ze sloupců a řádků, které tvoří „buňky“. Každá buňka odpovídá přesnému sloupci a přesné řadě. Pro vysvětlení tohoto příkladu se buňka B2 týká druhého sloupce (B) a druhého řádku (2). D16 tedy ukazuje na čtvrtý sloupec (D) a šestnáctý řádek (16). Skutečný okraj aplikace Excel spočívá v použitelnosti odkazů na buňky v jiných buňkách při vytváření vzorců.

Typy odkazů na buňky v Excelu

V Excelu máme tři různé typy buněčných odkazů -

  1. Relativní odkaz na buňku v Excelu
  2. Absolutní odkaz na buňku v Excelu
  3. Mixed Cell Reference v Excelu

Použití správného typu Cell Reference v excelu v konkrétním scénáři ušetří spoustu času a úsilí a usnadní práci.

# 1 - Relativní reference buněk v Excelu

Relativní odkazy na buňky v Excelu označují buňku nebo řadu buněk v Excelu. Pokaždé, když je hodnota zadána do vzorce, jako je SUMIFS, je možné do Excelu vložit „odkaz na buňku“ jako náhradu za pevně kódované číslo. Odkaz na buňku může přijít ve formě B2, kde B odpovídá písmenu sloupce příslušné buňky a 2 představuje číslo řádku. Kdykoli Excel narazí na odkaz na buňku, navštíví konkrétní buňku, extrahuje její hodnotu a použije tuto hodnotu v kterémkoli vzorci, který píšete. Když je tento odkaz na buňku v Excelu duplikován na jiné místo, relativní odkazy na buňku v Excelu se také odpovídajícím způsobem změní.

Když odkazujeme na buňky, jako je tento, můžeme to dosáhnout s kterýmkoli ze dvou typů referenčních buněk v Excelu: absolutní a relativní. Vymezení mezi těmito dvěma odlišnými referenčními typy je odlišné vlastní chování, když je přetáhnete nebo zkopírujete a vložíte do různých buněk. Relativní odkazy na buňky se mohou měnit a upravovat při jejich kopírování a vkládání; absolutní reference naopak nemají. Aby bylo možné úspěšně dosáhnout výsledků v Excelu, je důležité mít možnost používat relativní a absolutní odkazy na buňky správným způsobem.

Jak efektivně využít relativní odkaz na buňku v Excelu?

Abychom mohli komplexně porozumět všestrannosti a použitelnosti této úžasné funkce aplikace Excel, musíme se podívat na několik praktických příkladů, abychom pochopili její skutečnou hodnotu.

Tuto šablonu Cell Reference Excel si můžete stáhnout zde - Cell Reference Excel Template

Příklad č. 1

Podívejme se na jednoduchý příklad, který vysvětlí mechaniku Relativní buněčné reference v Excelu. Pokud si přejeme mít součet dvou čísel ve dvou různých buňkách - A1 a A2, a mít výsledek ve třetí buňce A3.

Použijeme tedy vzorec = A1 + A2

Což by přineslo výsledek jako 100 v A3.

Nyní předpokládejme, že máme podobný scénář v dalším sloupci (B). Buňka B1 a B2 mají dvě čísla a my chceme mít součet v B3.

Toho můžeme dosáhnout dvěma různými způsoby:

Zde fyzicky zapíšeme vzorec pro přidání dvou buněk B1 a B2 do B3.

Výsledek jako 30.

Nebo bychom mohli jednoduše zkopírovat vzorec z buňky A3 a vložit do buňky B3 (to by fungovalo, pokud bychom vzorec přetáhli také z A3 na B3).

Když tedy zkopírujeme obsah buňky A3 a vložíme do B3 nebo přetáhneme obsah buňky A3 a vložíme do B3, vzorec se zkopíruje, nikoli výsledek. Stejného výsledku bychom mohli dosáhnout pravým kliknutím na buňku A3 a použitím možnosti Kopírovat.

A poté přejdeme na další buňku B3 a klikneme pravým tlačítkem a vybereme „Vzorce (f)“.

To znamená, že buňka A3 = A1 + A2. Když zkopírujeme A3 a přesuneme jednu buňku doprava a vložíme ji do buňky B3, vzorec se automaticky přizpůsobí a změní se na B3 = B1 + B2. Místo toho použije souhrnný vzorec pro buňky B1 a B2.

Příklad č. 2

Nyní se podívejme na další praktický scénář, který by tento koncept udělal zcela jasně. Předpokládejme, že máme datový soubor, který se skládá z jednotkové ceny produktu a množství prodaného za každý z nich. Naším cílem je nyní vypočítat prodejní cenu, kterou lze popsat pomocí následujícího vzorce:

Prodejní cena = jednotková cena x prodané jednotky

Abychom mohli najít prodejní cenu, musíme nyní pro každý produkt znásobit jednotkovou cenu jednotkami prodanými. Nyní tedy přistoupíme k použití tohoto vzorce pro první buňku v prodejní ceně, tj. Pro produkt 1.

Když použijeme vzorec, získáme pro produkt 1 následující výsledek:

Úspěšně vynásobil jednotkové náklady jednotkami prodanými pro produkt 1, tj. Buňku G2 * buňku H2, tj. 1826, 00 * 20, což nám dává výsledek 36520, 00.

Takže teď vidíme, že máme dalších 9 produktů. Ve skutečných případech by to mohlo jít až na stovky nebo tisíce nebo řádky. Je obtížné a téměř nemožné jednoduše napsat vzorec pro každý řádek.

Proto použijeme funkci Relativní reference v Excelu a jednoduše zkopírujeme obsah buňky I2 a vložíme do všech zbývajících buněk v tabulce pro sloupec Prodejní cena nebo jednoduše přetáhneme vzorec z buňky I2 do zbývajících řádků v v tomto sloupci a získejte výsledky pro celou tabulku za méně než 5 sekund.

Zde stiskneme Ctrl + D. Výstup bude vypadat takto:

# 2- Absolutní reference buněk v Excelu

Většina naší každodenní práce v Excelu zahrnuje zpracování vzorců. Proto je velmi důležité mít pracovní znalosti Relativních, Absolutních nebo Smíšených buněčných odkazů v Excelu.

Uvidíme následující:

= A1 je relativní reference, kde se při kopírování buňky vzorce změní řádek i sloupec.

= $ A $ 1 je absolutní odkaz na buňku, sloupec i řádek jsou uzamčeny a při kopírování buňky vzorce se nemění. Hodnota buňky tak zůstává konstantní.

V = $ A1 je sloupec uzamčen a řádek se může pro tento konkrétní sloupec neustále měnit.

V = A $ 1 je řádek uzamčen a sloupec se může pro tento konkrétní řádek neustále měnit.

Na rozdíl od relativní reference, která se může měnit, když se pohybuje do různých buněk, absolutní reference se nemění. Jediné, co se zde vyžaduje, je úplné uzamčení konkrétní buňky.

Při použití znaku dolaru ve vzorci znamená odkaz na buňku absolutní odkaz na buňku, protože znak dolaru blokuje buňku. Můžeme zamknout řádek nebo sloupec pomocí znaku dolaru. Pokud je „$“ před abecedou, pak se sloupec zamkne a pokud je „$“ před číslem, pak se řádek zamkne.

# 3- Smíšené odkazy na buňky v Excelu

Jak efektivně používat absolutní odkaz na buňku v Excelu také jak používat Mixed cell Reference v Excelu?

Chcete-li získat komplexní porozumění Absolutní a smíšené buněčné reference ve Excelu, podívejme se na následující příklad.

Máme údaje o prodeji pro 4 prodejní manažery v různých měsících, kdy k prodeji došlo několikrát za měsíc.

Naším cílem je vypočítat konsolidovaný souhrn prodejů všech 4 prodejních manažerů. K dosažení požadovaného výsledku použijeme vzorec SUMIFS.

Výsledkem bude:

Podívejme se na vzorec, abychom viděli, co se stalo.

  • V „sum_range“ máme $ C $ 2: $ C $ 17. Před abecedou i čísly je znak dolaru. Řádky i sloupce pro oblast buněk jsou tedy uzamčeny. Toto je absolutní odkaz na buňku.
  • Dále máme „kritéria_range1“. I zde máme absolutní odkaz na buňku.
  • Poté máme „kritéria1“ - $ F2. Zde vidíme, že při kopírování buňky vzorce bude uzamčen pouze sloupec, což znamená, že se změní pouze řádek, když zkopírujeme vzorec do jiné buňky (posun dolů). Toto je smíšený odkaz na buňku.
  • Dále máme „kritéria_range2“, což je také absolutní odkaz na buňku.
  • Posledním segmentem vzorce jsou „kritéria2“ - 1 $. Zde pozorujeme, že znak dolaru je přítomen před číslem a nikoli abecedou. Při kopírování buňky vzorce se tedy zamkne pouze řádek. Sloupec se může změnit, když zkopírujeme buňku vzorce do jiné buňky (pohyb doprava). Toto je smíšený odkaz na buňku.

Přetažení vzorce přes souhrnnou tabulku stisknutím kláves Ctrl + D nejprve a později Ctrl + R. Získáme následující výsledek:

Odkaz na smíšenou buňku odkazuje pouze na konkrétní řádek nebo sloupec, například = $ A2 nebo = A $ 2 . Pokud chceme vytvořit smíšený odkaz na buňku, můžeme stisknout klávesu F4 ve vzorci dvakrát až třikrát podle vašeho požadavku, tj. Odkazovat na řádek nebo sloupec. Opětovným stisknutím F4 způsobí, že se odkaz na buňku změní na relativní.

Co si pamatovat

  • Při kopírování vzorce Excel je obvykle žádoucí relativní odkazování. To je důvod, proč se jedná o výchozí chování aplikace Excel. Ale někdy může být cílem použít absolutní referenci, spíše než relativní Cell reference ve Excelu. Absolute Reference vytváří odkaz na buňku fixovaný na absolutní adresu buňky, díky čemuž zůstává při kopírování vzorce nezměněn.
  • S relativním odkazováním nejsou vyžadovány žádné znaky dolaru. Když zkopírujeme vzorec z jednoho místa na druhé, vzorec se odpovídajícím způsobem přizpůsobí. Pokud tedy do buňky B3 napíšeme = B1 + B2 a poté přetáhneme nebo zkopírujeme stejný vzorec do buňky C3, odkaz Relativní buňka automaticky upraví vzorec na = C1 + C2 .
  • S relativním odkazováním se uvedené buňky automaticky upraví ve vzorci podle vašeho pohybu, buď doprava, doleva, nahoru nebo dolů.
  • Při relativním odkazování, pokud bychom měli dát odkaz na buňku D10 a posunout jednu buňku dolů, změnila by se na D11, pokud místo toho posuneme jednu buňku nahoru, změní se na D9 . Pokud však posuneme jednu buňku doprava, změní se odkaz na E10 a místo toho, pokud posuneme jednu buňku doleva, reference se automaticky upraví na C10.
  • Jedním stisknutím F4 změníte relativní odkaz na buňku na absolutní odkaz na buňku.
  • Dvojím stisknutím F4 změníte odkaz na buňku na smíšený odkaz, kde je řádek uzamčen.
  • Stisknutí F4 třikrát změní odkaz na buňku na smíšený odkaz, kde je sloupec uzamčen.
  • Stisknutí F4 počtvrté změní odkaz na buňku zpět na relativní odkaz ve Excelu.

Doporučené články

Toto byl průvodce Cell Reference v Excelu. Zde diskutujeme tři typy odkazů na buňky v Excelu, tj. Absolutní, relativní a smíšené odkazy na buňky a jak je používat každý z nich spolu s praktickými příklady a šablonou Excel ke stažení. Můžete si také prohlédnout naše další doporučené články -

  1. Porozumění relativní reference v Excelu
  2. Absolutní reference Excelu Snadná výuková aplikace Excel
  3. Jak používat funkci SUMIF v Excelu
  4. Použití funkce SUM s příklady

Kategorie:

Tipy pro Excel