Corrección automática de rangos en fórmulas

jueves, octubre 09, 2008

Supongamos esta sencilla tabla que muestra las ventas de una tienda por departamento



Vemos que faltan los datos del Departamento 3. Al insertar una línea entre los departamentos 2 y 4, el rango de la fórmula =SUMA(B2:B5) se corrige automáticamente



Veamos qué pasa si queremos agregar el Departamento 6, inmediatamente debajo del Departamento 5. Seleccionamos la fila 7 e insertamos una nueva fila. En este caso, Excel no corrige el rango de la fórmula



En ciertos casos, al agregar los datos del nuevo departamento, éstos no serán incluidos en la suma! Si tenemos habilitada la opción Extender formato de rangos y fórmulas en el menú Opciones y la tabla tiene más de cinco líneas, Excel modificará el rango de la fórmula automáticamente. Pero si pegamos los datos que hemos copiado de otra hoja o fuente, o no tenemos habilitada la opción, Excel no modificará el rango de la fórmula.

Cuando creamos cuadernos que serán usados por otros, no podemos estar seguros que al agregar líneas o hacer otras modificaciones los usuarios presten atención a este potencial problema.

La solución es usar la función DESREF para crear un rango que se adapte automáticamente cada vez que se agreguen líneas en cualquier ubicación del rango de la fórmula.

En nuestro caso la fórmula será

=SUMA(B2:DESREF(B8,-1,0))



Como ven, el primer argumento de la función, el "ancla", se refiera a la celda que contiene la fórmula. El segundo indica una referencia a una fila hacia arriba.

El mismo caso puede darse cuando insertamos una columna en un rango donde sumamos a los largo de una fila. Por ejemplo, si transponemos nuestra tabla, la fórmula será

=SUMA(B2:DESREF(G2,0,-1))








Technorati Tags:

13 comments:

Jose,  09 octubre, 2008 13:55  

Jorge, muy bueno tu comentario.
Segun lo que vos explicas tambien podremos anclar el primer sumando para el que caso que querramos agregar una fila antes del Departamento 1. La formula deberia quedar:
=SUMA(DESREF($B$1,1,0):DESREF(B8,-1,0))

Esto mes es muy util ya que normalmente trabajo con planillas donde tengo que insertar filas o columnas al principio o final.

Gracias! Jose

David 09 octubre, 2008 20:04  

Muchisimas gracias por los consejos que publica, me son de gran ayuda. Estoy empezando a manejar excel y tengo un conocimiento limitado, tan solo lo utilizo para confeccionarme una herramienta de trabajo. Mi pregunta es la siguiente. ¿Podria poner que si se cumple un requisito me salte una lista? EJEM.
=SI(J31=0;0;SI(J31=5;LISTA)).
Muchas gracias y animo.

Jorge L. Dunkelman 10 octubre, 2008 07:09  

Hoa David

no, las fórmulas sólo pueden dar un resultado, no pueden cambiar la estructura de la hoja.
Si se podría programar un evento que corra una macro de acuerdo al resultado de la fórmula.

David 10 octubre, 2008 18:13  

Muchas gracias por la respuesta y creo que antes de intentar programar eventos o macros buscare otra solucion. me gustaria saber si puedo simplificar esta formula que debo de hacer mas larga y ademas tengo que repetir en varias celdas. Esta contiene un error que tratare de encontrar, lo que me gustaria es no tener que utilizarla
ni repetirla.=SI(R20=0;0;SI(R12=0;T14;SI(C35="D";B33;SI(Y(C35=0;E35="D");B33*2;SI(Y(C35="V";E35="D");B33*2+2;SI(Y(C35=0;E35=0;G35="D");B33*3;SI(Y(C35="V";E35="V";G35="D");B33*3+4;SI(Y(C35=0;E35="V";G35="D");B33*3+2;SI(Y(C35="V";E35=0;G35="D");B33*3+2))))))))).
Es solo un cuarto de lo que realmente ocupa.
muchas gracias maestro.

