INSTITUTO LATINOAMERICANO DE COMPUTACION
ILAC PREPARANDO TÉNICOS PARA LA COSTA RICA DIGITAL
Curso Tablas Dinámicas en Excel 2016 Folleto Teórico – Práctico
Curso Tablas Dinámicas en Excel 2016
Contenido LECCIÓN 1 ..........................................................................................................................................5 Introducción a las bases de datos ......................................................................................................5 Concepto de bases de datos.....................................................................................................5 Excel como una base de datos .................................................................................................5 Consideraciones para evitar errores al generar una tabla en Excel ..........................................5 Introducción a las tablas dinámicas....................................................................................................5 Concepto y utilidad ..................................................................................................................5 Concepto de dinámico .............................................................................................................5 Aplicaciones en donde se incluye esta herramienta ................................................................6 Otras denominaciones que reciben las tablas dinámicas .........................................................6 Tablas dinámicas de dos y tres dimensiones ......................................................................................6 Planteamiento de una tabla dinámica ......................................................................................6 Crear un informe de tabla dinámica .........................................................................................8 Secciones del diseño de una Tabla Dinámica .........................................................................10 ¿Qué campos utilizar y dónde ubicarlos en la ventana de diseño? ........................................10 Tablas dinámicas de tres dimensiones ...................................................................................11 Manejo de la tercera dimensión ............................................................................................11 Aplicar filtros a una tabla dinámica ..................................................................................................11 Configuración de campo valor .........................................................................................................12 Nombre personalizado a un campo .......................................................................................12 Agrupar – desagrupar elementos .....................................................................................................13 Ordenar un campo ...........................................................................................................................14 Desplazar un informe .......................................................................................................................14 Mover un informe de tabla dinámica a otro lugar ...........................................................................14 Crear tabla dinámica con una fuente de datos externos ..................................................................14 Herramientas de la tabla dinámica ..................................................................................................15 Opciones de diseño ................................................................................................................15 Opciones de analizar ..............................................................................................................16 Opciones de tablas Dinámicas ..........................................................................................................16 Prácticas Lección Nº 1 ......................................................................................................................17 Tarea Nº 1 ..............................................................................................................................20 LECCIÓN 2 ........................................................................................................................................21 Convertir rango de datos en tabla ....................................................................................................21 Definir el nombre de la tabla ..................................................................................................21
Página 2
Curso Tablas Dinámicas en Excel 2016
Filtros de datos de nivel superior .....................................................................................................21 Segmentación de datos. .........................................................................................................21 Escala de tiempo ....................................................................................................................22 Resumen o detalle de los datos .......................................................................................................24 Generación de Subtablas........................................................................................................24 Mostrar páginas de filtro de informe .....................................................................................24 Campos calculados ...........................................................................................................................25 Crear una fórmula en una tabla dinámica ..............................................................................25 Agregar un campo calculado ..................................................................................................25 Agregar un elemento calculado a un campo ..........................................................................26 Problemas con los elementos calculados ...............................................................................26 Cómo eliminar un campo calculado o elemento calculado. ...................................................26 Gráfico dinámico ..............................................................................................................................26 Herramientas de grafico dinámico .........................................................................................27 Actualización de datos .....................................................................................................................28 Actualizar datos de tabla dinámica de forma manual ............................................................28 Actualizar automáticamente los datos de tabla dinámica al abrir el libro..............................28 Prácticas Lección Nº 2 ......................................................................................................................30 Tarea Nº 2 ........................................................................................................................................31 LECCIÓN Nº 3 ...................................................................................................................................32 Tablas porcentuales .........................................................................................................................32 Cambiar el tipo de cálculo predeterminado ...........................................................................32 Tablas de diferencias ........................................................................................................................33 Vinculación de tablas dinámicas ......................................................................................................34 Tablas dinámicas vinculadas...................................................................................................36 Referencias a tablas dinámicas ........................................................................................................36 Tablas dinámicas con rangos dinámicos. ..........................................................................................37 Asignar el rango dinámico a una tabla dinámica. ...................................................................38 Cruce de dos o más tablas para la creación de una tabla dinámica .................................................39 DASHBOARD .....................................................................................................................................42 Función de un dashboard estratégico ....................................................................................42 Herramientas para la creación de un dashboard ...................................................................42 Práctica Lección Nº3.........................................................................................................................44 Tarea Nº3 .........................................................................................................................................46 LECCIÓN Nº4 ....................................................................................................................................47
Página 3
Curso Tablas Dinรกmicas en Excel 2016
Imprimir un informe de tabla dinรกmica ...........................................................................................47 Power Pivot ......................................................................................................................................47 Relaciones entre tablas ..........................................................................................................48 Creando una tabla dinรกmica...................................................................................................49
Pรกgina 4
Curso Tablas Dinámicas en Excel 2016
LECCIÓN 1 Introducción a las bases de datos Concepto de bases de datos Las bases de datos son un conjunto de datos pertenecientes a un mismo contexto y almacenados sistemáticamente para su posterior uso. Ejemplo: el inventario. Entre los sistemas gestores de bases de datos (SGBD) tenemos: Oracle, Access, Sybase, MySql, etc.
Excel como una base de datos Excel se conoce como una base de datos plana, los registros se incluyen en listados simples. La base de datos plana tiene una flexibilidad limitada y sólo son adecuadas para tareas relativamente sencillas, como la fusión de correspondencia. Excel como base de datos no ofrece las funcionalidades de las bases de datos relacionales, pero es una herramienta de gran poder en el análisis de información. Los datos en una tabla de Excel están organizados en: • Campos (Columnas) • Registros (Filas) • Dato (elemento individual dentro de las columnas) • Base de datos (colección completa de registros)
Consideraciones para evitar errores al generar una tabla en Excel • • • • • • • •
El rango de una lista deberá estar separado al menos por una fila y una columna de otra información La primera fila, está destinada a los títulos o nombres de los campos Un título o un nombre de un campo puede contener hasta 255 caracteres (datos tipo alfanuméricos) No es válido introducir espacios en blanco al empezar un dato en una celda Cada columna debe usar el mismo formato (o mismo tipo de dato) Excel diferencia los caracteres en minúsculas o mayúsculas, cuando efectúa ordenamientos Se puede usar fórmulas para calcular valores de campo. No puede haber columna o filas en blanco entre los datos
Introducción a las tablas dinámicas Concepto y utilidad Comprende una serie de datos agrupados en forma de resumen, que agrupan aspectos concretos de una información global. Debe ser muy importante la cantidad de información a manejar para que el uso de la tabla dinámica se justifique. Las tablas dinámicas nos permiten: resumir, analizar, explorar y presentar un resumen de los datos de la hoja de cálculo o un origen de datos externos.
Concepto de dinámico Las tablas dinámicas se asocian al concepto de dinamismo ya que permite:
Página 5
Curso Tablas Dinámicas en Excel 2016
• Obtener diferentes totales • Filtrado de datos • Modificar la presentación de los datos • Actualización de la información. • Visualizar o no los datos origen, etc. Similitud con MS-ACCESS: Consulta de Referencia Cruzada o Crosstab Query Access
Aplicaciones en donde se incluye esta herramienta • • •
Microsoft Excel OpenOffice.org Calc Programas de inteligencia empresarial
Otras denominaciones que reciben las tablas dinámicas • • •
Tablas Dinámicas Tabla Pivote Pivot table (Marca registrada por Microsoft Corporation)
Tablas dinámicas de dos y tres dimensiones Planteamiento de una tabla dinámica Supongamos que tienes una tabla de datos que contiene mucha información sobre las ventas de la compañía entre las cuales se encuentra una columna con los productos de la empresa, también la ciudad donde se vende y las ventas correspondientes para cada ciudad.
Se solicita un reporte con el total de ventas por ciudad y el total de ventas por producto. Así que lo que necesitamos hacer es sumar las ventas para cada ciudad y sumar las ventas de cada producto para obtener el reporte. En lugar de utilizar fórmulas podemos utilizar una tabla dinámica para obtener el resultado deseado. Una tabla dinámica nos permite hacer una comparación entre diferentes columnas de una tabla. Puedes imaginarte una tabla dinámica de la siguiente manera:
Página 6
Curso Tablas Dinámicas en Excel 2016
Lo primero que debemos hacer es especificar los campos de nuestra tabla de datos que vamos a comparar. Elegimos las ciudades como las filas de nuestra tabla dinámica:
Excel tomará todos los valores de ciudades de nuestra tabla de datos y los agrupará en la tabla dinámica, es decir, colocará los valores únicos de la columna de datos eliminando las repeticiones. Ahora hacemos lo mismo para especificar las columnas de la tabla dinámica.
Finalmente elegimos una columna de valores numéricos que serán calculados y resumidos en la tabla dinámica:
Página 7
Curso Tablas Dinámicas en Excel 2016
Así tendremos un reporte que compara las ciudades y los productos y para cada combinación obtendrás el total de ventas. Lo más importante es que las tablas dinámicas permiten elegir entre todas las columnas de una tabla de datos y hacer comparaciones entre ellas para poder realizar un buen análisis de la información.
Crear un informe de tabla dinámica Partiendo de una Base de Datos, tomando como ejemplo las ventas de una determinada compañía, por país y vendedor, se aplicarán sobre la misma la herramienta de Excel, Tablas Dinámicas FECHA PEDIDO
PAIS
VENDEDOR
MONTO
02/02/2009
Costa Rica
Luis Solís
50.454,00
03/02/2009
Japón
Ana Pérez
52.345,00
04/02/2009
USA
Sofía Leiva
64.578,00
05/02/2009
Alemania
Pedro Mora
75.241,00
06/02/2009
Japón
Luis Solís
25.245,00
07/02/2009
USA
Ana Pérez
24.554,00
08/02/2009
Costa Rica
Pedro Mora
48.547,00
09/02/2009
Panamá
Sofía Leiva
40.578,00
03/03/2009
Costa Rica
Luis Solís
45.789,00
04/03/2009
Japón
Ana Pérez
48.250,00
05/03/2009
USA
Sofía Leiva
21.547,00
06/03/2009
Alemania
Pedro Mora
6.456,00
07/03/2009
Japón
Luis Solís
25.488,00
08/03/2009
USA
Ana Pérez
51.421,00
09/03/2009
Costa Rica
Pedro Mora
20.421,00
10/03/2009
Panamá
Sofía Leiva
54.120,00
Página 8
Curso Tablas Dinámicas en Excel 2016
La creación de una tabla dinámica se realiza a través del asistente y en varios pasos. 1. Poner el cursor en cualquier celda de la Tabla 2. Clic en la pestaña Insertar, panel Tablas, opción Tabla Dinámica 3. Aparece el siguiente cuadro de diálogo
4. Seleccione la tabla o el rango de celdas a utilizar (si dejó el cursor dentro de la Tabla ya estará seleccionada). 5. Elija donde colocar el informe de tabla dinámica resultante 6. Dar clic en el botón aceptar, aparece la siguiente imagen:
Página 9
Curso Tablas Dinámicas en Excel 2016
Secciones del diseño de una Tabla Dinámica A la izquierda se encuentra el área de diseño lista para el informe de tabla dinámica y, a la derecha se encuentra la Lista del campo de la tabla dinámica. Esta lista muestra los títulos de las columnas de los datos de origen: Cada título es un campo de la lista. En la parte inferior del panel se muestran las áreas de la tabla dinámica: ✓ Filtros. Los campos que coloques en esta área crearán filtros para la tabla dinámica a través de los cuales podrás restringir la información que ves en pantalla. Este es el filtro principal de la tabla dinámica. ✓ Columnas. Esta área contiene los campos que se mostrarán como columnas de la tabla dinámica. ✓ Filas. Contiene los campos que determinan las filas de la tabla dinámica. ✓ Valores. Son los campos que se colocarán como las “celdas” de la tabla dinámica y ✓ que serán totalizados para cada columna y fila.
¿Qué campos utilizar y dónde ubicarlos en la ventana de diseño? En la lista de campos, seleccione las casillas de verificación junto a los campos que desea agregar al informe. Los campos se agregan automáticamente al informe. Los campos que no son numéricos se agregan a la zona de etiquetas Filas del informe. A medida que agrega más campos no numéricos, Excel los coloca dentro de los campos que ya están en el informe de la tabla dinámica, creando una jerarquía. Los campos numéricos se agregan a la derecha. 1. Para eliminar un campo del informe, deseleccione la casilla de verificación junto al nombre del campo de la lista. 2. Para eliminar todos los campos de un informe para que pueda volver a empezar, haga clic en la pestaña Analizar de la Cinta bajo Herramientas de la tabla dinámica. En el grupo Acciones, haga clic en la flecha del botón Borrar y, a continuación, seleccione Borrar todo. 3. Para eliminar todo el informe, haga clic en la pestaña Analizar. En el grupo Acciones, haga clic en la flecha de Seleccionar. Haga clic en Toda la tabla dinámica. A continuación, presione SUPR. Al crear una tabla dinámica de esta forma, Excel es el que determina la posición de los campos de la tabla. Sin embargo, podemos elegir el lugar de la tabla dinámica donde queremos insertar los campos de la tabla, arrastrando con el mouse el campo de la tabla de la parte superior del panel, hacia alguna de las áreas de la tabla dinámica que se muestran en la parte inferior del panel.
Página 10
Curso Tablas Dinámicas en Excel 2016
Tablas dinámicas de tres dimensiones La Figura a la derecha, muestra una tabla dinámica más compleja que las anteriores. Permite analizar las ventas clasificadas no sólo por fecha de pedido y vendedor, sino también por país. Es una tabla de tres dimensiones. Se construye de la misma forma que las tablas anteriores; sólo cambia la parte del diseño: En la sección Filtro arrastre el campo que considere debe mostrarse ahí, por ejemplo, País.
Manejo de la tercera dimensión En realidad, la tabla obtenida, aunque incluye la variable País, muestra la información global de todos los países. Sin embargo, es posible operar sobre la tercera dimensión para mostrar, por ejemplo, sólo la información de Alemania Forma de Operar 1. En la sección País, se descuelgan las opciones con clic, como se muestra en la Figura anterior. 2. Se marca el país Alemania. 3. Se da un clic en Aceptar. 4. Aparece la tabla, con sólo la información de Alemania.
Aplicar filtros a una tabla dinámica Otra característica útil de las tablas dinámicas 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.
Página 11
Curso Tablas Dinámicas en Excel 2016
Los campos principales en el panel y los rótulos en la tabla están acompañados, en su parte derecha, de una flecha indicando una lista desplegable. Por ejemplo, si pulsamos sobre la flecha del rótulo Etiquetas de columna, nos aparece una lista como la que vemos en la imagen, con los distintos meses disponibles en la tabla con una casilla de verificación en cada uno de ellos para indicar si los queremos ver o no, más una opción para marcar todas las opciones; en este caso, todos los meses. Si dejamos marcados los meses Enero y Febrero, los otros meses desaparecerán de la tabla, pero no se pierden; en cualquier momento, podemos visualizarlos volviendo a desplegar la lista y marcando la casilla (Seleccionar todo). Para cerrar este cuadro guardando los cambios, debemos pulsar en Aceptar. Para cerrar sin conservar las modificaciones, pulsaremos Cancelar. Aplicando el filtro a varios campos, podemos formar condiciones de filtrado más complejas.
Configuración de campo valor Por defecto, al crear una tabla dinámica, Excel nos genera unos totales con sumatoria. Puede interesarnos modificar ese cálculo por otro, como pueden ser sacar el máximo o el mínimo, el promedio, etc. Para ello debe seguirse los siguientes pasos: •
• • •
Dar clic en el triángulo de lista en el campo valores a la operación que originalmente se había configurado, por ejemplo, suma de Monto. Inmediatamente se despliega un menú, escoger la opción Configuración de campo valor. La caja de diálogo que se despliega posteriormente, se abre por defecto en la pestaña Resumir valores por. En la parte inferior, aparecen varias funciones. Se puede seleccionar la que se necesite.
Nombre personalizado a un campo Excel por defecto coloca el nombre de la columna de la tabla de datos como nombre del campo en la tabla dinámica. Si queremos cambiar el nombre por uno más representativo se puede hacer de la siguiente forma: 1. Clic en cualquier celda del campo al que vamos a cambiar el nombre. 2. En la pestaña Analizar, en el panel Campo Activo, dar clic en Configuración de campo.
Página 12
Curso Tablas Dinámicas en Excel 2016
3. En el cuadro de diálogo que aparece, dar clic en el cuadro Nombre personalizado. Seguidamente escriba el nombre que quiera que aparezca en la tabla dinámica.
Agrupar – desagrupar elementos Puede agrupar datos que contienen fechas u horas siguiendo estos pasos. 1. Haga clic con el botón secundario del ratón en una celda del campo que desea agrupar y, a continuación, haga clic en Agrupar. 2 En la caja de diálogo Agrupar que se despliega debe definir la agrupación a realizar (p.ej. Trimestres) el resultado consiste en una agrupación por cada trimestre, como se muestra en la imagen siguiente:
Página 13
Curso Tablas Dinรกmicas en Excel 2016
Para desagrupar, haga clic con el botรณn derecho del ratรณn en el campo agrupado y seleccione Desagrupar.
Ordenar un campo โ ข
Haga clic con el botรณn secundario del ratรณn en una celda del campo que desea ordenar. Seleccione Ordenar y, a continuaciรณn, haga clic en una opciรณn. Por ejemplo, haga clic en Ordenar de mayor a menor o Mรกs opciones.
Desplazar un informe Cuando desplaza un informe de tabla dinรกmica, transpone la vista vertical u horizontal de un campo, desplazando filas al รกrea de columnas o desplazando columnas al รกrea de filas. โ ข Haga clic con el botรณn secundario del ratรณn en el campo que desea desplazar. Seleccione Mover y Mover "nombre del campo" a Columnas, o seleccione Mover "nombre del campo" a Filas. โ ข Ademรกs, existen otras opciones para mover el campo, como: Hacia arriba, Hacia abajo, Al principio, Al final.
Mover un informe de tabla dinรกmica a otro lugar 1. Haga clic en el informe de la tabla dinรกmica. 2. Haga clic en la pestaรฑa Analizar de la Cinta bajo Herramientas de tabla dinรกmica. En el grupo Acciones, haga clic en Mover tabla dinรกmica. Se abre el cuadro de diรกlogo Mover tabla dinรกmica. 3. En Elija dรณnde desea colocar el informe de tabla dinรกmica; seleccione Nueva hoja de cรกlculo o, en la casilla Ubicaciรณn de Hoja de cรกlculo existente, escriba la primera celda en el rango de celdas donde desea colocar el informe de la tabla dinรกmica. A continuaciรณn, haga clic en Aceptar.
Crear tabla dinรกmica con una fuente de datos externos 1. Seleccione una celda en blanco.
Pรกgina 14
Curso Tablas Dinámicas en Excel 2016
2. En la pestaña Insertar, en el grupo Tablas, haga clic en Tabla dinámica, a continuación, se muestra la caja de diálogo de la imagen. 3. Haga clic en Utilice una fuente de datos externa. 4. Haga clic en Elegir conexión. Aparece el cuadro de diálogo Conexiones existentes. 5. En la lista Mostrar situada en la parte superior del cuadro de diálogo, seleccione la categoría de conexiones para la que desea seleccionar una conexión, o seleccione Todas las conexiones existentes (que es la opción predeterminada). 6. De clic al botón Examinar en busca de más… 7. Se abre el cuadro de diálogo donde puede buscar algún archivo de base de datos al que desee conectarse, y a continuación, haga clic en Abrir. 8. Si el archivo de base de datos contiene varias tablas, se presenta un cuadro desde donde se debe seleccionar la tabla de la que deseamos insertar una tabla dinámica. 9. Especifique una ubicación. Realice uno de los procedimientos siguientes: ▪ Para colocar un informe de tabla dinámica en una nueva hoja de cálculo a partir de la celda A1, haga clic en Nueva hoja de cálculo. ▪ Para colocar el informe de tabla dinámica en una hoja de cálculo existente, seleccione Hoja de cálculo existente y, a continuación, escriba la primera celda del rango en el que desea colocar el informe de tabla dinámica. 10. Haga clic en Aceptar. Se agrega un informe de tabla dinámica vacío, con la Lista de campos de tabla dinámica visible, a la ubicación especificada.
Herramientas de la tabla dinámica Las herramientas de la tabla dinámica se agrupan en dos pestañas que son: • Diseño • Analizar
Opciones de diseño Después de crear la tabla dinámica, podríamos necesitar realizar algunos ajustes de forma para que se vea mejor. Esto lo podremos hacer en la pestaña Diseño.
Los cambios en esta pestaña son más que nada visuales, para ver o no ver los subtotales, los totales generales, cambiar el diseño de la tabla dinámica, para verlos en forma de esquema, tabular, compacta, etc. Además de ponerle colores a las filas y columnas con los Estilos de tabla dinámica que trae el Excel.
Página 15
Curso Tablas Dinámicas en Excel 2016
Opciones de analizar Algunas de estas opciones se observarán más adelante como temas separados.
Opciones de tablas Dinámicas La presentación de la información en una tabla dinámica puede tener diferentes apariencias, por ejemplo: puede no mostrar totales, subtotales, filtros, los botones para expandir o contraer la información, para ello debe dar clic derecho en cualquier celda de la tabla y escoger Opciones de tabla dinámica, luego en la caja de diálogo que se despliega tome las decisiones que considere para presentar su informe.
Página 16
Curso Tablas Dinámicas en Excel 2016
Prácticas Lección Nº 1 Objetivo: El estudiante demuestra mediante el desarrollo de las siguientes prácticas el conocimiento adquirido para construir Tablas Dinámicas en Excel. Práctica Nº 1 Instrucciones: 1. Crear un libro con el nombre de ¨Tablas Dinámicas¨ 2. Renombrar la Hoja 1 con el nombre de ¨Práctica1TD¨ y digitar la base de datos que se le propone seguidamente: FECHA PEDIDO PAIS VENDEDOR MONTO 02/02/2009 Costa Rica Luis Solís 50.454,00 03/02/2009 Japón Ana Pérez 52.345,00 04/02/2009 USA Sofía Leiva 64.578,00 05/02/2009 Alemania Pedro Mora 75.241,00 06/02/2009 Japón Luis Solís 25.245,00 07/02/2009 USA Ana Pérez 24.554,00 08/02/2009 Costa Rica Pedro Mora 48.547,00 09/02/2009 Panamá Sofía Leiva 40.578,00 03/03/2009 Costa Rica Luis Solís 45.789,00 04/03/2009 Japón Ana Pérez 48.250,00 05/03/2009 USA Sofía Leiva 21.547,00 06/03/2009 Alemania Pedro Mora 6.456,00 07/03/2009 Japón Luis Solís 25.488,00 08/03/2009 USA Ana Pérez 51.421,00 09/03/2009 Costa Rica Pedro Mora 20.421,00 10/03/2009 Panamá Sofía Leiva 54.120,00 3. Construir una Tabla Dinámica con el siguiente diseño: • Ubique en el Campo Columna: País • Ubique en el Campo Fila: Vendedor • Ubique en el Campo Valores: Suma Monto • Renombre la hoja resultante con el nombre de ¨SolPra1¨
Práctica Nº 2 Instrucciones: Con base en los datos de la práctica Nº 1 hoja ¨ Práctica1TD ¨ejecute las siguientes actividades para crear una tabla dinámica con el diseño que se le propone: • Ubique en el Campo Columna: Vendedor • Ubique en el Campo Fila: Fecha de Pedido
Página 17
Curso Tablas Dinámicas en Excel 2016
• •
Ubique en el Campo Valores: Suma Monto Renombre la hoja resultante con el nombre de ¨SolPra2¨
Práctica Nº 3 Instrucciones: 1. Digitar la siguiente base de datos en una hoja nueva del Libro ¨Tablas Dinámicas¨ creado en la Práctica Nº 1 2. Renombrar la Hoja 1 con nombre ¨Practica 2 TD¨ y digitar la base de datos que se le propone seguidamente: NOMBRE
AREA DE VENTAS FECHA VENTAS VENTAS X PROVINCIAS TOTAL VENTAS
LUIS MARIA OLGA JAVIER ANA PEDRO MANUEL CARLOS RAMON ROLANDO ALFONSO DAVID TERESA PILAR LUIS MARIA OLGA JAVIER ANA PEDRO MANUEL CARLOS RAMON ROLANDO ALFONSO DAVID TERESA PILAR LUIS MARIA OLGA JAVIER ANA PEDRO MANUEL CARLOS RAMON ROLANDO ALFONSO DAVID TERESA PILAR LUIS MARIA OLGA
AUTOMOVILES AUTOMOVILES MOTOS MOTOS AUTOMOVILES MOTOS AUTOMOVILES AUTOMOVILES MOTOS MOTOS AUTOMOVILES MOTOS AUTOMOVILES AUTOMOVILES AUTOMOVILES AUTOMOVILES MOTOS MOTOS AUTOMOVILES MOTOS AUTOMOVILES AUTOMOVILES MOTOS MOTOS AUTOMOVILES MOTOS AUTOMOVILES AUTOMOVILES AUTOMOVILES AUTOMOVILES MOTOS MOTOS AUTOMOVILES MOTOS AUTOMOVILES AUTOMOVILES MOTOS MOTOS AUTOMOVILES MOTOS AUTOMOVILES AUTOMOVILES AUTOMOVILES AUTOMOVILES MOTOS
02/08/2008 02/02/2008 03/03/2008 03/04/2008 02/08/2008 02/02/2008 03/03/2008 03/04/2008 02/08/2008 02/02/2008 03/02/2008 04/02/2008 05/02/2008 06/02/2008 07/02/2008 08/02/2008 03/09/2008 04/09/2008 03/09/2008 06/09/2008 07/09/2008 03/04/2008 03/03/2008 03/10/2008 06/10/2008 07/10/2008 03/03/2008 03/04/2008 03/03/2008 03/04/2008 02/08/2008 15/01/2008 22/05/2008 30/06/2008 14/07/2008 08/11/2008 10/12/2008 26/01/2008 06/05/2008 11/06/2008 29/07/2008 18/11/2008 02/12/2008 03/01/2008 15/05/2008
Página 18
CARTAGO HEREDIA SAN JOSE ALAJUELA CARTAGO LIMON PUNTARENAS GUANACASTE ALAJUELA CARTAGO LIMON PUNTARENAS GUANACASTE ALAJUELA PUNTARENAS GUANACASTE ALAJUELA CARTAGO LIMON PUNTARENAS GUANACASTE ALAJUELA PUNTARENAS CARTAGO HEREDIA SAN JOSE ALAJUELA CARTAGO CARTAGO HEREDIA SAN JOSE LIMON PUNTARENAS GUANACASTE ALAJUELA PUNTARENAS GUANACASTE ALAJUELA CARTAGO LIMON PUNTARENAS GUANACASTE ALAJUELA PUNTARENAS CARTAGO
₡2.500.000,00 ₡2.587.800,00 ₡3.654.789,00 ₡1.524.578,00 ₡2.264.879,00 ₡3.437.898,00 ₡2.880.708,93 ₡2.937.580,82 ₡2.994.452,70 ₡3.031.324,59 ₡3.108.196,48 ₡3.165.068,36 ₡3.221.940,25 ₡3.278.812,13 ₡2.264.879,00 ₡3.457.898,00 ₡2.880.708,00 ₡3.031.324,59 ₡2.937.380,82 ₡2.994.452,70 ₡3.031.324,59 ₡3.108.196,48 ₡3.165.068,26 ₡3.221.940,25 ₡3.278.812,13 ₡3.335.684,02 ₡3.392.355,90 ₡3.449.427,79 ₡3.506.299,68 ₡3.563.171,56 ₡3.620.915,33 ₡3.108.196,48 ₡3.165.068,36 ₡3.221.940,25 ₡3.278.812,13 ₡2.264.879,00 ₡3.457.898,00 ₡2.880.708,00 ₡3.031.324,59 ₡2.937.380,82 ₡2.994.452,70 ₡3.031.324,59 ₡3.108.196,48 ₡3.165.068,26 ₡3.221.940,25
Curso Tablas Dinámicas en Excel 2016
3. Construir una Tabla Dinámica con el siguiente diseño: • Ubique en el Campo Columna: Ventas x Provincia • Ubique en el Campo Fila: Fecha de Venta • Ubique en el Campo Valores: Suma Total Ventas • Renombre la hoja resultante con el nombre de ¨SolPra3¨
Página 19
Curso Tablas Dinámicas en Excel 2016
Tarea Nº 1 Instrucciones: Con base en los datos de la práctica Nº 1 hoja ¨ Práctica 2 TD ¨ejecute las siguientes actividades para crear una tabla dinámica con el diseño que se le propone: • Ubique en el Campo Columna: Área de Ventas • Ubique en el Campo Fila: Nombre • Ubique en el Campo Valores: Suma Total de Ventas • Renombre la hoja resultante con el nombre de ¨SolPra4¨
Página 20
Curso Tablas Dinámicas en Excel 2016
LECCIÓN 2 Convertir rango de datos en tabla Seleccione el rango de celdas que desea incluir en la tabla. Las celdas pueden estar vacías o contener datos. En la pestaña Insertar del grupo Tablas, haga clic en Tabla. Si el rango seleccionado incluye los encabezados de tabla, active la casilla La tabla tiene encabezados. El resultado será similar al de la imagen.
Definir el nombre de la tabla • • •
•
Pestaña herramientas de tabla Pestaña Diseño Grupo Propiedades Opción: Nombre de la Tabla
Filtros de datos de nivel superior Segmentación de datos. La segmentación de datos en tablas dinámicas es una característica que permite hacer un filtrado de los datos dentro de una tabla dinámica. De esta manera puedes filtrar fácilmente la información por más de una columna. En primer lugar, debes hacer clic sobre cualquier celda de la tabla dinámica y posteriormente en la pestaña Analizar y dentro del grupo Filtrar deberás hacer clic sobre el comando Insertar Segmentación de datos.
Excel mostrará el siguiente cuadro de diálogo:
Página 21
Curso Tablas Dinámicas en Excel 2016
En este cuadro deberás seleccionar el campo o los campos que deseas utilizar como filtros en la tabla dinámica y Excel colocará un panel para cada campo seleccionado: Para filtrar la información de la tabla dinámica es suficiente con hacer clic sobre cualquiera de las opciones del filtro. Para mostrar de nuevo toda la información puedes hacer clic en el botón Borrar filtro que se encuentra en la esquina superior derecha de cada panel. Podrás agregar tantos filtros como campos disponibles tengas en la tabla dinámica, lo cual te permitirá hacer un buen análisis de la información.
Escala de tiempo La escala de tiempo permite aprovechar campos de tipo fecha. Las fechas son campos importantes que permiten a cada registro asignarle una fecha de realización de la transacción, etc. Veamos el siguiente ejemplo: la tabla dinámica muestra los Agentes en Filas y en Columnas están los valores para cada una de las provincias. Además, dos cuadros de segmentación que corresponden a los campos Agente y Provincia, con los cuales se facilita demasiado la consulta de los datos de la tabla dinámica simplemente seleccionando cualquiera de las opciones de ambos cuadros de segmentación. Al observar los registros de la base de datos, vemos que existe una columna de fechas: Fecha de Venta.
Página 22
Curso Tablas Dinámicas en Excel 2016
Procedimiento: • Pestaña Analizar • Grupo de herramientas Filtrar • Opción: Insertar escala de tiempo • Aparece un cuadro de dialogo que lista los campos de tipo Fecha. Se selecciona la Fecha de Venta. • Clic en Aceptar • Luego aparece un control de Tiempo en la hoja
Este control permite observar los resultados de la tabla dinámica de acuerdo a la escala de tiempo que indiquemos.
Página 23
Curso Tablas Dinámicas en Excel 2016
Resumen o detalle de los datos Generación de Subtablas Haciendo doble clic sobre una celda, en este caso por ejemplo en la columna MONTO, donde figura la cantidad 52345, Excel automáticamente produce un detalle en hoja aparte según lo siguiente:
Mostrar páginas de filtro de informe Utilice un filtro de informe para centrarse en un subconjunto de datos del informe, a menudo una línea de producto, un periodo de tiempo o una región geográfica. • En la sección Campos de tabla dinámica, haga clic con el botón secundario en el nombre de un campo y seleccione Agregar a filtro de informe. • O bien dar clic sostenido sobre el campo que se desea sirva de filtro y arrástralo a la sección de Filtro, posteriormente aparece en la parte superior de la tabla el filtro definido.
Si no quiere filtrar una y otra vez para obtener un reporte y los datos de la sección Filtro de Informe no son muchos, puede obtener de forma rápida una serie de hojas con todos los filtros de informe que contenga la tabla. Por ejemplo, de la tabla anterior deseamos obtener filtros por País. Para eso filtramos la tabla por ese campo. Pero si quiero obtener todos los reportes por País que se puedan mostrar en la tabla dinámica se puede seguir el siguiente procedimiento: 1. Clic en cualquier celda de la tabla dinámica. 2. Clic en la pestaña Analizar. Luego clic en la opción Tabla dinámica. 3. Clic en Opciones y luego en Mostrar páginas de filtro de informes. 4. Una vez hecho esto, aparece un cuadro de diálogo en donde deberá seleccionar el campo por el cual desea generar las hojas de filtro de informe. En nuestro caso solo tenemos el campo País. 5. Al dar clic en Aceptar, Excel genera una hoja nueva para cada Filtro que se obtiene de la tabla dinámica.
Página 24
Curso Tablas Dinámicas en Excel 2016
Campos calculados Crear una fórmula en una tabla dinámica Las fórmulas creadas utilizando un campo calculado se pueden basar en cualquiera de los campos a partir de los que se crea el informe. Cuando se utiliza un campo calculado, se agrega un nuevo campo al informe de tabla dinámica. 1. Determine si desea un campo calculado o un elemento calculado dentro de un campo. ▪ Utilice un campo calculado si piensa utilizar los datos de otro campo en la fórmula. ▪ Utilice un elemento calculado si desea que la fórmula utilice datos de uno o más elementos específicos dentro de un campo. 2. Realice uno de los procedimientos siguientes:
Agregar un campo calculado 1. Haga clic en el informe de tabla dinámica. 2. En la pestaña Analizar, haga clic en Campos, Elementos y Conjuntos y, a continuación, haga clic en Campo calculado. 3. En el cuadro Nombre, escriba un nombre para el campo. 4. En el cuadro Fórmula, escriba la fórmula para el campo. Para utilizar los datos de otro campo en la fórmula, haga clic en el campo en el cuadro Campos y, a continuación, haga clic en Insertar campo. Por ejemplo, para calcular una comisión del 2% para cada valor del campo Monto, podría escribir = Monto *2%. 5. Haga clic en Aceptar, el resultado se muestra en la siguiente imagen:
Nótese como el campo calculado Comisión adiciona una nueva columna.
Página 25
Curso Tablas Dinámicas en Excel 2016
Agregar un elemento calculado a un campo 1. Si los elementos del campo están agrupados, en la pestaña Analizar, en el grupo Agrupar, haga clic en Desagrupar. 2. Haga clic en el campo donde desee agregar el elemento calculado. 3. En la pestaña Analizar, haga clic en Campos, Elementos y Conjuntos y, a continuación, haga clic en Elemento calculado. 4. En el cuadro Nombre, escriba un nombre para el elemento calculado. 5. En el cuadro Fórmula, escriba la fórmula para el elemento.
Problemas con los elementos calculados Excel tiene dos problemas relacionados con los elementos calculados: A. Son agregados automáticamente al final de la lista de elementos del campo. B. El total general incluye los elementos calculados, por lo tanto, da como resultado el doble de lo que debería ser. El primer problema lo resolvemos moviendo los elementos a la posición deseada o bien copiándolos y pegándolos inmediatamente debajo del que corresponde. Excel reorganiza la tabla automáticamente. El problema del total general lo podemos tratar de dos maneras. Una es eliminar el total general para las columnas en Opciones de Tablas. La otra forma para corregir el error del TOTAL, es crear un nuevo elemento, Total, que sume todos los elementos calculados que se crearon.
Cómo eliminar un campo calculado o elemento calculado. Para eliminar los campos o elementos calculados vamos a la pestaña Analizar, haga clic en Campos, Elementos y Conjuntos y, a continuación, haga clic en Campo Calculado o Elemento calculado. Por ejemplo, para eliminar el campo Comisión abrimos el diálogo en Campos Calculados, en la pestaña Nombre se selecciona el nombre del campo a eliminar (o se escribe), nótese que se activa inmediatamente el botón eliminar de la caja de diálogo.
Gráfico dinámico Para crear un gráfico dinámico, debe conectarse a un origen de datos y especificar la ubicación del informe. Seleccione una celda de un rango de celdas o coloque el punto de inserción dentro de una tabla de Excel. Asegúrese de que el rango de celdas tiene encabezados de columna. 1. Para crear un gráfico dinámico, vamos a pestaña Analizar, en el grupo Herramientas, haga clic en Gráfico dinámico. 2. Excel mostrará el cuadro de diálogo Insertar gráfico, como lo muestra la imagen siguiente:
Página 26
Curso Tablas Dinรกmicas en Excel 2016
3. Seleccione el tipo de grรกfico que desee y de clic en Aceptar.
El grรกfico resultante es un grรกfico dinรกmico, con las mismas posibilidades de filtrado que la tabla dinรกmica de donde procede.
Para modificar los campos en el grรกfico solo se necesita activar o desactivar campos de la secciรณn de Campos de Grรกfico Dinรกmico. Si se desea cambiar el tipo de grรกfico debe dar clic derecho y seleccionar la opciรณn Cambiar tipo de grรกfico.
Herramientas de grafico dinรกmico Las herramientas de los grรกficos dinรกmicos se agrupan en tres pestaรฑas:
โ ข
Analizar
Pรกgina 27
Curso Tablas Dinámicas en Excel 2016
•
Diseño
•
Formato
Actualización de datos Los datos de la tabla dinámica es necesario realizar el proceso de actualización, así se contará con la versión más reciente, incluyendo los cambios de los datos dados desde la última actualización, para ello, la tabla dinámica debe conectarse ya sea a otra tabla dinámica en el mismo libro o en otro libro, o bien en otro origen de datos externo, como una base de datos.
Actualizar datos de tabla dinámica de forma manual • • • •
Haga clic en cualquier lugar de la tabla dinámica. Herramientas de tabla dinámica Pestaña Opciones, en el grupo Datos Botón Actualizar
Otras formas son: • Presione ALT+F5 • También puede hacer clic con el botón derecho en la tabla dinámica y, después, hacer clic en Actualizar. Actualización de todas las tablas dinámicas de un libro. • Para actualizar todas las tablas dinámicas del libro, haga clic en la flecha del botón Actualizar y, después, haga clic en Actualizar todo.
Actualizar automáticamente los datos de tabla dinámica al abrir el libro • • • • •
Clic en cualquier lugar de la tabla dinámica. Herramientas de tabla dinámica Pestaña Opciones En el grupo Tabla dinámica, haga clic en Opciones. En el cuadro de diálogo Opciones de tabla dinámica, en la pestaña Datos, active el cuadro de Actualizar datos al abrir el archivo.
Página 28
Curso Tablas Dinรกmicas en Excel 2016
Pรกgina 29
Curso Tablas Dinámicas en Excel 2016
Prácticas Lección Nº 2 Objetivo: El estudiante debe construir tablas dinámicas que permitan aplicar las herramientas de filtros avanzados, agrupar y ordenar datos, así como presentar información con diferentes apariencias mediante el uso de las herramientas ¨Opciones de Tabla Dinámica¨, ¨Configuración de Campo¨ y ¨Subtablas¨. Práctica Nº 5 Instrucciones: Con base en los datos de la práctica Nº 1 ¨Práctica1TD¨ejecute las siguientes actividades para crear una tabla dinámica con el diseño que se le propone: • Ubique en el Campo Columna: País • Ubique en el Campo Fila: Fecha Pedido • Ubique en el Campo Valores: Suma Monto • Filtro: Vendedor • Ordenar : De más reciente a más antiguo • Cambie el nombre del Campo Suma Monto por Total Monto • Renombre la hoja resultante con el nombre de ¨SolPra5¨ Práctica Nº 6 Instrucciones: Con base en los datos de la práctica Nº 1 ¨Práctica 1TD¨ejecute las siguientes actividades para crear una tabla dinámica con el diseño que se le propone: • Ubique en el Campo Fila: Vendedor • Ubique en el Campo Valores: Mostrar valores % total • Filtro: País • Ordenar: Personalizado • Cambie el nombre del Campo Vendedor por Agente • Renombre la hoja resultante con el nombre de ¨SolPra6¨ Práctica Nº 7 Instrucciones: Con base en los datos de la práctica Nº 3 hoja ¨Práctica 2TD¨ejecute las siguientes actividades para crear una tabla dinámica con el diseño que se le propone: • • • •
Ubique en el Campo Fila: Fecha Venta Ubique en el Campo Valores: Suma Total Ventas Agrupar: Mes Renombre la hoja resultante con el nombre de ¨SolPra7¨
Página 30
Curso Tablas Dinámicas en Excel 2016
Tarea Nº 2 Instrucciones: Con base en los datos de la práctica Nº 3 hoja ¨Práctica 2TD¨ejecute las siguientes actividades para crear una tabla dinámica con el diseño que se le propone: • Ubique en el Campo Fila: Nombre • Ubique en el Campo Valores: Total de ventas • Crear subtablas para el campo de: Luis, Olga, Pedro, guarde cada subtabla con el nombre del vendedor • Renombre la hoja resultante con el nombre de ¨SolTa2¨
Página 31
Curso Tablas Dinámicas en Excel 2016
LECCIÓN Nº 3 Tablas porcentuales Las tablas porcentuales permiten calcular el porcentaje del total con una tabla dinámica, al modificar las propiedades de una columna de la tabla dinámica. Pasos: • Agregar una segunda columna que es la que mostrara el porcentaje del total de los datos, esta columna se coloca en valores. En el ejercicio planteado, se desea mostrar el porcentaje del total de las ventas, por lo cual en valores se colocará dos veces la columna Monto.
Cambiar el tipo de cálculo predeterminado Al agregar columnas en Valores, predeterminadamente se realiza el cálculo de suma. Para modificar este valor se realiza lo siguiente: • Clic a la segunda columna agregada (Suma de Monto2), opción Configuración de campo de valor, presentara el siguiente cuadro de dialogo:
Página 32
Curso Tablas Dinรกmicas en Excel 2016
โ ข โ ข
En la secciรณn de Nombre personalizado, se modifica el nombre. En la secciรณn Mostrar valores como, se escoge la opciรณn % del total general. Paso siguiente botรณn Aceptar.
De esta manera se puede calcular el porcentaje del total con una tabla dinรกmica.
Tablas de diferencias Las tablas de diferencias permiten obtener en una Tabla dinรกmica una diferencia entre elementos de un campo, a modo de comparativa de incremento o decremento. Supongamos que se tiene la tabla dinรกmica con los campos Provincia en filas y Total de Ventas en valores. Tal y como lo muestra la siguiente grรกfica.
Pรกgina 33
Curso Tablas Dinámicas en Excel 2016
En el valor correspondiente a las ventas de la provincia San José, mediante el clic derecho se accede a las opciones de Configuración del campo de valor. En la sección de Mostrar valores como, se selecciona la opción Diferencia de, aparecen las opciones de Campo base y Elemento base, en donde se selecciona Campo base Ventas X Provincias y Elemento Base San José.
Por último, se da clic al botón Aceptar y así se obtiene la diferencia de ventas con base en la Región Norte
Vinculación de tablas dinámicas Las tablas dinámicas son independientes, a pesar de que se hayan generado de un mismo origen de datos y se encuentren en la misma hoja. En el caso que se desee presentar diferentes panoramas de datos y controlar todas las tablas dinámicas mediante el uso de un solo filtro podrá hacerlo mediante el uso de la segmentación de datos. Este proceso era antes posible solo mediante la creación de macros. Para la demostración se partirá de la creación de las siguientes tres tablas dinámicas que se muestran en la gráfica.
Página 34
Curso Tablas Dinámicas en Excel 2016
Puede observarse que la primera tabla posee un filtro de página con el campo Fecha, pero este filtro solo es aplicable a la primera tabla dinámica en donde está definido. Insertar la segmentación de datos Para realizar la vinculación de tablas dinámicas es imprescindible agregar la segmentación de datos. Para ello se realiza los siguientes pasos: • Se selecciona cualquiera de las tablas dinámicas (en este caso la primera tabla dinámica) • Herramientas de la tabla dinámica. • Pestaña Analizar • Insertar segmentación de datos Para este ejemplo se seleccionará el campo Fecha y luego botón aceptar, aparece el cuadro donde es posible iniciar la segmentación mediante una fecha seleccionada.
Página 35
Curso Tablas Dinámicas en Excel 2016
En este punto, la segmentación de datos planteada solo afecta a la primera tabla dinámica. Para hacer que la segmentación de datos afecte a todas las tablas dinámicas en necesario realizar la vinculación de tablas dinámicas.
Tablas dinámicas vinculadas. Un filtro de informe es aplicable a una sola tabla dinámica, pero la segmentación de datos posee la característica de conectar varias tablas dinámicas para vincularlas y así aplicar un filtro en común. Para efectuar la vinculación de tablas se realizan los siguientes pasos: • Seleccionar la Segmentación de datos • Herramientas de segmentación de datos. • Pestaña opciones. • Conexiones de informe. Presenta el siguiente cuadro de dialogo
En el cuadro se muestran las tablas dinámicas que están conectadas con la segmentación de datos. Solo resta marcar las tablas dinámicas de la hoja respectiva y dar aceptar. Ya las tablas dinámicas están vinculadas y cualquier variación en la segmentación de datos afectará a las tres tablas. Además, el filtro de informe de la primera tabla dinámica es aplicable a las tablas vinculadas.
Referencias a tablas dinámicas Al crear una referencia a una celda dentro de una tabla dinámica, Excel inserta la función IMPORTARDATOSDINAMICOS, esto en vez de haber insertado la dirección de la celda B5.
Página 36
Curso Tablas Dinámicas en Excel 2016
Debido a que a celda B5 pertenece a una tabla dinámica, convierte la referencia en una formula, con el objetivo de asegurar que se obtendrá el valor adecuado aun cuando cambien la estructura o se actualice los datos de la tabla dinámica. Si se desea que Excel no genere este tipo de referencias y binde una referencia a una celda, se debe hacer el siguiente proceso: • Herramienta de la Tabla dinámica • Analizar • Tabla dinámica • Opciones • Generar GetPivotData (desmarcar esta opción)
Tablas dinámicas con rangos dinámicos. Al existir aumento o disminución de los registros en la base de datos, para que la nueva información se vea reflejada en la tabla dinámica es necesario ampliar o reducir el rango de los datos con el fin de que la tabla dinámica está actualizada con los cambios. Para evitar este proceso de actualización del rango de forma manual, es necesario crear un rango dinámico el cual se ajuste a las modificaciones que pueda experimentar la base de datos. El rango dinámico se creará mediante la opción de nombres de rangos. Nombres de Rango Para la creación del nombre de rango dinámico hacemos los siguientes pasos: • Fórmulas • Administrador de nombres. • Nuevo • Nombre: se le asigna el rango dinámico un nombre, en este caso RangoDinamico • Hace referencia a: empleamos la formula DESREF, de la siguiente manera:
Página 37
Curso Tablas Dinámicas en Excel 2016
• • • •
A: Primera celda de la base de datos (empleada como celda fija) B: valores de 0 para todos los casos. C: Cuenta todas las filas de la base de datos (se toma como base una columna que siempre tenga datos) D: Cuenta todas las columnas de la base de datos (se toma como base la fila de títulos)
Lo que permite la fórmula es crear un rango a partir de una celda fija, la cual asume como tamaño la cantidad de filas y columnas que se desprendan de la función CONTARA, incrementándose según se modifiquen.
Asignar el rango dinámico a una tabla dinámica. Al insertar la tabla dinámica, en la selección de la tabla o rango, se escribe el nombre del rango definido como dinámico: =RangoDinamico.
Página 38
Curso Tablas Dinámicas en Excel 2016
Cruce de dos o más tablas para la creación de una tabla dinámica Para crear una tabla dinámica la cual contenga la referencia cruzada de datos de dos o más tablas, es necesario que las tablas involucradas tengan información en común, estos campos se emplearan en la tabla dinámica para la creación de Relaciones, tal y como se hace en bases de datos. Para la demostración, se partirá de dos tablas denominadas Visitas y Ventas, cada una de ellas en hojas independientes.
Paso siguiente, cada rango de datos debe convertirse en Tabla, ya sea empleando alguna de las opciones: Inicio – Dar formato como tabla o Insertar – Tabla.
Página 39
Curso Tablas Dinรกmicas en Excel 2016
Para mayor facilidad a la hora de emplear los rangos convertidos en tablas, se le darรก a cada Tabla el nombre de rango que esta como identificador en la etiqueta de la hoja. Entonces tendremos las Tablas: Visitas y Ventas.
En una hoja de datos nueva, realizamos los pasos de creaciรณn de tabla dinรกmica.
Para la secciรณn Tabla o rango, escribimos el nombre de alguna de las tablas, en este caso Visitas, y marcamos la caja de verificaciรณn Agregar estos datos al Modelo de datos. Luego damos Aceptar. Para la creaciรณn de las relaciones entre tablas empleamos la siguiente opciรณn: โ ข Herramientas de tabla dinรกmica โ ข Analizar Pรกgina 40
Curso Tablas Dinámicas en Excel 2016
• Cálculos • Relaciones En el Administrador de relaciones, botón Nuevo. • • •
•
Primero se selecciona la tabla secundaria (tabla que NO contiene todos los ID), Tabla Ventas Posteriormente se selecciona la tabla principal (tabla que contiene todos los ID), Tabla Visitas. Ambas tablas se relacionarán mediante el campo en común: ID Botón aceptar
En la zona de la lista de campos de la tabla dinámica, seleccionamos TODAS, y nos muestra las tablas relacionadas y sus campos.
Se plantea el siguiente diseño de tabla dinámica, donde ID y Nombre serán tomados de la tabla donde contenga todos sus datos, es decir de la tabla Visitas.
Página 41
Curso Tablas Dinámicas en Excel 2016
Para finalizar, la tabla dinámica resultante, muestra la información de dos tablas independientes.
DASHBOARD Representa gráficamente los principales indicadores que intervienen en la consecución de los objetivos de negocio, y que está orientada a la toma de decisiones para optimizar la estrategia de la empresa.
Función de un dashboard estratégico • • •
Toma de decisiones: en optimizar la estrategia de la empresa. Identificar el origen de ese dato positivo o negativo que se ha detectado. Es una foto ‘fija’ de los principales indicadores de nuestro negocio.
Herramientas para la creación de un dashboard Para la creación de un Dashboard, se emplean todas las herramientas vistas en el curso de Tablas dinámicas como lo son: • Creación de tablas dinámicas Página 42
Curso Tablas Dinámicas en Excel 2016
• • •
Creación de gráficos dinámicos Segmentación de datos Conexiones entre informes
Página 43
Curso Tablas Dinámicas en Excel 2016
Práctica Lección Nº3 Objetivos: • • •
Diseñar y dar formato a gráficos dinámicos. Construir fórmulas en una tabla dinámica mediante la aplicación de ¨Campos y Elementos Calculados¨ Generar tablas y gráficos dinámicos a partir de bases de datos externas.
Práctica Nº 9 Instrucciones: Con base en los datos de la práctica Nº 1 hoja ¨Práctica 1TD¨ejecute las siguientes actividades para crear una tabla y un gráfico dinámico con el diseño que se le propone: • Ubique en el Campo Fila: Vendedor • Ubique en el Campo Columna: País • Ubique en el Campo Valores: Suma total • Crear un gráfico circular el cual debe indicar el título, l formato de etiqueta debe mostrarse en términos porcentuales con el nombre de los vendedores y dar formato al área del gráfico. • Renombre la hoja resultante con el nombre de ¨SolPra9¨ Práctica Nº 10 Instrucciones: Con base en los datos de la práctica Nº 1 hoja ¨Práctica 2TD¨ejecute las siguientes actividades para crear una tabla y un gráfico dinámico con el diseño que se le propone: • Ubique en el Campo Fila: • Ubique en el Campo Columna: Ventas x Provincia Área de ventas • Ubique en el Campo Valores: Suma total • Crear un gráfico de columnas el cual debe indicar el título y dar formato al área del gráfico y la línea de trazado. • Renombre la hoja resultante con el nombre de ¨SolPra9¨ Práctica Nº 11 Instrucciones: Con base en los datos de la práctica Nº 1 hoja ¨ Práctica1TD ¨ejecute las siguientes actividades para crear una tabla dinámica con el diseño que se le propone: • Ubique en el Campo Columna: Vendedor • Ubique en el Campo Fila: Fecha de Pedido • Ubique en el Campo Valores: Suma Monto • Inserte un campo calculado para calcular las comisiones que cada vendedor se gana, la formula a crear es la siguiente: • = si(monto>20000;monto*3%) • Renombre la hoja resultante con el nombre de ¨SolPra11¨
Página 44
Curso Tablas Dinámicas en Excel 2016
Práctica Nº 12 Instrucciones: Con base en los datos de la práctica Nº 1 hoja ¨ Práctica 2 TD ¨ejecute las siguientes actividades para crear una tabla dinámica con el diseño que se le propone: • Ubique en el Campo Columna: Ventas x Provincia • Ubique en el Campo Fila: Fecha de Ventas, Nombre • Ubique en el Campo Valores: Total Ventas • Inserte un elemento calculado para obtener totales para cada uno de los trimestres, no olvide corregir los errores que produce la inserción de elementos calculados en los totales generales. • Renombre la hoja resultante con el nombre de ¨SolPra12¨ Práctica Nº 13 Instrucciones: Utilizando las herramientas respectivas para crear una tabla dinámica obteniendo datos de bases externas realice las siguientes actividades: • Inserte una tabla dinámica y tome como base alguna de las bases de datos de Access que están en su computadora. Están en la carpeta Excel y Access del escritorio. Con base en esos datos elaborar una tabla dinámica y gráfico.
Página 45
Curso Tablas Dinámicas en Excel 2016
Tarea Nº3 Instrucciones: 1. Crear un libro con el nombre de ¨Tablas Dinámicas¨ 2. Renombrar la Hoja 1 con el nombre de ¨Tarea 3TD¨ y digitar la base de datos que se le propone seguidamente: MES SEMANA CLIENTE ENERO 1 Luis ENERO 2 María ENERO 3 Pedro ENERO 4 María FEBRERO 1 Pedro FEBRERO 2 Pedro FEBRERO 3 Luis FEBRERO 4 Sonia MARZO 1 María MARZO 2 Pedro MARZO 3 Luis MARZO 4 maría ABRIL 1 María ABRIL 2 Luis ABRIL 3 Pedro
ARTICULO CANTIDAD PRECIO TOTAL arroz 6 25 150 sal 15 15 225 mantequilla 2 30 60 frijol 15 40 600 mayonesa 8 15 120 arroz 25 25 625 frijol 1 40 40 salsa 12 42 504 salsa 16 25 400 azúcar 8 20 160 sal 6 15 90 arroz 11 25 275 frijol 10 40 400 sal 5 15 75 mantequilla 5 30 150
3. Construir una Tabla Dinámica con el siguiente diseño: • Ubique en el Campo Columna: Semana • Ubique en el Campo Fila: Cliente y Mes • Ubique en el Campo Valores: Total • Inserte un campo calculado para ofrecer un 3% de descuento sobre el total si la venta es mayor 300. • Inserte un campo calculado para determinar el pago neto • Cambie el nombre del Campo Total por Monto • Crear un gráfico de columnas el cual debe indicar el título y dar formato al área del gráfico y la línea de trazado. • Renombre la hoja resultante con el nombre de ¨SolTa3¨
Página 46
Curso Tablas Dinámicas en Excel 2016
LECCIÓN Nº4 Imprimir un informe de tabla dinámica Para establecer las opciones de impresión, haga clic en el informe de tabla dinámica. Haga clic en la pestaña Analizar de la Cinta de Herramientas de tabla dinámica. En el grupo Tabla dinámica, haga clic en Opciones y luego nuevamente en Opciones. En el cuadro de diálogo Opciones de tabla dinámica, en la pestaña Impresión, seleccione las opciones que desee.
Power Pivot PowerPivot es una herramienta desarrollada por Microsoft que nos da la posibilidad de modelar datos de una forma avanzada. Esta herramienta es un complemento de Excel que podemos usar para analizar de una manera mucho más eficaz grandes cantidades de datos ya que PowerPivot permite combinar grandes orígenes de datos, analiza la información de una forma veloz y permite compartir puntos de vista con facilidad. Es importante aclarar que PowerPivot no viene configurado por defecto en Excel 2016 por lo cual es necesario que la activemos. Para esto vamos al menú Archivo y allí seleccionamos Opciones. En la ventana desplegada seleccionamos la opción Complementos y en el costado inferior derecho elegimos la opción Complementos COM en el campo Administrar. Pulsamos en el botón Ir. En la ventana desplegada será necesarios activar la casilla Microsoft Office PowerPivot Excel. Pulsamos Aceptar y ahora podemos ver que se ha activado la pestaña PowerPivot en Excel. Para conocer cómo funciona PowerPivot en Excel hemos creado los siguientes datos, cada tabla en una hoja diferente:
En este caso hemos creado tres tablas, en una tenemos el nombre del ejecutivo y las unidades que ha vendido en una fecha determinada, en otra tabla tenemos la zona asignada a dicho ejecutivo y en la última tabla el producto asociado a un determinado ID.
Página 47
Curso Tablas Dinámicas en Excel 2016
Es obligatorio que integremos las tres tablas para poder generar un reporte que indique las cantidades vendidas por un ejecutivo en una zona determinada. Seleccionamos cualquier celda de la tabla creada y nos dirigimos a la pestaña PowerPivot y del grupo Tablas seleccionamos la opción Agregar a modelado de datos. Allí se desplegará la ventana de PowerPivot y el entorno que tendremos será similar al siguiente.
Debemos repetir este mismo proceso con las otras dos tablas. Veremos que en la parte inferior de la hoja tenemos las tres tablas integradas.
Relaciones entre tablas Una relación es una conexión entre dos tablas de datos que establece cómo se deben poner en correlación los datos de las dos tablas. Una vez tengamos las tablas integradas al modelado de datos el siguiente paso consiste en crear las relaciones entre las columnas. Para ello damos clic en la opción Vista de diagrama ubicada en la esquina superior derecha en el grupo Ver.
Página 48
Curso Tablas Dinámicas en Excel 2016
Desde esta vista podemos ver las tablas que hemos integrado y con estos datos debemos crear la respectiva relación.
En primer lugar, daremos clic en la línea Ejecutivo de la tabla 2 y la arrastraremos a la línea Ejecutivos de la tabla 1. Lo mismo haremos con la línea ID producto entre la tabla 1 y la tabla 3. Para validar que se ha creado una relación de forma correcta podemos dar clic derecho sobre alguna de las relaciones y seleccionar la opción Editar relación.
Creando una tabla dinámica A continuación, daremos clic en la opción Tabla dinámica ubicada en la pestaña Inicio y veremos el siguiente cuadro. Esta tabla dinámica se diferencia de las comunes ya que está basada en el modelo de datos que hemos definido y podremos acceder a cada una de las tablas usando la opción Campos de tabla dinámica. Al pulsar Aceptar basta con arrastrar los respectivos campos a los valores adecuados. En este caso arrastramos el campo Cantidad vendida de la tabla 1 a Valores, el campo Zona de la tabla 2 a Filas y el campo Producto de la tabla 3 a Filas.
Página 49
Curso Tablas Dinรกmicas en Excel 2016
Pรกgina 50