Seguro que alguna vez te has peleado con cientos de fórmulas en una hoja de cálculo para sacar un informe complejo. De haber sabido las fórmulas matriciales, es probable que todo habría sido más fácil. Son una de esas funcionalidades avanzadas de Excel que la mayoría de usuarios pasa por alto, pero que marcan la diferencia cuando empiezas a hacer cálculos serios.
Las fórmulas matriciales permiten a Excel procesar muchos valores a la vez, realizar cálculos intermedios de forma “invisible” y devolver uno o varios resultados sin tener que llenar la hoja de columnas auxiliares. Dan respeto al principio, pero una vez les pillas el truco se convierten en una herramienta brutal para análisis, informes y modelos complejos.
Qué es una matriz y qué es exactamente una fórmula matricial
Antes de hablar de fórmulas matriciales hay que entender qué es una matriz en Excel. Una matriz no es más que un conjunto de valores tratados como un bloque: pueden ser números o textos y pueden estar en una sola fila, en una sola columna o en un rango de varias filas y columnas.
Un ejemplo clásico de matriz es la lista de meses del año. Imagina una constante matricial con todos los meses: {«Enero»;»Febrero»;»Marzo»;»Abril»;»Mayo»;»Junio»;»Julio»;»Agosto»;»Septiembre»;»Octubre»;»Noviembre»;»Diciembre»}. Si seleccionas 12 celdas en una columna, escribes ={«Enero»;…;»Diciembre»} y confirmas como fórmula matricial, Excel rellenará todas esas celdas de golpe.
Una fórmula matricial es una fórmula que trabaja con matrices en lugar de con valores sueltos. La gran diferencia frente a las fórmulas “normales” es que puede:
- Recibir varios valores como entrada (todo un rango o una constante de varios elementos).
- Realizar múltiples cálculos internos sobre cada uno de esos elementos.
- Devolver un único valor o toda una matriz de resultados.
Piensa en la fórmula matricial como un cálculo masivo en paralelo: Excel evalúa elemento a elemento, guarda resultados intermedios en memoria y te muestra solo el resultado final (una celda o un bloque de celdas), sin que tengas que ver ni mantener los pasos intermedios.
Cómo introducir y reconocer una fórmula matricial en Excel
La seña de identidad de una fórmula matricial clásica son las llaves { } que ves en la barra de fórmulas cuando la celda está seleccionada. Esas llaves no las escribes tú, las añade Excel automáticamente cuando confirmas la fórmula correctamente.
Para introducir una fórmula matricial en versiones tradicionales de Excel (antes de las fórmulas dinámicas), el proceso es este:
- Escribe la fórmula como lo harías normalmente, por ejemplo: =SUMA(B2:B11*C2:C11).
- No pulses solo Intro. Debes pulsar la combinación Ctrl + Mayús + Intro (Ctrl + Shift + Enter).
- Excel rodeará la fórmula con llaves en la barra de fórmulas: {=SUMA(B2:B11*C2:C11)}. Eso indica que la está tratando como matricial.
Hay varios detalles importantes que conviene tener muy claros para no volverte loco con ellas:
- No sirve escribir tú las llaves a mano. Si tecleas { y } alrededor de la fórmula, Excel la seguirá tratando como una fórmula normal; solo la combinación de teclas la convierte en matricial.
- Al editar una fórmula matricial, las llaves desaparecen temporalmente. Tras modificarla, tendrás que pulsar otra vez Ctrl + Mayús + Intro para que vuelva a ser matricial.
- Si olvidas usar Ctrl + Mayús + Intro, la fórmula se evaluará como si fuese normal y probablemente solo usará el primer elemento de cada rango, devolviendo un resultado incorrecto.
- En fórmulas matriciales que devuelven varios resultados (varias celdas), primero debes seleccionar el rango de salida completo, escribir la fórmula y, después, confirmar con Ctrl + Mayús + Intro.
Además, puedes “espiar” cómo se calculan partes de una fórmula matricial seleccionando un fragmento de la fórmula (por ejemplo, B2:B11*C2:C11) en la barra de fórmulas y pulsando F9. Excel mostrará el resultado de esa parte como una matriz de valores. Luego, con Esc, cancelas para no modificar nada.
Fórmulas matriciales con resultado en una sola celda
Muchos de los usos más potentes de las fórmulas matriciales devuelven un único valor pero, por debajo, están manejando rangos completos y realizando multitud de operaciones internas. Vamos a ver varios patrones muy habituales que conviene dominar.
Ejemplo básico: total de ventas sin columnas auxiliares
Imagina una tabla sencilla de ventas: en la columna A el producto, en B las unidades vendidas (B2:B11) y en C el precio unitario (C2:C11). Quieres obtener el total facturado del período.
La forma tradicional sería crear una columna de importes (unidades * precio) y luego sumar esa columna. Con una fórmula matricial, te ahorras toda la columna auxiliar:
=SUMA(B2:B11*C2:C11)
Tras escribirla en una celda, confirma con Ctrl + Mayús + Intro. Excel multiplicará cada par de celdas fila a fila (B2*C2, B3*C3, etc.), almacenará internamente esos subtotales y al final hará la suma de todos ellos, devolviendo el total en una sola celda.
Sumar un rango que contiene errores
Si intentas sumar un rango que incluye errores como #N/A, la función SUMA falla. Con una fórmula matricial puedes “ignorar” esos errores y sumar solo los valores válidos. Supón que tienes un rango llamado Datos que contiene números y algunos errores:
{=SUMA(SI(ESERROR(Datos);»»;Datos))}
La lógica es la siguiente:
- ESERROR(Datos) devuelve una matriz de VERDADERO/FALSO según haya error en cada celda.
- SI(ESERROR(Datos);»»;Datos) genera una nueva matriz donde los errores se sustituyen por «» (cadena vacía) y los valores correctos se mantienen.
- SUMA opera sobre esa matriz filtrada, ignorando las cadenas vacías, y devuelve el total solo de los valores numéricos sanos.
Contar cuántos errores hay en un rango
Siguiendo la misma idea anterior, también puedes contar los errores en un rango llamado Datos:
{=SUMA(SI(ESERROR(Datos);1;0))}
Esta fórmula crea una matriz de 1 y 0, poniendo 1 en las celdas con error y 0 en las que no lo tienen, y luego suma esa matriz. Puedes simplificarla de varias maneras:
{=SUMA(SI(ESERROR(Datos);1))}
En esta versión, cuando ESERROR(Datos) es FALSO, la función SI devuelve FALSO directamente, que SUMA trata como 0. Aún se puede recortar más:
{=SUMA(SI(ESERROR(Datos)*1))}
Aquí se aprovecha que VERDADERO*1=1 y FALSO*1=0. ESERROR(Datos) produce VERDADERO/FALSO, al multiplicar por 1 obtienes 1/0, y SUMA cuenta los 1 (es decir, los errores).
Sumar valores que cumplen una o varias condiciones
Uno de los usos más típicos de las fórmulas matriciales es hacer sumas condicionadas muy flexibles, combinando varias pruebas lógicas sin depender siempre de SUMAR.SI o SUMAR.SI.CONJUNTO.
Por ejemplo, sumar solo los valores positivos de un rango llamado Ventas:
{=SUMA(SI(Ventas>0;Ventas))}
SI(Ventas>0;Ventas) genera una matriz en la que aparecen los valores positivos y FALSO para el resto. SUMA ignora los FALSO y suma solo los números.
Si necesitas aplicar varias condiciones tipo Y, puedes multiplicar condiciones lógicas. Por ejemplo, sumar las ventas mayores que 0 y menores o iguales que 5:
{=SUMA((Ventas>0)*(Ventas<=5)*Ventas)}
Cada expresión lógica devuelve VERDADERO/FALSO; al multiplicarlas y multiplicar después por Ventas, solo las celdas que cumplen ambas condiciones aportan su valor al total (VERDADERO se comporta como 1, FALSO como 0). Ojo: esta fórmula devolverá error si en el rango hay celdas no numéricas mezcladas.
Para condiciones tipo O puedes sumar las comprobaciones lógicas. Por ejemplo, sumar ventas menores que 5 o mayores que 15:
{=SUMA(SI((Ventas<5)+(Ventas>15);Ventas))}
La suma (Ventas<5)+(Ventas>15) devuelve 1 para las celdas que cumplen al menos una de las condiciones y 0 para el resto. SI se queda con los valores de Ventas cuando la suma es 1 y con FALSO en los demás casos, que SUMA ignora.
Es importante entender por qué no se usan directamente las funciones Y y O en fórmulas matriciales: esas funciones devuelven un único VERDADERO o FALSO, mientras que las fórmulas matriciales necesitan una matriz de resultados lógicos (uno por celda). Por eso se recurre a multiplicar o sumar condiciones elemento a elemento.
Calcular una media excluyendo los ceros
Otra necesidad típica es calcular promedios sin que los ceros “contaminen” el resultado. Con una fórmula matricial puedes filtrar esos ceros de un rango llamado Ventas:
{=PROMEDIO(SI(Ventas<>0;Ventas))}
La función SI crea una matriz con los valores distintos de 0 y devuelve FALSO donde hay ceros. PROMEDIO calculará la media solo con los valores numéricos, ignorando los FALSO.
Contar diferencias entre dos rangos
Si quieres saber cuántas celdas difieren entre dos rangos del mismo tamaño, por ejemplo MisDatos y TusDatos, una fórmula matricial lo resuelve de forma muy compacta:
{=SUMA(SI(MisDatos=TusDatos;0;1))}
La fórmula compara posición a posición ambos rangos. Si los valores coinciden, la SI devuelve 0; si no coinciden, devuelve 1. SUMA cuenta las diferencias. Puedes escribir una versión más corta:
{=SUMA(1*(MisDatos<>TusDatos))}
MisDatos<>TusDatos genera VERDADERO donde hay diferencias y FALSO donde todo coincide. Multiplicar por 1 convierte esos valores lógicos en 1 y 0, que SUMA añade para obtener el total de discrepancias.
Buscar la posición del valor máximo de un rango
Las fórmulas matriciales también permiten localizar posiciones dentro de un rango. Por ejemplo, el número de fila donde se encuentra el valor máximo dentro de un rango de una columna llamado Datos:
{=MIN(SI(Datos=MAX(Datos);FILA(Datos);»»))}
Esta fórmula funciona así:
- MAX(Datos) obtiene el valor máximo del rango.
- SI(Datos=MAX(Datos);FILA(Datos);»») genera una matriz con el número de fila para las celdas que contienen ese máximo y «» para el resto.
- MIN aplicado a esa matriz devuelve el número de fila más pequeño, es decir, la primera aparición del valor máximo.
Si en lugar del número de fila quieres la referencia de celda completa, puedes envolverlo en la función DIRECCION:
{=DIRECCION(MIN(SI(Datos=MAX(Datos);FILA(Datos);»»));COLUMNA(Datos))}
Aquí se combina la fila calculada con la columna del rango Datos para devolver una referencia tipo «$C$15».
Fórmulas matriciales con resultado en varias celdas
Además de fórmulas matriciales que devuelven un solo valor, existen fórmulas que, al confirmarlas, rellenan de golpe un rango completo. Son especialmente útiles para crear tablas transformadas, realizar cálculos fila a fila o construir resultados que dependen de más de una dimensión.
Ejemplo de matriz unidimensional: meses del año
Ya vimos que una matriz unidimensional vertical puede contener, por ejemplo, los meses. Para rellenarlos automáticamente en una columna, seleccionarías 12 celdas en una sola columna, escribirías la constante matricial y la confirmarías con Ctrl + Mayús + Intro.
También puedes utilizar fórmulas que calculan rangos completos. Por ejemplo, si quieres obtener el 5 % de impuestos sobre el importe de cada producto (usando ventas y precios del ejemplo anterior), puedes escribir:
=B2:B11*C2:C11*0,05
Selecciona antes un rango de 10 celdas verticales donde quieras que se desplieguen esos importes de impuestos y luego confirma la fórmula como matricial. Cada fila mostrará el impuesto correspondiente a cada producto.
Ejemplo de matriz multidimensional: TRANSPONER una tabla
Excel incluye funciones diseñadas para devolver matrices completas, como TRANSPONER, TENDENCIA, FRECUENCIA o la función MAP. TRANSPONER es de las más populares porque permite cambiar filas por columnas y viceversa.
Supón que tienes una tabla en A1:D11 y quieres volcarla girada, de forma que lo que eran filas pasen a ser columnas. El procedimiento es:
- Seleccionar un rango vacío con el tamaño invertido. Si la tabla original tiene 4 columnas y 11 filas, selecciona un rango de 11 columnas y 4 filas.
- Escribir la fórmula =TRANSPONER(A1:D11) en la barra de fórmulas mientras ese rango está seleccionado.
- Confirmar con Ctrl + Mayús + Intro para que Excel genere la matriz transpuesta en todas las celdas del rango.
Cuando trabajes con fórmulas matriciales que devuelven varias celdas, debes tener en cuenta varias reglas de juego:
- Debes seleccionar primero todo el rango donde se va a colocar la matriz; después escribes la fórmula y la confirmas.
- No puedes editar una sola celda de la matriz. Cualquier modificación afecta a toda la fórmula; si intentas cambiar una celda aislada verás un mensaje de error.
- Para eliminar una matriz, selecciona el rango completo y borra la fórmula desde la barra de fórmulas, confirmando después con Ctrl + Mayús + Intro o simplemente Supr si la vas a quitar.
- Si quieres que la matriz ocupe menos celdas, tendrás que eliminar primero la matriz original y volver a crearla en el nuevo rango.
- Si necesitas que abarque más celdas, selecciona el rango antiguo más las nuevas celdas, edita la fórmula para adaptarla al nuevo tamaño y vuelve a confirmarla como matricial.
Matrices constantes: valores fijos dentro de las fórmulas
Una “matriz constante” es simplemente un conjunto de valores estáticos escribidos dentro de la propia fórmula, encerrados entre llaves y separados por el carácter correspondiente. Son muy útiles para agrupar criterios, pesos o listas fijas que no quieres tener en celdas de la hoja.
En Excel en español, las matrices constantes siguen estas reglas generales:
- Matriz horizontal unidimensional: valores en una sola fila, separados por contrabarra (\) y encerrados entre llaves. Por ejemplo: {1\2\3\4}.
- Matriz vertical unidimensional: valores en una sola columna, separados por punto y coma (;) dentro de las llaves. Por ejemplo: {«Enero»;»Febrero»;»Marzo»}.
- Matriz bidimensional: combinación de ambas; se separan columnas con contrabarra y filas con punto y coma. Por ejemplo: {1\2\3;4\5\6} representa dos filas y tres columnas.
Los elementos de una constante de matriz pueden ser números, textos, valores lógicos y errores.
- Números: enteros, decimales o notación científica.
- Texto: siempre entre comillas dobles.
- Booleanos: VERDADERO y FALSO (sin comillas).
- Errores: #N/A, #¡DIV/0!, etc.
Lo que no puede contener una constante de matriz son referencias de celdas, otros rangos, funciones o nombres definidos. Solo valores “puros”.
Asignar nombres a matrices constantes
Para reutilizar una constante matricial de forma cómoda, puedes asignarle un nombre y así no tendrás que escribirla entera cada vez:
- Ve a la pestaña Fórmulas > Nombres definidos > Asignar nombre.
- Escribe un nombre descriptivo (por ejemplo, Meses).
- En el cuadro “Hace referencia a” introduce la constante matricial. Por ejemplo: ={«Enero»\»Febrero»\»Marzo»\»Abril»\»Mayo»\»Junio»\»Julio»\»Agosto»\»Septiembre»\»Octubre»\»Noviembre»\»Diciembre»}.
- Pulsa Aceptar para guardar el nombre.
Para usar esa matriz en la hoja, selecciona tantas celdas como elementos tenga, escribe =Meses y confirma con Ctrl + Mayús + Intro. Excel repartirá cada mes en su celda correspondiente.
Si una constante matricial “no tira” y te devuelve error, hay varios puntos que deberías revisar:
- Comprueba que has usado los separadores correctos: contrabarra para separar columnas en horizontal y punto y coma para separar filas en vertical.
- Revisa que el rango donde la quieres volcar tiene el tamaño y orientación adecuados.
- Asegúrate de que los textos van entre comillas y que no has mezclado referencias de celdas, que en constantes no están permitidas.
Ejemplo: sumar los N valores más altos de un rango
Un uso muy interesante de las constantes matriciales es combinarlas con funciones como K.ESIMO.MAYOR o K.ESIMO.MENOR. Imagina que quieres sumar los tres valores más altos de un rango de datos, digamos A1:A100.
La lógica sería llamar a K.ESIMO.MAYOR tres veces (para 1, 2 y 3) y sumar el resultado, pero con una constante matricial lo haces de una sola tacada:
{=SUMA(K.ESIMO.MAYOR(A1:A100;{1\2\3}))}
La constante {1\2\3} indica a K.ESIMO.MAYOR que debe devolver tres valores: el mayor, el segundo mayor y el tercero. SUMA agrega esos tres resultados. Cambiando a K.ESIMO.MENOR conseguirías la suma de los tres menores.
Ejemplo: contar celdas que cumplen muchos criterios
Cuando tienes que contar con varias condiciones, CONTAR.SI.CONJUNTO es una buena opción, pero cuando los criterios son muchos la fórmula se vuelve larga y poco legible porque repites el rango de criterios una y otra vez.
Imagina una lista de productos donde Pedro los ha valorado del 1 al 5 en la columna C (C2:C28), y en la columna A (A2:A28) está el nombre del evaluador. Queremos saber cuántas veces Pedro ha dado 3, 4 o 5 puntos.
En lugar de tres CONTAR.SI.CONJUNTO distintos, puedes usar una constante matricial:
=SUMA(CONTAR.SI.CONJUNTO(A2:A28;»Pedro»;C2:C28;{3\4\5}))
La constante {3\4\5} hace que CONTAR.SI.CONJUNTO genere internamente tres resultados (para cada puntuación) y SUMA los agrupa. Así consigues una fórmula compacta, fácil de leer y más eficiente cuando manejas muchos criterios.
Como ves, las fórmulas matriciales convierten a Excel en una especie de mini motor de cálculo vectorial, capaz de trabajar con grandes rangos, múltiples condiciones y operaciones encadenadas sin necesidad de llenar la hoja de columnas de apoyo. Dominar la introducción con Ctrl + Mayús + Intro, entender cómo se comportan las matrices lógicas (VERDADERO/FALSO) y practicar con ejemplos como los de sumar con condiciones, gestionar errores, promediar sin ceros o trabajar con constantes matriciales te da un nivel de control muy por encima del usuario medio y te permite construir hojas más limpias, rápidas y fáciles de mantener.
