Fórmula de matriz de Excel: ¿qué es?

Las fórmulas de matriz en Excel son una herramienta muy poderosa. Sin embargo, lamentablemente también es muy difícil. Mostramos cómo funciona una fórmula de matriz de Excel y qué debe tener en cuenta al hacerlo.

Una fórmula de matriz de Excel (también conocida como fórmula de matriz) puede realizar varios cálculos a la vez y, en casos extremos, reemplazar miles de fórmulas habituales. Sin embargo, se estima que un buen 90 por ciento de los usuarios nunca han usado funciones de matriz en sus hojas de trabajo.

¿Qué es una matriz en Excel?

Básicamente, una matriz es solo una colección de elementos. La traducción al alemán "campo" también es algo precisa, pero la traducción " serie " es mejor : una matriz es comparable a una serie simple de datos.

Los datos pueden ser texto o números, con Excel normalmente son números.

Excel ( como muchos lenguajes de programación ) escribe arreglos con una coma como separador y entre paréntesis.

Entonces, por ejemplo, si coloca la lista de compras en un formato de matriz de Excel, podría verse así: {“leche”, “huevos”, “mantequilla”, “copos de maíz”}

¿Qué es una fórmula matricial en Excel?

La diferencia entre una fórmula de matriz y una fórmula regular es que una fórmula de matriz maneja múltiples valores en lugar de un solo valor. Por ejemplo, en lugar de calcular la celda A1 contra B1, una fórmula de matriz puede calcular el rango completo de, por ejemplo, A1:A100 contra B1:B100.

Las fórmulas de matriz clásicas (hasta Excel 2021) siempre devuelven exactamente un resultado, en la celda en la que se ingresó. Excel ahora también puede devolver dinámicamente rangos de celdas completos como resultado, más sobre eso más adelante.

Importante :
para crear una fórmula de matriz, siempre debe terminar la entrada de la fórmula con Ctrl++ . ¡ La tecla Enter por sí sola no es suficiente!SHIFTENTER

Ejemplo

Suponga que tiene algunos productos en la columna B y sus precios en la columna C. Ahora desea calcular el total de todas las ventas.

Por supuesto, nada le impide calcular primero los subtotales en cada fila con algo como =B2*C2 y luego sumar estos valores.

En este caso, sin embargo, una fórmula de matriz de Excel permite escribir el resultado de forma compacta en una sola celda. Entonces, en lugar de tener que insertar una nueva columna D con los resultados, el resultado final se puede generar directamente. Esto ahorra espacio y le brinda una visión general, especialmente en tablas complejas.

Seleccione una celda vacía e ingrese la siguiente fórmula allí:

=SUMME(B2:B6*C2:C6)

Presione la combinación de teclas Ctrl++ para completar la fórmula de matriz. Una vez que haya hecho esto, Microsoft Excel rodea la fórmula con llaves . Así es como Excel indica que es una fórmula matricial.SHIFTENTER{ }

La fórmula multiplica los valores en cada fila individual de la matriz (celdas B2 a C6), suma los subtotales y devuelve el total general:

Este sencillo ejemplo muestra lo poderosa que puede ser una fórmula matricial. Solo uno puede reemplazar literalmente cientos de fórmulas normales.

Nuevas fórmulas de matriz desde Excel 2021

Una fórmula de matriz de Excel no solo puede funcionar con múltiples valores a la vez, sino que desde hace algún tiempo también puede devolver múltiples valores a la vez . El resultado devuelto por una fórmula de matriz es, por lo tanto, también una matriz (o en inglés: una fila o un "campo"). Esto es nuevo y no era posible antes.

Microsoft mismo llama a este tipo de función fórmula de matriz dinámica . Un buen ejemplo de tal función es la función = ÚNICO (Matriz). Esto filtra todos los valores que ocurren en un rango y los enumera exactamente una vez en el rango objetivo. Por ejemplo, puede ver rápidamente qué clientes estuvieron activos en un período de tiempo, incluso si realizaron varios pedidos.

Lógicamente, la matriz de resultados puede tener diferentes tamaños, dependiendo de cuántas entradas únicas haya en ella. Si la matriz de resultados no cabe en la hoja de Excel, Excel informa el error #OVERFLOW.

Sugerencias y sugerencias sobre fórmulas de matriz

1. Una vez que haya terminado de escribir la fórmula y presionado las teclas Ctrl++ al mismo tiempo, Excel la encerrará SHIFTautomáticamente entre {corchetes}. ENTERCuando seleccione una de esas celdas, verá las llaves en la barra de fórmulas, lo que indica que hay una fórmula de matriz allí.
2. ¡ Escribir manualmente las llaves alrededor de una fórmula no funciona! Debe presionar Ctrl + Shift + Enter para completar una fórmula de matriz.
3. Cada vez que edita una fórmula de matriz, los corchetes desaparecen y debe presionar Ctrl + Shift + Enter nuevamente para guardar los cambios.
4. Si olvida presionar ++, Excel informará un error o la fórmula se comportará como una fórmula ordinaria, usando solo los primeros valores en la matriz especificada Ctrl.UmschaltEingabe