Tablas Dinámicas en Excel 2010 – Segmentación de Datos

sábado, febrero 12, 2011

En la nota anterior empecé a exponer las mejoras introducidas en Excel 2010 en comparación a las versiones anteriores. Vamos a tratar otra mejora substancial al funcionamiento de las tablas dinámicas: la segmentación de datos (Slicers, en la interfaz en inglés).

Como en la nota anterior usamos la base de datos NorthWind para crear nuestro ejemplo



La posibilidad de filtrar los datos de la tabla existe en todas las versiones de Excel



Si bien el filtrado ha sido mejorado considerablemente en Excel 2007, todavía existían muchas limitaciones:
Si queremos elegir más de un ítem tenemos que marcar y desmarcar uno por uno (en Excel 2007 podemos usar filtros con criterios)


  • Los filtros no reflejan la organización jerárquica de los datos Si los datos están organizados en forma jerárquica (en nuestro ejemplo Tipo de Producto-->Producto). Por ejemplo, si agregamos los productos al capo de la filas y filtramos por Beverages (bebidas), seguiremos viendo todos los productos de todas las categorías.





  • No podemos configurar la apariencia de los filtros, ni su ubicación. Esto limita en gran medida la posibilidad de usarlo directamente en nuestros dashboards, en particular si el usuario no tiene suficiente experiencia en el uso de Excel.



  • Y, tal vez lo más importante, si creamos más de una tabla para la misma base de datos, tenemos que filtrar cada tabla por separado.


Todas estas limitaciones han sido superadas en Excel 2010 con la introducción de la segmentación de datos.
Para activar la segmentación de datos tenemos que seleccionar alguna de las celdas de la tabla dinámica



Vamos a empezar por insertar dos segmentos



Como puede verse, podemos mover y cambiar el diseño de los filtros. Cuando activamos uno de los filtros, aparece en la cinta el menú Herramientas de Segmentación de Datos. No me voy a extender aquí en todas la posibilidades pero si llamar la atención al menú Configuracion



Ahora crearemos una segunda tabla basada en los mismos datos para mostrarlos en forma porcentual. Para hacer el uso más evidente, vamos a exhibir en las tablas los productos.



Naturalmente queremos aplicar un filtro simultáneamente a ambas tablas. Esto la hacemos con el menú Conexiones de tabla dinámica (en la cinta de Herramientas de Segmentación o con el menú contextual del cuadro de segmentación)




Este video muestra dos efectos del filtro: 1) el filtro ProductName se sincroniza con la elección en el filtro CategoryName; 2) ambas tablas muestran los cambios en forma sincronizada



Creo que todo esto, junto con las posibilidades de configuración personalizada (y todavía no hablamos de Vba), muestran que en Excel 2010 tenemos un herramienta aún más poderosa para el desarrollo de nuestros informes o dashboards.

18 comments:

Sócrates 02 marzo, 2011 05:37  

Buenas noches Sr. Jorge primero que nada deje felicitarnos por excelente labor en pro de la enseñanza y el conocimiento que puede impartir en otros. Excelente su labor.

Lo molesto para solicitarle una ayuda en cuanto a un problema que tengo que resolver.

En el archivo que adjunto tenemos un matriz con una series de datos. Dichos datos son zonas y productos. Cantidad de clientes que compran determinado SKU (producto). Que quiero realizar? Una formula que cuando seleccione la zona, me traiga cuantos clientes compran por SKU. Es como realizar un autofiltro pero no con cada producto, ni manualmente. Debe ser automatico. Me explique?

Cualquier cosa me notifica para explicarle mejor. Muchas gracias y feliz noche

Jorge L. Dunkelman 02 marzo, 2011 12:15  

No puedes adjuntar archivos en un comentario. Puedes enviarme tu consulta con el archivo siguiendo las directivas que aparecen en el enlace Ayuda.
Para hacerlo con fórmulas puedes usar, por ejemplo, SUMAPRODUCTO o funciones base de datos.

Anónimo,  02 marzo, 2012 12:10  

Hola Jorge,
Me gustaría saber si es posible hacer filtros sobre filtros en tablas dinámicas en excel 2007. Es decir, si me genero una tabla dinamica a partir de un archivo csv. o hoja de datos, que al seleccionar un filtro, por ejemplo Tipo Material, en el siguiente filtro, solo me enseñe las opciones de filtrado de Material que se corresponen con el Tipo de Material seleccionado.
No se si me he explicado bien. Es eso posible de alguna forma?

Jorge L. Dunkelman 03 marzo, 2012 10:23  

No existe esa posibilidad, por lo menos que yo coozca. Tal vez se pueda crear la situación usando Vba.
Otra posibilidad sería enfocar la situación desde otra perspectiva. Por ejemplo, en lugar de de tablas dinámicas podríamos usar nombres definidos para crear listas desplegables dependientes (hay varias notas en el blog sobre el tema).
Podríamos también combinas los dos enfoques y usar el resultado de las listas desplegables para filtrar la tabla dinámica (usando Vba).
También podríamos usar Excel 2010 :)

