Kopiraj formule bez pomaka veze

Problem

Pretpostavimo da imamo ovakvu jednostavnu tablicu u kojoj su izračunati iznosi za svaki mjesec u dva grada, a zatim se ukupan iznos preračunava u eure prema tečaju iz žute ćelije J2.

Problem je u tome što ako kopirate raspon D2:D8 s formulama negdje drugdje na listu, tada će Microsoft Excel automatski ispraviti veze u ovim formulama, premjestiti ih na novo mjesto i prestati brojati:

Zadatak: kopirajte raspon s formulama tako da se formule ne mijenjaju i da ostanu iste, uz zadržavanje rezultata izračuna.

Metoda 1. Apsolutne veze

Metoda 2: Privremeno onemogućite formule

Kako biste spriječili promjenu formula prilikom kopiranja, trebate (privremeno) osigurati da ih Excel prestane tretirati kao formule. To se može učiniti zamjenom znaka jednakosti (=) bilo kojim drugim znakom koji se inače ne nalazi u formulama, kao što je znak hash (#) ili par ampersanda (&&) za vrijeme kopiranja. Za ovo:

1. Odaberite raspon s formulama (u našem primjeru D2:D8)
2. Kliknite Ctrl + H na tipkovnici ili na kartici Početna – Pronađi i odaberi – Zamijeni (Početna — Pronađi i odaberi — Zamijeni)

   

3. U dijaloški okvir koji se pojavi upisujemo što tražimo i čime zamjenjujemo te u Parametri (Opcije) ne zaboravi pojasniti Opseg pretraživanja – Formule. Pritišćemo Zamjeni sve (Zamjeni sve).
4. Kopirajte dobiveni raspon s deaktiviranim formulama na pravo mjesto:

   

5. zamijeniti # on = natrag koristeći isti prozor, vraćajući funkcionalnost formulama.

Metoda 3: Kopiranje putem Notepada

Ova metoda je mnogo brža i lakša.

Pritisnite tipkovnički prečac Ctrl+Ë ili gumb Prikaži formule kartica formula (Formule — Prikaži formule), za uključivanje načina provjere formula – umjesto rezultata u ćelijama će biti prikazane formule po kojima su izračunati:

Kopirajte naš raspon D2:D8 i zalijepite ga u standard Bilježnica:

Sada odaberite sve zalijepljeno (Ctrl + A), ponovno kopirajte u međuspremnik (Ctrl + C) i zalijepite na list na mjesto koje vam je potrebno:

Ostaje samo pritisnuti tipku Prikaži formule (Prikaži formule)kako biste Excel vratili u normalni način rada.

Napomena: ova metoda ponekad ne uspije na složenim tablicama sa spojenim ćelijama, ali u velikoj većini slučajeva radi dobro.

Metoda 4. Makro

Ako često morate raditi takvo kopiranje formula bez pomicanja referenci, onda ima smisla koristiti makronaredbu za to. Pritisnite tipkovni prečac Alt + F11 ili gumb Visual Basic kartica razvijač (Programer), umetnite novi modul kroz izbornik Umetak – Modul  i tamo kopirajte tekst ove makronaredbe:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Odaberi ćelije s formulama za kopiranje.", _ "Točno kopiraj formule", Default:=Selection.Address, Type := 8) Ako copyRange nije Nothing Then Exit Sub Set pasteRange = Application.InputBox("Sada odaberite raspon lijepljenja." & vbCrLf & vbCrLf & _ "Raspon mora biti jednak veličini izvornom " & vbCrLf & _ " rasponu ćelija za kopiranje." , "Točno kopiraj formule", _ Default:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Kopiraj i zalijepi raspone variraju u veličini!", vbExclamation, "Copy error " Exit Sub End If If pasteRange Is Nothing Then Exit Sub Else pasteRange.Formula = copyRange.Formula End If End Sub

Možete koristiti gumb za pokretanje makronaredbe. makronaredbe kartica razvijač (Razvojni programer — Makronaredbe) ili tipkovni prečac Alt + F8. Nakon pokretanja makronaredbe, tražit će od vas da odaberete raspon s izvornim formulama i raspon umetanja te će automatski kopirati formule:

• Jednostavan pregled formula i rezultata u isto vrijeme
• Zašto je stil reference R1C1 potreban u Excel formulama
• Kako brzo pronaći sve ćelije s formulama
• Alat za kopiranje točnih formula iz PLEX dodatka