Fórmulas y funciones de matriz en Excel: Potencia y versatilidad al alcance de tu mano
¿Te has preguntado alguna vez cómo simplificar tus análisis de datos y llevar tus habilidades en Excel al siguiente nivel? Las fórmulas y funciones de matriz son herramientas poderosas que pueden transformar la manera en que realizas cálculos y gestionas grandes volúmenes de información. En este artículo, exploraremos las maravillas de las matrices en Excel, desde sus fundamentos hasta aplicaciones prácticas, para que puedas optimizar tu flujo de trabajo y sorprender a tus colegas con resultados eficientes y precisos. ¡Prepárate para descubrir un mundo lleno de posibilidades!
Bienvenidos al mundo de las fórmulas y funciones de matriz en Excel. Si eres usuario de este popular programa de hojas de cálculo, seguro que estás familiarizado con las fórmulas básicas y las funciones predefinidas que te permiten realizar cálculos simples. Sin embargo, hoy vamos a adentrarnos en un nivel más avanzado, explorando las fórmulas y funciones de matriz, una herramienta poderosa que te ayudará a resolver problemas más complejos y a realizar análisis en tus datos de una manera más eficiente. ¡Prepárate para descubrir cómo estas fórmulas y funciones pueden impulsar tu productividad y llevarte al siguiente nivel en Excel!
En este tutorial, aprenderá qué es una fórmula matricial de Excel, cómo ingresarla correctamente en sus hojas de trabajo y cómo usar constantes y funciones de matriz.
Las fórmulas matriciales en Excel son una herramienta extremadamente poderosa y una de las más difíciles de dominar. Una única fórmula matricial puede realizar múltiples cálculos y reemplazar miles de fórmulas habituales. Y aún así, el 90% de los usuarios nunca ha utilizado funciones de matriz en sus hojas de trabajo simplemente porque tienen miedo de empezar a aprenderlas.
De hecho, las fórmulas matriciales son una de las funciones de Excel más confusas de aprender. El objetivo de este tutorial es hacer que la curva de aprendizaje sea lo más fácil y fluida posible.
¿Qué es una matriz en Excel?
Antes de comenzar con las funciones y fórmulas de matriz, averigüemos qué significa el término «matriz». Esencialmente, un formación es una colección de artículos. Los elementos pueden ser texto o números y pueden residir en una sola fila o columna, o en varias filas y columnas.
Por ejemplo, si coloca su lista de compras semanal en un formato de matriz de Excel, se vería así:
{«Leche», «Huevos», «Mantequilla», «Copos de maíz»}
Luego, si selecciona las celdas A1 a D1, ingrese la matriz anterior precedida por un signo igual (=) en la barra de fórmulas y presione CTRL + MAYÚS + ENTRARobtendrás el siguiente resultado:
>Lo que acaba de hacer es crear una matriz horizontal unidimensional. Nada terrible hasta ahora, ¿verdad?
¿Qué es una fórmula matricial en Excel?
La diferencia entre una fórmula matricial y una fórmula normal es que una fórmula matricial procesa varios valores en lugar de solo uno. En otras palabras, una fórmula matricial en Excel evalúa todos los valores individuales de una matriz y realiza múltiples cálculos en uno o varios elementos según las condiciones expresadas en la fórmula.
Una fórmula matricial no sólo puede tratar varios valores simultáneamente, sino que también puede devolver varios valores a la vez. Entonces, los resultados devueltos por una fórmula matricial también son una matriz.
Las fórmulas de matriz están disponibles en todas las versiones de Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 y versiones anteriores.
Y ahora parece ser el momento adecuado para que cree su primera fórmula matricial.
Ejemplo simple de fórmula matricial de Excel
Suponga que tiene algunos artículos en la columna B, sus precios en la columna C y desea calcular el total general de todas las ventas.
Por supuesto, nada le impide calcular primero los subtotales en cada fila con algo tan simple como =B2*C2 y luego sumar esos valores:
>Sin embargo, una fórmula matricial puede ahorrarle esas pulsaciones de teclas adicionales, ya que hace que Excel almacene resultados intermedios en la memoria en lugar de en una columna adicional. Entonces, todo lo que necesitas es una fórmula matricial única y 2 pasos rápidos:
=SUM(B2:B6*C2:C6)
Una vez hecho esto, Microsoft Excel rodea la fórmula con {llaves}, que es una indicación visual de una fórmula matricial.
Lo que hace la fórmula es multiplicar los valores en cada fila individual de la matriz especificada (celdas B2 a C6), sumar los subtotales y generar el total general:
>
Este sencillo ejemplo muestra lo poderosa que puede ser una fórmula matricial. Cuando trabaje con cientos y miles de filas de datos, piense cuánto tiempo puede ahorrar ingresando una fórmula matricial en una sola celda.
¿Por qué utilizar fórmulas matriciales en Excel?
Las fórmulas matrices de Excel son la herramienta más práctica para realizar cálculos sofisticados y tareas complejas. Una única fórmula matricial puede reemplazar literalmente cientos de fórmulas habituales. Las fórmulas matrices son muy buenas para tareas como:
Cómo ingresar una fórmula matricial en Excel (Ctrl + Shift + Enter)
Como ya sabes, la combinación de las 3 teclas CTRL + MAYÚS + ENTRAR es un toque mágico que convierte una fórmula regular en una fórmula matricial.
Al ingresar una fórmula matricial en Excel, hay 4 cosas importantes a tener en cuenta:
Debido a que todas las fórmulas de matriz de Excel requieren presionar Ctrl + Shift + Enter, a veces se les llama Fórmulas CSE.
Utilice la tecla F9 para evaluar partes de una fórmula matricial
Al trabajar con fórmulas matriciales en Excel, puedes observar cómo calculan y almacenan sus elementos (matrices internas) para mostrar el resultado final que ves en una celda. Para hacer esto, seleccione uno o varios argumentos dentro del paréntesis de una función y luego presione la tecla F9 llave. Para salir del modo de evaluación de fórmulas, presione la tecla Esc llave.
En el ejemplo anteriorpara ver los subtotales de todos los productos, seleccionas B2:B6*C2:C6, presionas F9 y obtener el siguiente resultado.
>
Nota. Preste atención a que debe seleccionar alguna parte de la fórmula antes de presionar F9; de lo contrario, la tecla F9 simplemente reemplazará su fórmula con los valores calculados.
Fórmulas matriciales de una sola celda y de varias celdas en Excel
La fórmula matricial de Excel puede devolver un resultado en una sola celda o en varias celdas. Una fórmula matricial ingresada en un rango de celdas se llama fórmula de varias celdas. Una fórmula matricial que reside en una sola celda se llama fórmula unicelular.
Existen algunas funciones de matriz de Excel que están diseñadas para devolver matrices de varias celdas, por ejemplo TRANSPONER, TENDENCIA, FRECUENCIA, ESTIMACIÓN LINEAL, etc.
Otras funciones, como SUMA, PROMEDIO, AGREGAR, MÁX, MIN, pueden calcular expresiones de matriz cuando se ingresan en una sola celda usando Ctrl + Mayús + Intro.
Los siguientes ejemplos demuestran cómo utilizar una fórmula matricial de una sola celda y de varias celdas.
Ejemplo 1. Una fórmula matricial de una sola celda
Suponga que tiene dos columnas que enumeran la cantidad de artículos vendidos en 2 meses diferentes, digamos las columnas B y C, y desea encontrar el aumento máximo de ventas.
Normalmente, agregaría una columna adicional, digamos la columna D, que calcula el cambio en las ventas para cada producto usando una fórmula como =C2-B2y luego encuentre el valor máximo en esa columna adicional =MAX(D:D).
Una fórmula matricial no necesita una columna adicional ya que almacena perfectamente los resultados intermedios en la memoria. Entonces, simplemente ingresa la siguiente fórmula y presiona Ctrl + Mayús + Intro:
=MAX(C2:C6-B2:B6)
>Ejemplo 2. Una fórmula matricial de varias celdas en Excel
En el anterior Ejemplo de sumasuponga que tiene que pagar un impuesto del 10% por cada venta y desea calcular el monto del impuesto para cada producto con una fórmula.
Seleccione el rango de celdas vacías, digamos D2:D6, e ingrese la siguiente fórmula en la barra de fórmulas:
=B2:B6 * C2:C6 * 0.1
Una vez que presione Ctrl + Mayús + IntroExcel colocará una instancia de su fórmula matricial en cada celda del rango seleccionado y obtendrá el siguiente resultado:
>Ejemplo 3. Usar una función de matriz de Excel para devolver una matriz de varias celdas
Como ya se mencionó, Microsoft Excel proporciona algunas de las llamadas «funciones de matriz» que están especialmente diseñadas para trabajar con matrices de varias celdas. TRANSPONER es una de esas funciones y la utilizaremos para transponer la tabla anterior, es decir, convertir filas en columnas.
En nuestro ejemplo, la fórmula es:
=TRANSPOSE($A$1:$D$6)
El resultado será similar a este:
>Así es como se usa TRANSPOSE como fórmula matricial CSE en Excel 2019 y versiones anteriores. En Dynamic Array Excel, esto también funciona como una fórmula normal. Para conocer otras formas de transponer en Excel, consulte este tutorial: Cómo cambiar columnas y filas en Excel.
Cómo trabajar con fórmulas de matriz de varias celdas
Cuando trabaje con fórmulas de matriz de varias celdas en Excel, asegúrese de seguir estas reglas para obtener los resultados correctos:
Si su fórmula puede devolver una matriz con un número variable de elementos, ingrésela en un rango igual o mayor que la matriz máxima devuelta por la fórmula y ajuste su fórmula en la función SIERROR, como se demuestra en este ejemplo.
Constantes de matriz de Excel
En Microsoft Excel, una constante de matriz es simplemente un conjunto de valores estáticos. Estos valores nunca cambian cuando copia una fórmula a otras celdas o valores.
Ya viste un ejemplo de una constante de matriz creada a partir de una lista de compras en el mismo comienzo de este tutorial. Ahora, veamos qué otros tipos de matrices existen y cómo se crean.
Existen 3 tipos de constantes de matriz:
1. Constante de matriz horizontal
Una constante de matriz horizontal reside en una fila. Para crear una constante de matriz de filas, escriba los valores separados por comas y enciérrelos entre llaves, por ejemplo {1,2,3,4}.
Nota. Al crear una constante de matriz, debe escribir las llaves de apertura y cierre manualmente.
Para ingresar una matriz horizontal en una hoja de cálculo, seleccione el número correspondiente de celdas en blanco en una fila, escriba la fórmula ={1,2,3,4} en la barra de fórmulas y presione Ctrl + Mayús + Intro. El resultado será similar a este:
>Como puede ver en la captura de pantalla, Excel envuelve una constante de matriz entre otro conjunto de llaves, exactamente como lo hace cuando ingresa una fórmula matricial.
2. Constante de matriz vertical
Una constante de matriz vertical reside en una columna. Lo creas de la misma forma que un array horizontal con la única diferencia de que delimitas los elementos con punto y coma, por ejemplo:
={11; 22; 33; 44}
>3. Constante de matriz bidimensional
Para crear una matriz bidimensional, separe cada fila con un punto y coma y cada columna de datos con una coma.
={«a», «b», «c»; 1, 2, 3}
>Trabajar con constantes de matriz de Excel
Las constantes de matriz son una de las piedras angulares de una fórmula matricial de Excel. La siguiente información y consejos pueden ayudarle a utilizarlos de la manera más eficiente.
Una constante de matriz puede contener números, valores de texto, valores booleanos (VERDADERO y FALSO) y valores de error, separados por comas o punto y coma.
Puede ingresar un valor numérico como un número entero, decimal o en notación científica. Si utiliza valores de texto, deben estar entre comillas dobles («) como en cualquier fórmula de Excel.
Una constante de matriz no puede incluir otras matrices, referencias de celdas, rangos, fechas, nombres definidos, fórmulas o funciones.
Para hacer que una constante de matriz sea más fácil de usar, asígnele un nombre:
- Cambiar a la Pestaña Fórmulas > Nombres definidos grupo y haga clic Definir nombre. Alternativamente, presione Ctrl + F3 y haga clic Nuevo.
- Escriba el nombre en el Nombre
- En el Se refiere a , ingrese los elementos de la constante de su matriz entre llaves con el signo de igualdad anterior (=). Por ejemplo:
={«Su», «Mo», «Tu», «We», «Th», «Fr», «Sa»}
> - Haga clic en Aceptar para guardar la matriz con nombre y cerrar la ventana.
>Para ingresar la constante de matriz con nombre en una hoja, seleccione tantas celdas en una fila o columna como elementos haya en su matriz, escriba el nombre de la matriz en la barra de fórmulas precedido por el signo = y presione Ctrl + Mayús + Intro.
El resultado debería parecerse a esto:
>
Si su constante de matriz no funciona correctamente, verifique los siguientes problemas:
- Delimite los elementos de su constante de matriz con el carácter adecuado: coma en constantes de matriz horizontales y punto y coma en las verticales.
- Seleccionó un rango de celdas que coincide exactamente con la cantidad de elementos en su matriz constante. Si selecciona más celdas, cada celda adicional tendrá el error #N/A. Si selecciona menos celdas, solo se insertará una parte de la matriz.
Usar constantes de matriz en fórmulas de Excel
Ahora que está familiarizado con el concepto de constantes de matriz, veamos cómo puede utilizar fórmulas de matriz para resolver sus tareas prácticas.
Ejemplo 1. Sumar N números más grandes/más pequeños en un rango
Empiece por crear una matriz vertical constante que contenga tantos números como desee sumar. Por ejemplo, si desea sumar 3 números más pequeños o más grandes en un rango, la constante de la matriz es {1,2,3}.
Luego, toma la función GRANDE o PEQUEÑA, especifica el rango completo de celdas en el primer parámetro e incluye la constante de matriz en el segundo. Finalmente, incorpórelo en la función SUMA, así:
Suma los 3 números más grandes: =SUM(LARGE(range, {1,2,3}))
Suma los 3 números más pequeños: =SUM(SMALL(range, {1,2,3}))
No olvides presionar Ctrl + Mayús + Intro ya que está ingresando una fórmula matricial y obtendrá el siguiente resultado:
>De manera similar, puedes calcular el promedio de N valores más pequeños o más grandes en un rango:
Promedio de los 3 primeros números: =AVERAGE(LARGE(range, {1,2,3}))
Promedio de los 3 últimos números: =AVERAGE(SMALL(range, {1,2,3}))
Ejemplo 2. Fórmula matricial para contar celdas con múltiples condiciones
Supongamos que tiene una lista de pedidos y desea saber cuántas veces un vendedor determinado ha vendido determinados productos.
La forma más sencilla sería utilizar una fórmula CONTAR.SI con múltiples condiciones. Sin embargo, si desea incluir muchos productos, su fórmula COUNTIFS puede crecer demasiado. Para hacerlo más compacto, puedes usar CONTAR.SI junto con SUMA e incluir una constante de matriz en uno o varios argumentos, por ejemplo:
=SUM(COUNTIFS(range1, «criteria1», range2, {«criteria1», «criteria2»}))
La fórmula real puede ser la siguiente:
=SUM(COUNTIFS(B2:B9, «sally», C2:C9, {«apples», «lemons»}))
>Nuestra matriz de muestra consta de solo dos elementos ya que el objetivo es demostrar el enfoque. En tus fórmulas matrices reales, puedes incluir tantos elementos como tu lógica de negocios requiera, siempre que la longitud total de la fórmula no exceda los 8.192 caracteres en Excel 2019 – 2007 (1.024 caracteres en Excel 2003 y anteriores) y tu computadora sea potente. suficiente para procesar matrices grandes. Consulte las limitaciones de las fórmulas matriciales para obtener más detalles.
Y aquí hay un ejemplo de fórmula matricial avanzada que encuentra la suma de todos los valores coincidentes en una tabla: SUMA y BUSCARV con una constante de matriz.
Operadores Y y O en fórmulas matriciales de Excel
Un operador de matriz le dice a la fórmula cómo desea procesar las matrices, utilizando la lógica AND u OR.
- El operador Y es el asterisco
- que es el símbolo de multiplicación. Le indica a Excel que devuelva VERDADERO si TODAS las condiciones se evalúan como VERDADERAS.
El operador O es el signo más (+). Devuelve VERDADERO si CUALQUIERA de las condiciones en una expresión dada se evalúa como VERDADERA.
Fórmula matricial con el operador AND En este ejemplo, encontramos la suma de ventas donde está el vendedor. Miguel Y el producto esmanzanas
=SUM((A2:A9=»Mike») * (B2:B9=»Apples») * (C2:C9))
:
=SUM(IF(((A2:A9=»Mike») * (B2:B9=»Apples»)), (C2:C9)))
>Una fórmula matricial con el operador AND
Técnicamente, esta fórmula multiplica los elementos de las tres matrices en las mismas posiciones. Las dos primeras matrices están representadas por valores VERDADERO y FALSO que son el resultado de comparar A2:A9 con Mike» y B2:B9 con «Manzanas». La tercera matriz contiene los números de ventas del rango C2:C9. Como cualquier operación matemática , la multiplicación convierte VERDADERO y FALSO en 1 y 0, respectivamente. Y como multiplicar por 0 siempre da cero, la matriz resultante tiene 0 cuando no se cumplen una o ambas condiciones. Si se cumplen ambas condiciones, el elemento correspondiente de la tercera matriz obtiene en la matriz final (por ejemplo, 1*1*C2 = 10). Entonces, el resultado de la multiplicación es esta matriz: {10;0;0;30;0;0;0;0}. Finalmente, la función SUMA suma los elementos de la matriz y devuelve un resultado de 40.
Fórmula matricial de Excel con el operador O La siguiente fórmula matricial con el operador OR (+) suma todas las ventas donde el vendedor es Mike O el producto es
=SUM(IF(((A2:A9=»Mike») + (B2:B9=»Apples»)), (C2:C9)))
>Fórmula matricial de Excel con el operador O
En esta fórmula, suma los elementos de las dos primeras matrices (que son las condiciones que desea probar) y obtiene VERDADERO (>0) si al menos una condición se evalúa como VERDADERA; FALSO (0) cuando todas las condiciones se evalúan como FALSO. Luego, IF verifica si el resultado de la suma es mayor que 0, y si lo es, SUM suma un elemento correspondiente de la tercera matriz (C2:C9). Consejo.
En las versiones modernas de Excel, no es necesario utilizar una fórmula matricial para este tipo de tareas: una simple fórmula SUMIFS las maneja perfectamente. Sin embargo, los operadores AND y OR en fórmulas matriciales pueden resultar útiles en escenarios más complejos, y mucho menos una muy buena gimnasia mental 🙂
Operador unario doble en fórmulas matriciales de Excel
Si alguna vez ha trabajado con fórmulas matriciales en Excel, es probable que haya encontrado algunas que contengan un guión doble (–) y se haya preguntado para qué se utiliza. Un doble guión, que técnicamente se llama operador unario doble,
se utiliza para convertir valores booleanos no numéricos (VERDADERO/FALSO) devueltos por algunas expresiones en 1 y 0 que una función de matriz pueda entender.
Se espera que el siguiente ejemplo haga las cosas más fáciles de entender. Suponga que tiene una lista de fechas en la columna A y desea saber cuántas fechas ocurren en enero, independientemente del año.
=SUM(–(MONTH(A2:A10)=1))
La siguiente fórmula funcionará de maravilla: Dado que se trata de una fórmula matricial de Excel, recuerde presionar Ctrl + Mayús + Intro
para completarlo.
>Usando el operador unario doble en fórmulas matriciales de Excel
Y ahora analicemos cómo funciona esta fórmula matricial. La función MES devuelve el mes de cada fecha en las celdas A2 a A10 representadas por un número de serie, lo que produce la matriz {2;1;4;2;12;1;2;12;1}. Después de eso, cada elemento de la matriz se compara con el valor de la celda D1, que es el número 1 en este ejemplo. El resultado de esta comparación es una matriz de valores booleanos VERDADERO y FALSO. Como recordará, puede seleccionar una determinada parte de una fórmula matricial y presionar F9
>Seleccione una parte de una fórmula matricial y presione F9 para ver a qué equivale esa parte.
Finalmente, debe convertir estos valores booleanos a 1 y 0 que la función SUMA pueda entender. Y para esto se necesita el operador unario doble. El primer unario fuerza VERDADERO/FALSO a -1/0, respectivamente. El segundo unario niega los valores, es decir, invierte el signo, convirtiéndolos en +1 y 0, que la mayoría de las funciones de Excel pueden entender y trabajar con ellos. Si elimina el doble unario de la fórmula anterior, no funcionará.