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