tabla dinamica

Sistemas de Informacin I Lic. Mara Josefina Castilla

1

Tabla dinmica

Una tabla dinmica consiste en el resumen de un conjunto de datos, atendiendo a uno o varios criterios de agrupacin, representado como una tabla de doble entrada que nos facilita la interpretacin de dichos datos. Es dinmica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentacin de los datos, etc.

Vamos a crear una tabla dinmica a partir de un conjunto de datos.

Sea la siguiente tabla de Excel

Cuando se crea una tabla dinmica, cada columna de los datos de origen se convierte en un campo que se puede utilizar en la tabla dinmica. Los campos resumen mltiples filas de informacin de los datos de origen. Los nombres de los campos se toman de los ttulos de las columnas.

En la tabla anterior, los ttulos de columnas Mes, Tipo, Vendedor, Regin, Unidades y Ventas se convertirn en nombres de campos.

En la tabla dinmica, se pueden diferenciar dos tipos de campos de la lista o de la tabla fuente:

Campos de organizacin de filas, columnas y pginas (filtro de informe) en la tabla dinmica. Estos campos generalmente contienen un conjunto limitado de valores de texto, como por ejemplo el campo Regin contiene las subcategoras Norte, Sur, Este y Oeste.


2

Campos de datos, que contienen los datos que se desean resumir. Estos campos generalmente contienen datos numricos, como por ejemplo los campos cantidades de ventas, totales de inventarios o datos estadsticos, pero tambin pueden contener texto.

Para crear una tabla dinmica, vamos a la ficha Insertar y hacemos clic en Tabla dinmica (tambin podemos desplegar el men haciendo clic en la flecha al pie del botn y seleccionar Tabla dinmica).

Aparece el cuadro de dilogo de creacin de tablas dinmicas. Desde aqu podremos indicar el lugar donde se encuentran los datos que queremos resumir y el lugar donde queremos ubicarla.

Seleccione los datos que desea analizar o Seleccione una tabla o rango

Es conveniente ubicar el indicador de celdas dentro de la tabla de datos antes de dar el comando para que el rango de celdas de la tabla figure en el cuadro Tabla o rango.

o Utilice una fuente de datos externa: Permite elegir la conexin a una base de datos externa a Excel para utilizarla como origen de la tabla dinmica

Elija dnde desea colocar el informe de tabla dinmica

En este cuadro de dilogo tambin debemos indicar la ubicacin de la tabla dinmica que puede ser: o Nueva hoja de clculo: Se insertar una nueva hoja en el libro que contendr la tabla dinmica. o Hoja de clculo existente: En este caso se deber indicar la direccin de la celda a partir de la

cual se insertar la tabla dinmica. Esta celda ser la superior izquierda del rango donde se


3

insertar la tabla dinmica y puede ser una celda de la hoja activa, de otra hoja del libro o de otro libro abierto.

En nuestro caso indicamos que vamos a seleccionar los datos de un rango de celdas y que queremos ubicarla en una hoja de clculo nueva. Al pulsar Aceptar se mostrar:

1. Una nueva cinta de opciones denominada Herramienta de tabla dinmica

2. Un panel llamado Lista de campos de tabla dinmica. El panel est dividido en dos reas. Una superior donde se ubican los campos de la tabla de origen. Una inferior subdividida en cuatro rectngulos denominados "Filtro de informe", "Rtulos de columna", "Rtulos de fila" y "Valores". A estas reas iremos arrastrando los campos de la tabla para personalizarla. Los nombres de campos en estas reas, se mostrarn en forma de botones pudiendo desplegar desde ellos un submen.

Construyamos una tabla dinmica con la siguiente estructura:

Arrastremos el campo Regin a , el campo Tipos a y finalmente el campo Ventas a la seccin .

Se originar una fila por cada una de las Regiones distintas y una columna por cada uno de los Productos distintos.


4

Suma de Ventas Rtulos de columnaRtulos de fila Carne Productos lcteos Verduras Total generalEste 101100 16500 117600Norte 720000 87000 1625 808625Oeste 168000 25050 10550 203600Sur 1488000 23100 2450 1513550Total general 2376000 236250 31125 2643375

