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.

Mejoras en las tablas dinámicas de Excel 2010

Las tablas son dinámicas son, en mi opinión, la mejor herramienta en el “arsenal” de Excel. En todo los que se refiere a analizar datos, en particular si se trata de grandes cantidades de datos, son irreemplazables. Pero siempre hay lugar a mejoras y en esta nota voy a reseñar una de esas mejoras.

Empecemos por crear una tabla dinámica basada, qué novedad!, en la base de datos Northwind que viene con el paquete de Office. Una imagen vale más que cien palabras pero un video vale más que mil, así que mostraremos el proceso en esta captura de pantalla



Una vez creado el informe dinámica, podemos cambiar la forma en que los datos son presentados. En las versiones anteriores de Excel hacíamos esto mediante el menú de configuración de campo de valor. En Excel 2010 la opción Mostrar Como ha sido agregada al menú contextual (el que se abre presionando el botón derecho del mouse)

No sólo tenemos un acceso más cómodo sino que también han sido agregadas seis nuevas opciones:

Vamos a demostrar algunos de los usos, para lo cual agregaremos algunas columnas a la tabla:

Mostrar valores como porcentaje de uno de los campos (de fila como Beverages, por ejemplo o de columna como Qtr1)



Como porcentaje del total de filas principales




Crear totales acumulados




Como ven estamos sacando provecho a la posibilidad de arrastrar más de una vez el mismo campo al área de datos.

Otra mejora importante es la posibilidad de agregar etiquetas de elementos:

Sumar cada enésimo valor de una serie con Excel

Recibí esta consulta: dado un rango de valores, ¿cómo hacer para sumar los valores de cada quinta fila con Excel?

Supongamos estos valores en el rango A1:A20

Esta fórmula suma cada quinto valor en el rango

=SUMAPRODUCTO((RESIDUO(FILA(A1:A20),5)=0)*A1:A20)

Podemos generalizar la fórmula reemplazando la constante 5 de la función RESIDUO por una referencia a una celda

=SUMAPRODUCTO((RESIDUO(FILA(A1:A20),D1)=0)*A1:A20)

Podemos cambiar el valor de D1 para ver el resultado de sumar cada n filas



Esta fórmula funciona de la siguiente manera:

RESIDUO(FILA(A1:A20),D1)=0 da como resultado VERDADERO sólo si el número de fila es múltiplo de 5 (o el número que hayamos ingresado en D1)

Luego al multiplicar los valores de la serie por los correspondientes valores VERDADERO o FALSO obtenemos