Naučte se kopírovat vzorec do více buněk, dolů po sloupci, do nesousedících buněk, kopírovat vzorce s absolutními nebo smíšenými odkazy na buňky atd.
Kopírování vzorců je jedním z nejběžnějších a nejjednodušších úkolů, které provádíte v typickém tabulkovém procesoru, který se opírá hlavně o vzorce. Namísto opakovaného zadávání stejného vzorce v Excelu můžete jednoduše zkopírovat a vložit vzorec z jedné buňky do více buněk.
Po napsání vzorce v Excelu můžete použít příkazy kopírovat a vložit do více buněk, více nesousedících buněk nebo celých sloupců. Pokud to neuděláte správně, skončíte s těmi hroznými chybami # REF a /DIV0. V tomto článku vám ukážeme různé metody, které můžete použít ke kopírování vzorců v Excelu.
Jak kopírovat a vkládat vzorce v Excelu
Aplikace Microsoft Excel poskytuje různé způsoby kopírování vzorců s relativními odkazy na buňky, absolutními odkazy na buňky nebo smíšenými odkazy.
- Zkopírujte vzorec z jedné buňky do druhé
- Zkopírujte vzorec z jedné buňky do více buněk
- Kopírování vzorce do celého sloupce
- Kopírování vzorce bez formátování
- Zkopírujte vzorce do nesousedících buněk
- Kopírování vzorců bez změny odkazů na buňky
Jak zkopírovat vzorec z jedné buňky do druhé v Excelu
Někdy možná budete chtít zkopírovat vzorec z jedné buňky do druhé v aplikaci Excel, abyste se vyhnuli přepisování celého vzorce znovu a ušetřili při tom nějaký čas.
Řekněme, že máme tuto tabulku:
Existuje několik metod, jak zkopírovat vzorec z jedné buňky do druhé.
Nejprve vyberte buňku se vzorcem a klikněte pravým tlačítkem myši a v kontextové nabídce vyberte „Kopírovat“ pro zkopírování vzorce. Nebo můžete použít možnost ‚Kopírovat‘ v části ‚Schránka‘ na kartě ‚Domů‘.
Vzorce ale také kopírujete pouhým stisknutím klávesové zkratky Ctrl + C. Jedná se o efektivnější a časově úspornější metodu.
Poté přejdeme do buňky, do které ji chceme vložit, stiskněte zkratku Ctrl + V pro vložení vzorce. Nebo klikněte pravým tlačítkem na buňku, kterou chcete vložit, a klikněte na možnosti v části „Možnosti vložení“: buď jednoduchá možnost „Vložit (P)“ nebo vložit jako možnost „Vzorec (F)“.
Případně můžete také kliknout na ‚Vložit jinak‘ pod šesti ikonami vložení a otevřít dialogové okno ‚Vložit jinak‘. Zde máte několik možností včetně šesti možností vložení z kontextové nabídky. V části Vložit vyberte „Vše“ nebo „Vzorce“ a klikněte na „OK“.
Nyní by buňka s vloženým vzorcem měla mít stejné vzorce (jako ta v zkopírované buňce), ale s jinými odkazy na buňky. Adresu buňky si Excel sám upraví tak, aby odpovídala číslu řádku vložené buňky.
Zkopírujte vzorec z jedné buňky do více buněk
Stejná operace vložení funguje stejně, pokud vybereme více buněk nebo rozsah buněk.
Vyberte buňku se vzorcem a stiskněte Ctrl + C zkopírovat vzorec. Poté vyberte všechny buňky, kam chcete vzorec vložit, a stiskněte Ctrl + V vložte vzorec nebo použijte jednu z výše uvedených metod vložení pro vložení vzorce (jako jsme to udělali pro jednu buňku).
Zkopírujte vzorec do celého sloupce nebo řádku
V Excelu můžete rychle zkopírovat vzorec do celého sloupce nebo řádku.
Chcete-li zkopírovat vzorec do sloupce nebo řádku, nejprve zadejte vzorec do buňky. Poté vyberte buňku vzorce (D1) a umístěte kurzor na malý zelený čtvereček v pravém dolním rohu buňky. Když najedete, kurzor se změní na černé znaménko plus (+), které se nazývá rukojeť výplně. Klikněte a podržte úchyt výplně a přetáhněte jej libovolným směrem (sloupec nebo řádek) přes buňky, abyste vzorec zkopírovali.
Když zkopírujete vzorec do rozsahu buněk, odkazy na buňky vzorce se automaticky upraví na základě relativního umístění řádků a sloupců a vzorec bude provádět výpočty na základě hodnot v těchto odkazech na buňky (viz níže).
Ve výše uvedeném příkladu, když se vzorec v D1 (=A1*B1)/2) zkopíruje do buňky D2, relativní odkaz se změní na základě jeho umístění (=A2*B2)/2) a tak dále.
Stejným způsobem můžete vzorec přetáhnout do sousedních buněk doleva, doprava nebo nahoru.
Dalším způsobem, jak zkopírovat vzorec do celého sloupce, je poklepat na úchyt výplně namísto jeho přetažení. Když dvakrát kliknete na úchyt výplně, okamžitě se použije vzorec, pokud jsou v sousední buňce nějaká data.
Zkopírujte vzorec do rozsahu bez kopírování formátování
Když zkopírujete vzorec do rozsahu buněk pomocí úchytu výplně, zkopíruje se také formátování zdrojové buňky, jako je barva písma nebo barva pozadí, měna, procento, čas atd. (jak je znázorněno níže).
Chcete-li zabránit kopírování formátování buňky, přetáhněte úchyt výplně a klikněte na „Možnosti automatického vyplnění“ v pravém dolním rohu poslední buňky. Poté v rozevírací nabídce vyberte „Vyplnit bez formátování“.
Výsledek:
Zkopírujte vzorec aplikace Excel pouze s formátováním čísel
Pokud chcete vzorec zkopírovat pouze se vzorcem a formátováním, jako je formát procent, desetinné čárky atd.
Zkopírujte vzorec a vyberte všechny buňky, do kterých chcete vzorec zkopírovat. Na kartě ‚Domů‘ klikněte na šipku pod tlačítkem ‚Vložit‘ na pásu karet. Poté klikněte na ikonu ‚Formulas & Number Formatting‘ (ikona s % fx) v rozevíracím seznamu a vložte pouze vzorec a formátování čísel.
Tato možnost zkopíruje pouze formátování vzorce a čísla, ale ignoruje všechna ostatní formátování buněk, jako je barva pozadí, barva písma atd.
Zkopírujte vzorec do nesousedících/nesouvislých buněk
Pokud chcete zkopírovat vzorec do nesousedících buněk nebo nesousedících oblastí, můžete to udělat pomocí Ctrl klíč.
Vyberte buňku se vzorcem a stiskněte Ctrl + C zkopírovat to. Poté vyberte nesousedící buňky/rozsahy a zároveň stiskněte a podržte tlačítko Ctrl klíč. Poté stiskněte Ctrl + V vložte vzorec a stiskněte Vstupte dokončit.
Kopírování vzorců beze změny odkazů na buňky v Excelu
Když je vzorec zkopírován do jiné buňky, Excel automaticky změní odkazy na buňky tak, aby odpovídaly jeho novému umístění. Tyto odkazy na buňky používají relativní umístění adresy buňky, proto se nazývají relativní odkaz na buňku (bez $). Pokud máte například v buňce C1 vzorec ‚=A1*B1‘ a zkopírujete tento vzorec do buňky C2, vzorec se změní na ‚=A2*B2‘. Všechny metody, které jsme diskutovali výše, používají relativní odkazy.
Když zkopírujete vzorec s relativními odkazy na buňky, automaticky se změní odkazy tak, aby vzorec odkazoval na odpovídající řádky a sloupce. Pokud ve vzorci použijete absolutní odkazy, zkopíruje se stejný vzorec beze změny odkazů na buňky.
Když vložíte znak dolaru ($) před písmeno sloupce a číslo řádku buňky (například $A$1), změní se buňka na absolutní buňku. Nyní bez ohledu na to, kam zkopírujete vzorec, který obsahuje absolutní odkaz na buňku, vzorec nikdy nebude. Ale pokud máte ve vzorci relativní nebo smíšený odkaz na buňku, použijte kteroukoli z následujících metod ke kopírování beze změny odkazů na buňky.
Zkopírujte vzorec s absolutním odkazem na buňku pomocí metody kopírování a vkládání
Občas může být nutné zkopírovat/aplikovat přesný vzorec ve sloupci, aniž byste změnili odkazy na buňky. Pokud chcete zkopírovat nebo přesunout přesný vzorec s absolutním odkazem, postupujte takto:
Nejprve vyberte buňku se vzorcem, který chcete zkopírovat. Poté klikněte na řádek vzorců, vyberte vzorec pomocí myši a stiskněte Ctrl + C zkopírovat to. Pokud chcete vzorec přesunout, stiskněte Ctrl + X abych to rozsekal. Dále stiskněte Esc klávesu pro opuštění řádku vzorců.
Případně vyberte buňku se vzorcem a stiskněte F2 nebo poklepejte na buňku. Tím přepnete vybranou buňku do režimu úprav. Poté vyberte vzorec v buňce a stiskněte Ctrl + C zkopírujte vzorec v buňce jako text.
Poté vyberte cílovou buňku a stiskněte Ctrl + V pro vložení vzorce.
Nyní se přesný vzorec zkopíruje do cílové buňky bez jakýchkoli změn odkazu na buňku.
Zkopírujte vzorce s absolutními nebo smíšenými odkazy na buňky
Pokud chcete přesouvat nebo kopírovat přesné vzorce beze změny odkazů na buňky, měli byste změnit relativní odkazy na buňky na absolutní odkazy. Například přidání znaménka ($) k relativnímu odkazu na buňku (B1) z něj udělá absolutní odkaz ($B$1), takže zůstane statický bez ohledu na to, kam se vzorec zkopíruje nebo přesune.
Někdy však možná budete muset použít smíšené odkazy na buňky ($B1 nebo B$1) přidáním znaku dolaru ($) před písmeno sloupce nebo číslo řádku, abyste uzamkli řádek nebo sloupec na místě.
Pojďme si to vysvětlit na příkladu. Předpokládejme, že máte tuto tabulku, která vypočítává měsíční úspory odečtením nájemného (B9) od výdělku (ve sloupci B) každý měsíc.
V níže uvedeném příkladu vzorec používá absolutní odkaz na buňku ($ B $ 9) k uzamčení částky nájemného na buňku B9 a relativní odkaz na buňku na buňku B2, protože je třeba upravit pro každý řádek tak, aby odpovídala každý měsíc. B9 je absolutní odkaz na buňku ($ B $ 9), protože chcete odečíst stejnou částku nájemného od měsíčních příjmů.
Řekněme, že chcete přesunout zůstatky ze sloupce C do sloupce E. Pokud vzorec zkopírujete (obvyklou metodou kopírování/vložení) z buňky C2 (=B2-$B$9), změní se po vložení na =D2-$B$9 v buňce E2, takže vaše výpočty jsou špatné!
V takovém případě změňte relativní odkaz na buňku (B2) na smíšený odkaz na buňku ($B2) přidáním znaku „$“ před písmeno sloupce vzorce zadaného do buňky C2.
A nyní, pokud zkopírujete nebo přesunete vzorec z buňky C2 do E2 nebo jakékoli jiné buňky a použijete vzorec dolů ve sloupci, odkaz na sloupec zůstane stejný, zatímco číslo řádku bude upraveno pro každou buňku.
Kopírovat Vložit vzorce aplikace Excel beze změny odkazů pomocí programu Poznámkový blok
Pomocí možnosti Zobrazit vzorec můžete zobrazit každý vzorec v tabulce aplikace Excel. Chcete-li to provést, přejděte na kartu „Vzorce“ a vyberte „Zobrazit vzorce“.
Případně můžete vstoupit do režimu zobrazení vzorce stisknutím tlačítka Ctrl + ` zkratka, která zobrazí každý vzorec ve vašem listu. Klávesu s velkým přízvukem (`) najdete v levém horním rohu klávesnice na řádku s numerickými klávesami (pod ESC a před klávesou číslo 1).
Vyberte všechny buňky se vzorci, které chcete zkopírovat, a stiskněte Ctrl + C kopírovat je, popř Ctrl + X stříhat je. Poté otevřete Poznámkový blok a stiskněte Ctrl + V pro vložení vzorců do poznámkového bloku.
Dále vyberte vzorec a zkopírujte (Ctrl + C) jej z poznámkového bloku a vložte jej (Ctrl + V) v buňce, kam chcete zkopírovat přesný vzorec. Můžete je zkopírovat a vložit jeden po druhém nebo všechny najednou.
Po vložení vzorců vypněte režim zobrazení vzorce stisknutím Ctrl + ` nebo znovu přejděte na ‚Vzorce‘ –> ‚Zobrazit vzorce‘.
Zkopírujte přesné vzorce pomocí Excelu Najít a nahradit
Pokud chcete zkopírovat řadu přesných vzorců, můžete k tomu také použít nástroj Najít a nahradit aplikace Excel.
Vyberte všechny buňky obsahující vzorce, které chcete zkopírovat. Poté přejděte na kartu ‚Domů‘, klikněte na ‚Najít a vybrat‘ ve skupině Úpravy a vyberte možnost ‚Nahradit‘ nebo jednoduše stiskněte Ctrl + H otevřete dialogové okno Najít a nahradit.
V dialogovém okně Najít a nahradit zadejte rovnítko (=) do pole Najít. Do pole ‚Nahradit čím‘ zadejte symbol nebo znak, který ještě není součástí vašich vzorců, například # nebo , atd. Poté klikněte na tlačítko ‚Nahradit vše‘.
Zobrazí se okno se zprávou s výzvou „Udělali jsme 6 nahrazení“ (protože jsme vybrali 6 buněk se vzorci). Poté kliknutím na „OK“ a „Zavřít“ zavřete oba dialogy. Tímto způsobem nahradíte všechny znaky rovnající se (=) znaky hash (#) a změníte vzorce na textové řetězce. Nyní se odkazy na buňky vzorců při kopírování nezmění.
Nyní můžete vybrat tyto buňky, stiskněte Ctrl + C zkopírovat je a vložit do cílových buněk pomocí Ctrl + V.
Nakonec musíte změnit znaménka (#) zpět na znaménka (=). Chcete-li to provést, vyberte oba rozsahy (původní a zkopírovaný rozsah) a stiskněte Ctrl + H otevřete dialogové okno Najít a nahradit. Tentokrát zadejte znak hash (#) do pole ‚Najít‘ a znak rovno (=) do pole ‚Nahradit‘ a klikněte na tlačítko ‚Nahradit vše‘. Dialogové okno zavřete kliknutím na „Zavřít“.
Nyní jsou textové řetězce převedeny zpět na vzorce a získáte tento výsledek:
Hotovo!