Rangos dinámicos con la función INDIRECTO de Excel.

sábado, enero 02, 2010

Excel permite construir rangos dinámicos, tema que ya hemos tratado en diversas oportunidades en este blog. Rangos dinámicos son aquellos cuya referencia (dirección) se expande o contrae con los cambios en el número de miembros del rango. Estos rangos se definen con fórmulas, por lo general con la función DESREF. También hemos visto que podemos referirnos dinámicamente a un rango usando su nombre como argumento de la función INDIRECTO, por ejemplo cuando creamos listas desplegables dependientes. Pero esto genera un problema con los rangos dinámicos: INDIRECTO no acepta fórmulas como argumentos, sólo texto.
En esta nota veremos un rodeo sencillo a este problema, sin usar macros o funciones definidas por el usuario.




Por ejemplo, si queremos construir una lista desplegable que muestre las sucursales de red



Para crear la lista desplegable usamos validación de datos, con la opción Lista donde usamos como referencia el nombre Sucursales que contiene el rango D2:D13




La referencia al rango en el nombre es absoluta, por lo que si agregamos sucursales a continuación del último valor de la lista, deberemos editar el nombre y cambar la referencia.
Para lograr que la lista se actualice automáticamente al agregar nuevas sucursales tenemos que definir el rango como rango dinámico. Para esto usamos la función DESREF
=DESREF(Hoja2!$D$2,0,0,CONTARA(Hoja2!$D:$D),1)
Aquí pueden leer una explicación detallada sobre la función DESREF (OFFSET en la versión inglesa).
Ahora vamos a agrupar las sucursales por zonas


La idea es elegir una zona en B3 y que la lista desplegable en B4 muestre sólo las sucursales correspondientes. Para esto usaremos validación de datos con la opción lista y en Origen pondremos INDIRECTO(B3). Esto funciona bien si usamos referencias absolutas. Por ejemplo, definimos el nombre “absOeste” como $H$3:$H$5



Nótese que la fórmula en Origen es =INDIRECTO(“abs”&B3), es decir concatenamos el nombre de la zona en B3 con “abs” para obtener el nombre “absOeste” que le hemos puesto al rango.
Si agregamos la sucursal 13 en H6, ésta no queda incluida en el rango del nombre. Podemos usar la fórmula “tradicional” con DESREF para crear el nombre “dinOeste”



Al tratar de crear la lista desplegable con validación de datos recibimos esta advertencia


Como explicamos más arriba, INDIRECTO no puede evaluar fórmulas, sólo texto. En lugar de DESREF o fórmulas definidas por el usuario (macros) como sugieren algunos sitios y foros, podemos usar la funcionalidad Tablas en (Listas Excel Clásico).

Veamos el proceso, primero en Excel 2007 y luego en Excel Clásico.
En B2 creamos una lista desplegable con validación de datos poniendo los nombres de las zonas directamente en la ventanilla Origen



Para crear el rango dinámico Norte seleccionamos las celdas E2:E5, activamos la pestaña Insertar y pulsamos Tabla. Marcamos la opción “La tabla tiene encabezados” y pulsamos Aceptar


Seguidamente activamos Herramientas de Tablas y en Nombre de la tabla cambiamos el nombre por defecto por Norte


Repetimos el mismo proceso para las restantes tres zonas. Ahora en B4 ponemos una lista desplegable con Validación de Datos-Lista y la fórmula =INDIRECTO($B$3)


Las tablas se expanden automáticamente, por lo que al agregar la sucursal 13 en la zona Oeste, ésta aparecerá en la lista desplegable.
En Excel Clásico (versiones 97-2003) usamos la misma técnica pero con algunas diferencias.
En lugar de Tablas, la funcionalidad en Excel Clásico es Listas. Para convertir un rango en una lista usamos el menú Datos-Lista-Crear Listas.
En Excel Clásico no tenemos la posibilidad de darle un nombre a la lista, por lo que usaremos el menú Insertar-Nombre-Definir



Seleccionamos el rango F4:F6 y creamos el nombre Norte. De la misma manera creamos las listas y los nombres del resto de las zonas. Al agregar nuevas sucursales en las zonas, la lista se expande automáticamente





El archivo del ejemplo se puede descargar aquí.



Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

37 comments:

Antonio8688,  03 enero, 2010 20:55  

