Empecemos por lo básico: ¿qué es un rango dinámico? El rango A1:A5 en la fórmula =SUMA(A1:A5) es estático.
Si agregamos una fila dentro del rango, Excel lo ajustará automáticamente convirtiendo la fórmula a =SUMA(A1:A6).
Pero si insertamos la nueva fila entre la fila 5 y 6 de nuestro ejemplo, Excel no ajustará el rango de la fórmula
Para superar este problema podemos convertir el rango A1:A5 de la fórmula en dinámico usando rangos nominados ("nombres"). Seleccionamos la última celda del rango (A5 en nuestro ejemplo) y usamos como referencia en un nombre que llamaremos "ultima_celda" (sin acentos y sin espacios en blanco que nos aceptables en nombres) la celda inmediata superior (A4 en nuestro ejemplo)
Hay que prestar atención a que la referencia es relativa (la dirección de la celda no incluye las "anclas" $)
Ahora podemos escribir nuestra fórmula de esta manera:
Otro tipo de situación se presenta cuando queremos que el rango de una lista se extienda o contraiga de acuerdo a la cantidad de elementos (celdas ocupadas) que la componen. Un ejemplo es cuando creamos una lista de valores para una lista desplegable con validación de datos.
La receta clásica es usar la función DESREF como en este ejemplo
DESREF crea un rango empezando en el "ancla" (la celda A1 en nuestro caso) y usando la función CONTARA para determinar cuántas filas están incluidas en él.
Para crear listas desplegables dependientes usamos la función INDIRECTO.
Aquí comienza la cuestión con las funciones volátiles. ¿Qué es una función volátil?
No todas las fórmulas de una hoja son recalculadas cada vez que introducimos un cambio. Excel determina qué celdas serán afectadas por el cambio y cuáles no y sólo recalcula las primeras.
Sin embargo hay algunas funciones que son recalculadas con cualquier cambio en la hoja. Estas son las funciones volátiles. Por eso se suele recomendar como buena práctica evitar, en la medida de lo posible el uso de estas funciones.
Entre las funciones volátiles de Excel se cuentan, justamente, DESREF e INDIRECTO.
Podemos construir rangos dinámicos sin usar DESREF e INDIRECTO con estas técnicas:
1 – si usamos la versión 2003 de Excel o posteriores, la mejor alternativa en mi opinión es usar Listas (Excel 2003, Tablas en Excel 2007/2010). No nos complicamos la vida con fórmulas complejas que a veces pueden producir errores inadvertidos y le dejamos que todo el trabajo lo haga Excel por detrás de las bambalinas.
2 – Usar una combinación de INDICE y CONTARA:
=Hoja1!$A$1:INDICE(Hoja1!$A:$A;CONTARA(Hoja1!$A:$A))
Finalicemos la nota diciendo que la influencia de las funciones volátiles en una hoja de Excel será significativa sólo cuando se haga uso intensivo de ellas, algo similar a lo que ocurre con las fórmulas matriciales. Por eso, y como en muchos otros aspectos de la vida, las cuestiones no son absolutas. Usar funciones volátiles no es ni buena ni mala práctica; todo es una cuestión de cantidad.
Hola,
ResponderBorrarPodías explicar más detenidamente el paso ese de nombrar A5 (última celda del rango), pero que hace referencia a A4 ¿?, la verdad que no me queda nada claro.
Un Saludo
Cuando creamos una referencia relativa (sin los símbolos $, tal como explico en la nota), la dirección de la celda en el nombre cambia en relación a la posición de la celda que lo contiene. Es decir, como creamos el nombre (se refiere a = A4) siendo la celda activa A5, cuando usemos el nombre en A6, la referencia será a la celda A5; si lo usamos en la celda A20, le referencia será a la celda A19.
ResponderBorrarMás o menos lo entendí,...muchas gracias por la celeridad en la respuesta; de todas maneras también probé que si insertas una fila en blanco("...Pero si insertamos la nueva fila entre la fila 5 y 6 de nuestro ejemplo, Excel no ajustará el rango de la fórmula..."), y luego a esa celda en blanco le pones un número, en la celda de abajo te hace la suma total y te agrega la nueva fila al rango, quedando por tanto, SUMA(A1:A6)
ResponderBorrarUn Saludo
Hola Jorge,
ResponderBorrarexisten alternativas para crear listas dependientes sin hacer uso de DESREF o INDIRECTO... sólo se necesita una taza de café bien cargado, un poco de Brahms y, por supuesto, de INDICE:
Listas dependientes sin DESREF o INDIRECTO.
Hola Mauricio
ResponderBorrargracias por compartir. Creo que vale la pena aclarar que la función INDICE tiene dos formas distintas: matricial y referencia. En tus fórmulas se hace uso de la forma de referencia.
Hola Jorge, te quiero agradecer los datos que brindas, son realmente muy buenos e interesantes; hace mucho tiempo que trabajo con Excel y siempre me gusta aprender algo nuevo sobre este programa.
ResponderBorrarTe quiero preguntar sobre la modificación automática de un gráfico. Lo que quiero hacer es que al agregar datos en una columna, estos automaticamente se incorporen al gráfico, evitando tener que editar la serie. ¿Es esto posible?
Desde ya muchas gracias
Eduardo,
ResponderBorrarpor supuesto. Puedes usar listas (tablas en Excel 2007/10) o rangos dinámicos, como explico en esta nota.
Tambiém puedes crear un gráfico dinámico (pivotchart) a partir de una tabla dinámica.
Tal vez publique una nota mostrando las distintas técnicas.
Señor Jorge
ResponderBorrarMuchas gracias por compartir sus habilidades y conocimientos con el mundo. He revisado este tema del tamaño de los rangos con nombre. Bien, he puesto nombre a una celda (H600)= "ultima_celda" para definir un nombre que va de H5 a H600,("ventas = "H5:ultima_celda") sin embargo, cuando elimino filas vacías ultima celda cambia de posición y resulta ser, por ejemplo, si elimino 10 filas, H590. ¿Qué solución cabe en este caso? Gracias y con toda sinceridad sus aportes me han ayudado inmensamente a comprender mejor excel.
Me llamo Reynerio Lanzas pero no supe como poner mi nombre en este comentario
Reynerio,
ResponderBorrarponte en contacto conmigo por mail privado (fijate en el enlace "Ayuda" en la parte superior del blog).
Hola Jorge,
ResponderBorrarQuería preguntarte 2 cosas en relación a la fórmula que evita el uso de DESREF:
=Hoja1!$A$1:INDICE(Hoja1!$A:$A;CONTARA(Hoja1!$A:$A))
1)¿Cómo sería la fórmula si los datos no empiezan en la fila 1?
2) No entiendo la mecánica de la fórmula. Es verdad que funciona pero ¿por qué la parte INDICE(Hoja1!$A:$A;CONTARA(Hoja1!$A:$A) reemplaza a A7 en tu ejemplo?
Desde España gracias,
Sergio
Sergio,
ResponderBorrar1 - a la función CONTARA tendrías que sumarle un número equivalente a la cantidad de celdas vacías que hay hasta la primer celda ocupada. Por ejemplo, si los valores de la column comienzan en la fila 5, la fórmula tendría que ser
=Hoja1!$A$1:INDICE(Hoja1!$A:$A;CONTARA(Hoja1!$A:$A)+4)
2 - INDICE tiene la característica de dar como resultado un rango. En mi ejemplo la fórmula
INDICE(Hoja1!$A:$A;CONTARA(Hoja1!$A:$A)) da como resultado A7 (la referencia a la última celda del rango),quedando el resultado de toda la fórmula Hoja1!$A$1:$A$7
Hola Jorge,
ResponderBorrarUna pequeña corrección (salvo que mi Excel me esté haciendo jugarretas y no sea como lo pongo a continuación)
Me basé en tu comentario pero cambié $A$1 por $A$5 y ahora sí funcionó:
=Hoja1!$A$5:INDICE(Hoja1!$A:$A;CONTARA(Hoja1!$A:$A)+4)
Al final el tema se resuelve y entiendo el beneficio de no usar funciones volátiles, pero sinceramente me parece más inteligible y más limpio usar la función DESREF para obtener un rango dinámico (que tan bien nos enseñaste a través de distintas notas) salvo casos de extrema necesidad.
Supongo que será una cuestión de gustos y lo importante es resolver las cosas.
Un abrazo y gracias,
Sergio
Tenés razón, se me pasó por alto la corrección de la celda de partida.
ResponderBorrarEn cuanto al uso de INDICE o DESREF es cuestión de gusto personal y funcionalidad. En cuaderno con muchos rangos dinámicos, DESREF puede realmente causar problemas de recalculación. En modelos sencillos, no se nota la diferencia.
Buenas Jorge,
ResponderBorrarMe llamo Jose. Lo primero quiero darte las gracias por todas las veces que me has ayudado con tu blog. Es genial de verdad.
Pero ahora me toca pedirte ayuda directamente. He creado un RANGO DINAMICO (he probado hacerlo tanto con INDICE como con DESREF) y funciona perfectamente. El problema que tengo es cuando intento crear una LISTA DEPENDIENTE que tire de un RANGO DINAMICO (el desplegable no funciona).
No sé sí me explico. Pero vamos, gracias por todo.
Muchas gracias Jorge!!!
Hola José,
ResponderBorrarsupongo que estás usando la función INDIRECTO para crear la lista desplegable dependiente con validación de datos. He tocado este tema en algunas notas. La función INDIRECTO sólo acepta rangos en forma de texto como argumento. Por eso no te funciona con DESREF pero sí tendría que funcionar con INDICE.
Te sugiero que me mandes tu cuaderno por mail privado (fijate en lo que pngo en el enalce Ayuda).
Buenas Jorge,
ResponderBorrarMuchas gracias por contestar, pero sobre todo por ofrecerte a revisar mi fichero.
He probado a repetir el ejercicio en un documento nuevo y funciona tal y como habías explicado. Seguro que es una tonteria. Voy a revisarlo otra vez, seguro que es una tonteria.
Gracias por todo!!!
Un saludo.
Jose.
Buenas Jorge,
ResponderBorrarSoy Jose de nuevo. Ya está! No funcionaba porque al pegar una hoja, concretamente de la que tiraba el RANGO DINAMICO, la aplicación me genero un VINCULO. Por circunstancias, este VINCULO que no debería existir estaba generando un conflicto que hacía que no funcionara los desplegables.
Muchas gracias por todo.
Jose.
Hola jorge.
ResponderBorrarMe llamo juan y no logro entender el comentario que haces:
2 - INDICE tiene la característica de dar como resultado un rango. En mi ejemplo la fórmula
INDICE(Hoja1!$A:$A;CONTARA(Hoja1!$A:$A)) da como resultado A7 (la referencia a la última celda del rango),quedando el resultado de toda la fórmula Hoja1!$A$1:$A$7
Si pones esta formula en cualquier celda, te devuelve el ultimo valor del indice. Porque se comporta de manera distinta al definirla dentro de un nombre de rango?.
Muchas gracias y perdón por mi ignorancia.
Un saludo.
Si te fijas en la ayuda en línea de Excel, verás que la función INDICE tiene dos formas: matricial y de referencia. Esta última es la que usamos al definir el rango dinámico. La definición de la forma de referencia es: "Devuelve la referencia de la celda ubicada en la intersección de una fila y de una columna determinadas".
ResponderBorrarAhora, cuando introduces la fórmula en una celda Excel muestra el valor de la referencia, no el texto de la referencia.
Muchas gracias jorge por la aclaración.
ResponderBorrarEstuve mirando la ayuda excel sobre la forma referencial y logre incluso realizar un ejemplo combinando indice en su forma de referencial con la función suma.
Es gratificante saber que existen personas como tu dispuestas ayudar de forma desinteresada.
Un abrazo amigo.
Hola Jorge, infinitas gracias por alumbrar las tinieblas de excel a los autodidactas!!
ResponderBorrar¿Puedes explicar por qué son mejores los rangos dinámicos que los estáticos del tipo SUMA(A:A)?
SUMA(A:A) obliga a Excel a evaluar un rango de 65000+ celdas (Excel 97-2003) o de más de un millón de celdas (Excel 2007 en adelante). Un rango dinámico acota la cantidad de celdas a evaluar a las necesarias.
ResponderBorrarOtro motivo es el control y prevención de errores. Por ejemplo, estás usando SUMA(A:A) para ir totalizando valores que vas poniendo en celdas de la columna A, pero inadvertidamente dejaste un valor en una celda remota (digamos A123456), de un cálculo anterior o de un cálculo auxiliar. El resultado será incorrecto.
Rápida y Genial respuesta, es la que esperaba, perooo
ResponderBorrarsi pongo la fórmula, por ejemplo, DESVEST($A$1;0;0;CONTARA(A:A);1), ¿no estaría forzando a excel a trabajar lo mismo? ¿trabaja 'menos' la función CONTARA que la SUMA?
Muchas gracias Jorge y ánimo con tu labor!!
Manuel, supongo que en lugar de DESVEST (desvío estándar) tu intención es DESREF. También DESREF($A$1;0;0;CONTARA(A:A);1) es bastante infeciente, pero un poco menos que SUMA(A:A). La forma maás eficiente de crear rangos dinámicos es usando Tablas. Te sugiero que también veas esta nota.
ResponderBorrarUna consulta yo tengo 3 listas desplegables que son dependientes. Cuando en la primera se selecciona "Materiales" y en la segunda "Materiales Principales", la tercera debe mostrar lo que el nombre MaterialesPrincipales le diga. Ese nombre es de rango variable.
ResponderBorrarEl nombre funciona correctamente (creo) pero la lista no me muestra nada.
Puede ayudarme?
Hola (espero que ya estés empleado), en el blog hay varias notas sobre el tema. Fijate en esta; en el primer párrafo hay dos enlaces a otras notas relacionadas.
ResponderBorrarMuchas gracias! Si por suerte si. La duda que tengo es: una lista desplegable puede hacer referencia a un nombre que define un rango dinámico. Eso me salió correctamente en Excel. Pero si esa lista depende de otra entonces la segunda lista no toma el nombre como válido.
ResponderBorrarEso le parece coherente?
Muchas gracias por la ayuda.
Hola, supongo que estás usando al función INDIRECTO para crear los rangos dinámicos dependientes. El problema es que la función INDIRECTO sólo acepta texto como argumento, no rangos. En estas notas muestro dos técnicas para superar este problema:
ResponderBorrarusando la función SUSTITUIR
usando Tablas