Cómo usar fórmulas matriciales para cálculos avanzados en Excel

Las fórmulas matriciales son una de las herramientas más potentes de Excel.

A diferencia de las fórmulas tradicionales que trabajan con un solo valor, las fórmulas matriciales operan con una serie de datos (una matriz) para producir uno o varios resultados.

Dominarlas te permitirá realizar operaciones complejas que de otra forma requerirían múltiples columnas auxiliares o engorrosas combinaciones de funciones.

¿Qué ha cambiado? Tradicionales (CSE) vs. Dinámicas

Antes de empezar, es crucial entender que existen dos tipos de fórmulas matriciales, dependiendo de la versión de Excel que uses.

• Fórmulas matriciales tradicionales (CSE): En versiones anteriores a Excel 365, para crear una fórmula matricial había que seleccionar el rango de resultados, escribir la fórmula y presionar Ctrl + Mayús + Enter (CSE). Al hacerlo, Excel las envolvía automáticamente entre llaves {} . Este método sigue funcionando, pero es más estático y propenso a errores.

• Fórmulas de matriz dinámicas (Excel 365 y Excel 2021): Con la llegada de los "arrays dinámicos", el proceso se ha simplificado drásticamente. Ahora, escribes la fórmula en una sola celda y presionas Enter. El resultado se "desborda" (spill) automáticamente hacia las celdas adyacentes necesarias. Este rango de resultados se llama "rango de desbordamiento" y se referencia con el operador # (Ej. A2#). Este tutorial se centrará en este enfoque moderno, ya que es el futuro de Excel.

Funciones Clave para Cálculos Avanzados con Matrices Dinámicas

Excel 365 incorpora funciones específicamente diseñadas para trabajar con matrices. Aquí tienes las más útiles.

FILTER (FILTRAR): Extraer datos dinámicamente

Esta función te permite filtrar un rango de datos basándote en uno o varios criterios. El resultado se actualiza automáticamente si cambian los datos de origen.

• Sintaxis: =FILTER(matriz, incluir, [si_vacío])

• Ejemplo práctico: Imagina una tabla con ventas. Quieres extraer todas las filas donde las ventas sean mayores a 100.

  • Fórmula: =FILTER(A2:B100, B2:B100>100, "Sin resultados")

  • Explicación: Esta fórmula, escrita en una celda, devolverá todas las filas de A2:B100 donde el valor correspondiente en B2:B100 sea superior a 100. El resultado se desbordará en las celdas de abajo.

UNIQUE (ÚNICOS): Obtener valores distintos

Extrae una lista de valores únicos de un rango, eliminando automáticamente los duplicados.

• Sintaxis: =UNIQUE(matriz, [por_columna], [una_vez])

• Ejemplo práctico: Tienes una lista de productos en la columna A con muchos repetidos y quieres generar una lista limpia de productos únicos.

  • Fórmula: =UNIQUE(A:A)

  • Explicación: Esta fórmula devolverá una lista dinámica con cada producto una sola vez, desbordándose hacia abajo. Si añades un nuevo producto a la columna A, la lista se actualizará sola.

SORT y SORTBY (ORDENAR): Ordenar datos dinámicamente

Permiten ordenar un rango de datos sin alterar el original.

• Sintaxis:

  • =SORT(matriz, [índice_orden], [orden_orden], [por_columna])

  • =SORTBY(matriz, matriz_orden1, [orden1], ...)

• Ejemplo práctico: Quieres ordenar tu lista de productos (columna A) y sus precios (columna B) de mayor a menor precio.

  • Fórmula: =SORT(A2:B20, 2, -1)

  • Explicación: SORT toma el rango A2:B20 y lo ordena por la segunda columna (índice_orden = 2) en orden descendente (-1) . SORTBY es más flexible, permitiendo ordenar basándose en otros rangos no incluidos en la matriz de resultado.

SUMPRODUCT (SUMAPRODUCTO): El todoterreno condicional

Aunque no es una función de matriz dinámica nueva, su capacidad para trabajar con matrices la convierte en una herramienta fundamental para cálculos condicionales complejos sin necesidad de columnas auxiliares.

• Sintaxis: =SUMPRODUCT(matriz1, [matriz2], ...)

• Ejemplo práctico: Calcula las ventas totales de un producto específico ("Manzanas") en una tabla donde la columna A tiene el producto y la columna B las ventas.

  • Fórmula: =SUMPRODUCT((A2:A100="Manzanas") * (B2:B100))

  • Explicación: La expresión (A2:A100="Manzanas") crea una matriz de VERDADERO/FALSO que Excel convierte en 1 y 0. SUMPRODUCT multiplica estos 1 y 0 por las ventas y suma el resultado, dando el total solo de las filas que cumplen la condición.

Concepto Avanzado: Combinar Rangos en una Matriz

A veces los datos que necesitas están en rangos separados. Las funciones como VSTACK (apilar verticalmente) o HSTACK (apilar horizontalmente) te permiten combinarlos en una única matriz para usarlos en otras funciones.

• Ejemplo: Tienes listas de empleados en dos columnas diferentes (A2:A5 y C2:C5). Quieres buscar la edad de "Juan" en ambas listas. Puedes combinarlas dentro de un XLOOKUP:

  • Fórmula: =XLOOKUP("Juan", VSTACK(A2:A5, C2:C5), VSTACK(B2:B5, D2:D5))

  • Explicación: VSTACK combina los rangos de búsqueda en una sola matriz vertical, y XLOOKUP busca en ella como si fuera un único rango.

Errores Comunes y Buenas Prácticas

• #¡DESBORDE!: Es el error más común. Ocurre cuando las celdas donde la fórmula intenta "derramar" sus resultados no están vacías. La solución es borrar el contenido de esas celdas.

• Referencias de rango completo: Evita usar columnas enteras (ej. A:A) dentro de funciones matriciales con grandes conjuntos de datos, ya que puede ralentizar drásticamente el cálculo. Es mejor acotar el rango al necesario (ej. A2:A10000) o, idealmente, convertir tus datos en una Tabla de Excel, cuyas referencias estructuradas se expanden de forma eficiente.

• No se pueden usar en Tablas de Excel: Las fórmulas de desbordamiento no se pueden escribir dentro de una celda que forme parte de una Tabla de Excel. Sin embargo, puedes escribir la fórmula fuera de la tabla y referenciar los datos dentro de la tabla.

Conclusión

Las fórmulas de matriz dinámica han revolucionado la forma de trabajar con Excel. Al dominar funciones como FILTER, UNIQUE, SORT y SUMPRODUCT, podrás crear hojas de cálculo más eficientes, dinámicas y fáciles de mantener, elevando tu capacidad de análisis de datos a un nivel profesional.