Skupna zamjena teksta formulama

Pretpostavimo da imate listu u kojoj su, sa različitim stepenom „jednostavnosti“, upisani početni podaci – na primer, adrese ili nazivi kompanija:

Jasno se vidi da je isti grad ili kompanija ovde prisutan u šarolikim varijantama, što će, očigledno, stvarati dosta problema pri radu sa ovim stolovima u budućnosti. A ako malo razmislite, možete pronaći mnogo primjera sličnih zadataka iz drugih područja.

Zamislite sad da vam ovako iskrivljeni podaci dolaze redovno, odnosno ovo nije jednokratna priča „ručno popravi, zaboravi“, već problem na redovnoj osnovi i u velikom broju ćelija.

šta da radim? Nemojte ručno zamijeniti iskrivljeni tekst 100500 puta ispravnim putem okvira „Pronađi i zamijeni“ ili klikom na Ctrl+H?

Prvo što vam padne na pamet u takvoj situaciji je da se izvrši masovna zamjena prema unaprijed sastavljenoj referentnoj knjizi podudaranja netačnih i tačnih opcija – ovako:

Nažalost, s očitom rasprostranjenošću takvog zadatka, Microsoft Excel nema jednostavne ugrađene metode za njegovo rješavanje. Za početak, hajde da shvatimo kako to učiniti sa formulama, bez uključivanja "teške artiljerije" u obliku makroa u VBA ili Power Queryju.

Slučaj 1. Masovna potpuna zamjena

Počnimo s relativno jednostavnim slučajem – situacijom u kojoj morate zamijeniti stari iskrivljeni tekst novim. potpuno.

Recimo da imamo dvije tabele:

U prvom – originalna šarolika imena kompanija. U drugom – referentna knjiga prepiske. Ako nađemo u nazivu kompanije u prvoj tabeli bilo koju riječ iz kolone Naći, onda morate potpuno zamijeniti ovo krivo ime ispravnim – iz kolone Zamjenik druga tabela pregleda.

Za praktičnost:

• Obje tabele se konvertuju u dinamičke („pametne“) pomoću prečice na tastaturi Ctrl+T ili tim Umetak – Tabela (Insert — Tabela).
  
• Na kartici koja se pojavi konstruktor (Dizajn) prva tabela imenovana podaci, i druga referentna tabela – Zamjene.

Da bismo objasnili logiku formule, idemo malo izdaleka.

Uzimajući za primjer prvu kompaniju iz ćelije A2 i privremeno zaboravljajući na ostale kompanije, pokušajmo odrediti koja opcija iz kolone Naći sastaje se tamo. Da biste to učinili, odaberite bilo koju praznu ćeliju u slobodnom dijelu lista i tamo unesite funkciju NAĆI (PRONAĐI):

Ova funkcija određuje da li je dati podniz uključen (prvi argument su sve vrijednosti iz stupca Naći) u izvorni tekst (prva kompanija iz tabele podataka) i treba da ispiše ili redni broj znaka iz kojeg je tekst pronađen, ili grešku ako podniz nije pronađen.

Trik je u tome što, budući da smo naveli ne jednu, već nekoliko vrijednosti kao prvi argument, ova funkcija će također vratiti kao rezultat ne jednu vrijednost, već niz od 3 elementa. Ako nemate najnoviju verziju Office 365 koja podržava dinamičke nizove, onda nakon što unesete ovu formulu i kliknete na ući videćete ovaj niz pravo na listu:

Ako imate prethodne verzije Excela, onda nakon klika na ući vidjet ćemo samo prvu vrijednost iz niza rezultata, tj. grešku #VRIJEDNOST! (#VRIJEDNOST!).

Ne biste se trebali bojati 🙂 Zapravo, naša formula funkcionira i još uvijek možete vidjeti cijeli niz rezultata ako odaberete unesenu funkciju u traci formule i pritisnete tipku F9(samo ne zaboravite da pritisnete Escda se vratim na formulu):

Rezultirajući niz rezultata znači da je u originalnom krivom nazivu kompanije (GK Morozko OAO) svih vrijednosti u koloni Naći našao samo drugu (Morozko), i počevši od 4. znaka u nizu.

Sada dodajmo funkciju našoj formuli VIEW(POGLEDAJ GORE):

Ova funkcija ima tri argumenta:

1. Željena vrijednost – možete koristiti bilo koji dovoljno veliki broj (glavno je da premašuje dužinu bilo kojeg teksta u izvornim podacima)
   
