Ordenar valores del filtro de informe en tablas dinámicas

Las tablas dinámicas presentan cuatro áreas a las cuales podemos arrastrar los campos de la base de datos que la alimenta: etiquetas de filas, etiquetas de columna, valores y filtro de informe

Por defecto, los valores de las áreas de etiquetas, columnas y filtro del informe están ordenadas alfabéticamente, de A a Z, y el orden de los valores depende del orden del área de las filas.
Para cambiar el orden por defecto de las eetiquetas de fila y de columna podemos usar el menú de edición de estas áreas

El filtro de informe no cuenta con un menú de edición y por lo tanto no tenemos una forma directa de cambiar el orden de los valores (que por defecto es de A a Z). Pero podemos hacerlo con un pequeño truco.
En nuestro ejemplo tenemos un único campo, País, en el área de filtro de informe

y como puede apreciarse está ordenado de A a Z. Para cambiar este orden reubicamos el campo del filtro de informe en el área de filas

Ahora podemos, por ejemplo, elegir "Ordenar de Z a A", apretar Aceptar y luego volver a ubicar el campo en el área de filtro de informe

con lo cual hemos invertido el orden por defecto del campo.

Ahora supongamos que queremos que los valores del filtro de informe aparezcan ordenados de mayor a menor según el total de ventas de cada país. Para lograrlo empezamos por quitar temporariamente el campo Ciudad y ubicar el campo País en el área de las filas. Abrimos el menú de edicion del campo y en "Más opciones de ordenación" elegimos "Descendente..." y "Por Ventas"

Pero esto no es suficiente. Ahora tenemos que apretar el botón "Más opciones" (en el ángulo inferior izquierdo del formulario, en la imagen está parcialmente oculto) y quitar la marca de la opción "Ordenar automáticamente cada vez que se actualice el informe)

Apretamos Aceptar para que el cambio cobre efecto

Tablas dinámicas - uso de Rangos de Consolidación Múltiples

A partir de la versión 2007 de Excel, la funcionalidad "Rangos de consolidación múltiples" desapareció del asistente de de tablas dinámicas:

Pero el "viejo" asistente no desapareció; se puede acceder a él agregando el icono a la barra de herramientas de acceso rápido

En esta nota voy a mostrar una solución posible a la consulta de un lector y al pasar algunas consideraciones sobre cuál es la mejor manera de organziar datos en Excel.

Empecemos por presentar el problema. Nuestro lector tiene esta matriz de datos

pero para poder consolidar datos y realizar cálculos, necesita organizar los datos de esta manera (más adelante veremos si realmente ésta es la mejor manera)

Si nuestra matriz contiene muchos datos la posibilidad de cortar y pegar o arrastrar los rangos puede ser poco práctica. En ese caso podemos echar mano a la funcionalidad Rangos de consolidación múltiples del viejo asistente de tablas dinámicas.

El primer paso es abrir el asistente y seleccionar los rangos a consolidar

Al apretar el botón "Finalizar" obtenemos la tabla

Ahora podemos seleccionar la tabla dinámica y usar Copiar-Pegar Valores para poder usarla como una matriz de datos regular.

Este video muestra el proceso


Los datos quedan organizados como un matriz de datos "plana". En lugar de la forma "plana" de la tabla dinámica, donde cada "Tag" es una columna, podemos organizar los datos en forma tabular con una única columna para los "Tags". Esto reduce el número de columnas de la tabla y hace que sea más fácil de manejar para nuestros cálculos.
Para convertir la tabla dinámica de rangos consolidados en una matriz de datos tabular hacemos un doble clic en el total general

Como resultado se abre una nueva hoja con la matriz de datos

Ahora podemos cambiar el encabezado de las columnas A, B y C y eliminar la columna D.

Tablas Dinámicas La Quinta Dimensión - libro electrónico

Aclaración: este post promueve el libro electrónico Tablas Dinámicas La Quinta Dimensión" de Miguel Caballero Sierra y Fabian Torres Hernandez. Por cada venta originada en este blog recibo una pequeña comisión.

Sin lugar a dudas la funcionalidad más eficiente de Excel para analizar datos son las tablas dinámicas. El dominio de las tablas dinámicas convierte al usuario intermedio en avanzado. Miguel Caballero Sierrra y Fabián Torres Hernández han publicado su manual Tablas Dinámicas La Quinta Dimensión para ayudar a todo usuario novato o intermedio dominar esta herramienta indispensable para el análisis de datos.

He aquí la descripción del libro en las palabras de los autores y una oferta especial al los lectores de JLD Excel

Tablas Dinámicas La Quinta Dimensión

Adquiere una nueva perspectiva sobre la funcionalidad estrella de Excel para analizar datos.

• ·        Aprende Tablas Dinámicas desde 0 hasta convertirte en un experto.
• ·        Personaliza tus Tablas Dinámicas de una manera única.
• ·        Construye aplicaciones y soluciones increíbles utilizando Tablas Dinámicas
• ·        Descubre Tips, rápidos y fáciles de usar para optimizar tu trabajo.
• ·        Automatiza y añade características a tus Tablas Dinámicas con Macros
• ·        Explora Power Pivot, El futuro del Análisis de Datos.
• ·        Utiliza los Video Tutoriales para facilitar el aprendizaje.
• ·        Apóyate en los archivos de práctica para afianzar tus conocimientos.

Enfocado a:

Usuarios de Excel, profesionales sin importar su campo de estudio o cualquier persona que esté interesada en aumentar sus conocimientos y habilidades, no es necesario que cuenten con conocimientos previos sobre Excel y en particular Tablas Dinámicas.  

Contenido detallado del Libro

El libro Tablas Dinámicas, La Quinta Dimensión es una obra enfocada en conseguir que cualquier persona interesada aprenda y posteriormente aplique los conocimientos adquiridos en cualquier situación que se requiera, brindamos al usuario:

• ·        Ejemplos fáciles de entender y aplicados.
• ·        Archivos de práctica.
• ·        Video tutoriales.
• ·        Anexos complementarios.

Este libro tiene como objetivo brindar un conjunto de trucos y macros enfocados a Tablas Dinámicas, trucos que servirán tanto para la estética del reporte, solución de tareas y automatización mediante macros, así como una primera guía a Power Pivot. La finalidad del presente texto es tomar un paso más avanzado si se quiere ir más allá de los cursos convencionales, en el siguiente enlace puedes verificar los trucos, macros, y demás elementos que componen el libro:

http://issuu.com/miguelcaballerosierra/docs/tablas_din__micas__look_inside

Audiencia JLD Excel

Debido a la gran acogida del libro, aplicaremos una promoción para los lectores del blog JLD Excel, consiste en un bono de descuento del 20% para adquirir el libro disponible hasta el 1 de agosto de 2015, usando el código JORGE en el bono.

Si tienes alguna duda de cómo aplicar puedes seguir las siguientes instrucciones, pero no olvides que estará disponible solamente por los próximos 15 días:

https://www.youtube.com/watch?v=oIqTvZxXeok

Filtrar por etiquetas en tablas dinámicas con dos criterios

Los filtros de etiquetas de los campos de las tablas dinámicas

son similares a los filtros de Autofiltro (la imagen de arriba es una tabla dinámica basada en la tabla de la imagen de abajo)

pero con un diferencia. Autofiltro nos permite filtrar el campo combinando dos criterios con O o Y (filtro cuando se cumple por lo menos un criterio o filtro cuando ambos criterios deben cumplirse);

pero el filtro de etiquetas del campo de la tabla dinámica solo permite aplicar un criterio

En nuestro ejemplo tenemos una tabla dinámica que muestra las ventas por ciudades.Supongamos que queremos filtrar la tabla dinámica para que muestre sólo ciudades que empiecen con la letra A o la letra B.
El filtro de etiqueta de las tablas dinámicas sólo nos permite filtrar por una de las letras

a diferencia del Autofiltro

Podemos superar este inconveniente con un pequeño truco. Empezamos por seleccionar el rango que contiene las etiquetas de los campos desde la primer celda libre a la derecha de la tabla dinámica

Ahora aplicamos Autofiltro (del menú Datos-Filtro). Las flechas del Autofiltro se "superponen" a las del filtro de las etiquetas (y además agregan un filtro al área de los datos).
Ahora, al activar la flecha del filtro, veremos las opciones de Autofiltro

