Título de gráfico dinámico con segmentaciones

Esta es la situación. Hemos creado una tabla dinámica y un gráfico dinámico relacionado y, como somos amables, también proporcionamos una segmentación para que el usuario pueda realizar selecciones fácilmente. Pero nos gustaría que los títulos de los informes se actualicen dinámicamente según las selecciones realizadas. Como ocurre con todo en Excel, existen varias formas de lograrlo. En esta publicación, usaremos una tabla dinámica auxiliar.

Objetivo

Aquí está nuestra hermosa tabla dinámica y nuestro gráfico dinámico relacionado.

Ambos muestran las ventas por artículo. Para que el usuario pueda elegir una región o regiones para incluirlas en ambos informes, hemos creado una segmentación de datos, como se muestra a continuación.

Cuando la segmentación de datos está en la misma hoja de trabajo que los informes, el usuario puede ver fácilmente qué regiones están seleccionadas incluso cuando se imprimen. Sin embargo, la segmentación de datos puede estar ubicada en una hoja de trabajo diferente o controlar varios informes en todo el libro. Nos gustaría que cada informe le permita al lector saber claramente qué regiones están incluidas en el informe. Entonces, simplemente configuraremos un título de informe dinámico, algo como esto:

Usaremos títulos de informes dinámicos para la tabla dinámica y el gráfico dinámico.

Creé un video y escribí una narrativa a continuación que explica los detalles.

Video

Narrativo

Seguiremos los siguientes pasos:

• Ayudante PT
• Fórmula auxiliar
• Accede a los informes

Hagámoslo.

Ayudante PT

El primer paso es crear una tabla dinámica auxiliar. La tabla dinámica auxiliar tiene un trabajo: mostrar los elementos que el usuario seleccionó en la segmentación de datos. Para hacer esto, primero creamos un PT basado en la misma fuente de datos e insertamos el campo usado para la segmentación de datos en el área de diseño de Columnas. En este punto, todos los elementos del campo se muestran en el informe, como se muestra a continuación.

Para que la segmentación de datos actualice este PT auxiliar, debemos seleccionar la segmentación de datos y luego hacer clic en Herramientas de segmentación Conexiones de informes . En el cuadro de diálogo Conexiones de informes resultante, marcamos la casilla de verificación de nuestro PT auxiliar, que se almacena en la hoja de trabajo de Configuración, como se muestra a continuación.

Ahora, las selecciones de segmentación también afectan al PT auxiliar. Dado que Este y Oeste están seleccionados en la segmentación de datos, el PT se actualiza en consecuencia, como se muestra a continuación.

El último paso es eliminar la etiqueta Total general, lo que se puede hacer fácilmente haciendo clic en el comando Herramientas de tabla dinámica Totales generales Desactivado para filas y columnas .

Bien, el PT actualizado se muestra a continuación.

En este punto, las selecciones realizadas en la segmentación se mostrarán en el PT. Hasta ahora, todo bien. Nuestro siguiente paso es crear una columna auxiliar para combinar los valores.

Nota: En esta publicación, coloqué el campo en el área de diseño de columnas para que las selecciones aparezcan en una sola fila, pero puedes adaptar fácilmente los pasos presentados si prefieres colocar el campo en el área de diseño de filas. . La técnica funcionará de cualquier manera.

Fórmula auxiliar

Escribiremos una fórmula auxiliar que combine las etiquetas PT (que son los mismos elementos seleccionados en la segmentación de datos). Probablemente la forma más sencilla de hacerlo es con la función TEXTJOIN.

Si las etiquetas de la columna PT auxiliar se mostraran en la fila 24, podríamos usar la siguiente fórmula:

=TEXTOJOIN(", ",VERDADERO,24:24)

Básicamente, esto le dice a Excel que combine los valores en la fila 24 (las etiquetas PT), usando una coma y un espacio para separar los valores, y que ignore las celdas vacías. Los resultados de esta fórmula se muestran a continuación:

Este oeste

Una vez completada nuestra fórmula de ayuda, es hora de incluirla en los informes.

Nota: dependiendo de su versión de Excel, es posible que no tenga la función TEXTJOIN. En su lugar, puede utilizar las funciones SUSTITUIR, RECORTAR y CONCATENAR. Por ejemplo, si las etiquetas PT auxiliares aparecen en B23, C23, D23 y E23, podría usar algo como esta fórmula (que se incluye en el archivo de muestra a continuación como referencia): =SUSTITUIR(TRIM(CONCATENAR(B23”, “ ,C23”, “,D23”, “,E23))”, “,”, “).

Accede a los informes

Digamos que queremos ingresar manualmente un título de informe, como Ventas por artículo, en una celda y luego usar ese valor como título principal del informe. Queremos que las selecciones de segmentación estén en el subtítulo. Simplemente configuramos las celdas en consecuencia. El título es una celda de entrada manual (C17) y el subtítulo se crea mediante la fórmula auxiliar escrita arriba (C18). Como esto:

Para incluir el título y el subtítulo en los informes, simplemente usamos una fórmula con una referencia de celda directa. Por ejemplo, para el informe de tabla dinámica, podríamos simplemente escribir dos fórmulas en las celdas encima de la tabla dinámica para recuperar los valores de C17 y C18. Como esto:

=Configuración!C17=Configuración!C18

El PT actualizado se muestra a continuación.

Podemos hacer lo mismo con el gráfico dinámico. Para hacerlo, seleccione el título del gráfico y comience la fórmula escribiendo = en la barra de fórmulas, como se muestra a continuación.

Luego, escriba la fórmula o navegue hasta la hoja de trabajo, seleccione la celda y presione Entrar. El título ahora extraerá el valor de la celda.

Podemos hacer lo mismo con el subtítulo. Inserte un cuadro de texto en el gráfico, justo debajo del título del gráfico, haciendo clic en Insertar Cuadro de texto y haciendo clic en el área del gráfico. Ingrese = en la barra de fórmulas, navegue y seleccione la celda de fórmula auxiliar de subtítulos y presione la tecla Enter en su teclado. Puede formatear el color y el tamaño del título y subtítulo como desee. El gráfico actualizado se muestra a continuación.

Ahora, a medida que el usuario realiza selecciones en la segmentación de datos, el gráfico se actualiza en consecuencia.

Sí… ¡lo logramos!

El archivo de muestra incluye un ejemplo práctico de los pasos presentados.

• Archivo de muestra: Slicer Titles.xlsx

Si tiene otros enfoques para los títulos de informes dinámicos, compártalos publicando un comentario a continuación… ¡gracias!