Contents
matsala
A ce muna da tebur mai sauƙi kamar wannan, wanda aka ƙididdige adadin kowane wata a cikin biranen biyu, sa'an nan kuma an juyar da jimlar zuwa Yuro a ƙimar daga rawaya cell J2.
Matsalar ita ce idan kun kwafi kewayon D2:D8 tare da dabaru a wani wuri a kan takardar, to Microsoft Excel za ta gyara hanyoyin da ke cikin waɗannan dabarun ta atomatik, motsa su zuwa sabon wuri kuma ya daina ƙirgawa:
Aiki: kwafi kewayon tare da ƙididdiga don kada ƙididdiga su canza kuma su kasance iri ɗaya, kiyaye sakamakon lissafin.
Hanyar 1. Cikakken haɗin kai
Hanyar 2: Kashe ƙididdiga na ɗan lokaci
Don hana ƙididdiga daga canzawa lokacin yin kwafi, kuna buƙatar (na ɗan lokaci) tabbatar da cewa Excel ya daina ɗaukar su azaman tsari. Ana iya yin wannan ta hanyar maye gurbin alamar daidai (=) tare da kowane hali wanda ba a saba samuwa a cikin ƙididdiga ba, kamar alamar zanta (#) ko biyu na ampersands (&&) don kwafin lokaci. Don wannan:
- Zaɓi kewayon da dabaru (a cikin misalinmu D2:D8)
- Click Ctrl + H a kan keyboard ko a kan tab Gida - Nemo kuma Zaɓi - Sauya (Gida - Nemo & Zaɓi - Sauya)
- A cikin akwatin maganganu da ya bayyana, shigar da abin da muke nema da abin da muka maye gurbinsa da, sannan a ciki Siga (Zaɓuɓɓuka) kar a manta a fayyace Iyakar bincike - Formulas. Muna danna Sauya duka (Maye gurbin duka).
- Kwafi kewayon da aka samu tare da kashe-kashen dabaru zuwa wurin da ya dace:
- Sauya # on = baya amfani da taga iri ɗaya, dawo da ayyuka zuwa ƙira.
Hanyar 3: Kwafi ta hanyar Notepad
Wannan hanya tana da sauri da sauƙi.
Danna gajeriyar hanyar madannai Ctrl+A ko button Nuna dabaru tab tsari (Formulas - Nuna dabaru), don kunna yanayin duba dabara - maimakon sakamakon, sel za su nuna tsarin da aka ƙididdige su:
Kwafi kewayon mu D2:D8 kuma manna shi cikin ma'auni Littafin rubutu:
Yanzu zaɓi duk abin da aka liƙa (Ctrl + A), sake kwafa shi zuwa allon allo (Ctrl + C) kuma manna shi a kan takardar a wurin da kuke buƙata:
Ya rage kawai don danna maɓallin Nuna dabaru (Nuna Formulas)don mayar da Excel zuwa yanayin al'ada.
Lura: wannan hanyar wani lokaci tana kasawa akan rikitattun tebura tare da haɗe-haɗe sel, amma a mafi yawan lokuta tana aiki lafiya.
Hanyar 4. Macro
Idan sau da yawa dole ne ku yi irin wannan kwafin dabarun ba tare da jujjuya nassoshi ba, to yana da ma'ana don amfani da macro don wannan. Danna gajeriyar hanyar madannai Alt+F11 ko button Kayayyakin aikin Basic tab developer (Mai haɓakawa), saka sabon tsari ta cikin menu Saka - Module kuma kwafi rubutun wannan macro a wurin:
Sub Copy_Formulas() Dim copyRange As Range, pasteRange As Range On Error Ci gaba Saita kwafin Range = Aikace-aikace.InputBox("Zaɓi sel tare da dabara don kwafa." 8) Idan copyRange Ba Komai ba Sai Ka fita Sub Set pasteRange = Application.InputBox("Yanzu zaži kewayon manna." & vbCrLf & vbCrLf & _ "Dole ne kewayon ya zama daidai da girmansa zuwa ainihin" & vbCrLf & _" kewayon sel. to copy." , "Copy formulas daidai", _ Default:=Selection.Address, Type:=8) If pasteRange.Cells.Count <> copyRange.Cells.Count Sa'an nan MsgBox "Kwafi da liƙa jeri sun bambanta da girman!", vbExclamation, "Kuskuren Kwafi" Fita Ƙarshen Ƙarshen Idan Idan PasteRange Ba Komai ba Sai Ka Fita Sub ElseRangeRange.Formula = copyRange.Formula Ƙarshen Idan Ƙarshen Sub.Kuna iya amfani da maɓallin don kunna macro. Macros tab developer (Developer - Macros) ko gajeriyar hanyar keyboard Alt+F8. Bayan gudanar da macro, zai tambaye ka ka zaɓi kewayon tare da ainihin dabarar da kewayon shigarwa kuma za ta kwafi dabarun ta atomatik:
- Ingantacciyar kallon dabaru da sakamako a lokaci guda
- Me yasa ake buƙatar salon tunani R1C1 a cikin ƙirar Excel
- Yadda ake saurin nemo duk sel masu tsari
- Kayan aiki don kwafe ainihin ƙididdiga daga PLEX add-on