Manual Excel Avanzado - Tablas dinámicas PDF

Preview

Summary

CursoMonográficoExcelTablasdinámicasAlberto Leal Recio Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.Alberto Leal Recio Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados. Introducción Tabla de contenido Tablas dinámicas Creación de tablas dinámicas Herrami...

Description

Curso Monográfico Excel

Tablas dinámicas

Alberto Leal Recio Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

Tablas dinámicas 2015 Tabla de contenido Introducción .................................................................................................................................................. 2 Tablas dinámicas ........................................................................................................................................... 3 Creación de tablas dinámicas ................................................................................................................... 3 Herramientas tabla dinámica.................................................................................................................... 8 Opciones ............................................................................................................................................... 8 Diseño .................................................................................................................................................10 Trabajar con datos existentes ............................................................................................................. 11 Orden datos en tabla dinámica............................................................................................................... 13 Filtros en tabla dinámica ......................................................................................................................... 16 Filtros avanzados en tabla dinámica ....................................................................................................... 18 Subtotales ............................................................................................................................................... 20 Drill down ................................................................................................................................................ 23 Drill to detail ...........................................................................................................................................25 Indicadores.............................................................................................................................................. 26 Campo Calculado ................................................................................................................................26 Campos derivados............................................................................................................................... 28 Agrupar datos .........................................................................................................................................31 Componentes visuales ............................................................................................................................ 33 Gráficos dinámicos .................................................................................................................................. 33 Macros ........................................................................................................................................................39 ¿Dónde se encuentra la herramienta Macros? ...................................................................................... 39 ¿Cómo funciona la herramienta Macros? .............................................................................................. 39 Referencias absolutas ......................................................................................................................... 39 Referencias relativas ........................................................................................................................... 42 Macros en tablas dinámicas.................................................................................................................... 44

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

1

Tablas dinámicas 2015 Introducción Las tablas dinámicas son una herramienta que nos ayudarán a analizar datos y visualizar de una manera más rápida y amigable la información que se desea de una base de datos bajo estudio. Incluso, esta herramienta nos permite hacer diferentes cáculos con los datos ya existentes así como también generar nuevos campos dentro de la tabla dinámica sin necesidad de añadirlo en la base de datos. No hay que olvidar que también tenemos la opción de uso de gráficos que nos ayudarán a mostrar la “foto” del resumen de nuestro análisis. El caso bajo estudio corresponde a las deudas totales y vencidas anuales que tienen diferentes clientes (países) de la empresa ficticia FRUITIPAK.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

2

Tablas dinámicas 2015 Tablas dinámicas Creación de tablas dinámicas Para crear una tabla dinámica es necesario contar con una base de datos, en la cual Excel nos pedirá que los datos estén bien organizados y no existan columnas en blanco entre las columnas donde tengamos información, así como también nombrar el campo Cabecero de cada columna (primer fila).

Antes de generar la tabla dinámica, nos enfocaremos en añadir a la base de datos los datos de Gerente y Gestor que corresponda a cada país, basándonos en la siguientes información.

Empezaremos agregando el campo Gestor utilizando la fórmula de búsqueda BUSCARV.   

Añadimos una columna después de la columna Año. Nombramos esta columna, en la cabacera, como Gestor. Insertamos la fórmula BUSCARV de la siguiente manera: o =BUSCARV(A2,Países!$A$2:$B$12,2,0) o Valor buscado: corresponde al valor buscado en la primera columna de la tabla (puede ser valor numérico o textual). Como ejemplo ARGENTINA, seleccionamos la celda A2de la hoja “basede datos”.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

3

Tablas dinámicas 2015 o

o

o



Matriz buscar en: es la matriz donde se encuentra la información de la cual queremos extaer cual coincida con el valor buscado. Seleccionaremos la matriz de la hoja donde se encuentra la tabla del País con su respectivo Gestor, es decir la celda $A$2:$B$12 (imagen superior). Ojo, la matriz la tenemos que marcar como absoluta, es decir con el símbolo “$” tanto para filas y columnas. Indicador columnas: como nosotros de la matriz seleccionada sabemos que el valor de Gestor se encuentra en la segunda columna y es el dato cual queremos obtener, por lo tanto indicaremos la columna 2. Ordenado: colocaremos “0”, lo que indica que buscamos una coincidencia exacta entre el valor buscado dentro de la matriz.

Copiamos la fórmula para el resto de las celdas de la columna, y observamos que tenemos ya el valor de Gestor.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

4

Tablas dinámicas 2015 Es turno de agregar el campo Gerente del cual nos apoyaremos utilizando la fórmula INDICE y COINCIDIR, ya que tenemos dos variables que dependiendo el periodo y el país tenemos diferente Gerente.   





