Kopa mafomula pasina chinongedzo chekuchinja

Dambudziko

Ngatitii tine tafura iri nyore seyiyi, iyo mari inoverengerwa mwedzi wega wega mumaguta maviri, uye ipapo iyo yose inoshandurwa kuva euro pachiyero kubva kune yellow cell J2.

Kopa mafomula pasina chinongedzo chekuchinja

Dambudziko nderekuti kana iwe ukakopa renji D2: D8 nemafomati kumwe kunhu pane pepa, saka Microsoft Excel inozogadzirisa otomatiki zvinongedzo mune aya mafomula, ichiaendesa kunzvimbo nyowani uye kumira kuverenga:

Kopa mafomula pasina chinongedzo chekuchinja

Basa: kopi chiyero nemafomati kuitira kuti mafomula asashanduke uye arambe akafanana, uchichengeta mhinduro dzekuverenga.

Nzira 1. Absolute links

Sezvauri kuona kubva pamufananidzo wapfuura, Excel inoshandura chete hukama hwehukama. Mhedziso (ine $ signs) referensi yeyero sero $J$2 haina kufamba. Naizvozvo, pakunyatso kukopa mafomula, unogona kushandura kwenguva pfupi mareferensi ese mune ese mafomula kuita iwo akazara. Iwe unozofanirwa kusarudza imwe neimwe fomula mune fomula bar uye tinya kiyi F4:
Kopa mafomula pasina chinongedzo chekuchinja
Nenhamba huru yemasero, iyi sarudzo, hongu, inonyangarika - yakanyanya kuoma.

Method 2: Kudzima mafomula kwenguva pfupi

Kuti udzivise mafomula kubva pakuchinja paunenge uchikopa, unofanirwa (kwechinguva) kuve nechokwadi chekuti Excel inomira kuvabata semafomula. Izvi zvinogona kuitwa nekutsiva chiratidzo chakaenzana (=) nechero humwe hunhu husingawanzo kuwanikwa mumafomula, senge hashi chiratidzo (#) kana peya yeampersand (&&) yenguva yekukopa. Nokuda kweizvi:

  1. Sarudza huwandu nemafomula (mumuenzaniso wedu D2: D8)
  2. Click Ctrl + H pa keyboard kana pane tab Imba - Tsvaga uye Sarudza - Tsiva (Kumba - Tsvaga & Sarudza - Tsiva)

    Kopa mafomula pasina chinongedzo chekuchinja

  3. Mubhokisi rebhokisi rinoonekwa, isa zvatiri kutsvaga uye izvo zvatinotsiva nazvo, uye mukati Parameters (Sarudzo) usakanganwa kujekesa Kutsvaga nzvimbo - Mafomula. Tinodzvanya Tsiva zvese (Siva zvese).
  4. Kopira mutsara wabuda nemafomula akavharwa kunzvimbo chaiyo:

    Kopa mafomula pasina chinongedzo chekuchinja

  5. Dzorera # on = kumashure uchishandisa hwindo rimwe chete, kudzorera kushanda kune mafomula.

Nzira 3: Kopa neNotepad

Iyi nzira inokurumidza uye iri nyore.

Dzvanya nzira yekudimbudzira kiibhodhi Ctrl+Ё kana bhatani Ratidza mafomula Tab Formula (Mafomula - Ratidza mafomula), kuti ubatidze fomura yekutarisa modhi - pachinzvimbo chemibairo, maseru acharatidza mafomula avanoverengerwa nawo:

Kopa mafomula pasina chinongedzo chekuchinja

Kopa huwandu hwedu D2: D8 uye uiise muyero notibhuku:

Kopa mafomula pasina chinongedzo chekuchinja

Zvino sarudza zvese zvakanamirwa (Ctrl + A), zvikope kune clipboard zvakare (Ctrl + C) uye unamate pane pepa munzvimbo yaunoda:

Kopa mafomula pasina chinongedzo chekuchinja

Inosara chete kudzvanya bhatani Ratidza mafomula (Ratidza Formulas)kudzorera Excel kune yakajairika mode.

Cherechedza: nzira iyi dzimwe nguva inokundikana pamatafura akaoma ane masero akabatanidzwa, asi muhuwandu hwezviitiko zvinoshanda zvakanaka.

Nzira 4. Macro

Kana iwe kazhinji uchifanirwa kuita kutevedzera kwakadai kwemafomula pasina kushandura mareferensi, saka zvine musoro kushandisa macro kune izvi. Dzvanya nzira yekudimbudzira kiibhodhi Alt + F11 kana bhatani Visual Basic Tab yokuvaka (Mugadziri), isa module nyowani kuburikidza nemenyu Pinza – Module  uye kopira zvinyorwa zveiyi macro ipapo:

Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Resume Next Set copyRange = Application.InputBox("Sarudza maseru ane mafomula ekukopa.", _ "Kopa mafomula chaiwo", Default:=Sarudzo.Kero, Type := 8) Kana copyRange Isiri Chiro Wobva wabuda muSub Set pasteRange = Application.InputBox("Zvino sarudza paste range." & vbCrLf & vbCrLf & _ "Rwanda runofanira kuenzana nekukura kune yekutanga " & vbCrLf & _" mutsara wemaseru. kukopa." , "Copy formulas exactly", _ Default:=Selection.Address, Type:=8) Kana pasteRange.Cells.Count <> copyRange.Cells.Count Zvadaro msgBox "Kopa nekuisa masaizi anosiyana muhukuru!", vbExclamation, "Copy error" Buda Sub End Kana PasteRange Isipo Wobva wabuda Sub Else pasteRange.Formula = copyRange.Formula End Kana End Sub

Unogona kushandisa bhatani kumhanya macro. macro Tab yokuvaka (Mugadziri - Macros) kana nzira yekudimbudzira keyboard Alt + F8. Mushure mekumhanyisa iyo macro, ichakukumbira kuti usarudze huwandu nemafomula ekutanga uye iyo yekuisa renji uye inoteedzera mafomula otomatiki:

Kopa mafomula pasina chinongedzo chekuchinja

  • Kuona kuri nyore kwemafomula uye mhedzisiro panguva imwe chete
  • Nei R1C1 referensi chimiro ichidikanwa muExcel mafomula
  • Maitiro ekukurumidza kuwana maseru ese ane mafomula
  • Chishandiso chekukopa chaiwo mafomula kubva kune PLEX yekuwedzera

 

Leave a Reply