tablas dinámicas en excel 2010

Upload: lone1016

Post on 20-Jul-2015

1.427 views

Category:

Documents


0 download

TRANSCRIPT

Tablas Dinmicas en Excel 2010

Juan Carlos Compean Pulido Subdireccin de Tecnologas de Informacin Octubre 2010

OBJETIVOS Entender la importancia de las Tablas Dinmicas para simplificar el anlisis de grandes cantidades de datos. Comprender las reas de una Tabla Dinmica y como cruzar y filtrar la informacin para obtener los resultados deseados.

INTRODUCCIN A LAS TABLAS DINMICASPara muchas personas estas herramientas son una funcionalidad desconocida de Excel, incluso usuarios avanzados desconocen su gran potencial. Podra decirse, sin exagerar que las tablas dinmicas son una de las herramientas ms potentes de Excel. Una tabla dinmica nos permite sintetizar los datos de que disponemos para poder analizarlos. Ante el desconocimiento del potencial de Excel, no es de extraar que algunas empresas gasten mucho dinero y tiempo en desarrollos a la medida para obtener los mismos resultados que podran obtener sabiendo utilizar las tablas dinmicas.

EJEMPLO PRCTICO DE TABLAS DINMICASLa siguiente tabla agrupa un listado de expositores, ciudades donde han trabajado, sueldo pagado y horas trabajadas:

En esta tabla estn los datos que necesitamos para responder a las siguientes preguntas: Cuantas conferencias se han dado en Monterrey? Cunto ha ganado Nuria Velzquez y Cristina Garca?

Cul es el nmero de horas que Nuria Velzquez ha dado en Guadalajara? Cul es el sueldo promedio de cada conferenciante? Y el sueldo medio por ciudad?

Y as, con este tipo de preguntas podamos seguir y seguir. Puede hallarse la solucin sin emplear tablas dinmicas, pero es muy complicada, laboriosa y, sobre todo, de un slo uso, esto es, podemos disear complicadas frmulas empleando funciones lgicas, por ejemplo, pero slo valdrn para el caso concreto que se analiza. Al contrario, la solucin mediante tablas dinmicas, con apenas unos cambios que tardan pocos segundos, se pueden responder a varias preguntas distintas. Vamos a responder una a una a cada una de las preguntas que hemos hecho utilizando tablas dinmicas: Cuantas conferencias se han dado en Monterrey? Lo primero que haremos ser, ahora y siempre, seleccionar el rango de datos que queremos usar para la tabla dinmica. En este caso sera el rango A1:D15. Es buena prctica seleccionar las columnas completas A, B, C y D, pulsando [CTRL] + el botoncito de su rtulo, para que, en el caso que la tabla se agrande, tenga ms valores, las tablas dinmicas sigan valindonos. Una vez seleccionado el rango, en nuestro caso, las columnas A, B, C y D. Nos vamos al men Insert, opcin PivotTable

En principio, dejaremos las opciones que hay seleccionadas: dos para el rango de datos, que ya hemos seleccionado previamente, y otras dos para la ubicacin de la tabla dinmica, que por defecto es en una nueva hoja. Le damos a aceptar, y nos aparece, ya en la hoja nueva, una serie de opciones y mens para generar nuestra tabla dinmica.

Elegiremos el campo Ciudad y, para saber cuntas conferencias ha habido, cualquier otro campo, ya que depender de la funcin que le asignemos,. En este caso hemos elegido el campo Expositor. Arrastramos el campo Ciudad al rea Etiqueta de la Fila (Row Labels) y el campo Expositor al rea Valores (Values).

Vemos que en el rea Valores, pone Count of Expositor, en un desplegable (marcado en azul en la imagen). En este caso lo dejaremos como est porque lo que queremos es que cuente el nmero de expositores, pero ah podramos elegir otras funciones como la funcin SUMA (SUM), PROMEDIO (AVERAGE), etc. Llegados a este punto se nos ha generado la siguiente tabla dinmica:

En la que se ve el nmero de conferencias por cada ciudad y el total. La pregunta fue cuantas conferencias se han dado en Monterrey, por lo que basta con elegir Monterrey del deplegable rotulado como Row Labels (Etiquetas de Fila).

El resultado es una tabla similar a la anterior, pero en la que slo aparece el dato de Monterrey:

De este modo, podremos obtener fcilmente la informacin por las localidades que queramos. Las Tablas Dinmicas permiten variar su contenido y la manera de agruparlo sin tener que manipular la informacin que analizan. De esta manera permiten obtener la informacin que nos interesa muy dinmicamente. Hasta el punto anterior podemos ver dinmicamente cuantas conferencias se han dado en las diferentes ciudades que han visitado los expositores, pero, Cmo podramos saber que expositores han visitado las ciudades analizadas? Existen 3 maneras de cruzar o relacionar las categoras de la informacin (columnas en nuestra tabla inicial): 1. Utilizando el Report Filter de nuestra tabla dinmica.

2.

Desglosando o cruzando los renglones por medio de las Etiquetas de Columna (Column Label)

3.

Utilizando una estructura de rbol, agregando ms categoras a las Etiquetas de Fila (Row Labels)

Depender la utilidad que necesitemos para saber cul de las tres opciones anteriores satisface mejor nuestra necesidad: La opcin 1, utilizando el filtro de reporte, permite crear versiones reducidas de una tabla centrada en algn tema o categora en particular. En este caso podramos analizar las ciudades que ha visitado cada expositor, Cristina Garca, en nuestro ejemplo. La opcin 2, permite una visin ms parecida a coordenadas de todas las ciudades que se han visitado y cuantas conferencias han dado ah nuestros expositores. La opcin 3, permite navegar y analizar detalladamente cada ciudad en un despliegue visual mucho ms amigable para su estudio.

RESPONDIENDO LA INFORMACIN REQUERIDA.

Con esta informacin bsica, se pueden reacomodar las categoras de nuestros datos para responder las preguntas planteadas al inicio. Cuantas conferencias se han dado en Monterrey?

Se han dado 3 conferencias. Cunto ha ganado Nuria Velzquez y Cristina Garca?

Nuria Velzquez $42,968.86 y Cristina Garca $28,220.00 Cul es el nmero de horas que Nuria Velzquez ha dado en Guadalajara?

Nuria Velzquez ha dado 8 horas en Guadalajara.

Cul es el sueldo promedio de cada expositor?

Cristina Garca, $9,406.67; Juan Lpez, $6,480.95; Martha Rodrguez, $5,278.50; Nuria Velzquez, $8,593.77 Y el sueldo medio por ciudad?

Cancn, $10987.00; Guadalajara, $5924.67; Mazatln, $9778.91; Mrida, $6781.47; Monterrey, $6703.27; Morelia, $6052.00; Villahermosa, $7701.00 Una vez entendiendo el potencial de informacin que tienen los posibles acomodos de las tablas dinmicas, se pueden combinar los valores en una estructura ms compleja que pueda responder las preguntas anteriores de una manera ms eficiente:

GRFICOS ASOCIADOS A UNA TABLA DINMICAAhora veremos cmo podemos generar un grfico dinmico y la utilidad que esto tiene. Usaremos los mismos datos que en el ejemplo anterior:

Se trata de una tabla con los expositores de diversas conferencias, el sueldo cobrado en cada una de ella, la ciudad donde fue impartida y las duracin en horas. Lo que buscamos es la representacin de estos datos de acuerdo a un criterio que pueda variar (de ah el nombre de Grficos Dinmicos) en funcin de nuestras preferencias, sin tener que modificar la tabla de datos. Generemos un grfico dinmico con la distribucin de horas por ciudad. Lo primero que haremos ser seleccionar el rango de A1:D15 o las columnas A, B, C y D, completas (para que todo lo que hagamos nos sirva aunque se aadan nuevos datos a partir de la fila 16).

Despus vamos al men Insert > PivotTable > PivotChart. Nos aparece un men emergente en el que podemos elegir el rango sobre el que queremos generar el grfico dinmico (y que ya hemos seleccionado en el punto anterior); y la ubicacin del mismo.

Dejamos todo como est y pulsamos aceptar. Nos aparece en la hoja de clculo unas ventanas similares a cuando estamos creando una tabla dinmica. Lo que haremos en nuestro caso ser arrastrar el campo Ciudad a la zona Axis Fields (Categories) y el campo Horas a la zona Values.

Para que en el grfico aparezca el total de horas por ciudad, nos aseguramos que la funcin Sum of Horas aparezca en la zona Values y no Count of Horas. El grfico se ha generado exitosamente y puede ser manipulado desde el mismo grfico:

0