Añadimos una columna después de la columna Gestor. Nombramos esta columna, en la cabacera, como Gestor. Insertamos la fórmula INDICE y dentro de ella COINCIDIR de la siguiente manera: =INDICE(Países!$G$3:$H$12,COINCIDIR(BaseDatos!A2,Países!$F$3:$F$12,0),COINCIDIR(BaseDat os!B2,Países!$G$2:$H$2,0)) o La fórmula INDICE nos ayuda a encontrar un valor que es la intersección entre una columna y fila deseada, dentro de una matriz. Como ejemplo, queremos buscar el valor de ARGENTINA en el periodo de la 2001 a 2005. Si miramos la tabla de Gestor por países y periodos nosotros sabemos que es Anna, pero a Excel hay que proporcionarle una fórmula para que lo calcule por si mismo, por eso utilizaremos COINCIDIR, para buscar la posición de ARGENTINA (número de fila), y 2001 a 2005 (número de columna). Observamos que hemos incluido el valor de Gerente correspondiente para cada país respecto al periodo indicado.

Copiamos la fórmula para el resto de las celdas de la columna.

Ya que tenemos la base de datos completa, ahora si construyamos la tabla dinámica. 

Nos dirigimos al menú principal y seleccionamos Archivo  Tabla dinámica  Tabla dinámica

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

5

Tablas dinámicas 2015   



Por defecto, Excel ya nos selecciona toda la tabla dinámica. En caso de que no sea así, damos click en la ventanita de Tabla o rango y seleccionamos la base de datos. Dejaremos marcada la casilla de Nueva hoja de cálculo para que nuestra tabla dinámica la tengamos en otra hoja y no interfiera con la hoja de la base de datos. Al final damos click en Aceptar.

Tendremos en el Menú principal las herramientas de la tabla dinámica (Opciones y Diseño), la tabla dinámica vacía por ahora y la lista de campos posibles a seleccionar (estos nombres son las cabeceras de la base de datos).

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

6

Tablas dinámicas 2015 

Dentro de la lista de campos de tabla dinámica: o Seleccionar campos para agregar al informe: estos campos podemos colocarlos ya sea en las áreas de filtro, columna, fila y/o valores. o Filtro de informe: opción para filtrar los datos. (Ejemplo: Colocando País aquí, podemos seleccionar todos los países o solo los de interés) o Etiquetas de columna: opción para colocar el campo deseado a nivel columna. o Etiquetas de fila: opción para colocar el campo deseado a nivel fila. o Valores: indica el valor del campo. Por defecto Excel nos da la sumatoria, pero podemos cambiar el tipo de operación a Máx, %, Promedio, etc.



Si colocamos los campos (arrastrándolos dentro de la lista de campo hacia el área que desamos), obtendremos la siguiente imagen:

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

7

Tablas dinámicas 2015 Herramientas tabla dinámica Opciones Dentro de este apartado tenemos diferentes Opciones para elegir en cuanto a la tabla dinámica. 

Opción de ocultar la Lista de Campo, Botones +/- y el Encabezado de campo de la tabla dinámica.



Opción de cambiar el origen de los datos: Opciones  Cambiar origen de datos  Cambiar origen de datos. Ojo, al cambiar la base de datos tendremos que Actualizar la tabla dinámica para que se vean reflejados los cambios (click en Actualizar).



Opción para limpiar la tabla dinámica: Opciones  Borrar, ya sea para limpiar por completo o solamente los filtros que hemos hecho.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

8

Tablas dinámicas 2015 

Opción para configurar formato (número, texto, moneda, fecha, etc) y tipo de operación del valor (suma, promedio, máx, etc), y también asignarle un nombre personalizado: Opciones  Configuración de campo



Observamos los cambios en el tipo de formato a moneda (euros) y el nombre del campo de Suma deuda total a Monto deuda total.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

9

Tablas dinámicas 2015 Diseño En este otro apartado tenemos diferentes opciones de diseño para elegir en cuanto a la tabla dinámica.  



Estilo de tabla dinámica: diferentes formatos de diseño y colores para obtener una mejor presentación de los datos. Opciones de estilo de tabla dinámica: para sombrear las filas por ejemplo y así distinguir más fácilmente las cifras para cada campo correspondiente.

Diseño: opciones de agregar o desactivar subtotales, totales generales para columna y fila.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

10

Tablas dinámicas 2015 Trabajar con datos existentes Haremos unos ajustes a la tabla dinámica que teníamos por ahora para visualizar la deuda total por período (columnas) y País / Gestor (columna). 

Para ello arrastramos el campo Gestor hacia el área de fila, el campo Periodo al área columna. Necesitaremos mover el campo Año que teníamos, para ello lo arrastraremos hacia el área de filtro. Y nuestra tabla dinámica tiene ahora este aspecto:



Ahora agregaremos el campo %Venc/deuda a nuestra tabla dinámica, arrastrándo el campo de la lista hacia el área de valores.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

11

Tablas dinámicas 2015 

 



Tenemos el formato de %Venc/deuda como número además como operación de SUMA, pero nos interesa tenerlo como % y PROMEDIO. Entonces nos posicionamos en la celda donde tenemos Suma de %Venc/Deud, dentro de la tabla dinámica, damos click derecho y seleccionamos configuración de campo. Seleccionamos del listado la operación Promedio. Después para cambiar el formato a porcentaje, damos click en formato de número, seleccionamos porcentaje, y lo dejamos con 2 decimales.