Jorge L. Dunkelman 11 octubre, 2008 18:56  

David

entre otras cosas, la función SI sólo acepta 7 niveles, es decir, sólo puedes "anidar" siete condiciones. Tu fórmula tiene 8.
Pot otro lado, cuando tienes que usar tantas condiciones conviene intentar usar otras funciones, como ELEGIR.

David 13 octubre, 2008 11:47  

Gracias Jorge por tu comentario, me di cuenta de que no podia anidar mas de 7 cuando quise verificar la formula, para este caso e empleado la combinacion de SI con CONTAR:SI porque no entiendo bien ELEGIR. Ya he leido tus notas y las de la ayuda de excel y sigo sin entenderlo, te envio la formula que he empleado en este caso para que me comentes como puedo utilizar ELEGIR,
=SI(R20=0;0;SI(R12=0;T14;SI(C35="D";B33;SI(E35="D";B33*2+CONTAR.SI(C35:D35;"V")*2;SI(G35="D";B33*3+CONTAR.SI(C35:E35;"V")*2;SI(I35="D";B33*4+CONTAR.SI(C35:G35;"V")*2;SI(K35="D";B33*5+CONTAR.SI(C35:I35;"V")*2)))))))
Gracias de nuevo por tu paciencia.

Jorge L. Dunkelman 13 octubre, 2008 13:52  

Hola David

tu consulta excede el amrco de un comentario. Tienes que dirigirla a través del mail (aparece en la columna izquierda del blog, arriba).

Agustin 17 diciembre, 2014 18:21  

Buenos días Jorge,
estoy usando formulas del tipo "=SUMA(B2:DESREF(B8,-1,0))" que explicas en este artículo desde hace mucho tiempo.
Hoy me empezaron a dar error de referencia circular en excel 2007. Sabés si hubo alguna actualización que origine esto?
Saludos.

Jorge Dunkelman 17 diciembre, 2014 18:50  

Hola Agustín, si bien la actualización del 9/12 de Microsoft ha generado unos buenos desmadres (en particular controles ActiveX y macros), no creo que sea el caso.
¿Cual es la función DESREF en tu modelo? Por ejemplo, si por error introducimos en la fórmula de la nota =SUMA(B2:DESREF(B9,-1,0)), la función SUMA incluiría la celda que la contiene creando así una referencia circular.

Agustin 18 diciembre, 2014 00:02  

Gracias por tu respuesta Jorge,
es una planilla que venia funcionando bien, y sin modificar nada empezó a dar error de referencias circulares (con una ventana emergente al abrir el archivo y leyenda "referencias circulares: G9" abajo a la izquierda en la barra de estado)

En la celda G9 ingresé esta fórmula "=SUMA(G$3:DESREF(G9;-1;0))"

Más allá del error la fórmula arroja el resultado correcto.

Jorge Dunkelman 18 diciembre, 2014 09:26  

¿Cuál es el contenido de la celda G9, constante(número) o referencia/fórmula)?

Agustin 18 diciembre, 2014 17:25  

Hola Jorge,

Lo solucioné con el menú formulas>comprobación de errores>Referencias circulares, que me indicaba un error en otra hoja (en la celda G36). Corrigiéndolo dejó de aparecer la leyenda "referencias circulares: G9" abajo a la izquierda en la barra de estado.

En concreto: error mío en una fórmula, error de excel en la barra de estado.
Aprendizaje: uso del menú comprobación de errores.

Muchas gracias,
Saludos.

Jorge Dunkelman 18 diciembre, 2014 18:31  

Je, je

Por eso mi abuellita decía que en el colegio primero aprendes la lección y luego te toman el examen mientras que la vida primero te toma el examen y entonces aprendes la lección.

En realida no se trata de un error de Excel en la barra de estado; en cada hoja te muestra la celda que "participa" en la fiesta de la referencia circular. El truco es llegar al origen (como descubriste por tus propios medios).

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP