Acumular por meses

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.

Ejercicio

Si lo desea, puede practicar con unos datos de fechas e importes con el siguiente archivo.

• ej_acumula.xlsx

Acumular por varios métodos

Descargar el fichero: Acumula.xlsx

Acumular los valores de una base de datos según cierto criterio es una de las tareas más comunes del gestor que utiliza Excel en su trabajo. En este caso presentamos seis métodos de acumulación: con la función de suma condicional (SUMAR.SI), con funciones de base de datos (BDSUMA), con tablas dinámicas, con Subtotales, con la función SUMAPRODUCTO y con una función matricial. De todos ellos posiblemente el método preferido por los usuarios sea el de Tabla Dinámica.




SUMAR.SI

Esta función de suma condicional nos permite sumar bajo cierto criterio. En este caso, el criterio consiste en que coincida con el tipo de lote (1, 2, 3, 4 o 5). La fórmula de la celda F5 es la siguiente:

=SUMAR.SI(tipo;E5;unidades)

=SUMAR.SI(rango; criterio; [rango_suma])

rango_suma es un argumento optativo. Cuando lo que queremos sumar es el argumento rango no es necesario añadir rango_suma, pero en otro caso será imprescindible hacerlo.

BDSUMA

Las funciones de base de datos comienzan por BD, y de ellas las más utilizadas son BDSUMA y BDCONTAR. En este caso, deseamos acumular las unidades por tipo de Lote, por ello utilizamos DBSUMA que acumula según cierto criterio.

La función es la siguiente:

=BDSUMA(base_de_datos;nombre_de_campo;criterios)

La base de datos ha de ser toda ella, incluida la fila de cabecera. Esto es, incluidos los nombres de campo.

El nombre de campo ha de ir entre comillas por tratarse de texto.

Los criterios de base de datos estan compuesto al menos por dos celdas. La primera es un nombre de campo, y la segunda, que se pone bajo la primera, es el criterio propiamente dicho.

Para la celda F13 la fórmula es:

=BDSUMA(basedatos;"Unidades";$E$12:E13)

En este caso el criterio esta compuesto por dos celdas. Son las siguientes:

Para la siguiente celda, F14, lo que deseamos es acumular las unidades del Lote 2. Por tanto el criterio debiera ser:

Pero este criterio no existe en la hoja. Si lo creáramos, nos veríamos obligados a hacer los acumulados en horizontal, y eso no nos interesa, ya que habitualmente la información se presenta en vertical. Por tanto, este criterio queda descartado.

Lo que utilizaremos es un criterio doble, que es el siguiente:

Los criterios múltiples en vertical, acumulan. Esto quiere decir que, en este caso, si empleamos el criterio dado por estas tres celdas (E12:E14), el resultado dado por la función DBSUMA será el número de unidades correspondientes al los Lotes 1 y 2 conjuntamente. Para eliminar esta acumulación nos vemos obligados a restar las unidades del Lote previo. Esto es, restamos las unidades del Lote 1. Pero lo hacemos de una forma muy curiosa. Vease la fórmula de la celda F14:

=BDSUMA(basedatos;"Unidades";$E$12:E14)-SUMA($F$13:F13)

Para restar el Lote 1, hemos utilizado la expresión: -SUMA($F$13:F13).

Observe donde estan situados los dólares en la fórmla. Esto nos permite que al copiar esta fórmula hacia abajo vayamos obteniendo los resultados pretendidos.

Tabla Dinámica

Posiblemente la opción preferida por la mayoría de los usuarios. No requiere que nos acordemos de fórmulas, y simplemente con algunos toques de ratón podemos crear la Tabla Dinámica.

SUMAPRODUCTO

La función SUMAPRODUCTO tiene la siguiente estructura:

=SUMAPRODUCTO(matriz1;matriz2;matriz3; ...)

Esta función permite multiplicar los elementos de cada matriz y luego suma esos productos.

