Lielapjoma teksta aizstāšana ar formulām

Pieņemsim, ka jums ir saraksts, kurā ar dažādu "tiešuma" pakāpi ir ierakstīti sākotnējie dati, piemēram, adreses vai uzņēmumu nosaukumi:

Skaidri redzams, ka šeit ir viena un tā pati pilsēta vai uzņēmums raibos variantos, kas, acīmredzot, nākotnē sagādās daudz problēmu, strādājot ar šīm tabulām. Un, ja jūs mazliet padomājat, jūs varat atrast daudz līdzīgu uzdevumu piemēru no citām jomām.

Tagad iedomājieties, ka šādi greizi dati pie jums nāk regulāri, proti, tas nav vienreizējs stāsts “manuāli labo, aizmirsti”, bet gan regulāri un lielā skaitā šūnu problēma.

Ko darīt? Neaizvietojiet 100500 XNUMX reizes manuāli izliekto tekstu ar pareizo, izmantojot lodziņu “Atrast un aizstāt” vai noklikšķinot uz Ctrl+H?

Pirmā lieta, kas nāk prātā šādā situācijā, ir veikt masveida nomaiņu saskaņā ar iepriekš sastādītu nepareizu un pareizu opciju saskaņošanas uzziņu grāmatu, piemēram:

Diemžēl ar acīmredzamu šāda uzdevuma izplatību Microsoft Excel nav vienkāršu iebūvētu metožu tā risināšanai. Sākumā izdomāsim, kā to izdarīt ar formulām, neiesaistot “smago artilēriju” makro veidā VBA vai Power Query.

1. gadījums. Lielapjoma pilna nomaiņa

Sāksim ar salīdzinoši vienkāršu gadījumu – situāciju, kad vecais greizais teksts ir jāaizstāj ar jaunu. pilnīgi.

Pieņemsim, ka mums ir divas tabulas:

Pirmajā – uzņēmumu oriģinālie raibi nosaukumi. Otrajā – korespondences uzziņu grāmata. Ja uzņēmuma nosaukumā pirmajā tabulā atrodam jebkuru vārdu no kolonnas Atrast, tad šis greizais nosaukums pilnībā jāaizstāj ar pareizo – no kolonnas Aizstājējs otrā uzmeklēšanas tabula.

Ērtībām:

• Abas tabulas tiek pārveidotas par dinamiskām (“viedajām”), izmantojot īsinājumtaustiņus Ctrl+T vai komanda Ievietojums – tabula (Ievietot — tabula).
  
• Parādītajā cilnē Konstruktors (Dizains) pirmā tabula nosaukta Datums, un otrā atsauces tabula – Aizstāšanu.

Lai izskaidrotu formulas loģiku, iesim mazliet no tālienes.

Ņemot par piemēru pirmo uzņēmumu no šūnas A2 un īslaicīgi aizmirstot par pārējiem uzņēmumiem, mēģināsim kolonnā noteikt, kura opcija Atrast satiekas tur. Lai to izdarītu, lapas brīvajā daļā atlasiet jebkuru tukšu šūnu un ievadiet tur funkciju ATRAST (Atrast):

Šī funkcija nosaka, vai dotā apakšvirkne ir iekļauta (pirmais arguments ir visas vērtības no kolonnas Atrast) avota tekstā (pirmais uzņēmums no datu tabulas), un tam ir jāizvada vai nu rakstzīmes kārtas numurs, no kuras teksts tika atrasts, vai kļūda, ja apakšvirkne netika atrasta.

Šeit triks ir tāds, ka, tā kā mēs kā pirmo argumentu norādījām nevis vienu, bet vairākas vērtības, šī funkcija arī atgriezīs nevis vienu vērtību, bet gan 3 elementu masīvu. Ja jums nav jaunākās Office 365 versijas, kas atbalsta dinamiskos masīvus, tad pēc šīs formulas ievadīšanas un noklikšķināšanas uz ienākt jūs redzēsiet šo masīvu tieši uz lapas:

Ja jums ir iepriekšējās Excel versijas, tad pēc noklikšķināšanas uz ienākt mēs redzēsim tikai pirmo vērtību no rezultātu masīva, ti, kļūdu #VALUE! (#VALUE!).

Jums nevajadzētu baidīties 🙂 Patiesībā mūsu formula darbojas, un jūs joprojām varat redzēt visu rezultātu masīvu, ja formulu joslā atlasāt ievadīto funkciju un nospiežat taustiņu F9(tikai neaizmirstiet nospiest Esclai atgrieztos pie formulas):

Iegūtais rezultātu masīvs nozīmē, ka sākotnējā greizajā uzņēmuma nosaukumā (GK Morozko OAO) no visām vērtībām kolonnā Atrast atrada tikai otro (Morozko), un sākot no 4. rakstzīmes pēc kārtas.

Tagad pievienosim mūsu formulai funkciju SKATĪT(LOOKUP):

Šai funkcijai ir trīs argumenti:

1. Vēlamā vērtība - varat izmantot jebkuru pietiekami lielu skaitli (galvenais, lai tas pārsniedz jebkura teksta garumu avota datos)
   
