Trabajar con códigos alfanuméricos en Excel.

Página inicial

Una duda que se presenta con cierta frecuencia entre los usuarios de Excel es si es posible presentar ordenada una tabla por sus códigos alfanuméricos. En este trabajo estoy dando por sentado que estamos hablando de una serie de códigos constituidos por el mismo número de caracteres (en el caso del ejemplo que veremos, 5 caracteres), puesto que el trabajo con códigos alfanuméricos de longitud variable plantea problemas de difícil solución.

Antes de comenzar, recordemos que Excel dispone de la función K.ESIMO.MAYOR() (y su pareja K.ESIMO.MENOR). Lo que hacen estas funciones es presentar ordenada una lista de números. El orden depende de la función que se use: K.ESIMO.MAYOR() ordena de forma descendente mientras que K.ESIMO.MENOR lo hace de forma ascendente.

El problema, en lo que respecta al tema que estamos tratando, es que ninguna de esas dos funciones puede procesar datos alfanuméricos. Por ejemplo, imaginemos que en el rango A1:C6 de una hoja de Excel tenemos una lista de productos con sus ventas del último ejercicio:

Como se puede apreciar, la lista se encuentra ordenada de forma descendente por la cifra de ventas del último ejercicio (columna C). Si se deseara obtener las ventas ordenadas de forma ascendente, lo único que habría que hacer es utilizar la función K.ESIMO.MENOR() en otra columna. Pero si lo que se necesitase fuese la lista ordenada por código, habría que ordenar los datos por esa columna (dado que las funciones K.ESIMO.() no pueden procesar textos), lo que lógicamente implicaría perder el orden por los importes de venta.

Por lo tanto vamos a suponer, como ejercicio para desarrollar el tema propuesto, que se necesita que se presente esta misma tabla pero ordenada por el código del producto, y que esta segunda tabla esté situada a la derecha de la primera. Lógicamente, la forma más sencilla de conseguirlo sería copiar la lista original, pegarla a la derecha y ordenarla por la columna correspondiente al código. Esto tiene el inconveniente de que si se añaden o eliminan productos o se modifica alguno de los datos de la tabla original dichas modificaciones no serán actualizadas en su copia.

Incluso esto se podría solucionar utilizando VBA, concretamente el evento Worksheet_Change de la hoja donde se encuentre la tabla, de forma que al detectarse una modificación que afectara a alguna de las celdas de la tabla se produjera su copiado, pegado y ordenación. Por ejemplo, el siguiente código haría eso:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.Count > 1 Then Exit Sub
    
    With Application
        If .Intersect(Target, Me.[A1].CurrentRegion) Is Nothing Then Exit Sub
        
        .EnableEvents = False
        Me.[E1].CurrentRegion.ClearContents
        Target.CurrentRegion.Copy Destination:=Me.[E1]
        .CutCopyMode = False
        .EnableEvents = True
    End With
    
    
    Me.Sort.SortFields.Clear
    Me.Sort.SortFields.Add Key:=Me.[E1].CurrentRegion.Offset(1).Resize(Me.[E1].CurrentRegion.Rows.Count - 1, 1), _
                           SortOn:=xlSortOnValues, _
                           Order:=xlAscending, _
                           DataOption:=xlSortNormal
    
    With Me.Sort
        .SetRange Me.[E1].CurrentRegion
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Sub

(Código VBA nº 1)

Puede verse el código anterior funcionando en este libro de ejemplo (sólo funcionará a partir de Excel 2007)

Ahora supongamos que no fuese posible o no se desease utilizar VBA en el libro y, por lo tanto, que fuese necesario obtener el listado de productos, ordenado por su código, usando sólo funciones de hoja de cálculo de Excel.

Lo primero que debemos considerar es de qué caracteres y números están constituidos los códigos alfanuméricos. En el ejemplo con el que estamos trabajando, resulta evidente que los caracteres son desde la A a la Z y los números del 0 al 9. Vamos a suponer que entre los caracteres no se encuentra la Ñ, aunque las fórmulas que veremos después podrían incluirla, si fuese necesario, con algunas pequeñas modificaciones.

Como tenemos que trabajar con K.ESIMO.(), la condición indispensable es que consigamos representar numéricamente de alguna forma las letras y números con los que se forman los códigos. ¿Existe alguna forma de conseguirlo en Excel?.

De forma cotidiana estamos acostumbrados a trabajar con números en base 10 (o decimal), pero sabemos que esta no es sino una de las (en teoría infinitas) bases posibles. Otras bases con las que trabajan algunas personas, como por ejemplo programadores, y máquinas como las computadoras, son por ejemplo la base 2 (binario), la base 8 (octal) o la base 16 (hexadecimal).

