Zamenjava množičnega besedila s formulami

Recimo, da imate seznam, na katerem so z različnimi stopnjami "preprostosti" zapisani začetni podatki - na primer naslovi ali imena podjetij:

Jasno je razvidno, da je tukaj prisotno isto mesto ali podjetje v pestrih variantah, kar bo očitno v prihodnosti povzročalo veliko težav pri delu s temi tabelami. In če malo pomislite, lahko najdete veliko primerov podobnih nalog iz drugih področij.

Sedaj pa si predstavljajte, da vam tako pokvarjeni podatki prihajajo redno, torej ne gre za enkratno zgodbo »ročno popravi, pozabi«, temveč za redno težavo in v velikem številu celic.

Kaj storiti? Ne zamenjajte ročno 100500-krat ukrivljenega besedila s pravilnim prek polja »Najdi in zamenjaj« ali s klikom Ctrl+H?

Prva stvar, ki pride na misel v takšni situaciji, je množična zamenjava v skladu z vnaprej sestavljenim priročnikom ujemajočih se napačnih in pravilnih možnosti – takole:

Na žalost, z očitno razširjenostjo takšne naloge, Microsoft Excel nima preprostih vgrajenih metod za njeno rešitev. Za začetek ugotovimo, kako to storiti s formulami, ne da bi vpletli "težko topništvo" v obliki makrov v VBA ali Power Query.

Primer 1. Popolna množična zamenjava

Začnimo z razmeroma preprostim primerom – situacijo, ko morate staro ukrivljeno besedilo zamenjati z novim. v celoti.

Recimo, da imamo dve tabeli:

V prvem – izvirna pestra imena podjetij. V drugem - referenčna knjiga korespondence. Če najdemo v imenu podjetja v prvi tabeli katero koli besedo iz stolpca Najti, potem morate to krivo ime popolnoma zamenjati s pravilnim – iz stolpca Namestnik druga iskalna tabela.

Za udobje:

• Obe tabeli se pretvorita v dinamično (»pametno«) z uporabo bližnjice na tipkovnici Ctrl+T ali ekipa Vložek – tabela (Vstavi — Tabela).
  
• Na zavihku, ki se prikaže Konstruktor (Oblikovanje) prva imenovana tabela datumin druga referenčna tabela – Zamenjave.

Da bi pojasnili logiko formule, pojdimo malo od daleč.

Če za primer vzamemo prvo podjetje iz celice A2 in začasno pozabimo na ostala podjetja, poskusimo ugotoviti, katera možnost iz stolpca Najti sreča tam. Če želite to narediti, izberite katero koli prazno celico v prostem delu lista in tam vnesite funkcijo NAJTI (NAJTI):

Ta funkcija določa, ali je dani podniz vključen (prvi argument so vse vrednosti iz stolpca Najti) v izvorno besedilo (prvo podjetje iz podatkovne tabele) in mora izpisati bodisi zaporedno številko znaka, iz katerega je bilo najdeno besedilo, bodisi napako, če podniz ni bil najden.

Trik je v tem, da ker kot prvi argument nismo določili ene, ampak več vrednosti, bo ta funkcija kot rezultat vrnila tudi ne eno vrednost, ampak niz treh elementov. Če nimate najnovejše različice Office 3, ki podpira dinamična polja, potem po vnosu te formule in kliku na Vnesite to matriko boste videli prav na listu:

Če imate prejšnje različice Excela, potem po kliku na Vnesite videli bomo samo prvo vrednost iz niza rezultatov, tj napako #VREDNOST! (#VREDNOST!).

Naj vas ni strah 🙂 Pravzaprav naša formula deluje in še vedno lahko vidite celotno paleto rezultatov, če v vrstici s formulami izberete vneseno funkcijo in pritisnete tipko F9(samo ne pozabite pritisniti Escda se vrnem k formuli):

Nastali niz rezultatov pomeni, da je v prvotnem ukrivljenem imenu podjetja (GK Morozko OAO) vseh vrednosti v stolpcu Najti našel samo drugo (Morozko), in začenši s 4. znakom v vrsti.

Zdaj pa naši formuli dodajmo funkcijo OGLED(POGLEJ GOR):

Ta funkcija ima tri argumente:

1. Želena vrednost – uporabite lahko poljubno dovolj veliko število (glavno je, da presega dolžino katerega koli besedila v izvornih podatkih)
   
2. Ogledan_vektor – obseg ali niz, kjer iščemo želeno vrednost. Tukaj je prej predstavljena funkcija NAJTI, ki vrne matriko {#VALUE!:4:#VALUE!}
   
3. Vector_Rezultati – obseg, iz katerega želimo vrniti vrednost, če je želena vrednost najdena v ustrezni celici. Tukaj so pravilna imena iz stolpca Namestnik našo referenčno tabelo.
   

Glavna in neočitna lastnost tukaj je, da funkcija OGLED če ni natančnega ujemanja, vedno išče najbližjo najmanjšo (prejšnjo) vrednost. Zato bomo z navedbo poljubne velike številke (na primer 9999) kot želene vrednosti prisilili OGLED poiščite celico z najbližjim najmanjšim številom (4) v matriki {#VALUE!:4:#VALUE!} in vrnite ustrezno vrednost iz vektorja rezultatov, tj. pravilno ime podjetja iz stolpca Namestnik.

Druga niansa je, da je tehnično naša formula matrična formula, ker funkcija NAJTI vrne kot rezultat ne eno, ampak niz treh vrednosti. Ker pa funkcija OGLED podpira matrike takoj, potem nam te formule ni treba vnesti kot klasično matrično formulo – z uporabo bližnjice na tipkovnici Ctrl+Shift+Vnesite. Enostavna bo zadostovala Vnesite.

To je vse. Upam, da razumeš logiko.

Ostaja še prenos končane formule v prvo celico B2 stolpca Določi – in naša naloga je rešena!

Seveda se tudi pri navadnih (ne pametnih) tabelah ta formula odlično obnese (samo ne pozabite na ključ F4 in popravljanje ustreznih povezav):

Primer 2. Množična delna zamenjava

Ta primer je malo bolj zapleten. Spet imamo dve "pametni" tabeli:

Prva tabela z narobe napisanimi naslovi, ki jih je treba popraviti (poklical sem jo Podatki2). Druga tabela je referenčna knjiga, po kateri morate delno zamenjati podniz znotraj naslova (to tabelo sem poimenoval Zamenjave2).

Bistvena razlika je v tem, da morate zamenjati le delček izvirnih podatkov – na primer, prvi naslov ima napačno »St. Petersburg" na desno »St. Petersburg", ostanek naslova (poštna številka, ulica, hiša) ostane takšen, kot je.

Končana formula bo videti takole (za lažje dojemanje sem jo razdelil na število vrstic z uporabo druga+Vnesite):

Glavno delo tukaj opravi standardna besedilna funkcija Excel NAMESTITEV (ZAMENJAVA), ki ima 3 argumente:

1. Izvorno besedilo – prvi ukrivljeni naslov iz stolpca Naslov
2. Kaj iščemo – tukaj uporabimo trik s funkcijo OGLED (POGLEJ GOR)iz prejšnjega načina, da potegnete vrednost iz stolpca Najti, ki je kot fragment vključen v ukrivljen naslov.
3. S čim nadomestiti – na enak način iz stolpca poiščemo pravilno vrednost, ki mu ustreza Namestnik.

Vnesite to formulo z Ctrl+Shift+Vnesite tudi tukaj ni potrebna, čeprav je v resnici matrična formula.

In jasno je razvidno (glej napake #N/A na prejšnji sliki), da ima takšna formula kljub vsej svoji eleganci nekaj pomanjkljivosti:

• funkcija SUBSTITUTE razlikuje med velikimi in malimi črkami, zato »Spb« v predzadnji vrstici ni bilo mogoče najti v nadomestni tabeli. Za rešitev te težave lahko uporabite funkcijo ZAMENIT (ZAMENJATI), ali pa obe tabeli predhodno spravite v isti register.
• Če je besedilo na začetku pravilno ali v njem ni fragmenta za zamenjavo (zadnja vrstica), potem naša formula vrže napako. Ta trenutek je mogoče nevtralizirati s prestrezanjem in zamenjavo napak s funkcijo NAPAKA (NAPAKA):

  

• Če izvirno besedilo vsebuje več fragmentov iz imenika hkrati, potem naša formula nadomesti samo zadnjo (v 8. vrstici Ligovsky «Avenue« spremenil v “pr-t”, Ampak “S-Pb” on »St. Petersburg" ne več, ker “S-Pb” je višje v imeniku). To težavo lahko rešimo s ponovnim zagonom lastne formule, vendar že vzdolž stolpca Določi:

  
  

Ni popolno in ponekod okorno, a veliko boljše kot enaka ročna zamenjava, kajne? 🙂

PS

V naslednjem članku bomo ugotovili, kako izvesti takšno množično zamenjavo z uporabo makrov in Power Queryja.

• Kako deluje funkcija SUBSTITUTE za zamenjavo besedila
  
• Iskanje natančnih besedilnih ujemanj s funkcijo EXACT
• Iskanje in zamenjava, občutljiva na velike in male črke (VLOOKUP, občutljiva na velike in male črke)