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í.

Definición de nombres en Excel

Cuando hacemos referencia a una celda usualmente utilizamos la nomenclatura A1, A2, A3, etc donde la primera letra corresponde a la columna y el número corresponde a la fila. Adicionalmente Excel posee la función para otorgarle nombres definidos por el usuario a una celda o a un rango de celdas.

Esta herramienta se encuentra en la ficha Formulas, en la opción definir nombres dentro de la sección Nombres definidos.


Una vez seleccionada esa opción aparecerá un cuadro de dialogo para definir el nombre, donde ingresamos el nombre del rango y la celda o celdas a las que hace referencia.



A continuación veremos algunos ejemplos sencillos sobre como definir fórmulas utilizando nombres definidos por el usuario y cuales son sus ventajas.

Si tenemos una lista de precios y deseamos devolver el precio tomando como parámetro el código podemos definir el rango A1:B5 con el nombre Datos y utilizar la siguiente formula para la búsqueda =BUSCARV("Uva",Datos,2,Falso).


Si tenemos dicha fórmula en varios lugares en nuestra planilla y tenemos que cambiar el rango Datos o agrandarlo solo debemos hacerlo en un solo lugar y esto afectara a todas las formulas facilitando la administración y mantenimiento de dicha hoja de cálculos.

Otra aplicación es cuando tenemos un valor fijo que es aplicado en varias formulas en la planilla y para facilitar le lectura de la formula definimos un nombre para ese rango, si tenemos un nombre Descuento que tiene el valor 0.15 entonces podemos definir una formula =A1 * Descuento para obtener el resultado.


Cuando deseamos cambiar el valor del descuento solo modificamos el valor asignado al nombre y esto actualiza todas las fórmulas sin tener que hacerlo una por una.

Búsquedas con múltiples parámetros

Nuevamente abordamos el tema de las búsquedas de datos en listas en Excel y si hablamos de buscar datos casi siempre utilizamos la función BUSCARV (o su hermana menos conocida BUSCARH) pero esta función tiene algunas limitaciones. En otras notas anteriores he explicado alternativas utilizando la formulas INDICE Y COINCIDIR

En esta nota explicaré como realizar una búsqueda utilizando más de un parámetro o celda para realizar la búsqueda.

La forma más sencilla de lograr esto es realizar una columna auxiliar donde concatenamos los datos de las dos columnas que forman nuestro criterio de búsqueda. De esta forma realizamos la búsqueda normal con un solo parámetro pero que contiene dos criterios o valores en su contenido

Si no deseamos o no podemos crear esta columna podemos recurrir nuevamente a INDICE Y COINCIDIR en una función matricial o array.

Para lograr el resultado, definimos cada condición o criterio de búsqueda que nos devolverá una matriz con VERDADERO O FALSO, ya que estamos utilizando una función matricial. Este array tendrá tantos elementos como filas tenga la lista de datos y dependiendo si esa fila cumple el criterio tendrá el valor VERDADERO y si no tendrá FALSO.

Luego para juntar todos los criterios multiplicamos las matrices y obtendremos una matriz con un valor verdadero si y solo si todas las matrices tenían un verdadero en esa posición. Aquí es donde realizamos la operación para asegurarnos que se cumplen todos los criterios de búsqueda y aquellas líneas que no cumplen con todos los criterios queda excluidas.

Una vez ubicado la posición devuelta por coincidir utilizamos la función INDICE para devolver el dato ubicado en esa posición.


Veamos un ejemplo


Tenemos una lista de precios como la que se muestra a continuación


Si deseamos ubicar el precio para Naranja y Minorista podemos utilizar la siguiente formula :
{=INDICE(C2:C11,COINCIDIR(1,(A12:A11="Naranja")*(B12:B11="Minorista"),0))}

Para ver como se resuelven las distintas condiciones pueden utilizar la herramienta Evaluar Fórmula de Excel que muestra paso a paso como se procesa todas estas fórmulas.

Recuerden que es una fórmula matricial por lo tanto se debe presionar Ctrl + Shift + Enter.

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

Accediendo a la información de una tabla dinámica con la función IMPORTARDATOSDINAMICOS

Las tablas dinámicas son de gran utilidad en Excel ya que permiten agrupar la información y poder verla de forma resumida. Además ofrece mucha flexibilidad para poder decidir como mostrar la información.

En algunos casos cuando tenemos mucha información, por más que utilicemos una tabla dinámica sigue quedando demasiado grande y en esos casos podemos utilizar IMPORTARDATOSDINAMICOS (o GETPIVOTDATA para la versión en Inglés) para traer algunos datos específicos de una tabla dinámica y presentarlos en una pequeña tabla o de la forma que decidamos.

La función toma 2 parámetros obligatorios que son, el primero el nombre del campo que contiene los datos en la tabla dinámica y el segundo una referencia a una celda de la tabla dinámica. Esto es necesario para saber que tabla dinámica se va a utilizar.

Luego se pueden agregar hasta 126 pares de parámetros donde se indica el nombre de un campo y un valor para filtrar los datos.
El valor tiene que estar visible en la tabla dinámica, si intentamos acceder un dato oculto o ingresamos por error un nombre de un campo incorrecto o valor inexistente la función devuelve el error #REF!. La función siempre devolverá el valor que sea la intersección de una fila y una columna de la tabla dinámica.



Veamos algunos ejemplos

Si tenemos una tabla dinámica como la que mostramos a continuación, podemos utilizar las siguientes fórmulas para buscar información.


Para obtener el total de artículos vendidos usamos la fórmula: =IMPORTARDATOSDINAMICOS("Cantidad";A$3)

Para obtener el importe total vendido a Pedro durante el mes de Enero usamos la fórmula: =IMPORTARDATOSDINAMICOS("Importe";$A$3;"Mes";"Enero";"Cliente";"Pedro")

Para obtener la cantidad de manzanas vendidas usamos la fórmula: =IMPORTARDATOSDINAMICOS("Cantidad";$A$3;"Producto";"Manzana")

La mejor forma de practicar con esta fórmula es presionar = y luego seleccionar un dato en la tabla dinámica y Excel creará la formula necesaria para devolver el elemento seleccionado

También podemos buscar datos en otras celdas y no ingresar los valores fijos en las celdas, por ejemplo si la celda B25 contiene el nombre del producto sobre el que deseo información puedo usar la siguiente fórmula: =IMPORTARDATOSDINAMICOS("Cantidad";$A$3;"Producto";B25). Al cambiar el valor de la celda H1 se modificará el resultado.

Ventajas

Es posible las formulas en cualquier parte de la hoja y moverlas o insertar filas ya que siempre solo hay una referencia a una cela de la tabla dinámica.
Al tratarse de una formula normal de Excel puedo formatearla como se desea y sin las restricciones de formato que tienen las tablas dinámica.

Desventajas

El dato tiene que estar visible en la tabla dinámica para poder ser utilizado con esta fórmula. Esto puede no ser tan importante si nuestra tabla está siendo creada con una rango de Excel como base pero si el origen de los datos es un cubo OLAP genera algunas limitaciones.

No se pueden cambiar los filtros de página de la tabla dinámica con una formula.

Para acceder el archivo de ejemplo haga clic aqui.

Buscar valores en una tabla con dos parámetros

Usualmente en Excel tenemos que buscar un dato particular que puede estar en una lista y para ello Excel tiene varias funciones de búsqueda. En el artículo de hoy veremos un ejemplo que combina dos funciones de búsqueda BUSCARV y COINCIDIR para poder ubicar un valor determinado en una tabla de datos.

Para buscar un valor que se encuentra en una celda determinada de la tabla vamos a necesitar realizar dos búsquedas. Primero debemos localizar la fila donde se encuentra el dato que deseamos buscar y luego la columna. Con estos dos valores identificamos la celda que contiene el dato buscado.

Para ubicar la fila usamos la función BUSCARV que busca un valor en la primera columna de un rango y devuelve el valor ubicado en la misma fila pero a una determinada cantidad de columnas hacia la derecha. Esta función toma 4 valores como parámetros, el primero es el valor buscado, el segundo el rango de celdas donde buscará dicho valor y que contiene también la columna que desea ser devuelta. Luego se envía el número de columna que se desea y finalmente el parámetro FALSE para indicar una coincidencia exacta.

En el tercer parámetro de BUSCARV en lugar de proporcionar directamente el número de columna utilizamos la función COINCIDIR y con ella buscamos el titulo de la columna que deseamos y como resultado obtendremos el número de columna dentro del rango.



Veamos un ejemplo


Si tenemos una tabla como la que se muestra a continuación donde se tienen Productos en las filas y los nombres de los vendedores en las columnas.


Podemos utilizar la siguiente función para ubicar cuantas Manzanas vendió Juan : =BUSCARV("Manzana";A2:E10;COINCIDIR("Juan";A2:E2;0);FALSE)

La parte de la fórmula =COINCIDIR("Juan";A2;E2;0) devolverá el número de columna que en este caso es 4, luego este valor es utilizado por BUSCARV para devolver la celda deseada.

Para hacer esta fórmula más flexible pondremos el nombre del producto y del vendedor en dos celdas separadas y luego haremos referencia a ellas en la formula.

Si la celda H5 tiene el producto y H6 el vendedor nuestra formula sería =BUSCARV(H5;A2:E10;COINCIDIR(H6;A2:E2;0);FALSE)

Cambiando los valores obtenemos distintos resultados sin tener que modificar la formula.

Para acceder al archivo del ejemplo haga clic aquí.