En las bases que requieren menos de los 10 símbolos numéricos de la base decimal, se utilizan los números desde el 0 hasta donde sea necesario. Así, por ejemplo, en base 2 se utilizan el 0 y el 1, en base 8 los números desde el 0 al 7, etc.

En las bases que requieren más de los 10 símbolos numéricos habituales, se añaden las letras necesarias, empezando por la A. Así, por ejemplo, en base 16 se añaden a los 10 números las 6 letras A B C D E F, por lo que la equivalencia entre la base 10 y la 16 sería:

Ahora imaginemos que quisiésemos trabajar en base 36. En tal caso, podríamos utilizar los números desde el 0 hasta el 9 y los 26 caracteres que van desde la A hasta la Z. Es decir, precisamente los números y letras que utilizan los códigos de producto del ejemplo. Así que ya hemos dado el primer paso en el camino cuya meta deberá ser la conversión a números de dichos códigos.

El siguiente paso tendrá que ser, lógicamente, la propia conversión de esos códigos a valores numéricos mediante alguna fórmula que use tan sólo funciones de hoja de cálculo, y para desarrollarla lo primero que deberemos recordar es el concepto de "descomposición polinómica": un número se puede descomponer utilizando potencias de su base. Lo anterior quizás "suene" complicado, pero mediante un ejemplo es fácil de ver.

Imaginemos un número en base 10 (que es con la que trabajamos la mayoría de nosostros todos los días), por ejemplo el 3597. Este número se podría expresar, mediante una fórmula de Excel, de la siguiente forma:

=3*10^3+5*10^2+9*10^1+7*10^0

Si ponemos esta fórmula en una celda, veremos que su resultado es 3597. La fórmula representa la descomposición polinómica de 3597.

Excel, obviamente, trabaja en base 10, así que la pregunta que surge es: ¿y qué sucedería si intentásemos aplicar en Excel el concepto de descomposición polinómica a un número cuya base no fuese 10? Pues, sencillamente, que Excel convertiría el número a su representación en base 10.

Por ejemplo, la fórmula (o, supongo, una de las posibles fórmulas) para convertir a decimal el número hexadecimal situado en A1 es:

(fórmula nº. 1)

Por ejemplo, supongamos que en A1 tenemos el número hexadecimal E0D₁₆ (el ₁₆ no hay que incluirlo, se trata de la base en la que está el número). De acuerdo con la tabla base 10 <--> base 16 que vimos antes, la descomposición polinómica de este número sería:

=14*16^2+0*16^1+13*16^0

Si ponemos esta fórmula en una celda, veremos que su resultado es 3597. La fórmula nº. 1 devolverá el mismo resultado, con la evidente ventaja de que no es necesario escribir los términos de la descomposición polinómica "a mano": la misma fórmula se encarga de hacer todo el trabajo.

Todo esto está muy bien, pero seguimos necesitando una fórmula capaz de devolver el valor en base 10 de cualquier número expresado en base 36.

Es fácil construir una tabla de equivalencias base 36 <--> base 10 similar a la que hemos visto para la base 16:

Así que la fórmula necesaria para convertir un código "base 36" en su representación numérica en base 10 es prácticamente igual a la de la base 16. Las únicas diferencias son que hay que añadir los caracteres comprendidos entre la G y la Z y sustituir el 16 por 36:

(fórmula nº. 2)

Por ejemplo, la fórmula para la descomposición polinómica en Excel del número 2RX₃₆ sería:

=2*36^2+27*36^1+33*36^0

Esta fórmula devolverá de nuevo 3597, puesto que 2RX es la representación en base 36 de 3597. La fórmula nº. 2 devolverá el mismo resultado.

Ahora ya disponemos de una forma de convertir en valores numéricos los códigos de producto. Necesitaremos, eso sí, una columna auxiliar en la que volcar dichos valores para luego poder evaluarlos mediante el K.ESIMO.() que nos interese. Vamos a suponer que dicha columna fuese la D (esta columna auxiliar, lógicamente, se podría ocultar  después). Ponemos en D2 la fórmula nº. 2, sustituyendo A1 por A2, es decir:

y una vez introducida, la copiamos y pegamos en el rango D3:D6. El resultado debería ser:

Quizás llame la atención el tamaño de los números que aparecen en la columna D, pero es que hay que tener en cuenta que estamos utilizando las potencias desde 0 hasta 4 de 36. Por ejemplo, si el código fuese ZZZZZ (el más alto posible para un código de 5 caracteres), la fórmula devolvería el número 60.466.175 ya que la descomposición polinómica de ZZZZZ₃₆ es:

=35*36^4+35*36^3+35*36^2+35*36^1+35*36^0

lo que equivale a:

=35*1679616+35*46656+35*1296+35*36+35*1

es decir:

=58786560+1632960+45360+1260+35
 

