Obsah
Problém
Předpokládejme, že máme jednoduchou tabulku, jako je tato, ve které se vypočítávají částky za každý měsíc ve dvou městech a pak se celková částka převede na eura kurzem ze žluté buňky J2.
Problém je v tom, že pokud zkopírujete rozsah D2:D8 se vzorci někam jinam na list, pak Microsoft Excel automaticky opraví odkazy v těchto vzorcích, přesune je na nové místo a přestane počítat:
Úkol: zkopírujte rozsah se vzorci tak, aby se vzorce neměnily a zůstaly stejné, při zachování výsledků výpočtu.
Metoda 1. Absolutní vazby
Metoda 2: Dočasně zakažte vzorce
Chcete-li zabránit změnám vzorců při kopírování, musíte se (dočasně) ujistit, že je Excel přestane považovat za vzorce. Toho lze dosáhnout nahrazením znaménka rovná se (=) jakýmkoli jiným znakem, který se běžně ve vzorcích nevyskytuje, například znakem hash (#) nebo dvojicí ampersandů (&&) pro dobu kopírování. Pro tohle:
- Vyberte rozsah pomocí vzorců (v našem příkladu D2:D8)
- klikněte Ctrl + H na klávesnici nebo na kartě Domů – Najít a vybrat – Nahradit (Domů — Najít a vybrat — Nahradit)
- V zobrazeném dialogovém okně zadejte, co hledáme a čím nahrazujeme, a v parametry (Možnosti) nezapomeňte upřesnit Rozsah vyhledávání – Vzorce. Tiskneme Vyměnit vše (Vyměnit vše).
- Zkopírujte výsledný rozsah s deaktivovanými vzorci na správné místo:
- Nahradit # on = zpět pomocí stejného okna s návratem funkčnosti do vzorců.
Metoda 3: Kopírování přes Poznámkový blok
Tato metoda je mnohem rychlejší a jednodušší.
Stiskněte klávesovou zkratku Ctrl+Ё nebo tlačítko Zobrazit vzorce Karta vzorec (Vzorce — Zobrazit vzorce), pro zapnutí režimu kontroly vzorců – místo výsledků se v buňkách zobrazí vzorce, podle kterých se počítají:
Zkopírujte náš rozsah D2:D8 a vložte jej do standardu zápisník:
Nyní vyberte vše vložené (Ctrl + A), znovu to zkopírujte do schránky (Ctrl + C) a vložte to na list na místo, které potřebujete:
Zbývá pouze stisknout tlačítko Zobrazit vzorce (Zobrazit vzorce)vrátit Excel do normálního režimu.
Poznámka: Tato metoda někdy selže na složitých tabulkách se sloučenými buňkami, ale v naprosté většině případů funguje dobře.
Metoda 4. Makro
Pokud musíte často dělat takové kopírování vzorců bez posouvání referencí, pak má smysl k tomu použít makro. Stiskněte klávesovou zkratku Alt + F11 nebo tlačítko Visual Basic Karta vývojka (Vývojář), vložte nový modul přes nabídku Vložit – Modul a zkopírujte tam text tohoto makra:
Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Chyba Pokračovat Další Set copyRange = Application.InputBox("Vyberte buňky se vzorci ke kopírování.", _ "Kopírovat vzorce přesně", Výchozí:=Selection.Address, Type := 8) Pokud copyRange Is Nothing Then Exit Sub Set pasteRange = Application.InputBox("Nyní vyberte rozsah vložení." & vbCrLf & vbCrLf & _ "Rozsah musí mít stejnou velikost jako původní " & vbCrLf & _ " rozsah buněk zkopírovat." , "Kopírovat vzorce přesně", _ Výchozí:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Kopírovat a vložit rozsahy se liší velikostí!", vbExclamation, "Chyba kopírování" Exit Sub End If If pasteRange Is Nothing Then Exit Sub Else pasteRange.Formula = copyRange.Formula End If End SubKe spuštění makra můžete použít tlačítko. makra Karta vývojka (Vývojář — Makra) nebo klávesovou zkratkou Alt + F8. Po spuštění makra vás požádá o výběr rozsahu s původními vzorci a rozsahem vložení a automaticky zkopíruje vzorce:
- Pohodlné prohlížení vzorců a výsledků současně
- Proč je ve vzorcích aplikace Excel potřebný referenční styl R1C1
- Jak rychle najít všechny buňky se vzorci
- Nástroj pro kopírování přesných vzorců z doplňku PLEX