domingo, 5 de julio de 2009

Tips para pivot tables o tablas dinamicas

<<-- Atras

Ocultar subtotales


A medida que se van agregando mas campos a la tabla dinámica la lectura de la misma se va tornando más complicada debido a la cantidad de subtotales, pero esto podemos evitarlo ocultándolos, simplemente haciendo clic con el botón derecho sobre el campo y posteriormente en la opción “Subtotal”. En el ejemplo retiramos los subtotales de las columnas: Year y Product group. Con lo que se redujo en gran medida el tamaño de la tabla.


ocultar subtotales pivot table Microsoft Office Excel 2007


Show Pages


Una de las opciones que considero muy útiles es la de poder dividir nuestra pivot en diferentes hojas de Excel según un campo especifico, podemos hacerlo a la antigua mediante (Copy & Paste) o bien utilizar la opción “Show Pages” de Excel.


mostrar pagina de filtro de informes pivot table Microsoft Office Excel 2007

En la ventana emergente seleccionaremos el campo por el cual deseamos dividir la pivot, “Importante: este campo debe encontrarse en la zona de página de la pivot”. Los datos contenidos en él se filtraran y se crearan nuevas hojas para cada una de las opciones. En el caso del Customer se filtrara por Televisa, Sony, Cisco,Cablecom y se creara una hoja nueva con cada uno de esos nombres.


2 mostrar paginas de filtro de informes pivot table Microsoft Office Excel 2007


El resultado se verá más o menos como en la imagen (Ejemplo mostrado para Cablecom) se nota mejor en el archivo que puedes descargar desde AQUÍ.


tabla dinamica pivot table Microsoft Office Excel 2007


Configuración campo de valor…


En algunas ocasiones Excel no reconoce correctamente el tipo de dato de nuestra columna por lo que puede poner una función contar en lugar de una sumatoria, o bien, puede que no requiramos la función sumar, si no, un conteo o alguna otra función. Para esto utilizaremos la opción “Configuración campo de valor…” que podemos visualizar haciendo clic con el botón derecho en la zona de valores de la tabla dinámica.


configuracion campo de valor pivot table o tabla dinamica Microsoft Office Excel 2007


En el menú emergente seleccionaremos la nueva función que deseamos aplicar al campo ya sea un conteo, promedio, producto, etc.


2 configuracion campo de valor pivot table o tabla dinamica Microsoft Office Excel 2007


En el ejemplo muestro la opción de suma y conteo sobre el mismo campo (Sales USD)


campo suma, comteo pivot table o tabla dinamica Microsoft Office Excel 2007


Formato


Excel 2007 cuenta con diferentes tipos de formato previamente definidos, podremos acceder a ellos seleccionando la tabla dinámica y dentro de la ficha “Diseño” eligiendo el más adecuado a nuestras necesidades. Muy sencillo.


diseño pivot table o tabla dinamica Microsoft Office Excel 2007


Rangos dinámicos


A menudo cuando trabajamos con tablas dinámicas es requerido agregar nuevos registros, pero al hacer esto, nuestra tabla no actualiza el rango lo que ocasiona que estos nuevos datos no se vean reflejados.


Esto se soluciona convirtiendo las celdas de datos en Excel, a una verdadera tabla, simplemente seleccionando el rango, vamos a la ficha insertar y elegimos la opción tabla. Nos preguntara el rango que deseamos, lo seleccionamos si no está definidoy después de hacer clic en aceptar, la tabla quedara creada.


crear tabla pivot table o tabla dinamica Microsoft Office Excel 2007


Luego insertamos la tabla dinámica como normalmente lo haríamos, y de ese modo, cada que agreguemos un registro, simplemente actualizando la pivot se agregaran los datos, ya no será necesario cambiar el rango. Como se puede ver en la imagen se agrego un nuevo registro para 2010 y después de actualizar la pivot ya puede verse el nuevo dato.


actualizar pivot table o tabla dinamica Microsoft Office Excel 2007


Campo calculado


Dentro de las opciones de las insertar campo calculado pivot table o tabla dinamica Microsoft Office Excel 2007tablas dinámicas podemos también agregar un campo que calcule alguna operación con el uso de otros campos existentes. Por ejemplo podemos calcular el 15% del total de las ventas. Vamos a Herramientas de tabla dinámica, Formulas, Campo calculado.

Normal 0 21 false false false ES X-NONE X-NONE MicrosoftInternetExplorer4

En la ventana seleccionamos el campo requerido (Sales USD) de la lista de campos, con doble clic, veremos que se agrega a la formula. Para terminar la operación agregamos el resto de la formula (* 0.15) y haciendo clic en aceptar.

formula de campo calculado pivot table o tabla dinamica Microsoft Office Excel 2007

El resultado sería el siguiente:

pivot table o tabla dinamica Microsoft Office Excel 2007

En los campos calculados solo podemos utilizar operaciones básicas, es decir, no se pueden utilizar formulas como vlookup, IF, etc.


Espero que les gustara acepto sus comentarios y correcciones, que seguro se me fue algo por ahí. Termino preguntando ¿Y tu como haces las tablas dinámicas? ¿Qué añaderias a este tutorial?


<<-- Atras

3 comentarios:

Anónimo dijo...

muy bueno si se quiere comenzar a usar esta herramienta...

Anónimo dijo...

excelente, la verdad muchas gracias

Anónimo dijo...

me parece excelente!! muchas gracias, apenas empiezo a usar tablas dinámicas