Con códigos de 5 caracteres alfanuméricos no hay mayor problema, pero ahora imaginemos que los códigos fuesen de 10 caracteres alfanuméricos. En este caso, la descomposición polinómica del código ZZZZZZZZZZ sería:

=35*36^9+35*36^8+35*36^7+35*36^6+35*36^5+35*36^4+35*36^3+35*36^2+35*36^1+35*36^0

que en Excel arroja el resultado 3.656.158.440.062.970, incorrecto puesto que debería ser 3.656.158.440.062.975

El problema con un número tan grande es que supera la precisión numérica máxima de Excel, que es de 16 bytes (15 dígitos más el signo), por lo que Excel convierte a ceros todos los dígitos a partir del decimosexto incluido (contando de izquierda a derecha). Conociendo esta limitación, estamos en condiciones de calcular cuál sería el mayor código (esto es, número base 36) que Excel puede procesar con garantías de que devolverá el resultado correcto en base 10: el 9UGXNORJLS₃₆, que en base 10 es el 1.000.000.000.000.000

Antes de continuar conviene que quede claro que los caracteres con los que hemos formado la base 36 no tiene por qué ser obligatoriamente los comprendidos primero entre el 0 y el 9 y luego entre la A y la Z. Por ejemplo, quizás podría interesar que "nuestra" base 36 estuviese formada al revés, es decir, primero con las letras y luego con los números. En este caso, lo único que habría que hacer es sustituir la cadena "0123456789abcdefghijklmnopqrstuvwxyz" por "abcdefghijklmnopqrstuvwxyz0123456789". Incluso sería posible establecer otro orden cualquiera, o utilizar otros caracteres. Debe quedar claro que lo que importa es la posición de cada carácter en la cadena, no de qué carácter en concreto se trate.

También resultaría posible que los códigos estuvieran constituidos por un conjunto más grande o más pequeño de caracteres, aunque si fuesen necesarios más habría que recurrir a caracteres que tal vez pudiesen resultar un poco "extraños", como por ejemplo signos de puntuación, o a diferenciar letras mayúsculas y minúsculas, lo que requeriría modificar las fórmulas puesto que la función HALLAR() que estamos utilizando en ellas no hace esta diferenciación. La función ENCONTRAR(), sin embargo, sí diferencia entre mayúsulas y minúsculas, por lo que podría utilizarse en este supuesto.

Como hemos visto ya, es posible averiguar el valor en base 10 de una base >10 usando funciones de hoja de cálculo en una sola fórmula. Sería posible hacer la operación inversa también mediante una fórmula, pero no tendría la versatilidad de la que hemos visto puesto que habría que modificarla a mano, en función del número de caracteres a procesar.

Por ejemplo, para códigos constituidos por cinco caracteres la fórmula sería (suponiendo que la equivalencia en base 10 del código estuviese en A1):

=EXTRAE("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";1+RESIDUO(ENTERO(A1/36^4);36);1)&EXTRAE("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";1+RESIDUO(ENTERO(A1/36^3);36);1)&EXTRAE("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";1+RESIDUO(ENTERO(A1/36^2);36);1)&EXTRAE("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";1+RESIDUO(ENTERO(A1/36^1);36);1)&EXTRAE("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";1+RESIDUO(ENTERO(A1/36^0);36);1)

El problema con esta fórmula es que si la longitud de los códigos fuese distinta de cinco caracteres, habría que añadir o quitar funciones EXTRAE() y modificar todos los exponentes, algo molesto y peligroso porque es un proceso sujeto a posibles errores.

Por lo tanto, para averiguar el mayor código posible que, como hemos visto, está limitado por la precisión numérica de Excel y por el número de caracteres que compongan el conjunto de caracteres con los que se pueden construir los códigos, lo mejor es recurrir a VBA:

Option Base 1

Public Function MayorCódigo(ByVal sCaracteres As String) As String
    Dim mtr() As Byte
    Dim n As Integer
    
    'Redimensionar mtr() a tantas filas como caracteres tendrá el código
    ReDim mtr(Int(WorksheetFunction.Log(1E+15, Len(sCaracteres))) + 1)
    
    'Calcular los restos y almacenarlos en mtr()
    For n = 1 To UBound(mtr)
        mtr(n) = 1 + Resto(Int(1E+15 / Len(sCaracteres) ^ (n - 1)), Len(sCaracteres))
    Next n
    
    'Construir el código
    For n = UBound(mtr) To LBound(mtr) Step -1
        MayorCódigo = MayorCódigo & Mid(sCaracteres, mtr(n), 1)
    Next n
End Function

