• Tablas dinámicas.

Esta opción permite resumir y analizar rápidamente valores contenidos en una tabla o base de datos. Excel ofrece diferentes opciones de distinta grado de complejidad a la hora de implementar tablas dinámicas. Comenzaremos con un ejemplo sencillo y de esta forma iremos familiarizándonos con el asistente para tablas dinámicas. Los ejemplos los construiremos sobre la base de datos siguiente, puede encontrarla en el archivo Datos.xls

 

Nombre	Ciudad	Provincia	Edad	Ocupación	Fecha	Ingreso
Pedro	Madrid	Madrid	32	Abogado	05-01-00	55000
Javier	Getafe	Madrid	44	Administrativo	06-04-00	50000
Ana	Hospitalet	Barcelona	32	Abogado	07-04-00	60000
Nuria	Getafe	Madrid	55	Administrativo	08-06-00	45000
Teresa	Jerez	Cádiz	54	Administrativo	09-08-00	40000
Agustín	Cádiz	Cádiz	35	Gerente	02-02-00	55000
Ramiro	Madrid	Madrid	37	Administrativo	12-10-00	40000
Luís	Barcelona	Barcelona	43	Abogado	09-09-00	40000
María	Barcelona	Barcelona	47	Administrativo	04-03-00	45000
José	Hospitalet	Barcelona	38	Gerente	08-01-00	50000
Antonia	Cádiz	Cádiz	48	Gerente	09-05-00	60000
Mercedes	Barcelona	Barcelona	39	Administrativo	03-06-00	55000
Juan	Hospitalet	Barcelona	41	Abogado	08-07-00	50000
Esther	Getafe	Madrid	40	Abogado	02-12-00	40000
Pilar	Madrid	Madrid	53	Administrativo	03-10-99	40000
Ramón	Madrid	Madrid	57	Gerente	02-04-00	55000
David	Hospitalet	Barcelona	48	Gerente	12-11-00	50000
Olga	Barcelona	Barcelona	35	Administrativo	04-08-00	60000
Manuel	Jerez	Cádiz	40	Administrativo	02-10-00	55000
Dolores	Madrid	Madrid	50	Gerente	05-03-00	45000

Ejemplo.

Tabla que muestra la edad media por ocupación y provincia.

1. Sitúe el cursor en la primera fila, primera columna de la base de datos. Puede colocarlo en cualquier parte de la tabla, pero es habitual ponerlo en esta posición.

2. Active opción del menú Datos/ Asistente para tablas dinámicas. Se despliega la primera ventana del asistente donde se pregunta sobre el origen de la base de datos, deje activada la opción Lista o Base de datos de Microsoft Excel y pulse sobre Siguiente.

3. Ahora, el asistente pide que seleccione el rango que contiene los datos que se quieren utilizar. Si el rango ya aparece seleccionado no hará falta que lo seleccione, si aparece el texto Base de Datos, deberá seleccionarlo incluyendo la primera fila. Pulse sobre Siguiente.

4. Aparece una ventana con los nombres de los campos y la descripción de los elementos de la tabla.

5. Seleccione el campo Ocupación y arrástrelo hasta posición Fila de la tabla.

6. Seleccione campo Provincia y arrástrelo hasta posición Columna de la tabla.

7. Seleccione campo Edad y arrástrelo hasta posición Datos de la tabla.

8. Seguramente en Datos aparecerá Suma de Edad, para cambiar por Promedio de Edad, haga doble clic sobre Suma de Edad, se desplegará un cuadro con una lista de funciones, seleccione Promedio y pulse Aceptar para volver al asistente, pulse sobre Siguiente.

9. En este paso, tendrá que seleccionar la casilla donde quiera que se genere la tabla, seleccione Hoja de cálculo nueva. Pulse sobre Terminar.

 

El asistente habrá generado una tabla como la que se muestra a continuación.

Operaciones con una tabla dinámica.

Cuando se genera una tabla dinámica, Excel muestra una barra de herramientas para poder realizar diferentes operaciones con las datos obtenidos de la tabla. Realice los ejercicios siguientes para ver como funcionan las diferentes opciones. Si no aparece la barra de herramientas active opción de la barra de menús Ver/ Barras de Herramientas/ Tabla dinámica.

Cambiar la función. cambiaremos la función Promedio por la función Contar.

1. Seleccione la primera casilla de la tabla dinámica (A1).

2. Pulse sobre el botón .

3. Se abre un cuadro de diálogo. De la lista Resumir por, escoja elemento Contar y pulse sobre Aceptar. Observe que además de contar puede Suman, Multiplicar, obtener valores máximos y mínimos, etc.

