Matrices dinámicas en Excel: todo lo que NECESITAS saber

Excel ha cambiado... en serio, ha cambiado. Cada vez que usábamos Excel en el pasado, aceptábamos una regla operativa simple; una fórmula, una celda. Incluso con fórmulas avanzadas, todavía era necesario tener una celda para cada una. Pero esto ha cambiado; Excel ahora permite que una sola fórmula llene varias celdas. Esto es posible porque Microsoft ha cambiado el motor de cálculo de Excel para permitir matrices dinámicas.

El término matrices dinámicas suena complicado, pero una vez que lo entiendas, apreciarás su simplicidad y potencia.

Nota: Al momento de escribir este artículo, las matrices dinámicas están disponibles en Excel 365, Excel Online y Excel 2021 únicamente. Excel 2019 y versiones anteriores no se actualizarán para incluir matrices dinámicas.

Tabla de contenido

• Descripción general de las matrices dinámicas
• Antes de las matrices dinámicas
• Con matrices dinámicas
• ¿Qué versiones de Excel tienen matrices dinámicas?
• Nuevas funciones de matriz dinámica
• Fórmulas de matriz dinámica
• Antes de las matrices dinámicas
• Con matrices dinámicas
• ¿Qué fórmulas se derraman?
• Derramar
• # Referencias
• matrices constantes
• El símbolo
• Conclusión

Descripción general de las matrices dinámicas

Comencemos viendo un ejemplo básico. Aquí está la fórmula:

=B2:B6

Vale, no es una fórmula que hayas usado nunca, pero es lo suficientemente simple como para ayudar a describir el impacto de las matrices dinámicas.

Antes de las matrices dinámicas

En Excel 2019 y versiones anteriores, una fórmula solo podía devolver el resultado de una celda. Si proporcionáramos una fórmula con cinco resultados (celdas B2 , B3 , B4 , B5 y B6 , según nuestro ejemplo), entonces era necesario hacer algunas suposiciones. hecho por Excel. Como resultado, hay múltiples resultados de la fórmula anterior:

• Si la fórmula estaba en línea con los datos de origen, Excel asumió que queremos el valor de la misma fila.
• Si la fórmula no coincidía con los datos de origen, Excel se confundía y devolvía el #¡VALOR! error.

Eche un vistazo a la captura de pantalla a continuación:

El valor de 1 se muestra en la celda D2 , ya que Excel supone que queremos el valor de la celda B2 porque está alineado con él. Si hubiéramos ingresado exactamente la misma fórmula en la celda D4 , Excel supone que queremos la celda en línea, por lo que devuelve 3 de la celda B4 .

Dado que la celda D7 no está alineada con ninguno de los datos de origen, Excel no sabe lo que queremos y devuelve el #¡VALOR! error. Esta suposición tiene un nombre técnico, intersección implícita . Pero realmente ya no necesitamos preocuparnos por eso.

Es interesante pensar que Excel podría calcular diferentes resultados a partir de la misma fórmula (con suerte, algo del pasado). Para anular la intersección implícita, Excel nos permitió presionar Ctrl+Shift+Enter al ingresar fórmulas. Pero no debemos preocuparnos sobre eso tampoco.

Con matrices dinámicas

Si tenemos una versión más nueva de Excel habilitada para matrices dinámicas, solo hay un resultado posible para nuestra fórmula de ejemplo; Excel devuelve las 5 celdas. En la captura de pantalla siguiente, la celda D2 contiene la fórmula, pero el resultado se muestra en las celdas D2 , D3 , D4 , D5 y D6 . Una fórmula muestra 5 resultados... ¡increíble!

La regla básica de una fórmula de una celda ha desaparecido. La terminología para describir una fórmula que llena varias celdas es desbordamiento , y el rango de celdas llenas por esa fórmula se llama rango de desbordamiento .

¿Qué versiones de Excel tienen matrices dinámicas?

Microsoft anunció originalmente el cambio en el motor de cálculo de Excel en septiembre de 2018. Durante más de un año, solo estuvo disponible para aquellos que se registraron para probar las primeras versiones de las nuevas funciones. Los suscriptores habituales del canal de actualización mensual de Microsoft 365 comenzaron a recibir la actualización. a partir de noviembre de 2019. Finalmente, en julio de 2020, aquellos en el canal semestral de la suscripción a Microsoft 365 (en su mayoría usuarios empresariales) también recibieron matrices dinámicas.

Microsoft ya ha confirmado que esta nueva funcionalidad no estará disponible en Excel 2019 o versiones anteriores. Entonces, si la desea, es hora de actualizar a Excel 2021 o una licencia de Microsoft 365.

Nuevas funciones de matriz dinámica

Cuando Microsoft anunció la nueva funcionalidad de matriz dinámica, también introdujo 6 nuevas funciones, todas las cuales utilizan este nuevo motor de cálculo:

• ÚNICO : para enumerar los valores únicos o distintos en un rango
• ORDENAR : para ordenar los valores en un rango
• SORTBY : para ordenar valores según el orden de otros valores
• FILTRO : para devolver solo los valores que cumplen criterios específicos
• SECUENCIA – para devolver una secuencia de números
• RANDARRAY – para devolver una matriz de números aleatorios

