Cargando...
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:
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:
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“.
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:
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:
5 comentarios sobre Función DESREF. Calcular datos mensuales acumulados en Excel.
Hola:
En este caso concreto, en lugar de =SUMA(DESREF($A3;0;0;1;COLUMNA(A3)-COLUMNA($A3)+1)) podemos poner =SUMA($A9:A9) y el resultado también se acumula. Pero coincido en que DESREF es una gran función.
Un saludo,
Sergio
Estimado Sergio,
Primero de todo, gracias por tu comentario.
Seguramente ha sido un pequeño error pero la función
=SUMA($A9:A9)
, es igual que=A9
lo cual no tiene ningún sentido en dicho ejemplo.Sin embargo, tienes toda la razón al decir que la función anterior puede ser sustituida por
=SUMA($A3:D3)
y daría el mismo resultado de una forma más sencilla.Un saludo,
Lcflores
necesito saber cómo con la función desref u otra función podemos coger los datos referidos a una celda de esa misma hoja ( hoja 1 )pero que trae datos de otra hoja y libro ( hoja 2 ).
Quiero utilizar la fórmula que tenemos en la hoja 1 y que me coga los datos cinco columnas a la derecha de la hoja 2
Creo que estoy entrando un tanto retrasado.
En la fórmula de la celda E3 (primer acumulado de enero) puedes sustituir
COLUMNA(A3)-COLUMNA($A3)+1
por
COLUMNAS($A$10:A10)
Fe de Errata:
donde dice A10 debe decir A3
Deja tu comentario sobre Función DESREF. Calcular datos mensuales acumulados en Excel.
*Nota: Sólo se tendrán en cuenta los comentarios correctamente redactados y que estén relacionados con el tema de la entrada.RSS a los comentarios de esta entrada · TrackBack URI