Sumar rangos dinámicos en excel como hacer un rango dinámico
-->Que fácil todo fuera si excel nos permitiera actualizar de forma automática sus fórmulas al ir aumentando o disminuyendo nuestros datos, por ejemplo tenemos la fórmula =SUMA(B3:B8) la cual suma el rango fijo B3:B8 pero que pasa si ingresamos más datos a sumar en la celda B9, B10, etc. Se puede apreciar claramente que la fórmula no abarca todo el rango, es decir no abarca el ingreso de datos nuevos; esta situación se soluciona mediante la creación y/o construcción de un rango dinámico el que nos permite flexibilidad , entonces hablamos de un rango que se ajusta a lo que necesitamos; de esta forma no estaremos actualizando de forma manual.
Te explico como hacerlo: (Al final de esta publicación puedes descargar el archivo)
Primeramente debes establecer las celdas sobre las cuales vas a trabajarlo, para este ejemplo sería bueno que te guies de acuerdo a mis rangos utilizados. (Mira la imagen para que te guies de las filas y columnas usadas).
La celda E3 es donde se ingresará la formula que tomará a un rango dinámico.
La celda D3 contiene una fórmula la cual suma a un rango fijo. =SUMA(B3:B6)
Ahora si vamos a construir nuestro rango dinámico para lo cual necesitamos saber el concepto de algunas fórmulas tales como:
DESREF: Sirve para desplazarse tomando una celda de referencia, por ejemplo =DESREF(A1,1,1) nos muestra el valor de la celda B2 ya que nos hemos desplazado 1 fila (fila 2) y 1 columna (columna B). También esta fórmula nos permite ampliar dicho rango mediante el uso de argumentos como alto y ancho, ¿Qué significa esto? por ejemplo si usamos esto: =DESREF(A1,0,0,10,1) estariamos indicando que nuestro rango tiene un alto de 10 filas y 1 sola columna, esto se utiliza en conjunto con alguna fórmula, así como lo plantié saldrar error.
Para construir este rango dinámico necesitamos saber cuantas filas tiene nuestro rango y esto variará de acuerdo a los datos que ingresemos, entonces para lograrlo utilizaremos lo siguiente: =COINCIDIR(9.99999999999999E+307,B:B)
Lo que hace la formula en rojo es encontrar la ultima fila con datos. Si vemos la imagen podemos darnos cuenta que la última fila es la 6.
Si bien es cierto que la última fila es la 6 podemos darnos cuenta que nuestro alto del rango es de 4 entonces tenemos un exceso de 2, para lograr con exactitud el tamaño del rango aplicaremos esto.
COINCIDIR(9.99999999999999E+307,B:B)-FILA(B3)+1
Lo que hace la formula en verde es simplemente ajustar el rango para que nos de un tamaño de 4.
DESREF(B3,0,0,COINCIDIR(9.99999999999999E+307,B:B)-FILA(B3)+1,1)
Poco a poco vamos consolidando la fórmula, ya sabemos el alto del rango dinámico, tambien el ancho (es de 1 ya que solo estamos tomando una columna)
Ahora procederemos a colocar la función SUMA para que se aplique a ese rango dinámico y nos debe quedar así:
=SUMA(DESREF(B3,0,0,COINCIDIR(9.99999999999999E+307,B:B)-FILA(B3)+1,1))
Este método me ha parecido el mas sencillo, sin embargo hay mas formas de poder lograr lo mismo.
GENIAL EL SEGUNDO EJEMPLO.
ME MANTENDRÉ EN CONTACTO CON USTEDES ,
SALUDOS DE ENSENADA BAJA CALIFORNIA
Gracias por su excelente apoyo , me gusta usar mucho excel y es de gran ayuda sus ejemplos
mil bendiciones
gracias por el aporte, creo que me va a servir para un informe que estoy elaborando, y que requiero se actualice automaticamente al realizar las consultas entre fechas.
saludos
Excelente!
Gracias por este sitio: Tengo que contar en una hoja excel la cantidad de registros de un detalle de facturas, entendiendo que una factura puede tener de uno a cinco de forma variable, pueda que una tenga solo dos, otra tres y así, gracias.
Gracias por la información Sebastiani, es muy interesante y me ha servido de mucha ayuda para lo que he necesitado. Gracias por enseñarnos cada día algo más…
Saludos desde Colombia!
Sebastiani tu pagina es genial, es mi periódico diario la reviso, gracias por todo lo que nos enseñas, quería compartir contigo la formula de como lo hago yo creo que es un poco mas simple todavía. (El maestro eres y seguirás siendo tu). Saludos
=SUMA(DESREF($B$3,0,0,CONTARA($B:$B),1))