Integración de aplicaciones: creación de una correspondencia semántica de códigos mediante Excel

cabeceraUna tarea ineludible cuando integramos varias aplicaciones es la creación de diccionarios de códigos de forma que se pueda traducir la información, con diferentes códigos pero la misma semántica, entre sí. Un ejemplo típico podría ser el sexo: mientras una aplicación usa ‘H’ para identificar al varón, otra aplicación puede usar ‘V’. En este ejemplo es poco problemático construir una correspondencia semántica entre códigos, pero si tenemos que construir, por ejemplo, una correspondencia entre códigos de varios cientos de ciudades nos encontramos ante un problema.

Existen diversas herramientas avanzadas para solventar estos problemas, pero dentro de un desarrollo de recursos limitados (esto seguro que le suena a mucha gente) donde el uso de estas herramientas pueda resultar como matar moscas a cañonazos, resulta incuestionable la utilidad de las hojas de cálculo.

Vamos a partir de un ejemplo muy común y veremos la solución usando Excel. Supongamos que tenemos dos listados de provincias españolas. En uno de ellos las tenemos identificadas numéricamente según códigos postales; a esta hoja la llamaremos “CP”. En el otro listado, las provincias están identificadas con códigos no numéricos del ministerio de interior; a esta hoja la llamaremos “MI”. Finalmente queremos conseguir hacer la correspondencia y obtener algo como esto, que luego podríamos guardar en una tabla de nuestra aplicación o donde y de la forma que más nos convenga; a esta tercera hoja la podemos llamar “Resultado”.

La hoja de cálculo de partida sería la siguiente: HojaProvincias_Inicial

Al inspeccionar la hoja nos encontramos con los primeros típicos problemas:

  1. En un caso las provincias vienen con la primera letra mayúscula y en otro caso todo en mayúsculas.
  2. En un caso vienen incluídas las tildes y en otro no.
  3. La descripción no siempre coincide del todo.

El primer paso para solucionar estos problemas consiste en normalizar los literales. Lo pasaremos todo a mayúsculas, sin tildes o acentos y sin espacios sobrantes. Para el paso a mayúsculas la solución es sencilla, aplicar la fórmula MAYUSC de Excel. Después necesitamos eliminar las tildes. Existen varias formas de solventar este problema. La más simple, aplicando la siguiente fórmula nos quitamos los dos primeros problemas de un plumazo:

=ESPACIOS(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(SUSTITUIR(MAYUSC([celda]);"Á";"A");"É";"E");"Í";"I");"Ó";"O");"Ú";"U"))

Sustituyendo [celda] por la referencia a la celda que queremos transformar. En nuestro caso, con esto nos valdría como solución, pero en casos en los que tratemos con varios idiomas será necesaria una función más avanzada para hacer sustitución de tildes y otros caracteres. Una buena solución sería crear la siguiente función VBA en un módulo de nuestra hoja de cálculo:

Function SinAcentos(ByVal Celda As String) As String

Dim temp, strA$, strB$, i&, p&
strA = "áàäâãåçéèêëíìîïóòôöõð?úùûüýÿ?" & _
       "ÁÀÄÂÃÅÇÉÈÊËÍÌÎÏÓÒÔÖÕÐ?ÚÙÛÜÝ?? "
strB = "aaaaaaceeeeiiiioooooosuuuuyyz" & _
       "AAAAAACEEEEIIIIOOOOOOSUUUUYYZ"
temp = Celda

For i = 1 To Len(temp)
    p = InStr(strA, Mid(temp, i, 1))
    If p > 0 Then Mid(temp, i, 1) = Mid(strB, p, 1)
Next

SinAcentos = temp

End Function

Usando esta función podremos hacer cualquier transformación de un carácter por otro muy fácilmente, y aplicar la normalización en la columna C de cada hoja así:

=SinAcentos(ESPACIOS(MAYUSC([celda])))

La hoja Excel con las provincias normalizadas quedaria así: HojaProvincias_Normalizadas

Ahora que ya tenemos las descripciones de provincias normalizadas, nos queda el tercer problema para terminar por conseguir una solución: las descripciones no coinciden de forma exacta. Por ejemplo, tenemos “SANTA CRUZ DE TENERIFE” y “S. CRUZ DE TENERIFE”.

La primera solución que se puede venir a la cabeza es modificar a mano los registros que no coincidan. Para el ejemplo que tenemos, donde tratamos con alrededor de 50 registros puede ser la mejor opción, pero ¿lo haremos si disponemos de varios cientos o miles de registros? Para estos casos la solución ideal será aplicar el algoritmo de la distancia de Levenshtein. En la wikipedia se describe así:

