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.