** Observe que la tabla dinámica muestra cuantos elementos hay por Ocupación y Provincia.

A continuación veremos que % representa cada uno de ellos respecto a los elementos de la fila, esto es, que % hay de Abogados, Administrativos y Gerentes. Repita los pasos anteriores y en el tercer paso, pulse sobre el botón Opciones , despliegue la lista Mostrar datos como y seleccione elemento % de Fila, pulse sobre Aceptar. El Total general de las filas (40%, 20% y 40%) representa el % de elementos por provincia.

Pruebe con % de Columna para ver que % de elementos hay por provincia.

Pruebe con % del total para ver el % de elementos por Provincia y Ocupación, (que % de Abogados hay en Barcelona, Madrid,...., que % de Administrativos hay en Barcelona, Cádiz,...)

 

Ejemplo.

Cree una tabla dinámica como la anterior pero en lugar de Promedio de Edad, seleccione Suma de Ingreso. La tabla debe quedar como se muestra a continuación.

 

A continuación veremos la misma tabla pero en acumulados por Ocupación, por ejemplo, en Abogado Barcelona veremos 150000, en Administrativo Barcelona (150000+160000 = 310000) y en Gerente Barcelona (310000+100000 = 410000).

Seleccione la primera casilla de la tabla dinámica (A1).

2. Pulse sobre el botón .

3. Se abre un cuadro de diálogo. De la lista Resumir por, deje el elemento Sumar y pulse sobre Opciones.

4. En lista Mostrar datos como, seleccione Total en.

5. En lista Campo base, seleccione elemento Ocupación.

**Pruebe mostrar los datos acumulados por provincia.

 

Ejemplo.

Vuelva a dejar la tabla de modo que muestre la suma de los ingresos por Ocupación y Provincia. Observe que en la provincia que se recauda más dinero es la de Barcelona. A continuación vamos a mostrar la diferencia con respecto a Barcelona de las otras dos provincias, es decir, veremos cuanto se recauda menos (o más) de los Abogados, Administrativos y Gerentes de Madrid y Cádiz con respecto a los de Barcelona.

1. Seleccione la primera casilla de la tabla dinámica.

2. Pulse sobre el botón .

3. Se abre un cuadro de diálogo. De la lista Resumir por, deje el elemento Sumar y pulse sobre Opciones.

4. En lista Mostrar datos como, seleccione Diferencia de.

5. En lista Campo base, seleccione elemento Provincia, en Elemento base seleccione Barcelona.

Ejemplo de Subtotales.

Construya una tabla dinámica como la anterior pero intercambie filas y columnas, es decir, en fila ponga Provincia y en columna ponga Ocupación.

Incluir un nuevo elemento para las filas. Haremos que aparezcan las Ciudades al lado de cada Provincia.

1. Seleccione toda la columna de provincia. Haga clic sobre el rótulo que pone Provincia.

2. Pulse sobre el botón de la barra de tabla dinámica o haga clic con el botón secundario sobre el rótulo Provincia y seleccione Agrupar y Esquema y luego Mostrar Detalle.

3. Seleccione elemento, en este caso Ciudad. Pulse sobre Aceptar. La tabla que aparecerá será como la que se muestra a continuación.

** Si desea incluir más de una fila o columna de datos también puede hacerlo cuando se está construyendo la tabla a través del asistente. Solo debe incluir los campos que desee teniendo en cuenta poner el campo de mayor nivel de agrupación antes que el de menor nivel. Por ejemplo, si construye la presente tabla dinámica incluyendo los campos Provincia y Ciudad en filas, debe hacerlo en este orden para que se muestren los resultados convenientemente agrupados (las ciudades por provincia), si incluye ciudad y luego provincia es posible que los datos aparezcan mal agrupados.

Ocultar los totales por provincia.

1. Haga doble clic sobre el botón de campo correspondiente. Provincia en este caso. Se despliega el cuadro de diálogo Campo de la tabla dinámica.

2. En apartado Subtotales, seleccione el botón de radio Ninguno.

3. Pulse sobre Aceptar.

** Si desea volver a mostrarlos repita los pasos anteriores y seleccione Automático.

Mostrar la Suma y el promedio por provincias.

1. Haga doble clic sobre el botón de campo correspondiente. Provincia en este caso. Se despliega el cuadro de diálogo Campo de la tabla dinámica.

2. En apartado Subtotales, seleccione los elementos Suma y Promedio.

