Las fórmulas matriciales permiten que una sola fórmula procese varios valores a la vez y devuelva varios resultados. Antes de Excel 365, las matrices eran una habilidad de nicho que requería una combinación de teclas especial. Las matrices dinámicas de Excel 365 cambiaron eso: funciones como FILTRAR, ORDENAR y UNICOS funcionan como matrices automáticamente y derraman sus resultados en las celdas contiguas sin pasos adicionales.
¿Qué son las matrices en Excel?
Una matriz es un conjunto de valores tratados como una sola unidad dentro de una fórmula. Piénsalo como si Excel hiciera un bucle por ti.
Ejemplo sin matrices: para sumar solo los valores cuya categoría sea «Ventas» escribirías: =SUMAR.SI(A2:A100;"Ventas";B2:B100) Cómo funcionan las matrices por dentro: SUMAR.SI multiplica una prueba lógica (¿A es «Ventas»?) por los valores correspondientes de B y suma los resultados; en el fondo es una operación matricial que la función gestiona internamente.Cuando escribes tus propias fórmulas matriciales aprovechas ese mismo mecanismo para tareas que ninguna función integrada cubre.
Fórmulas matriciales CSE antiguas (Excel 2019 y anteriores)
Antes de las matrices dinámicas, las fórmulas matriciales se introducían presionando Ctrl+Mayús+Entrar (CSE) en lugar de solo Entrar. Excel envolvía la fórmula entre llaves {} para indicar que era matricial.
Ejemplo: contar las celdas de B2:B100 que sean mayores que 100 Y que en la columna A digan «Norte»: {=SUMA((A2:A100="Norte")*(B2:B100>100))}Las llaves las agrega Excel automáticamente al presionar Ctrl+Mayús+Entrar: nunca las escribas a mano.
Limitación clave: una fórmula matricial CSE ocupa exactamente una celda. No puede derramar resultados en varias celdas, que es una característica de las matrices dinámicas. Dónde sigues viendo matrices CSE: en libros, plantillas y tutoriales antiguos escritos antes de 2020 se usan mucho. Conviene reconocerlas. Si tienes Excel 365, normalmente puedes sustituirlas por fórmulas de matriz dinámica más limpias.Matrices dinámicas (Excel 365 y Excel 2021)
Las matrices dinámicas derraman sus resultados en las celdas contiguas de forma automática. No hace falta Ctrl+Mayús+Entrar. Si una fórmula devuelve 10 valores, rellena 10 celdas sola.
Rango de derrame: el borde azul alrededor de las celdas donde se ha derramado una matriz dinámica. Si otra celda bloquea el derrame, obtienes un error #¡DESBORDAMIENTO!: mueve o borra la celda que estorba. Hacer referencia a un rango de derrame: usa la dirección de la celda de la fórmula seguida de #. Por ejemplo, si tu fórmula está en A1 y se derrama hacia abajo, =A1# hace referencia a todo el rango de derrame de forma dinámica.FILTRAR: extraer las filas que cumplen una condición
=FILTRAR(matriz; incluir; [si_vacío])FILTRAR devuelve solo las filas de un rango donde la condición es VERDADERO.
Ejemplo: mostrar solo las filas donde la columna C (Estado) sea «Abierto»: =FILTRAR(A2:D100; C2:C100="Abierto"; "Sin resultados")El tercer argumento («Sin resultados») se muestra si ninguna fila coincide, y evita un error #¡CALC!.
Varias condiciones (Y): =FILTRAR(A2:D100; (C2:C100="Abierto")*(B2:B100>1000); "Sin resultados")Multiplica las condiciones para la lógica Y. Usa + para la lógica O.
Varias condiciones (O): =FILTRAR(A2:D100; (C2:C100="Abierto")+(C2:C100="Pendiente"); "Sin resultados")FILTRAR sustituye a las combinaciones complicadas de SUMAR.SI y CONTAR.SI, y elimina la necesidad de mantener a mano tablas filtradas aparte.
ORDENAR y ORDENARPOR: ordenar datos con una fórmula
=ORDENAR(matriz; [índice_orden]; [orden]; [por_columna])Orden: 1 = ascendente (predeterminado), -1 = descendente
Ejemplo: ordenar el rango A2:D100 por la columna 3 de forma descendente: =ORDENAR(A2:D100; 3; -1) =ORDENARPOR(matriz; por_matriz1; [orden1]; [por_matriz2]; [orden2]; ...)ORDENARPOR es más flexible: ordena por una matriz que ni siquiera aparece en el resultado.
Ejemplo: ordenar una lista de productos por una columna de puntuación aparte que no quieres mostrar: =ORDENARPOR(A2:B50; C2:C50; -1)Esto devuelve las columnas A y B ordenadas por los valores de la columna C (descendente), sin incluir C en el resultado.
Combinarlo con FILTRAR: =ORDENAR(FILTRAR(A2:D100; C2:C100="Abierto"); 2; 1)Filtra las filas «Abierto» y luego ordena ese resultado por la columna 2 de forma ascendente, todo en una sola fórmula.
UNICOS: extraer una lista sin repetidos
=UNICOS(matriz; [por_columna]; [exactamente_una_vez])Devuelve una lista de valores sin duplicados. Sin columnas auxiliares, sin Quitar duplicados y sin tabla dinámica.
Ejemplo: obtener una lista única de clientes de A2:A500: =UNICOS(A2:A500) Combinaciones únicas entre columnas: deja por_columna en FALSO (el valor predeterminado) y pasa varias columnas: =UNICOS(A2:B500), que devuelve las combinaciones únicas de Nombre y Región Valores que aparecen exactamente una vez (no solo valores distintos): pon exactamente_una_vez en VERDADERO: =UNICOS(A2:A500; FALSO; VERDADERO), que devuelve los valores que no tienen ningún duplicado Combinado con ORDENAR para tener el origen de un desplegable limpio: =ORDENAR(UNICOS(A2:A500))SECUENCIA: generar series de números
=SECUENCIA(filas; [columnas]; [inicio]; [paso])Genera una matriz bidimensional de números consecutivos.
Ejemplos:- =SECUENCIA(10) → los números del 1 al 10 en una columna
- =SECUENCIA(5;3) → una cuadrícula de 5 filas por 3 columnas con los números del 1 al 15
- =SECUENCIA(12;1;1;1) → los meses del 1 al 12
- =SECUENCIA(10;1;0;5) → 0, 5, 10, 15... (10 valores, paso de 5)
BUSCARX con matrices
BUSCARX maneja búsquedas matriciales de forma nativa y sustituye tanto a BUSCARV como a BUSCARH.
=BUSCARX(valor_buscado; matriz_buscada; matriz_devuelta; [si_no_se_encuentra]; [modo_de_coincidencia]; [modo_de_búsqueda]) Devolver varias columnas a la vez: pon en matriz_devuelta un rango de varias columnas: =BUSCARX(G2; A2:A100; B2:D100), que busca G2 en la columna A y devuelve la fila entera de B a D Varias búsquedas a la vez (una matriz de valores buscados): =BUSCARX(G2:G10; A2:A100; B2:B100), que devuelve 10 resultados para 10 valores buscados simultáneamente Coincidencia aproximada para rangos: el parámetro modo_de_coincidencia (el cuarto argumento, después de si_no_se_encuentra):- 0 = coincidencia exacta (predeterminado)
- -1 = exacta o el siguiente menor
- 1 = exacta o el siguiente mayor
Preguntas frecuentes
¿Qué es el error #¡DESBORDAMIENTO! y cómo lo arreglo?
Un error #¡DESBORDAMIENTO! significa que la fórmula intenta derramar sobre celdas que no están vacías. Haz clic en la celda de la fórmula: unas líneas azules punteadas muestran dónde quiere derramarse. Borra o mueve lo que haya en esas celdas. Causa habitual: un valor escondido en una celda que parece vacía (un espacio). Selecciona cada celda que estorba y presiona Supr.
¿Las fórmulas de matriz dinámica funcionan en versiones antiguas de Excel?
No. FILTRAR, ORDENAR, ORDENARPOR, UNICOS y SECUENCIA requieren Excel 365 o Excel 2021. En Excel 2019 o anterior, estas funciones muestran errores #¿NOMBRE?. Si compartes archivos con gente que usa versiones antiguas, no podrán usar ni editar esas fórmulas. Para compatibilidad, la alternativa son las fórmulas matriciales CSE, aunque resulten menos elegantes.
¿Puedo usar FILTRAR para devolver los datos con una forma concreta?
Sí. FILTRAR devuelve las mismas columnas que la matriz de entrada. Para devolver solo algunas columnas, envuélvelo con ELEGIR o usa varias llamadas a FILTRAR. En Excel 365 también puedes pasar una matriz horizontal de índices de columna a ELEGIRCOLS:
=ELEGIRCOLS(FILTRAR(A2:D100; C2:C100="Abierto"); 1; 3), que devuelve solo las columnas 1 y 3 del resultado filtrado.
¿Hay diferencia de rendimiento entre las matrices CSE y las dinámicas?
Las fórmulas de matriz dinámica suelen ser más rápidas y eficientes que sus equivalentes CSE, porque se diseñaron desde cero para el motor de cálculo moderno de Excel. Evita funciones volátiles como DESREF o INDIRECTO dentro de fórmulas matriciales: se recalculan con cada cambio, haya cambiado o no su entrada.
¿Puedo usar UNICOS o FILTRAR como origen de una lista desplegable de validación de datos?
No directamente: el campo Origen de la validación de datos no acepta referencias a rangos de derrame (=A1#). Solución alternativa: ponle nombre al rango de derrame de UNICOS o FILTRAR usando DESREF con CONTARA para crear un rango con nombre dinámico y haz referencia a ese nombre en la validación. En Excel 365, crear una tabla sobre el resultado derramado y usar la columna de la tabla en la validación es un enfoque más limpio.