Ajuste automático de fórmulas en Excel

Excel ajusta las referencias dentro de una fórmula automáticamente. Supongamos esta operación en una hoja de Excel



La celda B7 calcula el total de unidades vendidas con =SUMA(B2:B6). Como ya habrán notado, hemos olvidado de incluir el Producto 4.
Como queremos guardar el orden en nuestra lista de productos incluimos el Producto 4 entre la línea 4 y la línea 5



Como pueden ver, Excel ha corregido automáticamente el rango en la función SUMA.

Agreguemos una nueva columna a nuestra tabla, Ingresos. En la celda A12 hemos puesto una lista desplegable con los productos y en la celda B12 una fórmula con la función BUSCARV que nos muestra los ingresos del producto elegido



Ahora agreguemos la columna Precio entre las columnas "Unidades Vendidas" e "Ingresos"



Como pueden ver Excel ha ajustado el rango de la fórmula de A2:C7 a A2:D7 para incluir la nueva columna, pero no ha modificado el tercer argumento de la función BUSCARV que sigue siendo 3. Es decir, BUSCARV sigue buscando en la tercer columna a la derecha de la columna A, que ahora es Precio y no Ingresos.
No se trata de un "bug" de Excel, ya que el argumento es un número de referencia estático y no un rango.
Veamos algunas técnicas para sobreponernos a este problema. Si nuestra función BUSCARV siempre se refiere a la última columna en el rango, podemos crear una referencia dinámica con la función COLUMNAS.

Empecemos por incluir nuestra tabla de datos en un nombre, tabla_productos



Ahora modificamos nuestra fórmula de la siguiente manera

=BUSCARV(A12,$A$2:$D$7,COLUMNAS(tabla_productos),0)

El resultado es el siguiente



Como ven, nuestra fórmula se ha ajustado automáticamente. Lo mismo sucederá si agregamos una nueva columna dentro del rango



La función COLUMNAS cuenta el número de columnas en una matriz o rango al cual se refiere.
Si queremos que la función se refiera dinámicamente a la anteúltima columna, usaremos la expresión COLUMNAS(referencia)-1.

Technorati Tags: MS Excel