Julio, como siempre, excelente explicación. Feliz Año Nuevo.

Guillain Barré 03 enero, 2010 20:57  

Gran aporte. Se ve que no descansas. TE deseo un Feliz Año.

Jorge L. Dunkelman 03 enero, 2010 20:58  

Gracias Anotnio 8688, pero por favor anotá: el autor de las notas es Jorge, no Julio. Ya lo aclaré en tu anterior comentario.

Mateo,  04 enero, 2010 13:55  

Gracias, necesitaba saber cómo hacer ésto.

¿Sería posible poder descargar el archivo empleado como ejemplo?

Gracias de nuevo.

Jorge L. Dunkelman 04 enero, 2010 14:21  

No guardé el archivo. Lo reconstruiré y pondré un enlace para desacargarlo, en breve.

María José,  04 enero, 2010 18:07  

Hola Jorge!
Tengo una consulta sobre esta nota, no sé si lo que quiero se puede hacer, pero si alguien puede ayudarme sos vos.
En la validación de la celda B3 pusiste directamente los textos: Norte, Sur, Este y Oeste. Se podría hacer con los nombres de rangos o nombres tablas en 2007?, porque de esta manera si hubiera algún cambio en los nombres de rangos la validación se ajustaría automáticamente, y si está hecha con textos habría que editar la validación de la celda B2 y de todas las celdas donde se haya utilizado, que en mi caso, sería en varios lugares distintos en distintas hojas del documento.
Te agradezco mucho por hacer este blog, que es realmente muy útil, y por tu ayuda.
Un saludo

Jorge L. Dunkelman 04 enero, 2010 18:48  

María José,
si, se puede hacer. Tanto con Excel 2007 como con Excel clásico.

María José,  04 enero, 2010 20:55  

Y cómo se hace? Me puedes ayudar?

Jorge L. Dunkelman 04 enero, 2010 21:01  

Así como hemos definido los nombres para las sucursales, defines una lista/tabla para las zonas.

María José,  04 enero, 2010 21:19  

Esa opción la sé hacer, pero me obliga a tener otra tabla auxiliar.
Mi duda es si hay alguna manera de escribir directamente en el campo Origen de la validación, a través de alguna función, los nombres de rango.
Muchas gracias por tu ayuda.

Jorge L. Dunkelman 04 enero, 2010 21:24  

No, o pones la lista de valores en Origen o una referencia a un rango que contiene los valores. Si se trata de un rango dinámico, la única opción es una referencia al rango. No hay una función en Excel que pueda crear valores, fuera de ALEATORIO, por supuesto.

Anónimo,  04 enero, 2010 22:22  

Alberto,
Hola Jorge y feliz año ante todo; una pregunta: para qué vale exactamente lo de "Lista" (la que aparece el asterisco azul al final de la tabla) y qué diferencia hay entre eso y por ejemplo autofiltro (Datos->Filtro->Autofiltro).

Saludos.

María José,  04 enero, 2010 22:25  

Me lo imaginaba, pero tenía la esperanza de que hubiera algún truquito con alguna función.
Muchas gracias y hasta la próxima!!!

Jorge L. Dunkelman 05 enero, 2010 02:11  

Alberto,
las listas (en excel 2003) o tablas (Excel 2007) es una forma de organizar los datos a la que Excel da ciertas funcionalidades. Por ejemplo, que el rango de la tabla se expande automáticamente a medida que agregamos (o se contrae si quitamos) datos. Otra característica es que si en una columna de la tabla hay una fórmula, al agregar una fila la fórmula es copiada automáticamente en la celda correspondiente. Es tal vez una de las funcionalidades más subestimadas en Excel. En breve estaré publicando una nota sobre el tema.
Autofiltro es una funcionalidad que nos permite ocultar filas enteras basándonos en algún criterio de alguna o algunas de las celdas en una columna.

Anónimo,  05 enero, 2010 12:03  

Hola:
Hace ya tiempo que intento hacerme un libro de contabilidad para casa y esta información me ha venido muy bien, pues me permite incluir cun concepto global (Banco, p.e.) y luego, en función de ese concepto, que se abra una segunda lista específica.
Sin embargo mi problema surge cuando la función INDIRECTO ha de evaluar un texto con espacios por medio (Gastos varios, p.e.). En este caso no encuentro el modo de que se abra esta segunda tabla. Bueno, el modo que se me ocurre es introducir el concepto glogal con "_" (Gastos_varios, p.e.) pero estéticamente no me convence.
Quizás mi duda sea una tontería pero no consigo solucionarlo.
Muchas gracias

