Masseteksterstatning med formler

Antag, at du har en liste, hvor der med varierende grad af "ligetilrettelighed" skrives indledende data - for eksempel adresser eller firmanavne:

Det ses tydeligt, at den samme by eller virksomhed er til stede her i brogede varianter, hvilket naturligvis vil skabe en del problemer, når man arbejder med disse borde i fremtiden. Og hvis man tænker sig lidt om, kan man finde en masse eksempler på lignende opgaver fra andre områder.

Forestil dig nu, at sådanne skæve data kommer til dig regelmæssigt, dvs. dette er ikke en engangs "manuel fix it, forget it"-historie, men et problem på regelmæssig basis og i et stort antal celler.

Hvad skal man gøre? Udskift ikke den skæve tekst manuelt 100500 gange med den rigtige gennem boksen "Find og erstat" eller ved at klikke Ctrl+H?

Det første, der kommer til at tænke på i en sådan situation, er at foretage en masseudskiftning i henhold til en prækompileret opslagsbog med matchende forkerte og korrekte muligheder – sådan her:

Desværre, med den åbenlyse udbredelse af en sådan opgave, har Microsoft Excel ikke enkle indbyggede metoder til at løse det. Til at begynde med, lad os finde ud af, hvordan man gør dette med formler, uden at involvere "tungt artilleri" i form af makroer i VBA eller Power Query.

Etui 1. Bulk fuld udskiftning

Lad os starte med en forholdsvis simpel sag – en situation, hvor du skal udskifte den gamle skæve tekst med en ny. fuldt ud.

Lad os sige, at vi har to tabeller:

I den første – de oprindelige brogede navne på virksomheder. I den anden - en opslagsbog med korrespondance. Hvis vi finder et hvilket som helst ord fra kolonnen i virksomhedens navn i den første tabel At finde, så skal du helt erstatte dette skæve navn med det rigtige – fra kolonnen Erstatning anden opslagstabel.

For nemheds skyld:

• Begge tabeller konverteres til dynamiske ("smarte") ved hjælp af en tastaturgenvej Ctrl+T eller hold Indsæt – tabel (Indsæt — tabel).
  
• På den fane, der vises Constructor (Design) første bord navngivet data, og den anden referencetabel – Udskiftninger.

For at forklare logikken i formlen, lad os gå lidt langvejs fra.

Tager vi det første firma fra celle A2 som et eksempel og midlertidigt glemmer resten af ​​virksomhederne, så lad os prøve at bestemme hvilken mulighed fra kolonnen At finde mødes der. For at gøre dette skal du vælge en hvilken som helst tom celle i den frie del af arket og indtaste funktionen der AT FINDE (FINDE):

Denne funktion bestemmer, om den givne understreng er inkluderet (det første argument er alle værdier fra kolonnen At finde) i kildeteksten (det første firma fra datatabellen) og skal udskrive enten ordensnummeret på det tegn, hvorfra teksten blev fundet, eller en fejl, hvis understrengen ikke blev fundet.

Tricket her er, at da vi ikke specificerede én, men flere værdier som det første argument, vil denne funktion også returnere som et resultat ikke én værdi, men en matrix af 3 elementer. Hvis du ikke har den seneste version af Office 365, der understøtter dynamiske arrays, skal du efter at have indtastet denne formel og klikke på Indtast du vil se dette array lige på arket:

Hvis du har tidligere versioner af Excel, så efter at have klikket på Indtast vi vil kun se den første værdi fra resultatarrayet, dvs. fejl #VALUE! (#VÆRDI!).

Du skal ikke være bange 🙂 Faktisk virker vores formel, og du kan stadig se hele rækken af ​​resultater, hvis du vælger den indtastede funktion i formellinjen og trykker på tasten F9(Glem bare ikke at trykke Escfor at gå tilbage til formlen):

Den resulterende række af resultater betyder, at i det originale skæve firmanavn (GK Morozko OAO) af alle værdier i en kolonne At finde fandt kun den anden (Morozko), og starter med det 4. tegn i rækken.

Lad os nu tilføje en funktion til vores formel UDSIGT(KIG OP):

Denne funktion har tre argumenter:

1. Ønsket værdi – du kan bruge et hvilket som helst tilstrækkeligt stort tal (det vigtigste er, at det overskrider længden af ​​enhver tekst i kildedataene)
   
