Las fórmulas matriciales en Excel

¿Alguna vez has intentado sumar los caracteres de un rango de celdas con una sola fórmula? ¿O has sumado los valores que se encuentran dentro de unos límites? Supongo que, si te ha sido imposible, serás de ese 90% de usuarios que no conoce las fórmulas matriciales y que tiene miedo a enfrentarse a ellas porque “no las entiende”.

En el artículo de hoy te mostraré por qué las fórmulas matriciales son una de las herramientas más útiles que tiene Excel (aunque son algo complicadas de aprender), cómo una fórmula matricial puede hacer múltiples cálculos que pueden reemplazar a miles de fórmulas “normales” y también hablaré de algunos ejemplos que podrás utilizar en tu trabajo diario.

Qué son las fórmulas matriciales

Antes de meterme de lleno a tratar las fórmulas matriciales, conviene que sepas qué es exactamente una matriz. Básicamente una matriz es una colección de elementos. Si eres programador, no te será difícil comprenderlo, ya que forman parte de la programación cotidiana.

Una matriz en Excel es una colección de elementos o valores. Estos elementos pueden ser texto o números y pueden ubicarse en una fila, una columna o varias filas y columnas.

Por ejemplo, una matriz muy común suele representar los meses del año.

{“Enero”;”Febrero”;”Marzo”;”Abril”;”Mayo”;”Junio”;”Julio”;”Agosto”;”Septiembre”;”Octubre”;”Noviembre”;”Diciembre”}

Si quisieras mostrarlos en una columna, tendrías que seleccionar 12 celdas de una columna e introducir la información anterior, poniendo un signo igual delante y pulsando Ctrl + Mayús + Intro para finalizar:

meses año formulas matriciales excel

En esta animación he creado una matriz vertical unidimensional. De momento no es complicado, ¿verdad?

tipos fórmulas matriciales excel

Además de los tipos de matrices que ves en la imagen, Excel también puede utilizar “multidimensionales”, que abarcan varias hojas de un libro, pero que rara vez se utilizan.

Pero, ¿qué es una fórmula matricial?

La principal característica, a diferencia de las fórmulas de hoja normales es que es capaz de procesar varios valores en lugar de sólo uno. Dicho de otra forma, en una fórmula matricial Excel evalúa todos los valores individuales, y realiza varios cálculos en uno o varios elementos de acuerdo con las condiciones reflejadas en la fórmula.

Y no solamente puede trabajar con varios valores a la vez, sino que además, puede devolver varios valores. Por lo tanto, el resultado de una fórmula matricial puede ser a su vez, una matriz.

Sé que es una definición algo complicada de entender, así que te mostraré un ejemplo simple:

Imagina que tienes una lista de los productos vendidos en el mes anterior. En la columna A aparece el nombre del producto, en la B el número de unidades vendidas y en la C el precio de cada unidad. Lo que quieres calcular es el importe total de las ventas del mes anterior.

La forma “fácil” sería la de calcular el resultado de cada producto en una columna diferente a modo de subtotales y más abajo, sumar esos subtotales.

formulas matriciales importe excel

Este cálculo es correcto, sin embargo, una fórmula matricial puede ahorrarte estos cálculos auxiliares ya que hace que Excel almacene internamente resultados intermedios en la memoria en lugar tener que hacerlo en una columna auxiliar.

Para calcular el importe de venta del mes anterior con una fórmula matricial, selecciona una celda vacía y escribe lo siguiente:

=SUMA(B2:B11*C2:C11)

A continuación pulsa Ctrl + Mayús + Intro para introducir la fórmula anterior. Observa que se crean unas llaves de apertura y cierre. Es la señal de que se trata de una fórmula matricial.

Lo que hace la fórmula anterior es multiplicar los valores de cada fila individual de la matriz para luego sumar sus resultados y generar un total:

uso fórmulas matriciales excel

