Influcharlas: John y Leo domando matrices

Influcharlas: John y Leo domando matrices

Esta vez resolvemos un par de casos con las explicaciones de @JohnVergaraD y @LEO_rumano.

En esta sesión de las Influcharlas, la comunidad de InflueXcel recupera el espíritu de sus primeras charlas en directo: compartir casos reales que van surgiendo en el grupo de WhatsApp y desgranar entre todos por qué una solución funciona. John y Leo nos guían a través de dos retos centrados en algo que apasiona a la comunidad: domar matrices dinámicas en Excel. A continuación tienes el resumen de lo que se explicó, con las funciones y la lógica detrás de cada solución.

El reto de John: totalizar tres pseudotablas con encabezados desordenados

El primer caso lo planteó Juan en el chat. Tenía la información organizada en tres tablas (en realidad, tres rangos de celdas) apiladas una encima de otra, con un número variable de filas y con las columnas en distintas posiciones. El objetivo: a partir de unos encabezados de referencia, extraer y totalizar las columnas correctas de cada tabla, sin importar dónde estuvieran.

Elegir columnas con ELEGIRCOLS

La pieza central es ELEGIRCOLS, una función que permite extraer las columnas que quieras de una matriz o un rango. Tiene dos formas de usarla:

  • Pasando los índices uno a uno como argumentos: ELEGIRCOLS(rango; 1; 4; 7; 10).
  • Pasando una sola constante matricial con todos los índices en un único argumento.

Calcular los índices con COINCIDIRX

Para no localizar a mano la posición de cada encabezado, John usa COINCIDIRX. La recomendación es clara: si tienes Microsoft 365 y buscas coincidencia exacta, usa COINCIDIRX en lugar de COINCIDIR, porque trabaja con coincidencia exacta por defecto y te ahorra el tercer argumento. Pasándole como valor buscado la matriz de encabezados deseados y como matriz buscada la primera fila del rango, devuelve una constante matricial con los índices que ELEGIRCOLS necesita (se puede comprobar con F9).

Encapsular la lógica en una LAMBDA con LET

Para no repetir la misma fórmula en cada tabla, John crea una función propia con LET y LAMBDA, asignándole un nombre (f). La función recibe una sola entrada, el rango (R), y dentro:

  • Toma la primera fila del rango con TOMAR para obtener los encabezados.
  • Calcula los índices con COINCIDIRX.
  • Devuelve las columnas con ELEGIRCOLS, excluyendo la primera fila para no arrastrar los encabezados.

Apilar, agrupar y sumar

Con la función lista, se apilan los tres resultados con APILARV, igualando el ancho de los rangos (por ejemplo, hasta la columna Z) para cubrir cualquier número de columnas. Después se guarda esa base en una variable (b) y se aplica AGRUPARPOR:

  • Primer argumento: la primera columna (la de trabajador), que marca el sentido de las filas.
  • Segundo argumento: las columnas a sumar, usando EXCLUIR para quitar la primera columna.
  • Función de agregación: SUMA.
  • Último argumento: un 3 para indicar que la primera fila es el encabezado y debe mostrarse.

El resultado es una totalización limpia, con encabezados incluidos, generada de forma totalmente dinámica. John también compartió su filosofía de trabajo: primero monta la maqueta mental de variables y, una vez resuelto, vuelve sobre la fórmula para optimizar pasos, como reutilizar la apilación dentro de la base para evitar repetir APILARV.

El reto de Leo: buscar el último elemento sin concatenar

El segundo caso lo trajo Leo. Juan buscaba todas las coincidencias de una factura y las concatenaba con MATRIZATEXTO, pero solo le interesaba el último resultado. La solución directa evita todo ese rodeo.

BUSCARX desde el final

BUSCARX permite buscar un valor en toda una columna y devolver la coincidencia, trabajando con rangos completos. La clave está en su último argumento, el modo de búsqueda: poniéndolo en negativo (-1), la búsqueda empieza desde el final hacia el principio, devolviendo directamente la última coincidencia. Sin necesidad de concatenar ni separar nada.

TEXTODESPUES para extraer el último de una cadena

Si ya partes de una cadena con varios elementos separados por un delimitador, la forma más sencilla de quedarte con el último es TEXTODESPUES. Indicando el texto, el delimitador (por ejemplo ; seguido de un espacio, como lo genera MATRIZATEXTO) y la instancia en negativo (-1), la función cuenta desde el final y devuelve el último fragmento. Igual que TOMAR, el signo negativo cambia el sentido del recorrido.

Un apunte sobre delimitadores

Leo recomienda evitar MATRIZATEXTO cuando vas a compartir el archivo: el delimitador depende de la configuración regional y puede generar conflictos al dividir después. En su lugar, sugiere UNIRCADENAS, donde el delimitador se especifica de forma explícita.

Funciones que devuelven referencia

La charla derivó en un tema interesante: BUSCARV devuelve un valor, mientras que BUSCARX devuelve una referencia. Pero no es la única: DESREF, INDIRECTO, INDICE, TOMAR y EXCLUIR también devuelven referencias cuando trabajan con rangos contiguos (no con matrices). Esto importa porque funciones como las del tipo .SI.CONJUNTO o JERARQUIA solo trabajan con rangos, no con matrices.

Conclusión

Más allá de las funciones concretas, la sesión deja una idea de fondo: en Excel moderno lo difícil no es conocer ELEGIRCOLS, AGRUPARPOR o BUSCARX, sino saber en qué momento y cómo aplicarlas, encajando las piezas como en un Tetris. Las matrices dinámicas, con relativamente pocas funciones, ofrecen una enorme combinatoria, y herramientas como Excel Labs ayudan a leer las fórmulas línea a línea y hacerlas mucho más comprensibles. El consejo final de la comunidad es claro: si aún no lo haces, da el siguiente paso y métete de lleno con las matrices.

Más contenido de Excel en InflueXcel