Maye gurbin rubutu mai girma tare da dabaru

A ce kana da jerin abubuwan da, tare da mabambantan digiri na “madaidaici,” an rubuta bayanan farko – misali, adireshi ko sunayen kamfani:

An gani a fili cewa birni ɗaya ko kamfani ɗaya yana nan a cikin bambance-bambancen motley, wanda, a fili, zai haifar da matsaloli masu yawa yayin aiki tare da waɗannan tebur a nan gaba. Kuma idan kun yi tunani kaɗan, za ku iya samun misalai da yawa na ayyuka iri ɗaya daga wasu wurare.

Yanzu ka yi tunanin cewa irin wannan karkatattun bayanai suna zuwa maka akai-akai, watau wannan ba labari ba ne na lokaci guda "gyara shi da hannu, manta da shi", amma matsala akai-akai kuma a cikin adadi mai yawa na sel.

Me za a yi? Kar a maye gurbin rubutun da hannu sau 100500 tare da madaidaicin ta akwatin "Nemi kuma Sauya" ko ta dannawa. Ctrl+H?

Abu na farko da ya zo a hankali a cikin irin wannan yanayin shi ne yin maye gurbin taro bisa ga littafin da aka riga aka tattara na daidaitattun zaɓuɓɓukan da ba daidai ba - kamar haka:

Abin baƙin ciki shine, tare da bayyanannun irin wannan aikin, Microsoft Excel ba shi da hanyoyin ginannun hanyoyi masu sauƙi don magance shi. Da farko, bari mu gano yadda ake yin wannan tare da dabaru, ba tare da haɗa “magungunan manyan bindigogi” a cikin sigar macros a cikin VBA ko Query ba.

Case 1. Cikakken cikakken maye

Bari mu fara da ƙarami mai sauƙi - yanayin da kake buƙatar maye gurbin tsohon rubutun karkatacce da sabon abu. cikakken.

Bari mu ce muna da tebur biyu:

A cikin farko - ainihin sunayen kamfanoni masu bambanta. A cikin na biyu - littafin tunani na wasiƙa. Idan muka sami sunan kamfani a cikin tebur na farko kowace kalma daga ginshiƙi Don nemowa, to kuna buƙatar gaba ɗaya maye gurbin wannan karkatacciyar suna tare da daidai - daga ginshiƙi Canje tebur dubawa na biyu.

Don dacewa:

• Dukansu allunan suna jujjuya su zuwa tsauri ("smart") ta amfani da gajeriyar hanyar madannai Ctrl+T ko tawaga Saka - Tebur (Saka - Tebur).
  
• A kan shafin da ya bayyana Constructor (Zane) tebur na farko mai suna data, da na biyu reference table – Abubuwa.

Don bayyana ma'anar dabarar, bari mu ɗan ɗan yi nesa.

Ɗaukar kamfani na farko daga cell A2 a matsayin misali kuma na ɗan lokaci manta game da sauran kamfanoni, bari mu yi ƙoƙari mu ƙayyade wane zaɓi daga shafi. Don nemowa ya hadu a can. Don yin wannan, zaɓi kowane fanko tantanin halitta a cikin ɓangaren kyauta na takardar kuma shigar da aikin a can DOMIN SAMU (NEMO):