Private Function Resto(ByVal cNumerador As Double, ByVal cDenominador As Double) As Double
    'Debido a que el límite con el que puede trabajar la función Mod de VBA es el del tipo de datos _
     Long (2.147.483.647), es necesario calcular los restos "a mano".
    Resto = cNumerador - cDenominador * Int(cNumerador / cDenominador)
End Function

(Código VBA nº 2)

Como se puede ver en el código, la función MayorCódigo hace uso de una auxiliar llamada Resto, que resulta necesaria por el motivo que se expone como comentario en el cuerpo de la propia función.

El argumento que se le ha de pasar a esta función es la cadena de caracteres que pueden conformar el código, y ella se encarga de calcular el código más alto posible para ese conjunto de caracteres, es decir, el código correspondiente al 1.000.000.000.000.000

Por ejemplo, para averiguar el código más alto de la cadena con la que estamos trabajando en el ejemplo, la sintaxis sería:

=MayorCódigo("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ")

que devuelve el código que vimos antes, esto es el 9UGXNORJLS. Ahora supongamos que el orden fuese primero las letras y luego los números. En tal caso, la sintaxis sería:

=MayorCódigo("ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789")

y el código correspondiente al 1.000.000.000.000.000 el J4Q7XY1TV2

Trabajar con menos caracteres resulta sencillísimo. Por ejemplo, supongamos que deseásemos códigos formados con un juego de 20 caracteres: del 0 al 9 y de la A a la J. Podemos averiguar cual sería en este caso el código más grande posible mediante:

=MayorCódigo("0123456789ABCDEFGHIJ")

que devuelve 4HD2A0000000. Un último ejemplo: códigos formados tan sólo por letras, incluyendo la Ñ, lo que totaliza 27 caracteres: ("ABCDEFGHIJKLMNÑOPQRSTUVWXYZ"). En este caso, el último código posible se obtendría mediante:

=MayorCódigo("ABCDEFGHIJKLMNÑOPQRSTUVWXYZ")

que devuelve EWDSBUKEHWB.

Este código máximo puede resultar útil porque de forma indirecta nos está diciendo cual podría ser la longitud máxima de un código constituido con la repetición del último carácter del juego de caracteres que conforman el código en cuestión. En el ejemplo con el que estamos trabajando dicho último carácter es la Z, y comoquiera que el código más alto es el 9UGXNORJLS (10 caracteres), resulta evidente que la longitud máxima del código constituido con la repetición de zetas tendrá que ser 9, es decir el ZZZZZZZZZ.

También de forma indirecta, el código devuelto por la función nos está indicando cual es el máximo código posible que esté formado por la repetición de un mismo carácter. En el ejemplo, dicho código sería el 9999999999 puesto que el resultado devuelto por la función nos está diciendo que el código máximo empieza por 9 y tiene 10 caracteres de longitud.

Ahora terminaremos el desarrollo de la solución al problema inicial. Al llegar a este punto, tenemos en nuestra columna auxiliar D la representación numérica de los códigos de la columna A. Ha llegado el momento de utilizar la función K.ESIMO.() que necesitemos. Si queremos los códigos ordenados de menor a mayor, la fórmula a poner en cualquier celda de la fila 2 de una columna vacía sería:

=K.ESIMO.MENOR($D$2:$D$6;FILA()-1)

Luego habría que copiar y pegar esta fórmula en el rango D3:D6. Con esto obtendríamos, ordenados, los valores en base 10 de los códigos. Pero esto no resulta de mucha utilidad. Como lo que queremos es un listado de los códigos en sí y de sus datos asociados, tendremos que combinar K.ESIMO.MENOR() con COINCIDIR() e INDICE():

=INDICE($A$2:$A$6;COINCIDIR(K.ESIMO.MENOR($D$2:$D$6;FILA()-1);$D$2:$D$6;0))

La fórmula anterior podría ponerse en, por ejemplo, E2 y devolvería el código menor de todos. En F2 iría entonces:

=INDICE($B$2:$B$6;COINCIDIR(K.ESIMO.MENOR($D$2:$D$6;FILA()-1);$D$2:$D$6;0))

que devolvería el nombre del producto con dicho código, y finalmente en G2:

=INDICE($C$2:$C$6;COINCIDIR(K.ESIMO.MENOR($D$2:$D$6;FILA()-1);$D$2:$D$6;0))

que devolvería el importe de las ventas del producto. En este libro de ejemplo se puede ver la hoja tal como quedaría.

Si se necesitase que los códigos estuviesen ordenados de mayor a menor, lo único que habría que hacer es sustituir MENOR por MAYOR.

Con lo anterior el problema propuesto quedaría resuelto, pero al desarrollar su solución pienso que han podido surgir algunas cuestiones sobre el tema que podrían resultar interesantes si profundizásemos en ellas.

Cualquiera que haya trabajado con códigos alfanuméricos sabe que a menudo resulta difícil crear nuevos códigos de forma secuencial. Por ejemplo, usando el tipo de códigos que hemos visto en el ejemplo (5 caracteres de longitud usando los caracteres de la cadena "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"), imaginemos que tuviésemos que crear el código siguiente al J9ZUZ, por poner un ejemplo. Para alguien que supiese bien cómo se generan los códigos no sería difícil determinar que el siguiente código es el J9ZV0, pero para alguien que no conociese el sistema no resultaría tan sencillo, y en cualquier caso un proceso manual de este tipo está siempre sujeto a errores, puesto que después de todo lo que se está haciendo no es otra cosa que contar en una base que no es nuestra (para la mayoría de la gente) familiar base 10.

Ahora imaginemos que se necesitase crear decenas o centenares de nuevos códigos a partir de uno dado, y/o que la secuencia no fuese consecutiva sino, por ejemplo en saltos de 10 unidades (es decir que, por ejemplo, al código XZ89P debiera seguirle el XZ89Z, a éste el XZ8A9, etc.). En este caso la consecución de los códigos de forma manual sería bastante más difícil, y la posibilidad de errores mucho mayor, y además acumulativa porque muy probablemente el error en el cálculo de un código nuevo haría que los que le siguieran estuviesen también equivocados.

Durante el desarrollo del ejemplo que hemos visto han ido apareciendo funciones capaces de hacer las conversiones base 36 a base 10 y base 10 a base 36. En el primer caso ya vimos que podían utilizarse funciones de hoja de cálculo en una sola fórmula, cosa que no era posible en el segundo, por lo que había que recurrir a VBA. Ahora pensemos qué tendría que hacer una función VBA capaz de devolver automáticamente el código siguiente (o, para el caso, situado n posiciones hacia adelante o hacia atrás) a uno dado.

El esquema del algoritmo de una tal función tendrían que ser:

1. Convertir el código a su valor en base 10 (puesto que Excel trabaja en base 10).

2. Sumarle o restarle 1 (o la cantidad que se desee).

3. Revertir el valor así obtenido a su base original.

Los pasos 1 y 2 sí podrían realizarse utilizando funciones de Excel en una fórmula capaz de ajustarse ella misma a la longitud que tenga el código, pero el 3, como ya vimos antes, no. Por lo tanto lo mejor es recurrir de nuevo a VBA:

Option Base 1

Public Function Generar_Código(ByVal sCódigoInicial As String, ByVal sCaracteres As String, ByVal iPaso As Integer) As String
    Dim lValorDecimal As Long
    Dim n As Integer
    Dim mtr() As Byte
     
    'Averiguar el valor en decimal de la cadena, para luego poder sumarle iPaso
    For n = 1 To Len(sCódigoInicial)
        lValorDecimal = lValorDecimal + (InStr(sCaracteres, Mid(sCódigoInicial, n, 1)) - 1) * Len(sCaracteres) ^ (Len(sCódigoInicial) - n)
    Next n
    
    'Sumar a lValorDecimal iPaso
    lValorDecimal = lValorDecimal + iPaso
    
    'Averiguar el siguiente código
    ReDim mtr(Int(WorksheetFunction.Log(lValorDecimal, Len(sCaracteres))) + 1)
    
    For n = 1 To UBound(mtr)
        mtr(n) = 1 + Int(lValorDecimal / Len(sCaracteres) ^ (n - 1)) Mod Len(sCaracteres)
    Next n
 
    For n = UBound(mtr) To LBound(mtr) Step -1
        Generar_Código = Generar_Código & Mid(sCaracteres, mtr(n), 1)
    Next n
    
    'Ajustar al largo que tenga sCódigoInicial
    Generar_Código = Right(String(Len(sCódigoInicial), "0") & Generar_Código, Len(sCódigoInicial))
End Function

(Código VBA nº 3)

La forma de llamar a la función es muy sencilla. Por ejemplo, para averiguar el código siguiente al XZ89P, siendo el conjunto de caracteres que conforman los códigos "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ", la sintaxis sería:

=Generar_Código("XZ89P";"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";1)

que devolverá XZ89Q

Si, por ejemplo, se desease el código anterior, lo único que habría que hacer es sustituir el 1 final por -1, con lo que el resultado devuelto por la función sería ZX89O.

Para averiguar códigos situados a más distancia del dado, lo único que habría que hacer es indicar el número deseado (ya fuese positivo o negativo) como último argumento de la función.

Conviene reseñar que el código VBA asume que se está trabajando siempre con códigos de la misma longitud, de ahí que devuelva siempre una cadena de idéntica longitud a la que se le pasó a la función como su primer argumento.

Como modesto aficionado a la criptografía que soy, desde que comencé a trabajar con códigos alfanuméricos en Excel las posibilidades que ofrecen para el cifrado de mensajes despertaron mi interés.

Imaginemos que seguimos trabajando con códigos como los del ejemplo que hemos desarrollado, esto es: códigos de 5 caracteres procedentes del conjunto "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ". Ahora imaginemos que los "códigos de producto" no fuesen tales sino palabras de 5 letras, por ejemplo EXCEL y TECLA que, utilizando la jerga criptográfica, constituirán el "texto plano" o "texto en claro" (lo que se quiere cifrar).

Si aplicamos la fórmula número 2 a cada una de ellas, obtendremos que la representación en base 10 de estas dos palabras "base 36" es 25.070.349 y 49.378.366 respectivamente. Estos dos números serán el "criptograma" (el texto cifrado).
 

Ahora imaginemos que deseásemos hacer llegar este "mensaje secreto" a alguien. Lógicamente, tendríamos que haber convenido previamente tanto el conjunto de caracteres como la longitud de los códigos (recordemos, 5 caracteres en este ejemplo). Si enviáramos el criptograma desnudo, quizás levantaríamos sospechas de que estamos intentando transmitir un mensaje cifrado, pero podemos utilizar una hoja de cálculo para ocultar el mensaje, por ejemplo incrustado en algún "informe" especialmente anodino:

Los "datos" del "informe" son pura invención, tan sólo una excusa para meter de tapadillo los dos números que realmente nos interesa transmitir, los cuales están situados en B5 y B6. Así que al convertir las palabras en números y situarlos en una hoja de cálculo, como si se tratase de datos numéricos, estamos añadiendo a la cifra un poco de esteganografía.

Para descifrar el mensaje cifrado, la otra parte necesitaría una función VBA que se encargara del trabajo (ya he dicho varias veces que esto se puede hacer mediante una fórmula pero que no es aconsejable por las limitaciones que tendría). La función que utilizamos antes para calcular el mayor código posible para un conjunto de caracteres y una longitud de códigos dados servirá para la tarea, con algunas pequeñas modificaciones:

Option Base 1

Public Function Descifrar(ByVal sCaracteres As String, ByVal dNúmero As Double) As String
    Dim mtr() As Byte
    Dim n As Integer
    
    'Redimensionar mtr() a tantas filas como caracteres tendrá el código
    ReDim mtr(Int(WorksheetFunction.Log(dNúmero, Len(sCaracteres))) + 1)
    
    'Calcular los restos y almacenarlos en mtr()
    For n = 1 To UBound(mtr)
        mtr(n) = 1 + Resto(Int(dNúmero / Len(sCaracteres) ^ (n - 1)), Len(sCaracteres))
    Next n
    
    'Construir el código
    For n = UBound(mtr) To LBound(mtr) Step -1
        Descifrar = Descifrar & Mid(sCaracteres, mtr(n), 1)
    Next n
End Function

Private Function Resto(ByVal cNumerador As Double, ByVal cDenominador As Double) As Double
    'Debido a que el límite con el que puede trabajar la función Mod de VBA es el del tipo de datos _
     Long (2.147.483.647), es necesario calcular los restos "a mano".
    Resto = cNumerador - cDenominador * Int(cNumerador / cDenominador)
End Function

(Código VBA nº 4)

La sintaxis para llamar a la función es muy sencilla:

=Descifrar("Cadena de caracteres"; Número a descifrar)

Por ejemplo, para el primer número, el 25.070.349, situado en la celda B5 y que representa la palabra "EXCEL", la sintaxis sería:

=Descifrar("0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ";B5)

Aquí está el "informe" anterior, con la función Descifrar() trabajando en el rango C3:C6:

He aplicado la función también a los dos números "de relleno" para que se vea cómo producen incongruencias al ser procesados por la función. Sería posible, por puro azar, que llegase a aparecer alguna palabra con sentido, pero en cualquier caso quien cifra el mensaje debería verificar, usando esta función antes de enviarlo, que los números de relleno no producen palabras con sentido.

La cadena con la que estamos trabajando, de 36 caracteres, carece de la Ñ, del espacio y de algunos signos de puntuación que podrían resultar necesarios para transmitir mensajes en el idioma español, así que a partir de ahora trabajaremos con los caracteres "0123456789ABCDEFGHIJKLMNÑOPQRSTUVWXYZ ¿?;,.:", que totalizan 44 caracteres.

Lógicamente, al añadir caracteres lo más probable es que se reduzca el número máximo de caracteres posible en los códigos. Para averiguarlo, podemos utilizar de nuevo la función MayorCódigo (cuadro VBA nº 2), esta vez sobre este nuevo conjunto de caracteres. La función nos devolverá 1Q830Y03ZV como código más alto posible, así que la longitud máxima de los códigos que utilicen esta cadena tendrá que ser 9 caracteres.

¿Podemos intentar complicarle la tarea a un posible criptoanalista que consiguiese interceptar nuestro mensaje e intentara romperlo? Bien, no soy experto en la materia ni de lejos, pero supongo que alterando la posición de las letras y caracteres dentro de la cadena se complicaría un posible análisis del criptograma. ¿Cuánto? Pues Excel nos ofrece la solución si utilizamos la función adecuada, es decir:

=PERMUTACIONES(44;44)

la cual arroja una cifra enorme: 265827157478845 seguido de 40 ceros. Pero esto no debería impresionarnos, ya que la dificultad que añadiría esta alteración del orden en el conjunto de caracteres a un posible criptoanalista pienso que no sería mucha puesto que básicamente se trata de un cifrado por sustitución. Pero como es algo que, como vamos a ver enseguida, se puede hacer fácilmente, quizás merezca la pena.

"Barajar" los 44 caracteres a mano sería una tarea bastante aburrida, pero puede ser ejecutada rápidamente mediante una pequeña función personalizada:

Public Function BarajarCadena(ByVal sCad As String) As String
    Dim col1 As New Collection, col2 As New Collection
    Dim lngElem As Long
    Dim n As Long

    Randomize

    For n = 1 To Len(sCad)
        col1.Add Mid(sCad, n, 1)
    Next n

    For n = col1.Count To 1 Step -1
        lngElem = Int(n * Rnd + 1)
        col2.Add col1(lngElem)
        col1.Remove lngElem
    Next n

    For n = 1 To col2.Count
        BarajarCadena = BarajarCadena & col2(n)
    Next n
End Function

(Código VBA nº 5)

Al poner en una celda:

=BarajarCadena("0123456789ABCDEFGHIJKLMNÑOPQRSTUVWXYZ ¿?;,.:")

la función me ha devuelto:

"S9GE7NDIX8P,OT:¿BZ4.?0K Y;3FJ2WL6C1UHMQRAÑV5"

así que a partir de ahora utilizaré esta cadena para los ejemplos. Hay que tener la precaución de copiar la celda que contenga esta función y hacer un pegado especial de su valor en otra vacía, porque Excel podría recalcularla si se editase la celda donde se encuentre, con lo que la cadena sería sustituida por una nueva y, casi con toda certeza, completamente distinta.

Hemos visto la forma de cifrar dos palabras separadas, pero normalmente querremos cifrar un mensaje que tendrá más palabras. Vamos a suponer que dicho mensaje fuese "ESTE TEXTO, DE 86 CARACTERES DE LONGITUD, ES UNA PRUEBA PARA SER CIFRADA. ¿FUNCIONARA?" (he tenido que suprimir el acento que debería ir en FUNCIONARÁ).

Lo primero que resulta evidente es que habrá que trocear la cadena, puesto que tiene 86 caracteres pero sabemos que el número de caracteres máximo para el conjunto de 44 caracteres que estamos usando es 9. Como 9 caracteres generarían números bastante grandes y posiblemente sospechosos, quizás lo mejor sea utilizar códigos más pequeños, por ejemplo de 5 caracteres.

Esto obliga, en primer lugar, a completar el texto en claro para que su longitud total sea múltiplo de 5 (recordemos que los códigos han de tener una longitud homogénea). Así pues, añadiremos dos X al principio y dos al final, con lo que la longitud del texto en claro será de 90 caracteres, número que sí es divisible por 5.

La siguiente tarea consiste en trocear la cadena en grupos de 5 caracteres y convertirlos a base 10. Por ejemplo, si en A1 está situado el texto en claro, en A2 se podría poner la fórmula:

=SUMAPRODUCTO((HALLAR("~"&EXTRAE(EXTRAE($A$1;5*(FILA()-2)+1;5);FILA(INDIRECTO("1:"&LARGO(EXTRAE($A$1;5*(FILA()-2)+1;5))));1);"S9GE7NDIX8P,OT:¿BZ4.?0K Y;3FJ2WL6C1UHMQRAÑV5")-1)*44^(LARGO(EXTRAE($A$1;5*(FILA()-2)+1;5))-FILA(INDIRECTO("1:"&LARGO(EXTRAE($A$1;5*(FILA()-2)+1;5))))))

y copiarla y pegarla hacia abajo, hasta que aparezca el error #¡REF!, que en este caso indica que ya no quedan caracteres que extraer y convertir del texto en claro. Para el caso de este ejemplo, el rango de las celdas que contendrán el texto "base 44" convertidos a base 10 será A2:A19. He aquí un "pantallazo":

