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

Autofiltro de campos en tablas dinámicas

Supongamos esta tabla dinámica (basada en la tabla de facturas de la base de datos Northwind que viene con Office)

Si queremos mostrar sólo los datos de un determinado intervalo (digamos entre el 15/08/2008 y el 15/09/2008) podemos desplegar la lista de fechas y elegir las fechas una por una



Pero como estamos usando Excel 2007, la tarea es mucho más fácil. Sencillamente usamos la opción Filtros de fecha-Entre…



Como podemos ver, las posibilidades de filtrar por criterios de fechas son múltiples

Lo mismo es válido si los valores del campo de filas son texto

En Excel 97-2003 no existen, aparentemente, estas posibilidades.

Digo aparentemente porque con un pequeño truco podemos replicar la funcionalidad de autofiltro en tablas dinámicas que existe en Excel 2007.

Seleccionamos la celda adyacente al borde derecho de la tabla dinámica en la fila de los encabezamientos (en nuestro ejemplo C4) y aplicamos Autofiltro con el menú Datos-Filtro-Autofiltro. El resultado es que el Autofiltro es aplicado a las columnas de la tabla dinámica

Ahora disponemos de la funcionalidad "Personalizar", con la cual podemos aplicar el filtro con criterios personalizados