Cómo utilizar matrices dinámicas con otras funciones (7 escenarios)

Hasta ahora, en esta serie, hemos analizado cómo funcionan los arreglos dinámicos y las nuevas funciones que están disponibles para nosotros. La mayoría de nuestros ejemplos se han centrado en cálculos que ocurren en la hoja de trabajo. Pero aún no hemos considerado cómo funcionan los arreglos dinámicos. trabaje con otras funciones de Excel, como gráficos, validación de datos, formato condicional, etc. Entonces, cubriremos eso en esta publicación.

No veremos todas las funciones de Excel, sino que nos centraremos en las más comunes para las que probablemente usaremos matrices dinámicas.

Tabla de contenido

• Descripción general
• Matrices versus rangos
• Mesas
• Administrador de nombres
• matrices
• Rangos de derrames (# referencias)
• Usar rangos con nombre con otras funciones
• Gráficos
• Imágenes vinculadas
• Tablas dinamicas
• Formato condicional
• Validación de datos
• 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 de archivo: 0038 Matrices dinámicas con otras funciones de Excel.xlsx

Tener acceso

Descripción general

Cuando se piensa en matrices dinámicas, resulta útil hacer una distinción clara entre rangos y matrices. A menudo usamos estos términos indistintamente, pero no son lo mismo y Excel ciertamente los aplica de manera diferente. Si tenemos una idea clara de esto, será más fácil entender cómo se comportan los arreglos dinámicos como lo hacen.

Matrices versus rangos

Excel convierte datos entre matrices y rangos sin que el usuario se dé cuenta. Pensemos en el viaje que realizan nuestros datos usando la función ORDENAR como ejemplo. Tenemos algunos datos de origen en las celdas B3-C5 .

La fórmula en la celda E3 es:

=SORT(B3:C5)

Cuando usamos la función ORDENAR, lo que realmente sucede:

1. El rango B3:C5 se convierte en una matriz: {“Banana”,10;”Pear”,15;”Apple”,5}
2. El cálculo SORT se realiza sobre la matriz, convirtiéndola en {“Apple,5;”Banana”,10;”Pear”,15}
3. El resultado de la matriz se devuelve a la celda E3.
4. Una vez que el resultado existe en la hoja de trabajo, se convierte en un rango al que podemos referirnos como E3#
5. Aunque hay # referencias en la hoja de cálculo, cuando se llama, Excel aún necesita volver a calcular para comprender el tamaño del rango como E3:F5 .

Esto significa que, en términos generales, las siguientes afirmaciones son ciertas:

• Las funciones que pueden manejar matrices y realizar cálculos pueden contener fórmulas de matrices dinámicas
• Las funciones que pueden manejar cálculos, pero no matrices, pueden usar el sistema de referencia #
• Las funciones que no pueden realizar cálculos deben utilizar otros métodos para manejar esto por ellos.

Mesas

Las matrices dinámicas tienen una relación de amor/odio con las tablas de Excel.

Si utilizamos tablas de Excel como fuente para una fórmula matricial dinámica, todo funciona de manera brillante. Si se agregan nuevos datos a la tabla, la fórmula matricial dinámica se actualiza automáticamente para incluir los nuevos datos. Esa es la parte que me encanta.

Sin embargo, intente hacer lo inverso y coloque una fórmula matricial dinámica dentro de una tabla de Excel, y obtendremos el mensaje #SPILL! error. Esa es la parte del odio. Las matrices tratan sobre valores y las tablas contienen valores, entonces, ¿por qué hay un problema?

En la captura de pantalla anterior:

1. La celda G3 contiene la siguiente fórmula:

   = ORDENAR (Tabla 1)

   Esto utiliza el rango completo de valores de la Tabla 1 y los resultados en el frente de la hoja de trabajo.

2. La fórmula en la celda L3 es:

   =G3#

   Esta referencia de celda es el rango de derrame que comienza en la celda G3 , pero siempre resulta en un #DERRAME. error.

¿Por qué (1) funciona y (2) no?

1. Cuando se utiliza una función de matriz dinámica con una tabla como fuente, se convierte la tabla en una matriz y luego se devuelve el resultado en otro lugar de la hoja de trabajo. Es un proceso de cálculo simple.
2. Cuando se utiliza una matriz dinámica dentro de una tabla, causa problemas. Ambos elementos son contenedores para datos que se expanden automáticamente y realizan cálculos automáticos. Entonces, si intentas poner algo que se expande y calcula automáticamente en otra cosa que también se expande y calcula automáticamente, ¿qué debería suceder? ¿Cuál debería expandirse primero? ¿Cuál debería calcular primero? ¿Qué pasa si uno crece fuera de los límites del otro? Hay demasiadas preguntas sin respuestas obvias. Dado que tenemos dos funciones en competencia que intentan lograr un resultado similar, #SPILL ! El error es probablemente lo mejor que podemos esperar.

Administrador de nombres

Una vez un lector me comentó que pensaba que los rangos con nombre tenían nombres incorrectos y que deberían llamarse fórmulas con nombre . Estoy de acuerdo, ya que el administrador de nombres puede contener una gran cantidad de otras cosas, como constantes, matrices, macros de Excel 4 y fórmulas.

En esta sección, veremos solo dos elementos (1) matrices y (2) rangos de derrame.

matrices

Como el administrador de nombres puede contener matrices y fórmulas, también está más que feliz de contener funciones de matrices dinámicas directamente. La siguiente captura de pantalla muestra un rango con nombre que contiene la función ORDENAR.

Podemos generar el rango con nombre en el frente de la hoja de trabajo (como se muestra en la captura de pantalla siguiente, celda G3 y su rango de derrame), o usarlo en funciones que acepten matrices.

Rangos de derrames (# referencias)

El administrador de nombres también se complace en contener # referencias de derrame, pero debemos tener cuidado con su creación. Si bien los rangos con nombre se pueden usar con referencias de celda absolutas o relativas, para hacer referencia a un rango de derrame correctamente, debemos asegurarnos de que se usen los símbolos $. para crear una referencia absoluta.

El rango con nombre creado en la captura de pantalla anterior se ha utilizado en la siguiente captura de pantalla (Celda L3 y su rango de derrame)

Usar rangos con nombre con otras funciones

A medida que avancemos en el resto de esta publicación, verá que los rangos con nombre son clave para usar matrices dinámicas con muchas otras características.

Aunque un rango de derrame se reconoce como un rango para fórmulas, parece que Excel necesita realizar algún procesamiento en segundo plano para determinar qué tan grande es el rango. El administrador de nombres puede realizar cálculos, por lo que puede activar este procesamiento. Características que no Si no contiene ningún cálculo, puede utilizar rangos con nombre como fuente para manejar el elemento de cálculo. Veremos esto más a fondo en las secciones siguientes.

Gráficos

Los gráficos ya pueden ser un poco exigentes con los rangos dinámicos. Creo que esto se debe a que el motor de gráficos no intenta calcular ningún rango, sino que simplemente quiere utilizar el rango que se le ha dado. Esto no ha cambiado con la introducción de las matrices dinámicas; siempre ha sido así. Incluso antes de las matrices dinámicas, si queríamos usar fórmulas como ÍNDICE o DESPLAZAMIENTO para crear un rango dinámico, necesitábamos colocarlo en un rango con nombre.

Las capturas de pantalla a continuación demuestran que el uso de un rango de derrame directamente en una fuente de gráfico generará un error.

En su lugar, necesitamos usar un rango con nombre que contenga una referencia de derrame.

Cabe señalar que la salida de los rangos con nombre debe ser del tipo de datos correcto para el elemento del gráfico. Con esto quiero decir que los rangos con nombre utilizados para los valores del gráfico deben contener números y los rangos con nombre utilizados para las etiquetas de los ejes deben contener valores de texto. .

Actualización para usuarios de Excel 365 :

Los usuarios de Excel 365 tienen una función adicional que no está disponible en Excel 2021; la capacidad de vincular un rango de datos de gráfico a un rango de matriz dinámica completo. Por ejemplo, si un gráfico utiliza las celdas B3:E10 como origen y ese rango también es el mismo que un rango de desbordamiento existente, Excel supone que el origen del gráfico debe ser el mismo que el rango de desbordamiento. Por lo tanto, el rango del gráfico cambia a medida que cambia el rango de derrame.

Imágenes vinculadas

Las imágenes vinculadas son similares a los gráficos; no intentan calcular nada; solo quieren mostrar el rango que se les proporciona. Por lo tanto, usar una referencia # directamente dentro de una imagen vinculada no funciona. Por lo tanto, debemos recurrir una vez más al administrador de nombres.

La siguiente captura de pantalla muestra una imagen vinculada a un rango con nombre llamado NamedRange_LinkedPicture. Ese rango con nombre contiene un cálculo de matriz dinámica.

Cuando se vuelve a calcular el rango nombrado, las filas y columnas pueden aumentar o disminuir. Luego, la imagen también crece o se reduce para coincidir con el tamaño del rango nombrado.

Para fines de demostración de otra técnica, he utilizado algunos trucos de Excel en el rango con nombre utilizado en la imagen vinculada de arriba. He unido dos rangos de derrames en un solo rango con nombre (como se muestra en la captura de pantalla a continuación).

La fórmula en el administrador de nombres es:

='Linked Picture'!$G$3#:'Linked Picture'!$H$3#

Estos son dos rangos de derrame separados, que se han combinado como un solo rango usando dos puntos en el medio . Esto crea un rango que va desde G3 hasta el final del rango de derrame para H3#.

Tablas dinamicas

Inicialmente, las tablas dinámicas parecen estar contentas con el uso de la referencia # como fuente, como se muestra en la siguiente captura de pantalla.

Pero no se deje engañar, tan pronto como hacemos clic en Aceptar , Excel convierte la referencia # a un rango estático estándar y luego crea la tabla dinámica. La siguiente captura de pantalla muestra que la 'Tabla dinámica'!$G$3# se ha convertido en una rango estático 'Tabla dinámica'!$G$3:$J$10

En lugar de ello, debemos recurrir una vez más a los rangos con nombre.

Las tablas dinámicas parten de una suposición sobre nuestros datos, que es que la primera fila es la fila del encabezado. Esto nos genera un problema ya que # referencias a menudo no incluyen el encabezado. En su lugar, podemos utilizar algunos trucos más de Excel.

Si nuestros datos son los siguientes:

Para incluir la fila del encabezado, podemos usar la siguiente fórmula en un rango con nombre; crea un rango desde G2 hasta el final del rango de derrame G3# .

='Pivot Table'!$G$3#:'Pivot Table'!$G$2

Aquí está la fórmula utilizada dentro de un rango con nombre.

Ahora podemos usar el rango nombrado como fuente para la tabla dinámica.

Esto ahora incluye correctamente la fila del encabezado para que podamos usarla para la tabla dinámica (como se muestra a continuación).

Actualización para usuarios de Excel 365 :

A finales de 2022 se lanzaron nuevas fórmulas de matriz dinámica. Una de esas fórmulas es VSTACK , que nos permite apilar matrices dinámicas en una sola matriz. Por lo tanto, ya no necesitamos utilizar trucos de rango para lograr el mismo resultado.

Formato condicional

El formato condicional no es compatible con matrices dinámicas. Si usamos # referencias dentro de un formato condicional, Excel lo convierte a un rango estático y ya no es dinámico.

La siguiente captura de pantalla demuestra que cuando se utiliza un rango de derrame ($B$3#), al hacer clic en Aplicar se convierte el rango en un rango estático ($B$3:$E$10).

Para utilizar formato condicional con matrices dinámicas, debemos seleccionar un rango estático que sea mayor que nuestra salida probable. Esto dará la apariencia de ser dinámico, pero puede necesitar mantenimiento de vez en cuando cuando el rango de derrame se expanda más que las celdas formateadas condicionalmente.

Validación de datos

Las listas de validación de datos realizan cálculos; pero sólo funcionan con rangos; no pueden contener matrices. Por lo tanto, una lista de validación de datos:

• Puede utilizar el sistema de referencia #.
• Puede usar un rango con nombre que use un rango (incluido el sistema de referencia #).
• No se puede contener una fórmula de matriz dinámica ni utilizar un rango con nombre que genere una matriz.

La siguiente captura de pantalla muestra una lista de validación de datos que contiene el rango de derrame que comienza en la celda G3. El sistema de referencia # se basa en un rango, por lo tanto funcionará correctamente en este escenario.

Nota: Los datos deben tener la forma correcta para trabajar con valoración de datos, por lo que debe ser una sola fila o columna.

Conclusión

Hemos visto que muchas funciones de Excel son directamente compatibles con las matrices dinámicas. Y para la mayoría de las funciones que no lo son, podemos usar el administrador de nombres para cerrar la brecha. Al observar las reglas generales, se proporciona una buena comprensión de cómo Las matrices dinámicas funcionan cuando se usan con otras funciones de Excel.

¿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

---