2. Viewed_vector – opseg ili niz u kojem tražimo željenu vrijednost. Ovdje je prethodno uvedena funkcija NAĆI, koji vraća niz {#VALUE!:4:#VALUE!}
   
3. Vector_rezultati – raspon iz kojeg želimo vratiti vrijednost ako se željena vrijednost nađe u odgovarajućoj ćeliji. Evo tačnih imena iz kolone Zamjenik naša referentna tabela.
   

Glavna i neočigledna karakteristika ovdje je ta funkcija VIEW ako nema tačnog podudaranja, uvijek traži najbližu najmanju (prethodnu) vrijednost. Stoga, specificiranjem bilo kojeg velikog broja (na primjer, 9999) kao željene vrijednosti, prisilit ćemo VIEW pronađite ćeliju sa najbližim najmanjim brojem (4) u nizu {#VALUE!:4:#VALUE!} i vratite odgovarajuću vrijednost iz vektora rezultata, odnosno ispravan naziv kompanije iz kolone Zamjenik.

Druga nijansa je da je, tehnički, naša formula formula niza, jer funkcija NAĆI vraća kao rezultate ne jednu, već niz od tri vrijednosti. Ali pošto je funkcija VIEW podržava nizove iz kutije, onda ne moramo unositi ovu formulu kao klasičnu formulu niza - koristeći prečicu na tipkovnici Ctrl+smjena+ući. Jednostavna će biti dovoljna ući.

To je sve. Nadam se da ste shvatili logiku.

Ostaje prenijeti gotovu formulu u prvu ćeliju B2 kolone fiksni – i naš zadatak je riješen!

Naravno, sa običnim (ne pametnim) tablicama ova formula također funkcionira odlično (samo ne zaboravite na ključ F4 i popravljanje relevantnih linkova):

Slučaj 2. Masovna djelomična zamjena

Ovaj slučaj je malo teži. Opet imamo dva "pametna" stola:

Prva tabela sa krivo ispisanim adresama koje treba ispraviti (ja sam je nazvao Podaci2). Druga tabela je referentna knjiga, prema kojoj treba da izvršite delimičnu zamenu podniza unutar adrese (ja sam ovu tabelu nazvao Zamjene2).

Osnovna razlika je u tome što trebate zamijeniti samo dio originalnih podataka – na primjer, prva adresa ima netačan “St. Petersburg” desno “St. Petersburg”, ostavljajući ostatak adrese (poštanski broj, ulica, kuća) kakav jeste.

Gotova formula će izgledati ovako (radi lakše percepcije, podijelio sam je na koliko linija koristim alt+ući):

Glavni posao ovdje obavlja standardna Excel funkcija teksta SUBSTITUTE (ZAMJENA), koji ima 3 argumenta:

1. Izvorni tekst – prva kriva adresa iz kolone Adresa
2. Ono što tražimo – ovdje koristimo trik s funkcijom VIEW (POGLEDAJ GORE)sa prethodnog načina za povlačenje vrijednosti iz kolone Naći, koji je uključen kao fragment u zakrivljenoj adresi.
3. Čime zamijeniti – na isti način iz kolone nalazimo odgovarajuću vrijednost koja joj odgovara Zamjenik.

Unesite ovu formulu sa Ctrl+smjena+ući nije potrebna ni ovdje, iako je, u stvari, formula niza.

I jasno se vidi (pogledajte #N/A greške na prethodnoj slici) da takva formula, uz svu svoju eleganciju, ima nekoliko nedostataka:

• funkcija SUBSTITUTE razlikuje velika i mala slova, tako da "Spb" u pretposljednjem redu nije pronađen u zamjenskoj tabeli. Da biste riješili ovaj problem, možete koristiti ili funkciju ZAMENIT (ZAMJENA), ili preliminarno dovesti obje tabele u isti registar.
• Ako je tekst u početku tačan ili u njemu nema fragmenta za zamjenu (poslednji red), tada naša formula daje grešku. Ovaj trenutak se može neutralizirati presretanjem i zamjenom grešaka pomoću funkcije IFERROR (UKOLIKO GREŠKA):

  

• Ako originalni tekst sadrži nekoliko fragmenata iz direktorija odjednom, tada naša formula zamjenjuje samo posljednju (u 8. redu, Ligovsky «avenija« promijenio u “pr-t”, Ali “S-Pb” on “St. Petersburg” ne više, jer “S-Pb” je više u imeniku). Ovaj problem se može riješiti ponovnim pokretanjem vlastite formule, ali već duž kolone fiksni:

  
  

Nije savršeno i glomazno na mjestima, ali mnogo bolje od iste ručne zamjene, zar ne? 🙂

PS

U sljedećem članku ćemo shvatiti kako implementirati takvu masovnu zamjenu koristeći makroe i Power Query.

• Kako funkcija SUBSTITUTE radi za zamjenu teksta
  
• Pronalaženje tačnih podudaranja teksta pomoću funkcije EXACT
• Pretraživanje i zamjena osjetljiv na velika i mala slova (VLOOKUP osjetljiv na velika i mala slova)