Manual de Excel Intermedio Pgina 1 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. Manual de Excel Intermedio Pgina 2 Tablas Dinmicas. Unatabladinmicacombinaycomparaenformarpidagrandesvolmenesdedatos. Permitiendoelanlisismultidimensionaldelosdatosalgirarlasfilasylascolumnas creando diferentes formas de visualizar reportes con los datos deorigen.Para lo cual va desde lo general a lo particular. Siempre al trabajar las tablas dinmicas se estar t eniendo a la vista la siguiente barra de herramientas. Recuerdequeparatodoslosejerciciosqueseharnenestemanual,debeabrir 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 posicionarnosen una celda con datosdelatabla.Denoseras,yestamosfueradenuestratabladedatos.Las opciones de trabajo partirn por tener que seleccionar un rea de trabajo.4.Para crear una tabla dinmicabuscamos la opcin Insertar, de la barra de men y la ficha Tablas, donde se encuentra el botn Tabla dinmica Manual de Excel Intermedio Pgina 3 5.Al presionar el botn tabla dinmica aparecer la siguiente ventana, y se marcaran todos los datos de la base de datos 6.Adicionalmente, nospregunta si deseamos ubicar nuestra tabla dinmica en una nuevahojadetrabajooenlamismahojadelatabla,ysuubicacindentrode ella.Idealmenteesseleccionarlaalternativapordefectoqueesenunanueva hoja. 7.Hacemos clic en el botn aceptar. 8.Acontinuacinsecrearaunanuevahojadondesecomenzaraadefinirlatabla dinmica. 9.Enestanuevahoja,sepuedenverqueexisteunlugarparalatabladinmica dentro de la Hoja4 (hoja creada para la tabla dinmica). Manual de Excel Intermedio Pgina 4 10. UnaventanaaladerechadelahojadondeseencuentranlosCamposdela tabla, y las posibles ubicaciones de loselementos de la tabla, como porejemplo 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. Manual de Excel Intermedio Pgina 5 11. Se selecciona directamente el campo al hacer clic en la box que est a la izquierda delcampoquequeremosasignaryesteseubicaencolumna,filasovalores automticamente. 12. Laotraformadeubicaruncampoenfilacolumnaovalores,esmediantela seleccin del campo desde la listayarrastrarloa los rtulos de fila, columnao Valores. 13. Primero ubicaremos los campos SEMANA en los Rtulos de columna yCLIENTE en los Rtulos de fila, llevando el campo MES a los Filtros de informe. Manual de Excel Intermedio Pgina 6 14. AlseleccionarloscamposARTICULOyTOTAL,porsercamposconvalores numricoslosmueveautomticamentealreadeValores,especificandoque estos se encuentran situados en los Rtulos de columna. Por defecto nos entrega como valor calculado de los campos seleccionados la SUMA 15. Latablaseextiendedemasiadoencolumnasysehacededifcillecturaporlo tantopodemosmoverlosalosrtulosdefila,solamentealmoverelRtulode columna Valores, a los Rtulos de fila. 16. Al agregar el campo CANTIDAD, este por defecto se agrega en Valores. Manual de Excel Intermedio Pgina 7 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. Manual de Excel Intermedio Pgina 8 19. Como la tabla se complica demasiado, podemos devolver el campoARTCULO, a Valores. Video Demostrativo 20. El valorentregado para todos los campos ubicadosen Valores, por defectoes Suma, pero es posible modificarlos, a otro tipo de funcin calculada. 21. Parapoderhacermodificacionesdelafuncinamostrar,esnecesario posicionarseenlatablasobrelaceldaconelcampoysufuncin,yhacerclic sobre el botn de la derecha del mouse y se despliega el siguiente men. Manual de Excel Intermedio Pgina 9 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 Manual de Excel Intermedio Pgina 10 24. Hacemos clic en el botn Aceptar. 25. La modificacin es inmediata para el tipo de clculo del campo ARTCULO. 26. LamodificacinsereplicaencadaceldadelatablaconelcampoARTCULO, como tambin en el rea de Valores. Otracaractersticadelastablasdinmicaseslaposibilidadderealizarfiltrospor determinados campos presentes en la tabla, o por campos no incorporados directamente en la tabla Video Demostrativo Pasos: 1.Siguiendoenlamismatablapodemosfiltrarnuestrainformacinporelcampo MES. 2.Se presiona el men de lista del filtro agregado previamente. Manual de Excel Intermedio Pgina 11 3.Se selecciona el mes de Enero y se hace clic en aceptar. 4.Solamentesemuestranlassociedadesquetuvieronactividadduranteel mesde Enero. Para borrarel filtro solo se debe seleccionar Todas en la misma ventana de seleccin. 5.TambinesposiblefiltrarapartirdelosRtulosdefila,enestecasoelcampo CLIENTE. 6.Se puede sacar el clic sobre Seleccionar todo, primero para desel eccionar todos los clientes, y luego elegir uno en especial, en este caso la Sociedad D Manual de Excel Intermedio Pgina 12 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. Manual de Excel Intermedio Pgina 13 Adicionalmente,encontramosenlabarradebotonesdeherramientasdetablas dinmicas, de la ficha Mostrar u ocultar los siguientes botones. Cada uno de estos botones muestra u oculta algunos elementos de las tablas dinmicas. MuestrauocultalaventanaListadeCamposdeTabla Dinmica Permite Expandir o contraer elementos en la tabla dinmica. Muestra u oculta los encabezados de campo para filao columna Grficos Dinmicos Un informe de grfico dinmico representa grficamente los datos de uninforme 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. Parapodercrearungrficodinmico,siempreantesdebemospartirporhacernuestra tabladinmica,yaqueaunquebusquemoslaopcindehacerlodeinmediato,solonos mostrar la siguiente secuencia. Video Demostrativo Pasos: 1.Para crear un grafico dinmico,buscamos la opcin Insertar, de la barra de men ylafichaTablas,dondeseencuentraelbotnTabladinmica,conunmende lista. Manual de Excel Intermedio Pgina 14 2.Al usar esta opcin, nos presentar una ventana similar a la de partida para hacer tablas dinmicas. 3.Altenerseleccionadoelrangoydefinirunanuevahojaparaubicarlatabla 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. Manual de Excel Intermedio Pgina 15 Creacin de un grafico dinmico por defecto Video Demostrativo Pasos: 1.Alestarenlahojadelatabladinmica,yactivadalabarradeHerramientasde 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. Manual de Excel Intermedio Pgina 16 4.Luego hacer clic sobre el botn Aceptar. 5.Al seleccionarel tipo y subtipo de grfico,este se ubica por defectoen la misma hoja de trabajo de la tabla dinmica. Los grficos dinmicos, tienen la particularidad de poder interactuar efectivamente con las tablasdinmicas,yaquepodemosmodificarlosgrficosenformadirectayestos modifican los contenidos mostrados en la tabla dinmica. Estoatravsdelasventanasdelcostadoderechodelahojadeclculo,dondeestla 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. Manual de Excel Intermedio Pgina 17 Modificacin de caractersticas de un grfico dinmico Losgrficosdinmicos,aligualquelosgrficosestndardeExcel,puedenmanejar infinidad de cambios de construccin, apariencia y ubicacin. Video Demostrativo Pasos:1.Modificaremoslaubicacindelgraficodinmico,yactivadalabarrade Herramientasdelgraficodinmico,enlafichaUbicacin,estaelbotnMover Grfico. 2.A continuacin nos mostrara unaventana donde debemos digitare nombre de la nueva hoja que contendr el grafico dinmico. Manual de Excel Intermedio Pgina 18 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.LoscambiosquepodamosrealizarenlasventanasdeListadecampostabla dinmicay/oPaneldefiltrosdelgrficodinmico,sereplicarnautomticamente en la tabla y grafico dinmico. 6.SiefectuamosuncambioenlaListadecamposdetabladinmica,comopor ejemploquitarelcampoTOTAL,automticamentesevernlassiguientes modificaciones. Manual de Excel Intermedio Pgina 19 7.Deigualformasirevisamoslatabladinmicapodemosrevisarloscambios producidos. 8.Deigualformasiaplicamosunfiltro,sobrelatabla,esteseverareflejadoenel grfico dinmico. 9.Si se aplica el filtro del campo MES, para solo mostrar el mes de Marzo. 10. NoscambiamosalahojadetrabajoGrficoDinmico,ypodemosapreciarel siguiente cambio Manual de Excel Intermedio Pgina 20 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 deagregar formulasofunciones,quepodrnentregarmayorcantidadderesultadosalinteractuar 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. Manual de Excel Intermedio Pgina 21 3.EnlabarradeHerramientasdetabladinmica,delafichaHerramientas,estael botn calcular. 4. Estebotn,contieneunmendelistaquenosofrecevariasalternativas,para usar, siendo la que usaremos ac la opcin Campo calculado. 5.Alseleccionarestaopcin,nosentregacomoresultadounaventanaInsertar campo calculado, donde podremos ingresar el nombre del campo y los datos que necesitamospara realizar la operacin. 6.En estaventana, debemos ingresarenel campo Nombre, el nombre del campo quesercalculado,enestecasoVALORTOTAL,yenelespacioreferidoala Frmula, podemos hacer uso del nombre de los campos para ingresarla, en este caso =CANTIDAD*PRECIO Manual de Excel Intermedio Pgina 22 7.Luego hacer clic en el botn Aceptar. 8.LaHoja5quedaconformadadelasiguienteforma,dondeseagregaelcampo calculado,enlospasosanteriores,tantoalaventanaListadecamposdetabla dinmica, como en la tabla dinmica misma. 9.Serepite,estenuevocampoenformacontinuaporcadaCLIENTEqueesta,como variable principal dentro de las filas, y como Total Suma de VALOR TOTAL Tambinesposible,ingresarunaformulaconvaloresnumricos,comosifuerauna formula normal de una tabla de una hoja de clculo. Video Demostrativo Pasos: 1.AplicamosenlaHoja5,dondeestalatabladinmica,desdeabarrade Herramientastabladinmica,enlafichaHerramientas,elbotnFormula,lasub opcin Campo calculado. 2.NosaparecerlaventanadeInsertarcampocalculado,dondepodremos ingresarelnombredelcampoylosdatosquenecesitamospararealizarla operacin, al igual como en el ejercicio anterior. Manual de Excel Intermedio Pgina 23 3.En esta ventana, debemos ingresarenel campo Nombre, el nombre del campo quesercalculado,enestecasoIVA,yenelespacioreferidoalaFrmula, podemos hacer uso del nombre de los campos para ingresarla, oagregar 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. Manual de Excel Intermedio Pgina 24 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. Esposiblepodervertodaslasformulasusadasennuestratabladinmicaoriginal, 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, desdesumendelista,aligualqueenCampocalculado,podemosobtenerla opcin Crear lista de frmulas 2.Soloconhacerclicsobredichaopcinenformaautomticasecreaunanueva hoja de trabajoa 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. Manual de Excel Intermedio Pgina 25