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 sencillo resumen podemos utilizar la función INDIRECTO de Excel, con esta función y un poco de cuidado a la hora de crear las hojas de detalle obtendremos nuestro resumen de forma automática.

NOTA: Este es un ejemplo sencillo que os recomiendo leer antes de pasar a otro ejemplo más avanzado. Sí queremos obtener resúmenes más complejos podemos combinar las fórmulas INDIRECTO, SUMAPRODUCTO y DESREF podéis ver un ejemplo en: Resumen avanzado con datos de diferentes hojas Excel (Será publicado en breve).

La función INDIRECTO:

En la ayuda de Excel encontramos la siguiente definición:

“Devuelve la referencia especificada por una cadena de texto. Las referencias se evalúan de inmediato para presentar su contenido. Use INDIRECTO cuando desee cambiar la referencia a una celda en una fórmula sin cambiar la propia fórmula.”

Ahora ya sabéis lo que es, ¿no?. Ciertamente con esa definición yo no me he enterado de mucho, así que lo explicaré con otras palabras y un ejemplo.

La función INDIRECTO nos permite utilizar un texto como referencia, es decir, convierte el texto en una parte de la función de Excel. Un ejemplo muy sencillo de cómo funciona, lo podemos ver en la siguiente imagen:

image 

En la celda B2, hemos escrito:

            =INDIRECTO(A1&A2)

Donde:

  • A1 contiene “C”.
  • A2 contiene “1”.
  • Concatenando A1 y B2 (es decir, =A1&B2) obtendríamos “C1”.
  • Finalmente al aplicar la función INDIRECTO() sobre el contenido de A1 y B2, obtenemos el mismo resultado que si hubiéramos puesto en la celda “=C1”, en el este caso “Ejemplo INDIRECTO”.

NOTA: También es interesante utilizar la función INDIRECTO para que una fórmula siempre haga referencia a las mismas celdas, porque Excel actualiza las fórmulas automáticamente cuando las celdas se desplazan al eliminar/insertar/cortar/pegar filas o columnas.

Un ejemplo de hoja Resumen:

Supongamos, por ejemplo, que tenemos un Excel con las compras de cada una de las sedes de nuestra empresa: A, B, C y D.

image

NOTA: Otros ejemplos o casos típicos son tener una hoja Excel por mes/año, por producto, por cliente… etc. Cuando el problema se complica, por ejemplo, si tenemos pestañas por meses y un documento Excel para cada Sedes, la solución más fácil es utilizar vínculos entre documentos Excel. Personalmente, estoy en contra de los vínculos en Excel, ya que son siempre foco de problemas (enlaces rotos, desactualizados,…). Para estos casos os recomendaría utilizar una herramienta de Business Intelligence integrada con Excel, por ejemplo PALO que además es gratuita.

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

image

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”.

image

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  ='SEDE A'!D1 obtendríamos el valor de la cantidad total de la Sede A, si ahora utilizamos la fórmula INDIRECTO quedaría =INDIRECTO("'SEDE A'!D1"). Finalmente sustituyendo “Sede A” y “D1” por el valor de las celdas B3 y C2 nos queda =INDIRECTO("'"&$B3&"'!"&C$2). Ponemos “$” para que nos valga la fórmula para toda la tabla que hemos creado según se puede ver en la siguiente imagen:

image

Sólo nos queda ocultar la segunda fila y ya estará la primera tabla del resumen terminada 🙂

3) Imaginación al poder; utilizando la misma técnica podemos crear todo tipo de resúmenes, no obstante está técnica se queda corta si queremos obtener cosas un poco más complejas, por ejemplo: ¿Cuál ha sido el importe total de las compras al proveedor número 6 («pro6»)? esto lo veremos en el próximo post: Resumen avanzado con datos de diferentes hojas Excel (Será publicado en breve).

Descargas:

Excel creado para el ejemplo de la función INDIRECTO.

Be Sociable, Share!