3. Pulse sobre Aceptar.

** Cuando aparecen tantos valores resulta conveniente dar formatos de colores a los diferentes resúmenes que aparecen en la tabla.

 

Ocultar un campo de una tabla dinámica. Ocultaremos el campo Ciudad.

1. Seleccione el campo a ocultar. En nuestro caso Ciudad.

2. Pulse sobre el botón de la barra de tabla dinámica o bien pulse el botón secundario y seleccione Agrupar y Esquema/ Ocultar detalle.

** Para volver a mostrar el detalle, debe seleccionar el campo Provincia y pulsar sobre el botón . Tenga en cuenta que si no está seleccionada la provincia y utiliza esta opción, se le preguntará de nuevo por que campo desea agrupar.

** Si sólo desea mostrar el detalle de un elemento, las ciudades de la provincia de Barcelona por ejemplo, seleccione únicamente este elemento y pulse sobre el botón

Ordenar los elementos de una fila o columna.

Ordenaremos los elementos del campo Ocupación en orden descendente.

1. Haga doble clic sobre el botón de campo. Ocupación en nuestro caso.

2. Pulse sobre el botón Avanzado...

3. En Opciones de Autoordenar, seleccione Descendente.

4. Pulse sobre Aceptar.

Mostrar el valor más alto de un campo (o los dos valores más altos, o los tres...)

Esta opción sirve para ver los N elementos mayores de una tabla. En este caso mostraremos cual es la ocupación que da más ingresos.

1. Haga doble clic sobre el botón de campo. Ocupación en nuestro caso.

2. Pulse sobre el botón Avanzado...

3. En Opciones de Automostrar, seleccione botón de radio Automáticamente. En lista Mostrar seleccione Superiores y en el cuadro de texto de al lado, ponga 1.

Mostrar los detalles de un elemento. (esto es mostrar que elementos conforman un resumen o grupo)

Como ejemplo, mostraremos los elementos que conforman los Administrativos de Barcelona.

Solo debe seleccionar el elemento donde cruza el elemento Administrativo con la Ciudad Barcelona y hacer doble clic, se creará y activará una nueva hoja donde verá los elementos que conforman este subtotal.

** Si desea que esto no ocurra, es decir que al hacer doble clic no se muestren detalles, haga lo siguiente: Haga clic con el botón secundario sobre cualquier elemento de la tabla, del menú contextual que se despliega, seleccione elemento Opciones... se activa el cuadro de diálogo Opciones de la tabla dinámica, en apartado Opciones de Datos desactive la casilla Habilitar Traspasar Detalles.

Utilizar un campo de página.

A veces, por comodidad a la hora de trabajar con datos de una tabla, resulta conveniente utilizar un campo de página. Un campo de página le permitirá cambiar los grupos de valores que se muestran en la tabla. En el siguiente ejemplo haremos lo mismo que en el anterior, es decir mostrar la suma de ingresos por Provincias, Ciudades y Ocupación, pero el campo Provincia lo utilizaremos como campo de página. En la tabla podrá ver los ingresos por Ciudad y Ocupación con la opción de ver solo las ciudades de una determinada Provincia que se seleccionará a través del campo de página.

1. Sitúe el cursor en la primera fila, primera columna de la base de datos. Puede colocarlo en cualquier parte de la tabla, pero es habitual ponerlo en esta posición.

2. Active opción del menú Datos/ Asistente para tablas dinámicas. Se despliega la primera ventana del asistente donde se pregunta sobre el origen de la base de datos, deje activada la opción Lista o Base de datos de Microsoft Excel y pulse sobre Siguiente.

3. Ahora, el asistente pide que seleccione el rango que contiene los datos que se quieren utilizar. Si el rango ya aparece seleccionado no hará falta que lo seleccione, en caso contrario selecciónelo. pulse sobre Siguiente.

4. Aparece una ventana con los nombres de los campos y la descripción de los elementos de la tabla.

5. Seleccione el campo Provincia y arrástrelo hasta posición Página.

6. Seleccione el campo Ciudad y arrástrelo hasta posición Fila de la tabla.

6. Seleccione campo Ocupación y arrástrelo hasta posición Columna de la tabla.

7. Seleccione campo Ingreso y arrástrelo hasta posición Datos de la tabla. Deje la operación Suma.

9. En este paso, tendrá que seleccionar la casilla donde quiera que se genere la tabla, seleccione Hoja de cálculo nueva. Pulse sobre Terminar

La tabla que se generará debe ser similar a la siguiente.

Observe que encima de la tabla aparece el campo Provincia como una lista desplegable, active esta lista y solo tendrá que seleccionar la provincia para que en la tabla aparezcan solo las ciudades de esta provincia.

Ocultar un elemento de la tabla.

Se ocultar cualquier elemento de una página, columna o fila. Como ejemplo, ocultaremos los totales para la provincia de Barcelona.

1. Haga doble clic sobre el campo de página Provincia. Se despliega el cuadro de diálogo Campo de la tabla dinámica.

2. En lista de elementos Ocultar elementos, seleccione el que desea ocultar. Barcelona en nuestro caso.

3. Pulse sobre el botón Aceptar.

**Ahora al desplegar la lista, observe que no aparece el elemento Barcelona.

** Observe que cuando selecciona Todas, no aparecen las ciudades correspondientes a Barcelona. Si desea que al seleccionar Todas aparezcan todos los elementos (todas las ciudades en este caso), haga clic con el botón secundario sobre la tabla y seleccione del menú contextual elemento Opciones..., se despliega el cuadro de diálogo Opciones de tabla dinámica, en apartado Opciones de formato, active la opción Subtotales de elementos ocultos de página.

** Para volver a mostrar un campo solo debe repetir los mismos pasos que al ocultarlo pero ahora quitando la selección.

Ejemplo de grupos.

Cree la siguiente tabla dinámica.

En Filas, seleccione campo Edad.

En Columnas, seleccione campo Ocupación.

En Datos, seleccione Nombre y función Contar.

La tabla que acabamos de construir muestra cuantos individuo hay por Edad y Ocupación, a continuación agruparemos una serie de valores de Edad de forma que veamos cuantos individuos hay por ocupación en la franja que va de los 30 a los 40 años, etc. La fila de edades debe aparecer ordenada de menor a mayor, si no aparece ordenada, haga clic con el botón secundario sobre cualquier elemento de la fila edad, seleccione del menú contextual el elemento Campo, aparece el cuadro de diálogo Campo de la tabla dinámica pulse sobre el botón Avanzado... en el cuadro de diálogo Opciones avanzadas de campo de tabla dinámica seleccione Ascendente en Opciones de Autoordenar, pulse sobre Aceptar.

 

Crear un grupo.

1. Seleccione las filas que van desde el valor 32 al valor 39.

2. Pulse sobre el botón o bien haga clic con el botón secundario, seleccione opción Agrupar y Esquema/ Agrupar.

Observe que se ha creado el elemento Grupo1, si desea ver los totales de este grupo. Haga clic con el botón secundario sobre Grupo1, seleccione opción Campo y en Subtotales, seleccione Automáticos. Observe que aparece la fila Total Grupo1.

** Puede crear los grupos que desee siguiendo los pasos anteriores.

Quitar un grupo.

1. Seleccione el elemento del grupo. En nuestro caso, seleccione elemento Grupo1.

2. Pulse sobre el botón o bien haga clic con el botón secundario sobre el elemento Grupo1 y del menú contextual, seleccione opción Agrupar y Esquema/ Desagrupar.

Grupos según rangos de valores.

Aunque muchas veces no nos quedará más remedio que agrupar como en el ejercicio anterior, en muchas ocasiones, sobre todo si los elementos de una fila o columna son datos numéricos y tipo fecha, Excel posibilitará crear grupos según rango de valores. A continuación crearemos tres grupos que comprenderán respectivamente las franjas de edades 30-40, 40-50 y 50 60.

1. Seleccione un único elemento de la fila (sólo uno).

2. Pulse sobre el botón o bien haga clic con el botón secundario sobre el elemento de fila seleccionado, y en el menú contextual, seleccione opción Agrupar y Esquema/ Agrupar, aparece el siguiente cuadro de diálogo.

3. Rellénelo de la forma siguiente. Comenzar en 30, Terminar en 60 Por 10, pulse sobre Aceptar.

Observe que los datos de tabla se ha agrupado en tres elementos de fila correspondientes a las tres franjas.

**Si desea desagrupar sólo debe seleccionar cualquier elemento de fila y pulsar sobre el botón o bien clic con el botón secundario y del menú contextual, selecciona Agrupar y Esquema/ Desagrupar.

** También pueden crearse agrupaciones por los elementos de las columnas.

Agrupaciones con fechas.

Cree la siguiente tabla dinámica.

En Filas ponga Fecha.

En Columnas ponga Ciudad.

En Datos ponga Ingreso y función Suma.

La tabla muestra los ingresos por Fecha y Ciudad. A continuación vamos a ver los ingresos por Trimestre y Ciudad.

1. Seleccione cualquier elemento de la fila Fechas.

2. Pulse sobre el botón

3. Se despliega el siguiente cuadro de diálogo. Seleccione elemento Trimestre y Aceptar.

Crear subgrupos por mes.

1. Seleccione cualquier elemento de las Filas (Cualquier Trimestre).

2. Pulse sobre el botón

3. Seleccione elemento Meses (tenga cuidado que también quede seleccionado el elemento Trimestre). Pulse Aceptar.

Si desea ver los totales por trimestre, seleccione cualquiera de ellos, haga clic con el botón secundario, del menú contextual, seleccione elemento Campo... y en el cuadro de diálogo Campo de tabla dinámica, seleccione botón de radio Automáticos de la sección Subtotales. Pulse sobre Aceptar.

Campos Calculados.

Un campo calculado es un campo que se construye a partir de los valores de los campos de una tabla dinámica. Sirven para realizar y mostrar cálculos mediante los valores de los campos de una tabla dinámica.

Cree la siguiente tabla dinámica.

En Filas seleccione Fecha.

En Columnas seleccione Provincia.

En Datos seleccione Ingreso con función Suma.

Cuando tenga la tabla creada, agrupe los elementos de las filas por mes. Ahora tenemos el total que se va a cobrar por mes y provincia durante este año. Supongamos que para el año que viene deseamos incrementar las cuotas un 5%, a continuación incluiremos un campo calculado sobre la tabla que acabamos de crear que nos muestre cuanto se cobrará por Provincia y Mes el próximo año.

1. Seleccione cualquier elemento de la Fila (los meses).

2. Haga clic con el botón secundario sobre el elemento seleccionado. Del menú contextual que se despliega, seleccione elemento Fórmulas/ Campo Calculado. Se abre el siguiente cuadro de diálogo.

3. En Nombre ponga Previsión, será el nombre para el nuevo campo calculado.

4. El apartado Fórmula sirve para poner la expresión que se desea calcular. Ponga la siguiente  =Ingreso + Ingreso * 5%.

5. Pulse sobre el botón Aceptar.

Observe que debajo de cada Suma de Incremento aparece un nuevo campo llamado Suma de previsión que muestra el ingreso incrementado en un 5%.

** Habitualmente a los campos calculados se les aplica un formato para destacarlos. Seleccione cualquier elemento Suma de Previsión, observe como se seleccionan todos, aplique (por ejemplo), Formato/ Celdas/ Trama y color rojo.

Elementos calculados.

Un elemento calculado es un nuevo elemento para un campo (de fila o columna) que se construye a partir de una expresión que incluye valores (elementos)  de un campo. Observe la diferencia de un campo calculado y un elemento calculado.

Un campo calculado siempre dará como salida tantas filas o columnas como elementos haya en el campo, es decir la expresión se aplica a todos los elementos del campo.

Un elemento calculado siempre dará una única fila o columna que será el resultado de operar elementos de un campo.

Construya la siguiente tabla dinámica.

En Filas seleccione Ocupación.

En Columnas seleccione Provincia.

En Datos seleccione Ingreso con función Suma.

La tabla muestra lo que se ingresará por profesión y provincia. Supongamos que ahora queremos cuanto se ingresará el próximo año teniendo en cuenta que los incrementos de las cuotas serán diferentes para cada provincia. Así a los de Cádiz, se les aplicará un incremento del 3%, a los de Madrid un 4% y a los de Barcelona un 5%.

1. Seleccione cualquier elemento del campo Provincia (Barcelona, Cádiz o Madrid).

2. Haga clic con el botón secundario y del menú contextual, seleccione elemento Fórmulas/ Elemento calculado. Se despliega el cuadro de diálogo Insertar Elemento Calculado en "Provincia".

3. En Nombre ponga Incremento Barcelona.

4. En Fórmula ponga =Barcelona+Barcelona*5%

5. Pulse sobre Aceptar.

Observe que se ha incluido una nueva columna con el calculo indicado.

Repita los pasos anteriores y cree los incrementos correspondientes a Cádiz (3%) y Madrid (4%)

Observe que en la columna Total General Aparecen Sumados los valores de todas las columnas, incluyendo los elementos calculados. A continuación eliminaremos esta columna e insertaremos dos de nuevas, una que calcule el total para el año actual y otra que calcule el total para la previsión.

Ocultar la columna de Total General. No mostrar la columna Total General.

1. Haga clic con el botón secundario sobre cualquier elemento de la tabla.

