martes, 10 de agosto de 2010

Tablas Dinámicas con Rangos de Consolidación Múltiples en Excel 2007

Descargar el fichero: xPoblamun_TD.xlsx

Habíamos llegado a pensar que la posibilidad de crear Táblas Dinámicas con Rangos de Consolidación Múltiple había desaparecido con Excel 2007, pero no es así. En Excel 2007 y en Excel 2010 podemos crear Tablas Dinámicas con información que proviene de varias tablas que pueden estar en la misma hoja, en distintas hojas, o incluso en distintos libros. Esto es lo que se conoce como Rangos de Consolidación Múltiples.

Primero: obtengamos los datos

Vamos a visitar la página del periódico El Pais. Concretamente su Anuario que contiene información estadística y nos la proporcionan en formato de hoja de cálculo Excel. Su dirección es:

http://www.elpais.com/archivo/anuario/


Vamos a tomar como datos de partida los correspondientes al anuario de 2007. Luego seleccionamos Economía dentro del apartado Internacional. Llegamos a una página en la que descargamos la hoja de cálculo correspondiente al apartado:

Países y territorios según población



Si todo va bien habremos descargado el siguiente fichero:

xPoblamun.xls

Nos centraremos en la Hoja3 que contiene información sobre:

Los países más poblados del mundo 1995-2030

Disponemos de cuatro tablas con los 30 países más poblados en 1995, 2005, 2015 y 2030. Algunos datos son reales y otros previstos.

Segundo: preparemos los datos

Preparamos un poco las tablas. Lo principal es poner en la fila 6 las cabeceras de las tablas. Indicamos la cabecera 'Pais' y para la población utilizamos como cabecera el año: 1995, 2005, 2015, 2030. La República Democrática del Congo la hemos abreviado por Congo. Al final la idea es obtener la información con el siguiente aspecto.


Tercero: veamos el proceso en Excel 2003

Vamos a lanzar el Asistente de Tablas Dinámicas. En Excel 2003 es sencillo, simplemente se ha de ir al menú Datos y allí veremos la opción que lanza el Asistente para Tablas y Gráficos Dinámicos.

El primer paso del asistente nos pregunta: ¿Dónde están los datos que desea analizar?. Elegimos 'Rangos de consolidación múltiple'.



En el segundo paso indicamos que deseamos un solo campo de página.



En la siguiente ventana nos preguntan ¿Dónde están los rangos de hoja de cálculo que desea consolidar?.




Vamos marcando cada rango y pulsando el botón Agregar, hasta tener incluidos los cuatro rangos correspondientes a las cuatro tablas.



En el tercer paso indicamos que deseamos el informe de tabla dinámica en una hoja de cálculo nueva.



Ya tenemos generada la tabla dinámica, aunque será necesario mejorar un poco. Por ejemplo la columna F de Total general aquí no tienen ningún sentido y tendremos que eliminarla. Pero antes de comenzar a mejorar la tabla veremos cómo se realiza este proceso en Excel 2007 y 2010.



Cuarto: veamos el proceso en Excel 2007 y Excel 2010

En Excel 2007 y en Excel 2010 en la Cinta de Opciones se encuentra la pestaña Insertar y  a su izquierda podemos ver la opción Tabla dinámica




Al pulsar sobre 'Tabla dinámica' obtendremos la siguiente ventana



En esta ventana no encontramos la opción de 'Rangos de Consolidación Múltiple'. Por tanto, abandonaremos esta vía.

Para obtener la opción de 'Rangos de Consolidación Múltiple' debemos llegar hasta el Asistente para Tablas y Gráficos dinámicos. Esto se consigue incluyendo en la Barra de Herramientas de Acceso Rápido el icono correspondiente a este asistente.

Inicialmente la Barra de Herramientas de Acceso Rápido contiene muy pocos icono, pero podemos personalizarla. Su imagen en Excel 2010 es la siguiente.




Pulsamos sobre el desplegable que pone 'Personalizar barra de herramientas de acceso rápido'.




Seleccionamos la opción 'Más comandos ...'



Así llegamos a obtener la siguiente ventana.



En 'Comandos disponibles en:' elegimos del desplegable 'Todos los comandos'. Encontraremos muchísimos iconos ordenados alfabéticamente. De entre todos ellos elegiremos 'Asistente para tablas y gráficos dinámicos' y con el botón Agregar le pasaremos al recuadro de la derecha, y aceptaremos.




De esta forma habremos conseguido en la barra de herramientas de acceso rápido el icono correspondiente al Asistente para tablas y gráficos dinámicos.





Quinto: usemos el Asistente

Ya podemos utilizar el icono con el Asistente para tablas y gráficos dinámicos. Al pulsarlo se obtiene esta ventana en Excel 2010.


Donde podemos ver la tan ansiada opción de 'Rangos de consolidación múltiple' que nos permitirá trabajar con tablas de datos que se encuentren en varias hojas, o incluso en varios libros.


En el segundo paso elegimos un solo campo de página.


Agregamos los cuatro rangos correspondientes a las cuatro tablas.


En el paso 3 pedimos crear la tabla dinámica en hoja de cálculo nueva.



En Excel 2010 obtenemos al siguiente tabla dinámica.


Eliminamos la columna de Total general, y añadimos una columna que nos de el porcentaje de variación de la población entre 1995 y 2030. Así obtenemos la siguiente tabla.


Los colores de las celdas se han añadido de forma manual y nos permiten efectuar un análisis de la evolución de la población para los próximos años. Es sorprendente ver la caída de la población en Rusia, y que India iguala o incluso supera a China.

Nota

También se puede obtener el Asisten para tablas y gráficos dinámicos en versiones posteriores a Excel 2003 pulsando la combinación de teclas ALT+T+B.

24 comentarios:

NERY dijo...

Saludos Cordiales desde Guatemala
Gracias esta clase de sitios son de los que hacen la diferencia de entre tanta cosa inutil de la cual esta saturada la internt

Alex.Chevez dijo...

como creas la columna de % que formula pones para que se llene con los porcentajes?.

Adolfo Aparicio dijo...

Hola Alex.

Para ver las fórmulas puedes descargar al archivo de Excel con todo el ejercicio. Lo tienes al inicio del Post.

Un saludo.

Adolfo Aparicio

Dave dijo...

Estimado Amigo, muchas gracias por tu aporte. Hace varios dias que no encontraba la forma de pivotear multiples tablas en excel, lo que me producia una "Referencia Circular con error" a mi cerebro jajaja!!!

Tu aporte aporte fue de gran ayuda para solucionar el aprieto

Gracias

David Silva
Costa Rica

Jorge Omar dijo...

Excelente articulo!!!!!!!

Me tenian haciendo estas cosas de consolidacion con un proceso manual hartisimo y demasiado demorado. Habia considerado la opcion de tener solo Excel 2003 en algun pc cerca para hacer estas dinamicaas y despues copiar el archivo en mi pc de trabajo... gracias de verdad.

Omar Lopez
Colombia

taryn dijo...

Buenas tardes, quisiera saber si tiene el tema de tablas dinamicas y como darlew porcentaje
gracias muy interesante la pagina

Amador dijo...

Muchíiiiiisimas gracias!!!!
Menos mal que hay "complementos" a la absurda ayuda de Office...

José Manuel Agundis dijo...

Buen día desde México:
Sus aportes son muy buenos para los que usamos esta herramienta, un atajo para las versiones posteriores a 2003
es presionar secuencial-mente ALT+T+B Y nos abrirá el asistente para tablas dinámicas.
Saludos y felicidades por su blog.

José Manuel Agundis dijo...

