Tömeges szövegcsere képletekkel

Tegyük fel, hogy van egy listája, amelybe a kezdeti adatok – például címek vagy cégnevek – különböző fokú „egyenességgel” vannak beírva:

Jól látható, hogy ugyanaz a város vagy cég van jelen itt tarka változatokban, ami nyilvánvalóan sok problémát fog okozni a jövőben ezekkel a táblázatokkal való munka során. És ha egy kicsit gondolkodik, rengeteg példát találhat hasonló feladatokra más területekről is.

Most képzeld el, hogy ilyen ferde adatok rendszeresen érkeznek hozzád, vagyis ez nem egy egyszeri „javítsd ki manuálisan, felejtsd el” történet, hanem rendszeresen és nagy számú cellában probléma.

Mit kell tenni? Ne cserélje ki kézzel a 100500 XNUMX-szor görbe szöveget a megfelelőre a „Keresés és csere” mezőben vagy a Ctrl+H?

Az első dolog, ami egy ilyen helyzetben eszébe jut, az, hogy tömeges cserét hajtson végre egy előre összeállított referenciakönyv szerint, amely a hibás és helyes opciók összeillesztését tartalmazza – például:

Sajnos egy ilyen feladat nyilvánvaló elterjedtsége mellett a Microsoft Excel nem rendelkezik egyszerű beépített módszerekkel a megoldására. Kezdésként nézzük meg, hogyan lehet ezt megtenni képletekkel anélkül, hogy a VBA-ban vagy a Power Query-ben makrók formájában bevonnánk a „nehéztüzérséget”.

1. eset. Tömeges teljes csere

Kezdjük egy viszonylag egyszerű esettel – egy olyan helyzettel, amikor a régi görbe szöveget egy újra kell cserélni. teljesen.

Tegyük fel, hogy két táblázatunk van:

Az elsőben – a cégek eredeti tarka nevei. A másodikban a levelezés kézikönyve. Ha az első táblázatban a cég nevében találunk egy szót az oszlopból Megtalálni, akkor ezt a görbe nevet teljesen ki kell cserélni a megfelelőre – az oszlopból Helyettes második keresőtábla.

Szükségszerűség miatt:

• Mindkét táblázat dinamikussá („intelligenssé”) alakul egy billentyűparancs segítségével Ctrl+T vagy csapat Beszúrás – táblázat (Beszúrás — táblázat).
  
• A megjelenő lapon építész (Tervezés) az első tábla neve dátumés a második referencia táblázat – Cserék.

A képlet logikájának magyarázatához menjünk egy kicsit messziről.

Példaként vesszük az A2 cellából az első céget, és átmenetileg megfeledkezve a többi cégről, próbáljuk meg meghatározni, melyik opciót az oszlopból Megtalálni ott találkozik. Ehhez válasszon ki egy tetszőleges üres cellát a lap szabad részén, és írja be oda a függvényt MEGTALÁLNI (MEGTALÁLJA):

Ez a függvény határozza meg, hogy az adott részkarakterlánc szerepel-e (az első argumentum az oszlop összes értéke Megtalálni) a forrásszövegbe (az adattáblázat első vállalata), és vagy annak a karakternek a sorszámát kell kiadnia, amelyből a szöveg található, vagy egy hibát, ha az alkarakterlánc nem található.

A trükk itt az, hogy mivel nem egy, hanem több értéket adtunk meg első argumentumként, ez a függvény szintén nem egy értéket, hanem egy 3 elemből álló tömböt ad vissza. Ha nem rendelkezik az Office 365 legújabb verziójával, amely támogatja a dinamikus tömböket, akkor a képlet beírása és a belép ezt a tömböt fogja látni közvetlenül a lapon:

Ha az Excel korábbi verzióival rendelkezik, akkor a gombra kattintás után belép csak az első értéket fogjuk látni az eredménytömbből, azaz #VALUE hibát! (#ÉRTÉK!).

Ne félj F9(csak ne felejtsd el megnyomni Eschogy visszatérjünk a képlethez):

A kapott eredmények tömbje azt jelenti, hogy az eredeti görbe cégnévben (GK Morozko OAO) egy oszlopban lévő összes értékből Megtalálni csak a másodikat találta meg (Morozko), és a sorban a 4. karaktertől kezdve.

Most adjunk hozzá egy függvényt a képletünkhöz Megtekintés(NÉZZ FEL):

Ennek a függvénynek három argumentuma van:

1. Kívánt érték - bármilyen kellően nagy számot használhat (a lényeg, hogy meghaladja a forrásadatokban szereplő szövegek hosszát)
   