Si bien a todos nos entusiasman las nuevas funciones, la introducción de matrices dinámicas es más grande que esto; es un cambio fundamental en la forma en que Excel (y los usuarios de Excel) piensan sobre TODAS las fórmulas. En el resto de esta publicación, comprenderá los conceptos básicos necesarios para comenzar a pensar de esta nueva manera.

Fórmulas de matriz dinámica

Como se señaló anteriormente, hay nuevas funciones que hacen uso de esta nueva funcionalidad de derrame. No entraremos en detalles sobre cada uno de estos en esta publicación; hay publicaciones separadas para cada uno de ellos. Lo fundamental a tener en cuenta es que la capacidad de derramar no se limita a estas nuevas funciones; muchas funciones existentes ahora también se derraman.

Mire los datos de ejemplo a continuación.

Este es un escenario simple en el que tenemos datos en las celdas B2-C9 . Estos datos muestran un nombre y una puntuación. Supongamos que el objetivo es calcular la puntuación total de cada persona.

Antes de las matrices dinámicas

Para calcular la puntuación total de cada individuo, podríamos utilizar la función SUMIFS.

Cada una de las celdas F2-F5 contiene una fórmula. Por ejemplo, F2 contiene lo siguiente:

=SUMIFS($C$2:$C$9,$B$2:$B$9,E2)

Para calcular el resultado para cada persona, esta fórmula se copiaría en las 3 filas siguientes. En cada fórmula, el último argumento cambiaría a las celdas de referencia E3 , E4 y E5 , respectivamente.

Con matrices dinámicas

Pero espera, el nuevo motor de cálculo de Excel devuelve múltiples resultados a partir de una única fórmula. Al igual que nuestra fórmula básica al comienzo de la publicación envió los resultados a otras celdas, SUMIFS funciona igual.

En lugar de tener 4 fórmulas, una para cada celda; podemos tener una fórmula que devuelva resultados en 4 celdas. La fórmula en la celda F2 demuestra esto:

=SUMIFS(C2:C9,B2:B9,E2:E5)

El último argumento en la función SUMIFS es el valor que se va a comparar. En lugar de un valor, proporcionamos una matriz de valores para que coincidan en las celdas E2-E5. Por lo tanto, Excel realizó los 4 cálculos y dividió los resultados en las celdas F2-F5. .

¿Qué fórmulas se derraman?

Entonces, ¿qué fórmulas se derraman y cuáles no? Buena pregunta. Depende de los argumentos que espera la fórmula.

Las funciones de agregación básicas, como SUMA, PROMEDIO, MIN, MAX, etc., no se derraman por sí solas, lo cual tiene sentido ya que aceptan un rango de valores y solo devuelven un valor único.

Generalmente, es probable que cualquier función que contenga un argumento en el que se evalúe un valor único como esperado se derrame. Ese valor único tiene un término técnico, se conoce como escalar .

Tomemos como ejemplo BUSCARV. El primer argumento en BUSCARV es el valor a buscar, generalmente es un valor único (es un escalar). Si proporcionamos dos o más valores en ese argumento, la fórmula se derramará y calculará el resultado. para cada elemento incluido dentro del valor de búsqueda.

Mire el siguiente ejemplo. La BUSCARV en la celda F7 busca a Dave y Jack , por lo que calcula dos veces y devuelve los valores en F7 y F8 .

Generalmente, la regla es que si trabajamos con fórmulas estándar, cada vez que usemos múltiples escalares, se derramará. Hay algunos escenarios técnicos en los que esto no es cierto, pero eso está fuera del alcance de esta publicación.

Derramar

Al hacer clic en una fórmula o en cualquier celda del rango de derrame, se muestra un cuadro azul para delinear todas las celdas dentro del mismo rango de derrame. Todo lo que está dentro del cuadro azul se calcula mediante la celda superior izquierda de ese cuadro.

Al seleccionar cualquier celda dentro del rango de derrame, la barra de fórmulas muestra la fórmula que genera ese resultado. Si es la celda superior izquierda, podemos editar la fórmula. Sin embargo, si seleccionamos una celda que no sea la superior izquierda, la fórmula aparece atenuada y no se puede editar.

Mire la captura de pantalla a continuación; Hemos seleccionado la segunda celda en el rango de derrame. La fórmula está atenuada porque en realidad no está en esa celda, está en la celda superior izquierda.

¿Qué sucede si ya hay datos en el rango de derrame? ¿Sobrescribirá los datos existentes? Afortunadamente, no sucede nada demasiado dramático. En cambio, la celda superior izquierda devuelve un mensaje #DERRAME. error.

Al hacer clic en el botón #DERRAME! error, Excel muestra una línea de puntos que muestra el rango de derrame y podemos ver qué está causando el problema.

Entonces es su elección (a) ingresar la fórmula en otra ubicación, o (b) mover o eliminar el valor que causa el #DERRAME. error.

#¡DERRAMAR! Los errores ocurren en las siguientes situaciones:

• El rango de derrame está fuera de las celdas disponibles en la hoja de trabajo
• El alcance del derrame tiene un tamaño desconocido
• La fórmula matricial dinámica se incluye en una tabla de Excel.
• El rango de derrame contiene una celda fusionada
• El rango de derrame es tan grande que Excel se ha quedado sin memoria

# Referencias

Como una fórmula puede derramar resultados en otras celdas, necesitamos una forma de hacer referencia a todas las celdas en el rango de derrame. Afortunadamente, Microsoft ya pensó en esto y creó una nueva metodología de referencia utilizando el símbolo #.

Si la celda superior izquierda del rango de derrame está en la celda F2, podríamos hacer referencia a todo el rango de derrame usando F2# .

La siguiente captura de pantalla retoma nuestro ejemplo anterior, pero con una función PROMEDIO agregada en la celda F7 .

La fórmula en la celda F7 es:

=AVERAGE(F2#)

Al usar F2# , hacemos referencia a todas las celdas en el rango de derrame (celdas F2 , F3 , F4 y F5 ). Una ventaja significativa es que si el rango de derrame cambia de tamaño, el PROMEDIO se expandirá automáticamente para incluir el rango aumentado.

matrices constantes

Las matrices constantes siempre han existido en Excel; sin embargo, dada la introducción de matrices dinámicas, es probable que aumente el uso de matrices constantes. Suenan más complicadas de lo que son. Entonces, dediquemos unos minutos a comprender cómo funcionan.

La forma más sencilla de entender esto es con un ejemplo; usemos BUSCARV.

La fórmula en la celda G3 es:

=VLOOKUP(F6,B2:D5,{2,3},0)

Esta fórmula incluye una matriz constante de {2,3} . Excel utiliza los valores de 2 y 3 y realiza cálculos para ambos en las celdas G3 y H3 . El método antiguo requeriría dos fórmulas para lograr esto, pero con las matrices dinámicas podemos usar solo una.

Los dos números entre llaves se conocen como una matriz constante. Mira, después de todo, no dan demasiado miedo.

Con matrices constantes, solo tenga en cuenta que las columnas están separadas por comas y las filas por punto y coma. Si quisiéramos dividir en filas en lugar de columnas, usaríamos un punto y coma entre los valores (por ejemplo, {2;3 } ).

=BUSCARV(F6,B2:D5, {2;3} ,0)

Si ingresáramos 2 y 3 en el orden opuesto (por ejemplo, {3,2} ), entonces los valores devueltos también cambiarían de orden.

El símbolo

Para forzar a Excel a operar a la antigua usanza, podemos usar el símbolo @. Volvamos a nuestro ejemplo original:

=B2:B6

Para utilizar el antiguo método de intersección implícita que solo devuelve los valores que están en la misma fila, podemos agregar el símbolo @.

=@B2:B6

Es poco probable que alguna vez queramos volver a la antigua forma de calcular fórmulas, pero inicialmente, es probable que veamos mucho el símbolo @.

Para garantizar que las fórmulas creadas en versiones anteriores de Excel sigan calculando el mismo resultado, el símbolo @ se agregará automáticamente a algunas fórmulas. Esto significa que los libros creados en versiones anteriores de Excel pero abiertos en la versión más nueva de Excel nunca deberían derramarse. El símbolo @ garantiza la compatibilidad con versiones anteriores.

NOTA: Puede parecer confuso porque el símbolo @ ya se usa dentro del formato de referencia estructurado que usamos con las Tablas. Pero, si lo piensas bien, en las Tablas, el símbolo @ se usa para hacer referencia a elementos en la misma fila. Por lo tanto, Las referencias estructuradas en tablas ya tienen una intersección implícita incorporada.

Conclusión

Estoy seguro de que tiene 100 preguntas dando vueltas en su mente sobre las matrices dinámicas. Hay mucha terminología y formas de trabajar nuevas aquí. Si bien los cambios pueden parecer confusos al principio, pronto verá que esto aporta nuevos poderes a Excel. usuarios, lo que hace que Excel sea más fácil de usar.

¿Querer aprender más?

Hay mucho que aprender sobre las matrices dinámicas y las nuevas funciones. Consulte mis otras publicaciones aquí para obtener más información:

• Introducción a las matrices dinámicas : aprenda cómo ha cambiado el motor de cálculo de Excel.
• ÚNICO : para enumerar los valores únicos en un rango
• ORDENAR : para ordenar los valores en un rango
• SORTBY : para ordenar valores según el orden de otros valores
• FILTRO : para devolver solo los valores que cumplen criterios específicos
• SECUENCIA – para devolver una secuencia de números
• RANDARRAY – para devolver una matriz de números aleatorios
• Uso de matrices dinámicas con otras funciones de Excel : aprenda a usar matrices dinámicas con gráficos, tablas dinámicas, imágenes, etc.
• Técnicas avanzadas de fórmulas de matrices dinámicas : aprenda las técnicas avanzadas para gestionar matrices dinámicas

---