Ús d'expressions regulars a Microsoft Excel: funcions a la cel·la i tècniques de bucle

Ús d'expressions regulars a Microsoft Excel: funcions a la cel·la i tècniques de bucle
Ús d'expressions regulars a Microsoft Excel: funcions a la cel·la i tècniques de bucle
Lucas Simon
Dimecres, 17 de juliol de 2024 a les 22:12:38

Dominar Regex a Excel: una guia completa

Les expressions regulars, conegudes comunament com a Regex, són eines poderoses per a la concordança de patrons i la manipulació de cadenes. A Microsoft Excel, podeu aprofitar Regex per millorar les capacitats de manipulació de dades, facilitant la gestió de tasques complexes de processament de text.

Aquesta guia explorarà com utilitzar Regex a Excel, tant a la cel·la com a través de bucles VBA, per extreure, coincidir i substituir patrons. També parlarem de la configuració necessària, els caràcters especials per a Regex a Excel i les funcions integrades alternatives com Esquerra, Mitjana, Dreta i Instr.

Comandament Descripció
CreateObject("VBScript.RegExp") Crea un objecte RegExp per gestionar expressions regulars.
regex.Pattern Defineix el patró que cal cercar al text.
regex.Global Especifica si l'expressió regular ha de trobar totes les coincidències (true) o només la primera (falsa).
regex.Test(cell.Value) Comprova si el valor de la cel·la coincideix amb el patró d'expressió regular.
regex.Execute(cell.Value) Executa el patró d'expressió regular al valor de la cel·la i retorna les coincidències.
cell.Offset(0, 1).Value Accedeix a la cel·la una columna a la dreta de la cel·la actual.
For Each cell In Selection Recorre cada cel·la de l'interval seleccionat.

Aprofundeix en VBA per a Regex a Excel

Els scripts proporcionats anteriorment mostren com utilitzar-los Regex en Microsoft Excel utilitzant VBA (Visual Basic per a aplicacions). El primer guió, Sub RegexInCell(), inicialitza a RegExp objecte utilitzant CreateObject("VBScript.RegExp"). A continuació, aquest objecte es configura amb un patró, en aquest cas, \d{4}, perquè coincideixi amb un nombre de 4 dígits. El Global la propietat està establerta a True per assegurar-se que es troben totes les coincidències del valor de la cel·la. A continuació, l'script fa un bucle per cada cel·la de l'interval seleccionat utilitzant For Each cell In Selection. Si el regex.Test(cell.Value) El mètode retorna true, indicant una coincidència, el valor coincident es col·loca a la cel·la adjacent utilitzant cell.Offset(0, 1).Value. Si no es troba cap coincidència, "Cap coincidència" es col·loca a la cel·la adjacent.

El segon guió, Sub ExtractPatterns(), és similar però apunta a un rang específic, Range("A1:A10"), per demostrar l'extracció de patrons sobre una àrea predefinida. Utilitza el patró [A-Za-z]+ per fer coincidir qualsevol paraula composta per lletres. Aquest script també utilitza regex.Test i regex.Execute mètodes per trobar coincidències i col·locar la primera coincidència a la cel·la adjacent. Aquests guions il·lustren la potent combinació de Regex i Excel VBA per a la manipulació de text, proporcionant un mètode per realitzar cerques complexes i extracció de dades que seria feixuc només amb les funcions integrades d'Excel.

Ús de VBA per a Regex a Excel: funcions a la cel·la i bucle

Ús de VBA (Visual Basic per a aplicacions)

Sub RegexInCell()
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "\d{4}" ' Example pattern: Match a 4-digit number
    regex.Global = True
    Dim cell As Range
    For Each cell In Selection
        If regex.Test(cell.Value) Then
            cell.Offset(0, 1).Value = regex.Execute(cell.Value)(0)
        Else
            cell.Offset(0, 1).Value = "No match"
        End If
    Next cell
End Sub

Extracció de patrons mitjançant Regex a Excel VBA

Ús de VBA (Visual Basic per a aplicacions)

Sub ExtractPatterns()
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "[A-Za-z]+" ' Example pattern: Match words
    regex.Global = True
    Dim cell As Range
    For Each cell In Range("A1:A10") ' Adjust range as needed
        If regex.Test(cell.Value) Then
            cell.Offset(0, 1).Value = regex.Execute(cell.Value)(0)
        Else
            cell.Offset(0, 1).Value = "No match"
        End If
    Next cell
End Sub

Ús de VBA per a Regex a Excel: funcions a la cel·la i bucle

Ús de VBA (Visual Basic per a aplicacions)

