guÍa para el uso de tablas dinÁmicas - 20101118
TRANSCRIPT
AUTOR: Iván Díaz P.
Noviembre 2010
Guía para el Uso de Tablas Dinámicas de MS-Excel Curso de Bases de Datos, Magíster en Gestión
2010
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 2
TABLA DE CONTENIDOS
1 PREPARACIÓN DE INFORMACIÓN ........................................................................................................ 3
2 OBTENCIÓN DE DATOS ......................................................................................................................... 5
2.1 COPIAR UNA TABLA .................................................................................................................................. 5 2.2 CONEXIÓN A UNA BASE DE DATOS ............................................................................................................... 6
2.2.1 Datos visibles localmente ........................................................................................................... 6 2.2.2 Datos no visibles localmente ...................................................................................................... 9
3 CREACIÓN DE UNA TABLA DINÁMICA ................................................................................................ 11
3.1 CONFECCIÓN DE UN INFORME BÁSICO ........................................................................................................ 11 3.2 FORMATEO DE LOS VALORES .................................................................................................................... 11
4 DISTINTAS OPCIONES DE DESPLIEGUE DE INFORMACIÓN .................................................................. 14
4.1 COMBINAR CAMPOS EN LAS FILAS ............................................................................................................. 14 4.2 DESPLIEGUE POR COLUMNAS ................................................................................................................... 14 4.3 PAGINACIÓN DE RESULTADOS ................................................................................................................... 15 4.4 FILTRO Y ORDENAMIENTO DE RESULTADOS .................................................................................................. 16 4.5 AGRUPACIÓN ........................................................................................................................................ 19 4.6 INTERACTIVIDAD DEL INFORME ................................................................................................................. 21 4.7 OBTENER DATOS DETALLADOS .................................................................................................................. 23 4.8 MOSTRAR TABLA DINÁMICA EN FORMATO EXCEL 2003................................................................................. 23
5 USO DE CAMPOS AD-HOC .................................................................................................................. 26
5.1 USO DE PORCENTAJES RESPECTO DE UN TOTAL ............................................................................................. 26 5.2 CAMPOS CALCULADOS ............................................................................................................................ 27 5.3 SUBTOTALES ......................................................................................................................................... 29
6 USO DE GRÁFICOS .............................................................................................................................. 31
6.1 CREACIÓN DE UN GRÁFICO SIMPLE ............................................................................................................ 31 6.2 INTERACTIVIDAD DE LOS GRÁFICOS ............................................................................................................ 32
7 FORMATOS CONDICIONALES.............................................................................................................. 34
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 3
1 Preparación de Información Previamente a trabajar con una tabla dinámica debemos disponer de datos listos para el análisis. Para ello es muy útil el uso de Access, donde se pueden procesar los datos y dejarlos en una estructura tal que facilite la obtención de las estadísticas relevantes para el negocio. Desde este punto de vista, una buena estructura de datos es aquella que contiene una combinación de columnas con descriptores - que ayudarán en la categorización de la información – y de columnas con valores numéricos –sobre las que se realizarán las operaciones aritméticas como contar, sumar u obtener promedios, entre otras -. Ejemplos de descriptores:
Categoría de productos
Nombre de clientes
Región
Fechas
Valores booleanos (SI/NO) Ejemplos de valores numéricos
Valor de las ventas
Cantidad de productos Por medio del trabajo hecho con las consultas en MS-ACCESS, hemos preparado la tabla EJER_TABLA_BASE para analizar el comportamiento de los pedidos y que usaremos en lo sucesivo en esta guía. Esta tabla luce de la siguiente forma:
Figura 1
Dónde:
CAMPO DESCRIPCIÓN
IdPedido Identificación del pedido
Cliente Nombre del cliente
PaísCliente País del Cliente
Empleado Nombre del empleado que realizó la venta
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 4
CAMPO DESCRIPCIÓN
CargoEmp Cargo del Empleado
FechaPedido Fecha del Pedido
FechaEntrega Fecha de Entrega
FechaEnvío Fecha de Envío
CargoPedido Cargo (costo) del envío
PaísDestinatario País al que va destinado el pedido
MismoPais Indicador (SI/NO) de la correspondencia del país de destino respecto del país del cliente.
Valor Valor de la venta (totalización de la cantidad de productos vendidos por el precio unitario de cada uno).
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 5
2 Obtención de datos Para trabajar con información para las tablas dinámicas existen varios métodos. Como punto de partida deberemos abrir Excel 2007, con lo que aparecerá una hoja en blanco.
Figura 2
Los métodos que revisaremos son:
Copiar datos
Conexión a una base de datos
2.1 Copiar una tabla Para ejemplificar esta opción usaremos la tabla “EJER_TABLA_BASE”, la que obtuvimos mediante los ejercicios sobre Access. Se selecciona la tabla completa y se copia al portapapeles:
Figura 3
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 6
Luego se selecciona una hoja en Excel, posicionándose en una determinada celda y se pegan los datos:
Figura 4
NOTA:
Esta opción puede ser buena cuando el volumen de datos es relativamente bajo (del orden de los miles de registros.), pero en la medida que la cantidad de información sea muy elevada se pueden producir problemas con la memoria del computador si esta no es lo suficientemente grande.
2.2 Conexión a una base de datos Conectándose a una base de datos es posible trabajar con los datos en línea desde una fuente operacional. De este modo la información para el análisis podría ayudar a monitorear en vivo un determinado proceso de la organización y hacer un control más eficiente de éste. Sin embargo no siempre es posible conectarse a la base de datos corporativa, lo que dependerá del rol del analista y de las políticas de TI de la organización. Para este ejemplo nos conectaremos a la base de datos “Neptuno” con la que se ha venido trabajando en los ejercicios con Access.
2.2.1 Datos visibles localmente
Para hacer una conexión con Access, se debe seleccionar la aleta “Datos”, luego “Obtener datos externos” y finalmente “Desde Access”.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 7
Figura 5
Seleccionar la base de datos desde la que se obtendrá la información.
Figura 6
Aparecerá a continuación una pantalla donde se confirma la ruta y nombre de la base de datos. Basta con presionar “Aceptar”.
Figura 7
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 8
Luego aparecerá una ventana donde se establece la conexión. En el caso de conectarse a MS-Access basta con presionar “OK”.
Figura 8
A continuación se selecciona la tabla:
Figura 9
Finalmente se deberá elegir la posición que ocuparán los datos dentro de la hoja de Excel. Además se debe elegir la manera en que se accederá a los datos, para lo cual se entregan 3 opciones:
Tabla: Los datos se copian localmente a la hoja de Excel, pero se mantiene una conexión con la base de datos de origen.
Informe de tabla dinámica: los datos no se visualizan en el archivo Excel, ya que aparece automáticamente la opción de trabajar con un informe de tabla dinámica.
Informe de gráfico y tabla dinámicos: es idéntico al caso anterior, pero se agrega la opción de crear un gráfico directamente.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 9
Figura 10
Para este ejemplo usaremos la primera opción, con lo que la información aparecerá de la siguiente manera:
Figura 11
El contenido completo de la tabla seleccionada estará disponible en la hoja de datos.
2.2.2 Datos no visibles localmente
En este caso se opera exactamente como fue descrito en el punto anterior, seleccionado esta vez la opción “Informe de Tabla Dinámica”.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 10
Figura 12
Tras hacerlo, aparecerá lo siguiente:
Figura 13
En este caso los datos no son visibles, pero a través de la tabla dinámica es posible acceder a ellos tal como será explicado en las siguientes secciones.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 11
3 Creación de una tabla dinámica Una vez que tenemos datos disponibles por alguno de los métodos descritos en la sección anterior, podremos comenzar a trabajar con nuestra tabla dinámica. Para ello usaremos la tabla generada en los ejercicios con Access donde totalizamos la información de los pedidos: “EJER_TABLA_BASE”.
3.1 Confección de un informe básico Supongamos que queremos analizar las ventas de cada uno de nuestros empleados. Para ello arrastre el campo “Empleado” en el panel de la derecha hasta la sección “Rótulos de Fila”. A continuación arrastre el campo “Valor” hasta la sección “Valores”. Obtendrá lo que muestra la figura.
Figura 14
3.2 Formateo de los valores A menudo los resultados requieren que sean formateados para facilitar la legibilidad. Para ello haga clic en “Suma de Valor” en el panel de la derecha y seleccione “Configuración del campo de valor…”.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 12
Figura 15
Aquí podrá cambiar el nombre del campo por uno más representativo, seleccionar el tipo de cálculo que necesita hacer (suma, contar, promedio, etc.) y elegir el formato del número, entre otras. Para nuestro ejemplo, cambiaremos el nombre a “Monto” y seleccionaremos “Formato de número”.
Figura 16
Con esto aparecerá una ventana donde podremos elegir el tipo de despliegue para el valor seleccionado. En nuestro ejemplo seleccionaremos formato Moneda y elegiremos la cantidad de posiciones decimales.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 13
Figura 17
Hecho esto, la información aparecerá de la siguiente manera:
Figura 18
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 14
4 Distintas opciones de despliegue de información
4.1 Combinar campos en las filas Supongamos ahora que necesitamos conocer el nivel de ventas por país del cliente. Para ello se debe arrastrar el campo “PaísCliente” hasta la sección “Rótulos de Fila” y ponerlo antes de “Empleado”. El resultado es el que se muestra en la figura.
Figura 19
4.2 Despliegue por columnas También puede hacer más detallado el despliegue de información si pone campos en la sección “Rótulos de columnas”. Supongamos que necesitamos cuantificar las ventas por País y empleado, y al mismo tiempo separando en envíos al mismo país del cliente y en envíos a un país diferente. El resultado se muestra en la figura:
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 15
Figura 20
Para mejorar la legibilidad del reporte, puede cambiar el nombre de los rótulos que Excel asigna por defecto, tal como se muestra en la figura. Basta con escribir el nuevo nombre en la columna respectiva.
4.3 Paginación de resultados En caso de que el reporte sea muy extenso, es posible que se prefiera visualizar la información de manera parcializada. Para ello una opción es la paginación, para lo cual basta con arrastrar el campo respectivo a la sección “Filtro de informe”.
Figura 21
En el ejemplo se ha elegido el país como criterio de paginación, donde puede elegirse uno o más países para establecer el filtro.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 16
Figura 22
4.4 Filtro y ordenamiento de resultados Si tenemos una lista muy extensa de resultados, probablemente queramos filtrar los valores para enfocarnos en aquellos que son más interesantes. Por ejemplo, suponga que necesita conocer a sus 10 mejores clientes.
Figura 23
Haciendo clic en la flecha al lado del campo que se desea ordenar, seleccione “Más opciones de ordenación”.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 17
Figura 24
A continuación elige el criterio por el que va a ordenar, que en este caso es el Monto y en forma descendente:
Figura 25
Y con ello el resultado quedará ordenado:
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 18
Figura 26
Sin embargo, es posible que deseemos obtener un resultado más directo para conocer a los 10 mejores clientes. En este caso se elige “Filtros de valor” y “Diez mejores…”
Figura 27
Finalmente se escoge el criterio de filtro, que en este caso son los 10 mejores.
Figura 28
Y de este modo tendremos el resultado que buscamos.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 19
Figura 29
Así como encontramos los 10 mejores, también es posible escoger los 10 (en realidad cualquier número) peores clientes.
4.5 Agrupación Supongamos que se requiere conocer la evolución de las ventas a lo largo del tiempo. Para ello se puede utilizar el campo “FechaPedido” y ponerlo en “Rótulos de columnas”. Sin embargo, el resultado no es útil tal como queda en la figura, ya que cada una de las fechas quedará como una columna de la tabla, lo que no ayuda en el análisis.
Figura 30
PREGUNTA: Además de la agrupación que aquí se explica, ¿qué solución alternativa existe? Para mejorar el despliegue de información, haga clic con el botón derecho en cualquiera de las columnas de “FechaPedido” y seleccione “Agrupar”.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 20
Figura 31
Aquí puede seleccionar distintas opciones de agrupación para la fecha. En nuestro ejemplo escogeremos “Trimestres” y “Años”.
Figura 32
El resultado queda de la siguiente manera:
Figura 33
Nótese que automáticamente se agregó un nuevo criterio en la sección “Rótulos de Columna” para el “Año”.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 21
4.6 Interactividad del informe Puede elegir diferentes formas de desplegar los datos, según la que sea más clara al momento de hacer el análisis. Por ejemplo, mueva “Años” a “Rótulos de Fila”.
Figura 34
En la figura anterior no se muestra una totalización por cada trimestre de cada año. Para mostrarlo, haga clic en el campo “Año” de la sección “Rótulos de Fila” y seleccione “Configuración de Campo”. En la ventana, en la sección “Subtotales” escoja “Automático”.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 22
Figura 35
Los resultados ahora se mostrarán totalizados:
Figura 36
Otro caso puede ser que se necesite analizar la evolución por trimestre de cada vendedor. Basta con cambiar de lugar “Años” y “FechaPedido”.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 23
Figura 37
4.7 Obtener datos detallados A menudo es necesario conocer el comportamiento detallado de ciertos datos. Por ejemplo, quisiéramos conocer cómo se componen las ventas del tercer trimestre del 2008 de Steven Buchanan (ver figura anterior). Haciendo doble clic en la celda correspondiente, aparecerá una nueva hoja en Excel con la información detallada.
Figura 38
4.8 Mostrar tabla dinámica en formato Excel 2003 El formato en que Excel 2007 muestra la información en ocasiones puede no ser la más clara. Para ello existe la opción de usar el formato de Excel 2003. Suponga el siguiente informe:
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 24
Figura 39
Haciendo clic con el botón derecho en cualquier parte de la tabla seleccione “Opciones de tabla dinámica…”.
Figura 40
En la aleta “Mostrar” seleccione “Diseño de tabla dinámica clásica”.
Figura 41
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 25
El resultado será el siguiente:
Figura 42
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 26
5 Uso de campos ad-hoc En ciertos análisis importará obtener indicadores que ayuden entender mejor el comportamiento de ciertas variables relevantes para el negocio. A continuación veremos tres ejemplos:
5.1 Uso de porcentajes respecto de un total Suponga que necesitamos saber cuánto representan las ventas de cada vendedor respecto del total de las ventas de un año en particular.
Figura 43
Arrastre el campo Valor de la lista de la derecha hasta “valores”. Ese campo ya existe y hasta ahora lo hemos estado usando con el nombre “Monto”. Obtendrá lo siguiente:
Figura 44
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 27
Vaya a “Configuración de campo valor…”, cámbiele el nombre y en “Mostrar valores como” elija “% del total”.
Figura 45
El resultado se mostrará de la siguiente forma:
Figura 46
5.2 Campos calculados Suponga que todos los vendedores reciben una bonificación del 10% de las ventas anuales, pero esta bonificación puede alcanzar el 15% si las ventas superan los $100.000. Para determinar el porcentaje de bonificación que corresponde a cada vendedor, haga clic en la tabla dinámica y luego elija el menú “Herramientas de tabla dinámica” (el que tal como todos los menús de Excel, aparece dependiendo del objeto seleccionado). Luego seleccione “Fórmulas” y finalmente en “Campo calculado…”.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 28
Figura 47
Escriba el nombre del campo calculado y la fórmula de cálculo.
Figura 48
El resultado se mostrará de la siguiente forma:
Figura 49
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 29
Probablemente deba configurar el campo para que aparezca en formato porcentaje.
5.3 Subtotales Suponga que necesita conocer la el total de la venta por país, el valor promedio, el máximo y el mínimo. Se tiene el siguiente despliegue:
Figura 50
Vaya a “Configuración del campo” de “PaisCliente” y seleccione los subtotales que requiere:
Figura 51
El resultado será el siguiente:
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 30
Figura 52
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 31
6 Uso de Gráficos Los gráficos son herramientas indispensables de todo análisis. Excel 2007 hace muy fácil esta tarea a partir de las tablas dinámicas, ya que basta con seleccionar el gráfico deseado para que tome automáticamente los datos y los despliegue de una manera muy clara.
6.1 Creación de un gráfico simple Suponga que se desea mostrar gráficamente el comportamiento de los vendedores según el año.
Figura 53
Para generar un gráfico, vaya al menú “Insertar” y seleccione el tipo de gráfico más apropiado.
Figura 54
El resultado será el mostrado en la figura.
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 32
Figura 55
6.2 Interactividad de los gráficos Tal como con las tablas dinámicas, los gráficos dinámicos tienen un importante grado de interactividad. Suponga que del gráfico anterior desea obtener más detalle, separando los montos según si fueron enviados al mismo país del cliente o no. Haciendo clic en alguna de las barras, aparecerá la ventana “Mostrar detalle”.
Figura 56
Al elegir el campo por el que se quiere obtener más detalle, el resultado será el siguiente:
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 33
Figura 57
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 34
7 Formatos condicionales Excel 2007 tiene formas de mostrar los datos que ayudan a entender rápidamente qué puede estar ocurriendo con las cifras. Por ejemplo, seleccionado el botón “Formato condicional” y luego “Barra de datos”, es posible que cada cifra se acompañe de una barra.
Figura 58
Es posible configurar cómo se comporta el formato condicional seleccionando “Más reglas…”.
Figura 59
Otra alternativa es asignar indicadores que permitan evaluar las cifras según una determinada regla:
GUÍA PARA EL USO DE TABLAS DINÁMICAS DE MS-EXCEL CURSO DE BASE DE DATOS, MAGÍSTER EN GESTIÓN
Preparado por Iván Díaz P. 35
Figura 60
Por ejemplo, podemos definir una semaforización para las ventas por año según el rango en que se encuentren.
Figura 61