Hemos visto varios ejemplos, con distintas funciones, donde hemos calculado acumulados, pues, ahora, vamos a crear un acumulado, con la función DESREF.
Tenemos los siguientes datos, aunque veamos los meses, estos meses corresponden a fechas, que van desde el día 1 de enero del año 2020, hasta el 31 de diciembre del año 2021.
Para que me devuelva el nombre del mes, he seleccionado las celdas, he pulsado, CTRL mas 1, para ir a formato de celdas, en la pestaña número, dentro del grupo, categoría, he seleccionado personalizada, y, en la ventana tipo, he puesto “MMMM”, para que devuelva el nombre del mes.
Bien, fijándonos en el modelo, tenemos valores desde el mes de enero hasta diciembre, correspondiente al año 2020, y, a continuación, del mes de enero a diciembre, pero del año 2021.
Con estos datos, queremos crear un acumulado pero por años, es decir, que cuando termine el acumulado del mes de enero del año 2020, para el mes de enero del año 2021, la cantidad debe de reiniciarse a cero.
Pues, empecemos.
Sabemos que un acumulado, es ir sumando las cantidades superiores, quiere decir, que el primer acumulado, es la primera cantidad, para el segundo acumulado, sería la suma de la primera más cantidad, más la segunda.
Para el siguiente acumulado, seria la suma de las tres primeras cantidades, y, así con el resto de las cantidades.
Si en la celda F3, escribo la siguiente función, donde fijo la referencia D2, para que al arrastrar, no se actualice.
=SUMA($D$2:D3)
Estoy sumando, desde nada, hasta el valor de la celda D3, es decir, 0 + 340.
Como resultado, obtengo, la cantidad de 340, correspondiente al mes de enero del año 2020.
Voy a arrastrar una vez, y, obtengo la cantidad de 545, que es la suma de las cantidades 340, y, 205, correspondientes a los meses de enero, y, febrero del año 2020.
Si miramos la función, al arrastrar, la celda D2 no se ha actualizado, entonces, ha sumado desde D2 a D4, es decir, esta sumando las cantidades, 0, 340, y, 205, con esto tenemos nuestro segundo acumulado.
=SUMA($D$2:D4)
Arrastremos hasta el final.
Ya tenemos nuestro acumulado, pero ¿es lo que queríamos?
La respuesta es no, porque para enero del año 2021, el acumulado debía de haberse reiniciado a cero, y, no lo ha hecho, ha seguido sumando las cantidades, como vemos:
Ha sumado el acumulado, mas la cantidad de enero del año 2021.
Esto lo podíamos arreglar, poniendo un contador al lado de los meses.
El problema es que con la función SUMA, no podemos hacerlo, porque debemos de movernos entre celdas, pero, ya conocemos la función DESREF, que si nos va a permitir movernos entre celdas.
Realmente, debemos de empezar desde cero el ejercicio, o, ejemplo.
Sabemos que con la función DESREF, especificamos un punto de partida, un numero de filas y columnas a movernos, y, un alto, y, un ancho.
Si en una celda, escribo la siguiente sintaxis:
=DESREF(D3;0;0;1;1)
Le estoy diciendo, que a partir de la celda D3, se mueva cero filas, y, cero columnas, por lo que se queda en la celda D3, ahora, que tome un alto de 1, también sigue en la misma celda, y, un ancho de 1, en conclusión, le estamos diciendo que se quede en la celda D3.
Si cambio el argumento alto, por 2.
=DESREF(D3;0;0;2;1)
Me devuelve una matriz desbordada, con los dos primeros valores.
Si englobamos la función DESREF, dentro de la función SUMA, tendremos un acumulado.
=SUMA(DESREF(D3;0;0;2;1))
Vemos que nos devuelve 545, que es la suma de las dos primeras cantidades.
Creo, que la primera cantidad, debería de ser 340, que corresponde al mes de enero del año 2020, es decir, al primer valor.
Recordamos, que en la función SUMA, la primera celda a sumar era una celda donde no había nada, pues, aquí debemos de hacer lo mismo, pero en vez de señalar una celda que no tenga nada, el argumento alto de la función DESREF, lo voy a poner en negativo, que es lo mismo, pero, lo voy a poner en 1.
=SUMA(DESREF(D3;0;0;-1;1))
Vemos que obtenemos la primera cantidad, es lo mismo que hicimos en la función SUMA.
Si arrastro no obtendré el acumulado, porque el argumento alto, siempre va a valer 1, y, queremos que sea dinámico.
Podemos crear un contador al lado de cada mes.
Vamos a cambiar el argumento alto, por el valor de la celda B3, que es donde empieza el contador, lo seguimos dejando en negativo.
=SUMA(DESREF(D3;0;0;-B3;1))
Vemos que tenemos el primar valor, pues, ahora arrastramos, y, tenemos el mismo problema que antes, que no se ha iniciado el acumulado, en el mes de enero del año 2021.
Quiere decir que este contador, aparte, de no ser muy estético, no nos vale, entre otras cosas, porque no es dinámico, porque puede haber más o menos meses.
Pero, si podemos usar el numero de mes, para ello, podemos usar la función MES, para ello, en el argumento alto, vamos a poner la función MES sobre la celda C3, que es el primer mes, debe de seguir en negativo.
=SUMA(DESREF(D3;0;0;-MES(C3);1))
Obtenemos la primera cantidad, pues, vamos a arrastrar.
Vemos que ahora el acumulado en el mes de enero del año 2021, toma la cantidad de dicho mes, es decir, se ha reiniciado a cero, porque en esta ocasión, quien manda es el numero de mes, y, enero, siempre corresponderá con el número 1.
Si nos fijamos en la función de enero del año 2021, a partir de la celda D15, no se mueve ni filas, ni columnas, y, toma un alto de 1, y, un ancho de 1, por lo que queda en la misma celda.
=SUMA(DESREF(D15;0;0;-MES(C15);1)
Pues, hemos terminado nuestro ejemplo.
Comments