Odkaz na bunku Excel (obsah)
- Odkaz na bunku v Exceli
- Typy odkazov na bunky v Exceli
- # 1 - Relatívna referencia buniek v Exceli
- # 2- Absolútna referencia bunky v Exceli
- # 3- Zmiešané referenčné bunky v Exceli
Odkaz na bunku v Exceli
Ak by ste chceli spomenúť, že jednou z funkcií programu Excel, ktorá skutočne robí túto magickú skúsenosť pri práci so vzorcami, pravdepodobne by to bola skutočnosť, že tvrdé kódovanie hodnôt do vzorcov nie je potrebné (v skutočnosti sa to dokonca ani neodporúča).
V Exceli sa tabuľky skladajú zo stĺpcov a riadkov, ktoré tvoria „bunky“. Každá bunka zodpovedá presnému stĺpcu a presnému riadku. Na vysvetlenie tohto príkladu sa bunka B2 týka druhého stĺpca (B) a druhého riadku (2). D16 preto ukazuje na štvrtý stĺpec (D) a šestnásty riadok (16). Skutočný okraj programu Excel je v použiteľnosti odkazov na bunky v iných bunkách pri vytváraní vzorcov.
Typy odkazov na bunky v Exceli
V Exceli máme tri rôzne typy bunkových referencií -
- Relatívna referencia bunky v Exceli
- Absolútna bunková referencia v Exceli
- Zmiešaná bunková referencia v Exceli
Použitie správneho typu referencie bunky v exceli v konkrétnom scenári ušetrí veľa času a úsilia a prácu značne uľahčí.
# 1 - Relatívna referencia buniek v Exceli
Relatívne odkazy na bunky v exceli označujú bunku alebo rad excelentných buniek. Zakaždým, keď sa do vzorca, napríklad SUMIFS, vloží hodnota, je možné do Excelu vložiť „odkaz na bunku“ ako náhradu za pevne kódované číslo. Odkaz na bunku môže mať formu B2, kde B zodpovedá stĺpci písmena príslušnej bunky a 2 predstavuje číslo riadku. Vždy, keď program Excel narazí na odkaz na bunku, navštívi konkrétnu bunku, extrahuje jej hodnotu a použije túto hodnotu v ktoromkoľvek vzorci, ktorý píšete. Keď je táto bunková referencia v Exceli duplikovaná na iné miesto, relatívna bunková referencia v Exceli sa tiež zodpovedajúcim spôsobom zmení.
Keď odkazujeme na bunky, ako je tento, môžeme to dosiahnuť pomocou ktoréhokoľvek z dvoch typov referenčných buniek vo vynikajúcich: absolútnych a relatívnych. Vymedzenie medzi týmito dvoma odlišnými referenčnými typmi je odlišné prirodzené správanie pri ich presúvaní alebo kopírovaní a prilepení do rôznych buniek. Relatívne odkazy na bunky sa môžu meniť a upravovať pri ich kopírovaní a vkladaní; absolútne odkazy naopak nie. Na úspešné dosiahnutie výsledkov v programe Excel je dôležité, aby ste mohli správne a relatívne a absolútne používať odkazy na bunky.
Ako efektívne používať relatívnu referenciu buniek v Exceli?
Aby sme mohli komplexne porozumieť všestrannosti a použiteľnosti tejto úžasnej vlastnosti programu Excel, musíme sa pozrieť na niekoľko praktických príkladov, aby sme pochopili jeho skutočnú hodnotu.
Túto šablónu referencie bunky Cell Excel si môžete stiahnuť tu - šablónu referencie bunky Cell ExcelPríklad č. 1
Pozrime sa na jednoduchý príklad na vysvetlenie mechanizmu relatívnej referencie buniek v Exceli. Ak chceme mať súčet dvoch čísiel v dvoch rôznych bunkách - A1 a A2 a výsledok v tretej bunke A3.
Použijeme teda vzorec = A1 + A2
Výsledkom by bol výsledok 100 v A3.
Teraz predpokladajme, že podobný scenár máme aj v nasledujúcom stĺpci (B). Bunka B1 a B2 má dve čísla a my chceme mať súčet v B3.
Môžeme to dosiahnuť dvoma rôznymi spôsobmi:
Tu fyzicky napíšeme vzorec na pridanie dvoch buniek B1 a B2 do B3.
Výsledok ako 30.
Alebo by sme mohli jednoducho skopírovať vzorec z bunky A3 a vložiť do bunky B3 (fungovalo by to, keby sme vzorec pretiahli aj z A3 na B3).
Keď teda skopírujeme obsah bunky A3 a vložíme do B3 alebo pretiahneme obsah bunky A3 a vložíme do B3, vzorec sa skopíruje, nie výsledok. Rovnakého výsledku by sme mohli dosiahnuť kliknutím pravým tlačidlom myši na bunku A3 a použitím možnosti Kopírovať.
A potom sa presunieme na nasledujúcu bunku B3 a klikneme pravým tlačidlom myši a vyberieme „Vzorce (f)“.
To znamená, že bunka A3 = A1 + A2. Keď skopírujeme A3 a presunieme jednu bunku doprava a vložíme ju do bunky B3, vzorec sa automaticky prispôsobí a zmení sa na B3 = B1 + B2. Namiesto toho použije sumačný vzorec pre bunky B1 a B2.
Príklad č. 2
Teraz sa pozrime na ďalší praktický scenár, vďaka ktorému by bol tento koncept celkom jasne jasný. Predpokladajme, že máme súbor údajov, ktorý pozostáva z jednotkovej ceny produktu a množstva predaného za každý z nich. Našim cieľom je teraz vypočítať predajnú cenu, ktorú možno opísať nasledujúcim vzorcom:
Predajná cena = Jednotková cena x Predané jednotky
Aby sme mohli nájsť predajnú cenu, musíme teraz pre každý produkt vynásobiť jednotkovú cenu jednotkami predanými. Takže teraz začneme uplatňovať tento vzorec pre prvú bunku v predajnej cene, tj pre produkt 1.
Keď použijeme vzorec, pre produkt 1 získame nasledujúci výsledok:
Úspešne vynásobila jednotkové náklady jednotkami predanými pre produkt 1, tj bunka G2 * bunka H2, tj 1826, 00 * 20, čo nám dáva výsledok 36520, 00.
Takže teraz vidíme, že máme ešte 9 ďalších produktov. V skutočných prípadoch by to mohlo ísť až na stovky alebo tisíce alebo riadky. Je ťažké a takmer nemožné jednoducho písať vzorec pre každý riadok.
Preto použijeme funkciu relatívnej referencie v programe Excel a jednoducho skopírujeme obsah bunky I2 a vložíme do všetkých zvyšných buniek v tabuľke stĺpec Predajná cena alebo jednoducho pretiahneme vzorec z bunky I2 do ostatných riadkov v v tomto stĺpci a získajte výsledky za celú tabuľku za menej ako 5 sekúnd.
Tu stlačíme Ctrl + D. Výstup bude vyzerať takto:
# 2- Absolútna referencia bunky v Exceli
Väčšina našej každodennej práce v Exceli zahŕňa spracovanie vzorcov. Preto je veľmi dôležité mať pracovné znalosti o relatívnych, absolútnych alebo zmiešaných bunkových referenciách.
Pozrime sa na nasledujúce:
= A1 je relatívna referencia, kde sa pri kopírovaní bunky vzorca zmení riadok aj stĺpec.
= $ A $ 1 je absolútny odkaz na bunku, stĺpec aj riadok sú zamknuté a pri kopírovaní bunky vzorca sa nemenia. Hodnota bunky teda zostáva konštantná.
V = $ A1 je stĺpec zamknutý a riadok sa môže v tomto konkrétnom stĺpci neustále meniť.
V = A $ 1 je riadok zamknutý a stĺpec sa môže naďalej meniť pre tento konkrétny riadok.
Na rozdiel od relatívnej referencie, ktorá sa môže meniť pri pohybe do rôznych buniek, absolútna referencia sa nemení. Jediné, čo sa tu vyžaduje, je úplné zamknutie konkrétnej bunky.
Pri použití znaku dolára vo vzorci znamená to, že odkaz na bunku robí absolútnu referenciu na bunku, pretože znak dolára blokuje bunku. Riadok alebo stĺpec môžeme uzamknúť pomocou znaku dolára. Ak je „$“ pred abecedou, uzamkne stĺpec a ak je „$“ pred číslom, riadok sa uzamkne.
# 3- Zmiešané referenčné bunky v Exceli
Ako efektívne používať odkaz na absolútnu bunku v Exceli aj ako používať Excel v Exceli?
Ak chcete získať komplexné porozumenie Absolute a Mixed Cell Reference vo vynikajúcej podobe, pozrime sa na nasledujúci príklad.
Máme údaje o predaji pre 4 manažérov predaja v rôznych mesiacoch, keď sa predaj uskutočnil viackrát za mesiac.
Naším cieľom je vypočítať konsolidované zhrnutie predaja všetkých 4 obchodných manažérov. Na dosiahnutie požadovaného výsledku použijeme vzorec SUMIFS.
Výsledkom bude:
Pozrime sa na vzorec, aby sme videli, čo sa stalo.
- V „sum_range“ máme $ C $ 2: $ C $ 17. Pred abecedami a číslami je znak dolára. Preto sú riadky aj stĺpce pre rozsah buniek zamknuté. Toto je absolútna referencia bunky.
- Ďalej máme „kritéria_range1“. Aj tu máme absolútny odkaz na bunky.
- Potom máme „kritérium1“ - $ F2. Tu vidíme, že pri kopírovaní bunky vzorcov sa zamkne iba stĺpec, čo znamená, že keď sa skopírujeme vzorec do inej bunky (posun nadol), zmení sa iba riadok. Toto je odkaz na zmiešané bunky.
- Ďalej máme „kritéria_range2“, čo je tiež absolútna referencia bunky.
- Posledný segment vzorca je „kritériá 2“ - 1 $. Tu pozorujeme, že znak dolára sa nachádza pred číslom a nie podľa abecedy. Pri kopírovaní bunky vzorca sa teda zamkne iba riadok. Stĺpec sa môže zmeniť, keď skopírujeme bunku vzorca do inej bunky (pohyb doprava). Toto je odkaz na zmiešané bunky.
Presúvanie vzorca cez súhrnnú tabuľku stlačením klávesov Ctrl + D a neskôr klávesov Ctrl + R. Získame nasledujúci výsledok:
Zmiešaná bunková referencia sa vzťahuje iba na konkrétny riadok alebo stĺpec, napríklad = $ A2 alebo = A $ 2 . Ak chceme vytvoriť zmiešaný odkaz na bunku, môžeme stlačiť kláves F4 vo vzorci dvakrát až trikrát, podľa vašej požiadavky, tj odkazovať na riadok alebo stĺpec. Opätovným stlačením klávesu F4 sa referencia bunky zmení na relatívnu.
Čo je potrebné pamätať
- Pri kopírovaní vzorcov programu Excel je vo všeobecnosti to, čo sa vyžaduje. To je dôvod, prečo je to predvolené správanie programu Excel. Cieľom však môže byť niekedy použiť absolútny odkaz, skôr ako relatívny odkaz na bunky vo vynikajúcich hodnotách. Absolútna referencia robí bunkovú referenciu fixovanú na absolútnu adresu bunky, vďaka ktorej zostáva pri kopírovaní vzorca nezmenená.
- Pri relatívnom odkazovaní sa nevyžadujú žiadne znaky dolára. Keď skopírujeme vzorec z jedného miesta na druhé, vzorec sa podľa toho prispôsobí. Ak teda do bunky B3 napíšeme = B1 + B2 a potom do bunky C3 pretiahneme alebo skopírujeme rovnaký vzorec, referencia relatívnej bunky automaticky upraví vzorec na = C1 + C2 .
- Pri relatívnom odkazovaní sa uvedené bunky automaticky upravia vo vzorci podľa vášho pohybu, buď doprava, doľava, nahor alebo nadol.
- Pri relatívnom odkazovaní, ak by sme mali dať odkaz na bunku D10 a potom posunúť jednu bunku smerom nadol, zmenilo by sa to na D11, ak namiesto toho posuneme jednu bunku smerom nahor, zmení sa na D9 . Ak by sme však posunuli jednu bunku doprava, referencia by sa zmenila na E10 a namiesto toho, ak posuneme jednu bunku doľava, referencia by sa automaticky prispôsobila na C10.
- Jedným stlačením klávesu F4 sa zmení vynikajúca referencia bunky na absolútnu referenciu bunky.
- Dvojnásobným stlačením klávesu F4 zmeníte odkaz na bunku na zmiešaný odkaz, kde je riadok uzamknutý.
- Stlačením F4 trikrát zmeníte odkaz na bunku na zmiešaný odkaz, kde je stĺpec zamknutý.
- Štvrtým stlačením klávesu F4 zmeníte referenciu bunky späť na relatívnu referenciu v Exceli.
Odporúčané články
Toto bol sprievodca Cell Reference v Exceli. Tu diskutujeme tri typy odkazov na bunky v Exceli, tj absolútny, relatívny a zmiešaný odkaz na bunky a ako ich používať spolu s praktickými príkladmi a šablónou Excel na stiahnutie. Môžete si tiež prečítať naše ďalšie navrhované články -
- Pochopenie relatívnej referencie v Exceli
- Absolútny odkaz Excel Jednoduchá výučba programu Excel
- Ako používať funkciu SUMIF v Exceli
- Použitie funkcie SUM s príkladmi