Zëvendësimi i tekstit në masë me formula

Supozoni se keni një listë në të cilën, me shkallë të ndryshme të "drejtësisë", shkruhen të dhënat fillestare - për shembull, adresat ose emrat e kompanive:

Shihet qartë se i njëjti qytet apo kompani është i pranishëm këtu në variante të larmishme, të cilat, padyshim, do të krijojnë shumë probleme gjatë punës me këto tabela në të ardhmen. Dhe nëse mendoni pak, mund të gjeni shumë shembuj të detyrave të ngjashme nga fusha të tjera.

Tani imagjinoni që të dhëna të tilla të shtrembëra t'ju vijnë rregullisht, dmth. Ky nuk është një histori "rregulloje manualisht, harroje" një herë, por një problem në baza të rregullta dhe në një numër të madh qelizash.

Çfarë duhet bërë? Mos e zëvendësoni manualisht tekstin e shtrembër 100500 herë me atë të saktë përmes kutisë "Gjej dhe zëvendëso" ose duke klikuar Ctrl+H?

Gjëja e parë që ju vjen ndërmend në një situatë të tillë është të bëni një zëvendësim masiv sipas një libri referimi të parapërpiluar të opsioneve të gabuara dhe të sakta që përputhen - si kjo:

Fatkeqësisht, me përhapjen e dukshme të një detyre të tillë, Microsoft Excel nuk ka metoda të thjeshta të integruara për ta zgjidhur atë. Për të filluar, le të kuptojmë se si ta bëjmë këtë me formula, pa përfshirë "artileri të rëndë" në formën e makrove në VBA ose Power Query.

Rasti 1. Zëvendësimi i plotë me shumicë

Le të fillojmë me një rast relativisht të thjeshtë - një situatë ku duhet të zëvendësoni tekstin e vjetër të shtrembër me një të ri. plotësisht.

Le të themi se kemi dy tabela:

Në të parën - emrat origjinalë të larmishëm të kompanive. Në të dytën - një libër referimi i korrespondencës. Nëse gjejmë në emrin e kompanisë në tabelën e parë ndonjë fjalë nga kolona Per te gjetur, atëherë duhet ta zëvendësoni plotësisht këtë emër të shtrembër me atë të saktë - nga kolona zëvendësim tabela e dytë e kërkimit.

Për lehtësi:

• Të dyja tabelat konvertohen në dinamike ("të zgjuara") duke përdorur një shkurtore të tastierës Ctrl+T ose ekipi Insert – Tabela (Fut - Tabela).
  
• Në skedën që shfaqet konstruktor (Dizajn) tabela e parë me emrin Data, dhe tabela e dytë e referencës - Zëvendësimet.

Për të shpjeguar logjikën e formulës, le të shkojmë pak nga larg.

Duke marrë si shembull kompaninë e parë nga qeliza A2 dhe duke harruar përkohësisht pjesën tjetër të kompanive, le të përpiqemi të përcaktojmë se cili opsion nga kolona Per te gjetur takohet atje. Për ta bërë këtë, zgjidhni çdo qelizë boshe në pjesën e lirë të fletës dhe futni funksionin atje PER TE GJETUR (GJEJ):

Ky funksion përcakton nëse është përfshirë nënvargu i dhënë (argumenti i parë janë të gjitha vlerat nga kolona Per te gjetur) në tekstin burimor (kompania e parë nga tabela e të dhënave) dhe duhet të nxjerrë ose numrin rendor të karakterit nga i cili u gjet teksti, ose një gabim nëse nënvargu nuk u gjet.

Truku këtu është se meqenëse ne specifikuam jo një, por disa vlera si argumentin e parë, ky funksion gjithashtu do të kthejë si rezultat jo një vlerë, por një grup prej 3 elementësh. Nëse nuk keni versionin më të fundit të Office 365 që mbështet vargje dinamike, atëherë pasi të futni këtë formulë dhe të klikoni në Shkruani do ta shihni këtë grup direkt në fletë:

Nëse keni versione të mëparshme të Excel, atëherë pasi të klikoni Shkruani do të shohim vetëm vlerën e parë nga grupi i rezultateve, pra gabimi #VALUE! (#VLERË!).

