Lær, hvordan du kopierer formler til flere celler, ned i en kolonne, til ikke-tilstødende celler, kopierer formler med absolutte eller blandede cellereferencer osv.
Kopiering af formler er en af de mest almindelige og nemmeste opgaver, du udfører i et typisk regneark, der hovedsageligt er afhængig af formler. I stedet for at skrive den samme formel igen og igen i Excel, kan du bare nemt kopiere og indsætte en formel fra én celle til flere celler.
Efter at have skrevet en formel i Excel, kan du bruge kopierings- og indsæt-kommandoer til flere celler, flere ikke-tilstødende celler eller hele kolonner. Hvis du ikke gør det rigtigt, ender du med den forfærdelige # REF og /DIV0 fejl. I denne artikel viser vi dig de forskellige metoder, du kan bruge til at kopiere formler i Excel.
Sådan kopieres og indsættes formler i Excel
Microsoft Excel giver forskellige måder at kopiere formler med relative cellereferencer, absolutte cellereferencer eller blandede referencer.
- Kopier formel fra en celle til en anden
- Kopier formel en celle til flere celler
- Kopiering af formel til hele kolonnen
- Kopiering af formel uden formatering
- Kopier formler til ikke-tilstødende celler
- Kopier formler uden at ændre cellereferencer
Sådan kopieres en formel fra en celle til en anden i Excel
Nogle gange vil du måske kopiere en formel fra en celle til en anden i Excel for at undgå at genindtaste hele formlen igen og spare lidt tid, mens du gør det.
Lad os sige, at vi har denne tabel:
Der er få metoder til at kopiere formel fra en celle til en anden.
Vælg først cellen med formlen og højreklik, og vælg 'Kopier' i kontekstmenuen for at kopiere formlen. Eller du kan bruge 'Kopier'-indstillingen i 'Udklipsholder'-sektionen på fanen 'Hjem'.
Men du kopierer også formler ved blot at trykke på tastaturgenvejen Ctrl + C. Dette er en mere effektiv og tidsbesparende metode.
Så går vi til den celle, vi vil indsætte den ved at trykke på genvejen Ctrl + V for at indsætte formlen. Eller højreklik på den celle, du vil indsætte, og klik på indstillingerne under 'Indsæt indstillinger': enten simpel 'Indsæt (P)' mulighed eller indsæt som 'Formel (F)' mulighed.
Alternativt kan du også klikke på 'Indsæt speciel' under de seks indsæt-ikoner for at åbne dialogboksen 'Indsæt speciel'. Her har du flere muligheder, herunder de seks indsæt-indstillinger fra kontekstmenuen. Vælg 'Alle' eller 'Formler' under Indsæt-sektionen, og klik på 'OK'.
Nu skal cellen med indsat formel have de samme formler (som den i den kopierede celle), men med de forskellige cellereferencer. Celleadressen justeres selv af excel for at matche rækkenummeret i den indsatte celle.
Kopier formel fra én celle til flere celler
Den samme indsætningsoperation fungerer på samme måde, hvis vi vælger flere celler eller en række celler.
Vælg cellen med formlen og tryk på Ctrl + C at kopiere formlen. Vælg derefter alle de celler, hvor du vil indsætte formlen, og tryk på Ctrl + V for at indsætte formlen eller bruge en af ovenstående indsætmetoder til at indsætte formlen (som vi gjorde for den enkelte celle).
Kopier formel til en hel kolonne eller række
I Excel kan du hurtigt kopiere en formel til en hel kolonne eller en række.
For at kopiere en formel til en kolonne eller en række skal du først indtaste en formel i en celle. Vælg derefter formelcellen (D1), og hold markøren over en lille grøn firkant i nederste højre hjørne af cellen. Når du svæver, ændres markøren til et sort plustegn (+), som kaldes udfyldningshåndtaget. Klik og hold fyldhåndtaget nede, og træk det i en hvilken som helst retning (kolonne eller række) over cellerne for at kopiere formlen.
Når du kopierer en formel til en række celler, justeres cellereferencerne for formlen automatisk baseret på den relative placering af rækker og kolonner, og formlen udfører beregninger baseret på værdierne i disse cellereferencer (se nedenfor).
I ovenstående eksempel, når formlen i D1 (=A1*B1)/2) kopieres til celle D2, ændres den relative reference baseret på dens placering (=A2*B2)/2) og så videre.
På samme måde kan du trække formlen ind i tilstødende celler til venstre, til højre eller opad.
En anden måde at kopiere formlen til en hel kolonne på er ved at dobbeltklikke på udfyldningshåndtaget i stedet for at trække det. Når du dobbeltklikker på udfyldningshåndtaget, anvender det straks formlen, så vidt der er nogen data på den tilstødende celle.
Kopier en formel til et område uden at kopiere formatering
Når du kopierer en formel til en række celler med udfyldningshåndtaget, kopierer den også kildecellens formatering, såsom skriftfarve eller baggrundsfarve, valuta, procent, tid osv. (som vist nedenfor).
For at forhindre kopiering af celleformateringen skal du trække udfyldningshåndtaget og klikke på 'Auto Fill Options' i nederste højre hjørne af den sidste celle. Vælg derefter 'Fyld uden formatering' i rullemenuen.
Resultatet:
Kopier en Excel-formel med kun talformatering
Hvis du ønsker at kopiere formlen med kun formlen og formateringen såsom procentformat, decimaler osv.
Kopier formlen, og vælg alle de celler, som du vil kopiere formlen til. På fanen "Hjem" skal du klikke på pilen under knappen "Sæt ind" på båndet. Klik derefter på ikonet 'Formler og talformatering' (ikonet med % fx) fra rullemenuen for kun at indsætte formlen og talformateringen.
Denne indstilling kopierer kun formel- og talformatering, men ignorerer alle andre celleformateringer som baggrundsfarve, skriftfarve osv.
Kopier en formel til ikke-tilstødende/ikke-sammenhængende celler
Hvis du vil kopiere en formel til ikke-tilstødende celler eller ikke-tilstødende områder, kan du gøre det ved hjælp af Ctrl nøgle.
Vælg cellen med formlen og tryk på Ctrl + C at kopiere det. Vælg derefter ikke-tilstødende celler/områder, mens du trykker og holder nede Ctrl nøgle. Tryk derefter på Ctrl + V for at indsætte formlen og trykke Gå ind at færdiggøre.
Kopiering af formler uden at ændre cellereferencer i Excel
Når en formel kopieres til en anden celle, ændrer Excel automatisk cellereferencerne, så de matcher dens nye placering. Disse cellereferencer bruger den relative placering af en celleadresse, derfor kaldes de relativ cellereference (uden $). For eksempel, hvis du har formlen '=A1*B1' i celle C1, og du kopierer denne formel til celle C2, ændres formlen til '=A2*B2'. Alle de metoder, vi diskuterede ovenfor, bruger relative referencer.
Når du kopierer en formel med relative cellereferencer, ændrer den automatisk referencer, så formlen refererer til de tilsvarende rækker og kolonner. Hvis du bruger absolutte referencer i en formel, bliver den samme formel kopieret uden at ændre cellereferencerne.
Når du sætter et dollartegn ($) foran kolonnebogstavet og rækkenummeret i en celle (For eksempel $A$1), forvandler det cellen til en absolut celle. Nu uanset hvor du kopierer formlen, der indeholder den absolutte cellereference, vil formlen aldrig. Men hvis du har relativ eller blandet cellereference i en formel, skal du bruge en af disse følgende metoder til at kopiere uden at ændre cellereferencer.
Kopier formel med absolut cellereference ved hjælp af Copy-Paste-metoden
Af og til kan det være nødvendigt at kopiere/anvende den nøjagtige formel ned i kolonnen uden at ændre cellereferencerne. Hvis du vil kopiere eller flytte en nøjagtig formel med absolut reference, skal du gøre dette:
Først skal du vælge cellen med den formel, du vil kopiere. Klik derefter på formellinjen, vælg formlen med musen, og tryk på Ctrl + C at kopiere det. Hvis du vil flytte formlen, skal du trykke på Ctrl + X at skære den. Tryk derefter på Esc tasten for at forlade formellinjen.
Alternativt kan du vælge cellen med formlen og trykke F2 tast eller dobbeltklik på cellen. Dette vil sætte den valgte celle i redigeringstilstand. Vælg derefter formlen i cellen og tryk Ctrl + C for at kopiere formlen i cellen som tekst.
Vælg derefter destinationscellen og tryk på Ctrl + V for at indsætte formlen.
Nu bliver den nøjagtige formel kopieret til destinationscellen uden ændringer i cellereferencen.
Kopier formler med absolutte eller blandede cellereferencer
Hvis du gerne vil flytte eller kopiere nøjagtige formler uden at ændre cellereferencer, bør du ændre celle-referencer til absolutte referencer. Tilføjelse af ($)-tegn til relativ cellereference (B1) gør det til en absolut reference ($B$1), så det forbliver statisk, uanset hvor formlen kopieres eller flyttes.
Men nogle gange skal du muligvis bruge blandede cellereferencer ($B1 eller B$1) ved at tilføje et dollartegn ($) foran kolonnebogstavet eller rækkenummeret for at låse enten en række eller en kolonne på plads.
Lad os forklare med et eksempel. Antag, at du har denne tabel, der beregner den månedlige opsparing ved at trække husleje (B9) fra indtjeningen (i kolonne B) hver måned.
I eksemplet nedenfor bruger formlen en absolut cellereference ($B$9) til at låse lejebeløbet til celle B9 og en relativ cellereference til celle B2, fordi det skal justeres for hver række for at matche hver måned. B9 er lavet til absolut cellereference ($B$9), fordi du vil trække det samme lejebeløb fra hver måneds indtjening.
Lad os sige, at du vil flytte saldi fra kolonne C til kolonne E. Hvis du kopierer formlen (ved sædvanlig kopi/indsæt-metode) fra celle C2 (=B2-$B$9), ændres den til =D2-$B$9, når den indsættes i celle E2, gør dine beregninger helt forkerte!
I så fald skal du ændre den relative cellereference (B2) til en blandet cellereference ($B2) ved at tilføje '$'-tegnet foran kolonnebogstavet i formlen, der er indtastet i celle C2.
Og nu, hvis du kopierer eller flytter formlen fra celle C2 til E2 eller en hvilken som helst anden celle og anvender formlen ned i kolonnen, forbliver kolonnereferencen den samme, mens rækkenummeret vil blive justeret for hver celle.
Kopier indsæt Excel-formler uden at ændre referencer ved hjælp af Notesblok
Du kan se alle formler i dit Excel-regneark ved at bruge Vis formelindstillinger. For at gøre det skal du gå til fanen 'Formler' og vælge 'Vis formler'.
Alternativt kan du gå ind i formelvisningstilstanden ved at trykke på Ctrl + ` genvej, som viser alle formler i dit regneark. Du kan finde gravtasten (`) i øverste venstre hjørne af dit tastatur på rækken med taltasterne (under ESC tasten og før nummer 1-tasten).
Marker alle cellerne med de formler, du vil kopiere, og tryk på Ctrl + C at kopiere dem, eller Ctrl + X at skære dem. Åbn derefter Notesblok og tryk Ctrl + V for at indsætte formlerne i notesblokken.
Vælg derefter formlen og kopier(Ctrl + C) det fra notesblokken, og indsæt det (Ctrl + V) i den celle, hvor du vil have den nøjagtige formel kopieret. Du kan kopiere og indsætte dem én efter én eller alle på én gang.
Når du har indsat formlerne, skal du deaktivere formelvisningstilstanden ved at trykke på Ctrl + ` eller igen gå til 'Formler' -> 'Vis formler'.
Kopier de nøjagtige formler ved hjælp af Excels Find og erstat
Hvis du vil kopiere en række eksakte formler, kan du også bruge Excels Find and Replace-værktøj til at gøre det.
Vælg alle de celler, der har de formler, du vil kopiere. Gå derefter til fanen 'Hjem', klik på 'Find & vælg' i redigeringsgruppen, og vælg indstillingen 'Erstat', eller tryk blot på Ctrl + H for at åbne dialogboksen Find og erstat.
Indtast lighedstegnet (=) i feltet 'Find hvad' i dialogboksen Find og erstat. I feltet 'Erstat med' skal du indtaste et symbol eller tegn, der ikke allerede er en del af dine formler, såsom #, eller , osv. Klik derefter på knappen 'Erstat alle'.
Du får en prompt beskedboks, der siger 'Vi lavede 6 udskiftninger' (fordi vi valgte 6 celler med formler). Klik derefter på 'OK' og 'Luk' for at lukke begge dialoger. Hvis du gør dette, erstattes alle lig med (=)-tegn med hash-tegn (#), og formler omdannes til tekststrenge. Nu ændres formlernes cellereferencer ikke, når de kopieres.
Nu kan du vælge disse celler, tryk på Ctrl + C at kopiere dem og indsætte dem i destinationsceller med Ctrl + V.
Til sidst skal du ændre (#) tegnene tilbage til (=) tegn. For at gøre det skal du vælge begge områder (oprindeligt og kopieret område) og trykke på Ctrl + H for at åbne dialogboksen Find og erstat. Denne gang skal du skrive hash-tegnet (#) i feltet 'Find hvad' og lig med (=)-tegnet i feltet 'Erstat med', og klik på knappen 'Erstat alle'. Klik på 'Luk' for at lukke dialogen.
Nu konverteres tekststrengene tilbage til formlerne, og du får dette resultat:
Færdig!