Damos click en Aceptar y obtendremos los ajustes buscados.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

12

Tablas dinámicas 2015 Orden datos en tabla dinámica Haremos unos ajustes en cuanto al orden de información en la tabla dinámica que teníamos ahora. Pero antes cambiaremos el diseño de la tabla dinámica para visualizar más fácilmente el Gestor y País. 

Nos posicionamos en la tabla dinámica (cualquier celda), nos dirigiremos al menú principal Opciones  Diseño de informe  Mostrar en forma de esquema



Obtendremos el siguiente aspecto de la tabla dinámica:

Por ahora el orden que tenemos en Gestor es de manera ascendente, ahora cambiaremos a descendente.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

13

Tablas dinámicas 2015 

Damos click en el ícono de Gestor dentro de la tabla dinámica y seleccionamos Orden de Z a A.



Vemos que el orden en Gestor ha pasado de América, Asia y Europa a Europa, Asia y América.

Esto mismo podemos hacerlo también para cambiar el orden según el País o los Valores (Monto deuda total y promedio de %venc/deud). Ahora ejemplificaremos como cambiar el orden según el Monto deuda total de manera de menor a mayor según el país. 

Nos posicionamos dentro de la tabla dinámica sobre cualquier celda dentro de la columna Monto deuda total y que corresponda a cualquier país. Ojo, si seleccionamos la celda que corresponda al Gestor el orden se hará de acuerdo a los montos subtotales de Gestor y no es lo que buscamos ahora.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

14

Tablas dinámicas 2015



Vemos que ha cambiado el orden de manera ascendente (antes vs. Después).

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

15

Tablas dinámicas 2015 Filtros en tabla dinámica Los Filtros nos facilitarán desplegar en la tabla dinámica solamente información que deseamos ver.   



De la lista de los campos que se encuentran en el área de Filtro, daremos click sobre el ícono que se encuentre a la derecha de Gerente. Se desplegarán todos los nombres que correspondan al campo Gerente. Podemos seleccionar uno, varios elementos o todos. Por default Excel tiene seleccionado todos los nombres. Como ejemplo, daremos click sobre Eduardo y después en Aceptar.

Observamos que solamente tenemos la información correspondiente a Eduardo, y el filtro dentro de Gerente a pasado de ser (Todas) a Eduardo.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

16

Tablas dinámicas 2015 

Volvamos a seleccionar todos los nombres. Para ello seguimos los pasos anteriores, daremos click en seleccionar varios elementos y después en todos. Y al final click en Aceptar.



Nuestra tabla dinámica quedará de nuevo de esta manera:

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

17

Tablas dinámicas 2015 Filtros avanzados en tabla dinámica  





De la lista de los campos que se encuentran en el área de Fila (Gerente y País), daremos click al ícono derecho que se encuentra a un lado de País. Se desplegarán varias opciones. Seleccionaremos Filtros de valor Diez mejores.

De la tabla generada, seleccionaremos los 2 elementos superior por Monto deuda Total, es decir en la tabla dinámica se visualizarán los 2 mejores países por Gestor. Se puede hacer también con cualquier campo que este dentro de valores (Ejemplo: Promedio de % venc/deud). Damos click en Aceptar.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

18

Tablas dinámicas 2015 

Podemos apreciar que solamente se han desplegado los registros en base a los filtros que hicimos. Además el ícono de País (en el área fila de la tabla dinámica) ha cambiado de forma, esto porque hemos hecho un filtro.



Para borrar el filtro que hemos hecho, damos click en el ícono derecho de País (área fila), y seleccionamos la opción Borrar filtro de “País”.



De nuevo tenemos nuesta tabla dinámica como antes sin filtro.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

19

Tablas dinámicas 2015 Subtotales Los subtotales los podemos visualizar de la siguiente manera: 

Actualmente ya podemos visualizar los subtotales para Gestor, sin embargo podemos desactivarlos posicionándonos sobre el campo Gestor, damos click derecho y des-seleccionamos la opción Subtotal “Gestor”.



Vemos que los subtotales han desaparecido.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

20

Tablas dinámicas 2015 

Para volverlos a visualizar seguimos los siguientes pasos, y seleccionamos Subtotal “Gestor”.



Para agregar otros cálculos de subtotales, nos posicionamos sobre el campo Gestor, damos click derecho y seleccionamos la opción Configuración de campo”.



Seleccionamos la casilla Personalizado, y de las funciones Promedio, Máx y Min. Damos click en Aceptar.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

21

Tablas dinámicas 2015 

Podemos ver que tenemos los subtotales que hemos seleccionado.

Alberto Leal Recio

Copyright © 2015 Manual Excel Avanzado. Todos los derechos reservados.

22

Tablas dinámicas 2015 Drill down Esta opción nos permitirá expander o contraer la informacion de la tabla dinámica. 

Nos posicionamos sobre el campo Gestor, damos click derecho, seleccionamos la opción E...