Uzziniet, kā kopēt formulu vairākās šūnās, kolonnā uz leju, šūnās, kas nav blakus, kopēt formulas ar absolūtām vai jauktām šūnu atsaucēm utt.
Formulu kopēšana ir viens no visizplatītākajiem un vienkāršākajiem uzdevumiem, ko veicat tipiskā izklājlapā, kas galvenokārt balstās uz formulām. Tā vietā, lai programmā Excel atkal un atkal rakstītu vienu un to pašu formulu, varat vienkārši kopēt un ielīmēt formulu no vienas šūnas vairākās šūnās.
Pēc formulas rakstīšanas programmā Excel varat izmantot kopēšanas un ielīmēšanas komandas vairākās šūnās, vairākās šūnās, kas nav blakus, vai veselas kolonnas. Ja nedarīsit to pareizi, jūs saņemsit šausmīgās kļūdas # REF un /DIV0. Šajā rakstā mēs parādīsim dažādas metodes, kuras varat izmantot, lai kopētu formulas programmā Excel.
Kā kopēt un ielīmēt formulas programmā Excel
Microsoft Excel nodrošina dažādus veidus, kā kopēt formulas ar relatīvām šūnu atsaucēm, absolūtām šūnu atsaucēm vai jauktām atsaucēm.
- Kopējiet formulu no vienas šūnas uz otru
- Kopējiet formulas vienu šūnu vairākās šūnās
- Notiek formulas kopēšana uz visu kolonnu
- Formulas kopēšana bez formatēšanas
- Kopējiet formulas uz šūnām, kas nav blakus
- Kopējiet formulas, nemainot šūnu atsauces
Kā kopēt formulu no vienas šūnas uz citu programmā Excel
Dažreiz jūs varat kopēt formulu no vienas Excel šūnas uz otru, lai izvairītos no visas formulas atkārtotas ievadīšanas un ietaupītu laiku.
Pieņemsim, ka mums ir šāda tabula:
Ir dažas metodes, kā kopēt formulu no vienas šūnas uz otru.
Vispirms atlasiet šūnu ar formulu un ar peles labo pogu noklikšķiniet, un konteksta izvēlnē atlasiet “Kopēt”, lai kopētu formulu. Vai arī varat izmantot opciju "Kopēt" cilnes "Sākums" sadaļā "Starpliktuve".
Bet jūs arī varat kopēt formulas, vienkārši nospiežot īsinājumtaustiņu Ctrl+C. Šī ir efektīvāka un laiku taupošāka metode.
Pēc tam mēs ejam uz šūnu, kuru vēlamies to ielīmēt, nospiediet īsceļu Ctrl+V lai ielīmētu formulu. Vai ar peles labo pogu noklikšķiniet uz šūnas, kuru vēlaties ielīmēt, un noklikšķiniet uz opcijām sadaļā “Ielīmēšanas opcijas”: vai nu vienkārša opcija “Ielīmēt (P)”, vai arī ielīmēt kā opciju “Formula (F)”.
Varat arī noklikšķināt uz “Īpašā ielīmēšana” zem sešām ielīmēšanas ikonām, lai atvērtu dialoglodziņu “Īpašā ielīmēšana”. Šeit jums ir vairākas iespējas, tostarp sešas ielīmēšanas opcijas no konteksta izvēlnes. Sadaļā Ielīmēt atlasiet “Visi” vai “Formulas” un noklikšķiniet uz “Labi”.
Tagad šūnai ar ielīmētu formulu ir jābūt tādām pašām formulām (kā tai, kas atrodas kopētajā šūnā), bet ar dažādām šūnu atsaucēm. Programma Excel pati pielāgo šūnas adresi, lai tā atbilstu ielīmētās šūnas rindas numuram.
Kopējiet formulu no vienas šūnas uz vairākām šūnām
Tā pati ielīmēšanas darbība darbojas tāpat, ja atlasām vairākas šūnas vai šūnu diapazonu.
Atlasiet šūnu ar formulu un nospiediet Ctrl+C lai kopētu formulu. Pēc tam atlasiet visas šūnas, kurās vēlaties ielīmēt formulu, un nospiediet Ctrl+V lai ielīmētu formulu vai izmantojiet kādu no iepriekš minētajām ielīmēšanas metodēm, lai ielīmētu formulu (kā mēs to darījām ar vienu šūnu).
Kopējiet formulu uz visu kolonnu vai rindu
Programmā Excel varat ātri kopēt formulu uz visu kolonnu vai rindu.
Lai kopētu formulu kolonnā vai rindā, vispirms šūnā ievadiet formulu. Pēc tam atlasiet formulas šūnu (D1) un virziet kursoru virs maza zaļa kvadrāta šūnas apakšējā labajā stūrī. Virzot kursoru, kursors mainīsies uz melnu pluszīmi (+), ko sauc par aizpildīšanas rokturi. Noklikšķiniet uz šī aizpildīšanas roktura un turiet to un velciet to jebkurā vēlamajā virzienā (kolonna vai rinda) virs šūnām, lai kopētu formulu.
Kopējot formulu šūnu diapazonā, formulas šūnu atsauces tiks automātiski pielāgotas, pamatojoties uz rindu un kolonnu relatīvo atrašanās vietu, un formula veiks aprēķinus, pamatojoties uz vērtībām šajās šūnu atsaucēs (skatiet tālāk).
Iepriekš minētajā piemērā, kad formula D1 (=A1*B1)/2) tiek kopēta šūnā D2, relatīvā atsauce mainās atkarībā no tās atrašanās vietas (=A2*B2)/2) un tā tālāk.
Tādā pašā veidā jūs varat vilkt formulu blakus esošajās šūnās pa kreisi, pa labi vai uz augšu.
Vēl viens veids, kā kopēt formulu uz visu kolonnu, ir dubultklikšķis uz aizpildīšanas roktura, nevis velkot to. Veicot dubultklikšķi uz aizpildīšanas roktura, tā nekavējoties piemēro formulu blakus esošajai šūnai, ciktāl ir kādi dati.
Kopējiet formulu diapazonā, nekopējot formatējumu
Kopējot formulu šūnu diapazonā ar aizpildīšanas turi, tā kopē arī avota šūnas formatējumu, piemēram, fonta krāsu vai fona krāsu, valūtu, procentus, laiku utt. (kā parādīts tālāk).
Lai novērstu šūnas formatējuma kopēšanu, velciet aizpildīšanas turi un pēdējās šūnas apakšējā labajā stūrī noklikšķiniet uz “Automātiskās aizpildīšanas opcijas”. Pēc tam nolaižamajā izvēlnē atlasiet “Aizpildīt bez formatēšanas”.
Rezultāts:
Kopējiet Excel formulu ar tikai skaitļu formatējumu
Ja vēlaties kopēt formulu tikai ar formulu un formatējumu, piemēram, procentu formātu, decimālpunktiem utt.
Kopējiet formulu un atlasiet visas šūnas, uz kurām vēlaties kopēt formulu. Cilnē Sākums noklikšķiniet uz bultiņas zem lentes pogas Ielīmēt. Pēc tam nolaižamajā izvēlnē noklikšķiniet uz ikonas Formulas un skaitļu formatēšana (ikona ar % fx), lai ielīmētu tikai formulu un skaitļu formatējumu.
Šī opcija kopē tikai formulu un skaitļu formatējumu, bet ignorē visus pārējos šūnu formatējumus, piemēram, fona krāsu, fonta krāsu utt.
Kopējiet formulu uz šūnām, kas nav blakus/nepieguļošas
Ja vēlaties kopēt formulu uz šūnām, kas nav blakus, vai diapazonos, kas nav blakus, varat to izdarīt, izmantojot Ctrl taustiņu.
Atlasiet šūnu ar formulu un nospiediet Ctrl+C lai to kopētu. Pēc tam atlasiet neblakus esošās šūnas/diapazonus, vienlaikus nospiežot un turot Ctrl taustiņu. Pēc tam nospiediet Ctrl+V lai ielīmētu formulu un trāpītu Ievadiet lai pabeigtu.
Formulu kopēšana, nemainot šūnu atsauces programmā Excel
Kad formula tiek kopēta citā šūnā, programma Excel automātiski maina šūnu atsauces, lai tās atbilstu tās jaunajai atrašanās vietai. Šīs šūnu atsauces izmanto šūnas adreses relatīvo atrašanās vietu, tāpēc tās sauc par relatīvo šūnas atsauci (bez $). Piemēram, ja šūnā C1 ir formula “=A1*B1” un jūs kopējot šo formulu šūnā C2, formula tiks mainīta uz “=A2*B2”. Visas metodes, par kurām mēs runājām iepriekš, izmanto relatīvas atsauces.
Kopējot formulu ar relatīvām šūnu atsaucēm, tā automātiski maina atsauces, lai formula atsauktos uz attiecīgajām rindām un kolonnām. Ja formulā izmantojat absolūtās atsauces, tā pati formula tiek kopēta, nemainot šūnu atsauces.
Ievietojot dolāra zīmi ($) pirms kolonnas burta un šūnas rindas numura (piemēram, $A$1), šūna tiek pārvērsta par absolūtu šūnu. Tagad neatkarīgi no tā, kur kopējat formulu, kurā ir absolūtā šūnas atsauce, formula nekad netiks izveidota. Bet, ja formulā ir relatīva vai jaukta šūnu atsauce, izmantojiet kādu no šīm metodēm, lai kopētu, nemainot šūnu atsauces.
Kopējiet formulu ar absolūto šūnu atsauci, izmantojot kopēšanas-ielīmēšanas metodi
Reizēm jums var būt nepieciešams kopēt/piemērot precīzu formulu kolonnā, nemainot šūnu atsauces. Ja vēlaties kopēt vai pārvietot precīzu formulu ar absolūtu atsauci, rīkojieties šādi:
Vispirms atlasiet šūnu ar formulu, kuru vēlaties kopēt. Pēc tam noklikšķiniet uz formulas joslas, atlasiet formulu, izmantojot peli, un nospiediet Ctrl+C lai to kopētu. Ja vēlaties pārvietot formulu, nospiediet Ctrl+X lai to sagrieztu. Tālāk nospiediet Esc taustiņu, lai izietu no formulas joslas.
Vai arī atlasiet šūnu ar formulu un nospiediet F2 taustiņu vai veiciet dubultklikšķi uz šūnas. Tādējādi atlasītā šūna tiks pārslēgta rediģēšanas režīmā. Pēc tam šūnā atlasiet formulu un nospiediet Ctrl+C lai kopētu formulu šūnā kā tekstu.
Pēc tam atlasiet mērķa šūnu un nospiediet Ctrl+V lai ielīmētu formulu.
Tagad precīza formula tiek kopēta mērķa šūnā bez šūnu atsauces izmaiņām.
Kopējiet formulas ar absolūtām vai jauktām šūnu atsaucēm
Ja vēlaties pārvietot vai kopēt precīzas formulas, nemainot šūnu atsauces, nomainiet šūnu relatīvās atsauces uz absolūtām atsaucēm. Piemēram, pievienojot zīmi ($) relatīvajai šūnas atsaucei (B1), tā kļūst par absolūtu atsauci ($B$1), tāpēc tā paliek statiska neatkarīgi no formulas kopēšanas vai pārvietošanas vietas.
Bet dažreiz jums var būt nepieciešams izmantot jauktas šūnu atsauces ($B1 vai B$1), pievienojot dolāra ($) zīmi pirms kolonnas burta vai rindas numura, lai bloķētu rindu vai kolonnu vietā.
Paskaidrosim ar piemēru. Pieņemsim, ka jums ir šī tabula, kas aprēķina ikmēneša ietaupījumus, katru mēnesi no ienākumiem (B ailē) atņemot īres maksu (B9).
Tālāk esošajā piemērā formula izmanto absolūtu šūnas atsauci (9 ASV dolāri), lai īres summu bloķētu šūnā B9, un relatīvo šūnas atsauci uz šūnu B2, jo tā ir jāpielāgo katrai rindai, lai tā atbilstu katru mēnesi. B9 ir absolūta šūnas atsauce (9 USD), jo vēlaties atņemt to pašu īres summu no katra mēneša ieņēmumiem.
Pieņemsim, ka vēlaties pārvietot atlikumus no kolonnas C uz kolonnu E. Ja nokopējat formulu (ar parasto kopēšanas/ielīmēšanas metodi) no šūnas C2 (=B2-$B$9), ielīmējot, tā mainīsies uz =D2-$B$9. šūnā E2, padarot visus aprēķinus nepareizi!
Tādā gadījumā mainiet relatīvo šūnas atsauci (B2) uz jauktas šūnas atsauci ($B2), pievienojot zīmi “$” šūnā C2 ievadītās formulas kolonnas burta priekšā.
Un tagad, ja kopējat vai pārvietojat formulu no šūnas C2 uz E2 vai jebkuru citu šūnu un lietojat formulu kolonnā uz leju, kolonnas atsauce paliks nemainīga, savukārt rindas numurs tiks pielāgots katrai šūnai.
Kopējiet un ielīmējiet Excel formulas, nemainot atsauces, izmantojot Notepad
Varat skatīt katru formulu savā Excel izklājlapā, izmantojot opcijas Rādīt formulu. Lai to izdarītu, dodieties uz cilni “Formulas” un atlasiet “Rādīt formulas”.
Varat arī pāriet formulas skata režīmā, nospiežot Ctrl + ` saīsne, kas parāda katru formulu jūsu darblapā. Grave akcenta taustiņu (`) varat atrast tastatūras augšējā kreisajā stūrī rindā ar ciparu taustiņiem (zem ESC taustiņu un pirms cipara 1 taustiņa).
Atlasiet visas šūnas ar formulām, kuras vēlaties kopēt, un nospiediet Ctrl+C lai tās kopētu, vai Ctrl+X lai tās sagrieztu. Pēc tam atveriet Notepad un nospiediet Ctrl+V lai ielīmētu formulas piezīmju grāmatiņā.
Pēc tam atlasiet formulu un kopējiet (Ctrl+C) no piezīmju grāmatiņas un ielīmējiet to (Ctrl+V) šūnā, kurā vēlaties kopēt precīzu formulu. Varat tos kopēt un ielīmēt pa vienam vai visus uzreiz.
Pēc formulu ielīmēšanas izslēdziet formulu skatīšanas režīmu, nospiežot Ctrl + ` vai vēlreiz dodieties uz "Formulas" -> "Rādīt formulas".
Kopējiet precīzās formulas, izmantojot programmu Excel Find and Replace
Ja vēlaties kopēt precīzo formulu diapazonu, varat arī izmantot Excel atrašanas un aizstāšanas rīku.
Atlasiet visas šūnas, kurās ir formulas, kuras vēlaties kopēt. Pēc tam dodieties uz cilni "Sākums", rediģēšanas grupā noklikšķiniet uz "Atrast un atlasīt" un atlasiet opciju "Aizstāt" vai vienkārši nospiediet Ctrl+H lai atvērtu dialoglodziņu Find & Replace.
Dialoglodziņā Atrast un aizstāt laukā “Atrast ko” ievadiet vienādības zīmi (=). Laukā Aizstāt ar ievadiet simbolu vai rakstzīmi, kas vēl nav iekļauta jūsu formulās, piemēram, # vai , utt. Pēc tam noklikšķiniet uz pogas Aizstāt visu.
Jūs saņemsit uzvednes ziņojuma lodziņu ar tekstu “Mēs veicām 6 aizvietošanas gadījumus” (jo mēs atlasījām 6 šūnas ar formulām). Pēc tam noklikšķiniet uz "Labi" un "Aizvērt", lai aizvērtu abus dialoglodziņus. To darot, visas vienādības (=) zīmes tiek aizstātas ar jaucējzīmēm (#) un formulas tiek pārvērstas teksta virknēs. Tagad formulu šūnu atsauces kopēšanas laikā netiks mainītas.
Tagad varat atlasīt šīs šūnas, nospiediet Ctrl+C lai tos kopētu un ielīmētu mērķa šūnās ar Ctrl+V.
Visbeidzot, jums ir jāmaina (#) zīmes atpakaļ uz (=) zīmēm. Lai to izdarītu, atlasiet abus diapazonus (oriģinālo un kopēto diapazonu) un nospiediet Ctrl+H lai atvērtu dialoglodziņu Find & Replace. Šoreiz laukā “Atrast ko” ierakstiet jaucējzīmi (#) un laukā “Aizstāt ar” vienādu ar (=) un noklikšķiniet uz pogas “Aizstāt visu”. Noklikšķiniet uz "Aizvērt", lai aizvērtu dialoglodziņu.
Tagad teksta virknes tiek pārveidotas atpakaļ formulās, un jūs saņemsit šādu rezultātu:
Gatavs!