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:

Fórmula Descripción
=DESREF(A1;2;3;1;1) Mostraría el valor en la celda D3
=SUMA(DESREF(A1:E5;1;0;3;3)) Suma el rango A2:E6
NOTA: Tiene que haber coherencia entre el rango de origen y los datos de referencia

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

image

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

image

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

image

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

Be Sociable, Share!