Masstextersättning med formler

Anta att du har en lista där, med varierande grad av "enkelhet", initial data skrivs - till exempel adresser eller företagsnamn:

Det syns tydligt att samma stad eller företag finns här i brokiga varianter, vilket uppenbarligen kommer att skapa en hel del problem när man arbetar med dessa bord i framtiden. Och om man tänker lite så kan man hitta en hel del exempel på liknande uppgifter från andra områden.

Föreställ dig nu att sådan sned data kommer till dig regelbundet, dvs. det här är inte en engångshistoria om att "fixa det manuellt, glöm det", utan ett problem på regelbunden basis och i ett stort antal celler.

Vad ska man göra? Byt inte ut den sneda texten manuellt 100500 gånger med den korrekta genom rutan "Sök och ersätt" eller genom att klicka ctrl+H?

Det första man tänker på i en sådan situation är att göra en massersättning enligt en förkompilerad referensbok med matchande felaktiga och korrekta alternativ – så här:

Tyvärr, med den uppenbara förekomsten av en sådan uppgift, har Microsoft Excel inte enkla inbyggda metoder för att lösa det. Till att börja med, låt oss ta reda på hur man gör detta med formler, utan att involvera "tungt artilleri" i form av makron i VBA eller Power Query.

Fodral 1. Bulk fullt utbyte

Låt oss börja med ett relativt enkelt fall – en situation där du behöver ersätta den gamla sneda texten med en ny. fullständigt.

Låt oss säga att vi har två tabeller:

I den första – de ursprungliga brokiga namnen på företag. I den andra – en uppslagsbok med korrespondens. Om vi ​​hittar i företagets namn i den första tabellen något ord från kolumnen Att hitta, då måste du helt ersätta detta sneda namn med det korrekta – från kolumnen Ersättning andra uppslagstabellen.

För bekvämlighet:

• Båda tabellerna konverteras till dynamiska ("smarta") med ett kortkommando ctrl+T eller team Infoga – Tabell (Infoga — Tabell).
  
• På fliken som visas Konstruktör (Design) första bordet namnges Data, och den andra referenstabellen – Substitutioner.

För att förklara logiken i formeln, låt oss gå lite på avstånd.

Ta det första företaget från cell A2 som ett exempel och tillfälligt glömma resten av företagen, låt oss försöka avgöra vilket alternativ från kolumnen Att hitta möter där. För att göra detta, välj valfri tom cell i den fria delen av arket och ange funktionen där ATT HITTA (HITTA):

Denna funktion avgör om den givna delsträngen är inkluderad (det första argumentet är alla värden från kolumnen Att hitta) i källtexten (det första företaget från datatabellen) och ska mata ut antingen ordningsnumret på tecknet från vilket texten hittades eller ett fel om delsträngen inte hittades.

Tricket här är att eftersom vi inte angav ett, utan flera värden som det första argumentet, kommer denna funktion också att returnera som ett resultat inte ett värde, utan en matris med 3 element. Om du inte har den senaste versionen av Office 365 som stöder dynamiska arrayer, efter att ha angett den här formeln och klickat på ange du kommer att se denna array direkt på arket:

Om du har tidigare versioner av Excel, sedan efter att ha klickat på ange vi kommer bara att se det första värdet från resultatmatrisen, dvs fel #VALUE! (#VÄRDE!).

Du ska inte vara rädd 🙂 Faktum är att vår formel fungerar och du kan fortfarande se hela utbudet av resultat om du väljer den angivna funktionen i formelfältet och trycker på knappen F9(glöm bara inte att trycka escför att gå tillbaka till formeln):

Den resulterande uppsättningen av resultat betyder att i det ursprungliga skeva företagsnamnet (GK Morozko OAO) av alla värden i en kolumn Att hitta hittade bara den andra (Morozko), och börjar från det fjärde tecknet i raden.

Låt oss nu lägga till en funktion till vår formel SE(SLÅ UPP):

Denna funktion har tre argument:

1. Önskat värde – du kan använda vilket tillräckligt stort antal som helst (huvudsaken är att det överskrider längden på vilken text som helst i källdata)
   
