tema 6 exav tablas dinamicas

Índice

TABLAS DINÁMICAS I ............................................................................................................................ 1

1. ELEMENTOS DE UNA TABLA DINÁMICA ............................................................................................... 1 2. CREAR TABLA DINÁMICA DE LISTA DE EXCEL ..................................................................................... 2 3. HABILITAR EL ASISTENTE DE TABLAS DINÁMICAS................................................................................ 7 4. CREAR UNA TABLA DINÁMICA DE RANGOS DE CONSOLIDACIÓN MÚLTIPLES .......................................... 8

Tema 6: Tablas dinámicas I

C/ Miguel de Unamuno, 1_47008_Valladolid

Tel. 983249455 / 625569162_Fax 983243547

[email protected]_www.ticsyformacion.com

Plaza Iturrilun nº5 bajo_20800 Zarautz_Gipuzkoa

Tel. 943830230_Fax 943830233

[email protected]_www.lombokdesign.com

1

Tablas dinámicas I En este capítulo se describe cómo crear y modificar informes de tabla dinámica.

Un informe de tabla dinámica es una tabla interactiva que resume o ejecuta una comprobación

cruzada de grandes volúmenes de datos. Su nombre se debe a que los rótulos de fila y de

columna pueden cambiar su posición. También admite filtros.

Para crear uno de estos informes es preciso tener unos datos que resumir. Para las siguientes

explicaciones tomaremos como referencia una lista de ingresos en un periodo de tiempo,

conformada por columnas (campos), cada uno de los conceptos y por filas (registros).

1. Elementos de una tabla dinámica Filtro de Informe Rótulos de columna

Rótulos de fila Valores

Filtro de informe. Se toma un campo de la lista que permitirá filtrar los datos resumidos por el

elemento seleccionado en ese campo. En el caso de utilizar la LOCALIDAD el resultado del

informe no será el mismo si realiza los cálculos con todos los registros o con los de una

determinada localidad.

Rótulos de fila. Se toma un campo de la lista en el que se hacen grupos de forma automática,

de manera que cada grupo resultante será un rótulo para cada fila de la tabla. Si utiliza el campo

SERVICIO mostrará cada uno de los servicios en filas consecutivas, sin repetir ninguno.



Tel. 983249455 / 625569162_Fax 983243547



Tel. 943830230_Fax 943830233


2

Rótulos de columna. Se toma un campo de la lista en el que se hacen grupos de forma

automática, de manera que cada grupo resultante será un rótulo para cada columna de la tabla.

Si utiliza el campo SEXO mostrará M y V como rótulos de dos columnas consecutivas.

Valores, muestra el resultado de la función de cálculo solicitada sobre los datos de un campo de

la tabla que corresponden a los registros resultados de filtrar por cada cruce de fila y columna.

Las funciones que podrá seleccionar son las siguientes:

Suma Suma de valores.

Contar El número de elementos. Funciona como la función CONTARA de la hoja de cálculo.

Promedio Media aritmética de los valores

Máx Valor máximo

Mín Valor mínimo

Producto Producto de los valores

Contar núm El número de filas que tienen datos numéricos. Funciona como la función CONTAR de

la hoja de cálculo.

Desvest Estimación de la desviación estándar de una población, donde la muestra son todos los

datos que van a resumirse.

Desvestp Estimación de la desviación estándar de una población, donde la muestra son todos los

datos que van a resumirse y éstos son el total de la población.

Var Estimación de la varianza de una población, donde la muestra son todos los datos que

van a resumirse.

Varp Varianza de una población, donde la muestra son todos los datos que van a resumirse

y estos son el total de la población.

2. Crear tabla dinámica de lista de Excel

Aunque no es estrictamente necesario, resulta más cómodo si sitúa el cursor en una celda de la

lista que desea resumir. Seleccione Tabla dinámica en el comando Tabla dinámica de la ficha

Insertar.

Mostrará en pantalla el cuadro de diálogo, donde deberá indicar la procedencia de los datos a

