Descargar el fichero: acumulaporfechas.xlsx
Tablas Dinámicas, esta es la revolución en hojas de cálculo. Vamos a resolver un caso de acumulación de importes entre dos fechas cualesquiera, y en particular por meses. Lo haremos mediante fórmulas matriciales y mediante Tablas Dinámicas. En general, tienen mucha mayor aceptación la utilización de Tablas Dinámicas ya que se evita la utilización de fórmulas complejas. Supongamos una base de datos con información de fechas e importes. Estas dos columnas se crean con valores aleatorios. Pulse la tecla de función F9 para comprobar como varían los datos. A esta base de datos añadimos una columna adicional que nos de el Mes correspondiente a cada fecha. Esto se hace con la función =MES(número de fecha).
Método 1
Trabajamos por meses y proporcionamos el importe de cada mes y el acumulado. Se resuelve con fórmulas matriciales que requieren para su validación no pulsar ENTER, sino simultaneamente Control+Shift+Enter.
Para la celda H6 la fórmula es:
{=SUMA((mes=H4)*importe)}
Los corchetes no los debemos escribir nosotros, lo hace Excel para indicar que se trata de una fórmula matricial.
Para el acumulado de los cinco primeros meses utilizamos la fórmula de la celda L7:
{=SUMA((mes<=L4)*importe)}
Método 2
Este método también utiliza fórmulas matriciales, es similar al anterior pero trabaja no necesariamente por meses, sino por periodos donde el usuario define entre que par de fechas se encuantra cada periodo.
La celda H14 contiene la siguiente expresión que nos proporciona el importe correspondiente al primer periodo:
{=SUMA((fecha>=H12)*(fecha<=H13)*importe)}
La celda L15 contiene una fórmula matricial que nos da el acumulado de los cinco primeros periodos:
{=SUMA((fecha>=$H$12)*(fecha<=L13)*importe)}
Método 3
Utilizando Tablas Dinámicas podemos crear una primera tabla con los importe de cada mes. A la derecha de esa columna creamos una de acumulados por meses.
La celda H22 tiene la siguiente expresión:
=+H21+IMPORTARDATOSDINAMICOS("Importe";$F$19;"Mes";F22)
Método 4
Utilizamos Tablas Dinámicas. Veamos paso a paso el proceso.
Paso 1
Primero hacemos una tabla dinámica normal con los importes de cada mes. Para ello arrastramos el 'Mes' hasta 'Rótulo de fila' e 'Importe' hata el recuadro 'Valores'. Esto es en Excel 2007.
Paso 2
Duplicamos la columna de la tabla dinámica. Esto se hace arrastrando con el ratón nuevamente el campo 'Importe' hasta el recuadro 'Valores'.
Paso 3
A la primera columna obtenida que se denomina 'Suma de Importe' la cambiamos el nombre y la denominamos 'Mensual', y a la segunda columna la denominamos 'Acumulado'.
Paso 4
Para conseguir el acumulado debemos pulsar con el ratón sobre el rótulo 'Acumulado' y obtener una ventana denominada 'Configuración de campo de valor'.
Seleccionar la pestaña 'Mostrar valores como', y en el desplegable elegir 'Total en', y como campo base elegir 'Mes'.
Con estos pasos hemos conseguido el resultado que pretendiamos. En una columna tendremos el importe por meses y en la columna de la derecha el acumulado.
El caso planteado y el fichero que lo resuelve son correctos, y por otro lado los métodos no coinciden, ¿cómo es posible?.
ResponderEliminarLa explicación proviene de que las tablas dinámicas se han de actualizar para que los cálculos se renueven. La base de datos esta realizada con valores aleatorios que cambian cada vez que se pulsa la tecla F9 (de recálculo manual) o cada vez que se escribe algo en una celda. Al actualizarse los datos de la base de datos las fórmulas matriciales se actualizan automáticamente, y por el contrario, las tablas dinámicas requieren actualización manual. Para actualizar la tabla dinámica se ha de pulsar botón derecho del ratón sobre la tabla dinámica y elegir 'Actualizar'.
Aún actualizando las tablas dinámicas vemos que no coinciden los resultados con los obtenidos mediante fórmulas matriciales. Esto es debido a que cuando la tabla dinámica se actualiza cambian nuevamente los datos de la base de datos, por ser aleatorios, y nuevamente la información que arrojan las tablas dinámicas ya no están actualizadas. Si nos fijamos vemos que la tabla dinámica al actualizarla va con retraso respecto a la información que dan las fórmulas matriciales. Para verlo, actualiza reiteradamente la tabla dinámica y fíjate en los resultados que proporciona en comparación con los que dan las fórmulas matriciales, y verás que las tablas dinámicas dan los mismos resultados que los de las fórmulas pero con retraso.
Si la base de datos no estuviera creada con datos aleatorios no existiría diferencia entre unos métodos y otros. Si quieres puedes hacer un copiar, pegado especial valores, de la base de datos sobre si misma, y así ya dispondrás de valores fijos, no aleatorios. Lo cual te permitirá ver que todos los métodos arrojan los mismos resultados.
Un saludo.
Adolfo Aparicio
Buenas, tengo una tabla dinámica y tengo un acumulado por meses. Inicialmente el problema era que no diferenciaba meses de distintos años, entonces en vez de poner meses, acumulé por fecha... así me separa Enero 2015 de Enero 2016, e hice el acumulado por fecha, pero cuando cambio de año comienza a acumular de 0... ¿Cómo se puede hacer para que siga acumulando? Gracias si me pueden ayudar! Saludos
ResponderEliminarYo también tengo el mismo problema y no le veo solución.....
Eliminartengo el mismo problema.. cuando cambia de año empieza de cero el acumulado.. alguien pudo resolverlo???
EliminarTengo la misma duda
EliminarPues una solución aunque tosca, es crear otra columna con el acumulado (duplicamos la columna en los datos) y a fecha 1/1 del nuevo año meter una linea en la columna duplicada por el total del acumulado del año anterior. No es lo ideal pero no se me ocurrió otra cosa.
EliminarTengo exactamente el mismo problema Ayelen, pudiste resolver el problema?
ResponderEliminarHola Adolfo,
ResponderEliminarme he bajado tu ejemplo y hay una cosa que no entiendo del Método 1 y es que en el mes de Enero justo encima del resultado del acumulado sólo aparece el Importe del Mes de Enero cuando este lo calcula y aparece Enero en la fila 6, por más que le doy vueltas no entiendo porque lo hace.
Gracias
Disculpa Adolfo, ahora si lo veo, pero me he dado cuenta de que has realizado la primera función para el mes de enero con Matrices y luego la copias en los demás mese.
ResponderEliminarBuenas tardes. como puedo saber el tiempo transcurrido entre dos datos. ejemplo: entre 4 años 11 meses 30 dias y 5 años 12 meses 31 dias,
ResponderEliminarCuantos años, meses y dias pasaron en total??? Gracias y saludos
Gracias!!! me acabas de resolver la vida
ResponderEliminarBuenas tardes, queria hacer una consulta, tengo una tabla donde cada linea es el total de gastos mensual por concepto y necesito armar un informe donde me muestre el total gastado del mes por concepto y el acumulado pero "hasta ese mes", es decir que si estoy mostrando los gastos de marzo, en una segunda columna me muestre los gastos acumulados de Enero a marzo de cada concepto, y si cambio a junio me traiga los gastos del mes y el acumulado Enero/Junio, de que fomar lo puedo hacer? muchas gracias.
ResponderEliminarGracias, muy valioso el aporte.
ResponderEliminar