Kopírovať vzorce bez posunu odkazu

Problém

Predpokladajme, že máme takúto jednoduchú tabuľku, v ktorej sa vypočítajú sumy za každý mesiac v dvoch mestách a potom sa súčet prepočíta na eurá kurzom zo žltej bunky J2.

Problém je v tom, že ak skopírujete rozsah D2:D8 so vzorcami niekde inde na hárku, potom Microsoft Excel automaticky opraví prepojenia v týchto vzorcoch, presunie ich na nové miesto a prestane počítať:

Úloha: skopírujte rozsah so vzorcami tak, aby sa vzorce nemenili a zostali rovnaké, pri zachovaní výsledkov výpočtu.

Metóda 1. Absolútne väzby

Metóda 2: Dočasne zakážte vzorce

Ak chcete zabrániť zmenám vzorcov pri kopírovaní, musíte sa (dočasne) uistiť, že ich Excel prestane považovať za vzorce. Dá sa to dosiahnuť nahradením znamienka rovnosti (=) akýmkoľvek iným znakom, ktorý sa vo vzorcoch bežne nenachádza, ako je napríklad znak hash (#) alebo pár ampersandov (&&) pre čas kopírovania. Pre to:

1. Vyberte rozsah pomocou vzorcov (v našom príklade D2:D8)
2. kliknite Ctrl + H na klávesnici alebo na karte Domov – Nájsť a vybrať – Nahradiť (Domov — Nájsť a vybrať — Nahradiť)

   

3. V zobrazenom dialógovom okne zadajte, čo hľadáme a čím nahrádzame, a v parametre (Možnosti) nezabudni si to ujasniť Rozsah vyhľadávania – Vzorce. Tlačíme Nahradiť všetko (Nahradiť všetko).
4. Skopírujte výsledný rozsah s deaktivovanými vzorcami na správne miesto:

   

5. vymeniť # on = späť pomocou rovnakého okna, čím sa vráti funkčnosť vzorcov.

Metóda 3: Kopírovanie cez Poznámkový blok

Táto metóda je oveľa rýchlejšia a jednoduchšia.

Stlačte klávesovú skratku Ctrl+Ё alebo tlačidlo Zobraziť vzorce pútko vzorec (Vzorce — Zobraziť vzorce), zapnúť režim kontroly vzorcov – v bunkách sa namiesto výsledkov zobrazia vzorce, podľa ktorých sa vypočítavajú:

Skopírujte náš rozsah D2:D8 a vložte ho do normy zápisník:

Teraz vyberte všetko prilepené (Ctrl + A), skopírujte to znova do schránky (Ctrl + C) a vložte ho na hárok na miesto, ktoré potrebujete:

Zostáva iba stlačiť tlačidlo Zobraziť vzorce (Zobraziť vzorce)vrátiť Excel do normálneho režimu.

Poznámka: Táto metóda niekedy zlyhá na zložitých tabuľkách so zlúčenými bunkami, ale vo veľkej väčšine prípadov funguje dobre.

Metóda 4. Makro

Ak často musíte robiť takéto kopírovanie vzorcov bez posúvania referencií, potom má zmysel použiť na to makro. Stlačte klávesovú skratku Alt + F11 alebo tlačidlo Visual Basic pútko vývojka (vývojár), vložte nový modul cez ponuku Vložiť – Modul  a skopírujte tam text tohto makra:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Chyba Pokračovať Ďalej Set copyRange = Application.InputBox("Vyberte bunky so vzorcami na kopírovanie.", _ "Kopírovať vzorce presne", Predvolené:=Selection.Address, Type := 8) Ak copyRange nie je nič, potom ukončite podmnožinu pasteRange = Application.InputBox("Teraz vyberte rozsah prilepenia." & vbCrLf & vbCrLf & _ "Rozsah musí mať rovnakú veľkosť ako pôvodný rozsah buniek " & vbCrLf & _ " kopírovať." , "Kopírovať vzorce presne", _ Predvolené:=Výber.Adresa, Typ:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Kopírovať a prilepiť rozsahy sa líšia veľkosťou!", vbExclamation, "Copy error" Exit Sub End If If pasteRange Is Nothing Then Exit Sub Else pasteRange.Formula = copyRange.Formula End If End Sub

Na spustenie makra môžete použiť tlačidlo. Makrá pútko vývojka (Vývojár – makrá) alebo klávesová skratka Alt + F8. Po spustení makra vás požiada, aby ste vybrali rozsah s pôvodnými vzorcami a rozsahom vloženia a vzorce sa automaticky skopírujú:

• Pohodlné prezeranie vzorcov a výsledkov súčasne
• Prečo je vo vzorcoch programu Excel potrebný referenčný štýl R1C1
• Ako rýchlo nájsť všetky bunky pomocou vzorcov
• Nástroj na kopírovanie presných vzorcov z doplnku PLEX