Masseteksterstatning med formler

Tenk deg at du har en liste der, med varierende grad av "rettfremhet", innledende data er skrevet - for eksempel adresser eller firmanavn:

Man ser tydelig at samme by eller bedrift er tilstede her i brokete varianter, noe som åpenbart vil skape mange problemer når man jobber med disse bordene i fremtiden. Og hvis du tenker deg litt om, kan du finne mange eksempler på lignende oppgaver fra andre områder.

Tenk deg nå at slike skjeve data kommer til deg regelmessig, dvs. at dette ikke er en engangshistorie om "fiks det manuelt, glem det", men et problem med jevne mellomrom og i et stort antall celler.

Hva å gjøre? Ikke erstatt manuelt den skjeve teksten 100500 ganger med den riktige gjennom "Finn og erstatt"-boksen eller ved å klikke Ctrl+H?

Det første du tenker på i en slik situasjon er å foreta en masseerstatning i henhold til en forhåndskompilert oppslagsbok med samsvarende feil og riktige alternativer – som dette:

Dessverre, med den åpenbare utbredelsen av en slik oppgave, har ikke Microsoft Excel enkle innebygde metoder for å løse den. Til å begynne med, la oss finne ut hvordan du gjør dette med formler, uten å involvere "tungt artilleri" i form av makroer i VBA eller Power Query.

Etui 1. Full utskifting

La oss starte med en relativt enkel sak – en situasjon der du må erstatte den gamle skjeve teksten med en ny. fullt.

La oss si at vi har to tabeller:

I den første – de opprinnelige varierte navnene på selskaper. I den andre - en oppslagsbok med korrespondanse. Hvis vi finner i navnet på selskapet i den første tabellen et hvilket som helst ord fra kolonnen Å finne, så må du erstatte dette skjeve navnet helt med det riktige – fra kolonnen Erstatning andre oppslagstabell.

For enkelhets skyld:

• Begge tabellene konverteres til dynamiske ("smart") ved hjelp av en hurtigtast Ctrl+T eller team Sett inn – Tabell (Sett inn – tabell).
  
• På fanen som vises Constructor (Design) første bord navngitt Data, og den andre referansetabellen – Innbyttere.

For å forklare logikken i formelen, la oss gå litt langveisfra.

Ved å ta det første selskapet fra celle A2 som et eksempel og midlertidig glemme resten av selskapene, la oss prøve å finne ut hvilket alternativ fra kolonnen Å finne møter der. For å gjøre dette, velg en hvilken som helst tom celle i den ledige delen av arket og skriv inn funksjonen der Å FINNE (FINNE):

Denne funksjonen bestemmer om den gitte understrengen er inkludert (det første argumentet er alle verdier fra kolonnen Å finne) inn i kildeteksten (det første selskapet fra datatabellen) og skal skrive ut enten ordensnummeret til tegnet som teksten ble funnet fra, eller en feil hvis delstrengen ikke ble funnet.

Trikset her er at siden vi ikke spesifiserte én, men flere verdier som det første argumentet, vil denne funksjonen også returnere som et resultat ikke én verdi, men en rekke med 3 elementer. Hvis du ikke har den nyeste versjonen av Office 365 som støtter dynamiske arrays, så etter å ha skrevet inn denne formelen og klikket på Enter du vil se denne matrisen rett på arket:

Hvis du har tidligere versjoner av Excel, så etter å ha klikket på Enter vi vil kun se den første verdien fra resultatmatrisen, dvs. feil #VERDI! (#VERDI!).

Du bør ikke være redd 🙂 Faktisk fungerer formelen vår og du kan fortsatt se hele utvalget av resultater hvis du velger den angitte funksjonen i formellinjen og trykker på tasten F9(bare ikke glem å trykke Escfor å gå tilbake til formelen):

Den resulterende rekke resultater betyr at i det opprinnelige skjeve firmanavnet (GK Morozko OAO) av alle verdier i en kolonne Å finne fant bare den andre (Morozko), og starter fra det fjerde tegnet på rad.

La oss nå legge til en funksjon til formelen vår SE(SE OPP):

Denne funksjonen har tre argumenter:

1. Ønsket verdi – du kan bruke et hvilket som helst tilstrekkelig stort tall (hovedsaken er at det overskrider lengden på teksten i kildedataene)
   