Los datos numricos o los datos de texto, se resumen en el rea de valores de la tabla dinmica. Esta tabla dinmica hace un resumen de los datos del campo Ventas en la lista o tabla fuente.

Para filtrar la presentacin de los datos en una tabla dinmica, se debe utilizar un . El Filtro de informe divide la tabla dinmica en pginas individuales, permitindole ver uno por uno los datos que corresponden a un elemento del campo seleccionado.

Por ejemplo, en la tabla dinmica anterior los datos de ventas de todos los vendedores se presentan por Regin y Tipo. Para ver las ventas totales de un solo vendedor a la vez, se asigna el campo Vendedor de la lista fuente a Filtro de informe en la tabla dinmica.

Vendedor Castro

Suma de Ventas Rtulos de columnaRtulos de fila Carne Productos lcteos Verduras Total generalEste 101100 6250 107350Oeste 168000 168000Sur 744000 744000Total general 912000 101100 6250 1019350

Solamente se muestran los datos de venta del vendedor Castro. Para mostrar los datos de otro vendedor, debe seleccionar la flecha junto al nombre Castro y elegir un nombre de la lista. Si se desea ver la informacin de dos o ms Vendedores a la vez, se debe activar la casilla Seleccionar varios elementos.

Mostrar ms detalles

Se puede elegir el nivel y el tipo de detalles que se desea incluir en una tabla dinmica.

Para agregar detalles, podramos arrastrar otro campo a Rtulos de fila o Rtulos de columnas. Hay que tener cuidado de no dificultar la lectura de la tabla. Resulta ms sencilla la interpretacin si colocamos los campos en Rtulos de filas para agregar ms detalle a la tabla. Por ejemplo llevemos el campo Mes. El resultado de la tabla ser:


5

Vendedor (Todas)

Suma de Ventas Rtulos de columnaRtulos de fila Carne Productos lcteos Verduras Total generalEste 101100 16500 117600

Jul 64800 6250 71050Ago 36300 4000 40300Set 6250 6250

Norte 720000 87000 1625 808625Jul 420000 23700 1625 445325Ago 300000 27000 327000Set 36300 36300

Oeste 168000 25050 10550 203600Jul 168000 6500 174500Ago 25050 1600 26650Set 2450 2450

Sur 1488000 23100 2450 1513550Jul 444000 2450 446450Ago 744000 23100 767100Set 300000 300000

Total general 2376000 236250 31125 2643375

Aplicar filtros

Otra caracterstica til de las tablas dinmicas es permitir filtrar los resultados y as visualizar nicamente los que nos interesen en un momento determinado. Esto se emplea sobre todo cuando el volumen de datos es importante.

Los rtulos en la tabla estn acompaados, en su parte derecha, de una flecha indicando una lista desplegable.

Por ejemplo, si pulsamos sobre la flecha del rtulo Rtulos de fila nos aparece una lista como vemos en la imagen con las distintas Regiones disponibles en la tabla con una casilla de verificacin en cada uno de ellos para indicar si los queremos ver o no, ms una opcin para marcar todas las opciones en este caso todas las Regiones.


6

Si dejamos marcadas las Regiones Este y Norte, las otras Regiones desaparecern de la tabla, pero no se pierden, en cualquier momento podemos visualizarlas volviendo a desplegar la lista y marcando la casilla (Seleccionar todo).

Para cerrar este cuadro debemos pulsar en Aceptar o sobre Cancelar para cerrar y dejarlo como estaba.

Aplicando el filtro a varios campos podemos formar condiciones de filtrado ms complejas.

Herramientas de Tabla Dinmica

Cuando se inserta una tabla dinmica aparece Herramientas de Tabla dinmica con 2 fichas: Ficha Opciones y Ficha Diseo.

Ficha Opciones

a) Grupo Tabla dinmica

Nombre de tabla dinmica: Muestra el nombre de la tabla dinmica. Para cambiarlo, haga clic en el cuadro de texto y modifique el nombre.


7

Opciones: En el cuadro de dilogo Opciones de tabla dinmica podemos controlar los distintos ajustes de un informe de tabla dinmica.

o Ficha Diseo y Formato

