Cómo usar una fórmula de matriz en Excel

Las fórmulas de matriz son una parte crucial del conjunto de herramientas que hace que Excel sea versátil. Sin embargo, estas expresiones pueden resultar abrumadoras para los principiantes.

Si bien pueden parecer complicados a primera vista, sus conceptos básicos son fáciles de comprender. Aprender matrices es útil ya que estas fórmulas simplifican la gestión de datos complejos de Excel.

¿Qué es una fórmula de matriz en Excel?

Una matriz es cualquier colección de celdas en una columna, fila o combinación reunidas en un grupo.

En Microsoft Excel, el término fórmula de matriz se refiere a una familia de fórmulas que realizan una o más operaciones en un rango de celdas de este tipo, todas a la vez, en lugar de una celda a la vez.

Las versiones anteriores de Excel requerían que los usuarios presionaran Ctrl + Shift + Enter para crear una función de matriz, lo que resultó en el nombre de funciones CSE (Ctrl, Shift, Escape), aunque este ya no es el caso de Excel 365.

Un ejemplo podría ser una función para obtener la suma de todas las ventas superiores a $100 en un día determinado. O para determinar la longitud (en dígitos) de cinco números diferentes almacenados en cinco celdas.

Las fórmulas de matriz también pueden ser la manera perfecta de extraer datos de muestra de grandes conjuntos de datos. Pueden usar argumentos que incluyen un rango de celdas de una sola columna, un rango de celdas de una sola fila o celdas que abarcan varias filas y columnas.

Además, las declaraciones condicionales se pueden aplicar al extraer datos en una fórmula de matriz, como solo extraer números por encima o por debajo de un valor específico o extraer cada valor enésimo.

Este control granular le permite asegurarse de que está extrayendo un subconjunto exacto de sus datos, filtrando valores de celda no deseados o tomando una muestra aleatoria de elementos con fines de prueba.

Cómo definir y crear una matriz en Excel

Las fórmulas de matriz son, en su nivel más básico, fáciles de crear. Para un ejemplo simple, podemos considerar una factura para un pedido de piezas de un cliente con la siguiente información de partida: el producto WeU (Unidad de mantenimiento de existencias), la cantidad comprada, el peso acumulado de los productos comprados y el precio por unidad comprada.

Para completar la factura, se deben agregar tres columnas al final de la tabla inferior, una para el subtotal de las partes, otra para el costo de envío y la tercera para el precio total de cada partida.

Puede usar una fórmula simple para calcular cada elemento de línea de forma independiente. Pero si la factura era para muchos elementos de línea, rápidamente podría volverse mucho más complicado. Entonces, en cambio, el «Subtotal» se puede calcular con una sola fórmula colocada en la celda E4:

=B4:B8 * D4:D8

Ambos números que estamos multiplicando son rangos de celdas en lugar de celdas individuales. En cada fila, la fórmula extrae los valores de celda apropiados de las matrices y coloca el resultado en la celda correcta sin más entrada del usuario.

Si asumimos una tarifa fija de envío de $1.50 por libra, podemos calcular nuestra columna de envío con una fórmula similar ubicada en la celda F4:

=C4:C8 * B11

Esta vez solo usamos una matriz en el lado izquierdo del operador de multiplicación. Los resultados aún se ingresan automáticamente, pero cada uno se multiplica por un valor estático esta vez.

Finalmente, podemos usar el mismo tipo de fórmula que usamos para el Subtotal para obtener un total al agregar matrices de Subtotales y Costos de envío en la celda G4:

=E4:E8 + F4:F8

Una vez más, una simple fórmula de matriz convirtió lo que de otro modo serían cinco fórmulas en una sola.

En el futuro, agregar cálculos de impuestos a esta hoja de cálculo requeriría cambiar solo una fórmula en lugar de cambiar cada elemento de línea.

Gestión de condicionales en matrices de Excel

El ejemplo anterior usa fórmulas aritméticas básicas en matrices para producir los resultados, pero las matemáticas simples ya no funcionarán para situaciones más complejas.

Por ejemplo, suponga que la empresa que crea la factura en el ejemplo anterior cambió a un transportista diferente. En ese caso, el envío estándar podría bajar de precio, pero se podría aplicar una tarifa para los objetos que superen un cierto peso.

Para las nuevas tarifas de envío, el envío estándar será de $1,00 por libra y el envío de mayor peso será de $1,75 por libra . El envío de alto peso se aplica a cualquier cosa de más de siete libras .

En lugar de volver a calcular cada tarifa de envío línea por línea, una fórmula de matriz puede incluir una declaración condicional IF para determinar el costo de envío apropiado para cada línea:

=IF(C4:C8 < 7, C4:C8*B11, C4:C8*B12)

Con un cambio rápido a una función en una celda y la adición de una nueva tarifa de envío en nuestra tabla de tarifas, ahora podemos ver que no solo toda la columna de envío se calcula en función del peso, sino que los totales también recogen automáticamente la nueva precios.

Por lo tanto, la factura está preparada para el futuro, lo que garantiza que cualquier cambio futuro en el cálculo del costo requerirá cambiar solo una función.

Combinar múltiples o diferentes condicionales puede permitirnos realizar varias acciones. Esta lógica se puede usar de varias maneras en la misma matriz de datos. Por ejemplo,

• Se puede agregar una función IF adicional para ignorar el envío si el subtotal supera cierta cantidad.
• Se podría agregar una tabla de impuestos para gravar diferentes categorías de productos a diferentes tasas.

No importa cuán compleja sea la factura, bastará con editar una sola celda para recalcular cualquier parte de la factura.

Ejemplo: promediar números enésimos

Además de simplificar el cálculo y la actualización de grandes franjas de datos, las fórmulas de matriz pueden extraer una porción de un conjunto de datos con fines de prueba.

Como muestra el siguiente ejemplo, extraer una porción de datos de un gran conjunto de datos para propósitos de validación es fácil usando fórmulas de matriz:

En el ejemplo anterior, la celda D20 usa una función simple para promediar cada n resultado del sensor 1. En este caso, podemos definir n usando el valor en la celda D19, lo que nos da control sobre el tamaño de la muestra que determina el promedio:

=AVERAGE(IF(MOD(ROW(B2:B16)-2,D19)=0,B2:B16,""))

Esto le permite segmentar rápida y fácilmente un gran conjunto de datos en subconjuntos.

Dominar fórmulas matriciales en Excel es esencial

Estos ejemplos simples son un buen punto de partida para comprender la lógica detrás de las fórmulas de matriz. Piénselo un poco para que pueda realizar múltiples operaciones rápidamente hoy y en el futuro ajustando solo algunas referencias y funciones de celda. Las fórmulas de matriz son una forma ligera y eficiente de dividir y trocear datos. Domínelos al comienzo de su viaje de aprendizaje de Excel para que pueda realizar cálculos estadísticos avanzados con confianza.