Eliminar valores de una matriz dinámica con tabla de exclusiones y REDUCE

Juan tiene una matriz generada con fórmulas de desbordamiento y necesita eliminar ciertos textos (nombres de áreas geográficas como "Área metropolitana", "Tarragona") sin convertir los datos a valores. El problema es que no puede simplemente editar celdas que forman parte de un array dinámico.

Un miembro de la comunidad (+34 617 05 62 11) resuelve el problema de forma progresiva. Primero, una versión para un solo valor a excluir:

``
=LET(
m; influ;
p; TOMAR(m;;1);
t; ELEGIRCOLS(m;2);
o; TOMAR(m;;-1);
APILARH(p; SI(t="Área metropolitana"; ""; t); o)
)
`

Pero Juan necesita excluir múltiples valores, así que la solución evoluciona. Se crea una Tabla2 con los términos a excluir y se usa REDUCE para iterar sobre ellos:

`
=LET(
l; Tabla2[Columna1];
m; influ;
p; TOMAR(m;;1);
t; ELEGIRCOLS(m;2);
o; ELEGIRCOLS(m;3);
q; TOMAR(m;;-1);
output; APILARH(p;
REDUCE(t; l; LAMBDA(X;Y; SI(X=Y; ""; X)));
o; q
);
SI(output=0; ""; output)
)
`

La idea clave es el REDUCE` sobre la columna a limpiar: itera sobre cada término de exclusión y lo reemplaza por cadena vacía. Al usar una tabla como fuente de exclusiones, basta con añadir una fila a Tabla2 para excluir un nuevo valor, sin tocar la fórmula.

Detalle importante: hay que vigilar los espacios en blanco al escribir los términos en la tabla de exclusiones — un espacio de más y la comparación falla silenciosamente.

Cuando trabajas con matrices dinámicas hay una regla que descoloca al principio: no puedes editar una celda del resultado. El array se derrama entero o no se derrama. Juan se topó con esa pared al querer eliminar ciertos textos de una matriz generada por fórmula, y la solución que salió en la comunidad convierte la limitación en una ventaja.

El problema

Juan tiene una matriz derramada con datos geográficos y necesita borrar ciertos valores de una de sus columnas: nombres de áreas como "Área metropolitana" o "Tarragona". Lo que no quiere es convertir el resultado a valores estáticos, porque perdería la actualización automática.

Con una matriz normal bastaría con seleccionar y suprimir. Con un array dinámico no hay tal opción: hay que hacerlo dentro de la fórmula.

Primera versión: un solo valor a excluir

Un miembro de la comunidad plantea la solución de forma progresiva. Empieza por el caso más simple, excluir un único término:

=LET(
    m; influ;
    p; TOMAR(m; ; 1);
    t; ELEGIRCOLS(m; 2);
    o; TOMAR(m; ; -1);
    APILARH(p; SI(t = "Área metropolitana"; ""; t); o)
)

La estrategia es descomponer y recomponer:

  • TOMAR(m; ; 1) se queda con la primera columna
  • ELEGIRCOLS(m; 2) aísla la columna que hay que limpiar
  • TOMAR(m; ; -1) recoge la última columna
  • APILARH vuelve a montar la matriz, sustituyendo por el camino la columna del medio por su versión filtrada

El SI hace el trabajo sucio: donde el texto coincide, devuelve cadena vacía; donde no, deja el valor.

Funciona, pero tiene un problema evidente de mantenimiento: el término a excluir está escrito dentro de la fórmula. Cada nueva exclusión obliga a editarla.

Versión definitiva: tabla de exclusiones con REDUCE

La solución madura mueve la lista de términos a una tabla aparte (Tabla2) y usa REDUCE para aplicarlos todos:

=LET(
    l; Tabla2[Columna1];
    m; influ;
    p; TOMAR(m; ; 1);
    t; ELEGIRCOLS(m; 2);
    o; ELEGIRCOLS(m; 3);
    q; TOMAR(m; ; -1);
    output; APILARH(p;
        REDUCE(t; l; LAMBDA(X; Y; SI(X = Y; ""; X)));
        o; q
    );
    SI(output = 0; ""; output)
)

El corazón está en una sola línea:

REDUCE(t; l; LAMBDA(X; Y; SI(X = Y; ""; X)))

REDUCE arranca con la columna completa t como acumulador (no con un 0, como es habitual) y recorre cada término Y de la lista de exclusiones. En cada vuelta, SI(X = Y; ""; X) compara la columna entera con ese término y vacía las coincidencias. La columna sale de cada iteración un poco más limpia, y entra así en la siguiente.

El resultado es que la lógica queda desacoplada de los datos: para excluir un término nuevo basta con añadir una fila a Tabla2. La fórmula no se toca nunca más.

El SI(output = 0; ""; output) final es un remate cosmético: las celdas vaciadas pueden mostrarse como ceros según el contexto, y esto las deja en blanco.

El detalle que hace fallar la fórmula en silencio

Hay un aviso importante en el caso, y es de los que cuestan una tarde: vigila los espacios en blanco en los términos de la tabla de exclusiones.

La comparación X = Y es exacta. Un espacio de más al final de "Tarragona " y la coincidencia no se produce. Y lo peor es que no da error: simplemente el valor no se elimina, y tú miras la fórmula sin entender por qué.

Si la lista la escriben varias personas, envolver la comparación con ESPACIOS sobre ambos lados es un seguro barato contra este fallo.

Funciones clave

  • REDUCE — aplica la lista de exclusiones una a una sobre la columna, arrastrando el resultado
  • LAMBDA — define la operación de limpieza que se repite en cada vuelta
  • TOMAR — extrae las columnas de los extremos, con índice negativo para contar desde el final
  • ELEGIRCOLS — aísla columnas concretas por posición
  • APILARH — recompone la matriz con la columna ya limpia
  • SI — la comparación que vacía las coincidencias

Conclusión

El patrón que deja este caso es reutilizable en cualquier contexto: descomponer la matriz en columnas, transformar la que interesa y recomponer con APILARH. Y cuando la transformación depende de una lista variable, REDUCE sobre una tabla externa te ahorra editar la fórmula para siempre.

Este es el tipo de reto que se resuelve a varias manos en el grupo de InflueXcel: alguien plantea el problema real, otro propone la versión simple, y entre todos acaba saliendo la que aguanta en producción.

Más casos con estas funciones

Más contenido de Excel en InflueXcel