Kopisha amafomula ngaphandle kokushintshwa kwesixhumanisi

Inkinga

Ake sithi sinetafula elilula elifana naleli, lapho amanani abalwa khona inyanga ngayinye emadolobheni amabili, bese isamba siguqulwa sibe ama-euro ngesilinganiso esivela kuseli eliphuzi J2.

Kopisha amafomula ngaphandle kokushintshwa kwesixhumanisi

Inkinga ukuthi uma ukopisha ububanzi D2:D8 namafomula kwenye indawo eshidini, iMicrosoft Excel izolungisa ngokuzenzakalelayo izixhumanisi kulawa mafomula, iziyise endaweni entsha futhi iyeke ukubala:

Kopisha amafomula ngaphandle kokushintshwa kwesixhumanisi

Umsebenzi: kopisha ububanzi ngamafomula ukuze amafomula angashintshi futhi ahlale efana, ugcine imiphumela yokubala.

Indlela 1. Izixhumanisi eziphelele

Njengoba ubona esithombeni esidlule, i-Excel ishintsha izixhumanisi ezihlobene kuphela. Ireferensi ephelele (enezimpawu ze-$) yeseli eliphuzi engu-$J$2 ayikasuki. Ngakho-ke, ukuze kukopishwe amafomula ngokuqondile, ungakwazi ukuguqula okwesikhashana zonke izinkomba kuwo wonke amafomula zibe yilawo aphelele. Uzodinga ukukhetha ifomula ngayinye kubha yefomula bese ucindezela inkinobho F4:
Kopisha amafomula ngaphandle kokushintshwa kwesixhumanisi
Ngenani elikhulu lamaseli, le nketho, vele, iyanyamalala - inzima kakhulu.

Indlela 2: Khubaza okwesikhashana amafomula

Ukuze uvimbele amafomula ukuthi angashintshi lapho ukopisha, udinga (okwesikhashana) uqinisekise ukuthi i-Excel iyayeka ukuwaphatha njengamafomula. Lokhu kungenziwa ngokushintsha uphawu olulinganayo (=) nanoma yiluphi olunye uhlamvu olungatholakali kumafomula, njengophawu lwe-hashi (#) noma amapheya ama-ampersand (&&) ngesikhathi sokukopisha. Kwalokhu:

  1. Khetha ububanzi obunamafomula (kusibonelo sethu D2:D8)
  2. Chofoza Ctrl + H kukhibhodi noma kuthebhu Ikhaya - Thola bese Khetha - Faka esikhundleni (Ikhaya — Thola & Khetha — Faka esikhundleni)

    Kopisha amafomula ngaphandle kokushintshwa kwesixhumanisi

  3. Ebhokisini lengxoxo elivelayo, faka esikufunayo nesishintsha ngakho, futhi ku Amapharamitha (Izinketho) ungakhohlwa ukucacisa Ububanzi bokusesha - Amafomula. Siyacindezela Faka esikhundleni konke (Faka esikhundleni sakho konke).
  4. Kopisha ububanzi obuwumphumela ngamafomula angasebenzi uye endaweni efanele:

    Kopisha amafomula ngaphandle kokushintshwa kwesixhumanisi

  5. Faka esikhundleni # on = emuva usebenzisa iwindi elifanayo, ukubuyisela ukusebenza kumafomula.

Indlela yesi-3: Kopisha nge-Notepad

Le ndlela ishesha kakhulu futhi kulula.

Cindezela isinqamuleli sekhibhodi Ctrl+Ё noma inkinobho Bonisa amafomula ithebhu formula (Amafomula — Bonisa amafomula), ukuvula imodi yokuhlola ifomula - esikhundleni semiphumela, amaseli azobonisa amafomula abalwa ngawo:

Kopisha amafomula ngaphandle kokushintshwa kwesixhumanisi

Kopisha uhla lwethu D2:D8 bese ulunamathisele kwejwayelekile Inothi:

Kopisha amafomula ngaphandle kokushintshwa kwesixhumanisi

Manje khetha konke okunamathiselwe (Ctrl + A), kukopishele ebhodini lokunamathisela futhi (Ctrl + C) bese unamathisele eshidini endaweni oyidingayo:

Kopisha amafomula ngaphandle kokushintshwa kwesixhumanisi

Kuhlala kuphela ukucindezela inkinobho Bonisa amafomula (Bonisa Amafomula)ukuze ubuyisele i-Excel kumodi evamile.

Qaphela: le ndlela ngezinye izikhathi ayiphumeleli kumathebula ayinkimbinkimbi anamaseli ahlanganisiwe, kodwa ezikhathini eziningi isebenza kahle.

Indlela 4. Macro

Uma ngokuvamile kufanele wenze ukukopisha okunjalo kwamafomula ngaphandle kokushintsha izinkomba, kunengqondo ukusebenzisa i-macro kulokhu. Cindezela isinqamuleli sekhibhodi I-Alt + F11 noma inkinobho Visual Basic ithebhu Unjiniyela (Unjiniyela), faka imojuli entsha ngemenyu Faka – Imojula  bese ukopisha umbhalo wale macro lapho:

I-Sub Copy_Formulas() Fiphalisa Ububanzi Bekhophi Njengobubanzi, Namathisela Ububanzi Njengobubanzi Bephutha Qalisa Kabusha Setha Okulandelayo copyRange = Isicelo.InputBox("Khetha amaseli anamafomula ozowakopisha.", _ "Kopisha amafomula ngokuqondile", Okuzenzakalelayo:=Ukukhetha.Ikheli, Uhlobo := 8) Uma i-copyRange Ingelutho Bese Uphuma Ku-Sub Set pasteRange = Application.InputBox("Manje khetha ububanzi bokunamathisela." & vbCrLf & vbCrLf & _ "Ububanzi kufanele bulingane ngosayizi nobubanzi " & vbCrLf & _ " bangempela. ukukopisha." , "Kopisha amafomula ngokuqondile", _ Okuzenzakalelayo:=Selection.Address, Type:=8) Uma unamathiseleRange.Cells.Count <> copyRange.Cells.Count Bese MsgBox "Kopisha futhi unamathisele ububanzi buhluka ngosayizi!", vbIsibabazo, "Kopisha iphutha " Phuma Esiphethweni Esingaphansi Uma Uma I-pasteRange Ingelutho Bese Uphuma Okunye PasteRange.Formula = copyRange.I-Formula End if End Sub

Ungasebenzisa inkinobho ukusebenzisa i-macro. amamakhro ithebhu Unjiniyela (Unjiniyela - Amamakhro) noma isinqamuleli sekhibhodi I-Alt + F8. Ngemuva kokusebenzisa i-macro, izokucela ukuthi ukhethe ububanzi obunamafomula okuqala kanye nobubanzi bokufaka futhi izokopisha amafomula ngokuzenzakalelayo:

Kopisha amafomula ngaphandle kokushintshwa kwesixhumanisi

  • Ukubuka okulula kwamafomula nemiphumela ngesikhathi esifanayo
  • Kungani isitayela sesithenjwa se-R1C1 sidingeka kumafomula e-Excel
  • Ungawathola kanjani ngokushesha wonke amaseli anamafomula
  • Ithuluzi lokukopisha amafomula aqondile kusuka kusengezo se-PLEX

 

shiya impendulo