1 Contenido
Tablas dinámicas ... 1
Características ... 1
Utilidad ... 1
Crear un Informe de Tabla dinámica ... 3
Herramientas Principales ... 7
Resumen de Datos ... 7
Filtros ... 11
Filtros de Informe ... 11
Agregar un filtro de informe a la tabla dinámica ... 11
Seleccionar elementos en el filtro de informe ... 12
Gráficos Dinámicos ... 14
Insertar un Grafico Dinámico a la Tabla dinámica ... 14
Video de consulta ... 19
Herramientas básicas adicionales de gran utilidad ... 20
1
Tablas dinámicas
Una tabla dinámica es una tabla interactiva que contiene campos y consiste en el resumen y análisis de un conjunto de datos de una lista, tabla o base de datos, atendiendo a varios criterios de agrupación, representado como una tabla de doble entrada que nos facilita la interpretación de dichos datos. Es dinámica porque nos permite ir obteniendo diferentes totales, filtrando datos, cambiando la presentación de los datos y mirándolos desde perspectivas diferentes.
Su ventaja y funcionalidad radica en la capacidad de concentrar los datos de los campos de interés de la lista, tabla o base de datos y realizar diferentes operaciones de agrupación y ordenación con ellos.
Características
Son una gran forma de resumir, analizar, explorar y presentar los datos.
Ayudan a dar sentido a los datos, y más cuando la cantidad de datos es grande.
Presentan la posibilidad de alterar la estructura en filas y columnas.
Puede actualizarse cada vez que se modifiquen los datos originales (los datos que se utilizaron para crear la tabla dinámica)
Permite presentar desde distintos puntos de vista una misma información, usando para ello, funciones de resumen, como la suma o el promedio.
Utilidad
Resume bases de datos grandes: se pueden resumir grandes cantidades de datos
que pueden estar en una hoja de trabajo o en una base de datos externa.
Prepara datos para su representación gráfica: Los gráficos basados en tablas
2
Permite el análisis de datos adecuado: A través de tendencias, excepciones,
problemas, se puede organizar el modo en que se resumen y presentan los datos y acceder a un mayor nivel de detalle cuando se desee.
Datos Resumidos: La tabla dinámica resume los datos utilizando la función de
3
Crear un Informe de Tabla dinámica
1-. Definir el origen de Datos del Informe de la Tabla Dinámica
Establecer la lista de elementos (rango), tabla o base de datos que se desea trabajar especificando claramente los campos (columnas) y registros (filas) que la integraran (no debe haber filas en blanco en el rango, tabla o base de datos).
Por ejemplo en una pequeña base de datos para el control de ventas de una Papelería nos muestra la información referente a la fecha de compra, la región o zona, nombre, comisión y total de la venta de cada vendedor así como el nombre del artículo, la cantidad vendida y su precio unitario definidos en los siguientes campos y 43 registros (solo se visualizan algunos como ejemplo de la información que contiene cada registro):
2-. Crear Informe de la Tabla Dinámica
4
Al hacer clic en este botón aparecerá la pantalla de opciones para la creación de la tabla dinámica, donde se podrán configurar las opciones para:
Seleccionar los datos que se desean analizar
Tabla o rango: Es el rango de las celdas donde se encuentra la información que se quiere agregar a la tabla dinámica
Fuente de datos externa: Son los datos almacenados fuera de Excel, por ejemplo, en una base de datos de Microsoft Access o Microsoft SQL Server o en un archivo de cubo de procesamiento analítico en línea (OLAP).
Elegir ubicación para colocar el informe de la tabla dinámica
Nueva Hoja de Calculo
Hoja de Cálculo Existente estableciendo la Ubicación de la celda donde se escribirá la primera celda de la tabla dinámica.
5 3-. Agregar campos al informe de tabla dinámica
Al hacer clic en Aceptar nos aparecerán dentro de la hoja de Excel la tabla dinámica sin datos así como la ventana de diseño de la tabla dinámica.
En la ventana de diseño se podrán arrastrar los campos para formar la tabla de acuerdo a los resultados que se están buscando.
Tabla dinámica sin datos
En este apartado se mostraran los campos disponibles de la lista de elementos, tabla o base de datos
6
Para colocar un campo en un área específica de la sección de diseño, haga clic con el botón derecho del mouse en el nombre de campo en la sección del campo y, a continuación, seleccione:
O bien puede arrastrar un campo al área que desea, haga clic y mantenga presionado en el nombre del campo en la sección de campo y, a continuación, arrástrelo hasta el área que corresponde en la sección de diseño.
De esta manera podrá colocar los campos en el área de Diseño según lo que requiera analizar y mostrar.
Elementos que conforman la ventana de Diseño de la Tabla Dinámica
Al aplicar un filtro se pueden mostrar los elementos que se necesitan o desean analizar y oculta lo demás. (Son muy útiles cuando el volumen de información es muy grande)
Por lo general los campos no numéricos se agregan al área de Etiquetas de Fila
Por lo general los campos
numéricos se agregan al área de Valores
Se pueden agregar jerarquías de Fecha y hora, así como elementos de valores para mostrar dicha información distribuida en las columnas.
En este apartado se mostraran los campos disponibles de la
7
Herramientas Principales
Resumen de Datos
Para poder realizar cálculos considerando los Valores de algún campo en particular se deberá posicionar sobre dicho campo para que en la opción de Campo Activo de la Pestaña de Opciones de la cinta de herramientas de la tabla dinámica se muestre seleccionado y al hacer clic en Configuración de campo, se muestre la ventana de Configuración de campo de valor donde se podrá especificar el tipo de cálculo que se desea usar para resumir los datos del campo seleccionado, como se muestra en las siguientes imágenes:
8 Tipos de Cálculo
Suma: Muestra la suma de los datos del campo insertado.
Cuenta: Muestra el recuento de los datos del campo insertado. Incluye las celdas
con contenido de tipo texto. Las celdas vacías son ignoradas.
Promedio: Muestra la media aritmética de los datos del campo insertado.
Max. Muestra el mayor de los datos del campo insertado.
Min. Muestra el menor de los datos del campo insertado.
Producto: Muestra el producto de los datos del campo insertado.
Contar números: Muestra el recuento de los datos del campo insertado sólo si son
datos numéricos. Las celdas vacías o con contenido de tipo texto son ignoradas.
DesvEst (Muestra): Muestra la desviación estándar de los datos del campo
insertado como muestra.
DesvEstP (Población): Muestra la desviación estándar de los datos del campo
insertado como población.
Var (Muestra): Muestra la varianza de los datos del campo insertado como muestra.
Varp (Población): Muestra la varianza de los datos del campo insertado como
9
Considerando el ejemplo suponga que se desea analizar y determinar por cada
vendedor la siguiente información:
Los productos vendidos
La cantidad total de productos vendidos
El importe Total de ventas generado
La comisión correspondiente por este mes de trabajo
Considerando lo solicitado tendríamos que ubicar y
arrastrar sin soltar el campo de Vendedor y Articulo en
Etiquetas de fila y los elementos para obtener los
cálculos solicitados en Valores, en este caso, los
campos de Cantidad, Total y Comisión, y de forma
automática los Valores se ubican en Etiquetas de
Columna para mostrar la información de manera
distribuida y organizada.
En el caso del ejemplo los cálculos que se solicitan son
totales por lo cual utilizando la herramienta de
Resumen de Datos el Cálculo de Suma que se dio por
Default en el apartado de Valores permanece y el
10 Trabajador
Productos vendidos
Total de productos
vendidos Importe Total de
Ventas Comisión total
del mes
**De esta forma se muestran los datos
de los 11
11
Filtros
El filtrado permite un análisis a profundidad de grandes cantidades de datos en un informe de tabla dinámica.
Los filtros de tablas dinámicas son aditivos, lo que significa que cada filtro adicional se basa en el filtro actual y además reduce el subconjunto de datos. En un subconjunto de datos, puede crear hasta tres tipos de filtros al mismo tiempo: manual, etiqueta o fecha y valor, y se evalúan en ese orden. Sin embargo, si desea permitir varios filtros por campo, debe activar esta opción antes de empezar. Esta opción está desactivada de forma predeterminada.
Al filtrar en un informe de tabla dinámica, tenga en cuenta lo siguiente:
No se puede filtrar por color, color de fuente o conjunto de iconos. Solo se puede filtrar manualmente en un filtro de informe.
Filtros de Informe
Usando un filtro de informe, puede mostrar rápidamente un conjunto de valores distintos en el informe de tabla dinámica. De forma predeterminada, los filtros de informe se muestran en filas encima de la tabla dinámica, pero puede mostrar varios filtros de informe en columnas en su lugar. Los elementos que seleccione en
el filtro de informe se muestran en el informe de tabla dinámica, y los elementos que no se seleccionen se ocultarán.
Agregar un filtro de informe a la tabla dinámica
En la Lista de campos de tabla dinámica, arrastre el campo que desea usar como filtro de informe al área Filtro de informe, como se muestra en la siguiente figura:
Campos disponibles de la
lista de elementos, tabla o base de datos
12 Se puede repetir este paso para crear más de un filtro de informe. Los filtros de informe se muestran encima del informe de tablas dinámicas para un fácil acceso.
Seleccionar elementos en el filtro de informe
1. En el informe de tabla dinámica, haga clic en la flecha del filtro de informe.
2. Para mostrar una casilla para todos los elementos para poder activarlos o desactivarlos cuando sea necesario, active la casilla Seleccionar varios elementos.
Datos de la Tabla Dinámica
13 3. Para ocultar o mostrar elementos en el informe de tabla dinámica, siga alguno de
los procedimientos siguientes:
o Desactive las casillas de los elementos que no desea mostrar en el informe de tabla dinámica.
o Desactive la casilla (Todas) para desactivar todas las casillas activadas actualmente; a continuación, active las casillas de los elementos que desea mostrar en el informe de tabla dinámica.
o Active la casilla (Todas) para mostrar todos los elementos.
Si los elementos tienen niveles jerárquicos, puede mostrar u ocultar los elementos de niveles inferiores haciendo clic en o en junto a un nivel.
4. Haga clic en Aceptar. Debe haber al menos una casilla activada para que este botón esté habilitado.
14
Gráficos Dinámicos
Los gráficos dinámicos son como los gráficos normales, solo que muestran los datos de una tabla dinámica. Al igual que un gráfico normal, se podrá seleccionar un tipo de gráfico, el diseño y el estilo para representar mejor los datos.
Insertar un Grafico Dinámico a la Tabla dinámica
1-. Seleccionar cualquier celda de la tabla dinámica para activar la Cinta de Herramientas de la Tabla Dinámica, Seleccionar la pestaña de Opciones y dar clic en el botón Grafico Dinámico del apartado Herramientas.
15 3-.El gráfico dinámico aparecerá en la hoja de cálculo, y se podrá manipular la información según lo que se desea mostrar o analizar, reflejándose todos los cambios directamente en la tabla dinámica o viceversa, si se realizan cambios en la tabla dinámica automáticamente se actualizarán en el grafico dinámico.
Además podrá moverlo haciendo clic y arrastrándolo hacia la posición donde se desee ubicar. Así mismo se podrá modificar el diseño y formato del Grafico, como si se realizará en un gráfico normal, a continuación se retoman algunos elementos básicos para modificar la presentación del gráfico.
Controles Interactivos del Informe de Grafico Dinámico
16
Guarda el formato y diseño de este grafico como plantilla que puede aplicar a futuros gráficos.
Cambia a un tipo diferente de Grafico.
Intercambia los datos del eje. Los datos que se han colocado en el eje X se moverán al eje Y y viceversa
Cambia el rango de datos incluidos en el Grafico
Cambia el diseño general del Grafico
Cambia el estilo visual general del Gráfico.
17
1. Selecciona un elemento del Grafico para que pueda darle Formato
2. Muestra el Cuadro de Dialogo Formato Para ajustar con Precisión el formato del grafico seleccionado
3. Borra el Formato personalizado del elemento de grafico seleccionado para volver al estilo visual global que tenía aplicado el grafico
Selecciona un estilo visual para la forma o línea
1. Rellena la forma seleccionada con un color sólido, degradado, imagen o textura
2. Especifica el color, ancho y estilo de línea del contorno de la forma seleccionada
3. Aplica un efecto visual a la forma seleccionada como una sombra, iluminado, reflejo o rotación 3D
Selecciona un estilo visual para el texto
Rellena el texto con un color sólido, degradado, imagen o textura
Especifica el color, ancho y estilo de línea del contorno del Texto
Aplica un efecto visual al texto como una sombra, iluminado, reflejo o rotación 3D
Cambia el alto de la forma o Imagen
18 Finalmente se complementarán algunas tablas dinámicas del ejemplo trabajado
agregando los gráficos dinámicos correspondientes para poder visualizar la información de una manera más clara.
Retomaremos la Tabla dinámica que muestra las sumas totales de Cantidad de artículos vendidos, la venta total y la comisión del mes de todos los trabajadores (es decir sin aplicar aun algún filtro en específico).
19 Como se puede modificar directamente el grafico, cambiaremos los datos desactivando las sumas totales de Cantidad de artículos vendidos y la comisión del mes de todos los trabajadores, y filtrando el nombre del trabajador considerando solo a Julio para que de esta forma solo muestre el total de sus ventas realizadas. Recordemos que al modificar el grafico se actualiza directamente la tabla dinámica por lo cual el resultado se reflejara como se muestra a continuación:
De esta manera se pueden manipular y modificar los datos según las necesidades del análisis que se requieran y deseen mostrar.
Video de consulta
20
Herramientas básicas adicionales de gran utilidad
Actualizar Datos
Cambiar origen de Datos
Mover Tabla Dinámica
Eliminar Tabla Dinámica
Para eliminar una tabla dinámica simplemente debemos seleccionar la tabla en su totalidad y presionar la Tecla Supr.
Actualiza toda la información que provenga de un origen de datos
Cambia los datos de origen de esta tabla dinámica
21
Actividad de Reforzamiento
Instrucciones: Descarga el archivo Reforzamiento2.xlsx que contiene una hoja con el nombre DATOS PROFESIONES, en la que se muestra una estructura de una serie de campos y filas con información sobre algunos datos estadísticos referentes a algunas profesiones. Considerando la información que se te proporciona realiza y responde lo que se solicita a continuación:
Genera la Tabla dinámica que muestre el promedio de edad de los trabajadores de cada profesión e Inserta su gráfico. Describe a continuación el procedimiento que realizaste.
Genera la Tabla dinámica que muestre el ingreso mensual más alto por profesión e Inserta su gráfico. Describe a continuación el procedimiento que realizaste.
22
