Sumar rangos dinámicos en excel como hacer un rango dinámico

Publicidad

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

Rango dinamico a sumar - vs fijo en excelLa 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.

Ultima fila en excel con formula

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))

Es método me ha parecido el mas sencillo, sin embargo hay mas formas de poder lograr lo mismo.

Descargar Construcción de rango dinámico – Suma de rangos Dínamicos.xls

Si te gusta, compártelo :)Share on Facebook17Tweet about this on Twitter0Share on Google+0Share on LinkedIn0
Publicidad

380 Total visitas 2 Visitas de hoy

2 comentarios en “Sumar rangos dinámicos en excel como hacer un rango dinámico

  1. Hernando

    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!

    Responder
  2. Ricardo Vega Rico

    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))

    Responder

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos necesarios están marcados *

Puedes usar las siguientes etiquetas y atributos HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>