lunes, 20 de julio de 2009

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.


7 comentarios:

  1. Excelente, acabo de agregar esta página a mis favoritos. Es tan difícil encontrar quien sepa usar excel en español!

    Saludos y Gracias!

    ResponderEliminar
  2. Hola 'Grupo Buhos'.

    Recojo tu sugerencia. Publico cómo se hace con SUMAPRODUCTO y utilizando una Función Matricial.

    Gracias por tu colaboración.

    Adolfo Aparicio

    ResponderEliminar
  3. yo prefiero convertir el valor bboleano con una multiplicación por 1
    =SUMAPRODUCTO((tipo=H7)*1;unidades)

    El inconveniente con esta fórmula es que consume mucha memoria, sobre todo cuando se quiere usar para unificar dos tablas por más de un campo en común.
    Este último mes descubrí que puedo hacer esta tarea con el administrador de conexiones, que puede tomar orígenes de datos de rangos de una misma hoja de cálculo. Sólo es necesario conocer un mínimo de SQL, pero el rendimiento vale la pena; aparte de ello, el tamaño del archivo disminuye notablemente. He tenido excels de 16 b reducidos a 1 o 2 megas.

    ResponderEliminar
  4. Hola Adolfo Aparicio y resto de gente. Veo que sois gente que sabéis un montón de Excel, yo soy un "burrito" que empiezo y no entiendo vuestro lenguaje, para mi demasiado técnico. He intentado utilizar el BDSUMA y no me aclaro. Te pregunto por si me podéis ayudar... Necesito que despues de aplicar un SUMAR.SI el resultado quede inalterable aunque cambie posteriormente los datos que estaban introducidos. ¿Se puede hacer? Necesito vuestra ayuda.GRACIAS.

    ResponderEliminar
  5. BDSUMA se refiere a una base de datos creada en excel o en otra programa gestor de base de datos?

    ResponderEliminar
  6. Adolfo trabajo en una empresa de aves y tengo que hacer una planilla en excel pero no se como puedo sumar estos datos en una sola celda considerando que no siempre van hacer los mismos
    Pabellon Granja Cantidad
    1225-3404-01-1 3000
    1225-3404-01-2 1500
    1225-3404-01-3 1500
    1225-3404-01-4 1500
    estos dato noo necesariamente van hacer lo mismos corrales de alojamiento pueden ser solo dos pabellones los pabellones son los 01 y los corrales son el numero final pueden ser 1.2.3.4.el resto de los numeros son centros de costos me interesa sumar las cantidades.

    ResponderEliminar