Čo by ste mali vedieť
- Ak chcete vypočítať viacero položiek na základe buniek inde a udržať riadok alebo stĺpec konštantný, použite absolútny odkaz na bunku.
- V tejto rovnici je absolútny odkaz na bunku A$12:=+B2+B2+A$12.
- Symbol $ „udržiava“riadok alebo stĺpec konštantný aj pri kopírovaní alebo vypĺňaní stĺpca alebo riadka rovnakým vzorcom.
Tento článok vysvetľuje, ako použiť absolútny odkaz na bunku v Exceli 2010 a novších verziách, aby ste zabránili automatickým úpravám vzorcov vzhľadom na to, koľko riadkov alebo stĺpcov ste vyplnili.
Čo je absolútna referencia bunky v Exceli?
Existujú dva typy spôsobov odkazovania na bunku vo vzorci v Exceli. Môžete použiť relatívny odkaz na bunku alebo absolútny odkaz na bunku.
- Relatívny odkaz na bunku: Adresa bunky, ktorá neobsahuje symbol $ pred súradnicami riadka alebo stĺpca. Tento odkaz automaticky aktualizuje stĺpec alebo bunku vzhľadom na pôvodnú bunku, keď vyplníte nadol alebo naprieč.
- Absolútny odkaz na bunku: Adresa bunky, ktorá obsahuje symbol $ buď pred súradnicami riadka alebo stĺpca. Toto "udrží" referenčný riadok alebo stĺpec konštantný, aj keď vyplníte stĺpec alebo riadok rovnakým vzorcom.
Odkazovanie na bunky sa môže zdať ako abstraktný pojem pre tých, ktorí sú v ňom noví.
Ako používať absolútnu referenciu buniek v Exceli
Existuje niekoľko rôznych spôsobov použitia absolútnej referencie v Exceli. Metóda, ktorú použijete, závisí od toho, ktorú časť referencie chcete zachovať konštantnú: stĺpec alebo riadok.
-
Vezmite si napríklad tabuľku, v ktorej chcete vypočítať skutočnú daň z obratu pre viacero položiek na základe referenčných buniek inde v hárku.
Image -
V prvej bunke by ste zadali vzorec s použitím štandardných relatívnych odkazov na bunky pre ceny položiek. Aby ste však do výpočtu zahrnuli správnu daň z obratu, použili by ste absolútnu referenciu pre riadok, ale nie pre bunku.
Image -
Tento vzorec odkazuje na dve bunky rôznymi spôsobmi. B2 je bunka naľavo od prvej bunky dane z obratu vzhľadom na pozíciu tejto bunky. Referenčné číslo A$12 odkazuje na skutočné číslo DPH uvedené v A12. Symbol $ udrží odkaz na riadok 12 konštantný bez ohľadu na to, ktorým smerom vyplníte susedné bunky. Vyplňte všetky bunky pod touto prvou, aby ste to videli v akcii.
Image -
Môžete vidieť, že keď vyplníte stĺpec, bunka v druhom stĺpci používa relatívne odkazovanie na bunky pre hodnoty nákladov v stĺpci B, čím sa stĺpec zvyšuje tak, aby zodpovedal stĺpcu dane z obratu. Odkaz na A12 však zostáva konštantný kvôli symbolu $, ktorý zachováva odkaz na riadok rovnaký. Teraz vyplňte stĺpec D počnúc bunkou C2.
Image -
Teraz môžete vidieť, že vyplnenie riadka vpravo používa správnu štátnu daň z obratu (Indiana), pretože odkaz na stĺpec je relatívny (posúva sa o jeden doprava rovnako ako bunka, ktorú vypĺňate). Relatívna referencia nákladov sa však teraz tiež posunula o jednu doprava, čo je nesprávne. Môžete to vyriešiť tak, že pôvodný vzorec v C2 bude odkazovať na stĺpec B s absolútnym odkazom.
Image -
Umiestnením symbolu $ pred "B" ste vytvorili absolútnu referenciu pre stĺpec. Keď teraz vyplníte údaje doprava, uvidíte, že odkaz na stĺpec B pre cenu zostáva rovnaký.
Image -
Teraz, keď vyplníte stĺpec D nadol, uvidíte, že všetky relatívne a absolútne odkazy na bunky fungujú presne tak, ako ste zamýšľali.
Image -
Absolútne odkazy na bunky v Exceli sú rozhodujúce pre kontrolu, na ktoré bunky sa odkazuje pri vypĺňaní stĺpcov alebo riadkov. V odkaze na bunku použite symbol $, ktorý potrebujete, aby ste udržali odkaz na riadok alebo stĺpec konštantný, aby ste mohli odkazovať na správne údaje v správnej bunke. Keď takto skombinujete relatívne a absolútne odkazy na bunky, nazýva sa to zmiešaný odkaz na bunku.
Ak používate zmiešaný odkaz, je dôležité zarovnať stĺpec alebo riadok zdrojových údajov so stĺpcom alebo riadkom, do ktorého zadávate vzorec. Ak ste odkaz na riadok označili za relatívny, majte na pamäti, že keď vyplníte na stranu, číslo stĺpca zdrojových údajov sa bude zvyšovať spolu so stĺpcom bunky vzorca. Toto je jedna z vecí, ktorá robí relatívne a absolútne adresovanie komplikovaným pre nových používateľov, ale keď sa to naučíte, budete mať oveľa väčšiu kontrolu nad sťahovaním zdrojových dátových buniek do vašich odkazov na bunky vzorcov.
Používanie absolútnych referencií buniek na pripnutie referencie jednej bunky
Ďalším prístupom k použitiu absolútneho odkazovania na bunky je jeho aplikovanie na stĺpec aj riadok, čím sa vzorec v podstate „pripne“tak, aby používal iba jednu jedinú bunku bez ohľadu na to, kde sa nachádza.
Pomocou tohto prístupu môžete vyplniť na stranu alebo nadol a odkaz na bunku zostane vždy rovnaký.
Používanie absolútneho odkazu na bunku v stĺpci aj riadku je účinné iba vtedy, ak odkazujete iba na jednu bunku vo všetkých vypĺňaných bunkách.
-
Pomocou rovnakého príkladu tabuľky vyššie môžete uviesť iba daňovú sadzbu jedného štátu pridaním symbolu $ do stĺpca aj odkazu na riadok.
Image -
To spôsobí, že stĺpec „A“aj riadok „12“zostanú konštantné bez ohľadu na to, akým smerom vyplníte bunky. Ak to chcete vidieť v akcii, vyplňte celý stĺpec pre daň z obratu ME po aktualizácii vzorca s absolútnym odkazom na stĺpec a riadok. Môžete vidieť, že každá vyplnená bunka vždy používa absolútnu referenciu $ A $ 12. Nezmení sa ani stĺpec ani riadok.
Image -
Použite rovnaký vzorec pre stĺpec dane z obratu v Indiane, ale tentoraz použite absolútnu referenciu pre stĺpec aj riadok. V tomto prípade je to $B$12.
Image -
Vyplňte tento stĺpec a znova uvidíte, že odkaz na B12 sa v žiadnej bunke nemení, vďaka absolútnemu odkazu pre stĺpec aj riadok.
Image -
Ako vidíte, existuje viacero spôsobov, ako môžete použiť absolútne odkazy na bunky v Exceli na vykonanie podobných úloh. Absolútne referencie vám poskytujú flexibilitu udržiavať konštantné referencie buniek, aj keď vypĺňate veľký počet stĺpcov alebo riadkov.
Môžete prechádzať medzi relatívnymi alebo absolútnymi odkazmi na bunky zvýraznením odkazu a následným stlačením F4 Zakaždým, keď stlačíte F4, absolútny odkaz sa použije buď na stĺpec, riadok, stĺpec aj bunku alebo žiadny z nich. Toto je jednoduchý spôsob, ako upraviť vzorec bez toho, aby ste museli zadávať symbol $.
Kedy by ste mali použiť absolútne bunkové referencie
V takmer každom odvetví a oblasti existuje veľa dôvodov, prečo by ste mohli chcieť použiť absolútne odkazy na bunky v Exceli.
- Používanie pevných násobiteľov (ako je cena za jednotku) vo veľkom zozname položiek.
- Pri projektovaní cieľov ročného zisku použite na každý rok jedno percento.
- Pri vytváraní faktúr použite absolútne referencie na označenie rovnakej sadzby dane pre všetky položky.
- Použite absolútne odkazy na bunky v riadení projektu na odkazovanie na pevné sadzby dostupnosti pre jednotlivé zdroje.
- Použite relatívne odkazy na stĺpce a absolútne odkazy na riadky na priradenie výpočtov stĺpcov v odkazovaných bunkách k hodnotám stĺpcov v inej tabuľke.
Ak pre stĺpce alebo riadky používate kombináciu relatívnych a absolútnych odkazov, musíte sa uistiť, že pozícia stĺpcov alebo riadkov zdrojových údajov sa zhoduje so stĺpcom alebo riadkom cieľových buniek (kde znova napíš vzorec).
