Ubadilishaji wa maandishi mengi na fomula

Tuseme una orodha ambayo, kwa viwango tofauti vya "unyoofu," data ya awali imeandikwa - kwa mfano, anwani au majina ya kampuni:

Inaonekana wazi kwamba jiji moja au kampuni iko hapa katika tofauti za motley, ambayo, kwa wazi, itaunda matatizo mengi wakati wa kufanya kazi na meza hizi katika siku zijazo. Na ikiwa unafikiri kidogo, unaweza kupata mifano mingi ya kazi zinazofanana kutoka kwa maeneo mengine.

Sasa fikiria kwamba data potofu kama hiyo inakuja kwako mara kwa mara, ambayo ni, hii sio hadithi ya wakati mmoja ya "kurekebisha kwa mikono, sahau", lakini shida mara kwa mara na kwa idadi kubwa ya seli.

Nini cha kufanya? Usibadilishe maandishi yaliyopotoka wewe mwenyewe mara 100500 na sahihi kupitia kisanduku cha "Tafuta na Ubadilishe" au kwa kubofya. Ctrl+H?

Jambo la kwanza linalokuja akilini katika hali kama hii ni kufanya uingizwaji wa wingi kulingana na kitabu cha kumbukumbu kilichokusanywa hapo awali cha kulinganisha chaguzi zisizo sahihi na sahihi - kama hii:

Kwa bahati mbaya, pamoja na kuenea kwa kazi kama hiyo, Microsoft Excel haina njia rahisi za kujengwa za kuisuluhisha. Kuanza, hebu tuone jinsi ya kufanya hivyo na fomula, bila kuhusisha "sanaa nzito" katika mfumo wa macros katika VBA au Hoja ya Nguvu.

Kesi 1. Uingizwaji kamili wa wingi

Hebu tuanze na kesi rahisi - hali ambapo unahitaji kubadilisha maandishi ya zamani yaliyopotoka na mpya. kikamilifu.

Wacha tuseme tunayo meza mbili:

Katika kwanza - majina ya awali ya variegated ya makampuni. Katika pili - kitabu cha kumbukumbu cha mawasiliano. Ikiwa tunapata kwa jina la kampuni katika jedwali la kwanza neno lolote kutoka kwa safu Kutafuta, basi unahitaji kubadilisha kabisa jina hili lililopotoka na moja sahihi - kutoka kwenye safu Msaada meza ya pili ya kuangalia.

Kwa urahisi:

• Majedwali yote mawili yanabadilishwa kuwa yanayobadilika (“smart”) kwa kutumia njia ya mkato ya kibodi Ctrl+T au timu Ingiza - Jedwali (Ingiza - Jedwali).
  
• Kwenye kichupo kinachoonekana kuujenga (Ubunifu) meza ya kwanza iliyopewa jina Data, na jedwali la pili la marejeleo - Mabadilisho.

Ili kuelezea mantiki ya formula, hebu tuende kidogo kutoka mbali.

Kuchukua kampuni ya kwanza kutoka kwa seli A2 kama mfano na kusahau kwa muda kuhusu kampuni zingine, wacha tujaribu kuamua ni chaguo gani kutoka kwa safu. Kutafuta hukutana huko. Ili kufanya hivyo, chagua kiini chochote tupu katika sehemu ya bure ya karatasi na uingie kazi huko KUTAFUTA (TAFUTA):

Chaguo hili la kukokotoa huamua ikiwa kamba ndogo iliyotolewa imejumuishwa (hoja ya kwanza ni maadili yote kutoka kwa safuwima Kutafuta) kwenye maandishi chanzo (kampuni ya kwanza kutoka kwa jedwali la data) na inapaswa kutoa ama nambari ya mpangilio ya herufi ambayo maandishi yalipatikana, au hitilafu ikiwa kamba ndogo haikupatikana.

Ujanja hapa ni kwamba kwa kuwa hatukutaja moja, lakini maadili kadhaa kama hoja ya kwanza, chaguo hili la kukokotoa pia litarudi kama matokeo sio thamani moja, lakini safu ya vitu 3. Ikiwa huna toleo la hivi punde la Office 365 linaloauni safu zinazobadilika, basi baada ya kuingiza fomula hii na kubofya kuingia utaona safu hii kwenye karatasi:

Ikiwa una matoleo ya awali ya Excel, basi baada ya kubofya kuingia tutaona tu thamani ya kwanza kutoka kwa safu ya matokeo, yaani kosa #VALUE! (#THAMANI!).

Haupaswi kuogopa 🙂 Kwa kweli, fomula yetu inafanya kazi na bado unaweza kuona safu nzima ya matokeo ikiwa utachagua kitendakazi kilichoingizwa kwenye upau wa formula na bonyeza kitufe. F9(usisahau tu kubonyeza Esckurudi kwenye fomula):

Safu inayotokana ya matokeo inamaanisha kuwa katika jina la kampuni iliyopotoka (GK Morozko OAO) ya maadili yote katika safu Kutafuta kupatikana ya pili tu (Morozko), na kuanzia herufi ya 4 mfululizo.

Sasa hebu tuongeze kitendakazi kwenye fomula yetu View(TAFUTA; TAZAMA JUU):

Kazi hii ina hoja tatu:

1. Thamani inayotakiwa - unaweza kutumia nambari yoyote kubwa ya kutosha (jambo kuu ni kwamba inazidi urefu wa maandishi yoyote kwenye data ya chanzo)
   
