Skupna zamjena teksta formulama

Pretpostavimo da imate popis na kojem su, s različitim stupnjevima "jednostavnosti", ispisani početni podaci - na primjer, adrese ili nazivi tvrtki:

Jasno se vidi da je isti grad ili tvrtka prisutna ovdje u šarolikim varijantama, što će, očito, u budućnosti stvarati dosta problema pri radu s ovim tablicama. A ako malo razmislite, možete pronaći puno primjera sličnih zadataka iz drugih područja.

Zamislite sada da vam takvi krivi podaci dolaze redovito, tj. ovo nije jednokratna priča “ručno popravi, zaboravi”, već problem redovito i u velikom broju ćelija.

Što učiniti? Nemojte ručno zamijeniti iskrivljeni tekst 100500 puta ispravnim putem okvira "Pronađi i zamijeni" ili klikom Ctrl+H?

Prva stvar koja pada na pamet u takvoj situaciji je izvršiti masovnu zamjenu prema unaprijed sastavljenom priručniku podudaranja netočnih i točnih opcija - ovako:

Nažalost, uz očitu prevalenciju takvog zadatka, Microsoft Excel nema jednostavne ugrađene metode za njegovo rješavanje. Za početak, shvatimo kako to učiniti pomoću formula, bez uključivanja “teške artiljerije” u obliku makronaredbi u VBA ili Power Queryju.

Slučaj 1. Skupna puna zamjena

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

Recimo da imamo dvije tablice:

U prvom – originalna šarolika imena tvrtki. U drugom – priručnik za korespondenciju. Ako u nazivu poduzeća u prvoj tablici pronađemo bilo koju riječ iz stupca Pronaći, onda ovaj krivi naziv trebate potpuno zamijeniti ispravnim – iz stupca Zamjena drugu tablicu pretraživanja.

Za praktičnost:

• Obje se tablice pretvaraju u dinamičke ("pametne") pomoću prečaca na tipkovnici Ctrl+T ili tim Umetak – Tablica (Umetak — Tablica).
  
• Na kartici koja se pojavi Konstruktor (Oblikovati) prva tablica imenovana Datum, a druga referentna tablica – Zamjene.

Da objasnimo logiku formule, idemo malo izdaleka.

Uzimajući za primjer prvu tvrtku iz ćelije A2 i privremeno zaboravivši na ostale tvrtke, pokušajmo odrediti koju opciju iz stupca Pronaći sastaje se tamo. Da biste to učinili, odaberite bilo koju praznu ćeliju u slobodnom dijelu lista i tamo unesite funkciju PRONAĆI (PRONAĆI):

Ova funkcija određuje je li dani podniz uključen (prvi argument su sve vrijednosti iz stupca Pronaći) u izvorni tekst (prva tvrtka iz podatkovne tablice) i trebala bi ispisati ili redni broj znaka iz kojeg je pronađen tekst ili pogrešku ako podniz nije pronađen.

Trik je ovdje 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 sustava Office 365 koja podržava dinamička polja, tada nakon unosa ove formule i klika na ući vidjet ćete ovaj niz točno na listu:

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

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

Dobiveni niz rezultata znači da je u originalnom krivom nazivu tvrtke (GK Morozko OAO) svih vrijednosti u koloni Pronaći našla samo drugu (Morozko), a počevši od 4. znaka u nizu.

Dodajmo sada funkciju našoj formuli POGLED(POGLEDAJ):

Ova funkcija ima tri argumenta:

1. Željena vrijednost – možete koristiti bilo koji dovoljno velik broj (najvažnije je da prelazi duljinu bilo kojeg teksta u izvornim podacima)
   