En la fórmula propuesta es de destacar un pequeño pero importante detalle: dado que el texto en claro puede contener el carácter de cierre de interrogación ? (de hecho, el texto del ejemplo lo contiene), hay que recordar que para Excel este carácter es un comodín que se puede utilizar en las búsquedas, por lo que para que HALLAR() busque exactamente el carácter y no lo interprete como comodín hay que anteponer a lo que se va a buscar el carácter ~ (código ASCII 126). Este carácter se puede obtener pulsando la tecla Alt y, sin soltarla, 126 en el teclado numérico.

Si copiamos dicho rango A2:A19 y luego hacemos un pegado especial->Valores en cualquier hoja en blanco (o en cualquier otro tipo de documento), tendremos nuestro mensaje cifrado. Como en el ejemplo anterior, podríamos intentar mimetizar un poco los números, disfrazándolos de algún tipo de "informe" o como se considerase más oportuno. Dado que ya hemos visto un ejemplo, no repetiré otra vez lo dicho al respecto.

También sería posible que emisor y receptor acordasen alterar de determinada forma el orden, o incluir los números que constituyen el criptograma cada n filas o columnas en un "informe" ad hoc. Las posibilidades son muchas, y cualquier cosa que se haga en este sentido supongo que dificultaría la labor de un posible criptoanalista que consiguiera hacerse con el criptograma.

Queda lo más importante: que el receptor y (esperemos) destinatario del mensaje sea capaz de descifrarlo. Se supone que el emisor del mensaje ha acordado con el receptor el conjunto de caracteres y la longitud de los códigos. Este intercambio se sobreentiende que, por razones obvias de seguridad, no se hace ni en la misma hoja o documento en la que se envía el criptograma ni en el libro o documento que lo contiene. De hecho, lo lógico sería que el medio de envío fuese otro completamente distinto.

Supongamos que los números se encuentran en una hoja en el rango A1:A18. La función para restaurarlos al texto en claro original sería:

Public Function Descifrar(ByVal sCaracteres As String, ByVal iLargoCódigos As Byte, ParamArray rCeldas() As Variant) As String
    'Objeto
    Dim rCelda As Range
    'Variables
    Dim mtr() As Byte
    Dim n As Integer
    Dim vRango As Variant
    
    'Redimensionar mtr() a tantas filas como el argumento iLargoCódigos
    ReDim mtr(iLargoCódigos)
    
    'Proceso
    For Each vRango In rCeldas
        For Each rCelda In vRango.Cells
            'Calcular los restos y almacenarlos en mtr()
            For n = 1 To UBound(mtr)
                mtr(n) = 1 + Resto(Int(rCelda.Value / Len(sCaracteres) ^ (n - 1)), Len(sCaracteres))
            Next n
            
            'Construir el código
            For n = UBound(mtr) To LBound(mtr) Step -1
                Descifrar = Descifrar & Mid(sCaracteres, mtr(n), 1)
            Next n
        Next rCelda
    Next vRango
    
    'Liberar objeto
    Set rCelda = Nothing
End Function

Private Function Resto(ByVal cNumerador As Double, ByVal cDenominador As Double) As Double
    'Debido a que el límite con el que puede trabajar la función Mod de VBA es el del tipo de datos _
     Long (2.147.483.647), es necesario calcular los restos "a mano".
    Resto = cNumerador - cDenominador * Int(cNumerador / cDenominador)
End Function

La sintaxis para llamar a la función es:

=Descifrar("Conjunto de caracteres"; Largo de los códigos; Celdas que contienen los números)

Como se supone que los números a descifrar se encuentran en A1:A18, la sintaxis sería:

=Descifrar("S9GE7NDIX8P,OT:¿BZ4.?0K Y;3FJ2WL6C1UHMQRAÑV5";5;A1:A18)

Respecto a esta función, cabe destacar que he usado en su declaración el argumento ParamArray para que admita una matriz de elementos Variant. Esto permite que el rango de celdas a procesar pueda tener varias áreas o varias celdas individuales. Por ejemplo, supongamos que se convino entre emisor y receptor que el criptograma estuviese distribuido en las celdas correspondientes a las filas pares de la columna A, mientras que las impares contendrían números puestos para "despistar".

En este caso, la función tendría que procesar las celdas A2, A4, A6... y así hasta A36; pero no debería hacer nada con A1, A3, A5, etc. La forma de llamar a la función en este caso sería:

=Descifrar("S9GE7NDIX8P,OT:¿BZ4.?0K Y;3FJ2WL6C1UHMQRAÑV5";5;A2;A4;A6;A8;A10;A12;A14;A16;A18;A20;A22;A24;A26;A28;A30;A32;A34;A36)

Queda por determinar la robustez de la cifra, que sospecho no será mucha, pero en cualquier caso un modesto aficionado a la criptografía como lo soy yo no podía dejar de exponer esta curiosa forma de utilizar en Excel códigos alfanuméricos para cifrar un mensaje.

Página inicial