BYROW+checkbox para filtrado dinámico en control alimentario (APPCC)

Rosario trabaja en seguridad alimentaria y tiene un sistema APPCC (Análisis de Peligros y Puntos de Control Crítico) montado en Excel para una fábrica de aceitunas. La estructura es compleja: una tabla con 60 pasos de proceso en filas y 28 tipos de producto en columnas, donde cada celda es TRUE/FALSE indicando si ese paso aplica a ese producto. Además, una segunda tabla con 793 filas de peligros vinculados a cada paso.

El reto: seleccionar un producto en un desplegable y que automáticamente se muestren solo los pasos que le aplican, y luego filtrar los peligros asociados a esos pasos. Con 28 productos y 60 pasos, hacerlo a mano es inviable.

Leo resuelve el primer problema con una combinación de ELEGIRCOLS y COINCIDIRX que selecciona dinámicamente la columna del producto elegido:

``
=ELEGIRCOLS(Tabla1[]; COINCIDIRX(E2; Tabla1[#Encabezados]))
`

Y para obtener los pasos donde ese producto tiene TRUE:

`
=FILTRAR(
Tabla1[PASO];
ELEGIRCOLS(Tabla1[]; COINCIDIRX(E2; Tabla1[#Encabezados]))
)
`

COINCIDIRX localiza la posición del nombre del producto en los encabezados de la tabla, y ELEGIRCOLS extrae esa columna entera. Luego FILTRAR devuelve solo los pasos donde el valor es TRUE. Todo dinámico: cambias el desplegable y se actualiza al instante.

Para el segundo nivel de filtrado (traer los peligros asociados a esos pasos), la comunidad aporta varias técnicas. John propone usar CONTAR.SI como puente entre las dos tablas:

`
=FILTRAR(C3:C12; CONTAR.SI(E3:E4; B3:B12))
`

Y una alternativa con división perpendicular que filtra descartando errores:

`
=ENCOL(C3:C12 / (B3:B12 = ENFILA(E3:E4)); 2)
`

Tamer completa con fórmulas para BYROW + Y/O (AND/OR) que permiten filtrar según si TODAS o AL MENOS UNA casilla está marcada:

`
=SORT(FILTER(B5:B10; BYROW(C5:F10; AND)))
``

El fichero adjunto incluye tanto el planteamiento original (la estructura APPCC con las tablas de fases, pasos y peligros) como el fichero con la solución implementada. Un caso muy práctico donde Excel resuelve un problema real de gestión de calidad industrial.

Un APPCC de verdad, montado en Excel

Rosario trabaja en seguridad alimentaria y tiene un sistema APPCC (Análisis de Peligros y Puntos de Control Crítico) para una fábrica de aceitunas. La estructura no es pequeña:

  • Una tabla con 60 pasos de proceso en filas y 28 tipos de producto en columnas, donde cada celda indica con VERDADERO o FALSO si ese paso aplica a ese producto.
  • Una segunda tabla con 793 filas de peligros, cada uno vinculado a un paso.

Lo que necesita es sencillo de enunciar y muy pesado de hacer a mano: seleccionar un producto en un desplegable y que aparezcan automáticamente solo los pasos que le aplican, y después solo los peligros asociados a esos pasos. Con 28 productos y 60 pasos, la vía manual es inviable.

Primer nivel: extraer la columna del producto elegido

Leo resuelve la primera mitad con una pareja de funciones que se complementan muy bien. La idea es localizar en qué columna está el producto seleccionado y quedarse con ella entera:

=ELEGIRCOLS(Tabla1[]; COINCIDIRX(E2; Tabla1[#Encabezados]))

COINCIDIRX busca el nombre del producto en la fila de encabezados de la tabla y devuelve su posición. ELEGIRCOLS usa esa posición para extraer la columna completa. Cambias el desplegable y la columna extraída cambia con él, sin INDIRECTO ni referencias construidas con texto.

El detalle que lo hace robusto es Tabla1[#Encabezados]: al referirse a los encabezados de la tabla estructurada, el sistema sigue funcionando aunque alguien reordene las columnas o añada un producto nuevo.

Con la columna en la mano, obtener los pasos aplicables es inmediato:

=FILTRAR(
    Tabla1[PASO];
    ELEGIRCOLS(Tabla1[]; COINCIDIRX(E2; Tabla1[#Encabezados]))
)

FILTRAR acepta directamente la columna de VERDADERO y FALSO como condición, sin necesidad de compararla con nada. Los valores lógicos ya son la máscara.

Segundo nivel: traer los peligros de esos pasos

Aquí el filtro se complica, porque hay que cruzar contra una lista de pasos que también es dinámica. La comunidad aportó dos técnicas distintas.

CONTAR.SI como puente entre tablas

John propone usar CONTAR.SI con el rango de pasos seleccionados como criterio:

=FILTRAR(C3:C12; CONTAR.SI(E3:E4; B3:B12))

Es un uso poco conocido de CONTAR.SI: al pasarle una matriz como criterio devuelve una matriz de recuentos, uno por cada elemento. Para cada peligro, cuenta cuántas veces su paso aparece en la lista de pasos aplicables. Si el resultado es cero, no pasa el filtro. Un "está en la lista" escrito en muy pocos caracteres.

La comparación perpendicular con división

La alternativa es más matricial y aprovecha un truco muy elegante:

=ENCOL(C3:C12 / (B3:B12 = ENFILA(E3:E4)); 2)

ENFILA pone la lista de pasos seleccionados en horizontal, mientras la columna de peligros sigue en vertical. Al compararlas, Excel genera una matriz de dos dimensiones: tantas filas como peligros y tantas columnas como pasos, con verdadero en cada cruce válido.

Al dividir los valores por esa matriz, las coincidencias devuelven el valor y las no coincidencias devuelven un error de división por cero. Después, ENCOL con el argumento 2 aplana el resultado descartando los errores. Lo que queda son exactamente los peligros que aplican.

Es una técnica que aparece mucho en la comunidad: usar el error como marcador de descarte y dejar que la función de aplanado limpie.

Tercer nivel: filtrar por combinación de casillas

Tamer completa el caso con el escenario de las casillas de verificación: varias columnas de VERDADERO y FALSO, y la necesidad de filtrar según si todas o al menos una están marcadas.

=ORDENAR(FILTRAR(B5:B10; BYROW(C5:F10; Y)))

BYROW recorre fila a fila y aplica la función indicada. Con Y, una fila pasa el filtro solo si todas sus casillas están marcadas. Cambiando Y por O, basta con que lo esté una. Pasar la función como argumento, sin paréntesis, es la parte que sorprende a todo el mundo la primera vez: en Excel moderno una función es un valor más.

Funciones clave

  • COINCIDIRX — devuelve la posición de un valor. Sobre [#Encabezados], localiza columnas por nombre.
  • ELEGIRCOLS — extrae columnas por posición. La pareja natural de COINCIDIRX para selección dinámica.
  • FILTRAR — admite valores lógicos directamente como condición, sin comparar con nada.
  • CONTAR.SI con criterio matricial — devuelve un recuento por elemento. Un "pertenece a esta lista" muy compacto.
  • ENFILA — transpone a horizontal para provocar comparaciones perpendiculares.
  • ENCOL — aplana una matriz y, con el argumento adecuado, descarta errores por el camino.
  • BYROW con Y u O — evalúa cada fila completa y devuelve un único booleano.

Conclusión

Lo interesante de este caso es que no hay una fórmula milagrosa, sino tres niveles encadenados: seleccionar la columna del producto, filtrar los pasos aplicables y cruzar esos pasos contra la tabla de peligros.

Cada nivel tiene su técnica, y todas comparten el mismo principio: nada de columnas auxiliares, nada de repetir la fórmula 28 veces. Se cambia el desplegable y el sistema entero se recalcula.

Un buen ejemplo de que Excel, bien usado, resuelve problemas reales de gestión de calidad industrial sin salir de la hoja.

Más casos con estas funciones

Más contenido de Excel en InflueXcel