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: MS Excel
Jorge, muy bueno tu comentario.
ResponderBorrarSegun 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
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.
ResponderBorrar=SI(J31=0;0;SI(J31=5;LISTA)).
Muchas gracias y animo.
Hoa David
ResponderBorrarno, 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.
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
ResponderBorrarni 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.
David
ResponderBorrarentre 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.
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,
ResponderBorrar=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.
Hola David
ResponderBorrartu consulta excede el amrco de un comentario. Tienes que dirigirla a través del mail (aparece en la columna izquierda del blog, arriba).
Buenos días Jorge,
ResponderBorrarestoy 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.
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.
ResponderBorrar¿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.
Gracias por tu respuesta Jorge,
ResponderBorrares 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.
¿Cuál es el contenido de la celda G9, constante(número) o referencia/fórmula)?
ResponderBorrarHola Jorge,
ResponderBorrarLo 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.
Je, je
ResponderBorrarPor 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).