Todo lo que necesitas saber sobre las constantes de matriz en Excel

Las constantes de matriz en Microsoft Excel son herramientas potentes para realizar múltiples cálculos con una sola fórmula. El uso de constantes de matriz en sus hojas de trabajo de Excel evita la necesidad de fórmulas largas o repetidas y elimina el riesgo de cambios de datos accidentales.

Las personas que no están familiarizadas con las constantes de matriz a menudo los encuentran desalentadores debido a su sintaxis inusual y a la necesidad de un símbolo especial para ingresarlos. Entonces, en esta guía, los despojaré a sus huesos y le mostraré cómo usarlos en escenarios del mundo real. ¡Al final, serás un experto constante de matriz!

Constantes de matriz en Excel: Definición

Una constante de matriz es un conjunto codificado de valores estáticos ingresados ​​en una fórmula, encerrado en aparatos ortopédicos. En un ejemplo directo, escribiendo:

={10,20,30,40}

En la celda A1 y Pressing Enter devuelve una matriz dinámica de 10 en la celda A1, 20 en la célula B1, 30 en la célula C1 y 40 en la célula D1.

En el ejemplo anterior, el resultado de la matriz dinámica se derrama horizontalmente (en columnas) porque las comas se usan entre cada valor en la constante de la matriz.

Por otro lado, el uso de semicolones entre los valores en la matriz constante devuelve una matriz dinámica vertical (en filas):

={10;20;30;40}

También puede combinar comas y semicolones en una matriz constante para devolver una matriz dinámica bidimensional:

={10,20;30,40}

Las constantes de matriz funcionan de manera diferente a lo que solían

Versiones únicas de Excel desde 2021 en adelante, Excel para Microsoft 365 y Excel para la web, todos admiten fórmulas de matriz dinámica. Esto significa que cuando escribe una constante de matriz en una fórmula que espera múltiples valores y presiona ENTER, el resultado se derrama en las celdas adyacentes, con solo la celda superior izquierda que contiene una fórmula.

Sin embargo, en versiones anteriores de Excel que no admiten fórmulas de matriz dinámica, debe seleccionar todas las celdas donde desea que se muestren los resultados, escriba la fórmula y presione Ctrl+Shift+Enter para confirmarlo. En este punto, la misma fórmula aparece en todas las celdas que seleccionó. Esto se conoce como una fórmula de matriz CSE o Legacy.

Sistema operativo

Windows, macOS, iPhone, iPad, Android

Prueba gratuita

1 mes

Microsoft 365 incluye acceso a aplicaciones de oficina como Word, Excel y PowerPoint en hasta cinco dispositivos, 1 TB de almacenamiento OneDrive y más.

Uso de constantes de matriz con funciones de Excel

Las constantes de matriz son más útiles en Excel cuando se combinan con funciones, ya que te dejan de tener que ingresar múltiples o largas fórmulas. Aquí hay dos escenarios de ejemplo.

Suma y cuenta: contar cuántos valores en una matriz cumplen con múltiples criterios

Imagina que tienes esta mesa de Excel, nombrada T_TaskLogy desea averiguar la cantidad de tareas cuyo estado es Completo, Pendienteo Inclinado.

Para hacer esto sin constantes de matriz, puede escribir la siguiente fórmula (dividida en líneas separadas para una lectura más fácil):

=COUNTIF(T_TaskLog[Status],"Complete")
+
COUNTIF(T_TaskLog[Status],"Pending")
+
COUNTIF(T_TaskLog[Status],"Pitched")

donde se suman los resultados de tres fórmulas Countif separadas utilizando el operador más (+).

Sin embargo, esta fórmula es larga y difícil de analizar. En su lugar, puede usar constantes de matriz para agrupar todos los criterios en un solo argumento. Primero, escriba el nombre de la función, un paréntesis de apertura y el rango para evaluar, seguido de una coma:

=COUNTIF(T_TaskLog[Status],

Ahora, para los criterios, debe ingresar los tres valores de estado como una matriz constante, encerrada en aparatos ortopédicos. Luego, agregue un paréntesis de cierre y presione Entrar:

=COUNTIF(T_TaskLog[Status],{"Complete","Pending","Pitched"})

En este punto, Excel devuelve tres resultados separados como una matriz dinámica: 3 para Completo2 para Pendientey 2 para Inclinado. Eso es porque aún no le has dicho a Excel que agregue los resultados juntos.

Para arreglar esto, envuelva toda la fórmula dentro de la función de suma y presione Entrar:

=SUM(COUNTIF(T_TaskLog[Status],{"Complete","Pending","Pitched"}))

Por lo tanto, en lugar de usar tres fórmulas de cuenta separadas separadas por el signo más para contar los tres criterios de texto, la matriz constante obliga a realizar el cálculo tres veces, una para cada criterio, y agregue los resultados a través de la función de suma.

Grande: encontrar la parte superior incógnita Valores en un rango

En este ejemplo, supongamos que desea devolver las tres ganancias principales de la tabla, nombradas T_profitscomo una matriz separada.

Una forma de hacerlo es usar la función grande en tres células separadas.

Escribir esta fórmula en Cell E2 devuelve el valor más grande en la columna de ganancias:

=LARGE(T_Profits[Profit],1)

Luego, escribir esta fórmula en la celda E3 devuelve el segundo valor más grande:

=LARGE(T_Profits[Profit],2)

Finalmente, escribir esta fórmula en Cell E4 devuelve el tercer valor más grande:

=LARGE(T_Profits[Profit],3)

Luego puede usar la función xlookup para recuperar las ID de las tiendas donde se obtuvieron estas tres ganancias más altas:

=XLOOKUP(E2:E4,T_Profits[Profit],T_Profits[Shop])

Sin embargo, escribir tres fórmulas separadas y no vinculadas lleva tiempo y es propenso al error. En su lugar, puede usar una matriz constante con la función grande para lograr el mismo resultado con una sola fórmula:

=LARGE(T_Profits[Profit],{1;2;3})

Esta fórmula aplica la función grande tres veces, la primera vez para devolver el valor más grande, la segunda vez para devolver el segundo valor más grande y la tercera vez para devolver el tercer valor más grande, y devuelve los tres resultados como una matriz dinámica vertical porque se usa un punto y coma entre cada número en la constante de la matriz.

Del mismo modo, si desea sumar las tres ganancias más grandes sin usar constantes de matriz, deberá ingresar la función grande tres veces, separada por el signo más:

=LARGE(T_Profits[Profit],1)+LARGE(T_Profits[Profit],2)+LARGE(T_Profits[Profit],3)

Sin embargo, el uso de una matriz constante con la función de suma hace que la fórmula sea mucho más ordenada:

=SUM(LARGE(T_Profits[Profit],{1,2,3}))

Además, si desea incluir la cuarta ganancia más grande en el cálculo, simplemente necesita agregar una coma y la número cuatro a la fórmula, en lugar de escribir una función extra grande en su totalidad:

=SUM(LARGE(T_Profits[Profit],{1,2,3,4}))

Nombres de constantes de matriz en Excel

Si te encuentras usando repetidamente el mismo conjunto de valores estáticos en Excel, nombrar una matriz constante te evitará escribirlo manualmente cada vez.

Para crear una constante de matriz nombrada, en la pestaña Fórmulas en la cinta, haga clic en «Administrador de nombres».

Luego, en el cuadro de diálogo Name Manager, haga clic en «Nuevo».

A continuación, en el campo se refiere al campo, escriba el signo igual, seguido de una abertura de aparejo rizado:

={

Ahora, escriba la constante de la matriz, recordando que para crear una matriz horizontal (fila), los valores deben estar separados por comas, y para matrices verticales (columna), debe separarlos con semicolones. Además, recuerde que los valores de texto deben estar encerrados en cotizaciones dobles. Cuando haya terminado, cierre los aparatos ortopédicos:

={"Mon","Tue","Wed","Thu","Fri","Sat","Sun"}

Finalmente, en el campo Nombre, escriba un nombre memorable para su constante de matriz y haga clic en «Aceptar».

Ahora puede usar esta constante de matriz en su libro de trabajo escribiendo el signo igual seguido del nombre que acaba de asignar y presionando Enter:

Al igual que con las constantes de matriz sin nombre, puede usarlas en fórmulas. En este ejemplo, la constante de matriz nombró Cinco es:

={5,10,15,20}

Entonces, multiplicar el valor en la celda A1 por esta constante de matriz nombrada produce cuatro resultados separados:

=A1*Fives

Puntos a tener en cuenta al usar constantes de matriz en Excel

A pesar de los muchos beneficios de usar constantes de matriz en Excel, existen algunas reglas y limitaciones que debe tener en cuenta:

• Las fórmulas de matriz dinámica no pueden derramarse en columnas de tabla estructuradas, por lo que las constantes de matriz que producen resultados de múltiples células solo pueden usarse en células regulares. Dicho esto, las constantes de matriz pueden operar con datos dentro de las tablas.

• Las constantes de matriz solo pueden contener texto en cotizaciones dobles, números simples (sin símbolos de divisas o porcentaje de signos), o valores booleanos (verdadero y falso), separados por los delimitadores de coma y semicolon. No pueden contener referencias celulares, otras matrices, fórmulas o comodines.

• Debido a que no puede hacer referencia a las celdas en constantes de matriz, todos los valores deben estar codificados (de ahí su nombre, matriz constantes). Como resultado, las fórmulas que contienen constantes de matriz son menos flexibles que las que no.

• Si planea compartir su libro de trabajo con personas con menos experiencia en Excel, tenga cuidado con el efecto de caja negra que crean las constantes de matriz. Es decir, debido a que el proceso de cálculo en las constantes de matriz es menos transparente que en las fórmulas estándar, aquellos que no están familiarizados con la lógica de matriz podrían luchar para comprender cómo funciona la hoja de cálculo.

• En algunos casos, el uso de una función de matriz dinámica es más eficiente que usar una constante de matriz. Por ejemplo, en lugar de ingresar una constante como {1,2,3}, puede usar la función de secuencia para producir el mismo resultado.

• Debido a que la forma en que se manejan las constantes de matriz en Excel cambió drásticamente cuando se introdujeron matrices dinámicas, cuando se abre un archivo de Excel en una versión moderna en una versión anterior, las matrices derramadas se convierten en fórmulas CSE.

Las constantes de matriz son particularmente útiles cuando se usan como argumentos en las funciones de Excel que no devuelven matrices dinámicas de forma predeterminada. Sin embargo, las funciones de matriz dinámica, disponibles en versiones únicas de Excel desde 2021 en adelante, Excel para Microsoft 365 y Excel para la web, están diseñadas específicamente para devolver múltiples valores a una gama de celdas. Ejemplos de estas funciones especiales incluyen Filter, Sort and Sortby, Unique y Xlookup.