Sub RegexInCell()
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "\d{4}" ' Example pattern: Match a 4-digit number
    regex.Global = True
    Dim cell As Range
    For Each cell In Selection
        If regex.Test(cell.Value) Then
            cell.Offset(0, 1).Value = regex.Execute(cell.Value)(0)
        Else
            cell.Offset(0, 1).Value = "No match"
        End If
    Next cell
End Sub

Extracció de patrons mitjançant Regex a Excel VBA

Ús de VBA (Visual Basic per a aplicacions)

Sub ExtractPatterns()
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "[A-Za-z]+" ' Example pattern: Match words
    regex.Global = True
    Dim cell As Range
    For Each cell In Range("A1:A10") ' Adjust range as needed
        If regex.Test(cell.Value) Then
            cell.Offset(0, 1).Value = regex.Execute(cell.Value)(0)
        Else
            cell.Offset(0, 1).Value = "No match"
        End If
    Next cell
End Sub

Millora d'Excel amb Regex i VBA

Mentre que Excel està equipat amb potents funcions integrades com ara LEFT, MID, RIGHT, i INSTR, la integració d'expressions regulars (Regex) amb VBA pot ampliar significativament les capacitats de manipulació de text d'Excel. Regex permet la concordança de patrons complexos i l'extracció de text que seria difícil d'aconseguir només amb les funcions estàndard d'Excel. Per exemple, podeu utilitzar Regex per extreure adreces de correu electrònic, números de telèfon o formats específics de grans conjunts de dades. Això pot ser especialment útil per netejar i estandarditzar dades, on s'han d'identificar i extreure patrons específics de manera eficient.

La configuració de Regex a Excel requereix l'ús de VBA, ja que Excel no admet de forma nativa les funcions Regex a les cel·les. En crear una macro VBA, podeu aplicar patrons de regex als intervals seleccionats o columnes senceres, automatitzant el procés d'extracció i manipulació de dades. Aquest enfocament no només estalvia temps, sinó que també redueix el risc d'errors associats amb la manipulació manual de dades. A més, la combinació de Regex amb VBA permet un processament de dades més dinàmic i flexible, permetent als usuaris adaptar els seus scripts a requisits i conjunts de dades específics.

Preguntes i respostes habituals sobre l'ús de Regex a Excel

  1. Com habilito VBA a Excel?
  2. Podeu habilitar VBA a Excel si aneu a la pestanya Desenvolupador i feu clic a Visual Basic per obrir l'editor VBA.
  3. Puc utilitzar Regex directament a les fórmules d'Excel?
  4. No, Regex no s'admet de manera nativa a les fórmules d'Excel. Heu d'utilitzar VBA per utilitzar Regex a Excel.
  5. Quin és l'avantatge d'utilitzar Regex sobre les funcions integrades?
  6. Regex proporciona més flexibilitat i potència en la concordança de patrons i l'extracció de text en comparació amb les funcions integrades com LEFT, MID, i RIGHT.
  7. Com puc extreure adreces de correu electrònic amb Regex a Excel?
  8. Podeu utilitzar un patró Regex com ara [\w\.-]+@[\w\.-]+\.\w{2,4} en un script VBA per extreure adreces de correu electrònic d'un conjunt de dades.
  9. Quin és un cas d'ús pràctic de Regex a Excel?
  10. Un cas d'ús pràctic de Regex a Excel és netejar i estandarditzar números de telèfon o extreure formats de dades específics d'un conjunt de dades gran.
  11. Regex distingeix entre majúscules i minúscules a VBA?
  12. De manera predeterminada, Regex a VBA distingeix entre majúscules i minúscules, però podeu configurar-lo IgnoreCase propietat a True perquè no distingeixi entre majúscules i minúscules.
  13. Com puc gestionar diverses coincidències en una cel·la utilitzant Regex?
  14. Podeu configurar el Global propietat de l'objecte Regex a True per trobar totes les coincidències en un valor de cel·la.
  15. Quins són alguns dels patrons de regex habituals?
  16. Els patrons Regex comuns inclouen \d+ per a dígits, \w+ per paraules, i [A-Za-z] per lletres.
  17. Puc substituir el text amb Regex a VBA?
  18. Sí, podeu utilitzar el regex.Replace mètode per substituir els patrons coincidents amb text nou a VBA.

Conclusió: el poder de Regex a Excel

L'aprofitament de Regex a Excel mitjançant scripts VBA augmenta significativament les habilitats de manipulació de dades, cosa que facilita la gestió del processament de text complex. Mitjançant la integració d'aquests scripts, els usuaris poden automatitzar l'extracció i la substitució de patrons específics dins dels conjunts de dades, millorant l'eficiència i la precisió. Tot i que és potent, Regex s'ha d'utilitzar amb criteri juntament amb les funcions integrades d'Excel per garantir un rendiment òptim per a diverses tasques de manipulació de text.