2. Vist_vektor – området eller matrisen der vi ser etter ønsket verdi. Her er den tidligere introduserte funksjonen Å FINNE, som returnerer en matrise {#VALUE!:4:#VALUE!}
   
3. Vector_resultater – området som vi ønsker å returnere verdien fra hvis ønsket verdi finnes i den tilsvarende cellen. Her er de riktige navnene fra kolonnen Erstatning vår referansetabell.
   

Den viktigste og ikke-åpenbare funksjonen her er at funksjonen SE hvis det ikke er eksakt samsvar, ser alltid etter den nærmeste minste (forrige) verdi. Derfor, ved å spesifisere et hvilket som helst heftig tall (for eksempel 9999) som ønsket verdi, vil vi tvinge SE finn cellen med det nærmeste minste tallet (4) i matrisen {#VERDI!:4:#VERDI!} og returner tilsvarende verdi fra resultatvektoren, dvs. riktig firmanavn fra kolonnen Erstatning.

Den andre nyansen er at formelen vår teknisk sett er en matriseformel, fordi funksjon Å FINNE returnerer som resultater ikke én, men en matrise med tre verdier. Men siden funksjonen SE støtter arrays ut av esken, så trenger vi ikke å angi denne formelen som en klassisk array-formel – ved å bruke en hurtigtast Ctrl+Skift+Enter. En enkel vil være tilstrekkelig Enter.

Det er alt. Håper du skjønner logikken.

Det gjenstår å overføre den ferdige formelen til den første cellen B2 i kolonnen Fikset – og vår oppgave er løst!

Selvfølgelig, med vanlige (ikke smarte) tabeller, fungerer denne formelen også utmerket (bare ikke glem nøkkelen F4 og fikse de relevante koblingene):

Case 2. Bulk delvis utskifting

Denne saken er litt vanskeligere. Igjen har vi to "smarte" tabeller:

Den første tabellen med skjevt skrevne adresser som må rettes (jeg kalte den Data2). Den andre tabellen er en oppslagsbok, ifølge hvilken du må gjøre en delvis erstatning av en delstreng inne i adressen (jeg kalte denne tabellen Bytter 2).

Den grunnleggende forskjellen her er at du bare trenger å erstatte et fragment av de originale dataene - for eksempel har den første adressen en feil «St. Petersburg" på høyre «St. Petersburg", og lar resten av adressen (postnummer, gate, hus) være som den er.

Den ferdige formelen vil se slik ut (for å lette oppfatningen delte jeg den inn i hvor mange linjer du bruker andre+Enter):

Hovedarbeidet her gjøres av standard Excel-tekstfunksjon ERSTATNING (ERSTATNING), som har 3 argumenter:

1. Kildetekst – den første skjeve adressen fra Adresse-kolonnen
2. Det vi ser etter – her bruker vi trikset med funksjonen SE (SE OPP)fra forrige måte å trekke verdien fra kolonnen Å finne, som inngår som et fragment i en buet adresse.
3. Hva skal erstattes med – på samme måte finner vi den riktige verdien som tilsvarer den fra kolonnen Erstatning.

Skriv inn denne formelen med Ctrl+Skift+Enter er ikke nødvendig her heller, selv om det faktisk er en matriseformel.

Og det er tydelig (se #N/A feil i forrige bilde) at en slik formel, på tross av all sin eleganse, har et par ulemper:

• Funksjon SUBSTITUTE skiller mellom store og små bokstaver, så "Spb" i nest siste linje ble ikke funnet i erstatningstabellen. For å løse dette problemet kan du enten bruke funksjonen ZAMENIT (ERSTATTE), eller foreløpig bringe begge bordene til samme register.
• Hvis teksten i utgangspunktet er riktig eller i den det er ikke noe fragment å erstatte (siste linje), så gir formelen vår en feil. Dette øyeblikket kan nøytraliseres ved å avskjære og erstatte feil ved å bruke funksjonen FEIL (FEIL):

  

• Hvis originalteksten inneholder flere fragmenter fra katalogen samtidig, så erstatter formelen vår bare den siste (på 8. linje, Ligovsky «Avenue« endret til "pr-t", Men "S-Pb" on «St. Petersburg" ikke lenger, fordi "S-Pb” er høyere i katalogen). Dette problemet kan løses ved å kjøre vår egen formel på nytt, men allerede langs kolonnen Fikset:

  
  

Ikke perfekt og tungvint stedvis, men mye bedre enn den samme manuelle erstatningen, ikke sant? 🙂

PS

I den neste artikkelen vil vi finne ut hvordan du implementerer en slik masseerstatning ved hjelp av makroer og Power Query.

• Hvordan ERSTATT-funksjonen fungerer for å erstatte tekst
  
• Finne eksakte teksttreff ved å bruke EXACT-funksjonen
• Snitt for store og små bokstaver i søk og erstatning (skiller mellom store og små bokstaver VLOOKUP)