El proceso se puede ver en este video:

Una alternativa a los elementos calculados de las tablas dinámicas

Cuando resumimos datos con tablas dinámicas podemos echar mano, entre otras, a dos herramientas valiosas: los campos y los elementos calculados.
Los elementos calculados tienen en ciertas situaciones un comportamiento un tanto enervante, por decirlo de alguna manera.

Veamos esta situación:

El año de cada venta aparece en el campo Año, por lo que si queremos calcular la diferencia entre ambos años para cada país, podemos hacerlo usando creando un elemento calculado,

Apretamos "Aceptar" y Excel crea un nuevo campo con la diferencia de los elementos del campo Año

Como mis observadores lectores habrán notado el reporte dinámico tiene un filtro que permite ver los resultados por vendedor. Elegimos la vendedora Anne Dodsworth y obtenemos este reporte

Podemos ver que también los países donde la compañera Anne no ha realizado ventas aparecen en el informe.

Una posible solución a este problema es crear un nuevo campo en el base de datos con las diferencias. La solución no es trivial e implica el uso intensivo de fórmulas, lo que puede afectar la eficiencia de nuestro modelo.

Podemos usar un pequeño truco para superar este problema. El primer paso es poner por segunda vez el campo "Venta total" en el área de los datos:

El próximo paso es seleccionar alguna de las celdas de la columna 2015 del campo Ventas total que acabamos de agregar y seleccionamos la opción "Diferencia de..." del menú "Mostrar valores como"

y luego elegimos como campo de base la el campo "Año"

El resultado es

Todo lo que nos queda por hacer es ocultar la columna D. Ahora el reporte de las ventas de Anne muestra sólo los países donde se realizaron ventas

BUSCARV con dos parámetros

Un lector me consulta si es posible hacer búsquedas en una tabla de acuerdo a dos parámetros. La respuesta es positiva y vamos a mostrar las distintas posibilidades en esta nota.
Aclaremos que el título de la nota es un poco engañoso ya que además de BUSCARV mostraremos soluciones con tablas dinámicas y con las funciones INDICE y COINCIDIR, en forma natural y en forma matricial.

Supongamos una tabla con tres columnas: artículo, fecha y precio. Cada artículo aparece varias veces pudiendo variar la fecha y el precio como en esta tabla:

1 - Solución con tabla dinámica (o "solución rápida").

Creamos una tabla dinámica basada en nuestra tabla de datos

En el área de las filas ponemos los campos Artículo y Fecha y el campo Precio en el área de los datos.
Ordenamos el campo Fecha de más reciente a más antiguo

con lo cual las fechas más reciente aparecerán al principio de cada grupo de artículos.

Ahora agregamos un cuadro de segmentación de datos y ocultamos todas la filas de la tabla excepto la que contiene el primer dato; agregamos algunos formatos para mejorar la presentación y ya tenemos nuestro modelo dinámico que siempre mostrará el último precio de cada artículo

La ventaja de esta solución reside en que no usa fórmulas sino tablas dinámicas por lo que funciona velozmente también con tablas de gran tamaño.

2 - Solución con BUSCARV y columna auxiliar.

Para usar BUSCARV en forma natural (es decir, no matricial) debemos crear una columna auxiliar con valores únicos por lo que combinamos el código del articulo con la fecha del precio )la columna ID)

Para esta solución es indispensable que la tabla esté organizada de menor a mayor de acuerdo al campo ID. Esto se debe a que usaremos BUSCARV con búsqueda aproximada, con esta fórmula

=BUSCARV(G3&MAX(tblPrecios[Fecha]),tblPrecios,4)

Como puede apreciarse, creamos el valor de búsqueda combinando al artículo buscado (en la celda G3) con el valor máximo del campo de las fechas. Como Hacemos una búsqueda aproximada, dejando el cuarto argumento de BUSCARV en blanco, obtenemos el valor más cercano que coincide con el valor buscado. Es por este motivo que la tabla tiene que estar ordenada de menor a mayor según el campo de búsqueda (ID).
Podemos ocultar la columna B de manera que el campo auxiliar no sea visible.

3 -Solución sin campo auxiliar con la función INDICE y COINCIDIR

Para ahorrarnos el campo auxiliar (considerado profano a las buenas prácticas de Excel por algunos puristas, concepto con el cual no concuerdo en absoluto), podemos combinar las funciones INDICE y COINCIDIR para crear esta función matricial (introducir con Ctrl-Mayúsculas-Enter):

=INDICE(tblPrecios4[Precio],COINCIDIR(F3&MAX(tblPrecios4[Fecha]),tblPrecios4[Articulo]&tblPrecios4[Fecha]))

Nótese que también aquí hacemos una búsqueda aproximada en la función COINCIDIR por lo que la tabla debe estar ordenada de menor a mayor según el campo Artículo y también de antiguo a reciente según el campo Fecha.

4 - Solución sin necesidad de ordenar la tabla.

En ciertas situaciones puede ser inconveniente o poco práctico tener que ordenar la tabla. Podemos extraer el valor buscado de acuero a artículo y fecha sin ordenar la tabla con esta fórmula matricial:

=INDICE(tblPrecios46[Precio],COINCIDIR(F3&MAX((tblPrecios46[Articulo]=F3)*tblPrecios46[Fecha]),tblPrecios46[Articulo]&tblPrecios46[Fecha],0))

Como puede apreciarse COINCIDIR realiza una búsqueda exacta, con el tercer parámetro puesto a 0, por lo que no hace falta ordenar la tabla.

En la función COINCIDIR, la expresión

F3&MAX((tblPrecios46[Articulo]=F3)*tblPrecios46[Fecha])

crea el valor de búsqueda;

la expresión

tblPrecios46[Articulo]&tblPrecios46[Fecha]

dentro de la fórmula matricial crea un vector que contiene todas las combinaciones de artículo/fecha (cono en el campo ID de la primera solución). Esto nos permite hacer una búsqueda exacta eximiéndonos de tener que ordenar la tabla.

El archivo con los ejemplos puede descargarse aquí.

Diagrama Gantt con tablas dinámicas

Reconozco que no hay nada novedoso en crear diagramas Gantt con Excel. Este blog aloja varias notas sobre el tema. Las técnicas más comunes para crear un diagrama de Gantt en Excel se basan en formato condicional o en gráficos de barras.

Todos los modelos que he visto hasta ahora, incluyendo los míos, tienen dos inconvenientes:

• por lo general tienen un solo nivel de actividad, es decir, no se puede descomponer una actividad central en varias sub-tareas;

• falta de flexibilidad a la hora de agregar o quitar alguna actividad.

El modelo sencillo que muestro en esta nota soluciona estos dos problemas usando tablas dinámicas.

Supongamos que queremos crear un diagrama de Gantt para un proyecto que se compone de 3 etapas. Cada etapa comprende varias actividades.
Empezamos por organizar los datos en una matriz de datos que convertimos en Tabla. Por ejemplo

A partir de esta tabla creamos esta tabla dinámica

Como puede apreciarse los campos de valores están resumidos con distintas funciones (Suma, Max.). Dado que cada registro aparece una única vez en la tabla de valores (existe una única línea para Etapa 1 - Actividad 1), podemos usar cualquier función que resuma valores excepto Contar.

También establecemos formato de fecha para el campo de fechas (Principio).

La ventaja de usar tablas dinámicas es que no necesitamos guardar ningún orden preestablecido al agregar o quitar filas de la tabla de datos. La tabla dinámica siempre estará agrupada jerárquicamente y también tenemos control total del orden.

Ahora tenemos que crear un gráfico dinámico. Los gráficos dinámicos reflejan la tabla dinámica en la cual se basan, no la base de datos de la tabla dinámica

Eligimos el tipo Barra Apilada y obtenemos este resultado

en el cual introduciremos, obviamente, algunas modificaciones. Empezamos por modificar las definiciones de la serie Principio para volverla "invisible" (ver mi prehistórica nota sobre el tema). Luego quitamos los botones y la leyenda. Este video muestra el proceso




A esta altura de los acontecimientos tenemos que corregir dos aspectos del gráfico: el orden de los valores en el eje de las X y el rango de los valores en el eje de las Y (recordemos que estamos usando un gráfico de barras de manera que el eje de las X es el eje vertical y el de las Y es el horizontal)

Si a pesar de las definiciones de Microsoft, el mínimo del eje de valores no es el mínimo del cuadro de valores, tendremos que hacer la corrección manualmente

En cuanto al eje de las categorías, usamos el menú de formato del eje

El último detalle es actualizar la tabla dinámica cada vez que introduzcamos un cambio en la tabla de datos (el gráfico dinámico que la refleja se actualiza junto con la tabla dinámica).
Si queremos evitar tener que hacer la actualización manualmente con cada cambio, podemos crear un evento usando la técnica que muestro en esta nota.

Como contar registros únicos en tablas dinámicas

Eduardo, colega de trabajo, es inteligente, aplicado y no le gusta depender del departamento de informática. Por eso, maneja una serie de pequeñas bases de datos en Excel (para el horror del departamento de IT ya mencionado). Para generar sus reportes usa principalmente tablas dinámicas de las cuales, para ponerlo de alguna manera, está perdídamente enamorado. A tal punto que todos mis intentos de mostrarle las bondades de otras herramientas como, por poner un ejemplo, Filtro Avanzado, siempre fracasan en forma rotunda.
Por eso cuando Eduardo entró ayer en mi oficina, sólo por la mirada, me di cuenta que algo le estaba pasando. No era una visita de cortesía.

- ¿Cómo hago para saber cuántos clientes tengo en mi base de datos?
 - Dado que todo lo hacés con tablas dinámicas, te sugiero que arrastres el campo de clientes al área de los datos usando la función Contar para totalizar.
- Si, es lo que hice; pero cada cliente aparece en más de una fila en la base de datos y entonces me cuenta la cantidad de veces que cada cliente aparece, no la cantidad de clientes.
- Por supuesto, así funcionan las tablas dinámicas.
- Pero, ¿cómo, no hay una función para contar registros únicos?
- Si y no...
- Uf, ya empezaste. ¿Si o no?
- En Excel 2013 hay una función para contar registros únicos en un reporte dinámico; en Excel 2010 y anteriores, no.
- ¡Ah! Yo uso Excel 2010, ¿cómo hago?
- Creando un campo auxiliar en la base de datos.

Supongamos que nuestra base de datos es la tabla de facturas de la base de datos Northwind

Para contar cuántos clientes hay en la base de datos creamos una tabla dinámica arrastrando los campos País y Cliente al área de las filas y nuevamente el campo Cliente al área de datos usando la función Contar (dado que el campo Cliente no contiene valores numéricos, Excel usará esta función en forma automática)

Inmediatamente podemos ver que en Argentina hay 3 clientes, pero la tabla dinámica muestra 11.
En las versiones de Excel anteriores a Excel 2013, tenemos que usar una columna auxiliar.
Insertamos la columna Auxiliar entre los campos Cliente y Dirección; en esta columan ponemos la fórmula =CONTAR.SI($B$3:B3,B3) que copiamos a todas las filas

El campo Auxiliar muestra el número de orden de aparación de cada cliente. Ahora podemos usar este campo como filtro de la tabla dinámica para que muestre sólo los registro donde el valor de Auxiliar es 1

Ahora podemos ver que la cuenta es correcta

Con Excel 2013, las cosas son más sencillas. No necesitamos crear ningún campo auxiliar. Sencillamente creamos la tabla dinámicas a partir de la base de datos. En el asistente de tablas dinámicas nos aseguramos de marcar la opción "Agregar estos datos al Modelo de datos" (esta opción sólo existe en Excel 2013)

Una vez creada la tabla, arrastramos el campo País al área de filas y el campo Cliente al área de los datos; seleccionamos el área de datos y abrimos el menú de configuración del campo. En la casilla de elección del tipo de cálculo tenemos una nueva función: "Recuento distinto"

Elegimos esta función y apretamos aceptar

Podemos ver que el encabezamiento del campo ha cambiado de "Recuento de cliente" a "Recuento distinto de Cliente" y que efectivamente tenemos 84 clientes en la base de datos.

Personalmente pienso que la traducción tendría que haber sido "Recuento único".