Domingo,  30 marzo, 2012 22:49  

Buenas tardes.

Consolide varias hojas en SQL Query en excel para generar una tabla dinamica pero seleccione hojas sin datos porque los voy metiendo mes con mes pero al pedir suma me genera puros ceros como puedo evitar eso

lo agradecere mucho

Jorge L. Dunkelman 31 marzo, 2012 11:09  

Domingo,

supongo que e refieres al MS Query para consolidar las hojas. Empezaría por fijarme en el MS Query si la consulta da resultados. Por supuesto que para que eso suceda por lo menos una de las hojas debe contener algún dato. Además deden estar construidas como base de datos planas (con encabezamientos en la primer fila).

Domingo,  27 abril, 2012 00:45  

Hola.


ya encontre el problema cuando baja la informacion la baja como texto, por lo cual si la cuenta y no la suma, como le hago para que la informacion la baje como numero


muchas gracias

Jorge L. Dunkelman 27 abril, 2012 17:58  

Lo más sencillo sería cambiar la función en la tabla dinámica a SUMA. Excel aplica CUENTA en forma automática si alguna de las celdas del campo de los datos contiene cualquier valor que no sea numérico o está vacía.

Fabio ALEJANDRE 05 junio, 2012 17:50  

Hola Jorge!
Utilizo mucho Tablas dinamicas en Excel y me interesó mucho tu articulo.
Ahora tengo otro problema y no se como resolverlo:
Tengo una tabla con nombres de persona y edades y debo obtener una tabla dinamica con la cantidad de personas de menos de 5 años, entre 5 y 15, entre 15 y 30, entre 30 y 60 y mas de 60 años.
Con lo que yo conozco, puedo contar por cada edad distinta cuantas personas hay, pero no por rango de edades.
Te agradeceria que me des alguna pista para poder resolverlo.
MUCHAS GRACIAS

Anónimo,  02 agosto, 2012 13:07  

Muchas gracias por el articulo. Muy útil y perfectamente explicado! Qué fácil es todo cuando se explica bien.
Muchas gracias otra vez!

Anónimo,  29 octubre, 2012 19:39  

Hola, estoy muy agradecida por todo lo que he aprendido con sus artículos. Le expongo una duda,¿se puede insertar un slicer pero qeu tome los datos de la hoja de excel en forma horizontal?

Mi problema es que en mi tabla dinámica necesito que el filtro sea CON IVA y SIN IVA. pero por las formúlas necesitaría que el filtro tome los datos de forma horizontal ¿es posible?

Jorge L. Dunkelman 30 octubre, 2012 20:06  

Excel 2010 genera slicers (segmentación de datos) en base a los campos de filas y de columnas. Así que si tienes un campo "con/sin iva" en el área de columnas puedes generar un slicer para ese campo.

Anónimo,  22 agosto, 2013 23:34  

Jorge:
Tengo dos tablas dinamicas con un segmento combinado de valores de Años, donde puedo seleccionar de que año voy a mostrar los datos en ambas Tablas.
En otra hoja tengo una tabla normal como un tipo de dashboard, y en una celda tengo una validacion de datos de una lista de los años de mis datos.
Yo necesito que cuando seleccione un valor en la celda con validacion, se seleccione ese valor en la segmentacion de las tablas dinamicas.
Agradezco tu ayuda

Jorge Dunkelman 23 agosto, 2013 10:58  

La única forma de hacerlo en con una macro. Como comprenderás, no puedo poner toda la explicación en el marco de un comentario.
Pero, ¿por qué no hacerlo al revés? Segmentación de datos cambia el valor del filtro del reporte dinámico. Este está ligado a una celda que puedes ligar a la tabla del dashboard.

Agustina Fernández 01 octubre, 2013 21:03  

Hola Jorge muy buena tu explicación.
Puede ser que no funcione la segmentación de datos cuando la tabla de datos que da origen a la tabla dinámica este en otro archivo o sea un access?
Como puedo hacer para solucionarlo?
Muchas gracias!!

Jorge Dunkelman 02 octubre, 2013 11:20  

Agustina,
no creo. La segmentación de datos trabaja sobre la tabla dinámica (sobre el cache), de manera que no creo que ese sea tu problema. Pero trataré de investigar el tema un poco. Siempre hay lugar para sorpresas...

Iñaki Díez 10 mayo, 2014 11:58  

Hola Jorge,

Tengo un cuadro de mandos creado con Excel 2007; me han actualizado el equipo de la empresa a 2010 y me gustaría saber si es posible aplicar la segmentación de datos a las tablas dinámicas creadas con anterioridad.

Gracias.

Jorge Dunkelman 16 mayo, 2014 14:36  

Supongo que si. Si al abrir el archivo se ve [Modo de Compatibilidad] al aldo del nombre, puedes convertir el archivo a versión 2010 y trabajar con todas las funcionalidades.

Publicar un comentario

Seguidores

Google+ Followers

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP