Eliminar "en blanco" en tablas dinámicas

Al resumir datos con una tabla dinámica, si falta algún valor en algunos de los campos de fila, Excel mostrará el texto "(en blanco)" en la celda correspondiente. Por ejemplo, si construimos un reporte dinámico a partir de esta tabla

al poner el campo "Responsable" en el área de filas, veremos esto

Muchos usuarios, yo entre ellos, prefieren que la celda permanezca en blanco de manera que sea fácil detectar las filas donde no se ha definido el responsable.

Excel no tiene un método nativo para definir esta situación. Si bien en el menú "Opciones de tabla dinámica-Diseño y formato" existe la alternativa de definir valores para celdas vacías

esta opción se aplica sólo al área de los datos.

Veamos cuales son nuestras posibilidades:

Reemplazar "(en blanco)" con un espacio: seleccionamos una de las celdas vacías en la tabla dinámica e introducimos un espacio. Hacemos esto porque Excel no permite introducir valores nulos como elementos en el campo de filas o columnas.
Podemos hacer lo mismo usando Buscar y Reemplazar (Ctrl+L)

Contrariamente a lo que podría esperarse, al actualizar la tabla no hace falta volver a aplicar el método. Aún si introducimos una nueva fila en la base de datos sin responsable, la celda correspondiente en la tabla dinámica aparecerá en blanco.

Formato condicional: podemos usar la opción "Fórmula" para detectar las celdas donde aparece el texto "(en blanco)" y cambiar el color de la fuente al color del fondo para volver "invisible" el texto

Ordenar campos en tablas dinámicas.

La forma en que Excel ordena los valores de un campo en una tabla dinámica depende de las definiciones en el menú “Opciones de tabla dinámica…”

En nuestro ejemplo, el campo “CategoryName” està ordenado según el origen de datos, es decir, según el orden de los valores en la base de datos.

Si cambiamos la definición a “Ordear de A a Z”, obtenemos

Pero si queremos más control debemos usar la opción “Más opciones de ordenación…”

Las opciones son claras y no hace falta extenderse en explicaciones. Al elegir una de las opciones, ascendente o descendente, podemos establecer si el orden se determinará por el campo de las filas o de los valores

En el ángulo inferior izquierdo del formulario puede verse el botón “Más opciones”

Si activamos la opción “Ordenar automáticamente...” las restantes quedan anuladas. Si quitamos la marca de “Ordenar automáticamente…” podemos activar la opción “Primer criterio de ordenación”. El título es un poco engañoso. Esta opción nos permite usar listas personalizadas para determinar el orden del campo.

Sincronización de tablas dinámicas en Excel

En el foro EEE (Expertos en Excel en Español), Julio consultaba: “Quiero vincular tres tablas distintas en el mismo archivo, de forma que cuando filtre por mes, se me cambien todos los datos de todas las tablas a la vez”.

Con Excel 2010 podemos hacerlo con facilidad usando la nueva funcionalidad “Segmentación de Datos”. Pero si usamos versiones anteriores de Excel, tendremos que usar macros o, más precisamente, programar un evento.

Veamos el siguiente ejemplo (que se puede descargar aquí): en la hoja “bd” tenemos datos de la facturación de una empresa,

en la hoja “reporte” hemos construido tres tablas dinámicas

La primer tabla a la izquierda muestra las ventas por país, la del centro resume los datos por vendedor y la tabla a la derecha muestra los 10 clientes principales. Las tres tablas están filtradas por año (los datos en “bd” aparecen por fecha, por eso usamos la funcionalidad Agrupar para resumir los datos por año).

Si queremos resumir los datos del año 2008, por ejemplo, tenemos que cambiar el valor del filtro de cada uno de los informes.

Nuestro objetivo es programar un evento de manera que cuando cambiamos el valor del filtro de un informe, los otros dos cambien simultáneamente. En el módulo de Vba de la hoja “reporte” ponemos este código

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim tdfecha As String
    Dim pt As PivotTable
 
    tdfecha = Target.PivotFields("Fecha").CurrentPage

    With Application
     .ScreenUpdating = False
     .EnableEvents = False
    End With

    For Each pt In ActiveSheet.PivotTables
        With pt.PivotFields("Fecha")
            .ClearAllFilters
            .CurrentPage = tdfecha
        End With
    Next pt
 
    With Application
     .ScreenUpdating = True
     .EnableEvents = True
    End With

End Sub

El evento Worksheet_PivotTableUpdate se dispara cuando cambiamos algo en la tabla que genera la actualización (en nuestro caso cambiar el valor del filtro).

Para captar el valor del filtro definimos la variable “tdfecha”

tdfecha = Target.PivotFields("Fecha").CurrentPage

Luego usamos Application.EnableEvents= False para evitar que se cree una reacción en cadena de eventos (queremos responder sólo al primer cambio).

El próximo paso es usar for – next para inicializar el valor del filtro de cada una de las tablas dinámicas en la hoja y fijar su valor con la variable “tdfecha”.

El último paso es restablecer los eventos y la actualización de pantalla que habíamos cancelado previamente.

En Excel 2003 tenemos que remplazar la sentencia .ClearAllFilters por .CurrentPage = "All"

El funcionamiento del evento puede verse en este video

Gráfico dinámico con lista desplegable - segunda nota

En la nota anterior mostramos un modelo sencillo para crear un gráfico dinámico según el valor elegido de una lista desplegable. Señalamos en esa nota alguna de sus limitaciones: la escalabilidad. Si bien esta palabra no figura en el diccionario de la Real Academia Española, Wikipedia la define como " la capacidad del sistema informático de cambiar su tamaño o configuración para adaptarse a las circunstancias cambiantes”.

Si queremos usar este tipo de reporte a lo largo del tiempo, agregando datos, tenemos que crear un modelo dinámico.
Excel permite hacer esto con facilidad, pero para lograrlo tenemos que organizar nuestro modelo en una forma distinta. El principio básico es separar los datos de los cálculos y de la presentación del reporte (en nuestro caso, el gráfico y la matriz de ventas)

Nuestra base de datos está en la hoja “BD”. El rango de los datos está definido como tabla. Todos los objetos o fórmulas que se refieren a la tabla se adaptan automáticamente a los cambios en los datos de ésta. Esto nos libera de la necesidad de crear rangos dinámicos con DESREF o INDICE.

En la hoja “cálculos” creamos nuestro “motor”. Este consiste en una tabla dinámica que resume los datos de la base de datos

La hoja reporte resume los datos en una tabla que nos servirá también para crear el gráfico dinámico

En la celda C3 ponemos una lista desplegable con los nombres de los vendedores; en la celda C4 una lista desplegable con los años disponibles. Los valores de estas listas están definidos con nombres que se refieren a rangos en la hoja “auxiliar”.

Para poner los datos de la tabla en forma dinámica usamos la función IMPORTARDATOSDINAMICOS,

Para crear la función con facilidad, definimos en Opciones de la tabla dinámica la opción “Generar GetPivotData”

Este video muestra el funcionamiento del modelo



Un último toque. Las tablas dinámicas no se actualizan automáticamente. En esta nota muestro una técnica para lograr la actualización automática de tablas dinámicas.

El archivo con el modelo se puede descargar aquí.

Actualización automática de tablas dinámicas

Ha pasado casi un mes desde que publiqué mi última nota. Antes que mis lectores (y Google!) piensen que he abandonado el blog, publico una nota breve sobre actualización automática de tablas dinámicas en Excel.

Excel no tiene un método incorporado para actualizar tablas dinámicas en forma automática. Cambios en la base de datos se reflejarán en el reporte dinámico sólo cuando apretemos el icono Actualizar (o Alt+F5) o Actualizar Todo (Ctrl+Alt+F5) si queremos actualizar todas las tablas dinámicas del cuaderno

Para evitar tener que actualizar manualmente las tablas dinámicas de nuestro cuaderno (pensemos en un dashboard donde hacemos uso intensivo de tablas dinámicas) podemos crear un evento. El código es muy sencillo

Private Sub Worksheet_Deactivate()
    ActiveWorkbook.RefreshAll
End Sub

En este caso usamos el evento Worksheet_Deactivate de la hoja “datos” (la que contiene la base de datos del reporte dinámico). De esta manera nos aseguramos que cada actualizamos la base de datos, al pasar a otra hoja las tablas dinámicas se actualizan.

Esta técnica supone que la base de datos se encuentra en una hoja del cuaderno que contiene los reportes dinámicos. En el caso en que las tablas dinámicas se basen en fuentes de datos externas, usamos el mismo código pero lo disparamos usando el evento Worksheet_Activate de alguna de las hojas que contienen los reportes dinámicos.

Crear tablas dinámicas independientes.

Tal vez habrán notado un problema que existe en tablas dinámicas que comparten la misma base de datos: aplicar agrupar o desagrupar en una tabla produce el mismo efecto en la otra.

Supongamos que tenemos una tabla de facturas por fecha (como siempre, la bendita base de datos Northwind)

Creamos dos tablas dinámicas: una que muestra las ventas por año y mes y la segunda por año y trimestre, para lo que usaremos Agrupar

Para la primer tabla

con este resultado

Ahora creamos la segunda tabla basándonos en la misma base de datos, para lo cual usamos Insertar-Tabla Dinámica (o Datos-Tabla Dinámica en Excel Clásico). Pero al momento de crear la tabla, antes de que hayamos arrastrado alguno de los campos a alguna de las áreas podemos notar que el campo Años, creado al agrupar la tabla anterior, está presente en la lista de campos

Al arrastrar el campo Fechas al área de filas, los datos estarán agrupados por meses

Veamos que pasa al agrupar los datos en la segunda tabla por trimestres



Ambas tablas sufren el mismo cambio. Para que esto no suceda tenemos que construir la segunda tabla usando el asistente de tablas dinámicas.

En Excel Clásico (97-2003), al crear la segunda tabla usando la misma base de datos, veremos este mensaje

Todo lo que nos queda por hacer es apretar No para crear informes independientes



En Excel 2007/10 no se abre la opción de crear tablas independientes al construir la segunda tabla. Para que esta opción se presente tenemos que usar el asistente de tablas dinámicas. El viejo asistente existe en Excel 2007/10, pero está oculto lejos de los ojos del usuario. Para hacerlo visible usamos el atajo de teclado Alt+T+B

Seguimos los pasos hasta recibir el mensaje

Procedemos como en el caso anterior y creamos tablas dinámicas independientes también en Excel 2007/10

Análisis Pareto en Excel 2003

En la nota anterior sobre análisis de datos según el principio de Pareto mostramos cómo crear el cuadro de resultados y el gráfico con Excel. La solución con tablas dinámicas que mostré en esa nota sólo se aplica a Excel 2010.

Entre las mejoras introducidas en Excel 2010 se cuentan seis funcionalidades en la opción “Mostrar como…” de las tablas dinámicas. Entre ellas “% del Total en…”, que calcula el total acumulado como porcentaje del total. Esta funcionalidad no está disponible en las versiones anteriores de Excel.

En esta nota mostraré un rodeo para dar la misma solución que Excel 2003
.
En la tabla de datos agregamos un campo (columna): “Pct del Total” (porcentaje del total)

La fórmula para calcular los valores del campo es

=C2/SUMA($C$2:$C$287)

que sencillamente calcula el porcentaje de cada valor en relación al total. Como hemos definido nuestra tabla como “lista” no debemos preocuparnos por actualizar las fórmulas cuando agreguemos o quitemos valores. Si usamos versiones anteriores a Excel 2003 tendremos que usar rangos dinámicos.

Una vez creado el campo auxiliar, creamos la tabla dinámica con el campo Categoría en el área de las filas y el campo Ventas en el área de datos. Luego ordenamos la tabla de mayor a menor según el campo de Ventas.

En el próximo paso agregamos el campo “Pct del Total” al área de ventas y definimos “Mostrar datos como..” con la opción “Total en…” y “Categoría” como capo de base

Todo el proceso puede verse en este video



Finalmente creamos el gráfico dinámico, donde deberemos hacer algunos cambios para obtener algo humanamente legible. Empezamos por crear el gráfico (un clic al icono de gráficos en la barra de herramientas de las tablas dinámicas). Por defecto Excel crea el gráfico en una hoja separada de manera que si queremos ubicarlo en la misma hoja de la tabla dinámica tendremos que cambiar la ubicación.

El segundo paso es cambiar las proporciones del gráfico (por defecto, estos gráficos dinámicos sufren de enanismo agudo al cambiarlos de ubicación).

Para que la serie “Pct del Total” sea visible tendremos que definirla en un eje Y secundario. La forma más sencilla es usar la barra de herramientas de los gráficos para seleccionar la serie y el menú Formato para cambiar las definiciones

Asegurándonos que la series esté seleccionada cambiamos a “eje secundario” y el tipo de gráfico a “Línea”. Después de hacer otros cambios obtenemos

Análisis de datos según el principio de Pareto con Excel

Esto es lo que me consulta uno de mis lectores:

Quisiera consultarte si se puede ser segmentar una tabla dinámica según el teorema de Pareto o sea mostrar el 20% de los ítem que explican el 80% de los valores y el resto agruparlo en un ítem "otros(as)" integrando el 100% de la muestra.

El principio de Pareto lleva el nombre del economista italiano que lo enunció, observó que:

la gente en su sociedad se dividía naturalmente entre los «pocos de mucho» y los «muchos de poco»; se establecían así dos grupos de proporciones 80-20 tales que el grupo minoritario, formado por un 20% de población, ostentaba el 80% de algo y el grupo mayoritario, formado por un 80% de población, el 20% de ese mismo algo.

Este principio es aplicado en muchas áreas, como la producción, logística y economía.

Supongamos que queremos analizar las ventas de una cadena a acuerdo al principio de Pareto (usaremos la base de datos Northwind incluida en el paquete de Office).

Antes de responder a la consulta de mi lector veamos cómo crear un histograma de Pareto con Excel.
Empezamos por resumir los datos por categoría en una tabla:

Esta matriz resume los datos que hemos organizados como “tabla” (En Excel 2003, Lista), a la que le hemos dado el nombre “tblVentas”

Para totalizar los datos por categoría usamos SUMAR.SI

=SUMAR.SI(tblVentas[Categoría],reporte!A4,tblVentas[Ventas])

Es importante notar que la matriz está ordenada de mayor a menor según el campo Ventas.

A partir de esta matriz, creamos este gráfico:

Como puede observarse usamos dos ejes Y. El primario para representar las ventas (columnas) y el secundario para el porcentaje acumulado (línea).

La ventaja de usar la funcionalidad “tabla” reside en que la matriz que resume los datos por categoría se actualiza automáticamente si agregamos o quitamos filas en la base de datos, y por lo tanto también el gráfico.

Podemos hacer lo mismo con tablas y gráficos dinámicos (para versiones anteriores a Excel 2010 ver esta nota). A partir de la base de datos creamos una tabla dinámica ordenada de mayor a menor según el campo Ventas

Ahora agregamos por segunda vez el campo Ventas en el área de valores y cambiamos “Mostrar valores en…” a “% del total en”

Todo lo que nos queda por hacer es crear el gráfico dinámico y hacer los cambios necesarios en el tipo de gráfico por serie.

Pero mi lector preguntaba si se puede hacer algo similar con tablas dinámicas, agrupando los datos en dos categorías: los productos que representan el 80% de las ventas y los que representan el restante 20%.

Una solución sencilla es agrupar los datos manualmente. Después de agrupar tenemos que hacer algunas correcciones al resultado

Tenemos que agregar subtotales y cambiar “Mostrar valores…” a “% del total general”

El archivo con los ejemplo se puede descargar aquí.

Determinar posición (ranking) por grupos

En la nota sobre la función JERARQUIA aparece este comentario

¿Es posible aplicar la función jerarquía a una columna de manera que se reinicie el ranking si el valor de otra columna cambia? Por ejemplo, tengo 5 vendedores en 4 países y quiero saber el principal vendedor en cada país.

Esta consulta me llevó a reflexionar sobre dos temas:

1 - ¿cómo aplicar la función JERARQUIA en este caso?
2 – ¿Por qué aplicar la función JERARQUIA para este caso?

Muy a menudo me encuentro con consultas de este tipo, donde el lector presupone que la solución pasa por usar una función o un método determinado. En lugar de preguntar "¿cómo determino el mejor vendedor de cada país?" la consulta se transforma en "¿cómo uso JERARQUIA para solucionar este problema?

El medio (el uso de la función JERARQUIA) se transforma en el objetivo (encontrar el mejor vendedor de cada país).

Supongo que esta forma de enfrentar la solución de problemas está relacionada con la tendencia natural, en mi opinión, de aferrarnos a lo conocido y nuestra aversión o temor a lo desconocido. Seguramente hay otros factores, pero no lo trataré en esta nota. Por supuesto, mis lectores están invitados a opinar sobre el tema.

Y ahora llegó el momento de empezar a trabajar. ¿Cómo solucionamos el problema? Empecemos por mostrar nuestros datos

Solución con JERARQUIA



Para usar la función JERARQUIA por grupos (vendedores de Argentina, vendedores de Colombia, etc.) empezamos por definir rangos con nombres

Cada nombre se refiere al rango de valores de ventas correspondiente al país a que se refiere. Nótese que estos rangos no son dinámicos, por lo que se agregamos valores, tendremos que editar el nombre para redefinir el rango.

Agregamos una columna a la tabla, Ranking, donde ponemos esta fórmula

=JERARQUIA(C2,INDIRECTO(A2))

Hemos agregado además un formato condicional para resaltar el nombre y las ventas del vendedor que recibe la posición 1

Solución con INDICE, COINCIDIR y MAX en forma matricial

La ventaja de esta solución es que nos permite definir rangos dinámicos, librándonos de la necesidad de editar los rangos definidos cada vez que agregamos o quitamos datos de la tabla.

Otra ventaja es que implementamos uno de los principios importantes de buenas prácticas en Excel: la separación de la base de datos de los cálculos y los informes.

Como en el caso anterior, empezamos por definir rangos en nombres (otra buena práctica en Excel)
En este caso definimos rangos dinámicos

pais =indice!$A$2:INDICE(indice!$A:$A,CONTARA(indice!$A:$A))
vendedor =indice!$B$2:INDICE(indice!$B:$B,CONTARA(indice!$B:$B))
ventas =indice!$C$2:INDICE(indice!$C:$C,CONTARA(indice!$C:$C))

Creamos una tabla separada para mostrar los resultados por país donde ponemos esta fórmula matricial (fórmulas que introducimos apretando simultáneamente Ctrl+Mayúsculas+Enter)

=INDICE(vendedor,COINCIDIR(MAX((pais=E2)*(ventas)),ventas,0))

Solución con tablas dinámicas

Empezamos por crear una tabla dinámica con los campos País y Vendedor en el área de filas y Ventas en el área de datos. Luego ordenamos el campo Vendedor según las Ventas

El próximo paso es crear un campo calculado, Ranking (o cualquier otro nombre) con la fórmula "=1"

Seleccionamos el campo que acabamos de agregar y en el menú de configuración del campo mostramos los valores como "Total en" usando como campo de base "Vendedor"

El resultado es

Como puede apreciarse, no se trata de una verdadera solución ya que nos basta con ordenar los valores tal como hicimos en el primer paso. Obviamente, quien aparece en el primer lugar de cada país es el que más vendió. Esta solución puede ser útil si queremos extraer el vendedor que más vendió (o el segundo o el tercer, etc.) usando funciones que se refieran a la tabla dinámica, como IMPORTARDATOSDINAMICOS.

En este enlace muestro otra forma de aplicar JERARQUIA, es decir señalar el ranking, en una tabla dinámica.