Rangos dinámicos y funciones volátiles

viernes, mayo 07, 2010

En los comentarios de la nota anterior surgió el asunto de los rangos dinámicos y las funciones volátiles. A pesar de que estos temas han sido tratados de alguna manera en este blog, haremos una reseña en esta nota para aquellos lectores que no conocen el tema.

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.


28 comments:

miguelnormo 08 mayo, 2010 09:33  

Hola,
Podí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

Jorge L. Dunkelman 08 mayo, 2010 10:04  

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.

miguelnormo 08 mayo, 2010 14:03  

Má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)
Un Saludo

Mauricio Robayo 12 mayo, 2010 00:46  

Hola Jorge,

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

Jorge L. Dunkelman 12 mayo, 2010 07:30  

Hola Mauricio
gracias 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.

Eduardo,  28 mayo, 2010 15:49  

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

Jorge L. Dunkelman 28 mayo, 2010 18:48  

Eduardo,
por 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.

Anónimo,  28 julio, 2010 20:54  

Señor Jorge
Muchas 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

Jorge L. Dunkelman 29 julio, 2010 10:57  

Reynerio,
ponte en contacto conmigo por mail privado (fijate en el enlace "Ayuda" en la parte superior del blog).

Anónimo,  01 septiembre, 2010 11:34  

Hola Jorge,

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

Jorge L. Dunkelman 01 septiembre, 2010 19:20  

Sergio,
1 - 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

Anónimo,  01 septiembre, 2010 20:19  

Hola Jorge,

Una 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

Jorge L. Dunkelman 01 septiembre, 2010 21:07  

Tenés razón, se me pasó por alto la corrección de la celda de partida.
En 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.

Anónimo,  10 septiembre, 2010 10:36  

Buenas Jorge,

Me 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!!!

Jorge L. Dunkelman 10 septiembre, 2010 11:48  

Hola José,
supongo 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).

Anónimo,  13 septiembre, 2010 09:48  

Buenas Jorge,

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

Anónimo,  14 septiembre, 2010 16:15  

Buenas Jorge,

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

Anónimo,  20 octubre, 2010 06:52  

Hola jorge.

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

Jorge L. Dunkelman 20 octubre, 2010 08:57  

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".
Ahora, cuando introduces la fórmula en una celda Excel muestra el valor de la referencia, no el texto de la referencia.

Anónimo,  21 octubre, 2010 11:53  

Muchas gracias jorge por la aclaración.
Estuve 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.

Manuel,  14 septiembre, 2015 09:47  

Hola Jorge, infinitas gracias por alumbrar las tinieblas de excel a los autodidactas!!
¿Puedes explicar por qué son mejores los rangos dinámicos que los estáticos del tipo SUMA(A:A)?

Jorge Dunkelman 14 septiembre, 2015 11:20  

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

Manuel,  16 septiembre, 2015 21:34  

Rápida y Genial respuesta, es la que esperaba, perooo
si 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!!

Jorge Dunkelman 17 septiembre, 2015 09:07  

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.

El desempleado 26 enero, 2016 16:23  

Una 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.
El nombre funciona correctamente (creo) pero la lista no me muestra nada.
Puede ayudarme?

Jorge Dunkelman 26 enero, 2016 18:17  

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.

El desempleado 26 enero, 2016 21:30  

Muchas 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.
Eso le parece coherente?
Muchas gracias por la ayuda.

Jorge Dunkelman 27 enero, 2016 07:38  

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:

usando la función SUSTITUIR

usando Tablas

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP