Sustituzione di testu in massa cù formule

Supponete chì avete una lista in quale, cù varii gradi di "simpletezza", i dati iniziali sò scritti - per esempiu, indirizzi o nomi di cumpagnie:

Sustituzione di testu in massa cù formule            Sustituzione di testu in massa cù formule

Hè chjaramente vistu chì a listessa cità o cumpagnia hè presente quì in varianti motley, chì, ovviamente, creanu assai prublemi quandu u travagliu cù queste tavule in u futuru. E se pensate un pocu, pudete truvà assai esempi di travaglii simili da altre zone.

Avà imaginate chì tali dati curretti venenu à voi regularmente, vale à dì questu ùn hè micca una storia di una sola volta "riparazione manualmente, scurdatevi", ma un prublema in una basa regulare è in un gran numaru di cellule.

Chì fà ? Ùn rimpiazzate micca manualmente u testu stortu 100500 volte cù u currettu attraversu a casella "Truvà è Sustituisce" o clicchendu Ctrl+H?

A prima cosa chì vene in mente in una tale situazione hè di fà un rimpiazzamentu di massa secondu un libru di riferimentu pre-cumpilatu di currispondenza d'opzioni sbagliate è currette - cum'è questu:

Sustituzione di testu in massa cù formule

Sfurtunatamente, cù a prevalenza evidenti di una tale attività, Microsoft Excel ùn hà micca metudi simplici integrati per risolve. Per principià, scopre cumu fà questu cù e formule, senza implicà "artiglieria pesante" in forma di macros in VBA o Power Query.

Casu 1. Bulk sustituzione cumpleta

Cuminciamu cù un casu relativamente simplice - una situazione induve avete bisognu di rimpiazzà u vechju testu stortu cù un novu. tinuti.

Diciamu chì avemu dui tavule:

Sustituzione di testu in massa cù formule

In u primu - i nomi variegati originali di cumpagnie. In u sicondu - un libru di riferimentu di currispundenza. Se truvamu in u nome di a cumpagnia in a prima tavula ogni parolla da a colonna Da truvà, allura vi tocca à rimpiazzà cumplettamente stu nomu crooked cù u currettu - da a colonna Sustituitu seconda tabella di ricerca.

Per comodità:

  • E duie tavule sò cunvertite in dinamica ("intelligente") utilizendu una scurciatoia di tastiera Ctrl+T o squadra Inserisci - Table (Inserisci - Table).
  • Nantu à a tabulazione chì appare regala (Cuncepimentu) prima tavola chjamata Dati, è a seconda tabella di riferimentu - Sustituzioni.

Per spiegà a logica di a formula, andemu un pocu da luntanu.

Pigliendu a prima cumpagnia da a cellula A2 cum'è un esempiu è scurdate temporaneamente di u restu di l'imprese, pruvemu di determinà quale opzione da a colonna Da truvà si incontra lì. Per fà questu, selezziunate ogni cellula viota in a parti libera di u fogliu è entre in a funzione quì À TROUVER (TROVA):

Sustituzione di testu in massa cù formule

Sta funzione determina se a substringa data hè inclusa (u primu argumentu hè tutti i valori da a colonna Da truvà) in u testu fonte (a prima cumpagnia da a tavula di dati) è deve esse u numeru ordinale di u caratteru da quale u testu hè statu trovu, o un errore se a substring ùn hè micca stata truvata.

U truccu quì hè chì, postu chì avemu specificatu micca unu, ma parechji valori cum'è u primu argumentu, sta funzione torna ancu in u risultatu micca un valore, ma un array di 3 elementi. Se ùn avete micca l'ultima versione di Office 365 chì supporta arrays dinamichi, dopu avè inseritu sta formula è cliccà nantu à Rinsignate vi vede stu array ghjustu nantu à u fogliu:

Sustituzione di testu in massa cù formule

Sè vo avete versioni precedenti di Excel, dopu dopu cliccà Rinsignate videremu solu u primu valore da u array di risultati, vale à dì l'errore #VALORE ! (#VALORE!).

Ùn avete micca a paura 🙂 In fatti, a nostra formula funziona è pudete sempre vede tutta a matrice di risultati se selezziunate a funzione inserita in a barra di formula è pressu a chjave. F9(ùn vi scurdate di appughjà Escper vultà à a formula):

Sustituzione di testu in massa cù formule

L'array resultante di risultati significa chì in u nome di l'impresa storta originale (GK Morozko OAO) di tutti i valori in una colonna Da truvà trovu solu u sicondu (Moruzzu), è partendu da u 4u caratteru in una fila.

Avà aghjunghje una funzione à a nostra formula Visu(CIRCÀ):

Sustituzione di testu in massa cù formule

Sta funzione hà trè argumenti:

  1. Valore desideratu - pudete aduprà qualsiasi numeru abbastanza grande (a cosa principal hè chì supera a lunghezza di qualsiasi testu in i dati fonte)
  2. Viewed_vector - a gamma o l'array induve cerchemu u valore desideratu. Eccu a funzione introdutta prima À TROUVER, chì torna un array {#VALUE!:4:#VALUE!}
  3. Vector_risultati - a gamma da quale vulemu vultà u valore se u valore desideratu si trova in a cellula currispundente. Eccu i nomi curretti da a colonna Sustituitu a nostra tabella di riferimentu.

