contingut
Una de les tasques més frustrants i que consumeixen més temps quan es treballa amb text a Excel és anàlisi – analitzar el "farinet" alfanumèric en components i extreure'n els fragments que necessitem. Per exemple:
- extreure el codi postal de l'adreça (és bo si el codi postal sempre està al principi, però i si no ho és?)
- trobar el número i la data de la factura a partir de la descripció del pagament a l'extracte bancari
- extracció de TIN a partir de descripcions abigarrades d'empreses a la llista de contraparts
- cerqueu un número de cotxe o un número d'article a la descripció, etc.
Normalment, en aquests casos, després de mitja hora d'escollint el text manualment, comencen a venir pensaments d'alguna manera per automatitzar aquest procés (sobretot si hi ha moltes dades). Hi ha diverses solucions i amb diferents graus de complexitat-eficiència:
- Ús Funcions de text d'Excel integrades per cercar text-tallar-pegar: LEVSIMV (ESQUERRA), DRET (DRET), PSTR (mitjana), STsEPIT (CONCATENAR) i els seus anàlegs, COMBINA (TEXT JOIN), EXACT (EXACTE) etc. Aquest mètode és bo si hi ha una lògica clara al text (per exemple, l'índex sempre està al començament de l'adreça). En cas contrari, les fórmules es tornen molt més complicades i, de vegades, fins i tot es tracta de fórmules de matriu, que s'alenteix molt en taules grans.
- Ús com l'operador de semblança de text de Visual Basic embolicat en una funció de macro personalitzada. Això us permet implementar una cerca més flexible amb caràcters comodí (*, #,?, etc.) Malauradament, aquesta eina no pot extreure la subcadena desitjada del text; només comproveu si hi conté.
A més de l'anterior, hi ha un altre enfocament que és molt conegut en cercles reduïts de programadors professionals, desenvolupadors web i altres tècnics: aquest és expressions regulars (Expressions regulars = RegExp = “regexps” = “regulars”). En poques paraules, RegExp és un llenguatge on s'utilitzen caràcters i regles especials per cercar les subcadenes necessàries al text, extreure-les o substituir-les per un altre text.. Les expressions regulars són una eina molt potent i bonica que supera totes les altres maneres de treballar amb text en un ordre de magnitud. Molts llenguatges de programació (C#, PHP, Perl, JavaScript...) i editors de text (Word, Notepad++...) admeten expressions regulars.
Desafortunadament, Microsoft Excel no té suport RegExp fora de la caixa, però això es pot solucionar fàcilment amb VBA. Obriu l'Editor de Visual Basic des de la pestanya revelador (Desenvolupador) o drecera de teclat Alt+F11. A continuació, inseriu el nou mòdul a través del menú Inserir – Mòdul i copieu-hi el text de la funció macro següent:
Funció pública RegExpExtract (Text com a cadena, patró com a cadena, element opcional com a enter = 1) com a cadena en cas d'error GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (Text) A continuació, estableix coincidències = regex.Execute (Text) RegExpExtract = coincideix.Item (Item - 1) Funció de sortida End If ErrHandl: RegExpExtract = CVErr (xlErrValue) End Function Ara podem tancar l'Editor Visual Basic i tornar a Excel per provar la nostra nova funció. La seva sintaxi és la següent:
=RegExpExtract(Txt; Patró; Element)
where
- txt – una cel·la amb el text que estem comprovant i de la qual volem extreure la subcadena que necessitem
- patró – màscara (patró) per a la cerca de subcadenes
- Article – el número de seqüència de la subcadena que s'ha d'extreure, si n'hi ha diverses (si no s'especifica, es mostra la primera ocurrència)
El més interessant aquí, per descomptat, és Patró: una cadena de plantilla de caràcters especials "en l'idioma" de RegExp, que especifica què exactament i on volem trobar. Aquests són els més bàsics per començar:
| patró | Descripció |
| . | El més senzill és un punt. Coincideix amb qualsevol caràcter del patró a la posició especificada. |
| s | Qualsevol caràcter que sembli un espai (espai, tabulació o salt de línia). |
| S | Una antivariant del patró anterior, és a dir, qualsevol caràcter que no sigui espai en blanc. |
| d | Qualsevol número |
| D | Una antivariant de l'anterior, és a dir, qualsevol dígit NOT |
| w | Qualsevol caràcter llatí (AZ), dígit o guió baix |
| W | Una antivariant de l'anterior, és a dir, ni llatí, ni un número ni un guió baix. |
| [personatges] | Entre claudàtors, podeu especificar un o més caràcters permesos a la posició especificada del text. Per exemple Art coincidirà amb qualsevol de les paraules: taula or cadira. Tampoc podeu enumerar caràcters, sinó establir-los com un interval separat per un guionet, és a dir, en lloc de [ABDCDEF] escriure [AF]. o en canvi [4567] Introduir [-4 7]. Per exemple, per designar tots els caràcters ciríl·lics, podeu utilitzar la plantilla [a-yaA-YayoYo]. |
| [^personatges] | Si després del claudàtor d'obertura afegiu el símbol "tapa" ^, aleshores el conjunt adquirirà el significat contrari: a la posició especificada del text, es permetran tots els caràcters, excepte els que s'indiquen. Sí, plantilla [^ЖМ]ut trobaré Camí or Substància or Oblidar, Però no De por or mut, per exemple. |
| | | Operador booleà OR (O) per comprovar qualsevol dels criteris especificats. Per exemple (a partir deDj|sparell|factura) cercarà al text qualsevol de les paraules especificades. Normalment, un conjunt d'opcions s'entrega entre parèntesis. |
| ^ | Inici de línia |
| $ | Final de línia |
| b | Final de la paraula |
Si busquem un nombre determinat de caràcters, per exemple, un codi postal de sis dígits o tots els codis de producte de tres lletres, anem al rescat quantificadors or quantificadors són expressions especials que especifiquen el nombre de caràcters que cal cercar. Els quantificadors s'apliquen al caràcter que li precedeix:
| Quantor | Descripció |
| ? | Zero o una ocurrència. Per exemple .? significarà qualsevol personatge o la seva absència. |
| + | Una o més entrades. Per exemple d+ significa qualsevol nombre de dígits (és a dir, qualsevol nombre entre 0 i infinit). |
| * | Zero o més ocurrències, és a dir, qualsevol quantitat. Tan s* significa qualsevol nombre d'espais o cap espai. |
| {nombre} or {número 1,número 2} | Si necessiteu especificar un nombre estrictament definit d'ocurrències, s'especifica entre claus. Per exemple d{6} significa estrictament sis dígits i el patró s{2,5} – De dos a cinc espais |
Passem ara a la part més interessant: una anàlisi de l'aplicació de la funció creada i el que vam aprendre sobre patrons d'exemples pràctics de la vida.
Extracció de números del text
Per començar, analitzem un cas senzill: heu d'extreure el primer número de les farinetes alfanumèriques, per exemple, la potència de les fonts d'alimentació ininterrompuda de la llista de preus:
La lògica darrere de l'expressió regular és senzilla: d significa qualsevol dígit i el quantificador + diu que el seu nombre hauria de ser un o més. El doble menys davant de la funció és necessari per convertir "sobre la marxa" els caràcters extrets en un nombre complet del nombre com a text.
Codi postal
A primera vista, aquí tot és senzill: busquem exactament sis dígits seguits. Utilitzem un caràcter especial d per dígit i quantificador 6 {} pel nombre de caràcters:
Tanmateix, és possible una situació en què, a l'esquerra de l'índex de la línia, hi hagi un altre gran conjunt de números seguits (número de telèfon, TIN, compte bancari, etc.) Aleshores la nostra temporada regular traurà els 6 primers. dígits, és a dir, no funcionarà correctament:
Per evitar que això passi, hem d'afegir un modificador al voltant de les vores de la nostra expressió regular b que significa el final d'una paraula. Això deixarà clar a Excel que el fragment (índex) que necessitem ha de ser una paraula separada i no formar part d'un altre fragment (número de telèfon):
Telèfon
El problema de trobar un número de telèfon al text és que hi ha tantes opcions per escriure números: amb i sense guions, a través d'espais, amb o sense un codi de regió entre claudàtors, etc. Per tant, al meu entendre, és més fàcil primer esborra tots aquests caràcters del text font mitjançant diverses funcions imbricades SUPLENT (SUBSTITUT)de manera que s'enganxi en un sol tot, i després amb un regular primitiu d{11} treu 11 dígits seguits:
ITN
Aquí és una mica més complicat, perquè el TIN (al nostre país) pot ser de 10 dígits (per a persones jurídiques) o de 12 dígits (per a persones físiques). Si no trobeu cap error especialment, és molt possible estar satisfet amb el normal d{10,12}, però, en sentit estricte, traurà tots els números de 10 a 12 caràcters, és a dir, 11 dígits introduïts erròniament. Seria més correcte utilitzar dos patrons connectats per un operador OR lògic | (barra vertical):
Tingueu en compte que a la consulta primer busquem números de 12 bits i només després els números de 10 bits. Si escrivim la nostra expressió regular a l'inrevés, s'extreurà per a tothom, fins i tot els TIN llargs de 12 bits, només els 10 primers caràcters. És a dir, un cop activada la primera condició, ja no es realitza una verificació addicional:
Aquesta és la diferència fonamental entre l'operador | des d'una funció lògica estàndard d'Excel OR (O), on reordenar els arguments no canvia el resultat.
SKU de producte
En moltes empreses, els identificadors únics s'assignen als béns i serveis: articles, codis SAP, SKU, etc. Si hi ha lògica en la seva notació, es poden extreure fàcilment de qualsevol text mitjançant expressions regulars. Per exemple, si sabem que els nostres articles sempre consten de tres lletres angleses majúscules, un guionet i un número de tres dígits posterior, aleshores:
La lògica darrere de la plantilla és senzilla. [AZ] – significa qualsevol lletra majúscula de l'alfabet llatí. El següent quantificador 3 {} diu que és important per a nosaltres que hi hagi exactament tres cartes d'aquest tipus. Després del guionet, estem esperant tres dígits, així que sumem al final d{3}
Imports en efectiu
De manera similar al paràgraf anterior, també podeu extreure preus (costos, IVA...) de la descripció de la mercaderia. Si les quantitats monetàries, per exemple, s'indiquen amb un guionet, aleshores:
patró d amb quantificador + cerca qualsevol nombre fins a un guionet, i d{2} buscarà cèntims (dos dígits) després.
Si no necessiteu extreure preus, sinó IVA, podeu utilitzar el tercer argument opcional de la nostra funció RegExpExtract, que especifica el número ordinal de l'element que cal extreure. I, per descomptat, podeu substituir la funció SUPLENT (SUBSTITUT) als resultats, feu un guionet al separador decimal estàndard i afegiu un doble menys al principi perquè Excel interpreti l'IVA trobat com un nombre normal:
Números de matrícula del cotxe
Si no agafeu vehicles especials, remolcs i altres motocicletes, el número estàndard del cotxe s'analitza segons el principi "lletra - tres números - dues lletres - codi de regió". A més, el codi de regió pot ser de 2 o 3 dígits, i només s'utilitzen com a lletres aquells que són semblants a l'alfabet llatí. Així, la següent expressió regular ens ajudarà a extreure nombres del text:
Temps
Per extreure l'hora en el format HH:MM, és adequada la següent expressió regular:
Després del fragment de còlon [0-5]d, com és fàcil d'esbrinar, estableix qualsevol nombre en el rang 00-59. Abans dels dos punts entre parèntesis, funcionen dos patrons, separats per un OR lògic (tuba):
- [0-1]d – qualsevol nombre en el rang 00-19
- 2[0-3] – qualsevol nombre en el rang 20-23
Al resultat obtingut, també podeu aplicar la funció estàndard d'Excel TIME (EQUIP)per convertir-lo en un format d'hora que sigui comprensible per al programa i adequat per a càlculs posteriors.
Comprovació de la contrasenya
Suposem que hem de comprovar la correcció de la llista de contrasenyes inventades pels usuaris. Segons les nostres normes, les contrasenyes només poden contenir lletres angleses (majúscules o minúscules) i números. No es permeten espais, guions baixos i altres signes de puntuació.
La comprovació es pot organitzar mitjançant l'expressió regular senzilla següent:
De fet, amb aquest patró necessitem que entre el principi (^) i acabar ($) al nostre text només hi havia caràcters del conjunt indicat entre claudàtors. Si també necessiteu comprovar la longitud de la contrasenya (per exemple, almenys 6 caràcters), aleshores el quantificador + es pot substituir per l'interval "sis o més" del formulari {6,}:
Ciutat des de l'adreça
Suposem que hem de treure la ciutat de la barra d'adreces. El programa normal us ajudarà, extreu el text de "g". a la coma següent:
Fem una ullada més de prop a aquest patró.
Si heu llegit el text anterior, ja heu entès que alguns caràcters de les expressions regulars (punts, asteriscs, signes de dòlar, etc.) tenen un significat especial. Si necessiteu cercar aquests caràcters ells mateixos, van precedits d'una barra invertida (de vegades anomenada blindatge). Per tant, quan cerqueu el fragment "g". hem d'escriure en expressió regular Sr si busquem un plus, aleshores + etcètera...
Els dos caràcters següents de la nostra plantilla, el punt i l'asterisc quantificador, representen qualsevol nombre de caràcters, és a dir, qualsevol nom de ciutat.
Hi ha una coma al final de la plantilla, perquè estem buscant text de "g". a una coma. Però hi pot haver diverses comes al text, oi? No només després de la ciutat, sinó també després del carrer, cases, etc. En quin d'ells s'aturarà la nostra petició? Per això serveix el signe d'interrogació. Sense ell, la nostra expressió regular extreuria la cadena més llarga possible:
Pel que fa a les expressions regulars, aquest patró és "cobdiciós". Per corregir la situació, cal un signe d'interrogació (fa que el quantificador després del qual es posi "avar" - i la nostra consulta només porta el text fins a la primera coma del comptador després de "g".
Nom del fitxer del camí complet
Una altra situació molt habitual és extreure el nom del fitxer del camí complet. Una expressió regular senzilla de la forma ajudarà aquí:
El truc aquí és que la cerca, de fet, es produeix en la direcció oposada, des del final fins al principi, perquè al final de la nostra plantilla hi ha $, i estem buscant tot abans fins a la primera barra invertida des de la dreta. La barra invertida s'escapa, com el punt de l'exemple anterior.
PS
“Cap al final” Vull aclarir que tot l'anterior és una petita part de totes les possibilitats que ofereixen les expressions regulars. Hi ha molts caràcters i regles especials per al seu ús, i s'han escrit llibres sencers sobre aquest tema (recomano almenys aquest per començar). En certa manera, escriure expressions regulars és gairebé un art. Gairebé sempre, una expressió regular inventada es pot millorar o complementar, fent-la més elegant o capaç de treballar amb una gamma més àmplia de dades d'entrada.
Per analitzar i analitzar les expressions regulars d'altres persones o per depurar les vostres pròpies, hi ha diversos serveis en línia convenients: RegEx101, RegExr i més
Malauradament, no totes les característiques de les expressions regulars clàssiques són compatibles amb VBA (per exemple, la cerca inversa o les classes POSIX) i poden funcionar amb ciríl·lic, però crec que el que hi ha és suficient per primera vegada per agradar-vos.
Si no sou nou en el tema i teniu alguna cosa per compartir, deixeu expressions regulars útils quan treballeu a Excel als comentaris següents. Una ment és bona, però dues botes són un parell!
- Substitució i neteja de text amb la funció SUBSTITUTE
- Cerca i ressaltat de caràcters llatins al text
- Cerca el text semblant més proper (Ivanov = Ivonov = Ivanof, etc.)