Filtrado de columnas horizontales en Excel

Si no eres un usuario novato, entonces ya te habrás dado cuenta de que el 99% de todo en Excel está diseñado para trabajar con tablas verticales, donde los parámetros o atributos (campos) pasan por las columnas, y se ubica información sobre objetos o eventos. en las lineas Tablas dinámicas, subtotales, copia de fórmulas con un doble clic: todo está diseñado específicamente para este formato de datos.

Sin embargo, no hay reglas sin excepciones y con una frecuencia bastante regular me preguntan qué hacer si en el trabajo se encuentra una tabla con una orientación semántica horizontal, o una tabla donde las filas y las columnas tienen el mismo peso en significado:

Y si Excel todavía sabe cómo ordenar horizontalmente (con el comando Datos – Ordenar – Opciones – Ordenar columnas), entonces la situación con el filtrado es peor: simplemente no hay herramientas integradas para filtrar columnas, no filas en Excel. Por lo tanto, si se enfrenta a una tarea de este tipo, tendrá que encontrar soluciones alternativas de diversos grados de complejidad.

Método 1. Nueva función FILTRO

Si tiene la nueva versión de Excel 2021 o una suscripción a Excel 365, puede aprovechar la función recientemente introducida FILTRO (FILTRAR), que puede filtrar los datos de origen no solo por filas, sino también por columnas. Para funcionar, esta función requiere una matriz-fila unidimensional horizontal auxiliar, donde cada valor (VERDADERO o FALSO) determina si mostramos o, por el contrario, ocultamos la siguiente columna de la tabla.

Agreguemos la siguiente línea arriba de nuestra tabla y escribamos el estado de cada columna en ella:

• Digamos que siempre queremos mostrar la primera y la última columna (encabezados y totales), por lo que para ellos en la primera y última celda de la matriz establecemos el valor = VERDADERO.
• Para las columnas restantes, el contenido de las celdas correspondientes será una fórmula que verifica la condición que necesitamos usando funciones И (Y) or OR (OR). Por ejemplo, que el total esté en el rango de 300 a 500.

Después de eso, solo queda usar la función. FILTRO para seleccionar columnas sobre las cuales nuestra matriz auxiliar tiene un valor VERDADERO:

Del mismo modo, puede filtrar columnas por una lista determinada. En este caso, la función ayudará COUNTIF (CONTAR.SI), que comprueba el número de apariciones del siguiente nombre de columna del encabezado de la tabla en la lista permitida:

Método 2. Tabla dinámica en lugar de la habitual

Actualmente, Excel tiene incorporado el filtrado horizontal por columnas solo en tablas dinámicas, por lo que si logramos convertir nuestra tabla original en una tabla dinámica, podemos usar esta funcionalidad incorporada. Para ello, nuestra tabla fuente debe cumplir las siguientes condiciones:

• tener una línea de encabezado de una línea "correcta" sin celdas vacías y combinadas; de lo contrario, no funcionará para construir una tabla dinámica;
• no contenga duplicados en las etiquetas de filas y columnas; se "colapsarán" en el resumen en una lista de solo valores únicos;
• contener solo números en el rango de valores (en la intersección de filas y columnas), porque la tabla dinámica definitivamente les aplicará algún tipo de función de agregación (suma, promedio, etc.) y esto no funcionará con el texto

Si se cumplen todas estas condiciones, para crear una tabla dinámica que se parezca a nuestra tabla original, será necesario expandirla (la original) de la tabla de referencias cruzadas a una tabla plana (normalizada). Y la forma más fácil de hacerlo es con el complemento Power Query, una poderosa herramienta de transformación de datos integrada en Excel desde 2016. 

Estos son:

1. Convirtamos la tabla en un comando dinámico "inteligente" Inicio – Formato como tabla (Inicio — Formatear como tabla).
2. Cargando en Power Query con el comando Datos: de la tabla/rango (Datos: de la tabla/rango).
3. Filtramos la línea con los totales (el resumen tendrá sus propios totales).
4. Haga clic derecho en el encabezado de la primera columna y seleccione Desplegar otras columnas (Anular pivote de otras columnas). Todas las columnas no seleccionadas se convierten en dos: el nombre del empleado y el valor de su indicador.
5. Filtrar la columna con los totales que entraron en la columna Atributo.
6. Construimos una tabla dinámica de acuerdo con la tabla plana (normalizada) resultante con el comando Inicio — Cerrar y cargar — Cerrar y cargar en… (Inicio — Cerrar y cargar — Cerrar y cargar a…).

Ahora puede usar la capacidad de filtrar las columnas disponibles en las tablas dinámicas: las marcas de verificación habituales delante de los nombres y elementos. Filtros de firma (Filtros de etiquetas) or Filtra por valor (Filtros de valor):

Y por supuesto, a la hora de cambiar los datos, deberás actualizar nuestra consulta y el resumen con un atajo de teclado Ctrl+otro+F5 o equipo Datos – Actualizar todo (Datos — Actualizar todo).

Método 3. Macro en VBA

Todos los métodos anteriores, como puede ver fácilmente, no filtran exactamente: no ocultamos las columnas en la lista original, sino que formamos una nueva tabla con un conjunto dado de columnas de la original. Si es necesario filtrar (ocultar) las columnas en los datos de origen, entonces se necesita un enfoque fundamentalmente diferente, a saber, una macro.

Supongamos que queremos filtrar columnas sobre la marcha donde el nombre del gerente en el encabezado de la tabla satisface la máscara especificada en la celda amarilla A4, por ejemplo, comienza con la letra "A" (es decir, obtiene "Anna" y "Arthur " como resultado). 

Como en el primer método, primero implementamos una fila de rango auxiliar, donde en cada celda nuestro criterio será verificado por una fórmula y los valores lógicos VERDADERO o FALSO se mostrarán para las columnas visibles y ocultas, respectivamente:

Luego agreguemos una macro simple. Haga clic derecho en la pestaña de la hoja y seleccione el comando Fuente (Código fuente). Copie y pegue el siguiente código VBA en la ventana que se abre:

Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$4" Entonces para cada celda en Range("D2:O2") If cell = True Then cell.EntireColumn.Hidden = False Else cell.EntireColumn.Hidden = Verdadero Fin si la siguiente celda Fin si final Sub  

Su lógica es la siguiente:

• En general, este es un controlador de eventos. Hoja de trabajo_Cambiar, es decir, esta macro se ejecutará automáticamente en cualquier cambio en cualquier celda de la hoja actual.
• La referencia a la celda cambiada siempre estará en la variable Target.
• Primero, verificamos que el usuario haya cambiado exactamente la celda con el criterio (A4); esto lo hace el operador if.
• Entonces el ciclo comienza Para cada… para iterar sobre celdas grises (D2:O2) con valores de indicador VERDADERO / FALSO para cada columna.
• Si el valor de la siguiente celda gris es VERDADERO (true), entonces la columna no está oculta, de lo contrario la ocultamos (propiedad Oculto).

•  Funciones de matriz dinámica de Office 365: FILTRO, ORDENAR y UNIC
• Tabla dinámica con encabezado multilínea usando Power Query
• Qué son las macros, cómo crearlas y usarlas