Supponiamo di avere un elenco in cui, con vari gradi di "semplicezza", sono scritti i dati iniziali, ad esempio indirizzi o nomi di società:
 |  |
Si vede chiaramente che la stessa città o azienda è presente qui in varianti eterogenee, il che, ovviamente, creerà molti problemi quando si lavora con questi tavoli in futuro. E se ci pensi un po', puoi trovare molti esempi di compiti simili da altre aree.
Ora immagina che dati così distorti ti arrivino regolarmente, cioè questa non è una storia "correggilo manualmente, dimenticalo", ma un problema su base regolare e in un gran numero di celle.
Cosa fare? Non sostituire manualmente il testo storto 100500 volte con quello corretto tramite la casella "Trova e sostituisci" o facendo clic Ctrl+H?
La prima cosa che viene in mente in una situazione del genere è fare una sostituzione di massa secondo un libro di riferimento precompilato di abbinare opzioni errate e corrette, come questo:
Sfortunatamente, con l'ovvia prevalenza di tale attività, Microsoft Excel non dispone di semplici metodi integrati per risolverlo. Per cominciare, scopriamo come farlo con le formule, senza coinvolgere "artiglieria pesante" sotto forma di macro in VBA o Power Query.
Caso 1. Sostituzione completa in blocco
Iniziamo con un caso relativamente semplice: una situazione in cui è necessario sostituire il vecchio testo distorto con uno nuovo. completamente.
Diciamo di avere due tabelle:
Nel primo – i nomi variegati originali delle aziende. Nel secondo – un libro di riferimento di corrispondenza. Se troviamo nel nome dell'azienda nella prima tabella qualsiasi parola della colonna Trovare, quindi devi sostituire completamente questo nome storto con quello corretto, dalla colonna Sostituire seconda tabella di ricerca.
Per comodità:
- Entrambe le tabelle vengono convertite in dinamiche ("intelligenti") utilizzando una scorciatoia da tastiera Ctrl+T o squadra Inserisci – Tabella (Inserisci — Tabella).
- Nella scheda che appare Costruttore (Design) prima tabella nominata Dati, e la seconda tabella di riferimento – Sostituzioni.
Per spiegare la logica della formula, andiamo un po' da lontano.
Prendendo come esempio la prima azienda dalla cella A2 e dimenticando temporaneamente il resto delle aziende, proviamo a determinare quale opzione dalla colonna Trovare si incontra lì. Per fare ciò, seleziona una cella vuota nella parte libera del foglio e inserisci lì la funzione TROVARE (TROVA):
Questa funzione determina se la sottostringa data è inclusa (il primo argomento è tutti i valori della colonna Trovare) nel testo di origine (la prima azienda dalla tabella dei dati) e dovrebbe restituire il numero ordinale del carattere da cui è stato trovato il testo o un errore se la sottostringa non è stata trovata.
Il trucco qui è che poiché abbiamo specificato non uno, ma diversi valori come primo argomento, anche questa funzione restituirà come risultato non un valore, ma un array di 3 elementi. Se non disponi dell'ultima versione di Office 365 che supporta gli array dinamici, dopo aver inserito questa formula e aver fatto clic su entrare vedrai questo array direttamente sul foglio:
Se hai versioni precedenti di Excel, dopo aver fatto clic su entrare vedremo solo il primo valore dall'array dei risultati, cioè l'errore #VALUE! (#VALORE!).
Non dovresti aver paura 🙂 Infatti la nostra formula funziona e puoi comunque vedere l'intero array di risultati se selezioni la funzione inserita nella barra della formula e premi il tasto F9(non dimenticare di premere Escper tornare alla formula):
La matrice risultante di risultati significa che nel nome dell'azienda originale storto (GK Morozko OAO) di tutti i valori in una colonna Trovare trovato solo il secondo (Morozko), e a partire dal 4° carattere consecutivo.
Ora aggiungiamo una funzione alla nostra formula VISTA(CERCARE):
Questa funzione ha tre argomenti:
- Valore desiderato – puoi usare qualsiasi numero sufficientemente grande (la cosa principale è che supera la lunghezza di qualsiasi testo nei dati di origine)
- Visto_vettore – l'intervallo o l'array in cui stiamo cercando il valore desiderato. Ecco la funzione precedentemente introdotta TROVARE, che restituisce un array {#VALUE!:4:#VALUE!}
- Vettore_sul risultato – l'intervallo da cui vogliamo restituire il valore se nella cella corrispondente si trova il valore desiderato. Ecco i nomi corretti dalla colonna Sostituire la nostra tabella di riferimento.
La caratteristica principale e non ovvia qui è che la funzione VISTA se non c'è una corrispondenza esatta, cerca sempre il valore più piccolo (precedente) più vicino. Pertanto, specificando un qualsiasi numero pesante (ad esempio, 9999) come valore desiderato, forzeremo VISTA trova la cella con il numero più piccolo più vicino (4) nell'array {#VALUE!:4:#VALUE!} e restituisce il valore corrispondente dal vettore dei risultati, ovvero il nome corretto dell'azienda dalla colonna Sostituire.
La seconda sfumatura è che, tecnicamente, la nostra formula è una formula di matrice, perché funzione TROVARE restituisce come risultati non uno, ma una matrice di tre valori. Ma poiché la funzione VISTA supporta gli array pronti all'uso, quindi non è necessario inserire questa formula come una formula di matrice classica, utilizzando una scorciatoia da tastiera Ctrl+Shift+entrare. Basterà uno semplice entrare.
È tutto. Spero che tu abbia la logica.
Resta da trasferire la formula finita nella prima cella B2 della colonna Fissa – e il nostro compito è risolto!
Naturalmente, anche con le tabelle ordinarie (non intelligenti), questa formula funziona alla grande (basta non dimenticare la chiave F4 e fissando i relativi link):
Caso 2. Sostituzione parziale in blocco
Questo caso è un po' più complicato. Anche in questo caso abbiamo due tabelle “intelligenti”:
La prima tabella con indirizzi scritti in modo storto che deve essere corretta (l'ho chiamata Data2). La seconda tabella è un libro di riferimento, in base al quale è necessario effettuare una sostituzione parziale di una sottostringa all'interno dell'indirizzo (ho chiamato questa tabella Sostituzioni2).
La differenza fondamentale qui è che è necessario sostituire solo un frammento dei dati originali, ad esempio il primo indirizzo ha un indirizzo errato “S. Pietroburgo” sulla destra “S. Pietroburgo”, lasciando il resto dell'indirizzo (codice postale, via, casa) così com'è.
La formula finita sarà simile a questa (per facilità di percezione, l'ho divisa in quante linee usando altro+entrare):
Il lavoro principale qui viene svolto dalla funzione di testo standard di Excel SOSTITUIRE (SOSTITUTO), che ha 3 argomenti:
- Testo di origine: il primo indirizzo storto dalla colonna Indirizzo
- Quello che stiamo cercando – qui usiamo il trucco con la funzione VISTA (CERCARE)dal modo precedente per estrarre il valore dalla colonna Trovare, che è incluso come frammento in un indirizzo curvo.
- Con cosa sostituire – allo stesso modo troviamo il valore corretto corrispondente dalla colonna Sostituire.
Inserisci questa formula con Ctrl+Shift+entrare non è necessario neanche qui, sebbene sia, in effetti, una formula di matrice.
Ed è evidente (vedi errori #N/A nella foto precedente) che una tale formula, nonostante tutta la sua eleganza, presenta un paio di inconvenienti:
- Funzione SUBSTITUTE fa distinzione tra maiuscole e minuscole, quindi "Spb" nella penultima riga non è stato trovato nella tabella di sostituzione. Per risolvere questo problema, puoi utilizzare la funzione ZAMENIT (SOSTITUIRE), o portare preliminarmente entrambe le tabelle nello stesso registro.
- Se il testo è inizialmente corretto o in esso non c'è nessun frammento da sostituire (ultima riga), quindi la nostra formula genera un errore. Questo momento può essere neutralizzato intercettando e sostituendo gli errori utilizzando la funzione IFERROR (SE ERRORE):
- Se il testo originale contiene diversi frammenti della directory contemporaneamente, quindi la nostra formula sostituisce solo l'ultima (nell'8a riga, Ligovsky «Avenue« cambiato in "pr-t", Ma “S-Pb” on “S. Pietroburgo” non più, perché “S-Pb” è più alto nella directory). Questo problema può essere risolto rieseguendo la nostra formula, ma già lungo la colonna Fissa:
Non perfetto e ingombrante in alcuni punti, ma molto meglio della stessa sostituzione manuale, giusto? 🙂
PS
Nel prossimo articolo, scopriremo come implementare tale sostituzione in blocco utilizzando le macro e Power Query.
- Come funziona la funzione SOSTITUISCI per sostituire il testo
- Trovare corrispondenze di testo esatte utilizzando la funzione EXACT
- Ricerca e sostituzione con distinzione tra maiuscole e minuscole (VLOOKUP con distinzione tra maiuscole e minuscole)