Técnicas avanzadas de fórmulas de matrices dinámicas (3 métodos)

En la parte final de esta serie, veremos algunas técnicas avanzadas de fórmulas de matrices dinámicas. No cubriremos las funciones individuales en detalle, pero consideraremos cómo podemos combinarlas para resolver algunos problemas complicados. Muchas de estas técnicas se han cubierto brevemente como ejemplos en publicaciones anteriores, pero ahora profundizaremos más.

NOTA: En septiembre de 2022, los usuarios de Excel 365 obtuvieron acceso a 14 nuevas funciones de matriz dinámica (TEXTBEFORE, TEXTAFTER, TEXTSPLIT, VSTACK, HSTACK, TOROW, TOCOL, WRAPROWS, WRAPCOLS, TAKE, DROP, CHOOSEROWS, CHOOSECOLS, EXPAND). Estas funciones proporcionan métodos más sencillos y reemplazan muchas de las técnicas analizadas en esta página. Por lo tanto, los usuarios de Excel 365 deberían explorar estas funciones primero. https://techcommunity.microsoft.com/t5/excel-blog/anncing-new-text-and-array-functions/ba-p/3186066

Como los usuarios de Excel 2021 actualmente no tienen acceso a estas funciones, esta página proporciona la mejor información actual.

Hay tres áreas clave que cubriremos:

• Metodologías de fórmula única o fórmula en cascada
• Funciones de apoyo útiles
• Usando # Referencias con el operador unión

Todos estos son temas separados, pero cuando se combinan las técnicas, podemos lograr cosas sorprendentes.

Tabla de contenido

• Fórmula única vs. metodología de fórmula en cascada
• Fórmulas en cascada
• Fórmula única
• Funciones de apoyo útiles
• ELEGIR
• ÍNDICE
• SECUENCIA
• Usando # referencias con operador union
• Conclusión

Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.

Nombre del archivo: 0039 Técnicas de fórmula de matriz dinámica.zip

Tener acceso

Fórmula única vs. metodología de fórmula en cascada

Cuando escribimos fórmulas de matriz dinámica, tenemos algunas opciones. Una de las cuales es si escribir múltiples fórmulas en cascada o agregadas únicas. Cada una tiene ventajas y desventajas. Veamos un ejemplo para entender de qué estamos hablando.

Aquí están los datos que usaremos.

El objetivo de este ejemplo es calcular el valor total de la factura para cada cliente utilizando las funciones ÚNICA y SUMIFS.

Fórmulas en cascada

Esta primera opción utiliza dos fórmulas separadas en las celdas E3 y F3 .

La fórmula en la celda E3 es:

=UNIQUE(B3:B10)

Esta fórmula crea una lista distinta de clientes y genera el resultado en el rango de derrame que comienza en la celda E3 . Hemos visto a UNIQUE hacer esto antes en una publicación anterior .

La fórmula en la celda F3 es:

