Kopier formler uten koblingsskift

Problem

Tenk deg at vi har en enkel tabell som denne, der beløpene beregnes for hver måned i to byer, og så blir summen konvertert til euro etter kursen fra den gule cellen J2.

Problemet er at hvis du kopierer området D2:D8 med formler et annet sted på arket, vil Microsoft Excel automatisk korrigere koblingene i disse formlene, flytte dem til et nytt sted og slutte å telle:

Oppgave: Kopier området med formler slik at formlene ikke endres og forblir de samme, og beholder beregningsresultatene.

Metode 1. Absolutte lenker

Metode 2: Deaktiver formler midlertidig

For å forhindre at formler endres ved kopiering, må du (midlertidig) sørge for at Excel slutter å behandle dem som formler. Dette kan gjøres ved å erstatte likhetstegnet (=) med et hvilket som helst annet tegn som vanligvis ikke finnes i formler, for eksempel et hash-tegn (#) eller et par og-tegn (&&) for kopieringstid. For dette:

1. Velg området med formler (i vårt eksempel D2:D8)
2. Klikk Ctrl + H på tastaturet eller på en fane Hjem – Finn og velg – Erstatt (Hjem — Finn og velg — Erstatt)

   

3. I dialogboksen som kommer opp skriver du inn hva vi ser etter og hva vi erstatter med, og inn parametere (Alternativer) ikke glem å avklare Søkeomfang – formler. Vi trykker Bytt ut alle (Erstatt alle).
4. Kopier det resulterende området med deaktiverte formler til rett sted:

   

5. Erstatt # on = tilbake med samme vindu, og returnerer funksjonalitet til formler.

Metode 3: Kopier via Notisblokk

Denne metoden er mye raskere og enklere.

Trykk på hurtigtasten Ctrl+Ё eller knappen Vis formler tab formel (Formler – Vis formler), for å slå på formelsjekkmodus – i stedet for resultatene vil cellene vise formlene som de beregnes etter:

Kopier vårt utvalg D2:D8 og lim det inn i standarden bærbare:

Velg nå alt som er limt inn (Ctrl + A), kopier det til utklippstavlen igjen (Ctrl + C) og lim det inn på arket på stedet du trenger:

Det gjenstår bare å trykke på knappen Vis formler (Vis formler)for å returnere Excel til normal modus.

Merk: denne metoden mislykkes noen ganger på komplekse tabeller med sammenslåtte celler, men i de aller fleste tilfeller fungerer den fint.

Metode 4. Makro

Hvis du ofte må gjøre slik kopiering av formler uten å skifte referanser, så er det fornuftig å bruke en makro til dette. Trykk hurtigtast Alt + F11 eller knappen Visual Basic tab utvikler (Utvikler), sett inn en ny modul gjennom menyen Sett inn – modul  og kopier teksten til denne makroen dit:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range Ved feil Gjenoppta Neste Set copyRange = Application.InputBox("Velg celler med formler som skal kopieres.", _ "Kopier formler nøyaktig", Default:=Selection.Address, Type := 8) If copyRange Is Nothing Then Exit Sub Set pasteRange = Application.InputBox("Velg nå limområdet." & vbCrLf & vbCrLf & _ "Rekkevidden må ha samme størrelse som det opprinnelige celleområdet " & vbCrLf & _ " å kopiere." , "Kopier formler nøyaktig", _ Default:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Then MsgBox "Kopier og lim inn områder varierer i størrelse!", vbUtrop, "Copy error " Exit Sub End If If pasteRange Is Nothing Then Exit Sub Else pasteRange.Formula = copyRange.Formula End If End Sub

Du kan bruke knappen til å kjøre makroen. Makroer tab utvikler (Utvikler – Makroer) eller hurtigtast Alt + F8. Etter å ha kjørt makroen, vil den be deg om å velge området med de originale formlene og innsettingsområdet og kopiere formlene automatisk:

• Praktisk visning av formler og resultater på samme tid
• Hvorfor R1C1-referansestil er nødvendig i Excel-formler
• Slik finner du raskt alle celler med formler
• Verktøy for å kopiere eksakte formler fra PLEX-tillegget