Esto es un ejemplo muy simple que demuestra lo poderosas y útiles que son las fórmulas matriciales. Aquí he utilizado muy pocos datos, pero cuando se utilizan cientos de miles de filas, te puedo asegurar que la velocidad de cálculo no es la misma….

Personalmente yo suelo utilizar este tipo de fórmulas para hacer cálculos complejos. Como has visto, una sola fórmula matricial puede reemplazar miles de cálculos intermedios. Otros usos que suelo darlas:

  • Cuando quiero sumar números que cumplen ciertas condiciones (por ejemplo, sumar las 10 temperaturas más altas del año).
  • Cuando quiero sumar los valores que se encuentran en las filas pares o cada tres filas.
  • Cuando quiero hallar el promedio de los valores máximo y mínimo de un rango.

Cómo introducir una fórmula matricial

Como has visto más arriba, debes utilizar la combinación de teclas Ctrl + Mayús + Intro para introducir una fórmula de forma matricial.

Al introducir una fórmula matricial hay cuatro cosas que debes tener en cuenta:

  1. Al pulsar Ctrl + Mayús + Intro para introducir la fórmula, Excel automáticamente la encierra entre llaves. Estas llaves se pueden ver en la barra de fórmulas cuando seleccionas la celda. Es una pista para saber que se trata de una fórmula matricial.
  2. Si introduces a mano las llaves delante y detrás de la fórmula, no la convertirá en matricial. Sólo se puede hacer pulsando Ctrl + Mayús + Intro.
  3. Cuando editas una fórmula matricial, las llaves desaparecen y para introducirla de nuevo debes pulsar otra vez Ctrl + Mayús + Intro.
  4. Si se te olvida pulsar Ctrl + Mayús + Intro, Excel la tratará como una fórmula normal, es decir, sólo realizará cálculos con los primeros valores de las matrices introducidas.

Cómo calcular algunas partes de una fórmula matricial

Cuando trabajas con fórmulas que utilizan matrices para el cálculo, es fácil observar cómo se calculan y almacenan sus elementos para mostrar el resultado final (lo que se ve en la celda). Para hacer esto selecciona uno o varios argumentos dentro de los paréntesis y a continuación, pulsa la tecla F9. Para salir del modo de evaluación pulsa la tecla Esc.

En el ejemplo anterior, si quieres ver los subtotales de cada fila, selecciona B2:B11*C2:C11 y pulsa F9.

cálculo fórmula matricial excel

Tipos de fórmulas matriciales

Una fórmula matricial puede devolver su resultado en una celda o en varias celdas.

  • El ejemplo anterior de los meses del año, devuelve el resultado en varias celdas.
  • El ejemplo de las ventas totales devuelve el resultado en una celda.

Excel tiene algunas funciones de hoja que son capaces de devolver matrices de varias celdas. La más utilizada puede ser TRANSPONER, pero existen otras como TENDENCIA o FRECUENCIA.

Otras funciones como SUMA, PROMEDIO, MAX o MIN, pueden calcular matrices cuando se introducen en una sola celda y se pulsa Ctrl + Mayús + Intro.

Aquí tienes algunos ejemplos para que entiendas a qué me refiero:

Ejemplo 1: resultado en una sola celda

Tengo los importes de venta de los últimos dos meses y quiero averiguar cuál ha sido el producto que ha registrado una mayor diferencia de venta.

Para un cálculo “normal” tendría que agregar una columna auxiliar para calcular los subtotales para, más abajo, hacer el cálculo final. Pero como te comenté anteriormente, una fórmula matricial no necesita de columnas auxiliares para hallar el resultado, así que la fórmula que tendría que introducir para hallar lo que busco sería esta:

cálculos con fórmulas matriciales excel

Ejemplo 2: resultado en varias celdas (unidimensional)

¿Te acuerdas del primer ejemplo que te puse donde quería calcular las ventas totales del mes anterior? Ahora quiero hallar los impuestos a pagar por cada producto, teniendo en cuenta que se trata del 5% sobre el importe. Es decir, debo calcular el importe de cada producto y aplicarle el 5%.

