Naučte sa kopírovať vzorec do viacerých buniek, nadol v stĺpci, do nesusediacich buniek, kopírovať vzorce s absolútnymi alebo zmiešanými odkazmi na bunky atď.
Kopírovanie vzorcov je jednou z najbežnejších a najjednoduchších úloh, ktoré vykonávate v typickom tabuľkovom procesore, ktorý sa spolieha hlavne na vzorce. Namiesto opakovaného zadávania rovnakého vzorca v Exceli môžete jednoducho skopírovať a prilepiť vzorec z jednej bunky do viacerých buniek.
Po napísaní vzorca v Exceli môžete použiť príkazy na kopírovanie a prilepenie do viacerých buniek, viacerých nesusediacich buniek alebo celých stĺpcov. Ak to neurobíte správne, skončíte s tými hroznými chybami # REF a /DIV0. V tomto článku vám ukážeme rôzne metódy, ktoré môžete použiť na kopírovanie vzorcov v Exceli.
Ako kopírovať a prilepiť vzorce v Exceli
Program Microsoft Excel poskytuje rôzne spôsoby kopírovania vzorcov s relatívnymi odkazmi na bunky, absolútnymi odkazmi na bunky alebo zmiešanými odkazmi.
- Skopírujte vzorec z jednej bunky do druhej
- Skopírujte vzorec z jednej bunky do viacerých buniek
- Kopírovanie vzorca do celého stĺpca
- Kopírovanie vzorca bez formátovania
- Skopírujte vzorce do nesusediacich buniek
- Skopírujte vzorce bez zmeny odkazov na bunky
Ako skopírovať vzorec z jednej bunky do druhej v Exceli
Niekedy možno budete chcieť skopírovať vzorec z jednej bunky do druhej v programe Excel, aby ste sa vyhli opätovnému zadávaniu celého vzorca a ušetrili pri tom nejaký čas.
Povedzme, že máme túto tabuľku:
Existuje niekoľko metód na kopírovanie vzorca z jednej bunky do druhej.
Najprv vyberte bunku so vzorcom a kliknite pravým tlačidlom myši a v kontextovej ponuke vyberte možnosť „Kopírovať“, aby ste vzorec skopírovali. Alebo môžete použiť možnosť „Kopírovať“ v časti „Schránka“ na karte „Domov“.
Vzorce však skopírujete aj jednoduchým stlačením klávesovej skratky Ctrl + C. Ide o efektívnejší a časovo úspornejší spôsob.
Potom prejdeme do bunky, do ktorej ju chceme vložiť, stlačte skratku Ctrl + V na prilepenie vzorca. Alebo kliknite pravým tlačidlom myši na bunku, ktorú chcete prilepiť, a kliknite na možnosti v časti „Možnosti prilepenia“: buď jednoduchá možnosť „Prilepiť (P)“ alebo prilepiť ako možnosť „Vzorec (F)“.
Prípadne môžete kliknúť na „Prilepiť špeciálne“ pod šiestimi ikonami prilepenia, čím otvoríte dialógové okno „Prilepiť špeciálne“. Tu máte niekoľko možností vrátane šiestich možností prilepenia z kontextovej ponuky. V sekcii Prilepiť vyberte „Všetko“ alebo „Vzorce“ a kliknite na „OK“.
Teraz by bunka s vloženým vzorcom mala mať rovnaké vzorce (ako tá v skopírovanej bunke), ale s inými odkazmi na bunky. Adresu bunky si Excel sám upraví tak, aby zodpovedala číslu riadka prilepenej bunky.
Skopírujte vzorec z jednej bunky do viacerých buniek
Rovnaká operácia prilepenia funguje rovnako, ak vyberieme viacero buniek alebo rozsah buniek.
Vyberte bunku so vzorcom a stlačte Ctrl + C skopírovať vzorec. Potom vyberte všetky bunky, do ktorých chcete vzorec vložiť, a stlačte Ctrl + V prilepte vzorec alebo použite jednu z vyššie uvedených metód prilepenia na prilepenie vzorca (ako sme to urobili pre jednu bunku).
Skopírujte vzorec do celého stĺpca alebo riadka
V Exceli môžete rýchlo skopírovať vzorec do celého stĺpca alebo riadka.
Ak chcete skopírovať vzorec do stĺpca alebo riadka, najprv zadajte vzorec do bunky. Potom vyberte bunku vzorca (D1) a umiestnite kurzor myši na malý zelený štvorec v pravom dolnom rohu bunky. Keď podržíte kurzor, kurzor sa zmení na čierne znamienko plus (+), ktoré sa nazýva rukoväť výplne. Kliknite a podržte rukoväť výplne a potiahnite ju ľubovoľným smerom (stĺpec alebo riadok) cez bunky, aby ste skopírovali vzorec.
Keď skopírujete vzorec do rozsahu buniek, odkazy na bunky vzorca sa automaticky upravia na základe relatívneho umiestnenia riadkov a stĺpcov a vzorec vykoná výpočty na základe hodnôt v týchto odkazoch na bunky (pozri nižšie).
Vo vyššie uvedenom príklade, keď sa vzorec v D1 (=A1*B1)/2) skopíruje do bunky D2, relatívny odkaz sa zmení na základe jeho umiestnenia (=A2*B2)/2) atď.
Rovnakým spôsobom môžete vzorec presunúť do susedných buniek doľava, doprava alebo nahor.
Ďalším spôsobom, ako skopírovať vzorec do celého stĺpca, je dvojité kliknutie na rukoväť výplne namiesto jej ťahania. Keď dvakrát kliknete na rukoväť výplne, okamžite sa použije vzorec, pokiaľ sú v susednej bunke nejaké údaje.
Skopírujte vzorec do rozsahu bez kopírovania formátovania
Keď skopírujete vzorec do rozsahu buniek pomocou rukoväte výplne, skopíruje sa aj formátovanie zdrojovej bunky, ako je farba písma alebo farba pozadia, mena, percento, čas atď. (ako je znázornené nižšie).
Ak chcete zabrániť kopírovaniu formátovania bunky, potiahnite rukoväť výplne a kliknite na položku „Možnosti automatického vyplnenia“ v pravom dolnom rohu poslednej bunky. Potom v rozbaľovacej ponuke vyberte možnosť „Vyplniť bez formátovania“.
Výsledok:
Skopírujte vzorec Excel iba s formátovaním čísel
Ak chcete skopírovať vzorec iba so vzorcom a formátovaním, ako je percentuálny formát, desatinné čiarky atď.
Skopírujte vzorec a vyberte všetky bunky, do ktorých chcete vzorec skopírovať. Na karte „Domov“ kliknite na šípku pod tlačidlom „Prilepiť“ na páse s nástrojmi. Potom kliknite v rozbaľovacej ponuke na ikonu „Formules & Number Formatting“ (ikona s % fx), aby ste prilepili iba vzorec a formátovanie čísel.
Táto možnosť skopíruje iba formátovanie vzorca a čísla, ale ignoruje všetky ostatné formátovanie buniek, ako je farba pozadia, farba písma atď.
Skopírujte vzorec do nesusediacich/nesusediacich buniek
Ak chcete skopírovať vzorec do nesusediacich buniek alebo nesusediacich rozsahov, môžete to urobiť pomocou Ctrl kľúč.
Vyberte bunku so vzorcom a stlačte Ctrl + C aby ste to skopírovali. Potom vyberte nesusediace bunky/rozsahy a súčasne stlačte a podržte tlačidlo Ctrl kľúč. Potom stlačte Ctrl + V vložte vzorec a stlačte Zadajte dokončiť.
Kopírovanie vzorcov bez zmeny odkazov na bunky v Exceli
Keď sa vzorec skopíruje do inej bunky, Excel automaticky zmení odkazy na bunky tak, aby zodpovedali jeho novému umiestneniu. Tieto odkazy na bunky používajú relatívnu polohu adresy bunky, preto sa nazývajú relatívny odkaz na bunku (bez $). Ak máte napríklad v bunke C1 vzorec „=A1*B1“ a skopírujete tento vzorec do bunky C2, vzorec sa zmení na „=A2*B2“. Všetky metódy, o ktorých sme hovorili vyššie, používajú relatívne odkazy.
Keď skopírujete vzorec s relatívnymi odkazmi na bunky, automaticky sa zmenia odkazy tak, aby vzorec odkazoval na zodpovedajúce riadky a stĺpce. Ak vo vzorci použijete absolútne odkazy, skopíruje sa rovnaký vzorec bez zmeny odkazov na bunky.
Keď vložíte znak dolára ($) pred písmeno stĺpca a číslo riadku bunky (napríklad $A$1), bunka sa zmení na absolútnu bunku. Teraz bez ohľadu na to, kam skopírujete vzorec, ktorý obsahuje absolútny odkaz na bunku, vzorec nikdy nebude. Ak však máte vo vzorci relatívny alebo zmiešaný odkaz na bunku, použite ktorúkoľvek z nasledujúcich metód na kopírovanie bez zmeny odkazov na bunky.
Skopírujte vzorec s absolútnou referenciou bunky pomocou metódy kopírovania a prilepenia
Príležitostne možno budete musieť skopírovať/použiť presný vzorec v stĺpci bez zmeny odkazov na bunky. Ak chcete skopírovať alebo presunúť presný vzorec s absolútnym odkazom, postupujte takto:
Najprv vyberte bunku so vzorcom, ktorý chcete skopírovať. Potom kliknite na riadok vzorcov, vyberte vzorec pomocou myši a stlačte Ctrl + C aby ste to skopírovali. Ak chcete vzorec presunúť, stlačte Ctrl + X aby som to rozrezal. Ďalej stlačte Esc opustíte riadok vzorcov.
Prípadne vyberte bunku so vzorcom a stlačte F2 alebo dvakrát kliknite na bunku. Vybratá bunka sa tým prepne do režimu úprav. Potom vyberte vzorec v bunke a stlačte Ctrl + C skopírovať vzorec v bunke ako text.
Potom vyberte cieľovú bunku a stlačte Ctrl + V na prilepenie vzorca.
Teraz sa presný vzorec skopíruje do cieľovej bunky bez akýchkoľvek zmien odkazu na bunku.
Skopírujte vzorce s absolútnymi alebo zmiešanými bunkami
Ak chcete presunúť alebo skopírovať presné vzorce bez zmeny odkazov na bunky, mali by ste zmeniť relatívne odkazy na bunky na absolútne odkazy. Napríklad pridanie znamienka ($) k relatívnemu odkazu na bunku (B1) z neho urobí absolútny odkaz ($B$1), takže zostane statický bez ohľadu na to, kde sa vzorec skopíruje alebo presunie.
Niekedy však možno budete musieť použiť zmiešané odkazy na bunky ($B1 alebo B$1) pridaním znaku dolára ($) pred písmeno stĺpca alebo číslo riadka, aby ste uzamkli riadok alebo stĺpec na mieste.
Vysvetlíme si to na príklade. Predpokladajme, že máte túto tabuľku, ktorá vypočítava mesačné úspory odpočítaním nájomného (B9) od zárobku (v stĺpci B) každý mesiac.
V nižšie uvedenom príklade vzorec používa absolútny odkaz na bunku ($ B $ 9) na uzamknutie sumy nájomného do bunky B9 a relatívny odkaz na bunku na bunku B2, pretože je potrebné ju upraviť pre každý riadok tak, aby sa zhodovala s každým mesiacom. B9 je absolútna referencia bunky ($ B$ 9), pretože chcete odpočítať rovnakú sumu nájomného od zárobku za každý mesiac.
Povedzme, že chcete presunúť zostatky zo stĺpca C do stĺpca E. Ak skopírujete vzorec (bežnou metódou kopírovania a prilepenia) z bunky C2 (=B2-$B$9), po vložení sa zmení na =D2-$B$9 v bunke E2, takže vaše výpočty sú nesprávne!
V takom prípade zmeňte relatívny odkaz na bunku (B2) na zmiešaný odkaz na bunku ($B2) pridaním znaku „$“ pred písmeno stĺpca vzorca zadaného v bunke C2.
A teraz, ak skopírujete alebo presuniete vzorec z bunky C2 do bunky E2 alebo akejkoľvek inej bunky a použijete vzorec nadol v stĺpci, odkaz na stĺpec zostane rovnaký, zatiaľ čo číslo riadka sa upraví pre každú bunku.
Kopírovanie a prilepenie vzorcov programu Excel bez zmeny odkazov pomocou programu Poznámkový blok
Pomocou možnosti Zobraziť vzorec môžete zobraziť každý vzorec v tabuľke Excel. Ak to chcete urobiť, prejdite na kartu „Vzorce“ a vyberte „Zobraziť vzorce“.
Prípadne môžete vstúpiť do režimu zobrazenia vzorca stlačením tlačidla Ctrl + ` skratka, ktorá zobrazí každý vzorec v pracovnom hárku. Kláves s vážnym prízvukom (`) nájdete v ľavom hornom rohu klávesnice v riadku s číselnými klávesmi (pod ESC a pred tlačidlom číslo 1).
Vyberte všetky bunky so vzorcami, ktoré chcete skopírovať, a stlačte Ctrl + C kopírovať ich, príp Ctrl + X aby som ich rozrezal. Potom otvorte Poznámkový blok a stlačte Ctrl + V na prilepenie vzorcov do poznámkového bloku.
Ďalej vyberte vzorec a skopírujte (Ctrl + C) z poznámkového bloku a prilepte ho (Ctrl + V) v bunke, do ktorej chcete skopírovať presný vzorec. Môžete ich skopírovať a vložiť jeden po druhom alebo všetky naraz.
Po vložení vzorcov vypnite režim zobrazenia vzorcov stlačením Ctrl + ` alebo znova prejdite na „Vzorce“ –> „Zobraziť vzorce“.
Skopírujte presné vzorce pomocou funkcie Nájsť a nahradiť v Exceli
Ak chcete skopírovať rozsah presných vzorcov, môžete na to použiť aj nástroj Nájsť a nahradiť v Exceli.
Vyberte všetky bunky, ktoré obsahujú vzorce, ktoré chcete skopírovať. Potom prejdite na kartu „Domov“, kliknite na „Nájsť a vybrať“ v skupine Úpravy a vyberte možnosť „Nahradiť“ alebo jednoducho stlačte Ctrl + H otvorte dialógové okno Nájsť a nahradiť.
V dialógovom okne Nájsť a nahradiť zadajte do poľa „Nájsť čo“ znamienko rovnosti (=). Do poľa „Nahradiť čím“ zadajte symbol alebo znak, ktorý ešte nie je súčasťou vašich vzorcov, napríklad # alebo , atď. Potom kliknite na tlačidlo „Nahradiť všetko“.
Zobrazí sa okno so správou s výzvou „Vykonali sme 6 nahradení“ (pretože sme vybrali 6 buniek so vzorcami). Potom kliknutím na „OK“ a „Zavrieť“ zatvorte obe dialógové okná. Týmto sa nahradí všetky znamienka rovná sa (=) znamienkam hash (#) a vzorce sa premenia na textové reťazce. Teraz sa odkazy na bunky vzorcov pri kopírovaní nezmenia.
Teraz môžete tieto bunky vybrať a stlačiť Ctrl + C skopírovať ich a vložiť do cieľových buniek pomocou Ctrl + V.
Nakoniec musíte zmeniť znaky (#) späť na znaky (=). Ak to chcete urobiť, vyberte oba rozsahy (pôvodný a skopírovaný rozsah) a stlačte Ctrl + H otvorte dialógové okno Nájsť a nahradiť. Tentokrát zadajte znak hash (#) do poľa „Nájsť čo“ a znak rovný (=) do poľa „Nahradiť za“ a kliknite na tlačidlo „Nahradiť všetko“. Kliknutím na „Zavrieť“ zatvorte dialógové okno.
Teraz sa textové reťazce skonvertujú späť na vzorce a dostanete tento výsledok:
Hotový!