Kopirajte formule brez premika povezave

problem

Recimo, da imamo preprosto tabelo, kot je ta, v kateri so zneski izračunani za vsak mesec v dveh mestih, nato pa se vsota pretvori v evre po tečaju iz rumene celice J2.

Kopirajte formule brez premika povezave

Težava je v tem, da če obseg D2:D8 s formulami kopirate nekam drugam na list, bo Microsoft Excel samodejno popravil povezave v teh formulah, jih premaknil na novo mesto in prenehal šteti:

Kopirajte formule brez premika povezave

Naloga: kopirajte obseg s formulami, tako da se formule ne spremenijo in ostanejo enake, pri čemer se ohranijo rezultati izračuna.

Metoda 1. Absolutne povezave

Kot lahko vidite na prejšnji sliki, Excel premika samo relativne povezave. Absolutni (z znaki $) sklic na rumeno celico $J$2 se ni premaknil. Zato lahko za natančno kopiranje formul vse sklice v vseh formulah začasno pretvorite v absolutne. V vrstici s formulami boste morali izbrati vsako formulo in pritisniti tipko F4:
Kopirajte formule brez premika povezave
Z velikim številom celic ta možnost seveda izgine - preveč je naporna.

2. način: Začasno onemogočite formule

Če želite preprečiti spreminjanje formul pri kopiranju, morate (začasno) zagotoviti, da jih Excel neha obravnavati kot formule. To je mogoče storiti tako, da enačaj (=) zamenjate s katerim koli drugim znakom, ki ga običajno ne najdemo v formulah, na primer z dišavo (#) ali parom & (&&) za čas kopiranja. Za to:

  1. Izberite obseg s formulami (v našem primeru D2:D8)
  2. klik Ctrl + H na tipkovnici ali na zavihku Domov – Najdi in izberi – Zamenjaj (Domov — Najdi&Izberi — Zamenjaj)

    Kopirajte formule brez premika povezave

  3. V pogovorno okno, ki se prikaže, vpišemo, kaj iščemo in s čim zamenjamo, ter v parametri (Opcije) ne pozabi pojasniti Obseg iskanja – Formule. Pritiskamo Zamenjaj vse (Zamenjaj vse).
  4. Kopirajte nastali obseg z deaktiviranimi formulami na pravo mesto:

    Kopirajte formule brez premika povezave

  5. Zamenjaj # on = nazaj z uporabo istega okna in vrnitvijo funkcionalnosti formulam.

3. način: Kopirajte prek Beležnice

Ta metoda je veliko hitrejša in lažja.

Pritisnite bližnjico na tipkovnici Ctrl+Ё ali gumb Prikaži formule tab Formula (Formule — Pokaži formule), da vklopite način preverjanja formul – namesto rezultatov bodo v celicah prikazane formule, po katerih so izračunani:

Kopirajte formule brez premika povezave

Kopirajte naš obseg D2:D8 in ga prilepite v standard notebook:

Kopirajte formule brez premika povezave

Zdaj izberite vse prilepljeno (Ctrl + A), znova kopirajte v odložišče (Ctrl + C) in prilepite na list na mesto, ki ga potrebujete:

Kopirajte formule brez premika povezave

Ostaja le še pritisk na gumb Prikaži formule (Pokaži formule)da vrnete Excel v običajni način.

Opomba: ta metoda včasih ne uspe pri kompleksnih tabelah z združenimi celicami, vendar v veliki večini primerov deluje dobro.

Metoda 4. Makro

Če morate pogosto izvajati takšno kopiranje formul brez premikanja referenc, potem je za to smiselno uporabiti makro. Pritisnite bližnjico na tipkovnici Alt + F11 ali gumb Visual Basic tab razvijalec (Razvijalec), vstavite nov modul skozi meni Vstavi – Modul  in tja kopirajte besedilo tega makra:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Izberi celice s formulami za kopiranje.", _ "Natančno kopiraj formule", Privzeto:=Selection.Address, Vrsta := 8) Če copyRange ni Nothing Then Exit Sub Set pasteRange = Application.InputBox("Zdaj izberite obseg lepljenja." & vbCrLf & vbCrLf & _ "Obseg mora biti po velikosti enak izvirnemu " & vbCrLf & _ " obsegu celic za kopiranje." , "Natančno kopiraj formule", _ Privzeto:=Selection.Address, Vrsta:=8) Če je pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Kopiraj in prilepi obsege različnih velikosti!", vbExclamation, "Napaka kopiranja " Exit Sub End If If pasteRange Is Nothing Then Exit Sub Else pasteRange.Formula = copyRange.Formula End If End Sub

Gumb lahko uporabite za zagon makra. Makri tab razvijalec (Razvijalec — Makri) ali bližnjico na tipkovnici Alt + F8. Po zagonu makra vas bo prosil, da izberete obseg z izvirnimi formulami in obsegom vstavljanja, ter bo samodejno kopiral formule:

Kopirajte formule brez premika povezave

  • Priročen pregled formul in rezultatov hkrati
  • Zakaj je v Excelovih formulah potreben referenčni slog R1C1
  • Kako hitro najti vse celice s formulami
  • Orodje za kopiranje natančnih formul iz dodatka PLEX

 

Pustite Odgovori