Kopirajte formule bez pomaka veze

problem

Pretpostavimo da imamo ovakvu jednostavnu tabelu u kojoj se iznosi izračunavaju za svaki mjesec u dva grada, a zatim se ukupan iznos konvertuje u evre po kursu iz žute ćelije J2.

Kopirajte formule bez pomaka veze

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

Kopirajte formule bez pomaka veze

Zadatak: kopirati opseg sa formulama tako da se formule ne mijenjaju i ostaju iste, zadržavajući rezultate proračuna.

Metoda 1. Apsolutne veze

Kao što možete vidjeti iz prethodne slike, Excel pomjera samo relativne veze. Apsolutna (sa znakovima $) referenca na žutu ćeliju $J$2 se nije pomjerila. Stoga, za tačno kopiranje formula, možete privremeno pretvoriti sve reference u svim formulama u apsolutne. Trebat ćete odabrati svaku formulu u traci formule i pritisnuti tipku F4:
Kopirajte formule bez pomaka veze
Sa velikim brojem ćelija, ova opcija, naravno, nestaje - previše je naporna.

Metod 2: Privremeno onemogućite formule

Da biste spriječili da se formule mijenjaju prilikom kopiranja, morate (privremeno) osigurati da Excel prestane da ih tretira kao formule. Ovo se može učiniti zamjenom znaka jednakosti (=) sa bilo kojim drugim znakom koji se inače ne nalazi u formulama, kao što je znak heš (#) ili par ampersanda (&&) za vrijeme kopiranja. Za ovo:

  1. Odaberite raspon sa formulama (u našem primjeru D2:D8)
  2. kliknite Ctrl + H na tastaturi ili na kartici Početna – Pronađite i odaberite – Zamijenite (Početna — Pronađi&Odaberi — Zamijeni)

    Kopirajte formule bez pomaka veze

  3. U dijaloškom okviru koji se pojavi unesite ono što tražimo i čime zamjenjujemo, iu parametri (Opcije) ne zaboravite pojasniti Opseg pretrage – formule. Pritiskamo Zamijenite sve (Zamijeni sve).
  4. Kopirajte rezultirajući raspon s deaktiviranim formulama na pravo mjesto:

    Kopirajte formule bez pomaka veze

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

Metoda 3: Kopirajte putem Notepad-a

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

Pritisnite prečicu na tastaturi Ctrl+Ë ili dugme Prikaži formule tabulator formula (Formule — Prikaži formule), da uključite način provjere formule – umjesto rezultata, ćelije će prikazati formule po kojima su izračunate:

Kopirajte formule bez pomaka veze

Kopirajte naš raspon D2:D8 i zalijepite ga u standard sveska:

Kopirajte formule bez pomaka veze

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

Kopirajte formule bez pomaka veze

Ostaje samo da pritisnete dugme Prikaži formule (Prikaži formule)da vratite Excel u normalan način rada.

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

Metoda 4. Makro

Ako često morate raditi takvo kopiranje formula bez pomjeranja referenci, onda ima smisla koristiti makro za ovo. Pritisnite prečicu na tastaturi Alt + F11 ili dugme Visual Basic tabulator Developer (programer), ubacite novi modul kroz meni Umetak – Modul  i kopirajte tekst ovog makroa tamo:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Nastavi Next Set copyRange = Application.InputBox("Odaberi ćelije sa formulama za kopiranje.", _ "Tačno kopiraj formule", Default:=Odabir.Adresa, Tip := 8) Ako copyRange nije ništa onda izađite iz Sub Set pasteRange = Application.InputBox("Sada odaberite raspon zalijepi." & vbCrLf & vbCrLf & _ "Opseg mora biti jednak po veličini originalnom " & vbCrLf & _ " opsegu ćelija za kopiranje." , "Tačno kopiraj formule", _ Zadano:=Odabir.Adresa, Tip:=8) Ako pasteRange.Cells.Count <> copyRange.Cells.Count Onda MsgBox "Rasponi kopiranja i lijepljenja variraju u veličini!", vbExclamation, "Greška pri kopiranju " Izađi iz podkraja ako je pasteRange ništa onda izađi iz poddruge pasteRange.Formula = copyRange.Formula End If End pod

Možete koristiti dugme za pokretanje makroa. Macros tabulator Developer (Programer — Makroi) ili prečica na tastaturi Alt + F8. Nakon pokretanja makronaredbe, tražit će od vas da odaberete raspon s originalnim formulama i rasponom umetanja i automatski će kopirati formule:

Kopirajte formule bez pomaka veze

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

 

Ostavite odgovor