Cuando tenemos una hoja de Excel con los datos organizados de forma mensual, es decir, en una columna Enero, luego Febrero, Marzo,…etc. Es habitual tener uno de los siguientes problemas:

• Los datos son mensuales y queremos obtener los datos acumulados.
• Los datos son acumulados y deseamos obtener los datos mensuales.

Para calcular de forma sencilla los datos acumulados/mensuales utilizaremos la función DESREF de Excel. También se puede realizar en base a sumas sencillas: mes n = mes n + mes n-1; el único problema es que esta fórmula no es válida para primer mes y debería ser diferente: mes 1 = mes 1 (hay que tener cuidado para evitar errores).

La función DESREF:

Con la función DESREF devuelve un rango de celdas (una o varias celdas) a partir de una celda o rango base, permite especificar un desplazamiento y después el alto y ancho del rango de celdas. El formato de la función es el siguiente:

     DESREF( ref; filas; columnas; [alto]; [ancho])

Donde:

• ref - La referencia base. Debe referirse a una celda o rango de celdas. (Ejemplo A1 o A1:B3)
• filas - Número de filas de desplazamiento (hacia arriba o hacia abajo). Si el argumento es 5, la celda de referencia pasa a estar cinco filas más abajo de ref.
• Columnas - Número de columnas de desplazamiento(hacia la derecha o izquierda). Si el argumento es 5, la celda de referencia pasa a estar cinco columnas hacia la derecha de ref.
• Alto - (Opcional) número de filas, que tendrá la referencia devuelta. Debe ser positivo.
• Ancho - (Opcional) número de columnas, que tendrá la referencia devuelta. Debe ser positivo.

Ejemplos:

Ejemplo de datos mensuales acumulados:

Supongamos que tenemos un informe con datos mensuales correspondientes al primer cuatrimestre del año, a partir de los datos mensuales deseamos generar los datos acumulados de los mismos meses del primer cuatrimestre. De esta forma “Febrero Acumulado” será igual a la suma de “Enero” + “Febrero“.

Una primera aproximación que podemos utilizar es “Ene A” =A3 y “Feb A” =B2+E2 ; esta fórmula podemos arrastrarla y completar “Mar A” y “Abr A” de forma sencilla, pero los datos de Enero tiene que ser calculados de forma diferente.

Ahora vamos a tratar de resolver el problema con la función DESREF. Con la función DESREF podemos seleccionar el conjunto de celdas que deseamos sumar, la formula final es:

         =SUMA(DESREF($A3;0;0;1;COLUMNA(A3)-COLUMNA($A3)+1))

Donde:

• COLUMNA(A3)
  • devuelve el número de la columna A = 1.
• COLUMNA($A3)
  • devuelve siempre el número de la columna A = 1.
• COLUMNA(A3)-COLUMNA($A3)+1
  • devuelve la diferencia entre las columnas.
• DESREF($A3;0;0;1;COLUMNA(A3) - COLUMNA($A3) + 1)
  • La celda de referencia está siempre en la columna A.
  • No hay desplazamiento de la celda de referencia.
  • El rango será de una fila.
  • Número de columnas a sumar, para Enero será 1, y se irá incrementando hasta para abril donde serán 4, es decir, SUMA(A3:D3).

En la imagen de arriba puede apreciarse la fórmula para Enero acumulado, Suma(A3:A3). Abajo aparece la fórmula para el mes de Abril acumulado, Suma(A3:D3).

Parece un poco más complejo, pero es la misma formula para todos los meses y un buen ejemplo del potencial de la fórmula DESREF.

Descargar:

Excel creado para el ejemplo de la función DESREF

Compártelo