Maye gurbin ɗigo tare da waƙafi a cikin Excel tare da hanyoyi daban-daban

Sau da yawa, yayin aiki a cikin tebur na Excel, ya zama dole don maye gurbin ɗigo tare da waƙafi. Sau da yawa hakan yana faruwa ne saboda a cikin ƙasashen da ake magana da Ingilishi ana amfani da ɗigo don raba sassan juzu'i da integer a cikin lamba, yayin da a ƙasarmu ana yin waƙafi don wannan dalili.

Kuma duk abin da zai yi kyau, amma matsalar ita ce a cikin Russified version of Excel, bayanai tare da dige ba a la'akari da lambobi, wanda ya sa ba zai yiwu a kara amfani da su a cikin lissafi ba. Kuma don gyara wannan, kuna buƙatar maye gurbin ɗigon tare da waƙafi. Yadda za a yi daidai wannan a cikin Excel, za mu yi la'akari da wannan labarin.

Hanyar 1: Amfani da Nemo da Sauya Kayan aiki

Za mu fara da, watakila, hanya mafi sauƙi, wanda ya haɗa da amfani da kayan aiki "Nemo kuma Sauya", lokacin aiki da abin da kuke buƙatar yin taka tsantsan don kada ku canza lokaci tare da waƙafi a cikin bayanan da bai kamata a yi hakan ba (misali, a cikin kwanakin). To ga yadda yake aiki:

1. Jeka shafin "Gida", kuma danna maballin "Nemo kuma zaɓi" (ikon ƙara girman gilashi) a cikin toshe "Edita". Jerin zai buɗe inda muka zaɓi umarni "Maye gurbin". Ko kuma za ku iya kawai danna haɗin maɓallin Ctrl + H.
2. Taga zai bayyana akan allon. "Nemo kuma Sauya":
   • a cikin filin don shigar da ƙimar kishiyar abu "Nemo" muna rubuta alama "." (magana);
   • a cikin filin "Maye gurbin da", rubuta alamar "," (wakafi);
   • danna maɓallin "Parameters".
3. Za a bayyana ƙarin zaɓuɓɓuka don yin Nemo da Sauya. Danna maballin "Tsara" don siga "Maye gurbinsa".
4. A cikin taga da ya bayyana, saka tsarin tantanin halitta da aka gyara (wanda muke samu a ƙarshe). Bisa ga aikinmu, mun zaɓa "Lambobi" format, sannan danna OK. Idan ana so, zaku iya saita adadin wuraren ƙima, da kuma ƙungiyoyi daban-daban na lambobi ta saita akwati mai dacewa.
5. A sakamakon haka, za mu sake samun kanmu a cikin taga "Nemo kuma Sauya". Anan babu shakka muna buƙatar zaɓar yanki na sel waɗanda za a bincika maki sannan a maye gurbinsu da waƙafi. In ba haka ba, aikin maye gurbin za a yi a kan dukan takardar, kuma bayanan da bai kamata a canza ba zai iya shafar. Ana yin zaɓin kewayon sel tare da danna maɓallin linzamin kwamfuta na hagu. Danna lokacin da aka shirya "Maye gurbin Duka".
6. Duk a shirye. An kammala aikin cikin nasara, kamar yadda taga bayanin da adadin wadanda aka maye gurbinsu ya nuna.
7. Muna rufe duk windows (ban da Excel kanta), bayan haka zamu iya ci gaba da aiki tare da bayanan da aka canza a cikin tebur.

lura: don kar a zaɓi kewayon sel lokacin saita sigogi a cikin taga "Nemo kuma Sauya", za ku iya yin shi a gaba, watau da farko zaɓi sel, sa'an nan kuma kaddamar da kayan aiki da ya dace ta hanyar maɓallan da ke kan ribbon shirin ko ta amfani da gajeriyar hanya ta keyboard. Ctrl + H.

Hanyar 2: MAUYA AIKI

Bari yanzu mu dubi aikin "MADAMA", wanda kuma yana ba ku damar maye gurbin ɗigo tare da waƙafi. Amma ba kamar hanyar da muka tattauna a sama ba, ba a yin maye gurbin dabi'u a farkon waɗanda aka yi ba, amma ana nunawa a cikin sel daban.

1. Muna zuwa mafi girman tantanin halitta inda muke shirin nuna bayanai, bayan haka muna danna maɓallin "Saka aikin" (fx) zuwa hagu na mashayin dabara.
2. A cikin taga bude Mayukan Ayyuka zabi nau'i - "Rubutu", wanda muke samun mai aiki "MADAMA", zaɓi shi kuma danna OK.
3. Za mu sami kanmu a cikin taga tare da gardama na aiki waɗanda ke buƙatar cikewa:
   • a cikin darajar hujja "Rubutu" saka madaidaitan tantanin halitta na farko na ginshiƙi inda kake son maye gurbin dige-dige da waƙafi. Kuna iya yin haka da hannu ta shigar da adireshin ta amfani da maɓallan da ke kan madannai. Ko kuma za ku iya fara danna linzamin kwamfuta a cikin filin don shigar da bayanai, sannan ku danna tantanin da ake so a cikin tebur.
   • a cikin darajar hujja "Tauraro_Text" muna rubuta alama "." (magana).
   • don jayayya "Sabon_rubutu" saka alama a matsayin ƙima "," (wakafi).
   • darajar gardama "Lambar shigarwa" bazai cika ba.
   • danna lokacin da aka shirya OK.
4. Muna samun sakamakon da ake so a cikin tantanin halitta da aka zaɓa.
5. Ya rage kawai don ƙaddamar da wannan aikin zuwa ragowar layuka na ginshiƙi. Tabbas, ba kwa buƙatar yin wannan da hannu, tunda Excel yana da aikin cikawa ta atomatik. Don yin wannan, matsar da siginan kwamfuta zuwa ƙananan kusurwar dama na tantanin halitta tare da dabarar, lokacin da mai nuni ya canza zuwa alamar daɗaɗɗen baki (cika alama), riƙe maɓallin linzamin kwamfuta na hagu kuma ja shi zuwa layin ƙarshe da ke ciki. canjin bayanai.
6. Ya rage kawai don matsar da bayanan da aka canza zuwa wurin da ke cikin tebur inda ya kamata. Don yin wannan, zaɓi sel na ginshiƙi tare da sakamakon (idan an share zaɓin bayan aikin da ya gabata), danna-dama akan kowane wuri a cikin kewayon da aka zaɓa kuma zaɓi abu. “Kwafa” (ko danna gajeriyar hanyar keyboard Ctrl + C).
7. Sa'an nan kuma mu zaɓi irin wannan kewayon sel a cikin asalin ginshiƙi wanda aka canza bayanansu. Muna danna dama akan yankin da aka zaɓa kuma a cikin mahallin mahallin da ke buɗewa, a cikin zaɓuɓɓukan manna, zaɓi "Dabi'u".
8. Bayan liƙa bayanan da aka kwafi, alamar alamar mamaki zata bayyana kusa da shi. Danna kan shi kuma zaɓi daga lissafin "Maida zuwa lamba".
9. Komai yana shirye, mun sami shafi wanda duk lokuta ana maye gurbinsu da waƙafi.
10. Rukunin aikin da aka yi amfani da shi don aiki tare da aikin MUSA, ba a buƙatar kuma ana iya cire shi ta menu na mahallin. Don yin wannan, danna-dama akan sunan shafi akan mashigin daidaitawa a kwance kuma zaɓi umarni daga lissafin da ya bayyana. "Goge".
11. Ayyukan da ke sama, idan an buƙata, za a iya yin su dangane da wasu ginshiƙan teburin tushen.

Hanyar 3: Amfani da Macro