2. Megtekintett_vektor – az a tartomány vagy tömb, ahol a kívánt értéket keressük. Itt van a korábban bemutatott funkció MEGTALÁLNI, amely egy {#VALUE!:4:#VALUE!} tömböt ad vissza
   
3. Vektor_eredmények – az a tartomány, ahonnan az értéket szeretnénk visszaadni, ha a megfelelő cellában a kívánt érték megtalálható. Itt vannak a helyes nevek az oszlopból Helyettes referencia táblázatunk.
   

A fő és nem nyilvánvaló jellemző itt az, hogy a funkció Megtekintés ha nincs pontos egyezés, mindig a legközelebbi legkisebb (korábbi) értéket keresi. Ezért ha tetszőleges nagy számot (például 9999) adunk meg kívánt értékként, akkor kényszerítjük Megtekintés keresse meg a legközelebbi legkisebb számú (4) cellát a {#ÉRTÉK!:4:#ÉRTÉK!} tömbben, és adja vissza a megfelelő értéket az eredményvektorból, azaz a helyes cégnevet az oszlopból Helyettes.

A második árnyalat az, hogy technikailag a képletünk egy tömbképlet, mert függvény MEGTALÁLNI eredményként nem egy, hanem egy három értékből álló tömböt ad vissza. De mivel a funkció Megtekintés támogatja a tömbök használatát, akkor nem kell ezt a képletet klasszikus tömbképletként megadnunk – billentyűparancs segítségével Ctrl+műszak+belép. Egy egyszerű is elég lesz belép.

Ez minden. Remélem érted a logikát.

Marad a kész képlet átvitele az oszlop első B2 cellájába Rögzített – és a feladatunk megoldva!

Természetesen a közönséges (nem okos) táblákkal ez a képlet is remekül működik (csak a kulcsról ne feledkezzünk meg). F4 és a kapcsolódó linkek rögzítése):

2. eset Tömeges részleges csere

Ez az eset egy kicsit trükkösebb. Ismét van két „okos” táblázatunk:

Az első táblázat ferdén írt címekkel, amit javítani kell (én hívtam Adatok2). A második táblázat egy referenciakönyv, mely szerint a címen belüli részstringet részben le kell cserélni (én ezt a táblát neveztem el Csere2).

Az alapvető különbség itt az, hogy az eredeti adatoknak csak egy töredékét kell kicserélni – például az első cím helytelen. "Utca. Petersburg” a jobb oldalon "Utca. Petersburg”, a cím többi részét (irányítószám, utca, ház) a régiben hagyja.

A kész képlet így fog kinézni (az áttekinthetőség kedvéért hány sorra osztottam más+belép):

A fő munkát itt a szabványos Excel szövegfüggvény végzi HELYETTES (HELYETTES), amelynek 3 érve van:

1. Forrásszöveg – az első görbe cím a Cím oszlopból
2. Amit keresünk – itt használjuk a trükköt a funkcióval Megtekintés (NÉZZ FEL)az előző módszerből, hogy kihúzza az értéket az oszlopból Megtalálni, amely töredékként szerepel egy ívelt címben.
3. Mire kell cserélni – ugyanígy az oszlopból is megtaláljuk a megfelelő értéket Helyettes.

Írja be ezt a képletet a következővel: Ctrl+műszak+belép itt sincs szükség, bár ez valójában egy tömbképlet.

És jól látható (lásd a #N/A hibákat az előző képen), hogy egy ilyen képletnek minden eleganciája ellenére van néhány hátránya:

• Funkció A SUBSTITUTE megkülönbözteti a kis- és nagybetűket, így az utolsó előtti sorban lévő „Spb” nem található a helyettesítő táblázatban. A probléma megoldásához használhatja a funkciót ZAMENIT (CSERE), vagy előzetesen hozza mindkét táblát ugyanabba a regiszterbe.
• Ha a szöveg kezdetben helyes vagy benne nincs cserélendő töredék (utolsó sor), akkor a képletünk hibát dob. Ez a pillanat semlegesíthető a hibák elfogásával és pótlásával a funkció használatával IFROROR (HIBÁS):

  

• Ha az eredeti szöveg tartalmazza több töredéket a könyvtárból egyszerre, akkor képletünk csak az utolsót helyettesíti (a 8. sorban, Ligovsky «Sugárút« változott "pr-t", De "S-Pb" on "Utca. Petersburg” már nem, mert „S-Pb” magasabban van a könyvtárban). Ez a probléma megoldható a saját képlet újrafuttatásával, de már az oszlop mentén Rögzített:

  
  

Nem tökéletes és helyenként nehézkes, de sokkal jobb, mint ugyanaz a kézi csere, igaz? 🙂

PS

A következő cikkben kitaláljuk, hogyan lehet végrehajtani egy ilyen tömeges helyettesítést makrók és Power Query használatával.

• Hogyan működik a SUBSTITUTE funkció a szöveg helyettesítésére
  
• Pontos szövegegyezések keresése az EXACT függvény segítségével
• Kis- és nagybetűk közötti keresés és helyettesítés (Kis- és nagybetűk megkülönböztetése VLOOKUP)