Las funciones de array dinámico en Excel (o también conocidas como «funciones DA») son capaces de filtrar, eliminar duplicados, organizar y realizar muchas otras funciones. Estas funciones se aplican a todo un rango de celdas, de ahí el nombre «funciones de array dinámico».
Las funciones DA representan un gran avance a nivel técnico y por tanto, a nivel práctico difieren de las funciones normales. Un ejemplo de esto es que las funciones normales solo aplican el valor a una celda, mientras que las funciones DA se aplican a todo un rango.
Otro ejemplo de ello es cómo las funciones DA contienen nuevas funciones para filtrar, ordenar, extraer objetos únicos y generar secuencias de números o valores aleatorios.
Respecto a sus limitaciones, las buenas noticias es que podremos usar las funciones DA solas o con funciones de Excel ya existentes. La única condición, y como era de esperarse, es que solo están disponibles en Excel 365.
Las funciones DA introducidas
Para abril de 2020, Microsoft ha introducido seis nuevas funciones bajo la categoría de array dinámico:
- FILTER, para filtrar un rango de celdas o tablas en base a un criterio establecido.
- RANDARRAY, para generar un rango de números aleatorios.
- SEQUENCE, para generar una secuencia de números en un rango de filas y/o columnas.
- SORT, para ordenar una lista por una columna específica.
- SORTBY, para ordenar una lista por otra lista.
- UNIQUE, para extraer objetos únicos de un rango de celdas o una tabla.
Aparte de estas nuevas funciones, la capacidad de DA nos permite usar estas características extra en Excel:
- Poder usar el operador # para referir a un rango de celdas derramado por funciones de Array Dinámico. Por ejemplo, si una celda A1 tiene una función DA que arroja el rango 10×1 (A1:A10), podremos referirnos a este rango dinámico por medio de la referencia A1#.
- La mayoría de fórmulas array ahora creáran rangos matriciales simplemente dentro de un rango de celdas sin necesidad de presionar CTRL+SHIFT+Enter.
- Fórmulas recién introducidas como XLOOKUP también pueden derramar produciendo una fila entera de resultados que coincidan.
- Cualquier fórmula o nombre que refiera a más de un valor será automáticamente derramado. Por ejemplo, si escribimos «=data» en una celda, esta arrojará la tabla completa en el rango derramado.
Explorando las funciones.
Para poder demostrar mejor el uso de las funciones DA usaremos una base de datos como ejemplo, la cual podremos descargar aquí.
FILTER()
Esta función filtra una tabla o lista de datos basada en las condiciones establecidas.La síntaxis de FILTER() lleva el formato «=FILTER(datos, condiciones, if empty value». Algunos ejemplos de esto serían:
- FILTER(data, (data[age]>30)*(data[departamento]=»Sitio web»))
Esto filtrará todos los datos donde la edad sea mayor a 30 y el departamento sea «Sitio web».
- FILTER(data, (data[Gerente]<>”Ian”)*(data[género]=»Mujer»))
Esto mostrará los datos de todo el personal femenino donde el gerente no sea Ian.
RANDARRAY()
Esta función también creará una lista de números al azar, tal y como hace SEQUENCE(), por lo cual no nos parecería útil, pero realmente lo es.
Su síntaxis simple es «RANDARRAY(count)», mientras que sus opciones la cambian a: «RANDARRAY(cuenta de filas, cuenta de columnas, número de inicio, número final, si lo queremos integrales al azar)».
Con algunos ejemplos siendo:
- RANDARRAY(10,,1,100,TRUE)
Genera 10 integrales al azar entre 1 y 100. - UNIQUE(RANDARRAY(10,,1,100,TRUE))
Generará 10 integrales al azar entre 1 y 100 y removerá cualquier duplicado que encuentre. - SORTBY(data, RANDARRAY(100))
Revolverá los datos en un orden aleatorio.
SEQUENCE()
Con la función SEQUENCE() vamos a poder generar una secuencia de números en filas, columnas o ambas. Podremos usarlo para crear secuencias de números, y aunque esto no parezca realmente útil, también lo podemos usar para crear soluciones elegantes y poderosas para análisis de datos.
La síntaxis simple es «=SEQUENCE(cuenta)», mientras que sus opciones añaden parámetros que la dejan así: =SEQUENCE(cuenta de filas, cuenta de columnas, número de inicio, paso por).
Algunos ejemplos de su funcionamiento son:
- SEQUENCE(10)
Generará números del 1 al 10 y los distribuirá en diez celdas. - SEQUENCE(100)<=10
Nos generará 100 valores, con los diez primeros siendo verdaderos mientras los demás serán falsos al no poder ser mayores a 10. - FILTER(SORT(data, 6, -1), SEQUENCE(100)<=10)
Mostrará datos para el top 10 de empleados por salario. - SORTBY(data, SEQUENCE(100), -1)
Mostrará los datos en orden invertido ordenando la secuencia de 100 números en orden descendente.
SORT()
Esta función ordenará una lista o datos por el número de columna especificado en un orden ascendente o descendente.
La síntaxis simple es «=SORT(lista)», mientras que las opciones extra cambiarían el formato a: «=SORT(lista, número de columna, orden ascendente o descendente, si preferimos ordenar a través de las columnas)»
Con algunos ejemplos de esto siendo:
- SORT(data[nombre])
Ordenará todos los nombres en los datos por orden ascendente, siendo este el predeterminado. - SORT(FILTER(data, data[gerente]=”Carla”),4)
Esto mostrará a todo el personal en los datos que reportan a Carla[?] por orden de edad ascendente (columna 4).
Algo importante a mencionar es el cómo hace SORT() con los valores similares. Digamos que al ordenar personal por edades usando la fórmula «SORT(data, 4)» tenemos casos donde la edad de dos empleados es la misma. Lo que hará SORT() es presentarlos en el mismo orden que se encontraban en los datos originales.
Si esto nos presenta un problema, entonces podremos usar función SORTBY(), la cual aceptará varios criterios para desempatar datos.
SORTBY()
SORTBY() se encarga de ordenar una lista o tabla en base a un conjunto de criterios, y lo podemos usar para desempatar datos similares o para definir criterios en varios niveles, como lo podría ser ordenar por departamento y edad al mismo tiempo.
Dado que no tiene síntaxis simplificada, su formato sería:
«SORTBY(lista 1, lista de criterios 1, orden de ordenamiento 1, lista 2, lista de criterios 2, orden de ordenamiento 2…»
Con esto se nos abren muchas posibilidades para analizar y presentar datos de una forma significativa. Algunos ejemplos prácticos serían:
- SORTBY(data, data[Departmento],1, data[Salario],-1)
Ordenará datos por departamento, en un orden alfabético y luego por salario en un orden descendente. - SORTBY(data, data[Departmento]=”RH”, -1, data[Nombre],1)
Nos muestra a todos los empleados en el departamento de RH hasta arriba y al resto por debajo y en órden alfabético.
UNIQUE()
La función UNIQUE() genera una lista de objetos únicos en base a una entrada, y le podemos dar un uso práctico para remover duplicados en una lista.
Su síntaxis simplificada sería «=UNIQUE(lista)», pero podemos elaborar más con opciones extra, lo cual haría que el formato se vea así: «=UNIQUE(lista, columna en la que están los datos, si queremos que los valores ocurran solo una vez)»
Estos son algunos ejemplos:
- UNIQUE(data[departamento])
Así se listará todos los departamentos, pero solo una fila por departamento. - UNIQUE(FILTER(data[departmento], data[edad]>40))
Esto listará todos los departamentos donde el personal sea mayor a 40 años. - UNIQUE({1;1;2;3;4;4;5;6;7;7;7;8;9;0;0}, FALSE, TRUE)
Esto regresará los números que solo aparezcan una vez, siendo en este caso 2, 3, 5, 6, 8 y 9.
¿Cómo interactúan los Arrays Dinámicos con otras carácteristicas de Excel?
Las funciones DA y los rangos de derrame se llevan bien con la mayoría de funcionalidades de Excel, algunas mejor que otras, por lo que habrá que hacer nota de las principales.
Rangos de derrame en otras fórmulas
Podemos referirnos a los rangos de derrame (el cual es generado por las funciones DA) usando el operador #. Por ejemplo, si tenemos una fórmula en la celda B4 para filtrar los salarios de todas las personas que reportan a Carla, nos podremos referir a todo el rango de derrame en otras situaciones por medio del operador # de esta forma:
- SUM(B4#)
Sumará todos los salarios listados en la celda B4 hacia abajo. Esta fórmula ajustará automáticamente si los datos o las condiciones de filtrado cambian.
- COUNTIFS(B4#, “>100000”)
Contará un número de valores en el rango de derrame mayores a 100000.
Esta imagen nos ayudará a entender mejor cómo trabajar con rangos de derrame en otras fórmulas.
Rangos nombrados y funciones DA
Es posible crear rangos nombrados y hacer que se refieran a un rango de derrame. También podemos usar rangos nombrados dentro de funciones DA y todo trabajará sin problemas. Por ejemplo, podemos crear un rango nombrado llamado «MejorDesempeno» que se refiera a la fórmula «=FILTER(data,data[Rating>=5]».
Tal y como se muestra en esta imágen:
Con esto podremos usar este rango nombrado en otras fórmulas o situaciones. Para contar el número de empleados de alto rendimiento, podemos usar «=ROWS(HighPerformers)».
Cuando combinamos las funciones DA con el poder de procesamiento de arrays de INDEX, podemos resolver algunos de los problemas que llegan a consumir mucho tiempo de forma simple.
Arrays Dinámicos en validación de datos
Podemos usar el operator # cuando nos refiramos a un rango de array dinámico con reglas de validación de datos. Por ejemplo, podemos establecer una lista desplegable de validación de datos en una celda para mostrar los nombres de los departamentos por orden ascendente usando el siguiente método:
- En una celda vacía (en este caso Q6), escribiremos la fórmula «=SORT(UNIQUE(data[Departmento]))».
- Ahora seleccionaremos la celda donde queremos aplicar la validación de datos. Iremos a Data > Validación y seleccionaremos el tipo como Lista.
- Seleccionaremos la fuente de la lista como $AH$6#.
- Ahora podremos seleccionar departamentos de la lista de validación.
Esta imágen nos ayudará a entenderlo de forma más práctica:
Formato condicional de Arrays Dinámicos
Desde abril de 2020, el formato condicional no reconoce los rangos de derrame dinámicos. Esto significa que cuando creamos una regla CF que se aplica a todo un rango de derrame, aun cuando Excel toma el valor B7# como valido, este convertirá automáticamente el rango a una dirección física, (como lo sería $B$7:$B$19).
Por lo cual, si tus datos o tu fórmula cambian, las reglas CF no se extenderán de manera automática.
Tablas y Arrays Dinámicos
Igualmente, desde abril de 2020, las tablas de Excel no reconocen los operadores de rangos de derrame. Esto significa que cuando creamos una tabla de un rango de derrame, este no se extenderá de forma automática si los datos o la fórmula cambian, lo cual se vería de esta forma:
Este grupo de funciones están pensadas para el máximo aprovechamiento de las hojas de calculo y seguro marcaran un parteaguas para el futuro de Excel. Si tienes dudas nos leemos abajo.
