Aby bylo možné provádět přesné výpočty v aplikaci Excel, je důležité pochopit, jak různé typy odkazů na buňky fungují.
A1 vs. R1C1 Reference
Pracovní listy aplikace Excel obsahují mnoho buněk a (ve výchozím nastavení) je každá buňka označena svou buňkou sloupcové písmeno následuje číslo řádku. Toto je známé jako odkazování ve stylu A1. Příklady: A1, B4, C6
Referenční styl A1
Volitelně můžete přepnout na Referenční režim R1C1 odkazovat na řádek buňky & číslo sloupce. Namísto odkazu na buňku A1 byste odkazovali na R1C1 (řádek 1, sloupec 1). Buňka C4 by byla označena jako R4C3.
R1C1 referenční styl
Odkazování ve stylu R1C1 je v Excelu velmi neobvyklé. Pokud nemáte dobrý důvod, měli byste se pravděpodobně držet výchozího referenčního režimu ve stylu A1. Pokud však používáte VBA, pravděpodobně se s tímto referenčním stylem setkáte.
Přepnout na referenční styl R1C1
Chcete -li přepnout referenční styl, přejděte na Soubor> Možnost> Vzorec. Zaškrtněte políčko vedle R1C1 referenční styl.
Pojmenované rozsahy
Jednou z nejméně využívaných funkcí Excelu je funkce Pojmenované rozsahy. Namísto odkazování na buňku (nebo skupinu buněk) podle umístění buňky (např. B3 nebo R3C2) můžete tento rozsah pojmenovat a jednoduše odkazovat na název rozsahu ve vzorcích.
Pojmenování rozsahu:
- Vyberte buňku nebo buňky, které chcete pojmenovat
- Klikněte do pole Název rozsahu
- Zadejte požadované jméno
- Stiskněte Enter
Nyní můžete odkazovat na buňku A1 zadáním = range_name1 místo. To je velmi užitečné při práci s velkými sešity s více listy.
Rozsah buněk
Při používání funkcí integrovaných v Excelu může být nutné odkazovat na rozsah buněk. Rozsahy buněk vypadají takto „A1: B3“. Tento odkaz se vztahuje na všechny buňky mezi A1 a B3: buňky A1, A2, A3, B1, B2, B3.
Jak vybrat rozsah buněk při zadávání vzorce:
- Zadejte rozsah (počáteční a koncový rozsah oddělte středníkem)
- Pomocí myši klikněte na první odkaz na buňku, podržte tlačítko myši a přetáhněte požadovaný rozsah.
- Podržte klávesu Shift a pomocí kláves se šipkami vyberte rozsah
Absolutní (zmrazené) a relativní reference
Při zadávání odkazů na buňky ve vzorcích můžete použít relativní nebo absolutní (zmrazené) odkazy. Když zkopírujete vzorec do nové buňky, relativní odkazy se budou pohybovat proporcionálně. Absolutní reference zůstanou beze změny. Podívejme se na několik příkladů:
Relativní reference
Relativní reference v Excelu vypadá takto
= A1
Když zkopírujete a vložíte vzorec s relativními odkazy, relativní odkazy se budou pohybovat proporcionálně. Pokud neurčíte jinak, budou vaše odkazy na buňky ve výchozím nastavení relativní (nezmrazené).
Příklad: Pokud zkopírujete „= A1“ do jednoho řádku, odkaz se změní na „= A2“.
Absolutní (zmrazené) odkazy na buňky
Pokud nechcete, aby se vaše odkazy na buňky při kopírování vzorce přesunuly, můžete „zmrazit“ odkazy na buňky přidáním znaků dolaru ($ s) před odkaz, který chcete zmrazit. Když nyní zkopírujete a vložíte vzorec, odkaz na buňku zůstane beze změny. Můžete se rozhodnout zmrazit odkaz na řádek, odkaz na sloupec nebo obojí.
A1: Nic není zmrzlé
$ A1: Sloupec je zmrazený, ale řádek není zmrazený
A $ 1: Řádek je zmrazený, ale sloupec není zmrazený
$ A $ 1: Řádek i sloupec jsou zmrazeny
Absolutní referenční zkratka
Ručně přidávat do vzorců znaky dolaru ($ s) není příliš praktické. Místo toho při vytváření vzorce přepínejte mezi odkazy na absolutní/relativní buňky pomocí klávesy F4.
Referenční příklad absolutní buňky
Kdy byste vlastně potřebovali zmrazit odkaz na buňku? Jedním běžným příkladem je, když máte vstupní buňky, na které se často odkazuje. V níže uvedeném příkladu chceme vypočítat daň z obratu pro každé množství položek nabídky. Daň z prodeje je u všech položek konstantní, takže na buňku daně z prodeje budeme odkazovat opakovaně.
Chcete -li zjistit celkovou daň z obratu, zadejte do sloupce D vzorec „= (B3*C3)*$ C $ 1“ a zkopírujte vzorec dolů.
Smíšená reference
Možná jste slyšeli o smíšených buňkách. Smíšený odkaz je, když je uzamčen odkaz na řádek nebo sloupec (ale ne obojí).
Smíšená reference
Pamatujte, že pomocí klávesy „F4“ můžete procházet vaše relativní, absolutní odkazy na buňky.
Odkazy na buňky - vkládání a mazání řádků/sloupců
Možná si říkáte, co se stane s vašimi odkazy na buňky při vkládání nebo mazání řádků/sloupců?
Odkaz na buňku se automaticky aktualizuje tak, aby odkazoval na původní buňku. To je případ bez ohledu na to, zda je odkaz na buňku zmrazený.
3D reference
Někdy může být nutné pracovat s několika listy se stejnými vzory dat. Excel vám umožňuje odkazovat na více listů najednou, aniž byste museli ručně zadávat každý list. Na řadu listů můžete odkazovat podobně jako na oblast buněk. Příklad „List1: Sheet5! A1“ by odkazoval na buňky A1 na všech listech od Listu1 až po List5.
Pojďme se podívat na příklad:
Chcete sečíst celkový počet prodaných jednotek pro každý produkt ve všech obchodech. Každý obchod má svůj vlastní pracovní list a všechny pracovní listy mají stejný formát. Můžete vytvořit vzorec podobný tomuto:
To není příliš obtížné pouze se čtyřmi pracovními listy, ale co kdybyste měli 40 pracovních listů? Opravdu byste chtěli ručně přidat odkaz na každou buňku?
Místo toho můžete pomocí 3D odkazu snadno odkazovat na více listů najednou (podobně jako můžete odkazovat na řadu buněk).
Buď opatrný! Na pořadí vašich pracovních listů záleží. Pokud přesunete další list mezi odkazované listy (StoreA a StoreD), bude tento list zahrnut. Naopak pokud přesunete list mimo rozsah listů (před StoreA nebo po StoreD), nebude již zahrnut.
Referenční kruhová buňka
Odkaz na kruhovou buňku je, když buňka odkazuje zpět na sebe. Pokud je například výsledek buňky B1 použit jako vstup pro buňku B1, vytvoří se kruhový odkaz. Buňka nemusí na sebe přímo odkazovat. Mohou existovat mezikroky.
Příklad:
V tomto případě je vzorec pro buňku B2 „A2+A3+B2“. Protože jste v buňce B2, nemůžete v rovnici použít B2. Tím by se spustila kruhová reference a hodnota v buňce „B2“ se automaticky nastaví na „0“.
Kruhové odkazy jsou obvykle výsledkem chyby uživatele, ale za určitých okolností můžete použít kruhový odkaz. Primárním příkladem použití kruhové reference je výpočet hodnot iterativně. Chcete -li to provést, musíte jít na Soubor> Možnosti> Vzorce a Povolený iterativní výpočet:
Externí reference
Při výpočtu dat budete někdy muset odkazovat na data mimo váš sešit. Toto se nazývá externí reference (odkaz).
Chcete -li při vytváření vzorce vybrat externí odkaz, přejděte do externího sešitu a vyberte svůj odkaz jako obvykle.
Chcete -li přejít do druhého sešitu, můžete použít zkratku CTRL + TAB nebo přejít na Zobrazit> Přepnout Windows.
Jakmile vyberete buňku, uvidíte externí odkaz, který vypadá takto:
Všimněte si, že název sešitu je uzavřen závorkami [].
Jakmile zavřete odkazovaný sešit, v odkazu se zobrazí umístění souboru:
Když znovu otevřete sešit obsahující externí odkaz, budete vyzváni k povolení automatické aktualizace odkazů. Pokud tak učiníte, Excel otevře referenční hodnotu s aktuální hodnotou sešitu. I když je zavřeno! Buď opatrný! To můžete, ale také nemusíte chtít.
Pojmenované rozsahy a externí reference
Co se stane s vaším odkazem na externí buňku, když jsou řádky / sloupce přidány nebo odstraněny z referenčního sešitu? Pokud jsou oba sešity otevřené, odkazy na buňky se automaticky aktualizují. Pokud však oba sešity nejsou otevřené, odkazy na buňky se neaktualizují a již nebudou platné. Při propojování s externími sešity je to obrovská starost. Je tím způsobeno mnoho chyb.
Pokud odkazujete na externí sešity, měli byste odkaz na buňku pojmenovat pojmenovaným rozsahem (další informace viz předchozí část). Nyní bude váš vzorec odkazovat na pojmenovaný rozsah bez ohledu na to, jaké změny nastanou v externím sešitu.
