Stel dat u een lijst heeft waarin, in verschillende mate van "eenvoudigheid", initiële gegevens zijn geschreven, bijvoorbeeld adressen of bedrijfsnamen:
 |  |
Het is duidelijk te zien dat dezelfde stad of hetzelfde bedrijf hier aanwezig is in bonte varianten, wat natuurlijk voor veel problemen zal zorgen bij het werken met deze tabellen in de toekomst. En als je een beetje nadenkt, kun je veel voorbeelden vinden van vergelijkbare taken uit andere gebieden.
Stel je nu eens voor dat dergelijke kromme gegevens regelmatig bij je binnenkomen, dat wil zeggen dat dit geen eenmalig "manueel repareren, vergeten"-verhaal is, maar een probleem op regelmatige basis en in een groot aantal cellen.
Wat moeten we doen? Vervang de kromme tekst niet 100500 keer handmatig door de juiste tekst via het vak "Zoeken en vervangen" of door te klikken op Ctrl+H?
Het eerste dat in een dergelijke situatie in je opkomt, is om een massale vervanging te maken volgens een vooraf samengesteld naslagwerk van het matchen van onjuiste en juiste opties - zoals deze:
Helaas, met de voor de hand liggende prevalentie van een dergelijke taak, heeft Microsoft Excel geen eenvoudige ingebouwde methoden om het op te lossen. Laten we om te beginnen eens kijken hoe we dit met formules kunnen doen, zonder "zware artillerie" in de vorm van macro's in VBA of Power Query te betrekken.
Geval 1. Bulk volledige vervanging
Laten we beginnen met een relatief eenvoudig geval - een situatie waarin u de oude kromme tekst moet vervangen door een nieuwe. geheel.
Laten we zeggen dat we twee tabellen hebben:
In de eerste – de originele bonte namen van bedrijven. In de tweede – een naslagwerk van correspondentie. Als we in de naam van het bedrijf in de eerste tabel een woord uit de kolom vinden Vinden, dan moet je deze kromme naam volledig vervangen door de juiste - uit de kolom Plaatsvervanger tweede opzoektabel.
Voor het gemak:
- Beide tabellen worden geconverteerd naar dynamisch ("slim") met behulp van een sneltoets Ctrl+T of team Tabel invoegen (Tabel invoegen).
- Op het tabblad dat verschijnt Constructeur (Ontwerp) eerste tafel genaamd Data, en de tweede referentietabel - Vervangingen.
Om de logica van de formule uit te leggen, gaan we een beetje van veraf.
Laten we het eerste bedrijf uit cel A2 als voorbeeld nemen en tijdelijk de rest van de bedrijven vergeten, laten we proberen te bepalen welke optie uit de kolom Vinden ontmoet daar. Om dit te doen, selecteert u een willekeurige lege cel in het vrije gedeelte van het blad en voert u daar de functie in VINDEN (VIND):
Deze functie bepaalt of de gegeven substring is opgenomen (het eerste argument zijn alle waarden uit de kolom) Vinden) in de brontekst (het eerste bedrijf uit de gegevenstabel) en moet ofwel het volgnummer weergeven van het teken waaruit de tekst is gevonden, of een fout als de subtekenreeks niet is gevonden.
De truc hier is dat aangezien we niet één, maar meerdere waarden als het eerste argument hebben opgegeven, deze functie ook als resultaat niet één waarde, maar een array van 3 elementen zal retourneren. Als u niet beschikt over de nieuwste versie van Office 365 die dynamische arrays ondersteunt, voer dan deze formule in en klik op Enter je ziet deze array direct op het blad:
Als u eerdere versies van Excel heeft, klik dan op Enter we zullen alleen de eerste waarde van de resultatenarray zien, dwz fout #VALUE! (#WAARDE!).
Je hoeft niet bang te zijn 🙂 In feite werkt onze formule en je kunt nog steeds de hele reeks resultaten zien als je de ingevoerde functie in de formulebalk selecteert en op de toets drukt F9(vergeet niet op te drukken) Escom terug te gaan naar de formule):
De resulterende reeks resultaten betekent dat in de oorspronkelijke kromme bedrijfsnaam: (GK Morozko OAO) van alle waarden in een kolom Vinden vond alleen de tweede (Morozko), en beginnend bij het 4e teken op rij.
Laten we nu een functie aan onze formule toevoegen BEKIJK(OPZOEKEN):
Deze functie heeft drie argumenten:
- Gewenste waarde - u kunt elk voldoende groot aantal gebruiken (het belangrijkste is dat het de lengte van elke tekst in de brongegevens overschrijdt)
- Bekeken_vector – het bereik of de array waar we naar de gewenste waarde zoeken. Hier is de eerder geïntroduceerde functie: VINDEN, die een array retourneert {#VALUE!:4:#VALUE!}
- Vector_ – het bereik waaruit we de waarde willen retourneren als de gewenste waarde in de overeenkomstige cel wordt gevonden. Dit zijn de juiste namen uit de kolom Plaatsvervanger onze referentietabel.
Het belangrijkste en niet voor de hand liggende kenmerk hier is dat de functie BEKIJK als er geen exacte overeenkomst is, zoekt altijd naar de dichtstbijzijnde kleinste (vorige) waarde. Daarom, door een fors getal (bijvoorbeeld 9999) op te geven als de gewenste waarde, forceren we BEKIJK zoek de cel met het dichtstbijzijnde kleinste getal (4) in de array {#VALUE!:4:#VALUE!} en retourneer de overeenkomstige waarde uit de resultaatvector, dwz de juiste bedrijfsnaam uit de kolom Plaatsvervanger.
De tweede nuance is dat onze formule technisch gezien een matrixformule is, omdat functie VINDEN retourneert als resultaten niet één, maar een array van drie waarden. Maar aangezien de functie BEKIJK ondersteunt direct uit de doos arrays, dan hoeven we deze formule niet in te voeren als een klassieke matrixformule - met behulp van een sneltoets Ctrl+Shift+Enter. Een simpele is voldoende Enter.
Dat is alles. Hoop dat je de logica snapt.
Het blijft om de voltooide formule over te brengen naar de eerste cel B2 van de kolom vast – en onze taak is opgelost!
Met gewone (niet slimme) tabellen werkt deze formule natuurlijk ook prima (vergeet alleen de sleutel niet) F4 en het bevestigen van de relevante links):
Geval 2. Bulk gedeeltelijke vervanging
Dit geval is een beetje lastiger. We hebben weer twee "slimme" tabellen:
De eerste tabel met scheef geschreven adressen die gecorrigeerd moet worden (ik noemde het Data2). De tweede tabel is een naslagwerk, volgens welke u een gedeeltelijke vervanging van een substring binnen het adres moet maken (ik noemde deze tabel Wissels2).
Het fundamentele verschil hier is dat u slechts een fragment van de originele gegevens hoeft te vervangen - het eerste adres heeft bijvoorbeeld een onjuiste “St. Petersburg” rechts “St. Petersburg”, waarbij de rest van het adres (postcode, straat, huis) ongewijzigd blijft.
De voltooide formule ziet er als volgt uit (voor het gemak heb ik het verdeeld in hoeveel regels er worden gebruikt) anders+Enter):
Het belangrijkste werk hier wordt gedaan door de standaard Excel-tekstfunctie PLAATSVERVANGER (VERVANGING), die 3 argumenten heeft:
- Brontekst – het eerste kromme adres uit de kolom Adres
- Wat we zoeken - hier gebruiken we de truc met de functie BEKIJK (OPZOEKEN)van de vorige manier om de waarde uit de kolom te halen Vinden, die als een fragment in een gebogen adres is opgenomen.
- Waarmee te vervangen - op dezelfde manier vinden we de juiste waarde die ermee overeenkomt in de kolom Plaatsvervanger.
Voer deze formule in met Ctrl+Shift+Enter is hier ook niet nodig, hoewel het in feite een matrixformule is.
En het is duidelijk te zien (zie #N/A-fouten in de vorige afbeelding) dat zo'n formule, ondanks al zijn elegantie, een aantal nadelen heeft:
- Functie VERVANGING is hoofdlettergevoelig, dus "Spb" in de voorlaatste regel werd niet gevonden in de vervangende tabel. Om dit probleem op te lossen, kunt u ofwel de functie ZAMENIT (VERVANGEN), of breng beide tabellen voorlopig naar hetzelfde register.
- Als de tekst in eerste instantie correct is of erin staat er is geen fragment om te vervangen (laatste regel), dan geeft onze formule een fout. Dit moment kan worden geneutraliseerd door fouten te onderscheppen en te vervangen met behulp van de functie IFFOUT (ALS FOUT):
- Als de originele tekst bevat meerdere fragmenten uit de directory tegelijk, dan vervangt onze formule alleen de laatste (in de 8e regel, Ligovsky «Laan« veranderd naar "pr-t", Maar "S-Pb" on “St. Petersburg” niet meer, want “S-Pb” staat hoger in de directory). Dit probleem kan worden opgelost door onze eigen formule opnieuw uit te voeren, maar al langs de kolom vast:
Niet perfect en omslachtig op sommige plaatsen, maar veel beter dan dezelfde handmatige vervanging, toch?
PS
In het volgende artikel zullen we uitzoeken hoe we een dergelijke bulkvervanging kunnen implementeren met behulp van macro's en Power Query.
- Hoe de SUBSTITUTE-functie werkt om tekst te vervangen
- Exacte tekstovereenkomsten zoeken met de functie EXACT
- Hoofdlettergevoelig zoeken en vervangen (hoofdlettergevoelig VERT.ZOEKEN)