Jorge L. Dunkelman 05 enero, 2010 12:28  

La solucion es combinar la funcion SUBSTITUIR con la funcion INDIRECTO. Fijate en el ejemplo que muestro en esta nota.

Anónimo,  05 enero, 2010 13:22  

Muchas gracias.
Soy nuevo en esto y no sabía cómo solucionarlo. Mayor rapidez y eficacia...imposible.

Anónimo,  18 enero, 2010 20:56  

Esta solución si que esta muy buena y muy facil de hacer muchas gracias por descubrirnos estas herramientas que tiene excel que no sabia utilizar

Anónimo,  22 enero, 2010 18:11  

Buenas tardes,
llevo todo el día leyendo tu blog. Mi herramienta de trabajo es Excel.
Algunas de las técnicas ya las conocía pero la mayoría no, sobretodo de tu blog de graficos.
Por todo ello, gracias, gracias, gracias y buen trabajo!
Un cordial saludo!

Anónimo,  01 febrero, 2010 04:24  

Que buena explicacion,Jorge tan claro como siempre.

Pavilla79

Anónimo,  23 septiembre, 2010 18:03  

Primero, Gracias por el blog, ha sido de mucha inspiración.

Segundo, si elimino un elemento de la lista me aparecen los espacios en blanco en la validacion. ¿Puede solucionarse este problema?

Gracias

Jorge L. Dunkelman 23 septiembre, 2010 18:18  

Si el rango dinámico está definido en forma correcta no deberían aparecer espacios en blanco. ¿Podrías ser más específico?

Anónimo,  03 marzo, 2011 02:15  

Buenas Noches Jorge,
Tengo una duda con los rangos dinámicos nominados (para las listas desplegables).

Hay alguna técnica para nominar (nombrar) que sea rápida?
Tengo que un cuadro de la distribución de ventas de 4 productos, para 4 paises y cada uno tiene direcciones, gerencias, zonas, distribuidores y puntos de venta. (5 niveles ciales.)

Además hay cambios con relativa frecuencia.

Se puede hacer algo para evitar crear y mantener los 160 columnas con las listas de nombres?
Conoces alguna técnica para que no sea tan pesado?

Muchas gracias y enhorabuena por el blog.

eres un monstruo del excel ;)

Saludos desde España,

José María

Jorge L. Dunkelman 04 marzo, 2011 18:07  

La única forma que conozco de crear varios nombres de un golpe es seleccionar los rangos y activar "Crear desde la selección".
Ahora, esto crea rangos fijos, no dinámicos. Así que cada vez que se produce un cambio, tendría que redifinir el nombre.

Anónimo,  29 junio, 2011 00:54  

que cantidad de nombres de rango se pueden ingresar ya que intento poner una validacion en base a un codigo postal pero serian una cantidad exagerada de tablas! o hay alguna otra manera

Jorge L. Dunkelman 29 junio, 2011 03:36  

¿Podrías explicar un poco más la consulta? No me queda claro si se trata de poner una validación de datos en muchas celdas o crear muchas listas de vallidación.

Yav Mar Kyn 12 julio, 2012 05:53  

... y cómo puedo utilizar un nombre definido en una macro, que no hace referencia a un rango sino que devuelve un valor (ya que es una fórmula). Me explico: creo un nombre "MiValor" que es una fórmula compleja, con funciones anidadas, y quiero en mi macro referirme a élla, algo asi como: If MiValor > x/y Then ...

En la macro no podría decir Range("MiValor") ya que MiValor no es un rango.

Gracias por la ayuda que me puedas ofrecer.

Jorge L. Dunkelman 12 julio, 2012 21:22  

Puedes usar la sentencia

Evaluate("MiValor")

o la forma abreviada

[MiValor]

Eduardo Giraldo 15 agosto, 2012 17:41  