Buen día desde México:
Sus aportes son muy buenos para los que usamos esta herramienta, un atajo para las versiones posteriores a 2003
es presionar secuencial-mente ALT+T+B Y nos abrirá el asistente para tablas dinámicas.
Saludos y felicidades por su blog.

Caro dijo...

ERES LO MAXIMO, GRACIAAAAAAAAAASSSS, TE AMOOOOO!!!! JEJEJEJE

MARIO dijo...

Gracias por la lección, Aparicio. Me ha parecido muy practica y muy bien explicada.

Padock Villarreal Méndez dijo...

Excelentísimo ejemplo de dos temas que me parecía poco probable utilizar en conjunto. Consolidados y tablas dinámicas. Qué buen post. Felicidades

Abraham Huerta dijo...

Excelente, por fin encontra la manera de hacerlo, gracias por la aportacion.

Jorge A Palacios C. dijo...

lo que pasa es que tengo que combinar 3 hojas de un libro (fact venta, nota credito, nota debito) estas estan asociadas por numero de fact de venta. lo que se requiere es que salga una fila los datos de fact de venta, en la siguiente o siguientes filas nota debito o credito asociada, y luego pues la siguiente fact de venta. todo eso que me lo ordene por ciudad,razon social, # fact

Nicolás F Bedoya M dijo...

Saludos desde Colombia.
Adolfo, ante todo gracias por el valioso aporte, es muy útil.

Estoy creando una tabla dinámica a partir de varias tablas que tienen números y texto; sin embargo, no logro que me incluya el texto en la tabla dinámica, en cambio, me pone "0". Cómo lo soluciono.

De antemano, gracias.

Nicolás F. Bedoya

Alejandra dijo...

Hola, me gustaría saber bien dónde radica la diferencia entre crear un solo campo de página y personalizar campos de página (más de uno). Es según cómo me vienen las tablas? Es según la finalidad de mi informe?. No me queda claro. Gracias

Alejandra dijo...
Este comentario ha sido eliminado por el autor.
Edgar Giovanni Daza dijo...

Buenas Tardes: Tengo 2 consultas, sobre Tablas Dinámicas en consolidación de datos de diferentes rangos:

1.Como hacer para que la lista del primer campo de filtro (por ejemplo País), se muestre en orden alfabético.

2. En el proceso de Tablas Dinámicas con Datos de Consolidación de diferentes Rangos, como mostrar en el segundo filtro de campos, por ejemplo, Clientes, solamente los que pertenecen al criterio de selección del primer campo, en este caso el país.

Es decir, lo que me sucede, es que tengo armada la tabla dinámica, con diferentes rangos de datos (Muchísimas Hojas) y al seleccionar el primer filtro que es país (Lo muestra de cualquier orden y deseo que sea alfabético), pero además que al filtrar por el camopo2 que son los clientes, solo me muestre los clientes de ese país y no todos e igualmente en orden alfabético.

Muchas Gracias,

JAIME MORALES dijo...

Muy buen articulo... Sabes ya lo utilise para realizar una tabla dinamica con multiples hojas... y me quedo bastante buena...
pero ahora necesito poder incorporar un filtro al informe y no puedo.
me aparecen tres campos de informe... Fila, Columna, Valor...
Pero quisiera que datos de COLUMNA aparecieran rotulos de columna y en filtro... pero no logre hacerlo. me puedes ayudar, se puede realizar..?? Un saludo gracias

Patricia FG dijo...
Este comentario ha sido eliminado por el autor.
Chino2014 dijo...

Gracias!!!!!, desde Chile.
Francisco.

ANGELES dijo...

Muchisimas gracias. Es perfecto!

ANGELES dijo...

Muchisimas gracias. Ha sido realmente útil y claro.
Gracìas por tu esfuerzo

Joaquim Borrell dijo...

Muchísimas gracias por brindarnos tu tiempo y dedicación.

A mi me ha ayudado mucho.

Saludos desde Barcelona.