Cargando...
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:
En la celda B2, hemos escrito:
=INDIRECTO(A1&A2)
Donde:
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.
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):
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 ='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:
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:
15 comentarios sobre Función INDIRECTO. Sencillo resumen con datos de diferentes hojas Excel
[…] 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 […]
hola.
el ultimo ejemplo que escribes en el fondo es montar una fórmula dinámica en excel. hay alguna manera de hacer esto? del estilo:
=indirecto (“suma(c1:c100)”)
con esto se podría estar formulando formulas en el propio excel.
saludos y buen artículo!
Hola Jordi,
Me temo que la función indirecto no permite incluir fórmulas (Suma,Avg,..etc) sólo referencias. Pero sin embargo si que puedes hacer
=SUMA(INDIRECTO(....))
. Para crear la fórmula que indicas la técnica adecuada (si hay otra forma yo la desconozco) es una macro y asignarle una fórmula concreta a una celda de Excel.Un saludo
Lcflores
ME CANTA LA HOJA DE EXCEL ES MUY PADRE
Hola ICFLORES
La función indirecto me salvo mucho es una muy buena herramienta.
Sabes ahora ¿como se hace todo lo contrario, en vez de agregar información a una hoja resumen, se puede llevar desde una hoja a varias.?
el asunto es que tengo un listado de personas en una columna en una hoja con sus datos en columnas
u quiero llenar un formulario con los datos de cada persona individualizada en una hoja.
juan perez
oscar fernandez
jaime rodriguez
etc
quiero una hoja que en cierto campo diga juan, otra hoja con el mismo campo pero que diga oscar,
jaime en la siguiente, etc.
y que tenga campos en blanco a modo de formulario que llegue a su domicilio ellos lo llenen y lo envien devuelta.
es como combinar correspondencia en word
¿se entiende.?
Bueno agradecería me pudieses ayudar
Atte. Oscar
Hola Oscar,
Lo que intentas hacer en Excel es muy complicado, es posible hacerlo, pero requiere bastante esfuerzo ya sea en base a fórmulas o con una Macro. Sin embargo, esto es relativamente sencillo realizarlo con Access, no sé si dispones de este programa porque es parte del paquete de Office Professional.
En Access sería tan “sencillo” como importar tu hoja Excel como una tabla, y generar un informe con los campos en el formato deseado.
Lcflores
SI ME AGRADO MUXO
tengo una pregunta, la forma en como usas la funcion indirecto, puede ser usado para referenciar a otros archivo o libros de excel. se que con macros se puede, el problemas es que no se mucho de esto.
Ejm: tengo un archivo con una hoja, pero que necesita extraer datos de otros archivos que me envian via internet (e-Mail adjunto), y so se como hacerlo, o existe alguna otra funcion
JESUS
Hola!
Intento hacer lo mismo que has explicado solo que, en vez de hojas del mismo libro, usando libros distintos. Utilizo un campo que he llamado vía y, además de concatenar la posición de la celda que quiero copiar, concateno la vía donde se encuentra el archivo.
Funciona bien, excepto por el pequeño y molesto detalle que el libro fuente tiene que estar abierto para que actualice los datos. No puedo utilizar actualizar vínculos porque no los reconoce como tal.
No sé si me he explicado correctamente…
Muchas gracias por tu ayuda.
Excelente información! Gracias!
Excelente información. Continúen así. Gracias
Muchas Gracias….
Me sirvió bastante.!!! Era lo que necesitaba.
Gracias por el aporte.
¿Podrías detallar más la lógica que empleaste en el uso de las “” del símbolo &, para lograr que se referencia a las distintas hojas del ejemplo.
Gracias
Hola RGlugo,
De forma breve.
> Las comillas “” se usan para delimitar cadenas de texto.
> El símbolo & para concatenarlas.
Realiza un par de pruebas y lo entenderás rápidamente.
Un saludo,
LCF
Gracias por el artículo. Me salvas la vida y la vista, llevo varios días intentando hacer lo que acabas de explicar.
Gracias, gracias, gracias!
Deja tu comentario sobre Función INDIRECTO. Sencillo resumen con datos de diferentes hojas Excel
*Nota: Sólo se tendrán en cuenta los comentarios correctamente redactados y que estén relacionados con el tema de la entrada.RSS a los comentarios de esta entrada · TrackBack URI