Seccin Diseo Combinar y centrar celdas con etiquetas: Active esta casilla de verificacin para combinar celdas de elementos de columna y fila externos de manera que pueda centrar los elementos horizontal y verticalmente. Desactvela para justificar a la izquierda los elementos de los campos de columna y fila externos al principio del grupo de elementos. Sangra de etiquetas de filas en forma compacta: Para aplicar una sangra al rea de rtulos de filas si el informe de tabla dinmica tiene un formato compacto, seleccione un nivel de sangra de 0 a 127. Mostrar campos en rea de filtro de informe: Seleccione Hacia abajo, luego horizontalmente para mostrar primero los campos del rea del filtro del informe de arriba a abajo, segn se agregan los campos, antes de mostrar otra columna. Seleccione Horizontalmente, luego hacia abajo para mostrar primero los campos en el rea de filtro de informe de izquierda a derecha, segn se agregan a l los campos, antes de continuar con otra fila. Campos de filtro de informe por columna: Escriba o seleccione el nmero de campos que desea mostrar antes de mostrar otra columna o fila en funcin del valor en Mostrar campos en rea de filtro de informe.

Seccin de formato Para valores errneos, mostrar: Active esta casilla de verificacin y escriba el texto, como por ejemplo, "No vlido", que desea que aparezca en la celda en lugar de un mensaje de error. Desactive esta casilla de verificacin para mostrar el mensaje de error.


8

Para celdas vacas, mostrar: Active esta casilla de verificacin y, a continuacin, escriba un texto, como por ejemplo, "Vaca", que desea que aparezca en la celda en lugar de la celda en blanco. Autoajustar anchos de columnas al actualizar: Active esta casilla de verificacin para ajustar las columnas de la tabla dinmica automticamente al tamao del texto o valor de nmero ms extenso. Desactvela para conservar el ancho de columna actual de la tabla dinmica. Mantener el formato de la celda al actualizar: Active esta casilla de verificacin para guardar el formato y el diseo del informe de la tabla dinmica de manera que se utilice cada vez que realice una operacin en la tabla dinmica. Desactvela para no guardar el formato y el diseo del informe de tabla dinmica con el libro y utilizar el formato y el diseo predeterminados cada vez que realice una operacin en la tabla dinmica.

o Ficha Totales y Filtro

Seccin Totales generales Mostrar totales generales de las filas: Active o desactive esta casilla de verificacin para mostrar u ocultar la columna Total general situada junto a la ltima columna. Mostrar totales generales de las columnas: Active o desactive esta casilla de verificacin para mostrar u ocultar la fila Total general situada en la parte inferior del informe de la tabla dinmica.


9

Seccin Filtros Subtotales de elementos filtrados de pgina: Active o desactive esta casilla de verificacin para incluir o excluir elementos filtrados de informes en subtotales. Permitir varios filtros por campo: Active esta casilla de verificacin para insertar todos los valores, incluidos los que estn ocultos por un filtro, cuando Microsoft Office Excel calcule los subtotales y el total general. Desactvela para incluir slo los elementos mostrados cuando Excel calcule los subtotales y el total general.

Seccin Ordenacin Usar listas personalizadas al ordenar: Active o desactive esta casilla de verificacin para habilitar o deshabilitar el uso de listas personalizadas cuando Excel ordena listas. Si desactiva esta casilla de verificacin mejorar el rendimiento al ordenar grandes cantidades de datos.

o Ficha Mostrar

Seccin Mostrar Mostrar botones para expandir y contraer: Active esta casilla de verificacin para mostrar el botn ms (+) o el botn menos (-) que utiliza para expandir o contraer rtulos de filas o columnas. Desactvela para ocultar estos botones. Por ejemplo, puede ocultar estos botones al imprimir un informe de tabla dinmica o al consultarlo. Mostrar informacin contextual sobre herramientas: Active esta casilla de verificacin para que aparezca la informacin contextual sobre herramientas que muestra informacin sobre el valor, fila o columna de un campo o valor de datos. Desactvela para ocultar la informacin contextual sobre herramientas.


10

Mostrar propiedades en informacin sobre herramientas: Active o desactive esta casilla de verificacin para mostrar u ocultar la informacin contextual sobre herramientas que muestra informacin sobre las propiedades de un elemento. Mostrar ttulos de campo y filtrar listas desplegables: Active o desactive esta casilla de verificacin para mostrar u ocultar ttulos de tabla dinmica al principio del informe de tabla dinmica y filtrar flechas desplegables en rtulos de filas y columnas. Diseo de tabla dinmica clsica: Active o desactive esta casilla de verificacin para habilitar o deshabilitar la opcin de arrastrar campos dentro y fuera del informe de tabla dinmica. La presentacin de la estructura de la tabla dinmica ser la siguiente:

Podr arrastrar los campos desde la Lista de campos de la tabla dinmica a cada una de las zonas en la estructura. Mostrar elementos sin datos en las filas: Active o desactive para mostrar u ocultar los elementos de fila que no tienen valores. Mostrar elementos sin datos en las columnas: Active o desactive para mostrar u ocultar los elementos de columna que no tienen valores. Mostrar las etiquetas de los elementos cuando no hay campos en las reas de valores: Activar o desactivar para mostrar u ocultar rtulos de elementos cuando no hay campos en el rea de valores.

Seccin Lista de campos Las dos opciones siguientes se excluyen mutuamente. Ordenar de A a Z: Active esta casilla de verificacin para ordenar los campos de una lista de campos de tabla dinmica por criterio de ordenacin alfabtico.


11

Ordenar segn origen de datos: Active esta casilla de verificacin para ordenar los campos de una lista de campos de tabla dinmica segn el orden especificado en el origen de datos externo.

o Ficha Impresin

Imprimir botones para expandir o contraer al mostrarlos en una tabla dinmica: Active o desactive esta casilla de verificacin para mostrar u ocultar los botones para expandir y contraer al imprimir un informe de tabla dinmica. Esta casilla de verificacin no est disponible si la casilla de verificacin Mostrar Aumentar botones est desactivada en la ficha Presentacin de este cuadro de dilogo. Repetir etiquetas de fila en cada pgina impresa: Active o desactive esta casilla de verificacin para repetir los rtulos del elemento actual del rea de rtulos de fila en todas las pginas de un informe de tabla dinmica impreso. Establecer ttulos a imprimir Active o desactive esta casilla de verificacin para habilitar o deshabilitar la repeticin de encabezados de campos de filas y columnas y de rtulos de elementos de columna en todas las pginas impresas de un informe de tabla dinmica.


12

o Ficha Datos

Seccin Datos de tabla dinmica Guardar datos de origen con el archivo: Active o desactive esta casilla de verificacin para guardar los datos del origen de datos externo con el libro. Habilitar Mostrar detalles: Active o desactive esta casilla de verificacin para habilitar la informacin detallada del origen de datos y, a continuacin, mostrar los datos en una nueva hoja de clculo. Actualizar al abrir el archivo: Active o desactive esta casilla de verificacin para actualizar los datos al abrir el libro de Excel que contiene este informe de tabla dinmica.

Seccin Mantener los elementos eliminados del origen de datos Nmero de elementos que se van a recuperar por campo: Para especificar el nmero de elementos de cada campo que se almacenarn temporalmente con el libro en la cach, seleccione una de estas opciones: Automtico El nmero predeterminado de elementos nicos para cada campo. Ninguno Ningn elemento nico para cada campo. Mx El nmero mximo de elementos nicos para cada campo. Puede

especificar hasta un mximo de 1.048.576 elementos.

b) Ficha Campo Activo Campo Activo: Muestra el nombre personalizado del campo activo. Este nombre puede

modificarse. Configuracin de campo:


13

o Campo de valor

Nombre del origen: es el nombre del campo en el origen de datos. Nombre personalizado: muestra el nombre del campo actual en el informe de tabla dinmica, o el nombre del origen si no hay ningn nombre personalizado. Para cambiar el Nombre personalizado, haga clic en el cuadro de texto y modifique el nombre. Ficha Resumir por

Resumir campo de valor por: En el cuadro de lista se puede cambiar el tipo de clculo de Resumen. Cuando el campo de valor es numrico por defecto la funcin de resumen es la Suma y si es de caracteres Cuenta (funcin Contara en la hoja de clculo) Formato de nmero: Muestra la ficha Nmero del cuadro de dilogo Formato de celdas para seleccionar el formato de nmero deseado.