Ju nuk duhet të keni frikë F9(thjesht mos harroni të shtypni Escpër t'u kthyer te formula):

Grupi rezultues i rezultateve do të thotë se në emrin e kompanisë origjinale të shtrembër (GK Morozko OAO) të të gjitha vlerave në një kolonë Per te gjetur gjeti vetëm të dytën (Morozko), dhe duke filluar nga karakteri i 4-të me radhë.

Tani le të shtojmë një funksion në formulën tonë VIEW(KËRKO):

Ky funksion ka tre argumente:

1. Vlera e dëshiruar - mund të përdorni çdo numër mjaft të madh (gjëja kryesore është se ai tejkalon gjatësinë e çdo teksti në të dhënat burimore)
   
2. Shikuar_vektor – diapazoni ose grupi ku po kërkojmë vlerën e dëshiruar. Këtu është funksioni i prezantuar më parë PER TE GJETUR, i cili kthen një grup {#VALUE!:4:#VALUE!}
   
3. Vektor_rezultatet – diapazoni nga i cili duam të kthejmë vlerën nëse vlera e dëshiruar gjendet në qelizën përkatëse. Këtu janë emrat e saktë nga kolona zëvendësim tabelën tonë të referencës.
   

Karakteristika kryesore dhe jo e dukshme këtu është se funksioni VIEW nëse nuk ka përputhje të saktë, gjithmonë kërkon vlerën më të vogël (të mëparshme) më të afërt. Prandaj, duke specifikuar ndonjë numër të madh (për shembull, 9999) si vlerën e dëshiruar, ne do të detyrojmë VIEW gjeni qelizën me numrin më të vogël më të afërt (4) në grupin {#VALUE!:4:#VALUE!} dhe ktheni vlerën përkatëse nga vektori i rezultatit, dmth saktë emrin e kompanisë nga kolona. zëvendësim.

Nuanca e dytë është se, teknikisht, formula jonë është një formulë grupi, sepse funksioni PER TE GJETUR kthen si rezultat jo një, por një grup prej tre vlerash. Por që nga funksioni VIEW mbështet vargjet jashtë kutisë, atëherë nuk duhet ta futim këtë formulë si një formulë klasike të grupit - duke përdorur një shkurtore të tastierës Ctrl+Ndryshim+Shkruani. Një e thjeshtë do të mjaftojë Shkruani.

Kjo eshte e gjitha. Shpresoj ta kuptoni logjikën.

Mbetet për të transferuar formulën e përfunduar në qelizën e parë B2 të kolonës Fixed - dhe detyra jonë është zgjidhur!

Sigurisht, me tavolina të zakonshme (jo të zgjuara), kjo formulë funksionon gjithashtu shkëlqyeshëm (thjesht mos harroni çelësin F4 dhe rregullimi i lidhjeve përkatëse):

Rasti 2. Zëvendësimi i pjesshëm në masë

Ky rast është pak më i ndërlikuar. Përsëri kemi dy tabela "të zgjuara":

Tabela e parë me adresa të shkruara shtrembër që duhet korrigjuar (e thirra unë Të dhënat2). Tabela e dytë është një libër referimi, sipas të cilit ju duhet të bëni një zëvendësim të pjesshëm të një nënvargu brenda adresës (Unë e quajta këtë tabelë Zëvendësimet 2).

Dallimi themelor këtu është se ju duhet të zëvendësoni vetëm një fragment të të dhënave origjinale - për shembull, adresa e parë ka një të pasaktë “Shën. Petersburg” on drejtë “Shën. Petersburg”, duke e lënë pjesën tjetër të adresës (kodin postar, rrugë, shtëpi) siç është.

Formula e përfunduar do të duket kështu (për lehtësinë e perceptimit, e ndava në sa rreshta duke përdorur Ndalet+Shkruani):

Puna kryesore këtu kryhet nga funksioni standard i tekstit Excel ZUBVENDITSUESI (ZËVENDËSUES), i cili ka 3 argumente:

1. Teksti burimor – adresa e parë e shtrembër nga kolona Adresa
2. Ajo që po kërkojmë - këtu përdorim trukun me funksionin VIEW (KËRKO)nga mënyra e mëparshme për të tërhequr vlerën nga kolona Per te gjetur, i cili përfshihet si fragment në një adresë të lakuar.
3. Me çfarë të zëvendësojmë - në të njëjtën mënyrë gjejmë vlerën e saktë që i korrespondon asaj nga kolona zëvendësim.

Fusni këtë formulë me Ctrl+Ndryshim+Shkruani as këtu nuk nevojitet, megjithëse në fakt është një formulë grupi.

Dhe shihet qartë (shih #N/A gabimet në foton e mëparshme) që një formulë e tillë, me gjithë elegancën e saj, ka disa të meta:

• funksion SUBSTITUTE është sensitive, kështu që "Spb" në rreshtin e parafundit nuk u gjet në tabelën e zëvendësimit. Për të zgjidhur këtë problem, mund të përdorni ose funksionin ZAMENIT (ZËVENDËso), ose sillni paraprakisht të dyja tabelat në të njëjtin regjistër.
• Nëse teksti është fillimisht i saktë ose në të nuk ka asnjë fragment për të zëvendësuar (rreshti i fundit), atëherë formula jonë hedh një gabim. Ky moment mund të neutralizohet duke përgjuar dhe zëvendësuar gabimet duke përdorur funksionin IFERROR (IFEROR):

  

• Nëse teksti origjinal përmban disa fragmente nga direktoria njëherësh, atëherë formula jonë zëvendëson vetëm atë të fundit (në rreshtin e 8-të, Ligovsky «rrugë« ndryshuar në "pr-t", Por "S-Pb" on “Shën. Petersburg” jo më, sepse “S-Pb” është më i lartë në drejtori). Ky problem mund të zgjidhet duke ri- ekzekutuar formulën tonë, por tashmë përgjatë kolonës Fixed:

  
  

Jo perfekt dhe i rëndë në vende, por shumë më mirë se i njëjti zëvendësim manual, apo jo? 🙂

PS

Në artikullin tjetër, ne do të kuptojmë se si të zbatojmë një zëvendësim të tillë në masë duke përdorur makro dhe Power Query.

• Si funksionon funksioni SUBSTITUTE për të zëvendësuar tekstin
  
• Gjetja e përputhjeve të sakta të tekstit duke përdorur funksionin EXACT
• Kërkim dhe zëvendësim i ndjeshëm ndaj rasteve (VLOOKUP)