sábado, febrero 12, 2011

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

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.

24 comentarios:

  1. 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

    ResponderBorrar
  2. 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.

    ResponderBorrar
  3. 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?

    ResponderBorrar
  4. 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 :)

    ResponderBorrar
  5. 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

    ResponderBorrar
  6. 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).

    ResponderBorrar
  7. 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

    ResponderBorrar
  8. 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.

    ResponderBorrar
  9. 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

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

    ResponderBorrar
  11. 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?

    ResponderBorrar
  12. 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.

    ResponderBorrar
  13. 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

    ResponderBorrar
  14. 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.

    ResponderBorrar
  15. 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!!

    ResponderBorrar
  16. 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...

    ResponderBorrar
  17. 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.

    ResponderBorrar
  18. 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.

    ResponderBorrar
  19. Hola, me gustaría saber como puedo utilizar las conexiones de tablas dinámicas, cuando utilizo esta opción únicamente me aparece una tabla dinámica, pero yo tengo dos que quiero vincular, no me aparece la segunda, hay que activar algo previamente?
    Saludos

    ResponderBorrar
  20. Las tablas deben compartir la misma base de datos, lo que no parece ser tu caso.

    ResponderBorrar
  21. Hola...

    Esta muy buena esta ayuda, pero tengo un problema.-

    Quiero trabajar en la Segmentación de Datos, pero no me deja; esta pero en gris y cuando pincho alguna celda de la tabla dinámica sigue en gris y no se desbloquea.-

    Por favor ayuda.-

    ResponderBorrar
  22. Asegurate que el archivo no esté en modo de compatibilidad (versiones anteriores a Excel 2010). De ser así, tenés que guardarlo en como Excel 2010 (xlsx), cerrarlo y volverlo a abrir. Estoy suponiendo que trabajás con Excel 2010 o 2013.

    ResponderBorrar
  23. Hola Jorge,
    Tengo el siguiente problema: Vinculé varias tablas dinámicas con segmentación de datos y conexiones de filtro para que al cambiar una cambiasen todas, pero me equivoqué y añadí una tabla de más que ahora quiero vincular con otro filtro, pero no puedo desvincularla. Me aparece el mensaje "Este control de filtros comparte una memoria caché de segmentación de datos con otros controles de filtros y no se puede conectar ni desconectar por separado". ¿Como hago para borrar todas las conexiones de filtros de segmentación de datos y empezar de nuevo?
    Mil gracias por la formación que nos impartes!!!

    ResponderBorrar
  24. Manel, lo que dice el mensaje es como las tabklas comparten la misma base de datos (caché, es decr, probablemente porque fueron creadas a partir de una misma tabla), hay que conectar a todas o desconectar.
    Una solución sería crear la nueva tabla en forma independiente para lo que te sugiero que veas este post en mi blog.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.