Sumar caracteres según su posición en el abecedario

En este ejercicio vamos a resolver un problema que, probablemente, no tiene una utilidad práctica inmediata pero que va a poner a prueba nuestro ingenio.

Asignaremos a cada letra un valor numérico que coincida con su posición en el abecedario, independientemente de que sea mayúscula, minúscula, esté acentuada  o lleve diéresis. Así,  "a", "á", "A" y "Á" tendrán valor 1; "b" y "B" tendrán valor 2; "u", "ú", "ü", "U", "Ú" y "Ü" tendrán valor 22; y así sucesivamente.

El problema va a consistir en escribir el nombre de una persona (o un texto cualquiera) y calcular, con arreglo al criterio anterior, la suma de las cifras asignadas a sus caracteres. El texto irá en la celda C2 y la suma en C3. Las celdas de las filas 6 a 26 las usaremos para hacer cálculos intermedios. Al final, crearemos una fórmula compleja y podremos eliminar los valores de las celdas auxiliares.

Resolveremos el ejercicio en 7 pasos:

1. Quitar los espacios en blanco y poner el texto en mayúscula (MARTÍNCORTÉS).
2. Contar los caracteres que han quedado (12).
3. Generar una lista de números del 1 al total de caracteres (del 1 al 12).
4. Extraer uno a uno todos los caracteres creando una matriz.
5. Eliminar la tilde de las vocales que lo lleven.
6. Asignar a cada letra un valor numérico según su posición en el abecedario.
7. Sumar los números.

Para quitar los espacios usaremos la función SUSTITUIR, y para poner el texto en mayúscula, la función MAYUSC.

En C6:
=MAYUSC(SUSTITUIR($C$2;" ";))    [Resultado: MARTÍNCORTÉS]

El número de caracteres del texto se obtiene mediante la función LARGO.

En B9:
=LARGO(C6)     [Resultado: 12]

La lista de números del 1 al 12 la generamos mediante las funciones FILA y DESREF.

Seleccionamos C9:C26 y escribimos:
=FILA(DESREF($A$1;;;$B$9))     [Terminar con Ctrl + Mayús + Intro]

La matriz de caracteres individuales del texto se obtiene con la función EXTRAE.

Seleccionamos D9:D26 y escribimos:
=EXTRAE($C$6;$C$9:$C$26;1)     [Terminar con Ctrl + Mayús + Intro]

La eliminación de las tildes de las vocales acentuadas se realiza con las funciones CODIGO, ELEGIR y COINCIDIR.

Seleccionamos E9:E26 y escribimos:
=SI(CODIGO($D$9:$D$26)>90;ELEGIR(COINCIDIR($D$9:$D$26;{"Á";"É";"Í";"Ó";"Ú";"Ü"});"A";"E";"I";"O";"U";"U");$D$9:$D$26)     [Terminar con Ctrl + Mayús + Intro]

Usamos la función HALLAR para obtener la posición de cada letra en el abecedario.

Seleccionamos F9:F26 y escribimos:
=HALLAR($E$9:$E$26;"ABCDEFGHIJKLMNÑOPQRSTUVWXYZ";1)     [Terminar con Ctrl + Mayús + Intro]

Sólo falta sumar los números.

En G9:
=SUMA(F9:F26)     [Resultado: #N/A]

Parece que no hemos obtenido el resultado correcto, pero cuando compongamos la fórmula veremos que todo se arregla. La razón es que en la suma hemos incluido las filas 21 a 26 que contienen errores. En la fórmula definitiva, Excel eliminará estas filas, como se comprobará más adelante.

Compondremos la fórmula paso a paso. Para ello, comenzamos poniendo en C3 la fórmula anterior pero sustituyendo F9:F26 por la fórmula de la matriz incluida en ese rango. El resultado será:

En C3:
=SUMA(HALLAR($E$9:$E$26;"ABCDEFGHIJKLMNÑOPQRSTUVWXYZ";1))     [Terminar con Ctrl + Mayús + Intro]

El error continúa.

En la fórmula que nos ha quedado, sustituimos $E$9:$E$26 por la fórmula de esa columna. Queda otra fórmula más larga:

En C3:

=SUMA(HALLAR(SI(CODIGO($D$9:$D$26)>90;ELEGIR(COINCIDIR($D$9:$D$26;{"Á";"É";"Í";"Ó";"Ú";"Ü"});"A";"E";"I";"O";"U";"U");$D$9:$D$26);"ABCDEFGHIJKLMNÑOPQRSTUVWXYZ";1))     [Terminar con Ctrl + Mayús + Intro]

Persiste el error. No desesperemos. Tenemos que seguir sustituyendo valores. En este momento, el valor a sustituir, tres veces, es $D$9:$D$26.

En C3:

=SUMA(HALLAR(SI(CODIGO(EXTRAE($C$6;$C$9:$C$26;1))>90;ELEGIR(COINCIDIR(EXTRAE($C$6;$C$9:$C$26;1);{"Á";"É";"Í";"Ó";"Ú";"Ü"});"A";"E";"I";"O";"U";"U");EXTRAE($C$6;$C$9:$C$26;1));"ABCDEFGHIJKLMNÑOPQRSTUVWXYZ";1))     [Terminar con Ctrl + Mayús + Intro]

Aún no hemos obtenido el valor correcto. $C$9:$C$26 aparece otras tres veces. Repetimos el proceso.

=SUMA(HALLAR(SI(CODIGO(EXTRAE($C$6;FILA(DESREF($A$1;;;$B$9));1))>90;ELEGIR(COINCIDIR(EXTRAE($C$6;FILA(DESREF($A$1;;;$B$9));1);{"Á";"É";"Í";"Ó";"Ú";"Ü"});"A";"E";"I";"O";"U";"U");EXTRAE($C$6;FILA(DESREF($A$1;;;$B$9));1));"ABCDEFGHIJKLMNÑOPQRSTUVWXYZ";1))     [Terminar con Ctrl + Mayús + Intro]

¡Por fin! Resultado correcto. ¿Por qué ahora Excel nos devuelve un resultado correcto y antes no? La razón es que las fórmulas anteriores incluían las filas 21 a 26, que contienen errores. Sin embargo, ahora, hemos introducido en la fórmula la función DESREF, que devuelve una matriz de únicamente 12 elementos, que no incluyen las filas mencionadas.

Aún no hemos acabado. Debemos quitar $B$9.

En C3:
=SUMA(HALLAR(SI(CODIGO(EXTRAE($C$6;FILA(DESREF($A$1;;;LARGO(C6)));1))>90;ELEGIR(COINCIDIR(EXTRAE($C$6;FILA(DESREF($A$1;;;LARGO(C6)));1);{"Á";"É";"Í";"Ó";"Ú";"Ü"});"A";"E";"I";"O";"U";"U");EXTRAE($C$6;FILA(DESREF($A$1;;;LARGO(C6)));1));"ABCDEFGHIJKLMNÑOPQRSTUVWXYZ";1))     [Terminar con Ctrl + Mayús + Intro]

Finalmente, quitamos $C$6.

En C3:
=SUMA(HALLAR(SI(CODIGO(EXTRAE(MAYUSC(SUSTITUIR($C$2;" ";));FILA(DESREF($A$1;;;LARGO(MAYUSC(SUSTITUIR($C$2;" ";)))));1))>90;ELEGIR(COINCIDIR(EXTRAE(MAYUSC(SUSTITUIR($C$2;" ";));FILA(DESREF($A$1;;;LARGO(MAYUSC(SUSTITUIR($C$2;" ";)))));1);{"Á";"É";"Í";"Ó";"Ú";"Ü"});"A";"E";"I";"O";"U";"U");EXTRAE(MAYUSC(SUSTITUIR($C$2;" ";));FILA(DESREF($A$1;;;LARGO(MAYUSC(SUSTITUIR($C$2;" ";)))));1));"ABCDEFGHIJKLMNÑOPQRSTUVWXYZ";1))     [Terminar con Ctrl + Mayús + Intro]

Podemos borrar las celdas auxiliares; ya no las necesitamos.

Descargar archivo (S58-Suma de caracteres.xlsx)