Înlocuirea în bloc a textului cu formule

Să presupunem că aveți o listă în care, cu diferite grade de „continuitate”, sunt scrise datele inițiale – de exemplu, adrese sau nume de companii:

Se vede clar că același oraș sau companie este prezent aici în variante pestrițe, ceea ce, evident, va crea multe probleme atunci când se lucrează cu aceste mese pe viitor. Și dacă te gândești puțin, poți găsi o mulțime de exemple de sarcini similare din alte domenii.

Acum imaginați-vă că astfel de date strâmbe vin la dvs. în mod regulat, adică aceasta nu este o poveste unică „remediați-l manual, uitați-o”, ci o problemă în mod regulat și într-un număr mare de celule.

Ce să fac? Nu înlocuiți manual textul strâmb de 100500 de ori cu cel corect prin caseta „Găsiți și înlocuiți” sau făcând clic Ctrl+H?

Primul lucru care vă vine în minte într-o astfel de situație este să faceți o înlocuire în masă conform unei cărți de referință pre-compilate de potrivire a opțiunilor incorecte și corecte - ca aceasta:

Din păcate, cu prevalența evidentă a unei astfel de sarcini, Microsoft Excel nu are metode simple încorporate pentru a o rezolva. Pentru început, să ne dăm seama cum să facem acest lucru cu formule, fără a implica „artilerie grea” sub formă de macrocomenzi în VBA sau Power Query.

Cazul 1. Înlocuire completă în vrac

Să începem cu un caz relativ simplu – o situație în care trebuie să înlocuiți vechiul text strâmb cu unul nou. complet.

Să presupunem că avem două tabele:

În primul – numele originale variate ale companiilor. În al doilea – o carte de referință de corespondență. Dacă găsim în numele firmei în primul tabel orice cuvânt din coloană A găsi, atunci trebuie să înlocuiți complet acest nume strâmb cu cel corect – din coloană Substitui al doilea tabel de căutare.

Pentru confort:

• Ambele tabele sunt convertite în dinamice („inteligente”) folosind o comandă rapidă de la tastatură Ctrl+T sau echipa Inserare – Tabel (Inserați — Tabel).
  
• Pe fila care apare Constructor (Proiecta) primul tabel numit Date, iar al doilea tabel de referință – substitutii.

Pentru a explica logica formulei, să mergem puțin de departe.

Luând ca exemplu prima companie din celula A2 și uitând temporar de restul companiilor, să încercăm să stabilim ce opțiune din coloană A găsi se intalneste acolo. Pentru a face acest lucru, selectați orice celulă goală din partea liberă a foii și introduceți funcția acolo A GĂSI (GĂSI):

Această funcție determină dacă subșirul dat este inclus (primul argument este toate valorile din coloană A găsi) în textul sursă (prima companie din tabelul de date) și ar trebui să scoată fie numărul ordinal al caracterului din care a fost găsit textul, fie o eroare dacă subșirul nu a fost găsit.

Trucul aici este că, deoarece am specificat nu una, ci mai multe valori ca prim argument, această funcție va returna și ca rezultat nu o singură valoare, ci o matrice de 3 elemente. Dacă nu aveți cea mai recentă versiune de Office 365 care acceptă matrice dinamice, atunci după ce ați introdus această formulă și faceți clic pe Intrați veți vedea această matrice chiar pe foaie:

Dacă aveți versiuni anterioare de Excel, atunci după ce faceți clic pe Intrați vom vedea doar prima valoare din tabloul de rezultate, adică eroarea #VALOARE! (#VALOARE!).

Nu ar trebui să vă fie frică 🙂 De fapt, formula noastră funcționează și puteți vedea în continuare întreaga gamă de rezultate dacă selectați funcția introdusă în bara de formule și apăsați tasta F9(doar nu uitați să apăsați Escpentru a reveni la formula):

Gama rezultată de rezultate înseamnă că în numele companiei strâmb inițial (GK Morozko OAO) a tuturor valorilor dintr-o coloană A găsi găsit doar al doilea (Morozko), și începând cu al 4-lea caracter la rând.

Acum să adăugăm o funcție la formula noastră VIEW(PRIVEŞTE ÎN SUS):

Această funcție are trei argumente:

1. Valoarea dorită – puteți utiliza orice număr suficient de mare (principalul este că depășește lungimea oricărui text din datele sursă)
   
