Masinis teksto keitimas formulėmis

Tarkime, kad turite sąrašą, kuriame su skirtingu „tiesumu“ įrašomi pradiniai duomenys, pavyzdžiui, adresai arba įmonių pavadinimai:

Aiškiai matosi, kad čia yra tas pats miestas ar įmonė margais variantais, kas, aišku, ateityje sukels daug problemų dirbant su šiomis lentelėmis. O šiek tiek pagalvojus galima rasti daug panašių užduočių pavyzdžių iš kitų sričių.

Dabar įsivaizduokite, kad tokie iškreipti duomenys jums ateina reguliariai, ty tai ne vienkartinė „pataisyk rankiniu būdu, pamiršk“ istorija, o nuolatinė ir daugybės langelių problema.

Ką daryti? Rankiniu būdu nekeiskite kreivo teksto 100500 kartų tinkamu per laukelį „Rasti ir pakeisti“ arba spustelėdami "Ctrl"+H?

Pirmas dalykas, kuris ateina į galvą tokioje situacijoje, yra atlikti masinį pakeitimą pagal iš anksto sudarytą žinyną, kuriame pateikiamos netinkamos ir teisingos parinktys, pavyzdžiui:

Deja, akivaizdžiai vyraujant tokiai užduočiai, „Microsoft Excel“ neturi paprastų integruotų metodų, kaip ją išspręsti. Pirmiausia išsiaiškinkime, kaip tai padaryti naudojant formules, nenaudojant „sunkiosios artilerijos“ makrokomandų pavidalu VBA ar „Power Query“.

1 atvejis. Masinis pilnas pakeitimas

Pradėkime nuo gana paprasto atvejo – situacijos, kai reikia pakeisti seną kreivą tekstą nauju. visiškai.

Tarkime, kad turime dvi lenteles:

Pirmajame – originalūs margi įmonių pavadinimai. Antroje – korespondencijos žinynas. Jei rasime įmonės pavadinime pirmoje lentelėje bet kurį žodį iš stulpelio Rasti, tuomet reikia visiškai pakeisti šį kreivą pavadinimą teisingu – iš stulpelio Pavaduojantis narys antroji paieškos lentelė.

Patogumui:

• Abi lentelės konvertuojamos į dinamines („išmaniąsias“) naudojant sparčiuosius klavišus "Ctrl"+T arba komanda Įterpimas – lentelė (Įterpti – lentelė).
  
• Pasirodžiusiame skirtuke Konstruktorius (Dizainas) pirmoji lentelė pavadinta Duomenys, o antroji nuorodų lentelė – keitimai.

Norėdami paaiškinti formulės logiką, nueikime šiek tiek iš tolo.

Kaip pavyzdį paimdami pirmąją įmonę iš langelio A2 ir laikinai pamiršdami apie likusias įmones, pabandykime nustatyti, kuri parinktis iš stulpelio Rasti ten susitinka. Norėdami tai padaryti, pasirinkite bet kurį tuščią langelį laisvoje lapo dalyje ir įveskite ten funkciją RASTI (RASTI):

Ši funkcija nustato, ar įtraukta nurodyta poeilutė (pirmasis argumentas yra visos stulpelio reikšmės Rasti) į šaltinio tekstą (pirmoji įmonė iš duomenų lentelės) ir turėtų išvesti arba simbolio, iš kurio buvo rastas tekstas, eilės numerį arba klaidą, jei poeilutė nerasta.

Apgaulė yra ta, kad kadangi kaip pirmąjį argumentą nurodėme ne vieną, o kelias reikšmes, ši funkcija taip pat grąžins ne vieną reikšmę, o 3 elementų masyvą. Jei neturite naujausios Office 365 versijos, palaikančios dinaminius masyvus, tada įvedę šią formulę ir spustelėję įeiti pamatysite šį masyvą tiesiai lape:

Jei turite ankstesnes Excel versijas, tada spustelėję įeiti matysime tik pirmąją reikšmę iš rezultatų masyvo, ty klaidą #VALUE! (#VALUE!).

Nereikėtų bijoti F9(tik nepamirškite paspausti escgrįžti prie formulės):

Gautas rezultatų masyvas reiškia, kad originalus kreivas įmonės pavadinimas (GK Morozko OAO) visų verčių stulpelyje Rasti rado tik antrą (Morozkas), ir pradedant nuo 4 simbolio iš eilės.

Dabar prie formulės pridėkime funkciją VAIZDAS(LOOKUP):

Ši funkcija turi tris argumentus:

1. Norima vertė - galite naudoti bet kokį pakankamai didelį skaičių (svarbiausia, kad jis viršytų bet kurio šaltinio duomenų teksto ilgį)
   