Wannan aikin yana ƙayyade idan an haɗa ƙananan igiyoyin da aka bayar (hujja ta farko ita ce duk dabi'u daga ginshiƙi Don nemowa) a cikin rubutun tushe (kamfanin farko daga teburin bayanai) kuma yakamata ya fitar da ko dai lambar ƙa'ida ta abin da aka samo rubutun daga gare ta, ko kuma kuskure idan ba a sami gunkin substring ba.

Dabarar a nan ita ce, tun da mun kayyade ba ɗaya ba, amma dabi'u da yawa a matsayin hujja ta farko, wannan aikin kuma zai dawo a sakamakon ba ƙima ɗaya ba, amma tsararru na abubuwa 3. Idan ba ku da sabon sigar Office 365 wanda ke goyan bayan tsararraki masu ƙarfi, to bayan shigar da wannan dabara kuma danna kan. Shigar za ku ga wannan jeri daidai a kan takardar:

Idan kuna da sigogin Excel na baya, to bayan danna kan Shigar za mu ga kimar farko ne kawai daga jerin sakamakon, watau kuskure #VALUE! (#DARAJAR!).

Kada ku ji tsoro 🙂 A gaskiya ma, tsarinmu yana aiki kuma har yanzu kuna iya ganin dukkanin sakamako idan kun zaɓi aikin da aka shigar a cikin mashigin dabara kuma danna maɓallin. F9(kawai kar a manta da dannawa Escdon komawa ga tsari):

Sakamakon tsararru na sakamako yana nufin cewa a cikin ainihin sunan kamfani na karkatacciyar hanya (GK Morozko OAO) na duk darajar a cikin wani shafi Don nemowa samu na biyu kawai (Morozko), kuma farawa daga hali na 4 a jere.

Yanzu bari mu ƙara aiki a tsarin mu Duba(DUBA SAMA):

Wannan aikin yana da dalilai guda uku:

1. Ƙimar da ake so - zaku iya amfani da kowane isasshe babban lamba (babban abu shine ya wuce tsawon kowane rubutu a cikin bayanan tushen)
   
2. Duban_vector - kewayon ko tsararru inda muke neman ƙimar da ake so. Ga aikin da aka gabatar a baya DOMIN SAMU, wanda ke dawo da tsararru {#VALUE!:4:#VALUE!}
   
3. Vector_sakamakon - kewayon da muke so mu dawo da ƙimar idan an sami ƙimar da ake so a cikin tantanin halitta mai dacewa. Anan ga daidaitattun sunaye daga ginshiƙi Canje Teburin nuninmu.
   

Babban abin da ba a bayyane yake ba anan shine aikin Duba idan babu takamaiman wasa, koyaushe yana neman mafi ƙarancin ƙima (ta baya).. Don haka, ta hanyar ƙididdige kowane lamba mai girma (misali, 9999) azaman ƙimar da ake so, za mu tilasta. Duba nemo tantanin halitta tare da mafi ƙarancin lamba (4) a cikin jeri {#VALUE!:4:#VALUE!} sannan a mayar da madaidaicin ƙimar daga ma'aunin sakamako, watau daidai sunan kamfani daga ginshiƙi. Canje.

Nuance na biyu shine, a fasahance, tsarin mu shine tsarin tsararru, saboda aiki DOMIN SAMU ya dawo a matsayin sakamako ba ɗaya ba, amma tsararrun ƙima uku. Amma tun da aikin Duba yana goyan bayan tsararraki daga cikin akwatin, to ba lallai ne mu shigar da wannan dabarar azaman ƙirar tsararrun gargajiya ba - ta amfani da gajeriyar hanyar keyboard. Ctrl+Motsi+Shigar. Mai sauƙi zai isa Shigar.

Shi ke nan. Da fatan za ku sami dabaru.

Ya rage don canja wurin ƙaƙƙarfan dabara zuwa tantanin halitta na farko na B2 na ginshiƙi Kafaffen - kuma an warware aikinmu!

Tabbas, tare da tebur na yau da kullun (ba mai hankali ba), wannan dabara kuma tana aiki sosai (kawai kar ku manta da maɓallin F4 da kuma gyara hanyoyin da suka dace):

Case 2. Babban maye gurbin

Wannan shari'ar ta fi wayo. Har ila yau muna da tebur "masu wayo" guda biyu:

Teburin farko tare da rubutattun adireshi na karkata wanda ke buƙatar gyara (Na kira shi Bayanai2). Teburi na biyu shine littafin tunani, bisa ga abin da kuke buƙatar yin juzu'i na maye gurbin substring a cikin adireshin (Na kira wannan tebur). Canje-canje2).

Bambanci mai mahimmanci a nan shi ne cewa kana buƙatar maye gurbin kawai guntun bayanan asali - alal misali, adireshin farko yana da kuskure. “St. Petersburg" a hannun dama “St. Petersburg", barin sauran adireshin (zip code, titi, gida) kamar yadda yake.

Ƙarshen dabarar za ta yi kama da wannan (don sauƙi na fahimta, na raba shi zuwa yawan layi da ake amfani da shi alt+Shigar):

Babban aikin anan ana yin shi ta daidaitaccen aikin rubutu na Excel MUSA (MADAMA), wanda ke da dalilai guda uku:

1. Rubutun tushe – adireshin farko na karkatacciyar hanya daga ginshiƙin adireshi
2. Abin da muke nema - a nan muna amfani da abin zamba tare da aikin Duba (DUBA SAMA)daga hanyar da ta gabata don cire ƙimar daga ginshiƙi Don nemowa, wanda aka haɗa a matsayin guntu a cikin adireshin lanƙwasa.
3. Abin da za a maye gurbin da shi - a cikin hanyar da za mu sami daidaitattun ƙimar da ta dace da shi daga shafi Canje.

Shigar da wannan dabarar tare da Ctrl+Motsi+Shigar a nan ma ba a buƙata, ko da yake, a gaskiya, tsari ne na tsararru.

Kuma an gani a sarari (duba Kuskuren #N/A a hoton da ya gabata) cewa irin wannan dabarar, ga duk kyawunta, tana da ma'ana biyu:

• aiki SAUKI yana da mahimmanci, don haka "Spb" a cikin layi mai mahimmanci ba a samo shi a cikin tebur mai sauyawa ba. Don magance wannan matsalar, zaku iya amfani da aikin ko dai ZAMANIT (MUSA), ko da farko kawo duka teburi zuwa rajista iri ɗaya.
• Idan rubutun daidai ne da farko ko a ciki babu guntun da za a maye gurbinsa (layin ƙarshe), to tsarin mu yana jefa kuskure. Wannan lokacin ana iya kawar da shi ta hanyar tsangwama da maye gurbin kurakurai ta amfani da aikin IFEROR (FURTA):

  

• Idan ainihin rubutun ya ƙunshi gutsure da yawa daga kundin adireshi lokaci guda, to, mu dabara maye gurbin kawai na karshe (a cikin 8th line, Ligovsky «Avenue« canza zuwa "pr-t", Amma "S-Pb" on “St. Petersburg" ba, saboda "S-Pb” ya fi girma a cikin kundin adireshi). Ana iya magance wannan matsala ta hanyar sake aiwatar da tsarin mu, amma riga tare da shafi Kafaffen:

  
  

Ba cikakke ba kuma mai wahala a wurare, amma ya fi kyau fiye da maye gurbin hannu ɗaya, daidai? 🙂

PS

A cikin labarin na gaba, za mu gano yadda ake aiwatar da irin wannan canji mai yawa ta amfani da macro da Query Query.

• Yadda aikin SUBSTITUTE ke aiki don maye gurbin rubutu
  
• Nemo Madaidaicin Matsalolin Rubutu Ta Amfani da EXACT Aiki
• Bincike mai mahimmanci da canji (masu hankali VLOOKUP)