2. Visad_vektor – intervallet eller matrisen där vi letar efter det önskade värdet. Här är den tidigare introducerade funktionen ATT HITTA, som returnerar en matris {#VALUE!:4:#VALUE!}
   
3. Vektor_resultat – intervallet från vilket vi vill returnera värdet om det önskade värdet finns i motsvarande cell. Här är de korrekta namnen från kolumnen Ersättning vår referenstabell.
   

Den huvudsakliga och icke-uppenbara egenskapen här är att funktionen SE om det inte finns någon exakt matchning, letar alltid efter närmaste minsta (föregående) värde. Därför, genom att ange valfritt rejält tal (till exempel 9999) som önskat värde, kommer vi att tvinga SE hitta cellen med närmaste minsta nummer (4) i arrayen {#VALUE!:4:#VALUE!} och returnera motsvarande värde från resultatvektorn, dvs korrekt företagsnamn från kolumnen Ersättning.

Den andra nyansen är att vår formel tekniskt sett är en matrisformel, eftersom funktion ATT HITTA returnerar som resultat inte ett, utan en matris med tre värden. Men eftersom funktionen SE stöder arrayer direkt, då behöver vi inte ange denna formel som en klassisk arrayformel – med en kortkommando ctrl+shift+ange. En enkel sådan kommer att räcka ange.

Det är allt. Hoppas du förstår logiken.

Det återstår att överföra den färdiga formeln till den första cellen B2 i kolumnen Fast – och vår uppgift är löst!

Naturligtvis, med vanliga (inte smarta) tabeller fungerar denna formel också utmärkt (glöm bara inte nyckeln F4 och fixa relevanta länkar):

Fall 2. Bulk partiell ersättning

Det här fallet är lite knepigare. Återigen har vi två "smarta" tabeller:

Den första tabellen med snett skrivna adresser som måste korrigeras (jag kallade den Data2). Den andra tabellen är en uppslagsbok, enligt vilken du måste göra en partiell ersättning av en delsträng inuti adressen (jag kallade den här tabellen Byten 2).

Den grundläggande skillnaden här är att du bara behöver ersätta ett fragment av den ursprungliga informationen – till exempel har den första adressen en felaktig "St. Petersburg” på höger "St. Petersburg”, lämna resten av adressen (postnummer, gata, hus) som den är.

Den färdiga formeln kommer att se ut så här (för att underlätta uppfattningen delade jag upp den i hur många rader som används andra+ange):

Huvudarbetet här görs av den vanliga Excel-textfunktionen ERSÄTTNING (ERSÄTTNING), som har 3 argument:

1. Källtext – den första sneda adressen från adresskolumnen
2. Det vi letar efter – här använder vi tricket med funktionen SE (SLÅ UPP)från föregående sätt att hämta värdet från kolumnen Att hitta, som ingår som ett fragment i en krökt adress.
3. Vad ska ersättas med – på samma sätt hittar vi det korrekta värdet som motsvarar det från kolumnen Ersättning.

Ange denna formel med ctrl+shift+ange behövs inte här heller, även om det i själva verket är en matrisformel.

Och det syns tydligt (se #N/A-fel i föregående bild) att en sådan formel, trots all sin elegans, har ett par nackdelar:

• Funktion SUBSTITUTE är skiftlägeskänsligt, så "Spb" på den näst sista raden hittades inte i ersättningstabellen. För att lösa detta problem kan du antingen använda funktionen ZAMENIT (BYTA UT), eller föra preliminärt båda tabellerna till samma register.
• Om texten är korrekt från början eller i den det finns inget fragment att ersätta (sista raden), då ger vår formel ett fel. Detta ögonblick kan neutraliseras genom att avlyssna och ersätta fel med funktionen FEL (FEL):

  

• Om originaltexten innehåller flera fragment från katalogen samtidigt, då ersätter vår formel endast den sista (på 8:e raden, Ligovsky «Avenue« ändrad till "pr-t", Men "S-Pb" on "St. Petersburg” inte längre, eftersom "S-Pb” är högre i katalogen). Detta problem kan lösas genom att köra om vår egen formel, men redan längs kolumnen Fast:

  
  

Inte perfekt och besvärligt på sina ställen, men mycket bättre än samma manuella byte, eller hur? 🙂

PS

I nästa artikel kommer vi att ta reda på hur man implementerar en sådan bulksubstitution med hjälp av makron och Power Query.

• Hur SUBSTITUTE-funktionen fungerar för att ersätta text
  
• Hitta exakta textmatchningar med funktionen EXAKT
• Skiftlägeskänslig sökning och ersättning (skiftlägeskänslig VLOOKUP)