2. Peržiūrėtas_vektorius – diapazonas arba masyvas, kuriame ieškome norimos reikšmės. Čia yra anksčiau pristatyta funkcija RASTI, kuris grąžina masyvą {#VALUE!:4:#VALUE!}
   
3. Vektorius_rezultatai – diapazonas, iš kurio norime grąžinti reikšmę, jei norima reikšmė randama atitinkamame langelyje. Štai teisingi vardai iš stulpelio Pavaduojantis narys mūsų nuorodų lentelė.
   

Pagrindinis ir neakivaizdus bruožas yra ta funkcija VAIZDAS jei tikslios atitikties nėra, visada ieškoma artimiausios mažiausios (ankstesnės) reikšmės. Todėl kaip norimą reikšmę nurodydami bet kokį didelį skaičių (pavyzdžiui, 9999), priversime VAIZDAS masyve {#VALUE!:4:#VALUE!} suraskite langelį su artimiausiu mažiausiu skaičiumi (4) ir grąžinkite atitinkamą reikšmę iš rezultatų vektoriaus, ty teisingą įmonės pavadinimą iš stulpelio Pavaduojantis narys.

Antras niuansas yra tas, kad techniškai mūsų formulė yra masyvo formulė, nes funkcija RASTI kaip rezultatus pateikia ne vieną, o trijų reikšmių masyvą. Bet kadangi funkcija VAIZDAS palaiko masyvus iš karto, tada mums nereikia įvesti šios formulės kaip klasikinės masyvo formulės – naudojant sparčiuosius klavišus "Ctrl"+perėjimas+įeiti. Užteks paprasto įeiti.

Tai viskas. Tikiuosi supratai logiką.

Belieka perkelti gatavą formulę į pirmąjį stulpelio langelį B2 Fiksuotas – ir mūsų užduotis išspręsta!

Žinoma, su įprastomis (ne išmaniosiomis) lentelėmis ši formulė taip pat puikiai veikia (tik nepamirškite apie raktą). F4 ir pataisyti atitinkamas nuorodas):

2 atvejis. Masinis dalinis pakeitimas

Šis atvejis yra šiek tiek sudėtingesnis. Vėl turime dvi „protingas“ lenteles:

Pirmoji lentelė su kreivai parašytais adresais, kurią reikia taisyti (aš ją pavadinau Duomenys2). Antroji lentelė yra žinynas, pagal kurį reikia iš dalies pakeisti adreso viduje esančią eilutę (aš pavadinau šią lentelę Pakeitimai2).

Esminis skirtumas yra tas, kad reikia pakeisti tik pradinių duomenų fragmentą – pavyzdžiui, pirmasis adresas yra neteisingas „Šv. Peterburgas“ dešinėje „Šv. Peterburgas“, likusią adreso dalį (pašto indeksą, gatvę, namą) palikdami tokį, koks yra.

Baigta formulė atrodys taip (kad būtų lengviau suvokti, suskirstiau ją į eilučių skaičių Kitas+įeiti):

Pagrindinį darbą čia atlieka standartinė Excel teksto funkcija PAKEITIMAS (PAKEIT.), kuris turi 3 argumentus:

1. Šaltinio tekstas – pirmas kreivas adresas iš stulpelio Adresas
2. Ko mes ieškome – čia naudojame triuką su funkcija VAIZDAS (LOOKUP)iš ankstesnio būdo ištraukti vertę iš stulpelio Rasti, kuris įtrauktas kaip fragmentas į kreivąjį adresą.
3. Kuo pakeisti – lygiai taip pat iš stulpelio randame ją atitinkančią teisingą reikšmę Pavaduojantis narys.

Įveskite šią formulę su "Ctrl"+perėjimas+įeiti čia taip pat nereikia, nors iš tikrųjų tai yra masyvo formulė.

Ir aiškiai matyti (žr. #N/A klaidas ankstesniame paveikslėlyje), kad tokia formulė, nepaisant visos savo elegancijos, turi keletą trūkumų:

• Funkcija SUBSTITUTE yra didžiosios ir mažosios raidės, todėl priešpaskutinėje eilutėje esantis „Spb“ pakeitimo lentelėje nerastas. Norėdami išspręsti šią problemą, galite naudoti šią funkciją ZAMENITAS (PAKEISTI), arba preliminariai įtraukite abi lenteles į tą patį registrą.
• Jei tekstas iš pradžių teisingas arba jame nėra fragmento, kurį būtų galima pakeisti (paskutinė eilutė), tada mūsų formulė pateikia klaidą. Šį momentą galima neutralizuoti perimant ir pakeičiant klaidas naudojant funkciją IFERROR (IFERROR):

  

• Jei originaliame tekste yra kelis fragmentus iš katalogo vienu metu, tada mūsų formulė pakeičia tik paskutinę (8-oje eilutėje, Ligovskis «Prospektas« pakeista į "pr-t", Bet "S-Pb" on „Šv. Peterburgas“ nebe, nes „S-Pb“ yra aukščiau kataloge). Šią problemą galima išspręsti iš naujo paleidus savo formulę, bet jau išilgai stulpelio Fiksuotas:

  
  

Ne tobula ir vietomis sudėtinga, bet daug geriau nei tas pats rankinis pakeitimas, tiesa? 🙂

PS

Kitame straipsnyje išsiaiškinsime, kaip įgyvendinti tokį masinį pakeitimą naudojant makrokomandas ir „Power Query“.

• Kaip veikia funkcija SUBSTITUTE pakeičiant tekstą
  
• Tikslių teksto atitikmenų paieška naudojant EXACT funkciją
• Didžiųjų ir mažųjų raidžių paieška ir keitimas (VLOOKUP)