2. Vector_vizual – domeniul sau matricea în care căutăm valoarea dorită. Iată funcția introdusă anterior A GĂSI, care returnează o matrice {#VALUE!:4:#VALUE!}
   
3. Vector_rezultate obținute – intervalul din care dorim să returnăm valoarea dacă valoarea dorită se găsește în celula corespunzătoare. Iată numele corecte din coloană Substitui tabelul nostru de referință.
   

Caracteristica principală și neevidentă aici este că funcția VIEW dacă nu există o potrivire exactă, caută întotdeauna cea mai mică valoare (anterioră).. Prin urmare, specificând orice număr mare (de exemplu, 9999) ca valoare dorită, vom forța VIEW găsiți celula cu cel mai apropiat număr (4) din tabloul {#VALUE!:4:#VALUE!} și returnați valoarea corespunzătoare din vectorul rezultat, adică numele corect al companiei din coloană Substitui.

A doua nuanță este că, din punct de vedere tehnic, formula noastră este o formulă matrice, deoarece funcția A GĂSI returnează ca rezultat nu una, ci o matrice de trei valori. Dar din moment ce functia VIEW acceptă matrice din cutie, atunci nu trebuie să introducem această formulă ca formulă clasică de matrice - folosind o comandă rapidă de la tastatură Ctrl+Schimba+Intrați. Unul simplu va fi suficient Intrați.

Asta e tot. Sper că înțelegi logica.

Rămâne să transferăm formula finită în prima celulă B2 a coloanei Fixed – și sarcina noastră este rezolvată!

Desigur, cu tabele obișnuite (nu inteligente), această formulă funcționează de asemenea grozav (doar nu uitați de cheie F4 și remedierea legăturilor relevante):

Cazul 2. Înlocuire parțială în vrac

Acest caz este puțin mai complicat. Din nou avem două mese „inteligente”:

Primul tabel cu adrese scrise strâmb care trebuie corectat (eu l-am numit Date2). Al doilea tabel este o carte de referință, conform căreia trebuie să faceți o înlocuire parțială a unui subșir în interiorul adresei (am numit acest tabel Înlocuiri 2).

Diferența fundamentală aici este că trebuie să înlocuiți doar un fragment din datele originale - de exemplu, prima adresă are o incorectă "Sf. Petersburg” pe dreapta "Sf. Petersburg”, lăsând restul adresei (cod poștal, stradă, casă) așa cum este.

Formula finală va arăta astfel (pentru ușurința percepției, am împărțit-o în câte linii folosind Alt+Intrați):

Lucrul principal aici este realizat de funcția standard de text Excel SUBSTITUI (SUBSTITUI), care are 3 argumente:

1. Text sursă – prima adresă strâmbă din coloana Adresă
2. Ce căutăm – aici folosim trucul cu funcția VIEW (PRIVEŞTE ÎN SUS)din modul anterior de a trage valoarea din coloană A găsi, care este inclus ca fragment într-o adresă curbă.
3. Cu ce ​​să înlocuim – în același mod găsim valoarea corectă corespunzătoare acesteia din coloană Substitui.

Introduceți această formulă cu Ctrl+Schimba+Intrați nu este nevoie nici aici, deși este, de fapt, o formulă matrice.

Și se vede clar (vezi erorile #N/A în poza anterioară) că o astfel de formulă, cu toată eleganța ei, are câteva dezavantaje:

• Funcţie SUBSTITUTE face distincție între majuscule și minuscule, deci „Spb” în penultima linie nu a fost găsit în tabelul de înlocuire. Pentru a rezolva această problemă, puteți utiliza fie funcția ZAMENIT (A INLOCUI), sau aduceți în prealabil ambele tabele în același registru.
• Dacă textul este inițial corect sau în el nu există niciun fragment de înlocuit (ultima linie), apoi formula noastră aruncă o eroare. Acest moment poate fi neutralizat prin interceptarea și înlocuirea erorilor folosind funcția DACA EROARE (DACA EROARE):

  

• Dacă textul original conţine mai multe fragmente din director deodată, atunci formula noastră o înlocuiește doar pe ultima (în linia a 8-a, Ligovsky «Bulevard« schimbat în „pr-t”, Dar „S-Pb” on "Sf. Petersburg” nu mai, pentru că „S-Pb” este mai mare în director). Această problemă poate fi rezolvată prin reluarea propriei formule, dar deja de-a lungul coloanei Fixed:

  
  

Nu perfect și greoi pe alocuri, dar mult mai bine decât aceeași înlocuire manuală, nu? 🙂

PS

În următorul articol, ne vom da seama cum să implementăm o astfel de înlocuire în bloc folosind macrocomenzi și Power Query.

• Cum funcționează funcția SUBSTITUTE pentru a înlocui textul
  
• Găsirea potrivirilor exacte ale textului utilizând funcția EXACT
• Căutare și înlocuire care ține cont de majuscule și minuscule (Căutare V diferențiază de majuscule și minuscule)