2. Del menú contextual, seleccione elemento Opciones.Se despliega el cuadro de diálogo Opciones de la tabla dinámica.

3.  En apartado Opciones de formato, desactive la casilla Totales generales de Filas.

4. Pulse sobre Aceptar.

• Incluya un nuevo elemento calculado llamado Total Actual cuya fórmula será
  
  =Barcelona+Cádiz+Madrid.
  
• Incluya un nuevo elemento calculado llamado Total Previsión cuya fórmula será
  
  ='Previsión Barcelona'+'Previsión Cádiz'+'Previsión Madrid'.
  
  Note que debe encerrar los nombres entre comillas simples ya que estos son compuestos, de hecho, si en lugar de escribir utiliza el botón Insertar Elemento ya aparecerán los elementos entre comilla simple.

Construir una tabla dinámica a partir de datos ubicados en otro libro de trabajo.

Si ha utilizado las opciones de Datos/ Obtener Datos Externos, verá que los pasos son similares.

En un libro nuevo construiremos una tabla dinámica que nos muestre la media de Edad por Ciudad y Ocupación del libro Datos.xls

Primero que nada abra el libro de trabajo Datos.xls, y póngale el nombre Tabla_Datos al rango de datos

1. Seleccione el rango (toda la tabla incluida la primera fila de nombres de columna).

2. Active Insertar/ Nombre/ Definir. Se despliega el cuadro de diálogo Definir Nombre.

3. Ponga Tabla_Datos en cuadro de texto Nombres en el Libro y pulse Aceptar.

Este no es un paso necesario pero nos será útil a la hora de indicar al asistente sobre que rango deseamos crear la tabla dinámica.

Ahora guarde el libro de trabajo Datos.xls, ciérrelo y abra un nuevo libro de trabajo.

En este nuevo libro construiremos la tabla dinámica que nos muestre la edad media por ocupación y ciudad. Siga los pasos siguientes.

1. Active opción Datos/ Asistente para Tablas Dinámicas.

2. En ¿Donde están los datos que desea analizar?, seleccione Fuente de datos Externa, pulse sobre Siguiente.

3. Aparece un cuadro preguntando ¿Donde están guardados los datos externos?, pulse sobre el botón Obtener Datos... se despliega el cuadro de diálogo Elegir fuente de datos (si ha trabajado con algún otro mecanismo de obtener datos externos, observe que realmente se ha activado la aplicación Query).

4. De la ficha Base de Datos, seleccione <Nueva fuente de datos> y pulse sobre el botón Aceptar. Se despliega el cuadro de diálogo Crear Nueva fuente de datos, este cuadro sirve para definir la procedencia de datos externos a la aplicación, documento o archivo sobre el que se está trabajando. En ¿Que Nombre desea dar a la fuente de datos? ponga Datos_De_Libro_Datos, sirve para identificar la fuente, posteriormente una fuente de datos definida podrá utilizarse desde otros libros de trabajo, e incluso desde otras aplicaciones. En lista Seleccione un controlador para el tipo de base de datos a la cual desea tener acceso, seleccione elemento Microsoft Excel Driver(*.xls) ya que los datos los recogeremos de un libro de trabajo de Excel, observe que la fuente de datos puede ser cualquier aplicación que se muestra en la lista (Tabla de Access, archivo de texto, Tabla de Sql Server, etc.). Pulse sobre el botón Conectar, se abre el cuadro de diálogo Instalación de ODBC de Microsoft Excel, para los que sepan lo que es el ODBC, ya habrán intuido hace rato que lo que estamos haciendo es crear una DSN, para los que no sepan que es una DSN, indicarles brevemente que es un mecanismo que permite acceder a diferentes fuentes de datos desde diferentes aplicaciones.

5. Pulse sobre el botón Seleccionar Libro.Se activa un nuevo cuadro de diálogo donde deberá buscar la unidad carpeta y archivo donde están los datos. En nuestro caso debe buscar y seleccionar Datos.xls. Cuando lo tenga seleccionado, pulse sobre el botón Aceptar. Vuelve a estar en el cuadro de diálogo Instalación de ODBC de Microsoft Excel, pulse sobre Aceptar para cerrarlo.

6. Ahora vuelve a estar cuadro Crear Nueva Fuente de Datos. En lista Seleccione una tabla predeterminada para la fuente de datos (Opcional), seleccione Tabla_Datos, observe que es el nombre dado al rango de datos del libro Datos.xls, si no hubiéramos dado nombre al rango, entonces lo deberíamos especificar con la notación de rango típica (Celda_Inicial:Celda_Final) que rango contiene los datos. Pulse sobre Aceptar para volver a Elegir Fuente de datos.

