Absolútne a relatívne odkazy: ako zafixovať bunku vo vzorci
Keď vzorec skopíruješ, odkazy v ňom sa posunú. Znak $ niektoré z nich zafixuje na mieste. Naučíš sa, kedy ho písať a kam, aby kopírovaný vzorec počítal správne.
Vyskúšaj si to: Odmena podľa sadzby
Do bunky C4 napíš =B4*$B$1 a stlač Enter. Vzorec sa skopíruje do C5 a C6. Vďaka znakom $ bude každý riadok násobiť tou istou sadzbou v B1.
| A | B | C | |
|---|---|---|---|
| 1 | Sadzba (€/h) | 15 | |
| 2 | |||
| 3 | Brigádnik | Hodiny | Odmena (€) |
| 4 | Lukáš | 12 | |
| 5 | Simona | 20 | |
| 6 | Erik | 8 |
Klikni na bunku a jej adresa sa vloží do vzorca. Bunky s prerušovaným okrajom ukážu, čo vzorec vypočíta po skopírovaní.
Riešenie: =B4*$B$1
Po skopírovaní do C5 vznikne =B5*$B$1 a v C6 =B6*$B$1. Odkaz B4 sa posunul, $B$1 zostal na mieste.
Relatívny odkaz sa pri kopírovaní posúva
Obyčajný odkaz, napríklad B2, je relatívny. Vo vzorci v D2 si ho Excel pamätá ako „bunka o dva
stĺpce naľavo v tom istom riadku“. Keď vzorec =B2-C2 z D2 skopíruješ do D3, zmení sa na
=B3-C3. Vďaka tomu stačí napísať vzorec raz a ťahať ho nadol cez celú tabuľku.
Vzorec skopíruješ tak, že chytíš malý štvorček v pravom dolnom rohu bunky a potiahneš ho nadol. Dvojklik na štvorček vzorec doplní až po koniec údajov v susednom stĺpci.
Absolútny odkaz zostane na mieste
Niekedy majú všetky riadky počítať s tou istou bunkou: s kurzom, sadzbou, percentom alebo celkovým
súčtom. Vtedy pred písmeno aj číslo napíš znak $: $B$7. Takýto odkaz je absolútny
(hovorí sa aj ukotvený, prípadne zafixovaný alebo zamknutý) a pri kopírovaní sa nezmení.
=B2*(1-$B$5) | A | B | C | |
|---|---|---|---|
| 1 | Tovar | Bez zľavy (€) | So zľavou (€) |
| 2 | Taška | 40 | 34,00 |
| 3 | Opasok | 25 | |
| 4 | |||
| 5 | Zľava | 15% |
Po skopírovaní do C3 vznikne =B3*(1-$B$5). Bez dolárov by z B5 bolo B6,
prázdna bunka, a opasok by bol bez zľavy.
Zmiešaný odkaz a kláves F4
$B$5: ukotvený stĺpec aj riadok. Nemení sa nikdy.B$5: ukotvený riadok. Pri kopírovaní nadol ostane riadok 5, doprava sa mení stĺpec.$B5: ukotvený stĺpec. Pri kopírovaní doprava ostane stĺpec B, nadol sa mení riadok.B5: nič nie je ukotvené.
Doláre netreba písať ručne. Pri písaní vzorca daj kurzor na odkaz a stlač F4. Každé stlačenie prepne na ďalší typ v poradí $B$5, B$5, $B5, B5. Na niektorých notebookoch treba stlačiť Fn+F4.
Pomôcka: dolár ukotvuje to, čo nasleduje hneď za ním. Pri kopírovaní len nadol stačí ukotviť riadok, pri kopírovaní len doprava stĺpec. V prípade pochybností použi $B$5.
Časté chyby a čo znamenajú
V prvom riadku je výsledok správny, nižšie nie
Chýba $ pri bunke, ktorá sa nemá posúvať. Dvakrát klikni na bunku nižšie (alebo ju označ a stlač F2) a pozri sa, kam vzorec ukazuje. Excel odkazy vo vzorci farebne orámuje aj v tabuľke.
Chyba #DIV/0! v skopírovaných riadkoch
Delíš bunkou, ktorá sa pri kopírovaní posunula na prázdnu. Najčastejšie ide o celkový súčet v podiele:
=B2/B8 treba zapísať ako =B2/$B$8.
Všetky riadky majú rovnaký výsledok
Dolárov je priveľa. Ukotvený je aj odkaz, ktorý sa mal posúvať, napríklad $B$2 namiesto B2.
Ukotvený je riadok, ale kopíruje sa doprava
B$5 pri kopírovaní doprava prejde na C$5. Keď sa vzorec kopíruje doprava, ukotvi
stĺpec alebo celý odkaz.
Cvičenia
Päť úloh od ľahkých po náročnejšie. Vzorec napíšeš do prvej bunky a kontrola ho sama skopíruje do ostatných. Uvidíš tak, či počíta správne v každom riadku.
Cvičenie 1: Zisk po mesiacoch
ľahkáDo D2 napíš vzorec pre zisk: tržba mínus náklady. Vzorec sa skopíruje do D3 až D6 a v každom riadku má počítať s údajmi z toho riadku.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Mesiac | Tržba (€) | Náklady (€) | Zisk (€) |
| 2 | Január | 12400 | 9800 | |
| 3 | Február | 11800 | 9650 | |
| 4 | Marec | 13900 | 10200 | |
| 5 | Apríl | 14250 | 10900 | |
| 6 | Máj | 15100 | 11300 |
Klikni na bunku a jej adresa sa vloží do vzorca. Bunky s prerušovaným okrajom ukážu, čo vzorec vypočíta po skopírovaní.
Riešenie: =B2-C2
B2 a C2 sú relatívne odkazy. Po skopírovaní do D3 sa z nich stane B3 a C3, v D6 B6 a C6. Presne to tu chceš.
Cvičenie 2: Prepočet na koruny
ľahkáHotel v Tatrách ukazuje ceny aj českým hosťom. Do C2 napíš vzorec, ktorý prepočíta cenu v eurách kurzom z B7. Vzorec sa skopíruje do C3 až C5.
| A | B | C | |
|---|---|---|---|
| 1 | Izba | Cena (€) | Cena (Kč) |
| 2 | Jednolôžková | 68 | |
| 3 | Dvojlôžková | 96 | |
| 4 | Apartmán | 145 | |
| 5 | Prístelka | 25 | |
| 6 | |||
| 7 | Kurz (Kč za 1 €) | 25,2 |
Klikni na bunku a jej adresa sa vloží do vzorca. Bunky s prerušovaným okrajom ukážu, čo vzorec vypočíta po skopírovaní.
Riešenie: =B2*$B$7
V =B2*$B$7 sa pri kopírovaní mení len B2. Odkaz na kurz $B$7 ostane rovnaký. Pri kopírovaní nadol by stačilo aj B$7.
Cvičenie 3: Provízia po mesiacoch
strednáTržby sú v riadku 2, každý mesiac v inom stĺpci. Do B3 napíš vzorec pre províziu: tržba krát percento z B5. Vzorec sa skopíruje doprava do C3 až E3.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Mesiac | Jan | Feb | Mar | Apr |
| 2 | Tržba | 4200 | 3850 | 5100 | 4600 |
| 3 | Provízia | ||||
| 4 | |||||
| 5 | Sadzba | 8% |
Klikni na bunku a jej adresa sa vloží do vzorca. Bunky s prerušovaným okrajom ukážu, čo vzorec vypočíta po skopírovaní.
Riešenie: =B2*$B$5
Po skopírovaní do C3 vznikne =C2*$B$5. Pri kopírovaní doprava stačí ukotviť stĺpec, takže prejde aj =B2*$B5. B$5 by nestačilo.
Cvičenie 4: Podiel hlasov
strednáVo voľbách do obecného zastupiteľstva je v B8 počet všetkých platných hlasov. Do C2 napíš vzorec, ktorý vypočíta podiel hlasov kandidáta. Vzorec sa skopíruje do C3 až C6.
| A | B | C | |
|---|---|---|---|
| 1 | Kandidát | Hlasy | Podiel |
| 2 | Bartoš | 412 | |
| 3 | Danková | 388 | |
| 4 | Fabian | 297 | |
| 5 | Hricová | 251 | |
| 6 | Kollár | 164 | |
| 7 | |||
| 8 | Platných hlasov | 1890 |
Klikni na bunku a jej adresa sa vloží do vzorca. Bunky s prerušovaným okrajom ukážu, čo vzorec vypočíta po skopírovaní.
Riešenie: =B2/$B$8
Bartoš: 412 / 1 890 = 0,218, teda 21,8 %. Vďaka $B$8 delí každý riadok tým istým počtom platných hlasov.
Cvičenie 5: Cenník pre skupiny
náročnejšiaKúpalisko chce tabuľku, koľko zaplatí skupina. V stĺpci B je cena lístka, v riadku 1 počet osôb. Do C2 napíš jeden vzorec, ktorý sa skopíruje do celej tabuľky C2 až E4.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Lístok | Cena | 2 | 4 | 6 |
| 2 | Dospelý | 9,00 | |||
| 3 | Dieťa | 5,00 | |||
| 4 | Senior | 6,50 |
Klikni na bunku a jej adresa sa vloží do vzorca. Bunky s prerušovaným okrajom ukážu, čo vzorec vypočíta po skopírovaní.
Riešenie: =$B2*C$1
V E4 vznikne =$B4*E$1: cena seniora krát 6 osôb. $B2 drží stĺpec B a riadok sa mení, C$1 drží riadok 1 a stĺpec sa mení. Takémuto odkazu sa hovorí zmiešaný.
Otázky a odpovede
Ako zafixovať bunku vo vzorci v Exceli?
Napíš pred písmeno stĺpca aj pred číslo riadku znak $, napríklad $B$7, alebo pri písaní
vzorca stlač F4. Takto zafixovaná bunka sa pri kopírovaní vzorca neposunie.
Čo znamená dolár vo vzorci?
Dolár ukotvuje časť odkazu, ktorá nasleduje za ním. $B ukotvuje stĺpec, $7 riadok. S menou ani s formátom čísla nemá nič spoločné.
Ako ukotviť riadok, aby ho bolo stále vidno?
To je iná vec ako odkaz vo vzorci. Na karte Zobraziť použi Ukotviť priečky. Hlavička tabuľky potom zostane na obrazovke, aj keď posúvaš nadol.
Ako kopírovať vzorec bez zmeny odkazov?
Odkazy, ktoré sa nemajú meniť, ukotvi znakmi $. Keď chceš preniesť jeden vzorec presne tak, ako je, skopíruj ho z riadka vzorcov ako text a prilep do inej bunky.