Suposem que teniu una llista en la qual, amb diferents graus de "directivitat", s'escriuen dades inicials, per exemple, adreces o noms d'empresa:
 |  |
Es veu clarament que la mateixa ciutat o empresa està present aquí en variants abigarrades, cosa que, òbviament, crearà molts problemes a l'hora de treballar amb aquestes taules en el futur. I si hi penseu una mica, podeu trobar molts exemples de tasques similars d'altres àrees.
Ara imagineu-vos que aquestes dades falses us arriben amb regularitat, és a dir, no es tracta d'una història d'una sola vegada "arregla'l manualment, oblida't-ho", sinó d'un problema de manera regular i en un gran nombre de cel·les.
Què fer? No substituïu manualment el text tort 100500 vegades amb el correcte mitjançant el quadre "Cerca i substitueix" o fent clic Ctrl+H?
El primer que em ve al cap en aquesta situació és fer un reemplaçament massiu d'acord amb un llibre de referència compilat prèviament de la concordança d'opcions incorrectes i correctes, com aquesta:
Malauradament, amb la prevalença òbvia d'aquesta tasca, Microsoft Excel no té mètodes integrats senzills per resoldre'l. Per començar, anem a esbrinar com fer-ho amb fórmules, sense implicar "artilleria pesada" en forma de macros a VBA o Power Query.
Cas 1. Substitució completa a granel
Comencem amb un cas relativament senzill: una situació en què heu de substituir el text tort antic per un de nou. completament.
Suposem que tenim dues taules:
En el primer, els noms variats originals de les empreses. En el segon, un llibre de referència de correspondència. Si trobem en el nom de l'empresa a la primera taula qualsevol paraula de la columna Trobar, aleshores heu de substituir completament aquest nom tort per el correcte, de la columna Suplent segona taula de cerca.
Per comoditat:
- Ambdues taules es converteixen en dinàmiques ("intel·ligents") mitjançant una drecera de teclat Ctrl+T o equip Inserir - Taula (Insereix — Taula).
- A la pestanya que apareix constructor (Disseny) primera taula anomenada dades, i la segona taula de referència - Substitucions.
Per explicar la lògica de la fórmula, anem una mica de lluny.
Prenent com a exemple la primera empresa de la cel·la A2 i oblidant-nos temporalment de la resta d'empreses, intentem determinar quina opció de la columna Trobar es reuneix allà. Per fer-ho, seleccioneu qualsevol cel·la buida a la part lliure del full i introduïu-hi la funció TROBAR (TROBAR):
Aquesta funció determina si s'inclou la subcadena donada (el primer argument són tots els valors de la columna Trobar) al text font (la primera empresa de la taula de dades) i hauria de generar el número ordinal del caràcter del qual s'ha trobat el text, o bé un error si no s'ha trobat la subcadena.
El truc aquí és que, com que no vam especificar un, sinó diversos valors com a primer argument, aquesta funció també retornarà com a resultat no un valor, sinó una matriu de 3 elements. Si no teniu la darrera versió d'Office 365 que admeti matrius dinàmiques, després d'introduir aquesta fórmula i fer clic a Enter veureu aquesta matriu just al full:
Si teniu versions anteriors d'Excel, després de fer clic a Enter només veurem el primer valor de la matriu de resultats, és a dir, error #VALOR! (#VALOR!).
No hauríeu de tenir por 🙂 De fet, la nostra fórmula funciona i encara podeu veure tot el conjunt de resultats si seleccioneu la funció introduïda a la barra de fórmules i premeu la tecla F9(no us oblideu de prémer Escper tornar a la fórmula):
La matriu de resultats resultant significa que en el nom original de l'empresa tort (GK Morozko OAO) de tots els valors d'una columna Trobar només va trobar el segon (Morozko), i començant pel 4t caràcter consecutiu.
Ara afegim una funció a la nostra fórmula MÉS INFO(CERCAR):
Aquesta funció té tres arguments:
- Valor desitjat - podeu utilitzar qualsevol nombre prou gran (el més important és que superi la longitud de qualsevol text a les dades font)
- Vector_visualitzat – l'interval o matriu on busquem el valor desitjat. Aquí teniu la funció introduïda anteriorment TROBAR, que retorna una matriu {#VALUE!:4:#VALUE!}
- Vector_resultats – l'interval des del qual volem retornar el valor si el valor desitjat es troba a la cel·la corresponent. Aquí teniu els noms correctes de la columna Suplent la nostra taula de referència.
La característica principal i no òbvia aquí és que la funció MÉS INFO si no hi ha coincidència exacta, sempre cerca el valor més petit (anterior) més proper. Per tant, especificant qualsevol nombre important (per exemple, 9999) com a valor desitjat, forçarem MÉS INFO Trobeu la cel·la amb el nombre més petit més proper (4) a la matriu {#VALUE!:4:#VALOR!} i retorneu el valor corresponent del vector de resultats, és a dir, el nom correcte de l'empresa de la columna Suplent.
El segon matís és que, tècnicament, la nostra fórmula és una fórmula matricial, perquè la funció TROBAR retorna com a resultats no un, sinó una matriu de tres valors. Però des de la funció MÉS INFO admet matrius fora de la caixa, llavors no hem d'introduir aquesta fórmula com a fórmula de matriu clàssica, utilitzant una drecera de teclat Ctrl+Canviar+Enter. N'hi haurà prou amb un de senzill Enter.
Això és tot. Espero que entenguis la lògica.
Queda per transferir la fórmula acabada a la primera cel·la B2 de la columna Fix - i la nostra tasca està resolta!
Per descomptat, amb taules normals (no intel·ligents), aquesta fórmula també funciona molt bé (no t'oblidis de la clau F4 i arreglant els enllaços pertinents):
Cas 2. Substitució parcial a granel
Aquest cas és una mica més complicat. De nou tenim dues taules "intel·ligents":
La primera taula amb adreces escrites malament que cal corregir (la vaig dir Dades2). La segona taula és un llibre de referència, segons el qual heu de fer una substitució parcial d'una subcadena dins de l'adreça (jo vaig anomenar aquesta taula Substitucions 2).
La diferència fonamental aquí és que només heu de substituir un fragment de les dades originals; per exemple, la primera adreça té una adreça incorrecta “St. Petersburg” a la dreta “St. Petersburg”, deixant la resta de l'adreça (codi postal, carrer, casa) tal qual.
La fórmula acabada es veurà així (per facilitar la percepció, la vaig dividir en quantes línies utilitzant Alt+Enter):
El treball principal aquí es realitza mitjançant la funció de text estàndard d'Excel SUPLENT (SUBSTITUT), que té 3 arguments:
- Text font: la primera adreça torta de la columna Adreça
- El que estem buscant: aquí fem servir el truc amb la funció MÉS INFO (CERCAR)de la manera anterior per treure el valor de la columna Trobar, que s'inclou com a fragment en una adreça corba.
- Amb què substituir: de la mateixa manera trobem el valor correcte corresponent a la columna Suplent.
Introduïu aquesta fórmula amb Ctrl+Canviar+Enter Tampoc és necessari aquí, tot i que, de fet, és una fórmula matricial.
I es veu clarament (vegeu errors #N/A a la imatge anterior) que aquesta fórmula, per tota la seva elegància, té un parell d'inconvenients:
- function SUBSTITUTE distingeix entre majúscules i minúscules, de manera que "Spb" a la penúltima línia no es va trobar a la taula de substitució. Per resoldre aquest problema, podeu utilitzar la funció ZAMENIT (SUBSTITUIR), o portar prèviament les dues taules al mateix registre.
- Si el text inicialment és correcte o en ell no hi ha cap fragment per substituir (última línia), aleshores la nostra fórmula genera un error. Aquest moment es pot neutralitzar interceptant i substituint errors mitjançant la funció SI ERROR (SI ERROR):
- Si el text original conté diversos fragments del directori alhora, aleshores la nostra fórmula substitueix només l'última (a la vuitena línia, Ligovsky «Avinguda« canviat a "pr-t", Però "S-Pb" on “St. Petersburg” ja no, perquè “S-Pb” és més alt al directori). Aquest problema es pot resoldre tornant a executar la nostra pròpia fórmula, però ja al llarg de la columna Fix:
No perfecte i feixuc en alguns llocs, però molt millor que la mateixa substitució manual, oi? 🙂
PS
Al següent article, descobrirem com implementar aquesta substitució massiva mitjançant macros i Power Query.
- Com funciona la funció SUBSTITUTE per substituir el text
- Trobar coincidències de text exactes mitjançant la funció EXACTA
- Cerca i substitució que distingeix entre majúscules i minúscules (Cerca V distingeix entre majúscules i minúscules)