7. Con el elemento Datos_De_Libro_Datos seleccionado, pulse sobre Aceptar. Se activa el cuadro Asistente para Consultas con la lista de campos (Columnas) del rango Tabla_Datos.

8. Seleccione los elementos Ciudad, Ocupación y Edad de la lista Tablas y Columnas disponibles y páselos a la lista Columnas en la consulta. Observe que este paso sirve para seleccionar los campos cuyos valores nos interesan importar.Pulse sobre el botón Siguiente, se despliega Asistente para consultas- Filtrar datos, este paso sirve por si sólo se desea importar los datos que cumplen determinados criterios, por ejemplo, si deseáramos importar los mayores de 40 años, seleccionaríamos el campo Edad de la lista Columnas para filtrar en la primera lista desplegable escogeríamos Mayor igual que y en el cuadro de texto de al lado escribiríamos 40. Pulse sobre Siguiente sin hacer nada de eso. Se activa el cuadro Asistente para consultas - Orden de resolución, este paso sirve para indicar si al importar los datos estos deben aparecer ordenados por un campo, no ordenaremos, pulse sobre Siguiente. Aparece el cuadro Asistente para consulta-Terminar, en apartado ¿Qué desea hacer a continuación?, seleccione Devolver Datos a Microsoft Excel y pulse sobre Finalizar. Vuelve a mostrarse el asistente para tablas dinámicas. Pulse sobre Siguiente y los pasos que viene a continuación ya los conocemos.

9. Ponga en Fila el campo Ciudad en Columna el campo Ocupación y en Datos el campo Edad con la operación Promedio. Pulse sobre Siguiente.

10. Escoja la ubicación de la tabla y pulse sobre Terminar.

** Observe que los pasos del 4 (Elegir nueva fuente de datos) al 6 donde se seleccionan los datos a importar son pasos para definir de donde proceden los datos y con que nombre se guardará esta fuente de datos (Datos_De_Libro_Datos). A partir de ahora no será necesario volverlos a definir, siga los pasos del ejemplo siguiente.

Crear una tabla dinámica que muestre el total de Ingresos por Provincia y Ocupación. Solo le describiremos los pasos hasta conseguir los datos externos, observe como cambia el paso 4, ahora, como ya tenemos definido el origen de datos (Datos_De_Libro_Datos), solo se deberá seleccionar este elemento y continuar como en el ejemplo anterior pero a partir del paso 8 (escogiendo los campos convenientes para esta nueva tabla).

1. Active opción Datos/ Asistente para Tablas Dinámicas.

2. En ¿Donde están los datos que desea analizar?, seleccione Fuente de datos Externa, pulse sobre Siguiente.

3. Aparece un cuadro preguntando ¿Donde están guardados los datos externos?, pulse sobre el botón Obtener Datos... se despliega el cuadro de diálogo Elegir fuente de datos

4. Seleccione elemento Datos_De_Libro_Datos y pulse sobre Aceptar. Observe que seleccionamos el origen de datos que creamos en el ejemplo anterior, recuerde que le dijimos que este origen lo podrá utilizar a partir de ahora en cualquier libro de trabajo o aplicación que soporte DNS.

5.... Continúe usted mismo hasta el final.

Tablas dinámicas con rangos de consolidación múltiples.

Con Miscrosoft Excel es posible construir tablas dinámicas con diferentes rangos de datos. Hasta ahora hemos confeccionado tablas dinámicas cogiendo un único rango, ahora exploraremos esta capacidad de Excel construyendo una tabla dinámica con más de un rango de datos de entrada, obviamente, para que la tabla dinámica tenga sentido los datos deberán guardar alguna relación entre si.

Active el libro de trabajo Datos2.xls será el que usemos para ver los ejemplos. Verá que en el libro hay cuatro hojas correspondientes a las ventas de Cartón y Papel durante los meses de Enero y Febrero. La primera tabla dinámica calculará las ventas que corresponden a cada comercial (observe que son los datos datos de la primera columna de cada rango), la tabla mostrará los totales de los Importes de las ventas, las cantidades pagadas y las pendientes de las cuatro hojas.

1. Active opción Datos/ Asistente para tablas dinámicas.

2. Seleccione Rangos de consolidación múltiples y pulse sobre Siguiente.

