Muchas veces utilizamos Microsoft Excel para tener informes/datos/sumas de diferentes empresas/sedes/productos que ponemos en hojas diferentes de Excel, para finalmente necesitamos acabamos creando una hoja resumen donde poder ver la visión global de todo.
Para crear un resumen utilizaremos varias funciones que ya hemos explicado anteriormente en Xperimentos: SUMAPRODUCTO, INDIRECTO y DESREF. Con estas funciones y un poco de cuidado a la hora de crear las hojas de detalle obtendremos nuestro resumen de forma automática.
NOTA: Dependiendo del caso, yo personalmente utilizaría una solución basada en una base de datos (MS Access o similar), pero la realidad es que muchas personas se sienten más cómodas con Excel y prefieren utilizar las herramientas que conocen.
Un ejemplo de hoja resumen Avanzado:
NOTA: Vamos a usar el mismo ejemplo que utilizamos en el post de la función INDIRECTO, os recomiendo empezar leyendo el primer ejemplo inicial ya que en este caso daremos muchas cosas básicas por explicadas, en este caso vamos a utilizar las fórmulas SUMAPRODUCTO e INDIRECTO. También os recomiendo descargaros el Excel de ejemplo a la vez para tenerlo abierto a la vez que leéis el post.
Supongamos, por ejemplo, que tenemos un Excel con las compras de cada una de las sedes de nuestra empresa: A, B, C y D.
En cada una de las hojas tenemos el resumen de las compras realizadas con las siguientes columnas de información (cómo se puede ver en la imagen):
- Fecha
- Descripción
- Responsable
- P/U (Precio unitario)
- Importe
Ahora queremos crear un resumen en el cual obtengamos la suma total de cada una de las sedes de forma automática y sencilla:
1) En cada una de las hojas creamos un resumen en la primera fila, es importante que en todas las hojas de las sedes estén los datos en las mismas celdas. En el ejemplo, el Total del importe estará en la celda “F1”.
2) Creamos una hoja Resumen, donde recogeremos los datos totales de cada una de las sedes. En este ejemplo inicialmente sólo recogeremos los totales del importe y la cantidad de cada una de las sedes.
Primero creamos una tabla donde en la primera columna aparecerán los nombres de las diferentes hojas Excel, escritos exactamente igual, y en la primera fila pondremos el nombre de la celda donde están los datos que deseamos recoger de todas las hojas de las Sedes. Con la fórmula =INDIRECTO(”‘”&$B3&”‘!”&C$2) ya tenemos un primer resumen con los datos totales.
3) Ahora vamos a sacar un resumen más avanzado utilizando la función SUMAPRODUCTO y INDIRECTO. Una vez que conocemos los datos totales para cada sede, seguro que nos interesará conocer las compras realizadas en cada uno de los proveedores o incluso que productos se han comprado.
Con una tabla dinámica podríamos conseguir estos datos fácilmente para una hoja Excel, pero cuando se combinan varias hojas Excel debemos utilizar un método más refinado.
Con la fórmula =INDIRECTO(”‘”&C$18&”‘!”&”B3:B15″) obtenemos el rango de celdas entre B3 y B15 (columna donde están los nombres de los proveedores) de la hoja de Excel con el nombre que aparece en la celda C$18 (es decir, “Sede A“). Ahora comparamos la matriz de datos obtenida con $B19 (el nombre del proveedor, en este caso “Pro 1“) =INDIRECTO(”‘”&C$18&”‘!”&”B3:B15″)=$B19 , esta fórmula nos devuelve una matriz de 0s y 1s que indican si la fila es o no del proveedor (coincide con el nombre del proveedor).
Por último, sólo es necesario multiplicar la matriz por los valores de las compras realizadas que los obtenemos con la siguiente fórmula INDIRECTO(”‘”&C$18&”‘!”&”F3:F15″). El resultado de la formula final es:
=SUMAPRODUCTO(1*(INDIRECTO(”‘”&C$18&”‘!”&”B3:B15″)=$B19)*INDIRECTO(”‘”&C$18&”‘!”&”F3:F15″))
Aquí tenéis una imagen con el ejemplo (click para ampliar).
4) Ya tenemos un resumen que permite obtener el detalle de cada proveedor en cada una de las sedes, a partir de aquí podéis realizar cualquier tipo de informe utilizando la misma técnica, en el Excel de ejemplo tenéis dos ejemplos más. Mucha suerte y ánimo que es más sencillo de lo que parece.
Descargas:
Excel creado para el ejemplo de resumen avanzado.
Compártelo
Sobre EDS
Sobre HP
Todos los discos duros son realmente pequeños si esperas el tiempo suficiente. Con esta frase empieza la descripción de la aplicación 
Si el