En entradas anteriores hemos hablado un poco sobre cómo es que funcionan los Arrays dinámicos y las nuevas capacidades que tienen en Excel 365, pero todavía no hemos considerado cómo es que estos funcionan con otras funciones de Excel, tales como tablas, validaciones de datos, formatos condicionales… por mencionar algunos.
Por lo tanto, hoy vamos a cubrir un poco ese aspecto, aunque no vamos a explorar cada función de Excel, nos vamos a enfocar en las más comúnes y que estaremos usando más seguido.
Cuando pensamos en arrays dinámicos debemos hacer una distinción entre rangos y arrays, ya que muy seguido usamos ambos términos como si fueran lo mismo y este no es el caso. Conocer la diferencia entre uno y otro nos ayudará a entender mejor cómo es que se comportan los arrays dinámicos.
Arrays dinámicos: babysteps
Excel convierte datos entre arrays y rangos sin que el usuario siquiera lo note. Pensemos en el camino que recorren nuestros datos a la hora de usar una función, como en este caso será la función SORT. Tenemos algunos datos fuente en las celdas B3-C5. La fórmula en la celda E3 es:
=SORT(B3:C5)
Cuando usamos la función SORT, lo que está pasando es lo siguiente:
- El rango B3:C5 se convierte a un array: {“Plátano”,10;”Pera”,15;”Manzana”,5}.
- El cálculo SORT se realiza en el array, convirtiéndolo en {“Manzana,5;”Plátano”,10;”Pera”,15}.
- El resultado del array regresa dentro de la celda E3.
- Una vez que el resultado exista en la hoja de cálculo se volverá un rango, al cual nos podemos referir como E3#.
- Aun cuando las referencias # están en el anverso de la hoja de cálculo, Excel aun necesita recalcularlas para entender el tamaño del rango como E3:F5 al ser lllamadas.
Generalmente hablando, esto significa que lo siguiente expresado en funciones es correcto:
- Pueden manejar arrays y desarrollar cálculos pueden contener fórmulas de arrays dinámicos.
- Pueden manejar cálculos, pero no arrays, pueden usar el sistema de referencia #.
- No pueden desarrollar cálculos deben usar otros métodos para manejar esto en su lugar..
Arrays dinámicos en Tablas.
Ahora hablemos de tablas. Lo cierto es que los arrays dinámicos tienen una relación curiosa con las tablas de Excel.
Si usamos tablas de Excel como la fuente para una fórmula de array dinámico, todo funcionará de maravilla. Si nuevos datos son añadidos a la tabla, la fórmula se actualizará automáticamente para incluír esos nuevos datos, esta es la parte buena.
En la imágen de arriba tenemos que:
- La celda E3 contiene la siguiente fórmula:
=SORT(Table1)
Esto utiliza el rango entero de valores de Tabla1 y lo saca en el anverso de la hoja de cálculo.
Pero, si intentamos hacer lo opuesto y ponemos una fórmula de array dinámico dentro de una tabla de Excel y obtenemos el error #SPILL! es en donde tendremos la parte mala. Los arrays trabajan con valores y las tablas contienen valores, entonces, ¿dónde está el problema?
- La fórmula en la celda B3 es:
=$E$3:$F$5
Esta celda de referencia es el rango de derrame que inicia en la celda E3, pero esto siempre resulta en un error #SPILL!
Ahora tenemos la pregunta de por qué (1) funciona mientras que (2) no. Y hay dos razones:
- Cuando usamos una función de array dinámico con una tabla como su fuente, estamos convirtiendo la tabla en un array y luego regresando ese resultado en algún otro lugar en la hoja de cálculo. Por lo tanto, la fuente y salida no se cruzan.
- Cuando usamos un array dinámico dentro de una tabla tenemos problemas. Ambos objetos son contenedores para datos autoexpandibles. Si intentamos poner algo que se autoexpande dentro de algo que también se autoexpande entonces, ¿cuál se debe expandir primero? ¿Qué pasaría si uno crece fuera de los límites del otro? Hay muchas preguntas como esta sin una respuesta clara, por lo tanto el error #SPILL! es lo más probable a esperar que ocurra.
Arrays dinámicos y rangos de derrame.
Ahora vamos a darle un vistazo a dos objetos: arrays y rangos de derrame, empezando por los arrays.
Dado que el manejador de nombres puede contener arrays y fórmulas, también puede contener funciones de array dinámicos de manera directa. La siguiente imágen nos muestra un rango nombrado conteniendo la función SORT.
Podemos sacar el rango nombrado en el anverso de la hoja de cálculo, tal y como se muestra a continuación (celda H3 y su rango de derrame), o bien usarlo en funciones que acepten arrays.
Ahora tenemos los rangos de derrame. El administrador de nombres también puede contener referencias de derrame #, pero necesitamos tener cuidado con su creación. Mientras que los rangos nombrados pueden ser usados con absoluta o relativa referencia de celdas, para poder referenciar un rango de derrame correctamente tenemos que asegurarnos de que los símbolos $ son usados para crear una referencia absoluta.
El rango nombrado creado en la imágen anterior ha sido usado en la siguiente imágen (celda K3 y su rango de derrame):
USANDO RANGOS NOMBRADOS CON OTRAS FUNCIONES
Conforme seguimos avanzando podremos ver que los rangos nombrados son clave para usar arrays dinámicos con otras funciones. Aun cuando un rango de derrame es reconocido como un rango para fórmulas, parece ser que Excel necesita hacer procesos de fondo para averiguar qué tan grande es el rango. El administrador de nombres puede hacer cálculos para poder activar este procesamiento. Las funciones que no contienen algún cálculo pueden usar rangos nombrados como su fuente para manejar esta parte por ellas, pero eso lo iremos explorando por a poco.
Arrays dinámicos en graficos.
Empezando por los gráficos, los cuales pueden ser algo complicados respecto a los rangos dinámicos. Esto puede deberse a que el motor de graficado no intenta calcular rango alguno, sino que solo quiere usar el rango que se le ha dado. Esto no ha cambiado con la introducción de arrays dinámicos, y aun antes de ellos, si queríamos usar fórmulas tales como INDEX u OFFSET para crear un rango dinámico, necesitabamos ponerlo en un rango nombrado.
Como podemos ver en la siguiente imágen, usar un rango de derrame directamente en un gráfico fuente resultará en un error:
En su lugar, necesitamos usar un rango nombrado que contenga una referencia de derrame:
Debe notarse que la salida de los rangos nombrados debe ser del tipo correcto de datos para el elemento del gráfico. Esto quiere decir que los rangos nombrados usados para valores de gráficos deben contener números y que los rangos nombrados usados para etiquetas de eje deben contener valores de texto. Los gráficos pueden ser creados usando arrays, por lo tanto, podemos usar también un rango nombrado que contenga una fórmula de array dinámico como el gráfico fuente.
Arrays dinámicos en imágenes vinculadas.
Luego tenemos imágenes vinculadas, las cuales son similares a los gráficos puesto que no intentan calcular nada y solo quieren mostrar el rango que les son dados. Por lo tanto, usar una referencia # directamente dentro de una imágen vinculada no funcionará, así que debemos consultar el administrador de nombres una vez más.
La siguiente imágen nos muestra una imágen vinculada a un rango nombrado llamado RangoNombrado_ImagenVinculada:
Cuando los resultados cambien, también lo hará la imágen, crecerá o se encogerá con el tamaño del rango de derrame.
Para poder demostrar otra técnica aquí usaremos un par de trucos de Excel en el rango nombrado usado en la imágen vinculada de arriba. He unido dos rangos de derrame juntos dentro de un solo rango nombrado:
La fórmula en el administrador de nombres es:
='Linked Picture'!$C$3#:'Linked Picture'!$D$3#
Estos son dos rangos de derrame separados, los cuales han sido combinados como un solo rango usando dos puntos entre ellos. Esto crea un rango que va desde G3 hasta el final del rango de derrame para H3#.
Arrays dinámicos en Tablas dinámicas
Ahora pasamos a las tablas dinámicas, las cuales parecen no tener problemas usando referencias # como fuentes, tal y como se muestra a continuación:
Pero no es tan fácil, pues en cuanto le damos click en OK, Excel convertirá la referencia # a un rango estático estándar y entonces creará la tabla dinámica. La siguiente imágen muestra que ‘Pivot Table’!$E$2# ha sido convertida a un rango estático ‘‘Pivot Table’!$E$2:$F$7’.
En su lugar, podemos recurrir a los rangos nombrados una vez más. Las tablas dinámicas hacen una asumpción sobre nuestros datos, en el cual la primera fila es el encabezado de fila. Si no estamos usando los encabezados en el array, es importante construir uno nuevo que nos permita acceder a ellos.
Esta es la fórmula usada dentro del rango nombrado:
Ahora podemos usar el rango nombrado como la fuente para la tabla dinámica.
Arrays dinámicos en formato condicional.
Después tenemos el formato condicional, el cual no es compatible con los arrays dinámicos. Si usamos referencias # dentro de un formato condicional, Excel lo convertirá a un rango estático y ya no será dinámico. La siguiente imágen nos muestra que cuando usamos un rango de derrame ($F$3#) y damos click en Aplicar, se convertirá el rango a un rango estático ($F$3:$F$7):
Para usar formato condicional, tenemos que seleccionar un rango estático que sea mayor que nuestra posible salida. Esto dará la apariencia de ser dinámico, pero puede necesitar mentenimiento de vez en cuando cuando el rango de derrame se expanda más allá de las celdas de formato condicional.
Arrays dinámicos con listas de validación.
Por último tenemos las listas de validación de datos, las cuales pueden desarrollar cálculos y por lo tanto, funcionan excepcionalmente bien con el sistema de referencias # por sí mismas. Su única desventaja es que no usan arrays, por lo que no pueden contener una fórmula de array dinámico. La siguiente imágen nos muestra una lista de validación de datos que contiene un rango de derrame a partir de la celda G3.
Los datos aun deben estar en la forma correcta para poder trabajar con validación de datos, por lo que deben ser filas o columnas individuales. Si queremos usar un rango nombrado, también funcionará.
CONCLUSIONES
Con esto hemos visto que muchas funciones de Excel son directamente compatibles con arrays dinámicos, y para la mayoría de funciones que no lo son podemos usar el administrador de nombres para cubrir ese espacio. Si miramos a las reglas generales veremos que se nos ha proveído un buen entendimiento de cómo es que los arrays dinámicos operarán cuando se usan con otras funciones de Excel.
Cualquier duda, nos leemos abajo.