La fórmula a utilizar sería esta:

=B2:B11*C2:C11*0,05

Cálculo impuestos fórmula matricial excel

Recuerda que antes de introducir la fórmula, debes seleccionar las celdas donde quieras introducirla. Y no olvides Ctrl + Mayús + Intro.

Ejemplo 3: resultado en varias celdas (multidimensional)

Como comenté más arriba, Excel cuenta con alguna de las llamadas funciones matriciales que están especialmente diseñadas para trabajar con matrices de varias celdas. TRANSPONER es la función más conocida de este tipo y te voy a mostrar cómo utilizarla para transponer la tabla anterior, es decir, convertir las filas en columnas y las columnas en filas.

  1. Selecciona un rango vacío de celdas donde quieras generar la tabla transpuesta. Como se trata de convertir las filas en columnas, asegúrate la misma cantidad de filas y columnas, que columnas y filas (respectivamente) contenga la tabla original.

En el ejemplo, la tabla tiene 4 columnas y 11 filas, por lo que habrá que seleccionar 4 filas y 11 columnas.

  1. Pulsa en la barra de fórmulas e introduce la fórmula =TRANSPONER(A1:D11)
  2. Pulsa Ctrl + Mayús + Intro para introducir la fórmula.

El resultado debería ser parecido a este:

transponer fórmula matricial excel

Algunas cosas que debes tener en cuenta cuando trabajes con fórmulas matriciales que devuelven una matriz de celdas son:

  1. Antes de introducir la fórmula debes seleccionar el rango donde se va a calcular.
  2. Para eliminar una matriz, debes seleccionar todas las celdas de la matriz. Si no lo haces, Excel no te permitirá borrarla. También puedes borrar la fórmula desde la barra de fórmulas y pulsar Ctrl + Mayús + Intro.
  3. No se puede editar o mover el contenido de una celda individual de una matriz. Tampoco se pueden insertar celdas nuevas. Cada vez que intentes hacerlo, recibirás un mensaje de advertencia.
  4. Para aplicar la fórmula matricial a menos celdas de las actuales, primero debes eliminar la matriz original y luego tienes que crearla nuevamente.
  5. Para aplicar la fórmula matricial a más celdas de la original, selecciona todas las celdas de la matriz más las que quieres agregar y edita la fórmula para que se ajuste al nuevo rango. No te olvides de pulsar Ctrl + Mayús + Intro.

Matrices constantes

En Excel, una matriz constante es simplemente un conjunto de valores estáticos, es decir, que nunca cambian cuando se copian fórmulas a otras celdas o valores.

Al principio te mostré un ejemplo que contenía los meses del año. Era una matriz vertical.

Ahora te mostraré los tipos de matriz que existen y cómo crearlos.

Unidimensionales horizontales

Este tipo de matriz se ubica en una sola fila. Para crear una matriz unidimensional horizontal escribe los valores separados por contrabarras (o barras invertidas) y encerrado todo ello entre llaves. De esta forma:

formula matricial horizontal excel

Atención: Si usas el idioma inglés, puede que en vez de contrabarras, debas separar los valores por comas.

Unidimensionales verticales

Este tipo de matriz se encuentra en una sola columna. En este caso, el separador de valores es el punto y coma (tanto para la versión inglesa como para la española). Mira el ejemplo:

formula matricial vertical excel

Observa que, para introducir valores de texto, éstos deben ir encerrados entre comillas.

Bidimensional

Las constantes bidimensionales son una mezcla de las unidimensionales verticales y horizontales y, por lo tanto, debes mezclar puntos y comas y contrabarras. Observa la imagen:

formula matricial bidimensional excel

Cómo utilizar una constante matricial en una fórmula