=SUMIFS(C3:C10,B3:B10,E3#)

Esta es una función SUMIFS estándar que utiliza el rango de derrame de la función ÚNICA en el último argumento.

El punto a tener en cuenta aquí es que existen dos fórmulas separadas para lograr el resultado final. La segunda fórmula se basa en el rango de derrame de la primera para crear su resultado.

Fórmula única

En este segundo escenario, vemos que se puede crear el mismo resultado a partir de una única fórmula.

La fórmula en la celda H3 es:

=CHOOSE({1,2},UNIQUE(B3:B10),SUMIFS(C3:C10,B3:B10,UNIQUE(B3:B10)))

Hemos utilizado ELEGIR para combinar las funciones en una sola matriz. La primera columna es el resultado de ÚNICO y la segunda columna es el resultado de SUMIFS. Como el rango de derrame de la función ÚNICA no existe en la hoja de trabajo, no podemos usar una referencia #; en su lugar, repetimos la función ÚNICA como último argumento de SUMIFS.

Funciones de apoyo útiles

Cuando trabajamos con matrices dinámicas, hay muchas funciones que nos ayudan a trabajar con matrices. Tres de las más útiles son ELEGIR, ÍNDICE y SECUENCIA. Consideraremos cada una de ellas en esta sección.

ELEGIR

Habiendo visto un ejemplo usando CHOOSE, este parece ser el lugar obvio para comenzar.

ELEGIR se puede utilizar para la agregación de matrices. En el ejemplo anterior, tomamos dos matrices separadas y las combinamos en una. Como hemos visto, esto es útil para crear un rango de derrame único seleccionando intencionalmente los datos a devolver.

ÍNDICE

ÍNDICE es una función que se puede utilizar para reducir la salida de nuestra función de matriz.

Mira el ejemplo de abajo.

La fórmula en la celda G3 es:

=INDEX(SORT(B3:E10,2,-1),{1;3;5;7},{1,4})

La función ORDENAR se aplica a las celdas B3-E10 , en orden descendente según la columna 2 . Para obtener más ejemplos del uso de SORT, consulte esta publicación .

El propósito de este ejemplo no es demostrar CLASIFICAR, sino mostrar cómo opera la función ÍNDICE. En nuestro escenario, ÍNDICE devuelve las filas 1, 3, 5 y 7 y las columnas 1 y 4 de la matriz. Tenga en cuenta que cuando trabaje con filas, la matriz constante está separada por punto y coma. Pero cuando se usa con columnas, las matrices constantes están separadas por comas.

ÍNDICE vs. ELIJA – ¿cuál es la diferencia?

De los ejemplos anteriores, INDEX y CHOOSE parecen estar realizando tareas similares, pero si lo piensas bien, en realidad están realizando lo opuesto entre sí. En este contexto, CHOOSE agrega columnas en una sola matriz, mientras que INDEX realiza tareas en una sola matriz. y selecciona los datos a retener.

Usar CHOOSE para agregar datos requiere que seamos explícitos sobre lo que combinamos. Sin embargo, el ÍNDICE se puede hacer más dinámico usando la función SECUENCIA para seleccionar qué datos retener.

SECUENCIA

Si bien SECUENCIA es una función de matriz dinámica por derecho propio, también es una excelente función de soporte. En el ejemplo anterior, usamos la función ÍNDICE con matrices constantes de tamaño fijo. Para crear funciones más dinámicas, podemos recurrir a la función SECUENCIA. .

Mira el ejemplo de abajo.

El resultado es el mismo que el ejemplo anterior, pero la fórmula en la celda J3 es:

=INDEX(SORT(B3:E10,2,-1),SEQUENCE(ROWS(B3:B10)/2,1,1,2),{1,4})

SEQUENCE crea una matriz de números alternativos, reemplazando la matriz constante utilizada anteriormente. Al usar SEQUENCE, cada uno de los argumentos se puede vincular a una celda, lo que significa que podríamos hacer que esto se seleccione fácilmente cada 3.ª, 4.ª o enésima fila simplemente cambiando un único valor de la celda.

Usando # referencias con operador union

Como técnica final, consideremos # referencias de derrame con el operador de unión. En este ejemplo, veremos cómo podemos agregar datos adicionales en un rango de derrame usando dos puntos (que es el operador de unión) entre direcciones de celda.

Sugerimos que tenemos un rango de desbordamiento que comienza en la celda A2 y queremos incluir una fila de encabezado en ese rango de desbordamiento. Podríamos usar la siguiente fórmula:

=A2#:A1

Excel entiende que se trata de un rango que cubre todas las celdas desde A1 hasta el final del rango de derrame que comienza en A2. No importa qué tan grande sea el rango de derrame de A2; se expandirá o retraerá automáticamente según sea necesario.

Para ilustrar esto aún más, me gustaría cubrir una técnica descubierta por Jon Acampora en esta publicación: https://www.excelcampus.com/functions/total-rows-dynamic-arrays

Aquí están los datos con los que trabajaremos:

En este ejemplo, queremos devolver los elementos con un recuento superior a 100, pero también incluir un encabezado y una fila total.

Primero, usemos la función FILTRO para devolver solo aquellos elementos con un recuento mayor que 100. Esto no es nada complicado; Hemos visto muchos ejemplos similares en la parte FILTRO de esta serie.

La fórmula en la celda F4 es:

=FILTER(B3:C10,C3:C10=100)

A continuación, agreguemos encabezados de columna encima de los datos. En la celda F3 , ingresé Item y en G3 , ingresé Count .

Ahora agreguemos una fila total.

En la celda F2 , ingresé Total y en la celda G2 , agregué la siguiente fórmula:

=SUM(INDEX(F4#,SEQUENCE(ROWS(F4#)),2))

Con suerte, notarás que estamos aplicando las técnicas de las secciones anteriores. En términos simples, esto es devolver la SUMA de todas las filas de la segunda columna del rango de derrame comenzando en F4 .

El siguiente paso es crear un número de índice, que representa el orden en el que deseamos mostrar las filas.

• La fórmula en la celda E4 es:

  =SECUENCIA(FILAS(F4#)) 

• Como la fila del encabezado debe aparecer primero, a la celda E3 se le ha asignado un índice codificado de 0 .
• Luego, la fórmula en la celda E2 calcula el número de filas en el rango de derrame más 1.

  =FILAS(E4#)+1

Todo lo que tenemos que hacer ahora es usar SORTBY para crear un nuevo rango de derrame con los datos mostrados en el mismo orden que la columna de índice.

La fórmula en la celda I2 es:

=SORTBY(F4#:F2,E4#:E2)

El punto clave a tener en cuenta aquí son los dos rangos:

• F4# :F2 se refiere a un rango que comienza en la celda F2 y termina al final del rango de derrame de F4.
• E4# :E2 es el rango de valores de índice que creamos

El mayor problema de esta técnica es que el resultado final no se formatea automáticamente.

• La fila del encabezado no se moverá, por lo que podemos formatearla usando las herramientas estándar.
• La fila del total se puede mover dependiendo de cómo cambien nuestros datos (es decir, si el recuento de melones aumenta a 150, la fila del total tendrá que moverse hacia abajo para acomodar la fila de datos adicional). Para formatear el total, necesitamos usar formato condicional. con un rango lo suficientemente grande como para cubrir cualquier crecimiento potencial.

Conclusión

Los rangos de derrame en cascada, las funciones de soporte y el operador de unión son técnicas de fórmulas de matrices dinámicas que podemos utilizar para hacer que las matrices dinámicas sean aún más flexibles.

Como puede ver, el ejemplo final de esta publicación incorpora muchas de nuestras otras técnicas de fórmulas. Vale la pena tomarse el tiempo para comprender cómo funciona; Si dominas esto, podrás lograr casi cualquier cosa con matrices dinámicas.

Esto es sólo el comienzo; A medida que más usuarios se apoderen de las funciones de matriz dinámica, puede estar seguro de que se descubrirán más técnicas de fórmulas. Quizás después de leer esta publicación y comprender los conceptos, pueda descubrir las nuevas técnicas.

¿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

---