Referencias extrañas. Mantener numeración correlativa.
Ya hemos hablado sobre los estilos de referencia A1 y F1C1, sobre referencias relativas y absolutas, referencias a rangos, a otras hojas y a otros libros, así como referencias tridimensionales.
Sin embargo, una pregunta en el foro me hizo pensar en otra manera de hacer referencia a celdas, en este caso mediante fórmulas de referencia. La pregunta en cuestión era esta:
Tengo en la COLUMNA A, los Nos. consecutivos y en la COLUMNA B los nombres de las personas (Lenín, Antonio, Carlos, etc.). El ejercicio es numerar consecutivamente y de manera automática la COLUMNA A una vez que haya ingresado el nombre en la columna B, de tal forma que si borro un nombre se actualice la consecutividad.
A los efectos de este artículo, entenderemos que “borrar el nombre” significa eliminar la fila en la que está ese nombre.
Respuesta 1. Obviedad que no resuelve el problema.
Lo primero que se nos ocurre es crear la tabla tal y como nos lo dice nuestro amigo Eduardo. En la columna A ponemos números, arrastramos hacia abajo y se rellenan los números consecutivamente. En la columna B introducimos los nombres y en la columna C utilizamos una fórmula que une ambos datos. La columna C es para demostrar que se puede operar con lo que tenemos en las otras dos columnas.
El problema que tiene hacer esto es que al eliminar una fila, la columna A no se renumera. Si eliminamos la fila 3, por ejemplo, lo que nos quedará es una lista de números que pasa del 2 al 4.
Respuesta 2. Elaboramos la obviedad, pero sigue sin funcionar.
Si en A3 introducimos la fórmula siguiente:
=A2 + 1
Ahora estas referencias se nos van a renumerar automáticamente en algunos casos. En otros, no. Sí funcionaría si añadimos una fila y arrastramos fórmula, o si eliminamos una fila y arrastramos fórmula. Pero esto requiere de dos pasos.
Incluso si hemos convertido el rango en una tabla, al eliminar filas genera el mismo error #REF!. En este caso, al insertar una nueva fila rellenaría automáticamente las fórmulas en las columnas A y C. Pero no modifica como debiera la fórmula de la celda siguiente a la inserción. Así que tendríamos el número 3 duplicado hasta que arrastremos la fórmula de nuevo.
Por lo tanto, vemos que nos vamos acercando, pero sigue sin ser una solución correcta.
Respuesta 3. Utilizando DESREF.
DESREF es una de esas funciones mágicas de Excel. No se suele conocer hasta profundizar un poco, y no se suele utilizar hasta profundizar un poco más. Pero es una de las funciones imprescindibles. Si tu función favorita ahora es BUSCARV, dentro de no mucho tiempo cambiarás a DESREF. Si utilizas VBA probablemente ya la hayas utilizado sin saberlo, pues en inglés DESREF se dice OFFSET, y es muy habitual hacer referencias a celdas con algo así como:
for i = 1 to 10
range("A1").offset(i,0)
<-- hacer lo que se quiera con la celda -->
next
Ya hemos hablado de esta función aquí, por lo que no voy a extenderme. Simplemente explicaré un poco cómo funciona en nuestro ejemplo. En cada celda de la columna A haré referencia a sí misma y subiré una fila. Así de sencillo. La función quedará así:
(En la celda A3) =DESREF(A3;-1;0)+1
De esta manera, la numeración se actualiza automáticamente al eliminar una fila. En el caso de insertar una fila, habrá que arrastrar la fórmula. Esto se hará automáticamente si convertimos los datos en una tabla. Como en la primera fila tenemos un 1 y en el resto de filas tenemos la fórmula, Excel nos avisará de que la primera fila tiene un error de “fórmula incoherente”. Normalmente no le haremos caso. No obstante, en ocasiones Excel nos pregunta si queremos extender la fórmula a toda la columna. Si respondemos que sí, nos dará error en la primera fila. Haremos una pequeña comprobación y así ponemos la misma fórmula en toda la columna:
(En la celda A2) =SI(fila()=2 ; 1 ; DESREF(A3;-1;0)+1)
De esta manera, si estamos en la fila 2, Excel pondrá 1 en la celda, mientras que si estamos en cualquier otra fila, Excel calculará la fórmula que tenemos escrita.
Respuesta 4. Utilizando INDIRECTO y DIRECCION.
Otra manera de resolver el mismo problema es combinando INDIRECTO y DIRECCION.
DIRECCION es una función que nos pide una fila y columna y nos devuelve una referencia a celda. INDIRECTO es una función que nos pide una referencia a celda y nos devuelve su valor. Si combinamos ambas funciones podemos obtener el valor de una celda sabiendo su fila y columna, utilizando el resultado de una como parámetro de la otra. Como nosotros queremos conocer el valor de la celda inmediatamente superior, podemos saber su fila y columna porque son una fila menos que la celda donde introducimos la fórmula y la misma columna. Para conocer fila y columna de nuestra celda actual, utilizaremos las funciones FILA y COLUMNA, que sirven precisamente para eso.
En cualquier celda de la columna A (vemos que la fórmula es exactamente igual en todas las celdas) introduciremos la siguiente fórmula:
=INDIRECTO( DIRECCION( FILA() - 1 ; COLUMNA() ) ) + 1
Como en el caso de DESREF, esto funciona perfectamente al eliminar una fila. En el caso de insertar una nueva habrá que copiar las fórmulas en las celdas que las lleven. Si hemos convertido el rango en una tabla, al insertar una nueva fila se copiará automáticamente la fórmula y en nuestro caso solo tendríamos que introducir el nombre nuevo.
También, como en el caso anterior, para eliminar el error de la primera columna, hacemos la misma comprobación:
=SI( FILA() = 2 ; 1 ; INDIRECTO( DIRECCION( FILA() - 1 ; COLUMNA() ) ) + 1 )
Conclusión
Con un poco de habilidad podemos eliminar los típicos errores de relleno de tablas que se generan al eliminar o al añadir entradas por arrastras referencias incorrectas. Si quieres profundizar un poco más en los temas tratados, puedes mirar las siguientes entradas: