Lær hvordan du kopierer formel til flere celler, ned i en kolonne, til ikke-tilstøtende celler, kopierer formler med absolutte eller blandede cellereferanser, etc.
Kopiering av formler er en av de vanligste og enkleste oppgavene du gjør i et typisk regneark som hovedsakelig er avhengig av formler. I stedet for å skrive den samme formelen om og om igjen i Excel, kan du enkelt kopiere og lime inn en formel fra én celle til flere celler.
Etter å ha skrevet en formel i Excel, kan du bruke kommandoene kopier og lim inn til flere celler, flere ikke-tilstøtende celler eller hele kolonner. Hvis du ikke gjør det riktig, vil du ende opp med den forferdelige # REF- og /DIV0-feilen. I denne artikkelen viser vi deg de forskjellige metodene du kan bruke til å kopiere formler i Excel.
Hvordan kopiere og lime inn formler i Excel
Microsoft Excel tilbyr forskjellige måter å kopiere formler med relative cellereferanser, absolutte cellereferanser eller blandede referanser.
- Kopier formel fra en celle til en annen
- Kopier formel én celle til flere celler
- Kopier formel til hele kolonnen
- Kopier formel uten formatering
- Kopier formler til ikke-tilstøtende celler
- Kopier formler uten å endre cellereferanser
Hvordan kopiere en formel fra en celle til en annen i Excel
Noen ganger vil du kanskje kopiere en formel fra en celle til en annen i Excel for å unngå å skrive inn hele formelen på nytt og spare litt tid mens du gjør det.
La oss si at vi har denne tabellen:
Det er få metoder for å kopiere formel fra en celle til en annen.
Velg først cellen med formelen og høyreklikk, og velg "Kopier" i kontekstmenyen for å kopiere formelen. Eller du kan bruke "Kopier"-alternativet i "Utklippstavle"-delen av "Hjem"-fanen.
Men du kopierer også formler ved å trykke på hurtigtasten Ctrl + C. Dette er en mer effektiv og tidsbesparende metode.
Så går vi til cellen vi vil lime den inn, trykk på snarveien Ctrl + V for å lime inn formelen. Eller høyreklikk på cellen du vil lime inn og klikk på alternativene under "Lim inn alternativer": enten enkelt "Lim inn (P)" alternativ eller lim inn som "Formel (F)" alternativ.
Alternativt kan du også klikke på "Lim inn spesial" under de seks lim-ikonene for å åpne "Lim inn spesial"-dialogboksen. Her har du flere alternativer, inkludert de seks limalternativene fra kontekstmenyen. Velg "Alle" eller "Formler" under Lim inn-delen og klikk "OK".
Nå skal cellen med innlimt formel ha de samme formlene (som den i kopierte cellen), men med de forskjellige cellereferansene. Celleadressen justeres selv av excel for å matche radnummeret til den limte cellen.
Kopier formel fra én celle til flere celler
Den samme limoperasjonen fungerer på samme måte hvis vi velger flere celler eller en rekke celler.
Velg cellen med formelen og trykk Ctrl + C for å kopiere formelen. Deretter velger du alle cellene der du vil lime inn formelen og trykker Ctrl + V for å lime inn formelen eller bruk en av de ovennevnte limmetodene for å lime inn formelen (som vi gjorde for enkeltcellen).
Kopier formel til en hel kolonne eller rad
I Excel kan du raskt kopiere en formel til en hel kolonne eller en rad.
For å kopiere en formel til en kolonne eller en rad, skriv først inn en formel i en celle. Velg deretter formelcellen (D1), og hold markøren over en liten grønn firkant i nedre høyre hjørne av cellen. Når du holder musepekeren, vil markøren endres til et svart plusstegn (+), som kalles fyllhåndtaket. Klikk og hold det fyllhåndtaket, og dra det i hvilken som helst retning (kolonne eller rad) over cellene for å kopiere formelen.
Når du kopierer en formel til en rekke celler, vil cellereferansene til formelen automatisk justeres basert på den relative plasseringen av rader og kolonner, og formelen vil utføre beregninger basert på verdiene i disse cellereferansene (se nedenfor).
I eksemplet ovenfor, når formelen i D1 (=A1*B1)/2) kopieres til celle D2, endres den relative referansen basert på plasseringen (=A2*B2)/2) og så videre.
På samme måte kan du dra formelen inn i tilstøtende celler til venstre, til høyre eller oppover.
En annen måte å kopiere formelen til en hel kolonne på er ved å dobbeltklikke på fyllhåndtaket i stedet for å dra det. Når du dobbeltklikker på fyllhåndtaket, bruker det formelen umiddelbart så langt det er data til den tilstøtende cellen.
Kopier en formel til et område uten å kopiere formatering
Når du kopierer en formel til en rekke celler med fyllhåndtaket, kopierer den også kildecellens formatering, for eksempel skriftfarge eller bakgrunnsfarge, valuta, prosent, tid osv. (som vist nedenfor).
For å forhindre kopiering av celleformateringen, dra i fyllhåndtaket og klikk på "Autofyllalternativer" nederst til høyre i den siste cellen. Velg deretter "Fyll uten formatering" i rullegardinmenyen.
Resultatet:
Kopier en Excel-formel med kun tallformatering
Hvis du vil kopiere formelen med kun formelen og formateringen som prosentformat, desimaltegn osv.
Kopier formelen og velg alle cellene du vil kopiere formelen til. På "Hjem"-fanen klikker du på pilen under "Lim inn"-knappen på båndet. Deretter klikker du på "Formler og tallformatering"-ikonet (ikonet med % fx) fra rullegardinmenyen for å bare lime inn formelen og tallformateringen.
Dette alternativet kopierer bare formel- og tallformatering, men ignorerer alle andre celleformateringer som bakgrunnsfarge, skriftfarge osv.
Kopier en formel til ikke-tilstøtende/ikke-sammenhengende celler
Hvis du vil kopiere en formel til ikke-tilstøtende celler eller ikke-tilstøtende områder, kan du gjøre det ved hjelp av Ctrl nøkkel.
Velg cellen med formelen og trykk Ctrl + C å kopiere den. Deretter velger du ikke-tilstøtende celler/områder mens du trykker og holder nede Ctrl nøkkel. Trykk deretter Ctrl + V for å lime inn formelen og trykke Tast inn å fullføre.
Kopiere formler uten å endre cellereferanser i Excel
Når en formel kopieres til en annen celle, endrer Excel automatisk cellereferansene slik at de samsvarer med den nye plasseringen. Disse cellereferansene bruker den relative plasseringen til en celleadresse, og kalles derfor relativ cellereferanse (uten $). For eksempel, hvis du har formelen '=A1*B1' i celle C1, og du kopierer denne formelen til celle C2, vil formelen endres til '=A2*B2'. Alle metodene vi diskuterte ovenfor bruker relative referanser.
Når du kopierer en formel med relative cellereferanser, endrer den automatisk referanser slik at formelen refererer til de tilsvarende radene og kolonnene. Hvis du bruker absolutte referanser i en formel, blir den samme formelen kopiert uten å endre cellereferansene.
Når du setter et dollartegn ($) foran kolonnebokstaven og radnummeret til en celle (For eksempel $A$1), gjør det cellen til en absolutt celle. Uansett hvor du kopierer formelen som inneholder den absolutte cellereferansen, vil formelen aldri gjøre det. Men hvis du har relativ eller blandet cellereferanse i en formel, bruk en av disse metodene nedenfor for å kopiere uten å endre cellereferanser.
Kopier formel med absolutt cellereferanse ved å bruke Copy-Paste-metoden
Noen ganger må du kanskje kopiere/bruke den nøyaktige formelen nedover i kolonnen, uten å endre cellereferansene. Hvis du vil kopiere eller flytte en eksakt formel med absolutt referanse, gjør du dette:
Velg først cellen med formelen du vil kopiere. Klikk deretter på formellinjen, velg formelen med musen og trykk Ctrl + C å kopiere den. Hvis du vil flytte formelen, trykk Ctrl + X å kutte den. Deretter trykker du på Esc tasten for å forlate formellinjen.
Alternativt kan du velge cellen med formelen og trykke F2 tasten eller dobbeltklikk på cellen. Dette vil sette den valgte cellen i redigeringsmodus. Velg deretter formelen i cellen og trykk Ctrl + C for å kopiere formelen i cellen som tekst.
Velg deretter destinasjonscellen og trykk Ctrl + V for å lime inn formelen.
Nå blir den eksakte formelen kopiert til målcellen uten cellereferanseendringer.
Kopier formler med absolutte eller blandede cellereferanser
Hvis du vil flytte eller kopiere eksakte formler uten å endre cellereferanser, bør du endre celle-relative referanser til absolutte referanser. Hvis du for eksempel legger til ($)-tegn til relativ cellereferanse (B1), blir det en absolutt referanse ($B$1), slik at det forblir statisk uansett hvor formelen kopieres eller flyttes.
Men noen ganger må du kanskje bruke blandede cellereferanser ($B1 eller B$1) ved å legge til et dollartegn ($) foran kolonnebokstaven eller radnummeret for å låse enten en rad eller en kolonne på plass.
La oss forklare med et eksempel. Anta at du har denne tabellen som beregner den månedlige besparelsen ved å trekke husleie (B9) fra inntektene (i kolonne B) hver måned.
I eksemplet nedenfor bruker formelen en absolutt cellereferanse ($B$9) for å låse leiebeløpet til celle B9, og en relativ cellereferanse til celle B2 fordi den må justeres for hver rad for å matche hver måned. B9 er gjort til absolutt cellereferanse ($B$9) fordi du vil trekke fra det samme leiebeløpet fra hver måneds inntekt.
La oss si at du vil flytte saldoene fra kolonne C til kolonne E. Hvis du kopierer formelen (ved vanlig kopier/lim-metode) fra celle C2 (=B2-$B$9) vil den endres til =D2-$B$9 når den limes inn i celle E2, gjør beregningene dine helt feil!
I så fall endrer du den relative cellereferansen (B2) til en blandet cellereferanse ($B2) ved å legge til "$"-tegnet foran kolonnebokstaven til formelen som er angitt i celle C2.
Og nå, hvis du kopierer eller flytter formelen fra celle C2 til E2, eller en hvilken som helst annen celle, og bruker formelen nedover i kolonnen, vil kolonnereferansen forbli den samme mens radnummeret vil bli justert for hver celle.
Kopier Lim inn Excel-formler uten å endre referanser ved å bruke Notisblokk
Du kan se hver formel i Excel-regnearket ditt ved å bruke Vis formelalternativer. For å gjøre det, gå til "Formler"-fanen og velg "Vis formler".
Alternativt kan du gå inn i formelvisningsmodus ved å trykke på Ctrl + ` snarvei, som viser hver formel i regnearket ditt. Du kan finne gravaksenttasten (`) øverst til venstre på tastaturet på raden med talltastene (under ESC tasten og før nummer 1-tasten).
Velg alle cellene med formlene du vil kopiere og trykk Ctrl + C å kopiere dem, eller Ctrl + X å kutte dem. Åpne deretter Notisblokk og trykk Ctrl + V for å lime inn formlene i notisblokken.
Deretter velger du formelen og kopierer(Ctrl + C) den fra notisblokken, og lim den inn(Ctrl + V) i cellen der du vil ha den nøyaktige formelen kopiert. Du kan kopiere og lime dem inn én etter én eller alle samtidig.
Etter å ha limt inn formlene slår du av formelvisningsmodus ved å trykke Ctrl + ` eller igjen gå til "Formler" -> "Vis formler".
Kopier de eksakte formlene ved å bruke Excels Finn og erstatt
Hvis du vil kopiere en rekke eksakte formler, kan du også bruke Excels Finn og erstatt-verktøy for å gjøre det.
Velg alle cellene som har formlene du vil kopiere. Gå deretter til "Hjem"-fanen, klikk på "Finn og velg" i redigeringsgruppen, og velg alternativet "Erstatt" eller trykk ganske enkelt Ctrl + H for å åpne dialogboksen Finn og erstatt.
I dialogboksen Finn og erstatt skriver du inn likhetstegnet (=) i feltet "Finn hva". I 'Erstatt med'-feltet skriver du inn et symbol eller tegn som ikke allerede er en del av formlene dine, som #, eller , osv. Klikk deretter på 'Erstatt alle'-knappen.
Du vil få en meldingsboks som sier "Vi har gjort 6 erstatninger" (fordi vi valgte 6 celler med formler). Klikk deretter "OK" og "Lukk" for å lukke begge dialogboksene. Dette erstatter alle lik (=)-tegn med hash-tegn (#), og gjør formler om til tekststrenger. Nå vil ikke cellereferansene til formlene endres når de kopieres.
Nå kan du velge disse cellene, trykk Ctrl + C for å kopiere dem, og lime dem inn i målceller med Ctrl + V.
Til slutt må du endre (#)-tegnene tilbake til (=)-tegn. For å gjøre det, velg begge områdene (originalt og kopiert område) og trykk Ctrl + H for å åpne dialogboksen Finn og erstatt. Denne gangen skriver du inn hash-tegnet (#) i "Finn hva"-feltet, og lik (=)-tegnet i "Erstatt med"-feltet, og klikker på "Erstatt alle"-knappen. Klikk "Lukk" for å lukke dialogboksen.
Nå konverteres tekststrengene tilbake til formlene, og du vil få dette resultatet:
Ferdig!