o Campo de datos


14

Nombre del origen: es el nombre del campo en el origen de datos. Nombre personalizado: muestra el nombre del campo actual en el informe de tabla dinmica, o el nombre del origen si no hay ningn nombre personalizado. Para cambiar el Nombre personalizado, haga clic en el cuadro de texto y modifique el nombre. Ficha Subtotales y filtros

Seccin Subtotales Automticos Utiliza la funcin predeterminada

para el campo. Ninguno Muestra el campo sin ningn subtotal. Personalizados Habilita la seleccin de una de las

funciones listada como subtotal. Seccin Filtros

Incluir nuevos elementos en el filtro manual: Active o desactive esta casilla de verificacin para incluir o excluir nuevos elementos en un informe de tabla dinmica con un filtro aplicado.

Expandir todo el campo: Cuando el campo activo no tiene otro campo de detalle, abre el cuadro de dilogo Mostrar detalle para elegir el campo de detalle. Cuando el campo activo tiene otro campo de detalle expande todos los elementos.

Contraer todo el campo: Cuando el campo activo tiene otro campo de detalle contrae todos los elementos del campo.

c) Grupo Agrupar Agrupar seleccin: Crea un grupo personalizado con los elementos seleccionados. Desagrupar: Desagrupa un rango de celdas agrupadas.. Agrupar campos: Permite agrupar el campo seleccionado de acuerdo al tipo de dato:

numrico o de fecha y hora. o Agrupar elementos numricos:

1. Seleccione el campo numrico en el informe de tabla de dinmica que desea agrupar. 2. Haga clic en Agrupar seleccin. 3. En el cuadro Comenzar en, escriba el primer elemento que desee agrupar. 4. En el cuadro Terminar en, escriba el ltimo elemento que desee agrupar. 5. En el cuadro Por, escriba un nmero que represente el intervalo incluido en cada

grupo.


15

o Agrupar fechas u horas: 1. Seleccione el campo de fecha u hora en el informe de tabla dinmica que desea

agrupar. 2. Haga clic en Agrupar campos 3. Escriba la primera hora o fecha que desee agrupar en el cuadro Comenzar en y la

ltima en el cuadro Terminar en. 4. En el cuadro Por, haga clic en uno o ms perodos de tiempo para los grupos.

Para agrupar los elementos por semanas, haga clic en Das en el cuadro Por, asegrese de que Das es el nico perodo de tiempo seleccionado y, despus, haga clic en 7 en el cuadro Nmero de das. A continuacin, puede hacer clic en otros perodos de tiempo por los que desee agrupar, como Mes.

d) Grupo Ordenar Pueden ordenarse tanto los datos de los campos de fila o columna como los campos valores. A a Z: Ordena en forma ascendente dependiendo del tipo de dato. Z a A: Ordena en forma descendente dependiendo del tipo de dato.


16

Ordenar

o Seccin Opciones de ordenacin Manualmente: Permite arrastrar y arreglar los elementos del modo que desee. Ascendente (A a Z): Permite seleccionar el campo por el cual se desea ordenar en

forma ascendente. Descendente (Z a A): Permite seleccionar el campo por el cual se desea ordenar en

forma descendente. o Seccin Resumen: Muestra las distintas opciones seleccionadas. o Ms opciones. Ordenar automticamente cada vez que se actualice el informe: Permite activar

o desactivar la operacin de ordenacin cada vez que el informe de tabla dinmica se actualiza.

Primer criterio de ordenacin: Permite ordenar segn un criterio de ordenacin definido por el usuario mediante una lista personalizada. Esta opcin slo est disponible si no se ha seleccionado la casilla de verificacin de Ordenar automticamente cada vez que se actualice el informe.

Ordenar por: Permite cambiar el tipo de ordenacin: Total general: Para ordenar por valores totales generales Valores de la columna seleccionada: Para cambiar la referencia a una celda en

la fila o columna con los valores por los que desea ordenar.

e) Grupo Datos Actualizar: Cuando los datos de la tabla que dieron origen a la tabla dinmica se modifican,