2. Imetazamwa_vekta - safu au safu ambapo tunatafuta thamani inayotakiwa. Hapa kuna kazi iliyoletwa hapo awali KUTAFUTA, ambayo inarudisha safu {#VALUE!:4:#VALUE!}
   
3. Vekta_matokeo – masafa ambayo tunataka kurudisha thamani kutoka kwayo ikiwa thamani inayotakiwa inapatikana katika kisanduku sambamba. Hapa kuna majina sahihi kutoka kwa safu Msaada meza yetu ya kumbukumbu.
   

Kipengele kikuu na kisicho wazi hapa ni kwamba kazi View ikiwa hakuna inayolingana kabisa, kila wakati hutafuta thamani ndogo iliyo karibu zaidi (iliyotangulia).. Kwa hivyo, kwa kutaja nambari yoyote kubwa (kwa mfano, 9999) kama dhamana inayotakiwa, tutalazimisha View tafuta kisanduku chenye nambari ndogo iliyo karibu zaidi (4) katika safu {#VALUE!:4:#VALUE!} na urudishe thamani inayolingana kutoka kwa vekta ya matokeo, yaani, jina sahihi la kampuni kutoka kwenye safu wima. Msaada.

Nuance ya pili ni kwamba, kitaalam, formula yetu ni fomula ya safu, kwa sababu kazi KUTAFUTA inarudi kama matokeo sio moja, lakini safu ya maadili matatu. Lakini tangu kazi View inasaidia safu nje ya kisanduku, basi sio lazima tuweke fomula hii kama fomula ya safu ya kawaida - kwa kutumia njia ya mkato ya kibodi. Ctrl+Kuhama+kuingia. Moja rahisi itatosha kuingia.

Ni hayo tu. Natumai utapata mantiki.

Inabakia kuhamisha fomula iliyokamilishwa kwa seli ya kwanza B2 ya safu Fasta - na kazi yetu imetatuliwa!

Kwa kweli, na meza za kawaida (sio smart), fomula hii pia inafanya kazi vizuri (usisahau tu ufunguo F4 na kurekebisha viungo vinavyofaa):

Kesi 2. Uingizwaji wa sehemu ya wingi

Kesi hii ni gumu kidogo. Tena tuna meza mbili "smart":

Jedwali la kwanza lililo na anwani zilizoandikwa kwa upotovu ambazo zinahitaji kusahihishwa (niliita Takwimu2) Jedwali la pili ni kitabu cha kumbukumbu, kulingana na ambayo unahitaji kufanya uingizwaji wa sehemu ndogo ndani ya anwani (niliita jedwali hili. Badala2).

Tofauti ya kimsingi hapa ni kwamba unahitaji kubadilisha kipande tu cha data asili - kwa mfano, anwani ya kwanza ina anwani isiyo sahihi. “St. Petersburg” juu ya haki ya “St. Petersburg”, ukiacha anwani iliyobaki (msimbo wa zip, barabara, nyumba) kama ilivyo.

Fomula iliyokamilishwa itaonekana kama hii (kwa urahisi wa mtizamo, niliigawanya katika mistari ngapi inayotumia Alt+kuingia):

Kazi kuu hapa inafanywa na kazi ya maandishi ya kawaida ya Excel MBADALA (BADALA), ambayo ina hoja 3:

1. Maandishi ya chanzo - anwani ya kwanza iliyopotoka kutoka safu ya Anwani
2. Tunachotafuta - hapa tunatumia hila na kazi View (TAFUTA; TAZAMA JUU)kutoka kwa njia ya awali ya kuvuta thamani kutoka kwa safu Kutafuta, ambayo imejumuishwa kama kipande katika anwani iliyopinda.
3. Nini cha kuchukua nafasi - kwa njia ile ile tunapata thamani sahihi inayofanana nayo kutoka kwenye safu Msaada.

Ingiza fomula hii na Ctrl+Kuhama+kuingia haihitajiki hapa pia, ingawa, kwa kweli, ni fomula ya safu.

Na inaonekana wazi (angalia makosa ya #N/A kwenye picha iliyotangulia) kwamba fomula kama hiyo, kwa uzuri wake wote, ina shida kadhaa:

• kazi SUBSTITUTE ni nyeti kwa kesi, kwa hivyo "Spb" kwenye mstari wa mwisho haukupatikana kwenye jedwali la uingizwaji. Ili kutatua tatizo hili, unaweza kutumia kazi ZAMENIT (BADILISHA), au kuleta majedwali yote mawili kwa rejista moja.
• Ikiwa maandishi ni sahihi mwanzoni au ndani yake hakuna kipande cha kuchukua nafasi (mstari wa mwisho), kisha fomula yetu inatupa makosa. Wakati huu unaweza kubadilishwa kwa kuingilia na kubadilisha hitilafu kwa kutumia chaguo la kukokotoa IFERRO (IFERROR):

  

• Ikiwa maandishi asilia yana vipande kadhaa kutoka kwa saraka mara moja, basi formula yetu inachukua nafasi ya mwisho tu (katika mstari wa 8, Ligovsky «Avenue« iliyopita kuwa "pr-t", Lakini "S-Pb" on “St. Petersburg” tena, kwa sababu "S-Pb” iko juu zaidi kwenye saraka). Tatizo hili linaweza kutatuliwa kwa kukimbia tena formula yetu wenyewe, lakini tayari kwenye safu Fasta:

  
  

Sio kamili na ngumu katika maeneo, lakini bora zaidi kuliko uingizwaji sawa wa mwongozo, sivyo? 🙂

PS

Katika makala inayofuata, tutajua jinsi ya kutekeleza uingizwaji wa wingi kwa kutumia macros na Swala la Nguvu.

• Jinsi kitendakazi SUBSTITUTE kinavyofanya kazi ili kubadilisha maandishi
  
• Kutafuta Maandishi Yanayolingana Kwa Kutumia Utendaji HALISI
• Utafutaji nyeti wa kesi na uingizwaji (VLOOKUP nyeti kwa kesi)