En Teoría de la información y Ciencias de la Computación se llama Distancia de Levenshtein, distancia de edición, o distancia entre palabras, al número mínimo de operaciones requeridas para transformar una cadena de caracteres en otra. Se entiende por operación, bien una inserción, eliminación o la sustitución de un carácter.

Pues bien, implementamos en VBA el algoritmo de Levenshtein en nuestro módulo de Excel de la siguiente forma:

Function Levenshtein(ByVal string1 As String, ByVal string2 As String) As Long

Dim i As Long, j As Long
Dim string1_long As Long
Dim string2_long As Long
Dim distancia() As Long

string1_long = Len(string1)
string2_long = Len(string2)
ReDim distancia(string1_long, string2_long)

For i = 0 To string1_long
    distancia(i, 0) = i
Next

For j = 0 To string2_long
    distancia(0, j) = j
Next

For i = 1 To string1_long
    For j = 1 To string2_long
        If Asc(Mid$(string1, i, 1)) = Asc(Mid$(string2, j, 1)) Then
            distancia(i, j) = distancia(i - 1, j - 1)
        Else
            distancia(i, j) = Application.WorksheetFunction.Min _
            (distancia(i - 1, j) + 1, _
             distancia(i, j - 1) + 1, _
             distancia(i - 1, j - 1) + 1)
        End If
    Next
Next

Levenshtein = distancia(string1_long, string2_long)

End Function

Después necesitaremos una función que saque partido de este algoritmo y nos busque los resultados deseados. En la hoja que habíamos denominado “Resultado” tendremos referenciados los códigos postales y descripciones de provincias de la hoja “CP”. Necesitamos, por cada una de estas provincias, buscar entre las provincias que se encuentren en la hoja “MI” y devolver el código de aquella cuyo mejor resultado dé el algoritmo de Levenshtein.

Para ello lo ideal sería crear una nueva función VBA en nuestro módulo Excel a la que pasamos como parámetros la provincia que vamos a buscar, el rango de búsqueda, y dentro del rango la columna donde buscaremos los valores a comparar y finalmente la columna donde se encuentra el código resultante a devolver. Esta función sería algo así:

Function Correspondencia(ByVal string1 As String, ByVal rango As Range, ByVal columnaABuscar As Integer, ByVal columnaADevolver As Integer) As String

Dim i As Long
Dim levDist As Long
Dim levTemp As Long
Dim result As String

levDist = Len(string1)
For i = 1 To rango.Rows.Count
    levTemp = Levenshtein(string1, rango.Cells.Item(i, columnaABuscar))
    If levTemp = 0 Then
        result = rango.Cells.Item(i, columnaADevolver)
        Exit For
    Else
        If levTemp < levDist Then
            levDist = levTemp
            result = rango.Cells.Item(i, columnaADevolver)
        End If
    End If
Next

Correspondencia = result

End Function

Aplicamos esta función en la hoja de resultado usando referencias absolutas para definir el rango de celdas donde buscar; por ejemplo, en la celda C2 de la hoja “Resultado” tendríamos:

=Correspondencia(B2;MI!$A$2:$C$53;3;1)

Y de esta forma conseguimos el resultado final, que se puede descargar aquí: HojaProvincias_Resultado

Para terminar el post, una cuantas observaciones/comentarios:

  • Para que todo esto funcione es necesario habilitar las macros en Excel y habilitar el editor de Visual Basic, dependiendo de la versión instalada.
  • Aplicar el algoritmo de Levenshtein es algo que sólo se puede hacer si existe tolerancia a errores, pues con él no siempre se consiguen los resultados esperados.
  • Otra opción a considerar además de Levenshtein es el algoritmo n-gram para la detección de secuencias. En algunos casos puede ser incluso más recomendable que Levenshtein. Sería una mejora “para nota” crear un algoritmo que combine a ambos. Una versión fácilmente adaptable a VBA del algoritmo n-gram se encuentra aquí.
Anuncios

Un pensamiento en “Integración de aplicaciones: creación de una correspondencia semántica de códigos mediante Excel

  1. Sobre el contenido del post no te puedo comentar mucho.. :), sólo que parece que te lo estás currando un montón, así que te auguro mucho éxito con esta “re-vuelta” del blog. Te deseo toda la suerte del mundo y, sobre todo, que lo disfrutes mucho.
    Un saludo

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s