Naučite se kopirati formulo v več celic, navzdol po stolpcu, v nesosednje celice, kopirati formule z absolutnimi ali mešanimi referencami celic itd.
Kopiranje formul je eno najpogostejših in najpreprostejših nalog, ki jih opravljate v tipični preglednici, ki se opira predvsem na formule. Namesto da v Excel vedno znova vnašate isto formulo, lahko preprosto kopirate in prilepite formulo iz ene celice v več celic.
Ko napišete formulo v Excelu, lahko uporabite ukaze za kopiranje in lepljenje v več celic, več celic, ki niso sosednje, ali celotne stolpce. Če tega ne storite pravilno, boste na koncu imeli grozne napake # REF in /DIV0. V tem članku vam bomo pokazali različne metode, ki jih lahko uporabite za kopiranje formul v Excelu.
Kako kopirati in prilepiti formule v Excelu
Microsoft Excel ponuja različne načine za kopiranje formul z relativnimi sklici na celice, absolutnimi sklici na celice ali mešanimi sklici.
- Kopirajte formulo iz ene celice v drugo
- Kopirajte formulo ena celica v več celic
- Kopiranje formule v celoten stolpec
- Kopiranje formule brez oblikovanja
- Kopirajte formule v celice, ki niso sosednje
- Kopirajte formule brez spreminjanja referenc celic
Kako kopirati formulo iz ene celice v drugo v Excelu
Včasih boste morda želeli kopirati formulo iz ene celice v drugo v excelu, da se izognete ponovnemu vnašanju celotne formule in pri tem prihranite nekaj časa.
Recimo, da imamo to tabelo:
Obstaja nekaj načinov za kopiranje formule iz ene celice v drugo.
Najprej izberite celico s formulo in z desno tipko miške kliknite ter v kontekstnem meniju izberite »Kopiraj«, da kopirate formulo. Lahko pa uporabite možnost »Kopiraj« v razdelku »Odložišče« na zavihku »Domov«.
Toda formule kopirate tudi s preprostim pritiskom na bližnjico na tipkovnici Ctrl + C. To je učinkovitejša metoda, ki prihrani čas.
Nato gremo v celico, ki jo želimo prilepiti, pritisnemo bližnjico Ctrl + V da prilepite formulo. Ali pa z desno miškino tipko kliknite celico, ki jo želite prilepiti, in kliknite možnosti pod »Možnosti lepljenja«: bodisi preprosta možnost »Prilepi (P)« ali prilepi kot možnost »Formula (F)«.
Lahko pa tudi kliknete »Posebno lepljenje« pod šestimi ikonami lepljenja, da odprete pogovorno okno »Posebno lepljenje«. Tukaj imate več možnosti, vključno s šestimi možnostmi lepljenja iz kontekstnega menija. V razdelku Prilepi izberite »Vse« ali »Formule« in kliknite »V redu«.
Zdaj bi morala celica z prilepljeno formulo imeti enake formule (kot tista v kopirani celici), vendar z različnimi referencami celic. Excel sam prilagodi naslov celice, da se ujema s številko vrstice prilepljene celice.
Kopiraj formulo iz ene celice v več celic
Ista operacija lepljenja deluje enako, če izberemo več celic ali obseg celic.
Izberite celico s formulo in pritisnite Ctrl + C za kopiranje formule. Nato izberite vse celice, kamor želite prilepiti formulo, in pritisnite Ctrl + V da prilepite formulo ali uporabite eno od zgornjih metod lepljenja, da prilepite formulo (kot smo to storili za eno celico).
Kopirajte formulo v celoten stolpec ali vrstico
V Excelu lahko hitro kopirate formulo v celoten stolpec ali vrstico.
Če želite formulo kopirati v stolpec ali vrstico, najprej vnesite formulo v celico. Nato izberite celico formule (D1) in premaknite kazalec miške nad majhen zeleni kvadrat v spodnjem desnem kotu celice. Ko lebdite, se kazalec spremeni v črn znak plus (+), ki se imenuje Ročica za polnjenje. Kliknite in pridržite to ročico za polnjenje ter jo povlecite v katero koli smer (stolpec ali vrstica) čez celice, da kopirate formulo.
Ko kopirate formulo v obseg celic, se sklicevanja na celice formule samodejno prilagodijo na podlagi relativne lokacije vrstic in stolpcev, formula pa bo izvedla izračune na podlagi vrednosti v teh sklicih na celice (glejte spodaj).
V zgornjem primeru, ko je formula v D1 (=A1*B1)/2) kopirana v celico D2, se relativna referenca spremeni glede na njeno lokacijo (=A2*B2)/2) in tako naprej.
Na enak način lahko povlečete formulo v sosednje celice na levo, desno ali navzgor.
Drug način za kopiranje formule v celoten stolpec je, da dvokliknete ročico za polnjenje, namesto da jo povlečete. Ko dvokliknete ročico za polnjenje, takoj uporabi formulo, kolikor obstajajo podatki, v sosednjo celico.
Kopirajte formulo v obseg brez kopiranja oblikovanja
Ko kopirate formulo v obseg celic z ročico za polnjenje, kopira tudi oblikovanje izvorne celice, kot je barva pisave ali barva ozadja, valuta, odstotek, čas itd. (kot je prikazano spodaj).
Če želite preprečiti kopiranje oblikovanja celice, povlecite ročico za polnjenje in kliknite »Možnosti samodejnega polnjenja« v spodnjem desnem kotu zadnje celice. Nato v spustnem meniju izberite »Izpolni brez oblikovanja«.
rezultat:
Kopirajte Excelovo formulo samo z oblikovanjem številk
Če želite formulo kopirati samo s formulo in oblikovanjem, kot so odstotna oblika, decimalne točke itd.
Kopirajte formulo in izberite vse celice, v katere želite kopirati formulo. Na zavihku »Domov« kliknite puščico pod gumbom »Prilepi« na traku. Nato v spustnem meniju kliknite ikono »Formule in oblikovanje številk« (ikona z % fx), da prilepite samo formulo in oblikovanje številk.
Ta možnost kopira samo formulo in oblikovanje številk, vendar ignorira vse druge oblike celic, kot so barva ozadja, barva pisave itd.
Kopirajte formulo v celice, ki niso sosednje/nesosednje
Če želite kopirati formulo v nesosednje celice ali nesosednje obsege, lahko to storite s pomočjo Ctrl ključ.
Izberite celico s formulo in pritisnite Ctrl + C da ga kopiram. Nato izberite nesosednje celice/razsege, medtem ko pritiskate in držite Ctrl ključ. Nato pritisnite Ctrl + V da prilepite formulo in pritisnite Vnesite dokončati.
Kopiranje formul brez spreminjanja referenc celic v Excelu
Ko se formula kopira v drugo celico, Excel samodejno spremeni reference celic, da se ujemajo z njeno novo lokacijo. Te reference celic uporabljajo relativno lokacijo naslova celice, zato se imenujejo relativna referenca celice (brez $). Če imate na primer formulo „=A1*B1“ v celici C1 in to formulo kopirate v celico C2, se bo formula spremenila v „=A2*B2“. Vse metode, o katerih smo razpravljali zgoraj, uporabljajo relativne reference.
Ko kopirate formulo z relativnimi sklici na celice, samodejno spremeni reference, tako da se formula nanaša na ustrezne vrstice in stolpce. Če v formuli uporabite absolutne reference, se ista formula kopira brez spreminjanja sklicevanj na celice.
Ko postavite znak za dolar ($) pred črko stolpca in številko vrstice celice (na primer $A$1), spremeni celico v absolutno celico. Ne glede na to, kam kopirate formulo, ki vsebuje absolutno referenco celice, formula nikoli ne bo. Če pa imate v formuli relativno ali mešano sklicevanje na celico, uporabite katero koli od teh metod za kopiranje, ne da bi spremenili sklic na celico.
Kopirajte formulo z absolutno referenco celice z uporabo metode kopiraj-prilepi
Občasno boste morda morali kopirati/uporabiti natančno formulo navzdol po stolpcu, ne da bi spremenili sklic na celice. Če želite kopirati ali premakniti natančno formulo z absolutno referenco, naredite to:
Najprej izberite celico s formulo, ki jo želite kopirati. Nato kliknite na vrstico s formulo, izberite formulo z miško in pritisnite Ctrl + C da ga kopiram. Če želite premakniti formulo, pritisnite Ctrl + X da ga odrežem. Nato pritisnite na Esc tipko, da zapustite vrstico s formulo.
Lahko pa izberete celico s formulo in pritisnete F2 tipko ali dvokliknite celico. To bo izbrano celico postavilo v način za urejanje. Nato izberite formulo v celici in pritisnite Ctrl + C da kopirate formulo v celico kot besedilo.
Nato izberite ciljno celico in pritisnite Ctrl + V da prilepite formulo.
Zdaj se natančna formula kopira v ciljno celico brez sprememb sklicevanja na celico.
Kopirajte formule z absolutnimi ali mešanimi referencami celic
Če želite premakniti ali kopirati natančne formule, ne da bi spremenili sklice na celice, spremenite relativne reference celic v absolutne. Če na primer dodate znak ($) relativni sklici celice (B1), postane absolutna referenca ($B$1), tako da ostane statična, ne glede na to, kje je formula kopirana ali premaknjena.
Včasih pa boste morda morali uporabiti mešane reference celic ($B1 ali B$1), tako da dodate znak za dolar ($) pred črko stolpca ali številko vrstice, da zaklenete vrstico ali stolpec na mestu.
Naj razložimo s primerom. Recimo, da imate to tabelo, ki izračuna mesečne prihranke tako, da vsak mesec odštejete najemnino (B9) od zaslužka (v stolpcu B).
V spodnjem primeru formula uporablja absolutno sklicevanje na celico ($ B$9), da zaklene znesek najemnine v celico B9, in relativno sklic na celico v celico B2, ker jo je treba prilagoditi za vsako vrstico, da se ujema vsak mesec. B9 je absolutna referenca celice (9 B$), ker želite od zaslužka vsakega meseca odšteti enak znesek najemnine.
Recimo, da želite premakniti stanja iz stolpca C v stolpec E. Če kopirate formulo (na običajen način kopiraj/prilepi) iz celice C2 (=B2-$B$9), se bo ob lepljenju spremenila v =D2-$B$9 v celici E2, zaradi česar so vaši izračuni napačni!
V tem primeru spremenite relativno referenco celice (B2) v mešano referenco celice ($B2) tako, da dodate znak '$' pred črko stolpca formule, vnesene v celico C2.
In zdaj, če kopirate ali premaknete formulo iz celice C2 v celico E2 ali katero koli drugo celico in uporabite formulo navzdol po stolpcu, bo referenca stolpca ostala enaka, medtem ko bo številka vrstice prilagojena za vsako celico.
Kopiraj in prilepi Excelove formule brez spreminjanja referenc z beležnico
Vsako formulo si lahko ogledate v Excelovi preglednici z uporabo možnosti Pokaži formulo. Če želite to narediti, pojdite na zavihek »Formule« in izberite »Pokaži formule«.
Druga možnost je, da vstopite v način pogleda formule s pritiskom na Ctrl + ` bližnjico, ki prikaže vsako formulo na vašem delovnem listu. Tipko za velik naglas (`) najdete v zgornjem levem kotu tipkovnice v vrstici s številskimi tipkami (pod ESC in pred tipko številka 1).
Izberite vse celice s formulami, ki jih želite kopirati, in pritisnite Ctrl + C da jih kopirate, oz Ctrl + X da jih razrežem. Nato odprite Beležnico in pritisnite Ctrl + V da prilepite formule v beležnico.
Nato izberite formulo in kopirajte (Ctrl + C) ga iz beležnice in ga prilepite (Ctrl + V) v celici, kamor želite kopirati natančno formulo. Lahko jih kopirate in prilepite enega za drugim ali vse naenkrat.
Ko prilepite formule, izklopite način pogleda formule s pritiskom na Ctrl + ` ali znova pojdite na »Formule« -> »Pokaži formule«.
Kopirajte natančne formule z uporabo Excelovega orodja Find and Replace
Če želite kopirati vrsto formul Exact, lahko za to uporabite tudi Excelovo orodje Najdi in zamenjaj.
Izberite vse celice, ki vsebujejo formule, ki jih želite kopirati. Nato pojdite na zavihek »Domov«, kliknite »Najdi in izberi« v skupini za urejanje in izberite možnost »Zamenjaj« ali pa preprosto pritisnite Ctrl + H da odprete pogovorno okno Najdi in zamenjaj.
V pogovornem oknu Najdi in zamenjaj vnesite znak enakosti (=) v polje »Najdi kaj«. V polje »Zamenjaj z« vnesite simbol ali znak, ki še ni del vaših formul, na primer # ali , itd. Nato kliknite gumb »Zamenjaj vse«.
Prejeli boste okno s pozivnim sporočilom z napisom »Naredili smo 6 zamenjav« (ker smo izbrali 6 celic s formulami). Nato kliknite »V redu« in »Zapri«, da zaprete obe pogovorni okni. S tem zamenjate vse znake enako (=) z znaki hash (#) in pretvorite formule v besedilne nize. Zdaj se sklicevanja na celice formul ob kopiranju ne bodo spremenila.
Zdaj lahko izberete te celice, pritisnite Ctrl + C da jih kopirate in prilepite v ciljne celice z Ctrl + V.
Končno morate znake (#) spremeniti nazaj v znake (=). Če želite to narediti, izberite oba obsega (izvirni in kopirani obseg) in pritisnite Ctrl + H da odprete pogovorno okno Najdi in zamenjaj. Tokrat v polje »Najdi kaj« vnesite znak razpršitve (#), v polje »Zamenjaj z« pa znak enako (=) in kliknite gumb »Zamenjaj vse«. Kliknite »Zapri«, da zaprete pogovorno okno.
Zdaj so besedilni nizi pretvorjeni nazaj v formule in dobili boste ta rezultat:
Končano!