Las constantes de matriz son una de las claves que tiene Excel. Seguro que los siguientes consejos te serán de utilidad a la hora de trabajar con ellas:

  1. Tipos de elementos de una matriz: Una constante de matriz en Excel puede tener valores de texto, booleanos (VERDADERO y FALSO), y valores de error.

Puedes introducir un valor numérico, ya sea entero, decimal o en notación científica. Si utilizas valores de texto, recuerda que deben ir siempre entre comillas.

Una constante de matriz no puede contener otras matrices, referencias de celda, rangos, fechas, nombres definidos, fórmulas o funciones.

  1. Puedes asignar nombres a las constantes de matriz: Para hacer que una constante sea más fácil de utilizar, asígnale un nombre :
    • Haz clic en Fórmulas > Nombres definidos > Asignar nombre.
    • Escribe el nombre que quieres ponerle a la matriz.
  • En el cuadro Hace referencia a, introduce los elementos de la constante de la misma forma que lo has hecho en los ejemplos anteriores. Por ejemplo:

={“Enero”\”Febrero”\”Marzo”\”Abril”\”Mayo”\”Junio”\”Julio”\”Agosto”\”Septiembre”\”Octubre”\”Noviembre”\”Diciembre”}

cuadro fórmula matricial excel

  • Haz clic en Aceptar para guardar la matriz con su nombre y cerrar la ventana.
  • Para introducir esta matriz en una hoja de Excel, selecciona tantas celdas como elementos contiene la matriz, escribe el nombre de la matriz y, a continuación, pulsa Ctrl + Mayús + Intro.

fórmulas matriciales nombre excel

  1. Si la constante no funciona correctamente, presta atención a los siguientes puntos:
    1. Comprueba que los caracteres delimitadores son los correctos: Contrabarra (\) para el horizontal y punto y coma (;) para el vertical.
    2. Comprueba si antes de escribir la fórmula seleccionaste las celdas en número y posición adecuada.

Mira estos ejemplos de uso de constantes matriciales para que lo entiendas mejor:

Ejemplo 1: Sumar los tres valores más altos del rango

Imagina que tienes un rango de valores numéricos y quieres hallar la suma de los tres valores más altos.

k.esimo.mayor formula matricial excel

De manera parecida, puedes calcular la suma de los N valores más pequeños del rango. Sólo tienes que cambiar la función K.ESIMO.MAYOR por K.ESIMO.MENOR.

Ejemplo 2: contar celdas que tienen muchas condiciones

Imagina que tengo una lista de productos, los cuales han valorado tres personas diferentes con puntuaciones del uno al cinco. En este caso quiero saber cuántas veces Pedro ha valorado los productos con 3, 4 ó 5 puntos.

La forma fácil sería utilizar directamente la función CONTAR.SI.CONJUNTO, ya que permite establecer varias condiciones. Esto está bien cuando vas a evaluar hasta 3 ó 4 condiciones. Sin embargo, cuando necesitas utilizar muchas condiciones, la fórmula se puede volver bastante lenta e ilegible porque cada vez que quieres introducir un criterio, necesitas especificar el rango que lo contiene.

Para hacer la fórmula más compacta, lo mejor es utilizar una fórmula matricial:

=SUMA(CONTAR.SI.CONJUNTO(A2:A28;”Pedro”;C2:C28;{3\4\5}))

matriciales excel formulas

Esta matriz contiene sólo tres elementos, pero la fórmula está hecha a modo de ejemplo. Puedes utilizar tantos elementos como lo requiera el cálculo, siempre que la longitud de la fórmula no supere los 8.192 caracteres.

Resumen

Como has podido comprobar, Excel cuenta con una “misteriosa” forma de utilizar las fórmulas, que permite realizar cálculos más potentes y complejos.

Aprender a utilizar eficientemente las fórmulas matriciales te hará que pases al siguiente nivel en cuanto al uso de Excel y destaques por encima de otros compañeros.

Respuestas

  1. Muchas gracias por tu artículo me parece muy interesante.
    Pero tengo un problema cuando realizo fórmulas matriciales ya que cuando tengo que modificar la base de donde recoge los datos Excel comienza a realizar cálculos de nuevo pero no avanza y finalmente se queda bloqueado.
    ¿Cómo puedo solucionarlo?
    Gracias por su respuesta.
    Un saludo.

    1. si la formula matricial hace referencia a muchas celdas, es normal que tarde en calcular todo. En este caso te recomiendo que habilites el cálculo manual para que solo se calcule la fórmula cuando tú quieras.

  2. Buenos dias, gracias por tu artículo me sirvió bastante… tengo una pregunta oja puedas ayudarme…..
    Como hago para copiar y pegar una formula matricial a otra celda o grupo de celdas (uso referencias relativas y absolutas en la formula, por lo que obviamente se toman valores según corresponda), ya que cuando presiones “Ctrl+C” en una celda con formula matricial y luego “Ctrl V” en la o las celda o celdas de destino, la formula matricial no funciona. . gracias.

    1. Wilmer, después de pegarla con Ctrl + V, vuelve a editarla con F2 y a continuación pulsa Ctrl + Mayús + Intro para introducirla como matricial.

  3. Buenos días,

    tengo una duda con una fórmula que quiero incluir en un excel. La situación es la siguiente:

    Tengo un listado de escenarios de riesgo, y los he valorado según el Grado de impacto y el Nivel de probabilidad.

    Una vez que tengo estos valores, necesito llevarlos a un matriz individual que me analice el riesgo. Me explico, es una tabla en la que tengo en columna la Probabilidad (por grados) y en fila el impacto (por niveles) y necesito que cada uno de los escenarios de riesgo automáticamente cuando los valoremos, y nos den un nivel X y un grado Y se coloque en esta matriz. Pero no encuentro la fórmula correcta para hacerlo…

    No sé si podría ayudarme.

    Muchas gracias de antemano.

    Un saludo

    1. Hola, Andrea!

      El escenario que planteas es perfecto para utilizar Power Query. Busca información sobre la herramienta “Anular dinamización de columnas”.

  4. Hola, al advertirnos sobre posibles fallos en constantes matriciales, escribes:

    “Comprueba que los caracteres delimitadores son los correctos: Contrabarra (\) para el horizontal y dos puntos (:) para el vertical.”

    No era mas bien punto y coma para los verticales?

    1. Ricardo, tienes toda la razón del mundo… Ya está corregido.
      Muchas gracias!

  5. Que tal Sergio, he intentado colocar una formula matricial sin embargo a la hora de pulsar la combinación de teclas, no pasa nada, de intentado en varios libros y con varias formulas sin embargo sigue sin pasar nada.

    ¿Que configuración puedo tener mal?

    1. Hola Isaí! ¿Podrías darme más datos de la fórmula? Por ejemplo, qué fórmula es, qué necesitas que haga, qué datos tienes en las celdas a las que hace referencia, etc…

    2. Buenas tardes

      Me paso lo mismo la primer vez, en mi caso mi teclado es en ingles asi que use Ctrl+Shift+Enter, listo

  6. Hola Sergio muy descriptivo el post y de mucha ayuda.
    Me ayudarias con una pregunta cuando hago una funcion matricial con un SI(Y no me arroja el resultado correcto, esta es mi funcion:
    ={SI(Y(initiative!BM1:BM300=”L1″,initiative!F1:F300=”05. Educational productivity”),”OKKKKK”,”NO”)}
    en todos los resultados me arroja NO y si tengo valores que cumplen ambas condiciones

    Saludos

    1. Jorge, ¿realmente necesitas que la fórmula sea matricial? ¿qué deseas conseguir con ella?

      1. Muchas gracias por responder, si realmente estoy haciendo un dashboard y busco no agregar columnas o modificar las bases por que esta en constante actualizacion.
        Cambie la funcion de esta forma y me funciono bien, pero ahora cuando uso el indicador * en vez de A2 para que me traiga toda la informacion no me trae datos
        =K.ESIMO.MAYOR(SI((initiative!$BM$1:$BM$300=”L3″)*(initiative!$F$1:$F$300=Analisys!$A$2),initiative!$DD$1:$DD$300,0),A4)

        1. Jorge, se me ocurre que lo hagas con una fórmula como esta: =SI(Y(CONTAR.SI(BM1:BM300;”L3″)=CONTARA(BM1:BM300);CONTAR.SI(BM1:BM300;”05. Educational productivity”)=CONTARA(BM1:BM300));”OK”;”NO”)
          Dime si te sirve.

  7. Hola
    Necesito modificar/ampliar la matriz, cuando trato eliminar la formula donde esta la matriz me manda error de -“No puede cambiar una parte de una matriz”- como la elimino/modifico ?
    Saludos

    1. Fausto, tendrás que crear de nuevo la fórmula en la que incluyas los rangos actualizados.

  8. Hola, muy interesante el articulo. Si me puedes ayudar con lo siguiente sería fenomenal. Necesito sumar los últimos 5 valores de una columna (entendiendo cada día se ingresa una nueva celda con valor). Muy agradecido por tu tiempo

    1. Hola Felipe!
      Para tu consulta no necesitas una fórmula matricial. Suponiendo que la columna a sumar sea la A, yo introduciría la siguiente fórmula:

      =SUMA(DESREF(A1;CONTAR(A1:A160)-5;0;5;1))

      Espero que te sirva.

  9. Estimados, buenas tardes, quiero utilizar esta formula matricial en un excel con tablas. +MAX(IF(Eventos[Categoría]=”LTI (Accidente con Baja)”;Eventos[Fecha del accidente])). Tiene todo para funcionar. Pero no funciona no me trae la fecha.
    lo que quiero saber es si hay que mejorar algo o si hay alguna formula que lo reemplace.
    Gracias
    Slds Luciano

    1. Hola Luciano!
      Necesitaría ver el archivo para poder decirte el motivo de que no te funcione. Pero viendo tu fórmula veo que utilizas la función IF (que está en inglés). Trata de traducirla al español y vuelve a probar.

  10. Buenas!
    Muy buen artículo. Me encanta jugar con las fórmulas matriciales, sin embargo, para rangos muy grandes me han resultado muy mal.
    Estoy tratando de contar valores únicos por mes y por otras variables y me da números decimales en lugar de enteros… Donde debería darme 1 me da 0.34, por ejemplo. En algunas líneas lo cuenta bien, en otras mal.
    Sólo achicando los rangos da buenos resultados.

  11. ¿Puedo obtener la fila que cumple las condiciones de esta formula?
    =CONTAR.SI.CONJUNTO(R:R;”=”&B2;T:T;”=”&C2)

    1. Esa función únicamente sirve para contar las celdas que cumplen uno o varios criterios. No te dice dónde se encuentran esas condiciones. Para saber qué filas coinciden con los criterios que quieras, mejor utiliza formato condicional, resaltando en algún color las celdas adecuadas.

  12. Hola. Muchas gracias por tu post, me pareció bastante claro. Solamente tengo una pregunta. Y es que, no entiendo porqué en los ejemplos finales se coloca los criterios con “\”, teniendo en cuenta que estamos haciendo uso de datos organizados de manera vertical, no horizontal….como en el caso del listado de elementos al buscar la suma de los 3 mayores, o el caso de Pedro, de hallar cuantas veces calificó a los productos con las valoraciones de 3,4 y 5… porque no va ” ; ” ? Gracias de antemano.

    1. ¡Hola Arturo!
      El que no te funcione el punto y coma para separar los valores tiene que ver con la configuración regional de tu ordenador. Seguramente funcionará si los sustituyes por comas.

Los comentarios están cerrados.