2. Skatītais_vektors – diapazons vai masīvs, kurā mēs meklējam vēlamo vērtību. Šeit ir iepriekš ieviestā funkcija ATRAST, kas atgriež masīvu {#VALUE!:4:#VALUE!}
   
3. Vector_rezultāti – diapazons, no kura vēlamies atgriezt vērtību, ja attiecīgajā šūnā ir atrasta vēlamā vērtība. Šeit ir pareizie nosaukumi no kolonnas Aizstājējs mūsu atsauces tabula.
   

Galvenā un acīmredzamā iezīme šeit ir funkcija SKATĪT ja nav precīzas atbilstības, vienmēr meklē tuvāko mazāko (iepriekšējo) vērtību. Tāpēc, norādot jebkuru lielu skaitli (piemēram, 9999) kā vēlamo vērtību, mēs piespiedīsim SKATĪT masīvā {#VALUE!:4:#VALUE!} atrodiet šūnu ar tuvāko mazāko skaitli (4) un atgrieziet atbilstošo vērtību no rezultāta vektora, ti, pareizi uzņēmuma nosaukumu no kolonnas Aizstājējs.

Otra nianse ir tāda, ka tehniski mūsu formula ir masīva formula, jo funkcija ATRAST atgriež kā rezultātus nevis vienu, bet trīs vērtību masīvu. Bet tā kā funkcija SKATĪT atbalsta masīvus no kastes, tad šī formula nav jāievada kā klasiska masīva formula — izmantojot īsinājumtaustiņu Ctrl+maiņa+ienākt. Pietiks ar vienkāršu ienākt.

Tas ir viss. Cerams, ka saprati loģiku.

Atliek gatavo formulu pārsūtīt uz kolonnas pirmo šūnu B2 Fiksētie – un mūsu uzdevums ir atrisināts!

Protams, ar parastajām (ne gudrajām) tabulām šī formula darbojas arī lieliski (tikai neaizmirstiet par atslēgu). F4 un labojot attiecīgās saites):

2. gadījums. Lielapjoma daļēja nomaiņa

Šis gadījums ir nedaudz sarežģītāks. Atkal mums ir divas “gudrās” tabulas:

Pirmā tabula ar greizi rakstītām adresēm, kas jālabo (es to nosaucu Dati2). Otrā tabula ir uzziņu grāmata, saskaņā ar kuru adreses iekšpusē ir daļēji jāaizstāj apakšvirkne (es saucu šo tabulu Aizstāšana2).

Būtiskā atšķirība šeit ir tāda, ka jāaizstāj tikai fragments no oriģinālajiem datiem – piemēram, pirmajā adresē ir nepareiza “Sv. Pēterburga” uz labo “Sv. Pēterburga”, atstājot pārējo adresi (pasta indekss, iela, māja) tādu, kāda tā ir.

Gatavā formula izskatīsies šādi (lai atvieglotu uztveri, es to sadalīju pa rindiņām, izmantojot cits+ienākt):

Šeit galveno darbu veic standarta Excel teksta funkcija Aizstāt (AIZSTĀTĀJS), kam ir 3 argumenti:

1. Avota teksts – pirmā greizā adrese no ailes Adrese
2. Ko mēs meklējam – šeit mēs izmantojam triku ar funkciju SKATĪT (LOOKUP)no iepriekšējā veida, lai izvilktu vērtību no kolonnas Atrast, kas ir iekļauts kā fragments izliektā adresē.
3. Ar ko aizstāt – tādā pašā veidā no ailes atrodam tai atbilstošo pareizo vērtību Aizstājējs.

Ievadiet šo formulu ar Ctrl+maiņa+ienākt arī šeit nav vajadzīga, lai gan patiesībā tā ir masīva formula.

Un ir skaidri redzams (sk. #N/A kļūdas iepriekšējā attēlā), ka šādai formulai, neskatoties uz visu savu eleganci, ir pāris trūkumi:

• funkcija SUBSTITUTE ir reģistrjutīgs, tāpēc priekšpēdējā rindā esošais “Spb” nomaiņas tabulā netika atrasts. Lai atrisinātu šo problēmu, varat izmantot funkciju ZAMENIT (AIZVIETOT), vai provizoriski apvienojiet abas tabulas vienā reģistrā.
• Ja teksts sākotnēji ir pareizs vai tajā nav neviena fragmenta, ko aizstāt (pēdējā rinda), tad mūsu formula rada kļūdu. Šo brīdi var neitralizēt, pārtverot un aizstājot kļūdas, izmantojot funkciju IFERROR (IFERROR):

  

• Ja oriģinālajā tekstā ir vairākus fragmentus no direktorija vienlaikus, tad mūsu formula aizstāj tikai pēdējo (8. rindā Ligovskis «Avēnija« mainīts uz "pr-t", Bet “S-Pb” on “Sv. Pēterburga” vairs ne, jo “S-Pb” atrodas augstāk direktorijā). Šo problēmu var atrisināt, atkārtoti izpildot mūsu pašu formulu, bet jau gar kolonnu Fiksētie:

  
  

Vietām nav ideāls un apgrūtinošs, bet daudz labāks par to pašu manuālo nomaiņu, vai ne? 🙂

PS

Nākamajā rakstā mēs izdomāsim, kā ieviest šādu lielapjoma aizstāšanu, izmantojot makro un Power Query.

• Kā darbojas funkcija SUBSTITUTE, lai aizstātu tekstu
  
• Precīzu teksta atbilstību meklēšana, izmantojot funkciju EXACT
• Reģistrjutīga meklēšana un aizstāšana (reģistrjutīga VLOOKUP)