lunes, 21 de diciembre de 2009

Formulas con rangos dinámicos usando la función INDIRECTO

Muchas veces necesitamos realizar una misma operación pero sobre rangos de celdas diferentes y no deseamos estar cambiando la fórmula para ajustar las celdas sobre las cuales se realiza la operación.

Para lograr esto podemos utilizar la función INDIRECTO (INDIRECT para la versión en Inglés) que toma una valor de texto, como el ingresado por un usuario en una celda y lo devuelve como una referencia a una celda, tal cual como se utiliza cuando es ingresado en una formula.

La función INDIRECTO toma dos parámetros, el primero es el nombre de la celda o nombre de rango al cual se desea hacer referencia. El segundo indica el formato de la referencia, si es VERDADERO (valor por defecto) la referencia será del tipo "A1" si es FALSO será "R1C1".

Si en la celda B2 ingresamos =INDIRECTO("C3") o =INDIRECTO("R3C3",Falso) obtendremos el valor de la celda como vemos en la siguiente pantalla.


Una alternativa es definir nombres de rangos y luego cambiar las celdas a las que se refiere ese rango, esta alternativa brinda la ventaja de hacer este proceso directamente en la planilla de Excel sin tener que entrar a definir nuevos nombres.


Veamos un ejemplo.


En cada hoja tenemos un listado de ventas que representan las ventas de todo el mes. Todas las hojas tienen el mismo formato pero pueden variar en cantidad de líneas.


En cada hoja definimos nombres para los rangos para facilitar las formulas y que nos permita trabajar con distinta cantidad de datos por mes. La rango en columna B tiene el nombre Clientes.

Es importante definir los nombres de los rangos locales a la hoja, de esta forma podemos utilizar el mismo nombre de rango en varias hojas. Realizamos este proceso para todas las hojas con datos.


De manera similar definimos el nombre Productos para el rango en la columna C y Cantidades para el rango en la columna D.

En la hoja resumen tomamos información de las hojas presentando un estado de situación. El usuario puede seleccionar el mes a evaluar con solo seleccionar de la lista desplegable ubicada en la celda B2


Por ejemplo si deseamos calcular el total de productos vendidos usamos la fórmula: =SUMA(INDIRECTO(B2&"!Cantidades")), al cambiar el valor de B2 la función INDIRECTO hará referencia a distintos rangos.

Para acceder al archivo del ejemplo pueden hacer clic aquí.