Muchas gracias por el artículo me ha sido de gran ayuda en otras ocasiones, El día de hoy he complicado el asunto un poco, en mi desarrollo tengo una tabla que alimento de una base de datos con la información de los diferentes combos, esto con el fin tener una administración más sencilla de los datos, el problema es que al usar el indirecto (TablaParametricas[Estado]) me genera un error “Error de formula”
Es importante aclarar que la tabla donde tengo los datos se llama TablaParametricas y una de las columnas de la tabla es “estado” el objetivo es que el combo se llene con esta información
Agradezco si me puedes dar una ayuda al respecto.

Jorge L. Dunkelman 19 agosto, 2012 15:57  

Eduardo,

tienes que usar (TablaParametricas[Estado]) sin la función INDIRECTO.

Anónimo,  17 octubre, 2014 19:25  

Hola Jorge! Gracias por el artículo.

Tengo una consulta:

¿Es posible usar la función INDIRECTO() asociado a un rango discontinuo?

Me explico: Si asigno el nombre "hola" al rango continuo A1:A4, al usar INDIRECTO(hola), me estaré refiriendo al rango A1:A4. Así al usar SUMA(hola), me sumará el contenido de las celdas A1, A2, A3 y A4.
Ahora, si en la celda B1 escribo "hola", creo un nuevo nombre en forma manual, por ejemplo "variable" que se refiera a =INDIRECTO($B$1), al hacer la función SUMA(variable), también me arrojará la suma del contenido de las celdas A1, A2, A3 y A4.

¿Pero qué pasa si mi rango es discontínuo?
Por ejemplo, si mi rango definido por A1, A3, A5, A7, A9 y le asigno el nombre "miRango", al usar la función SUMA(miRango), me suma las celdas A1, A3, A5, A7, A9 sin problemas. Pero si creo un nuevo nombre "variable1" =INDIRECTO($B$2), donde en B2 puse "miRango", al ocupar la SUMA(variable1), me arroja el error #¡REF!.

¿La función INDIRECTO no soporta rangos discontínuos?
¿Hay alguna manera de hacerlo?

Muchas gracias!!
Estaré atento a tu respuesta.
Saludos,
Mauricio.

Jorge Dunkelman 18 octubre, 2014 10:34  

Así es, INDIRECTO no soporta rangos discontinuos, es más, los odia, no los aguanta, los detesta :)
Y ahora seriamente, no se pueden usar rangos discontinuos con INDIRECTO (y también con otras muchas funciones de Excel).
Un rodeo posible sería crear un rango continuo creando referencias a las celdas del rango discontinuo, En tu caso podría ser, por ejemplo
C1=A1
C2=A3
C3=A5
C4=A7
C5=A9
y definir el nombre miRango que se refiera al rango C1:C5

Anónimo,  20 octubre, 2014 15:29  

Muchas gracias por tu respuesta Jorge!
Tendré que hacero así entonces.

Saludos,
Mauricio.

Anónimo,  12 noviembre, 2015 18:28  

Gracias por este post. Pude resolver justo lo que necesitaba.

no, 16 noviembre, 2015 02:42  

Estimado, quiero hacer algo parecido a esto pero no exactamente esto, llevo varios dias buscando e intentando con indirecto, con buscarv, con desref, pero no me resuta, y aunque seguiré intentando, acudo a solicitarte consejo.

te explico para ver si por favor me puedes ayudar:

- tengo una hoja con una tabla en la que registro el estado actual de varias caracteristicas a evaluar en diferentes sucursales.

- las caracteristicas a evaluar van hacia abajo en la columna A y las sucursales hacia la derecha en la fila 1.

- el estado de cada caracteristica lo voy ingresando manualmente para cada sucursal.

lo que necesito hacer es lo siguiente:

- en otra hoja tener un consolidado solamente con 2 filas: la de las caracteristicas a evaluar y la de la sucursal que yo elija en un menu desplegable. este menu ya lo hice y funciona bien, lo que no he podido hacer es que cada vez que yo cambie en el menu a una sucursal distinta, las celdas inferiores cambien todas, trayendo el texto que les corresponde en la tabla grande de la hoja 1.

¿se entiende?

espero que si y sino estare muy atento a alguna respuesta tuya.

Saludos y muchas gracias

Jorge Dunkelman 16 noviembre, 2015 06:54  

Me parece que podrías hacerlo con una tabla dinámica, pero tendría que ver tu modelo para entederlo cabalmente. Te sugiero que te pongas en contacto conmigo por mail privado (fijate en el enlace Ayuda, en la parte superior del blog).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP