Alternativa de valores de texto de tabla dinámica con FILTRO

Esta es la tercera y última publicación de la serie Alternativa de valores de texto de tablas dinámicas, donde analizamos alternativas para mostrar valores de texto en tablas dinámicas. En la primera publicación , utilizamos Power Query como alternativa. En la segunda publicación , combinamos varios valores de texto. En esta publicación, usaremos la función FILTRO como alternativa a los valores de texto de la tabla dinámica. Hagámoslo.

Objetivo: tabla dinámica con valores de texto

Antes de llegar demasiado lejos, confirmemos nuestro objetivo. Exportamos algunos datos como este:

Y nos gustaría crear un informe resumido como este:

En nuestras publicaciones anteriores, utilizamos Power Query como alternativa al uso de una tabla dinámica para crear este informe. Esta vez usaremos fórmulas de Excel.

Vídeo: alternativa de valores de texto de tabla dinámica con FILTRO

Narrativo

Esta descripción proporciona los pasos específicos para crear una alternativa a los valores de texto de la tabla dinámica con la función FILTRO. Crearemos el informe siguiendo los siguientes tres pasos:

• Etiquetas de informe con UNIQUE y TRANSPOSE
• Recuperar valores de texto con FILTRO
• Unir valores de texto con ARRAYTOTEXT

Sigamos estos pasos uno por uno.

Nota: dependiendo de su versión de Excel, puede que tenga o no acceso a las funciones comentadas.

Etiquetas de informe

El primer paso para crear este informe basado en fórmulas es crear las etiquetas del informe. Estos son los encabezados de filas y columnas. Como se trata de Excel, hay varias formas de lograrlo.

Una opción sería crearlos manualmente. Para crear las etiquetas de las filas, copiaríamos toda la columna Cliente y pegaríamos valores especiales en un área vacía de la hoja de trabajo. Luego, usaríamos el comando Datos Eliminar duplicados para eliminar cualquier valor duplicado. Para crear las etiquetas de las columnas, copiaríamos toda la columna de retorno y luego pegaríamos valores especiales en un área vacía. Luego, eliminaríamos los duplicados. Luego, podríamos copiar y pegar una transposición especial. Luego, podríamos moverlos a su lugar como deseemos.

Otra opción sería utilizar una fórmula. Para crear una lista de etiquetas de fila únicas (sin duplicados), ingresaríamos la siguiente fórmula:

=ÚNICO(Tabla1[Cliente])

Le damos Enter y bam:

Si quisiéramos ordenar las etiquetas de las filas, podríamos simplemente envolver la función ORDENAR alrededor de la función ÚNICA, así:

=ORDENAR(ÚNICO(Tabla1[Cliente]))

Y bam:

Para crear las etiquetas de las columnas, usaríamos la función ÚNICA para recuperar una lista de los valores de la columna de retorno sin duplicados. Queremos transponerlos de filas a columnas, por lo que envolvemos la función TRANSPONER alrededor de la función ÚNICA de esta manera:

=TRANSPONER(ÚNICO(Tabla1[Retorno]))

Le damos Enter y bam:

Con las etiquetas del informe en buen estado, es hora de recuperar nuestros valores de texto con la función FILTRO.

Recuperar valores de texto con FILTRO

Usaremos la función FILTRO para recuperar los valores de texto de la columna Personal. Podemos utilizar la siguiente fórmula:

=FILTRO(Tabla1[Personal],((Tabla1[Cliente]=$F7)*(Tabla1[Retorno]=G$6)),"")

Dónde:

• Tabla1[Personal] es la columna que tiene los valores a devolver
• ((Table1[Client]=$F7)*(Table1[Return]=G$6)) es el criterio sobre qué valores incluir
• “” devuelve una cadena vacía si no hay personal para una devolución de cliente determinada

Nota: si desea obtener más información sobre cómo construir la expresión de criterios, consulte esta publicación .

Le damos Enter y bam:

Observamos que se devuelven DAR y DMK. Esto se debe a que ambos están asignados a DIG290 Personal. Entonces, necesitamos una manera de combinar múltiples valores de pentagrama en una sola celda. Abordaremos este último paso con ARRAYTOTEXT.

Unir valores de texto con ARRAYTOTEXT

Para combinar varios valores de pentagrama con un delimitador de espacio de coma, envolvemos la función ARRAYTOTEXT alrededor de la función FILTRO, así:

=ARRAYTOTEXT(FILTER(Tabla1[Personal],((Tabla1[Cliente]=$F7)*(Tabla1[Retorno]=G$6)),""))

Le damos Enter y bam:

Nota: si desea utilizar un delimitador que no sea un espacio de coma, puede utilizar TEXTJOIN en lugar de ARRAYTOTEXT.

Finalmente, necesitamos completar la fórmula hacia abajo y hacia la derecha. Copiamos la fórmula y la pegamos en las otras celdas vacías. Bam:

Nota: si intenta completar la fórmula correctamente haciendo clic y arrastrando, probablemente obtendrá resultados inesperados porque las referencias de las columnas se tratarán como relativas. Entonces, en lugar de hacer clic y arrastrar, querrás usar Copiar/Pegar o el comando Rellenar Derecha. Además, asegúrese de utilizar el estilo de referencia de celda correcto, como $F7 para bloquear la referencia de columna y G$6 para bloquear la referencia de fila.

Con las etiquetas y los valores del informe en buen estado, ahora podemos aplicar cualquier formato deseado. Esto podría incluir fuentes en negrita para las etiquetas, centrar los valores y aplicar algunos bordes de celda:

Sí… ¡lo logramos!

Conclusión

Esta es la última publicación de la serie y espero que haya sido útil.

Si tiene alguna alternativa o sugerencia, compártala publicando un comentario a continuación… ¡gracias!

Archivo de muestra:

Valores de texto3.xlsxDescargar