7/21/2019 U10 Excel Tablas Dinmicas
1/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 1
Copyright octubre de 2014 por TECSUP
Excel avanzado:Tablas Dinmicas
Introduccin
Cuando se trabaja con grandes cantidades de datos en Excel,sean estos de tipo estadstico, financiero, comercial, deservicios, etc. las Tablas Dinmicas pueden ayudar a revisar,analizar, monitorear y generar informes resumidos de manera
ms rpida y confiable que utilizando frmulas y funcionestradicionales.
2
7/21/2019 U10 Excel Tablas Dinmicas
2/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 2
Objetivos
Crear tablas dinmicas. Personalizar el diseo de las tablas dinmicas. Dar formato a las tablas dinmicas. Generar grficos dinmicos.
3
ndice
Concepto de Tabla Dinmica Creacin de una Tabla Dinmica
Preparar los datos Procedimiento de creacin Diseo de la Tabla Dinmica Uso de filtros
Mostrar detalles Edicin de una Tabla Dinmica
Aadir, mover, eliminar campos Funciones resumen Subtotales Clculos adicionales
Formato de Tablas Dinmicas Grficos dinmicos
4
7/21/2019 U10 Excel Tablas Dinmicas
3/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 3
Tablas dinmicas
Una Tabla Dinmica es una herramienta de Excel que permiteanalizar rpidamente grandes cantidades de datos.
Cientos o miles de filas y columnas pueden resumirse en unsolo cuadro o reporte utilizando Tablas Dinmicas.
Por ejemplo, dada una Hoja Excel con la informacin deventas a nivel nacional, una Tabla Dinmica permitiracomparar las ventas por Ciudad, Almacn o Producto.
5
Tablas dinmicas: Utilidad
Utilizando Tablas Dinmicas se puede analizar la informacindesde distintos ngulos.
Por ejemplo: comparar por regiones o por perodos de tiempo;qu ventas estn creciendo, o disminuyendo, etc.
6
7/21/2019 U10 Excel Tablas Dinmicas
4/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 4
Creacin: Procedimiento
Para crear una tabla dinmica: Utilice la opcin Tabla dinmicadel grupoTablas, fichaInsertar.
Cuando previamente se ha creado una Tabla Excel: Seleccione una celda de la Tabla Use la ficha Herramientas de Tabla / Diseo En el grupo Herramientas, haga clic sobre Resumir con Tabla
dinmica
7
Creacin: Procedimiento
En el cuadro de dilogo:Crear tabla dinmica Puede seleccionar un rango o una Tabla previamente creada, si se ha posicionado
en una celda dentro de toda la data, automticamente se coloca el rango. Puede elegir la ubicacin de la Tabla dinmica: en una hoja nueva, o en una
existente.
8
7/21/2019 U10 Excel Tablas Dinmicas
5/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 5
Creacin: Procedimiento
Por defecto, la Tabla Dinmica se crea en una Hoja nueva. En esta hoja aparecen la Tabla Dinmica vaca y un panel con la
Lista de campospara seleccionarlos y asignarles una ubicacin.
9
Creacin: Diseo
En la parte inferior de laLista de campossemuestran las cuatro reas de la TablaDinmica: Filtro de informe: es el dato de mayor nivel
que se quiere analizar. Etiquetas de columna: los datos que se
mostrarn en columnas. Etiquetas de fila: los datos que se
mostrarn en filas. Valores: los datos con que se harn los
clculos numricos: contar, sumar,promediar, etc.
10
7/21/2019 U10 Excel Tablas Dinmicas
6/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 6
Creacin: Diseo
Segn el anlisis que se desearealizar, bastar con arrastrar loscampos al rea correspondiente.
Ntese que no es indispensableutilizar las cuatro reas.
2
31
4
1
2
3
4
Creacin: Ejemplo de diseo 1
Se desea comparar las ventas realizadas en las ciudades de laregin Cusco, para ello se estn utilizando los campos Regin /Ciudad / Categora / Producto / Total Final
12
7/21/2019 U10 Excel Tablas Dinmicas
7/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica
Creacin: Ejemplo de diseo 2 Se desea analizar la venta de papel por regiones, para ello seestn utilizando los campos Categora / Regin / Producto /Total Final
13
Ejercicio 1: Creacin
Abrir el archivoLibreria.xlsxy ubicarse en la hoja Ventas. Ir a la fichaInsertar, grupoTablasyTabla dinmica. La tabla queda marcada en el rangoA1:L454. Elegir la opcinNueva hoja de clculo. Clic enAceptar.
14
7/21/2019 U10 Excel Tablas Dinmicas
8/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica !
Ejercicio 2: Diseo
Calcular la Suma de los Totales Finales por Regin. Seleccionar los campos: Regin, Categora, Producto, Ciudad y Total final
y ubicarlos como se muestran. Renombre la hoja con el nombreTablad1
15
2
3
1 4
5
Uso de Filtros
En el rea Filtro de informe es posible utilizar el botn defiltro para uno o varios de los valores posibles.
Asimismo, los Etiquetas de columna y de fila ofrecenopciones para filtrar y ordenar los valores mostrados.
16
7/21/2019 U10 Excel Tablas Dinmicas
9/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica "
Ejercicio 3: Filtros
En la hojaTablaD1 Mostrar los datos de la regin Cuscoy luego
Aceptar. Luego seleccionar en los etiquetas de fila
CorrectoryLapicero.
17
Mostrar detalles
Una vez creada la Tabla Dinmica, puede ser necesariomostrar los datos que originan cada valor mostrado.
Para ello haga clic derecho sobre al valor que desea detallar: Use la opcinMostrar detalles Los datos originales se mostrarn en una nueva Hoja
18
7/21/2019 U10 Excel Tablas Dinmicas
10/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 10
Edicin: Aadir, eliminar, mover campos
Para aadir o eliminar campos dela Tabla Dinmica bastar conmarcarlos o desmarcarlos de laLista de campos.
Para mover campos de un rea aotra, bastar con arrastrarlos en elpanel inferior de la Lista decampos.
19
Ejercicio 4: Detalles y edicin
En la TablaD1, ir a la celdaB11 hacer clic derecho yseleccionar Mostrardetalles.
Cmbiele el nombre a estahoja y colqueleDetalles.
Copie el contenido de lahoja TablaD1 a una nuevahoja de clculo y colquelede nombreTablaD2.
En la hoja TablaD2,deshacer filtros y modifiquela ubicacin de los camposcomo se muestra en lasiguiente imagen.
20
7/21/2019 U10 Excel Tablas Dinmicas
11/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 11
Edicin: Cambio de funcin resumen
Por defecto, las cantidadesnumricas son sumadas. Peroesta funcin puede modificarse.
Haga clic derecho sobre una celdaque contenga valores: La opcin Resumir datos por
permite elegir otras funciones. La opcin Configuracin de
campo de valor muestra eldilogo con las funciones
disponibles:Cuenta, Promedio,Mx, Mn, Producto, etc.
21
Funciones resumen disponibles
Funcin: Descripcin
Suma Suma todos los datos
Cuenta Cuenta nmeros, textos, y errores
Promedio Suma de los datos dividida por la cantidad de valores
Mx Muestra el valor ms altoMn Muestra el valor ms bajo
Producto Multiplica todos los datos
Contar nmeros Cuenta solo nmeros
Desvest Calcula la desviacin estndar de una muestra
Desvestp Calcula la desviacin estndar de la poblacin
Var Calcula la varianza de un muestra
Varp Calcula la varianza de la poblacin
22
7/21/2019 U10 Excel Tablas Dinmicas
12/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 12
Ejercicio 5: Cambio de Funcin
En la hojaTablaD2, elimine el campoProducto. Cambie la funcin Suma de Total Final por la funcin Promediode Total
Final. En la celdaB6haga clic derecho, seleccione Resumir datos pory seleccione: Promedio.
23
Edicin: Dos funciones resumen
Para utilizar dos funciones resumensobre un mismo valor, por ejemploSuma y Promedio, incluya en el reaValores el mismo elemento dos veces.El segundo valor aparecer con el sufijo2.
Para cambiar de funcin, se har clicderecho sobre una celda de la columnaa cambiar y elegir otra funcin resumen(por ejemplo, Promedio).
Luego podr modificar el encabezadode la columna por uno ms apropiado.
24
7/21/2019 U10 Excel Tablas Dinmicas
13/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 13
Ejercicio 6: Uso de dos funciones
En la hojaTablaD2, agregue en el rea de Valores el campo Total Final 2veces ms.
A la columna que muestra el encabezado de Suma de Total Final2,deber cambiarle su funcin para que cuente (Recuento).
Modifique el encabezado, elimine el nmero 2.
Edicin: Ubicacin de subtotales
Los subtotales puedenocultarse, o mostrarse porencima o por debajo de lasfilas que representan.
Su ubicacin se puede
establecer mediante el botnSubtotales del grupoDiseo de la ficha Diseode las Herramientas deTabla dinmica.
26
Grupo
7/21/2019 U10 Excel Tablas Dinmicas
14/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 14
Edicin: Funcin de subtotal
Los subtotales son sumados pordefecto. Es posible elegir unafuncin distinta: Haga clic derecho sobre el rtulo
de fila que desea modificar. Use la opcin Configuracin de
campo
En este dilogo puede elegir laopcin Personalizado y una ovarias funciones de subtotal.
27
Ejercicio 7: Subtotales
En la hojaTablaD1se cambiar la forma de mostrar los subtotales. En la ficha Diseo, de las Herramientas de la tabla dinmica, seleccione Subtotalesy
Mostar todos los subtotales en la parte inferior el grupo .
Ubicarse en la celda A8, hacer clic derecho y seleccionar la opcinConfiguracin de Campo, seleccionar Personalizado y en l lasfuncionesSumayPromedio.
28
7/21/2019 U10 Excel Tablas Dinmicas
15/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 15
Edicin: Clculos adicionales
Existen otros clculos que permitencomparar valores, con otros de lamisma Tabla dinmica.
El dilogo Configuracin decampo valor,mostrado antes, tienela fichaMostrar valores como, conlas opciones:
Diferencia de % de % de la diferencia de Total en % del total de filas % del total de columnas % del total general ndice
29
Clculos adicionales disponibles
Clculo Ejemplo de uso
Diferencia deComparar las ventas de cada ciudad con las ventas en una ciudad enparticular
% deMostrar las ventas de cada regin como porcentaje de las ventas deotra regin
% de la diferencia deVer el cambio porcentual de las ventas de cada mes respecto al mesanterior
Total en Mostrar las ventas anuales a la fecha, por producto o regin
% del total de fi las Ver las ventas de cada producto como porcentaje de las ventas totales
% del total de columnasVer las ventas de cada regin como porcentaje de las ventas a nivelnacional
% del total generalMostrar la venta regional de cada producto como un porcentaje deltotal de ventas
ndiceComparar las ventas regionales de cada producto con las ventas porproducto, las ventas regionales y las ventas totales
30
7/21/2019 U10 Excel Tablas Dinmicas
16/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 16
Ejercicio 8: Clculos adicionales
En la hoja TablaD1,adicione el campo TotalFinalen el rea de Valores.
Seleccione la celdaC7, clicderecho, seleccionarResumir Valores por yMs opciones.
Seleccione la fichaMostrar valores como, yde la lista seleccione: %del total general.
Personalice el nombre dela columna con: % delTotal General.
31
Edicin: Actualizar los datos
Es posible que, luego de construida la Tabla dinmica, losdatos originales sufran modificaciones.
Para que estas modificaciones se reflejen en la TablaDinmica debe utilizar: La fichaOpcionesde lasHerramientas de tabla dinmica
BotnActualizardel grupoDatos
32
7/21/2019 U10 Excel Tablas Dinmicas
17/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 1
Ejercicio 9: Actualizacin de Datos
Vaya a la hoja Ventas (datosoriginales) y ubquese en la celdaL152, modifique el datos de2077.74 a 20770.74.
Regrese a la hoja TablaD1y noteque el valor en B8 no se hamodificado.
Para que se refleje este cambiodeber ir a la Ficha Opciones,
Grupo datos y seleccionarActualizar. Note ahora cmo los valores de la
tabla dinmica han variado.
Formato de Tabla Dinmica
En la ficha Diseo, grupoDiseo, el botn Diseo deinformepresenta tres diseospredefinidos: Compacto (diseo por defecto). Esquema.
Tabular.
34
Compacto
Esquema
Tabular
7/21/2019 U10 Excel Tablas Dinmicas
18/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 1!
Formato de Tabla Dinmica
Adicionalmente, la ficha Diseopresenta los grupos Estilosy Opcionesde estilospara mejorar la presentacin de la Tabla Dinmica.
Tambin estn disponibles todas las opciones de formato de una Hojaconvencional.
35
Ejercicio 10: Formato y diseo
En la hoja TablaD2, modifique el diseo del informe,seleccione el tipo: Esquema.
Ahora cambie el estilo de la tabla dinmica, puede escogeralgn estilo predeterminado de la ficha Diseo, grupo Estilosde la Tabla Dinmica.
7/21/2019 U10 Excel Tablas Dinmicas
19/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 1"
Grfico dinmico
Un grfico dinmico sebasa en los datos de unaTabla dinmica.
Para crear un grficodinmico puede utilizar elgrupo Herramientas de laficha Opciones de laHerramientas de tabladinmica.
Las facilidades para elegirel tipo de grfico y darleformato son similares a lasde los grficosconvencionales.
37
Grfico dinmico
Excel mostrar dentro del GrficoDinmico opciones para poderaplicar los filtros que serequieran.
Tenga presente que el Grficodinmico y la Tabla dinmica
estn vinculados: Los cambios en la estructura o enlos filtros de uno se reflejarn enel otro. Sin embargo, si se eliminauno de ellos, el otro permanecer.
Se pueden crear varios grficosdinmicos de la misma Tabladinmica, y ubicarlos yformatearlos independientemente.
38
7/21/2019 U10 Excel Tablas Dinmicas
20/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 20
Ejercicio 11: Grficos Dinmicos En la hojaTablad2elimine el campo Ciudad y en el campo Valores solo deje la Suma del
total. En la ficha Herramientas de Tabla dinmica, en la ficha Opciones, grupo herramientas,
seleccione Grfico Dinmico. Seleccione el grficoColumna agrupada con efecto 3D Aceptar. En la ficha Presentacin del Grfico Dinmico, oculte la leyenda.
39
Excel Avanzado
Tablas dinmicas
Campos
Filtro deinforme Rtulos decolumna
Filtrarvalores
Que son
Permiten
Contiene
Rtulos de fila Valores
Analizardatos
irven para
!reas
"uestran
Contiene
Funcionesresumen
C#lculosadicionales
$iene
%ise&o
$ales como
Compacto
's(uema
$a)ular
uma* Cuenta*Promedio* "a+* "in*
Producto*%esvest* Var
',emplos
%iferencia de* - de*$otal en* - de fila*
- de columna* - detotal
',emplos
Vinculadascon
.r#ficosdin#micos
7/21/2019 U10 Excel Tablas Dinmicas
21/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 21
Glosario
Registro: conjunto de atributos o caractersticas (campos) deun ente (persona, objeto, concepto). Ejemplo: el enteCLIENTE puede tener los atributos Cdigo, Apellido,Direccin, Fecha de Nacimiento, etc.
Campo: un atributo o caracterstica con un valor especfico.Ejemplo: Precio, Fecha de venta.
Filtro: opcin para ocultar / mostrar las filas que cumplan unaespecificacin determinada.
41
Fernando Rosino Alonso. Excel 2010. Grupo AnayaComercial. 2010.
Walkenbach, John. La Biblia de Excel 2010. Grupo AnayaComercial. 2010.
Harvey, Greg. Microsoft Office Excel 2010 for dummies. WileyPublishing Inc. 2010.
Se ha utilizado como consulta la pgina de MicrosoftLatinoamrica: http://office.microsoft.com/es-es/excel-help/informacion-general-
RZ101773335.aspx?CTT=1§ion=1
Referencias Bibliogrficas
7/21/2019 U10 Excel Tablas Dinmicas
22/23
Informtica Aplicada: Unidad 10 Tecsup
Dpto. de Informtica 22
http://office.microsoft.com/es-es/excel-help/novedades-de-excel-2010-HA010369709.aspx?CTT=1#_Toc274394078
http://www.uv.mx/personal/llopez/files/2013/03/Manual-Microsoft-Office-Excel-2010.pdf
http://www.aulaclic.es/excel2010/index.htm http://www.aulafacil.com/excel-2010/curso/Temario.htm
Enlaces
Preguntas
1. Por defecto, las Tablas dinmicas se crean en la mismaHoja que los datos?
2. Dnde se ubica la opcin Tabla dinmica?3. Dnde se ubica la opcin Resumir como tabla dinmica?
4. Cuando los datos originales sufren modificaciones seactualiza automticamente la Tabla dinmica?
44
7/21/2019 U10 Excel Tablas Dinmicas
23/23
Informtica Aplicada: Unidad 10 Tecsup
Respuestas
1. Por defecto, las Tablas dinmicas se crean en la mismaHoja que los datos?
No. Se crean en una Hoja nueva.
2. Dnde se ubica la opcin Tabla dinmica? En la ficha Insertar, grupo Tablas.
3. Dnde se ubica la opcin Resumir como tabla dinmica? En la ficha Diseo de Herramientas de tabla, grupo Herramientas
4. Cuando los datos originales sufren modificaciones se
actualiza automticamente la Tabla dinmica? No. Hay que utilizar el botn Actualizar del grupo Datos de lasHerramientas de tabla dinmica.
45