Macros kuma suna ba ku damar maye gurbin dige da waƙafi. Ga yadda ake yi:

1. Da farko kana buƙatar tabbatar da cewa an kunna shafin "Developer"wanda aka kashe ta tsohuwa a cikin Excel. Don kunna shafin da ake so, je zuwa menu "Fayil".
2. A cikin lissafin hagu, je zuwa sashin "Parameters".
3. A cikin zaɓuɓɓukan shirin, danna kan sashin "Kwasta Ribbon", Bayan haka, a gefen dama na taga, sanya kaska a gaban abu "Developer" kuma danna OK.
4. Canja zuwa shafin "Developer"wanda muke danna maballin "VisualBasic".
5. A cikin editan, danna kan takardar da muke son yin canji a kanta, a cikin taga da ke buɗewa, liƙa lambar da ke ƙasa, sannan rufe editan:

   Sub Макрос_замены_точки_на_запятую()

   Zabi.Maye gurbin Me:=".", Sauyawa:=".", LookAt:=xlPart, _

   SearchOrder: = xlByRows, MatchCase: = Ƙarya, Tsarin Bincike: = Ƙarya, _

   Sauya Tsarin: = Ƙarya

   karshen Sub

6. Yanzu zaɓi kewayon sel akan takardar inda muke shirin yin maye gurbin, sannan danna maɓallin "Macro" duk a cikin wannan shafin "Developer".
7. Wani taga zai buɗe tare da jerin macro, wanda muka zaɓa "Macro_masanin_dot_by_comma" da turawa "Gudu".
8. A sakamakon haka, za mu sami sel tare da bayanan da aka canza, waɗanda aka maye gurbin ɗigo da waƙafi, wanda shine abin da muke buƙata.

Hanyar 4: Amfani da Notepad

Ana aiwatar da wannan hanyar ta hanyar kwafin bayanai a cikin editan da aka gina a cikin tsarin aiki na Windows. Littafin rubutu don gyarawa daga baya. Ana nuna hanyar a ƙasa:

1. Da farko, za mu zaɓi kewayon sel a cikin ƙimar da muke buƙatar maye gurbin ɗigo tare da waƙafi (bari mu ɗauki shafi ɗaya a matsayin misali). Bayan haka, danna-dama akan kowane wuri a cikin yankin da aka zaɓa kuma zaɓi umarni daga menu wanda ya buɗe. “Kwafa” (ko zaka iya amfani da gajeriyar hanyar keyboard Ctrl + C).
2. Run Littafin rubutu sannan a manna bayanan da aka kwafi. Don yin wannan, danna-dama kuma zaɓi umarnin daga menu mai saukewa. "Saka" (ko amfani da haɗin gwiwa Ctrl + V).
3. A saman mashaya menu, danna kan "Shirya". Za a buɗe jerin sunayen, inda muka danna kan umarnin "Maye gurbin" (ko danna hotkeys Ctrl + H).
4. Ƙaramar taga mai maye zai bayyana akan allon:
   • a cikin filin don shigar da ƙimar sigina "Me" buga hali "." (magana);
   • a matsayin ƙimar siga "Yaya" sanya alama "," (wakafi);
   • da tura "Maye gurbin Duka".
5. Rufe taga canji. Zaɓi bayanan da aka canza, sannan danna-dama akansa kuma zaɓi umarnin “Kwafa” a cikin mahallin menu wanda ya buɗe (zaka iya amfani da Ctrl + C).
6. Bari mu koma Excel. Muna yiwa yankin alama inda kake son saka bayanan da aka canza. Sannan danna-dama akan kewayon da aka zaɓa kuma zaɓi umarnin "Ajiye rubutu kawai" a cikin zaɓuɓɓukan sakawa (ko danna Ctrl + V).
7. Ya rage kawai don saita tsarin tantanin halitta azaman "Lambobi". Kuna iya zaɓar shi a cikin akwatin kayan aiki "Lambar" (taba "Gida") ta hanyar latsa tsarin na yanzu kuma zaɓi wanda ake so.
8. An kammala aikin cikin nasara.