resumir y analizar. Como Microsoft Excel utiliza los datos de la primera fila de la lista para los

nombres del campo, esa lista deberá contener rótulos de columna.



Tel. 983249455 / 625569162_Fax 983243547



Tel. 943830230_Fax 943830233


3

Indique dónde desea colocar la tabla dinámica. (Se recomienda en hoja nueva).

Le llevará a una hoja nueva donde le mostrará la lista de campos a la derecha, con diferentes

paneles que le ayudarán a diseñarla.

Arrastre cada uno de los campos señalados al panel que se indica en la imagen:

Por omisión, suma el campo que arrastra hasta el panel Valores, excepto si el campo es texto,

que lo cuenta. Opera con los valores de ese campo para cada cruce de fila y columna o lo que

es lo mismo con los registros que resultan de aplicar el filtro del valor de la fila y el filtro del valor

de la columna



Tel. 983249455 / 625569162_Fax 983243547



Tel. 943830230_Fax 943830233


4

Para cambiar el cálculo y el rótulo haga clic en el desplegable que presenta ese campo en el

área de Valores y seleccione Configuración de campo de valor.

Primero debe seleccionar el tipo de cálculo y después puede personalizar el nombre.

El resultado será el siguiente:



Tel. 983249455 / 625569162_Fax 983243547



Tel. 943830230_Fax 943830233


5

Al pasar el campo SERVICIO a Rótulos de fila se han agrupado sus datos y ha utilizado cada

grupo para rotular una fila. Al pasar el campo SEXO a Rótulos de columna se han agrupado sus

datos y ha utilizado cada grupo para rotular una columna.

Con los valores solicitados, en cada cruce de columna y fila, por ejemplo M en columna y GIN en

fila, cuenta el nº de registros que obtendría al realizar ese filtro: Sexo = M y Servicio = GIN.

En el ejemplo utilizado puede contar pero en lugar de responder con el número de registros

puede responder con un porcentaje de ese número sobre el total, o sobre el número de registros

correspondientes al filtro de fila, o sobre el número de registros correspondientes al filtro de

columna, …



Tel. 983249455 / 625569162_Fax 983243547



Tel. 943830230_Fax 943830233


6

Para ello muestre el cuadro de diálogo Configuración de campo valor y haga clic en la pestaña

Mostrar valores como, le mostrará un cuadro para elegir el modo de realizar el cálculo:

Diferencia de Muestra todos los datos en el área de datos como la diferencia entre el valor

del Campo base y el Elemento base especificados.

% de Muestra todos los datos del área de datos como un porcentaje del valor del

Campo base y del elemento base especificados.

% de la diferencia de Presenta todos los datos en el área de datos como la diferencia entre el valor

del Campo base y del Elemento base, como un porcentaje de los datos base.

Total en Presenta los datos de elementos sucesivos como un total actual.

% de la fila Presenta los datos en cada fila como un porcentaje del total de cada fila.

% de la columna Presenta los datos en cada columna como un porcentaje del total de cada

columna.

% del total Presenta los datos en el área de datos como un porcentaje del total general

de todos los datos de la tabla dinámica.