3. En este paso, el asistente pregunta por los campos de página, se puede optar por un campo de página o bien por crear campos personalizados. En este ejemplo optaremos por la opción Crear un solo campo de página, los elementos del campo de página corresponderán a los rangos de las cuatro hojas. Pulse sobre botón Siguiente.

4. En este paso deberemos especificar los rangos que se desean consolidar en la tabla dinámica.

4.1 Active hoja Cartón Enero, seleccione el rango de datos de esta hoja y pulse sobre Agregar.

4.2 Repita el paso 4.1 para las tres hojas restantes, Papel Enero, Cartón Febrero, Papel Febrero.

4.3 Pulse el botón Siguiente cuando haya terminado de agregar todos los rangos.

5. Aparece la pantalla para el diseño de la tabla dinámica. Cambie la opción Contar de valor por Suma, recuerde, debe hacer doble clic sobre el botón Contar de valor y seleccionar Suma. Pulse sobre botón Siguiente.

6. En este paso debe seleccionar donde desea crear la tabla dinámica. Seleccione opción Hoja de cálculo existente, seleccione la celda A5 de la Hoja Resúmenes y pulse sobre Terminar.

Observe la distribución de la tabla.

Elementos de Filas, son los elementos de la primera columna de cada rango de consolidación, en este caso los comerciales A, B, C y D.

Elementos de Columnas. Son todos los campos de los rangos de consolidación, excepto el primero (que aparece en elementos de filas), con los valores sumados (Observe que en el campo Cliente aparecen ceros ya que no es un campo con valores numéricos).

El campo de página sirve para ver los datos totalizados por hoja (si escoge Elemento1 verá los totales de la primera hoja, Elemento2 los de la segunda, ...) o bien la suma de todos los rangos de consolidación.

Ejemplo de campos de página personalizados.

En este segundo ejemplo, definiremos nosotros los campos de página de forma que en la tabla que se construya sea posible a través de ellos ver los totales por producto (los totales de Cartón y los totales Papel) y los totales por mes (Los totales de Enero y los totales de Febrero).

1. Active opción Datos/ Asistente para tablas dinámicas.

2. Seleccione Rangos de consolidación múltiples y pulse sobre Siguiente.

3. En este paso, el asistente pregunta por los campos de página, seleccione Campos de página personalizados y pulse sobre Siguiente.

4. Seleccione los rangos correspondientes de las cuatro hojas y agréguelos a la lista Todos los rangos.

4.1 En Apartado Cuantos campos de página desea, seleccione 2, uno corresponderá a Papel y Cartón y el otro a Enero y Febrero.

4.2 En ¿Que rótulos de elemento desea que use cada campo de página para identificar el rango de datos seleccionado?, siga los pasos siguientes

4.2.1 Seleccione elemento Cartón Enero$A1.. de lista Todos los Rangos, vaya a Campo1 y escriba Cartón, en Campo2 escriba Enero.

4.2.2 Seleccione elemento Cartón Febrero... en lista Todos los Rangos, vaya a Campo1 y escriba Cartón, en Campo2 escriba Febrero.

4.2.3 Seleccione elemento Papel Enero.. en lista Todos los Rangos, vaya a Campo1 y escriba Papel, en Campo2 escriba Enero.

4.2.4 Seleccione elemento Papel Febrero... en lista Todos los Rangos, vaya a Campo1 y escriba Papel, en Campo2 escriba Febrero.

4.3 Pulse sobre botón Siguiente.

5. Se activa la ventana para el diseño de la tabla, en apartado Datos, cambie Contar de Valor por Suma de valor. Pulse sobre Siguiente.

6. En este paso debe seleccionar donde desea crear la tabla dinámica. Seleccione opción Hoja de cálculo existente, seleccione la celda A20 de la Hoja Resúmenes y pulse sobre Terminar.

Observe que aparecen dos campos de página, ahora la tabla muestra los totales de los cuatro rangos.

Si desea ver los totales de Cartón o Papel, haga clic sobre la lista Página1 y seleccione el elemento. En la segunda lista debe estar seleccionado Totales

Si desea ver los totales de Enero o Febrero, haga clic sobre la lista Página2 y seleccione el elemento. En la primera lista debe estar seleccionado Totales.

Si desea ver los totales de Papel en Enero, seleccione de la primera Lista Papel, y de la segunda Enero.

.............

Y para terminar este recorrido por las opciones de las tablas dinámicas, pruebe de hacer usted mismo las dos tablas anteriores pero en lugar de resumir por comerciales, resuma por clientes. Solo debe seguir los pasos pero al seleccionar los rangos, hágalo por la segunda columna, es decir por la de Cliente.

FIN.