En nuestro caso para la celda I7 la función utilizada es:

=SUMAPRODUCTO(--(tipo=H7);unidades)

Una de las matrices es el rango 'unidades' que contiene los datos numéricos de la base de datos. La otra matriz que utilizamos es un tanto peculiar: --(tipo=H7). Lo que hacemos es igualar el rango 'tipo' con la celda H7, que es en la que figura con la leyenda: 'Lote 1'. El resultado de esta igualdad será el siguiente:

{VERDADERO\FALSO\VERDADERO\FALSO\FALSO\FALSO\VERDADERO\VERDADERO\VERDADERO\FALSO\VERDADERO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\FALSO\VERDADERO}

Lo que se hace es evaluar que valores del rango 'tipo' corresponden con el 'Lote 1'. Esto se puede ver editando la fórmula (por ejemplo con la tecla de función F2), luego seleccionamos con el ratón la expresión (tipo=H7) y pulsamos la tecla de función F9.

Esta serie de valores Verdaderos y Falsos queremos convertirlos en unos y ceros. Para ello debemos convertir esta serie, en valores numéricos. Se podría conseguir multiplicando por 1. Otro método para obtener los valores numéricos consiste en incluir dos signos menos que preceden a la expresión anterior. El primer signo menos multiplica por -1, por lo que ya convierte en numéricos los valores de la serie, y el segundo signo menos sirve para volver a convertir en positivos los valores. Recordemos: "menos por menos: más".

--(tipo=H7)

Finalmente conseguimos esta serie:

{1\0\1\0\0\0\1\1\1\0\1\0\0\0\0\0\0\0\0\1}

que al multiplicarse por la matriz 'unidades' nos da el acumulado de las unidades del 'Lote 1'.

Función Matricial

La idea es análoga a la que se utiliza en SUMAPRODUCTO. En este caso utilizamos una función matricial que se valida pulsando simultaneamente: Control + Mayúsculas + Intro.

En F21 la expresión es:

=SUMA((tipo=E21)*unidades)

Subtotales

Ver la Hoja 2. Con Datos, Esquema, Subtotal en Excel 2007 conseguimos una ventana en la que podemos elegir las opciones de los Subtotales. Este es un caso sencillo en el que únicamente tenemos una base de datos con dos columnas, en las que una de ellas es el tipo de Lote y en la otra tenemos el número de unidades. Lo que queremos es acumular las unidades por tipo de Lote.

La ventaja que ofrece Subtotales es que a al izquierda aparecen unos signos + y - que permiten agregar o desagregar la información. Es muy útil para dar respuestas rápidas en caso de que nos consulten por teléfono.

Tabla Dinámica para clasificar por meses

Descargar el fichero: tdpormeses.xlsx

Clasificar por meses la información de una base de datos es una tarea que se puede hacer de varias formas. La más sencilla es con Tablas Dinámicas. Supongamos que disponemos de una base de datos con información por fechas. Añadimos una columna más que calcule el mes correspondiente a esa fecha. Esto se consigue con la función =MES.

=MES(fecha)

Luego creamos una Tabla Dinámica con los meses y los Tipos. La tabla nos indica cuantas veces aparece en la base de datos un Tipo concreto en un mes determinado.


Los valores de la base de datos son aleatorios, cambian pulsando la tecla de función F9. Para que la Tabla Dinámica se ajuste a la nueva información se ha de actulizar. En Excel 2003 y anteriores esto se hace pulsando sobre una admiración de color rojo que aparece en la barra de Tabla Dinámica. En la versión de Excel 2007 se ha de pulsar sobre 'Actualizar'.



Estos cálculo se podrían hacer con la función BDCONTAR. Esto supondría la ventaja de no tener que actualizar ya que, al tratarse de una función de Excel, se actualiza automaticamente al cambiar la información de la base de datos. Aunque, realmente las Tablas Dinámicas son más amigables para el usuario.