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:

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

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

    ResponderEliminar
  3. 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

    ResponderEliminar
  4. 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

    ResponderEliminar
  5. 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

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

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

    ResponderEliminar
  8. 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.

    ResponderEliminar
  9. 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.

    ResponderEliminar
  10. ERES LO MAXIMO, GRACIAAAAAAAAAASSSS, TE AMOOOOO!!!! JEJEJEJE

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

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

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

    ResponderEliminar
  14. 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

    ResponderEliminar
  15. 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

    ResponderEliminar
  16. 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

    ResponderEliminar
  17. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  18. 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,

    ResponderEliminar
  19. 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

    ResponderEliminar
  20. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  21. Gracias!!!!!, desde Chile.
    Francisco.

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

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

    A mi me ha ayudado mucho.

    Saludos desde Barcelona.

    ResponderEliminar