2. Set_vektor – det område eller array, hvor vi leder efter den ønskede værdi. Her er den tidligere introducerede funktion AT FINDE, som returnerer en matrix {#VALUE!:4:#VALUE!}
   
3. Vector_resultater – det område, hvorfra vi ønsker at returnere værdien, hvis den ønskede værdi findes i den tilsvarende celle. Her er de rigtige navne fra kolonnen Erstatning vores referencetabel.
   

Det vigtigste og ikke-oplagte træk her er, at funktionen UDSIGT hvis der ikke er et nøjagtigt match, søger du altid efter den nærmeste mindste (tidligere) værdi. Derfor, ved at angive et hvilket som helst stort tal (f.eks. 9999) som den ønskede værdi, vil vi tvinge UDSIGT find cellen med det nærmeste mindste tal (4) i arrayet {#VALUE!:4:#VALUE!} og returner den tilsvarende værdi fra resultatvektoren, altså korrekt firmanavn fra kolonnen Erstatning.

Den anden nuance er, at vores formel teknisk set er en matrixformel, fordi funktion AT FINDE returnerer som resultater ikke én, men en matrix med tre værdier. Men siden funktionen UDSIGT understøtter arrays ud af boksen, så behøver vi ikke indtaste denne formel som en klassisk array-formel – ved hjælp af en tastaturgenvej Ctrl+Flytte+Indtast. En enkel vil være tilstrækkelig Indtast.

Det er alt. Håber du forstår logikken.

Det er tilbage at overføre den færdige formel til den første celle B2 i kolonnen Fast – og vores opgave er løst!

Med almindelige (ikke smarte) tabeller fungerer denne formel selvfølgelig også godt (glem bare ikke nøglen F4 og rette de relevante links):

Case 2. Bulk delvis udskiftning

Denne sag er lidt sværere. Igen har vi to "smarte" tabeller:

Den første tabel med skævt skrevne adresser, der skal rettes (jeg kaldte den Data 2). Den anden tabel er en opslagsbog, ifølge hvilken du skal lave en delvis erstatning af en understreng inde i adressen (jeg kaldte denne tabel Udskiftninger 2).

Den grundlæggende forskel her er, at du kun skal erstatte et fragment af de originale data - for eksempel har den første adresse en forkert "St. Petersborg” til højre "St. Petersborg”, og forlader resten af ​​adressen (postnummer, gade, hus) som den er.

Den færdige formel vil se sådan ud (for at lette opfattelsen opdelte jeg den i, hvor mange linjer der bruges andre+Indtast):

Hovedarbejdet her udføres af standard Excel-tekstfunktionen ERSTATNING (ERSTATNING), som har 3 argumenter:

1. Kildetekst – den første skæve adresse fra kolonnen Adresse
2. Det vi leder efter – her bruger vi tricket med funktionen UDSIGT (KIG OP)fra den forrige måde at trække værdien fra kolonnen At finde, der indgår som et fragment i en buet adresse.
3. Hvad skal erstattes med – på samme måde finder vi den korrekte værdi svarende til den fra kolonnen Erstatning.

Indtast denne formel med Ctrl+Flytte+Indtast er heller ikke nødvendig her, selvom det i virkeligheden er en matrixformel.

Og det ses tydeligt (se #N/A fejl på det forrige billede), at en sådan formel, trods al sin elegance, har et par ulemper:

• Funktion SUBSTITUTE skelner mellem store og små bogstaver, så "Spb" i næstsidste linje blev ikke fundet i erstatningstabellen. For at løse dette problem kan du enten bruge funktionen ZAMENIT (ERSTATTE), eller foreløbigt bringe begge tabeller til samme register.
• Hvis teksten oprindeligt er korrekt eller i den der er intet fragment at erstatte (sidste linje), så giver vores formel en fejl. Dette øjeblik kan neutraliseres ved at opsnappe og erstatte fejl ved hjælp af funktionen FEJL (FEJL):

  

• Hvis den originale tekst indeholder flere fragmenter fra mappen på én gang, så erstatter vores formel kun den sidste (i 8. linje, Ligovsky «Avenue« ændret til "pr-t", Men "S-Pb" on "St. Petersborg” ikke længere, fordi "S-Pb” er højere i mappen). Dette problem kan løses ved at køre vores egen formel igen, men allerede langs kolonnen Fast:

  
  

Ikke perfekt og besværligt steder, men meget bedre end den samme manuelle udskiftning, ikke? 🙂

PS

I den næste artikel vil vi finde ud af, hvordan man implementerer en sådan massesubstitution ved hjælp af makroer og Power Query.

• Hvordan ERSTAT-funktionen fungerer for at erstatte tekst
  
• Sådan finder du nøjagtige tekstmatcher ved hjælp af funktionen NØJAGTIG
• Skift og små bogstaver søgning og substitution (forskel på store og små bogstaver VLOOKUP)