Manual de Excel Intermedio
Modulo 5
Tablas Dinmicas y Grficos Dinmicos
Objetivo
Crear tablas dinmicas, a partir de bases de datos, para analizar la informacin existe en ellas, as
tambin crear grficos dinmicos con informacin obtenida de las tablas dinmicas.
Al finalizar el MODULO, sers capaz de:
Crear tablas dinmicas
Modificar tablas dinmicas
Filtrar informacin.
Incluir formulas en las tablas dinmicas
Generar grficos dinmicos
Contenidos:
Organizacin de bases de datos para creacin de tablas dinmicas
Creacin de tablas dinmicas y realizar modificaciones en ellas.
Filtrar informacin para hacer mas fcil el anlisis.
Realizar grficos dinmicos a partir de las tablas dinmicas ya construidas
Incorporar formulas o funciones a las tablas dinmicas para obtener mejores resultados del
anlisis realizado.
Pgina 1
Manual de Excel Intermedio
Tablas Dinmicas.
Una tabla dinmica combina y compara en forma rpida grandes volmenes de datos.
Permitiendo el anlisis multidimensional de los datos al girar las filas y las columnas
creando diferentes formas de visualizar reportes con los datos de origen. Para lo cual va
desde lo general a lo particular.
Siempre al trabajar las tablas dinmicas se estar teniendo a la vista la siguiente barra de
herramientas.
Recuerde que para todos los ejercicios que se harn en este manual, debe abrir
previamente Microsoft Excel 2007.
Video Demostrativo
Pasos:
1. Posicinese en la Hoja1 del libro abierto de Microsoft Excel 2007.
2. Digite la siguiente informacin en las celdas correspondientes
3. Si deseamos crear una tabla dinmica, debemos posicionarnos en una celda con
datos de la tabla. De no ser as, y estamos fuera de nuestra tabla de datos. Las
opciones de trabajo partirn por tener que seleccionar un rea de trabajo.
4. Para crear una tabla dinmica buscamos la opcin Insertar, de la barra de men y
la ficha Tablas, donde se encuentra el botn Tabla dinmica
Pgina 2
Manual de Excel Intermedio
5. Al presionar el botn tabla dinmica aparecer la siguiente ventana, y se marcaran
todos los datos de la base de datos
6. Adicionalmente, nos pregunta si deseamos ubicar nuestra tabla dinmica en una
nueva hoja de trabajo o en la misma hoja de la tabla, y su ubicacin dentro de
ella. Idealmente es seleccionar la alternativa por defecto que es en una nueva
hoja.
7. Hacemos clic en el botn aceptar.
8. A continuacin se creara una nueva hoja donde se comenzara a definir la tabla
dinmica.
9. En esta nueva hoja, se pueden ver que existe un lugar para la tabla dinmica
dentro de la Hoja4 (hoja creada para la tabla dinmica).
Pgina 3
Manual de Excel Intermedio
10. Una ventana a la derecha de la hoja donde se encuentran los Campos de la
tabla, y las posibles ubicaciones de los elementos de la tabla, como por ejemplo
las Etiquetas de columnas, las Etiquetas de filas, las columnas sobre las cuales
se realizaran operaciones matemticas, Valores, y tambin si se desea filtrar la
informacin por alguna variable no incorporada a la tabla se puede usar el rea de
Filtro de Informes.
Pgina 4
Manual de Excel Intermedio
11. Se selecciona directamente el campo al hacer clic en la box que est a la izquierda
del campo que queremos asignar y este se ubica en columna, filas o valores
automticamente.
12. La otra forma de ubicar un campo en fila columna o valores, es mediante la
seleccin del campo desde la lista y arrastrarlo a los rtulos de fila, columna o
Valores.
13. Primero ubicaremos los campos SEMANA en los Rtulos de columna y CLIENTE
en los Rtulos de fila, llevando el campo MES a los Filtros de informe.
Pgina 5
Manual de Excel Intermedio
14. Al seleccionar los campos ARTICULO y TOTAL, por ser campos con valores
numricos los mueve automticamente al rea de Valores, especificando que
estos se encuentran situados en los Rtulos de columna. Por defecto nos entrega
como valor calculado de los campos seleccionados la SUMA
15. La tabla se extiende demasiado en columnas y se hace de difcil lectura por lo
tanto podemos moverlos a los rtulos de fila, solamente al mover el Rtulo de
columna Valores, a los Rtulos de fila.
16. Al agregar el campo CANTIDAD, este por defecto se agrega en Valores.
Pgina 6
Manual de Excel Intermedio
17. Si se desea se puede modificar en un desglose mayor la tabla, moviendo el campo
ARTICULO desde su posicin a los rtulos de fila, sobre el campo CLIENTE.
18. Se puede modificar el desglose mayor de la tabla, moviendo el campo ARTICULO
desde su posicin bajo el campo CLIENTE.
Pgina 7
Manual de Excel Intermedio
19. Como la tabla se complica demasiado, podemos devolver el campo ARTCULO, a
Valores.
Video Demostrativo
20. El valor entregado para todos los campos ubicados en Valores, por defecto es
Suma, pero es posible modificarlos, a otro tipo de funcin calculada.
21. Para poder hacer modificaciones de la funcin a mostrar, es necesario
posicionarse en la tabla sobre la celda con el campo y su funcin, y hacer clic
sobre el botn de la derecha del mouse y se despliega el siguiente men.
Pgina 8
Manual de Excel Intermedio
22. Al seleccionar la opcin Configuracin de campo de valor, se despliega la
siguiente ventana.
23. El clculo de artculos, por defecto es sumatoria, pero no es lo que buscamos en
esta ocasin; no es el cuantos lo que se busca sino el cuantas veces, se repite
este elemento, por lo que seleccionamos el tipo de clculo Cuenta
Pgina 9
Manual de Excel Intermedio
24. Hacemos clic en el botn Aceptar.
25. La modificacin es inmediata para el tipo de clculo del campo ARTCULO.
26. La modificacin se replica en cada celda de la tabla con el campo ARTCULO,
como tambin en el rea de Valores.
Otra caracterstica de las tablas dinmicas es la posibilidad de realizar filtros por
determinados campos presentes en la tabla, o por campos no incorporados directamente
en la tabla
Video Demostrativo
Pasos:
1. Siguiendo en la misma tabla podemos filtrar nuestra informacin por el campo
MES.
2. Se presiona el men de lista del filtro agregado previamente.
Pgina 10
Manual de Excel Intermedio
3. Se selecciona el mes de Enero y se hace clic en aceptar.
4. Solamente se muestran las sociedades que tuvieron actividad durante el mes de
Enero. Para borrar el filtro solo se debe seleccionar Todas en la misma ventana
de seleccin.
5. Tambin es posible filtrar a partir de los Rtulos de fila, en este caso el campo
CLIENTE.
6. Se puede sacar el clic sobre Seleccionar todo, primero para deseleccionar todos
los clientes, y luego elegir uno en especial, en este caso la Sociedad D
Pgina 11
Manual de Excel Intermedio
7. Resultando un filtro que solo me permite ver al cliente Sociedad D.
8. El proceso de quitar los filtros es exactamente igual a los filtros normales de Excel.
9. Se vern de esta manera todos los registros del campo que haba sido filtrado.
Pgina 12
Manual de Excel Intermedio
Adicionalmente, encontramos en la barra de botones de herramientas de tablas
dinmicas, de la ficha Mostrar u ocultar los siguientes botones.
Cada uno de estos botones muestra u oculta algunos elementos de las tablas dinmicas.
Muestra u oculta la ventana Lista de Campos de Tabla
Dinmica
Permite Expandir o contraer elementos en la tabla dinmica.
Muestra u oculta los encabezados de campo para fila o columna
Grficos Dinmicos
Un informe de grfico dinmico representa grficamente los datos de un informe de tabla
dinmica. Puede modificar el diseo y los datos que se muestran en un informe de grfico
dinmico tal y como se hace con un informe de tabla dinmica. Un anlisis de las tablas
dinmicas, siempre ser ms eficiente si podemos trabajar con los Grficos Dinmicos.
Para poder crear un grfico dinmico, siempre antes debemos partir por hacer nuestra
tabla dinmica, ya que aunque busquemos la opcin de hacerlo de inmediato, solo nos
mostrar la siguiente secuencia.
Video Demostrativo
Pasos:
1. Para crear un grafico dinmico, buscamos la opcin Insertar, de la barra de men
y la ficha Tablas, donde se encuentra el botn Tabla dinmica, con un men de
lista.
Pgina 13
Manual de Excel Intermedio
2. Al usar esta opcin, nos presentar una ventana similar a la de partida para hacer
tablas dinmicas.
3. Al tener seleccionado el rango y definir una nueva hoja para ubicar la tabla
dinmica y el grafico dinmico, nos presentara una hoja de clculo con la siguiente
distribucin.
Por lo tanto es preferible hacer inicialmente la tabla dinmica, y luego el grafico dinmico.
Ya que est esta ultima organizada y definida.
Pgina 14
Manual de Excel Intermedio
Creacin de un grafico dinmico por defecto
Video Demostrativo
Pasos:
1. Al estar en la hoja de la tabla dinmica, y activada la barra de Herramientas de
tabla dinmica, en la ficha Herramientas, esta el botn Grfico Dinmico.
2. Al hacer clic sobre este botn, se activa la alternativa de realizar un grafico basado
en la tabla dinmica.
3. Si seleccionamos los grficos de columna, de columna agrupada.
Pgina 15
Manual de Excel Intermedio
4. Luego hacer clic sobre el botn Aceptar.
5. Al seleccionar el tipo y subtipo de grfico, este se ubica por defecto en la misma
hoja de trabajo de la tabla dinmica.
Los grficos dinmicos, tienen la particularidad de poder interactuar efectivamente con las
tablas dinmicas, ya que podemos modificar los grficos en forma directa y estos
modifican los contenidos mostrados en la tabla dinmica.
Esto a travs de las ventanas del costado derecho de la hoja de clculo, donde est la
ventana Lista de campos de la tabla dinmica, ventana original de la tabla dinmica, y se
agrega la ventana Panel de filtros del grafico dinmico, desde donde podemos realizar
filtros para obtener un mejor anlisis de la informacin de nuestra base de datos.
Pgina 16
Manual de Excel Intermedio
Modificacin de caractersticas de un grfico dinmico
Los grficos dinmicos, al igual que los grficos estndar de Excel, pueden manejar
infinidad de cambios de construccin, apariencia y ubicacin.
Video Demostrativo
Pasos:
1. Modificaremos la ubicacin del grafico dinmico, y activada la barra de
Herramientas del grafico dinmico, en la ficha Ubicacin, esta el botn Mover
Grfico.
2. A continuacin nos mostrara una ventana donde debemos digitar e nombre de la
nueva hoja que contendr el grafico dinmico.
Pgina 17
Manual de Excel Intermedio
3. Luego de escrito el nombre, hacemos clic en el botn Aceptar.
4. As nuestro grfico estar ubicado en una nueva hoja de trabajo.
5. Los cambios que podamos realizar en las ventanas de Lista de campos tabla
dinmica y/o Panel de filtros del grfico dinmico, se replicarn
automticamente en la tabla y grafico dinmico.
6. Si efectuamos un cambio en la Lista de campos de tabla dinmica, como por
ejemplo quitar el campo TOTAL, automticamente se vern las siguientes
modificaciones.
Pgina 18
Manual de Excel Intermedio
7. De igual forma si revisamos la tabla dinmica podemos revisar los cambios
producidos.
8. De igual forma si aplicamos un filtro, sobre la tabla, este se vera reflejado en el
grfico dinmico.
9. Si se aplica el filtro del campo MES, para solo mostrar el mes de Marzo.
10. Nos cambiamos a la hoja de trabajo Grfico Dinmico, y podemos apreciar el
siguiente cambio
Pgina 19
Manual de Excel Intermedio
11. El grafico dinmico, adquiere las modificaciones efectuadas desde la tabla.
Uso de formulas o funciones en Tablas Dinmicas
Una caracterstica, de gran utilidad de las tablas dinmicas, es la posibilidad de agregar
formulas o funciones, que podrn entregar mayor cantidad de resultados al interactuar
con los valores contenidos en la tabla.
Video Demostrativo
Pasos:
1. Nos posicionamos en la Hoja5, donde esta la tabla dinmica.
2. Activamos el campo TOTAL, y borramos el filtro usado en el campo MES.
Pgina 20
Manual de Excel Intermedio
3. En la barra de Herramientas de tabla dinmica, de la ficha Herramientas, esta el
botn calcular.
4.
Este botn, contiene un men de lista que nos ofrece varias alternativas, para
usar, siendo la que usaremos ac la opcin Campo calculado.
5. Al seleccionar esta opcin, nos entrega como resultado una ventana Insertar
campo calculado, donde podremos ingresar el nombre del campo y los datos que
necesitamos para realizar la operacin.
6. En esta ventana, debemos ingresar en el campo Nombre, el nombre del campo
que ser calculado, en este caso VALOR TOTAL, y en el espacio referido a la
Frmula, podemos hacer uso del nombre de los campos para ingresarla, en este
caso =CANTIDAD*PRECIO
Pgina 21
Manual de Excel Intermedio
7. Luego hacer clic en el botn Aceptar.
8. La Hoja5 queda conformada de la siguiente forma, donde se agrega el campo
calculado, en los pasos anteriores, tanto a la ventana Lista de campos de tabla
dinmica, como en la tabla dinmica misma.
9. Se repite, este nuevo campo en forma continua por cada CLIENTE que esta,
como variable principal dentro de las filas, y como Total Suma de VALOR TOTAL
Tambin es posible, ingresar una formula con valores numricos, como si fuera una
formula normal de una tabla de una hoja de clculo.
Video Demostrativo
Pasos:
1. Aplicamos en la Hoja5, donde esta la tabla dinmica, desde a barra de
Herramientas tabla dinmica, en la ficha Herramientas, el botn Formula, la sub
opcin Campo calculado.
2. Nos aparecer la ventana de Insertar campo calculado, donde podremos
ingresar el nombre del campo y los datos que necesitamos para realizar la
operacin, al igual como en el ejercicio anterior.
Pgina 22
Manual de Excel Intermedio
3. En esta ventana, debemos ingresar en el campo Nombre, el nombre del campo
que ser calculado, en este caso IVA, y en el espacio referido a la Frmula,
podemos hacer uso del nombre de los campos para ingresarla, o agregar valores
numricos, en este caso =VALOR TOTAL*19%
4. Hacemos clic en el botn Aceptar.
5. La Hoja5 se mostrara de la siguiente manera con los datos de campos calculados
ingresados.
Pgina 23
Manual de Excel Intermedio
6. Agregndose como en el caso anterior, el nuevo campo IVA, tanto en la Lista de
campos de la tabla dinmica, como en la tabla misma.
Es posible poder ver todas las formulas usadas en nuestra tabla dinmica original,
mediante, un listado de formulas, al cual podemos acceder de la siguiente forma.
Video Demostrativo
Pasos:
1. En la barra Herramientas de tabla dinmica, ficha Herramientas, botn Formulas,
desde su men de lista, al igual que en Campo calculado, podemos obtener la
opcin Crear lista de frmulas
2. Solo con hacer clic sobre dicha opcin en forma automtica se crea una nueva
hoja de trabajo a la izquierda de la Hoja5, que contiene la tabla dinmica, donde
se puede ver un listado de los campos calculados y sus formas de clculo.
Pgina 24
Manual de Excel Intermedio
Pgina 25