A funzione principale è micca evidenti quì hè chì a funzione Visu s'ellu ùn ci hè micca una corrispondenza esatta, cerca sempre u valore più vicinu (precedente).. Dunque, specificendu un numeru forte (per esempiu, 9999) cum'è u valore desideratu, forzeremu Visu truvate a cellula cù u numeru più chjucu vicinu (4) in l'array {#VALUE!:4:#VALUE!} è rinviate u valore currispundente da u vettore di u risultatu, vale à dì u nome di cumpagnia curretta da a colonna. Sustituitu.

A seconda sfumatura hè chì, tecnicamente, a nostra formula hè una formula array, perchè funzione À TROUVER Ritorna cum'è risultati micca unu, ma un array di trè valori. Ma postu chì a funzione Visu supporta arrays fora di a scatula, allora ùn avemu micca da inserisce sta formula cum'è una formula array classica - utilizendu una scurciatoia di tastiera Ctrl+Shift+Rinsignate. Un simplice serà abbastanza Rinsignate.

Eccu tuttu. Spergu chì avete a logica.

Resta per trasfirià a formula finita à a prima cellula B2 di a colonna erettu - è u nostru compitu hè risoltu!

Sustituzione di testu in massa cù formule

Di sicuru, cù e tavule ordinarie (micca intelligenti), sta formula funziona ancu bè (ùn vi scurdate micca di a chjave). F4 è riparà i ligami pertinenti):

Sustituzione di testu in massa cù formule

Casu 2. Sustituzione parziale di massa

Stu casu hè un pocu più complicatu. In novu avemu dui tavule "intelligenti":

Sustituzione di testu in massa cù formule

U primu tavulu cù l'indirizzi scritti tortu chì deve esse currettu (aghju chjamatu Dati2). A seconda tavula hè un libru di riferimentu, secondu a quale avete bisognu di fà una sustituzione parziale di una substringa in l'indirizzu (aghju chjamatu sta tabella. Sustituzioni 2).

A diferenza fundamentale quì hè chì avete bisognu di rimpiazzà solu un frammentu di i dati originali - per esempiu, u primu indirizzu hà un incorrectu "St. Petersburg" à diritta "St. Petersburg", lascendu u restu di l'indirizzu (codice postale, strada, casa) cum'è hè.

A formula finita sarà cusì (per facilità di percepzioni, l'aghju divisu in quante linee usendu Alt+Rinsignate):

Sustituzione di testu in massa cù formule

U travagliu principale quì hè fattu da a funzione standard di testu Excel SUSTITUTU (SUBSTITUT), chì hà 3 argumenti:

  1. Testu surghjente - u primu indirizzu stortu da a colonna Indirizzu
  2. Ciò chì circhemu - quì avemu aduprà u truccu cù a funzione Visu (CIRCÀ)da a manera precedente per tirà u valore da a colonna Da truvà, chì hè inclusu cum'è un fragmentu in un indirizzu curvatu.
  3. Chì rimpiazzà cù - in a listessa manera truvamu u valore currettu chì currisponde à questu da a colonna Sustituitu.

Entre sta formula cù Ctrl+Shift+Rinsignate ùn hè micca necessariu ancu quì, ancu s'ellu hè, in fattu, una formula array.

È si vede chjaramente (vede l'errori #N/A in a stampa precedente) chì una tale formula, per tutta a so eleganza, hà un paru di inconvenienti:

  • funzioni SUBSTITUTE hè sensible à u casu, cusì "Spb" in a penultima linea ùn hè micca stata truvata in a tavola di sustituzione. Per risolve stu prublema, pudete puru aduprà a funzione ZAMENIT (SOSTITUIRE), o portanu preliminarmente e duie tavule à u stessu registru.
  • Se u testu hè inizialmente currettu o in questu ùn ci hè micca frammentu per rimpiazzà (ultima linea), allora a nostra formula tira un errore. Stu mumentu pò esse neutralizatu da intercepting è rimpiazzà errori cù a funzione IFERROR (IFERROR):

    Sustituzione di testu in massa cù formule

  • Se u testu originale cuntene parechji frammenti da u cartulare à una volta, allura a nostra formula rimpiazza solu l'ultima (in a linea 8, Ligovsky «Avenue« cambiatu in "pr-t", Ma "S-Pb" on "St. Petersburg" micca più, perchè "S-Pb” hè più altu in u cartulare). Stu prublemu pò esse risolta da ripiglià a nostra propria formula, ma digià longu à a colonna erettu:

    Sustituzione di testu in massa cù formule

Micca perfetta è ingombrante in i lochi, ma assai megliu cà u listessu sustitutu manuale, nò? 🙂

PS

In u prossimu articulu, scopreremu cumu implementà una tale sustituzione in massa utilizendu macros è Power Query.

  • Cumu funziona a funzione SOSTITUTI per rimpiazzà u testu
  • Truvà Testu Esattamente Matches Utilizendu a Funzione EXACT
  • Ricerca è sostituzione di case sensitive (VLOOKUP case sensitive)

Lascia un Audiolibro