Hanyar 5: Saita Zaɓuɓɓukan Excel

Ta hanyar aiwatar da wannan hanyar, muna buƙatar canza wasu saitunan shirye-shirye.

1. Je zuwa menu "Fayil", inda muka danna sashin "Parameters".
2. A cikin sigogin shirin a cikin jeri na hagu, danna kan sashin "Ƙari"… A cikin saituna toshe "Zaɓuɓɓuka Gyara" cire akwati kusa da zaɓuɓɓukan "Yi amfani da tsarin separators". Bayan haka, ana kunna filayen don shigar da haruffa azaman masu rarrabawa. A matsayin mai raba lamba da sassan juzu'i, muna rubuta alamar "." (dot) kuma ajiye saitunan ta latsa maɓallin OK.
3. Ba za a sami canje-canje na gani a cikin tebur ba. Saboda haka, mu ci gaba. Don yin wannan, kwafi bayanan kuma liƙa a ciki Littafin rubutu (bari mu kalli misalin shafi daya).
4. Ciro bayanai daga Binciken sa'an nan kuma saka a cikin tebur Excel a daidai wurin da aka kwafi su. Daidaiton bayanan ya canza daga hagu zuwa dama. Wannan yana nufin cewa yanzu shirin yana fahimtar waɗannan ƙimar a matsayin lambobi.
5. Koma zuwa saitunan shirin (sashe "Ƙari"), inda muka mayar da akwati kishiyar abu "Yi amfani da tsarin separators" a wurin kuma danna maɓallin OK.
6. Kamar yadda kuke gani, shirin ya maye gurbin ɗigon ta atomatik tare da waƙafi. Kar a manta da canza tsarin bayanai zuwa "Lambobi" kuma za ku iya yin aiki tare da su gaba.

Hanyar 6: Saitunan Tsari

Kuma a ƙarshe, yi la'akari da wata hanyar da ta yi kama da wadda aka bayyana a sama, amma ta ƙunshi canza saitunan ba na Excel ba, amma na tsarin aiki na Windows.

1. Mu shiga Control panel ta kowace hanya mai dacewa. Alal misali, ana iya yin wannan ta hanyar searchta hanyar buga sunan da ake so da zaɓar zaɓin da aka samo.
2. Saita ra'ayi azaman ƙarami ko manyan gumaka, sannan danna sashin "Ka'idojin Yanki".
3. Tagan saitunan yanki zai bayyana, wanda a ciki, kasancewa a cikin shafin "Tsara" danna maballin "Ƙarin saituna".
4. A cikin taga na gaba tare da saitunan tsarin, muna ganin siga “Masu Rarraba Integer/Decimal” da darajar da aka saita don shi. Maimakon waƙafi, rubuta lokaci kuma latsa OK.
5. Hakazalika zuwa hanya ta biyar da aka tattauna a sama, muna kwafin bayanai daga Excel zuwa Littafin rubutu da baya.
6. Muna mayar da saitunan tsarin zuwa matsayinsu na asali. Wannan aikin yana da mahimmanci, saboda in ba haka ba kurakurai na iya faruwa a cikin ayyukan wasu shirye-shirye da kayan aiki.
7. Duk dige-dige a cikin ginshiƙin da muke aiki akai an maye gurbinsu ta atomatik da waƙafi.

Kammalawa

Don haka, Excel yana ba da hanyoyi daban-daban guda 5, ta amfani da waɗanda zaku iya maye gurbin ɗigo tare da waƙafi, idan irin wannan buƙatar ta taso yayin aiki. Bugu da kari, za ka iya amfani da wata hanya, wanda ya shafi yin canje-canje a cikin saitunan tsarin aiki na Windows kanta, wanda aka shigar da Excel.