2. Viewed_vector – raspon ili niz u kojem tražimo željenu vrijednost. Ovdje je prethodno predstavljena funkcija PRONAĆI, koji vraća niz {#VALUE!:4:#VALUE!}
   
3. Vektor_rezultati – raspon iz kojeg želimo vratiti vrijednost ako se željena vrijednost nalazi u odgovarajućoj ćeliji. Evo točnih imena iz stupca Zamjena našu referentnu tablicu.
   

Glavna i neočita značajka ovdje je da funkcija POGLED ako nema točnog podudaranja, uvijek traži najbližu najmanju (prethodnu) vrijednost. Stoga, navođenjem bilo kojeg velikog broja (na primjer, 9999) kao željene vrijednosti, prisilit ćemo POGLED pronađite ćeliju s najbližim najmanjim brojem (4) u nizu {#VALUE!:4:#VALUE!} i vratite odgovarajuću vrijednost iz vektora rezultata, tj. ispravite naziv tvrtke iz stupca Zamjena.

Druga nijansa je da je, tehnički, naša formula formula polja, jer funkcija PRONAĆI vraća kao rezultat ne jednu, već niz od tri vrijednosti. Ali budući da funkcija POGLED podržava nizove izvan okvira, tada ovu formulu ne moramo unositi kao klasičnu formulu polja – pomoću tipkovničke prečice 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 stupca fiksni – i naš zadatak je riješen!

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

Slučaj 2. Skupna djelomična zamjena

Ovaj slučaj je malo teži. Opet imamo dvije "pametne" tablice:

Prva tablica s krivo napisanim adresama koju treba ispraviti (nazvao sam je Podaci2). Druga tablica je referentna knjiga, prema kojoj trebate izvršiti djelomičnu zamjenu podniza unutar adrese (nazvao sam ovu tablicu Zamjene2).

Temeljna razlika ovdje je u tome što trebate zamijeniti samo fragment izvornih podataka – na primjer, prva adresa ima netočan “Sv. Petersburg” na desnom “Sv. Petersburg”, ostavljajući ostatak adrese (poštanski broj, ulica, kuća) kakav jest.

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

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

1. Izvorni tekst – prva kriva adresa iz stupca Adresa
2. Ono što tražimo – ovdje koristimo trik s funkcijom POGLED (POGLEDAJ)s prethodnog načina za povlačenje vrijednosti iz stupca Pronaći, koji je uključen kao fragment u zakrivljenu adresu.
3. Čime zamijeniti – na isti način pronalazimo odgovarajuću vrijednost iz stupca Zamjena.

Unesite ovu formulu s Ctrl+smjena+ući nije potreban ni ovdje, iako je, zapravo, formula polja.

I jasno se vidi (vidi #N/A pogreške na prethodnoj slici) da takva formula, uza svu svoju eleganciju, ima par nedostataka:

• funkcija SUBSTITUTE je osjetljiv na velika i mala slova, pa “Spb” u pretposljednjem retku nije pronađen u zamjenskoj tablici. Da biste riješili ovaj problem, možete upotrijebiti funkciju ZAMENIT (ZAMIJENITI), ili preliminarno dovesti obje tablice u isti registar.
• Ako je tekst u početku točan ili u njemu nema fragmenta za zamjenu (posljednji red), tada naša formula izbacuje pogrešku. Taj se trenutak može neutralizirati presretanjem i zamjenom pogrešaka pomoću funkcije AKO POGREŠKA (UKOLIKO GREŠKA):

  

• Ako izvorni tekst sadrži nekoliko fragmenata iz imenika odjednom, onda naša formula zamjenjuje samo posljednju (u 8. retku, Ligovsky «avenija« promijenjeno u “pr-t”, Ali “S-Pb” on “Sv. Petersburg” više ne, jer “S-Pb” nalazi se više u imeniku). Ovaj problem se može riješiti ponovnim pokretanjem vlastite formule, ali već duž stupca fiksni:

  
  

Nije savršen i glomazan na mjestima, ali puno bolji od iste ručne zamjene, zar ne? 🙂

PS

U sljedećem ćemo članku otkriti kako implementirati takvu skupnu zamjenu pomoću makronaredbi i Power Queryja.

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