Índice Presenta los datos utilizando los siguientes cálculos: ((valor en celda) x (Suma

total de sumas totales)) / ((Suma total de fila) x (Suma total de columna)

Veamos diferentes ejemplos:

El nº de registros se muestra como porcentaje sobre el nº de registros en el servicio de NEU



Tel. 983249455 / 625569162_Fax 983243547



Tel. 943830230_Fax 943830233


7

El nº de registros se muestra como acumulado en Servicio (en las filas)

3. Habilitar el asistente de tablas dinámicas

El asistente de tablas dinámicas facilita la creación de tablas dinámicas. En la versión 2007 se

puede habilitar mostrándolo en la barra de herramientas de acceso rápido. Para ello utilice el

desplegable de la misma y seleccione Más comandos

En el cuadro de diálogo que muestra seleccione la categoría Personalizar en el panel izquierdo.

Seleccione Todos los comandos en Comandos disponibles en



Tel. 983249455 / 625569162_Fax 983243547



Tel. 943830230_Fax 943830233


8

En la lista que muestra haga clic en Asistente para tablas y gráficos dinámicos y luego haga clic

en Agregar.

El resultado es el siguiente

4. Crear una tabla dinámica de rangos de consolidación múltiples

Esta opción del asistente se elige cuando los datos de origen se encuentran en diferentes hojas

de cálculo, que tienen en común su estructura o bien los rangos se encuentran en la misma hoja

en zonas diferentes.

Los rangos pueden ser de dimensión diferente, es decir, pueden tener diferente número de filas

y/o diferente número de columnas, aunque los rótulos de las filas y/o columnas comunes deben

ser idénticos.



Tel. 983249455 / 625569162_Fax 983243547



Tel. 943830230_Fax 943830233


9

En el caso de una empresa de turismo con dos posadas rurales, de manera que anotan para

cada una de ellas y por año, los ingresos por conceptos diferentes mes a mes en hojas como las

siguientes:

Datos de la Posada1 en el año 2012




Utilice el asistente de tablas dinámicas que encontrará ahora en la barra de herramientas de

acceso rápido.



Tel. 983249455 / 625569162_Fax 983243547



Tel. 943830230_Fax 943830233


10

En el cuadro de diálogo que muestra marque la opción Rangos de consolidación múltiple y pulse

el botón Siguiente.

En el paso 2 marque la opción Campos de página personalizados y pulse el botón Siguiente

En el paso 3 seleccione el rango de celdas A1:M4 de la hoja Posada1-2012 pulse el botón

Agregar y marque la opción 2 campos. Escriba para cada campo los valores que corresponden a

esa hoja, en Campo uno: Posada 1 y en campo dos: 2012, tal como muestra la imagen.



Tel. 983249455 / 625569162_Fax 983243547



Tel. 943830230_Fax 943830233


11

Sin abandonar el cuadro de diálogo vuelva a Rango para seleccionar el rango de la hoja

Posada2-2012, indicando para este, Campo uno: Posada2 y para campo dos: 2012



Tel. 983249455 / 625569162_Fax 983243547



Tel. 943830230_Fax 943830233


12

Vuelva a Rango para seleccionar el rango de la hoja Posada1-2013, indicando para este, Campo

uno: Posada1 y para campo dos: 2013 y por último el rango de la hoja Posada2-2013, indicando

para este, Campo uno: Posada2 y para campo dos: 2013

Tras pulsar el botón Siguiente, le muestra el cuadro de diálogo para indicar donde desea situar la

tabla dinámica. Si ya tenía el cursor en una hoja nueva, marque la opción Hoja de cálculo

existente, si el cursor se encontraba en la hoja con los datos marque Hoja de cálculo nueva



Tel. 983249455 / 625569162_Fax 983243547



Tel. 943830230_Fax 943830233


13

El resultado es el siguiente:

Puede cambiar los rótulos que se muestran marcados, para ello sitúe el cursor en la celda y

escriba el texto que desee y que describa mejor ese elemento.

Por defecto realiza la suma de los valores. Como ya se ha visto es posible cambiar la función de

cálculo en el cuadro de diálogo Configuración del campo. En ese cuadro de diálogo también

podrá cambiar el formato de los valores para que su lectura sea más fácil.

Haga clic en el botón Formato de número y mostrará el cuadro de diálogo Formato de celdas



Tel. 983249455 / 625569162_Fax 983243547



Tel. 943830230_Fax 943830233


14

Seleccione el formato que desee y pulse Aceptar.

La tabla dinámica se verá del modo siguiente:

En el campo de página Posada podrá filtrar por cualquiera de las dos posadas o tomar todas. En

el campo de página Año podrá filtrar y tomar el año 2012 o el 2013 o ambos años.

tema 6 exav tablas dinamicas

Education

que los rtulos

que es

tabla que corresponden

los registros que resultan

lista en

crear una tabla dinmica

lista para los nombres

una poblacin