este cambio no se refleja en la tabla dinmica hasta que no se seleccione la opcin de Actualizar dentro de este submen


17

Cambiar origen de datos: Permite cambiar el rango de datos que dieron origen a la tabla dinmica.

f) Grupo Acciones Borrar:

o Borrar todo: Elimina los datos y formatos de la tabla dinmica

o Borrar filtros: Elimina los filtros establecidos en cualquier tipo de campo

g) Grupo Herramientas Frmulas

o Campo calculado Es un nuevo campo que obtiene su valor de la operacin realizada entre campos existentes en la tabla dinmica.

Nombre: Denominacin del campo calculado Frmula: Frmula que Excel utilizar para realizar el clculo del nuevo campo. Sumar: Eliminar: Permite borrar el campo calculado. Campos: Muestra un cuadro de lista con todos los campos del origen de datos. Al

seleccionar alguno de ellos y presionar el botn Insertar campo se insertar su nombre en la frmula.

o Elemento calculado Es un nuevo elemento en un campo, creado con los elementos existentes del campo.

h) Grupo Seleccionar


18

Para aplicar formato, copiar datos o realizar cambios en un informe de tabla dinmica, puede seleccionar celdas individuales o regiones de datos mediante comandos. Para que se habiliten las opciones siguientes primero debe seleccionarse la opcin Toda la tabla dinmica Etiquetas y valores: Selecciona los rtulos y los datos Valores: Selecciona slo los datos Etiquetas: Selecciona slo los rtulos Toda la tabla dinmica

i) Grupo Mover tabla dinmica

Permite cambiar la ubicacin de la tabla dinmica.

j) Grupo Mostrar u ocultar Lista de campos: Muestra u oculta el panel de Lista de campos Botones +/-: Muestra u oculta los botones + y que expanden o contraen los elementos del

campo Encabezados de campos: Muestra u oculta los rtulos de campos

Ficha Diseo

a) Grupo Diseo

Subtotales o No mostrar subtotales: Oculta los subtotales de campos de columnas y de filas

individuales. o Mostrar todos los subtotales en la parte inferior: Muestra los subtotales al finalizar cada

grupo de elementos. o Mostrar todos los subtotales en la parte superior: Muestra los subtotales al comienzo de

cada grupo de elementos. Totales Generales

o Desactivado para filas y columnas: Oculta los totales de campos de columnas y de filas. o Activado para filas y columnas: Muestra los totales de campos de columnas y de filas. o Activado slo para filas: Muestra los totales de campos de filas. o Activado slo para columnas: Muestra los totales de campos de columnas.


19

Diseo de informes o Mostrar en forma compacta: Se usa para evitar que los datos relacionados se salgan

horizontalmente de la pantalla y minimizar la necesidad de desplazarse por ella. Los campos iniciales se ubican en una columna y tienen aplicada sangra para mostrar la relacin de columnas anidadas.

o Mostrar en forma de esquema: Se usa para esquematizar los datos al estilo clsico de tabla dinmica.

o Mostrar en formato tabular Se usa para ver todos los datos en un formato de tabla tradicional y para copiar celdas fcilmente a otra hoja de clculo.

Filas en blanco o Insertar lnea en blanco despus de cada elemento: Incluye una fila en blanco al

finalizar cada grupo.

o Quitar lnea en blanco despus de cada elemento: Elimina la fila en blanco que se incluy al finalizar cada grupo.

Opciones de tabla dinmica o Encabezados de fila: Activa o desactiva el mostrar un formato especial para los

encabezados de filas

o Encabezados de columna: Activa o desactiva el mostrar un formato especial para los encabezados de columnas

o Filas con bandas: Activa o desactiva el mostrar filas con bandas en las que las filas pares tienen un formato diferente a las impares.

o Columnas con bandas: Activa o desactiva el mostrar con bandas en las que las columnas pares tienen un formato diferente a las impares.

Estilos de tabla dinmica Muestra diferentes estilos de tabla dinmica para seleccionar el deseado. El estilo seleccionado se modificar de acuerdo a las Opciones de tabla dinmica elegidas.

tabla dinamica

Documents