Mukati
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.
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:
Basa: kopi chiyero nemafomati kuitira kuti mafomula asashanduke uye arambe akafanana, uchichengeta mhinduro dzekuverenga.
Nzira 1. Absolute links
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:
- Sarudza huwandu nemafomula (mumuenzaniso wedu D2: D8)
- Click Ctrl + H pa keyboard kana pane tab Imba - Tsvaga uye Sarudza - Tsiva (Kumba - Tsvaga & Sarudza - Tsiva)
- Mubhokisi rebhokisi rinoonekwa, isa zvatiri kutsvaga uye izvo zvatinotsiva nazvo, uye mukati Parameters (Sarudzo) usakanganwa kujekesa Kutsvaga nzvimbo - Mafomula. Tinodzvanya Tsiva zvese (Siva zvese).
- Kopira mutsara wabuda nemafomula akavharwa kunzvimbo chaiyo:
- 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 huwandu hwedu D2: D8 uye uiise muyero notibhuku:
Zvino sarudza zvese zvakanamirwa (Ctrl + A), zvikope kune clipboard zvakare (Ctrl + C) uye unamate pane pepa munzvimbo yaunoda:
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:
- 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