Fórmula para calcular la letra del NIF

En España se utiliza el NIF. Este número, que es identificativo a efectos fiscales, se calcula operando con el número del DNI de cada persona de la forma siguiente:

  • El número del DNI se divide entre 23
  • Lo importante de la anterior operación es el resto obtenido. Este resto estará entre 0 y 22, como es lógico.
  • Tomamos el resto y sumamos uno.
  • Con el número obtenido, miramos la letra que tiene dicha posición en la siguiente cadena de caracteres: “TRWAGMYFPDXBNJZSQVHLCKE”. Por ejemplo, si de las operaciones anteriores obtuvimos un 6, corresponde la letra M.

Para poner esta fórmula en Excel tenemos las siguientes funciones:

  • Residuo(dividendo;divisor) : nos devuelve precisamente el resto de dividir un número (dividendo) entre otro (divisor). Por ejemplo si ponemos =residuo(10;3) el resultado es uno, ya que al dividir 10 entre 3, el resto es 1. Si ponemos =residuo(25;5), obtenemos 0, ya que 25 es múltiplo de 5 y al hacer la división el resto es cero.
  • Extrae(Texto;inicio;caracteres): nos devuelve el número de caracteres especificado en el tercer argumento, a partir de inicio, del texto indicado como primer argumento. Por ejemplo, si ponemos =extrae(“Este es un ejemplo de Excel”;9;2), nos devuelve “un” porque si contamos las letras contenidas en el texto “Este…” vemos que la novena posición (se incluyen espacios en blanco) corresponde a la u, y si tomamos 2 letras (o caracteres) tenemos “un”.

Hacemos ahora la fórmula en Excel (suponemos que el número del DNI está en A1):
=EXTRAE("TRWAGMYFPDXBNJZSQVHLCKE";RESIDUO(A1;23)+1;1)
Que leeríamos de la forma siguiente:

De la cadena “TRW…” extrae un carácter (el tercer argumento es un uno), comenzando en el número que obtenemos como resto de dividir lo que hay en A1 por 23, y sumarle 1. (Residuo(A1;23)+1.

Hacer que las filas o columnas de nuestra hoja de cálculo aparezcan alternativamente coloreadas.

Es frecuente que, con el fin de darle mejor legibilidad a nuestra hoja, recurramos a colorear de manera diferente las filas (o columnas). Por lo general se suelen colorear las filas alternativamente de gris claro. La hoja resultante tiene un acabado con un aspecto mucho más profesional y es mucho más sencillo leer los datos que contiene.

La solución parece fácil, nos situamos con el cursor en cada una de las filas/columnas que deseamos cambiar de color y vamos aplicándoles los formatos. Incluso existe la posibilidad de agilizar nuestra tarea si seleccionamos las filas/columnas pulsando la tecla ctrl simultáneamente y después les aplicamos el formato de una sola vez. Esta solución es buena si la cantidad de filas/columnas a colorear no es muy grande. ¿Pero y si tu hoja de cálculo es muy extensa?. Incluso podría ser que te olvidaras de alguna fila/columna. En cualquier caso se trata de una tarea bastante pesada.

Antes de continuar con nuestro tema, haremos un paréntesis para ver un par de funciones y lo que hacen, luego explicaremos en qué nos pueden ayudar para lo que perseguimos.

La primera de ellas es RESIDUO. Esta función obtiene el resto de la división entera de dos números que hemos de poner como argumentos. El primer número es el dividendo y el segundo el divisor. Así por ejemplo si ponemos la fórmula =RESIDUO(32;5) el resultado que obtenemos es 2, porque si dividimos 32 entre 5, el resto es 2. Otro ejemplo: si ponemos =RESIDUO(20;2) nos dirá que es cero, ya que al dividir 20 entre 2 el resto es cero, pues 20 es un múltiplo de 2. Podemos seguir poniendo ejemplos y llegamos a la conclusión de que cuando un número es divisible por otro (es decir que el primer número  es un múltiplo del segundo), entonces el resto es cero. Cuando no es divisible por el segundo número, obtenemos otra cosa que no es cero.

Las funciones FILA() y COLUMNA() sin ningún argumento entre los paréntesis, nos devuelven respectivamente un número que corresponde a la fila y la columna actual.

Entonces, si unimos las funciones anteriores y ponemos la fórmula =RESIDUO(FILA();2) ó =RESIDUO(COLUMNA();2) se nos devolverá un cero para las filas/columnas pares y un 1 para las filas/columnas impares. Y como sabemos, en informática el cero tiene un valor falso y el uno un valor cierto.

Volviendo a la cuestión principal que era el objetivo de nuestra entrada: colorear alternativamente las filas o columnas de una hoja, la formula anterior puede ser usada como formato condicional,veamos ahora cómo conseguirlo. Señalaremos previamente el rango al que queremos aplicar el formato. Vamos a la opción de formato condicional que se encuentra dentro de Inicio. Elegimos establecer Nueva regla… Buscamos entre las distintas posibilidades, eligiendo la última de ellas que se llama Utilice una fórmula que determine las celdas para aplicar el formato (efectivamente, queremos una fórmula para aplicar el formato), y nos aparecerá la ventana siguiente:

Entrada de un formato condicional mediante una fórmula
Aplicación de un formato condicional

En el apartado Dar formato a los valores donde esta fórmula sea verdadera: escribiremos la fórmula que acabamos de determinar, es decir: =RESIDUO(FILA();2). Y dando en el botón Formato determinaremos el formato elegido, para nuestro ejemplo debemos poner el Relleno en el color deseado.

Si damos a aceptar, efectivamente se nos colorean las filas o columnas (según la formula que hayamos puesto) alternativamente. Sin embargo, la fórmula tal y como está, colorea las filas/columnas impares. Si específicamente necesitamos colorear las filas/columnas pares y dejar en blanco las impares, podemos aplicar otra función que es NO(). La función NO() niega todo el argumento que tenga dentro. Entonces sería: =NO(RESIDUO(FILA();2)).

Como última observación diremos que el formato se aplica a todas las filas o columnas independientemente de que tengan o no contenido. Si quisiéramos aplicar un formato sólo para aquellos casos en que haya algo escrito en alguna celda y de esta manera fuera dinámico, es decir que al escribir nuevas entradas en la hoja se cambiara, tenemos que añadir algo más a nuestra fórmula.

Pero eso lo explicaremos en otro momento…