vsebina
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.
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:
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
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:
- Izberite obseg s formulami (v našem primeru D2:D8)
- klik Ctrl + H na tipkovnici ali na zavihku Domov – Najdi in izberi – Zamenjaj (Domov — Najdi&Izberi — Zamenjaj)
- 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).
- Kopirajte nastali obseg z deaktiviranimi formulami na pravo mesto:
- 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 naš obseg D2:D8 in ga prilepite v standard notebook:
Zdaj izberite vse prilepljeno (Ctrl + A), znova kopirajte v odložišče (Ctrl + C) in prilepite na list na mesto, ki ga potrebujete:
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:
- 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