Fórmulas de matriz dinámicas y comportamiento de matriz desbordada

Las fórmulas de Excel que devuelven un conjunto de valores, también conocidos como matrices, devuelven estos valores a celdas cercanas. Este proceso se llama desbordamiento.

Las fórmulas que pueden devolver matrices de tamaño variable se denominan fórmulas de matriz dinámica. Las fórmulas que actualmente devuelven matrices que se desbordan correctamente se pueden denominar fórmulas de matriz desbordada.

A continuación se muestran algunas notas que le ayudarán a comprender y usar este tipo de fórmulas.

¿Qué significa el desbordamiento?

Nota: Las fórmulas de matriz antiguas, conocidas como fórmulas de matriz heredada, siempre devuelven un resultado de tamaño fijo: siempre se desbordan en el mismo número de celdas. El comportamiento de desbordamiento descrito en este tema no se aplica a las fórmulas de matriz heredada.

El desbordamiento significa que una fórmula ha dado como resultado varios valores y esos valores se han colocado en las celdas cercanas. Por ejemplo, =ORDENAR(D2:D11,1,-1), que ordena una matriz en orden descendente, devolvería una matriz correspondiente de 10 filas de alto. Pero solo tiene que escribir la fórmula en la celda superior izquierda, o F2 en este caso, y se desbordará automáticamente a la celda F11.

Ordenar los valores de las celdas D2:D11 con =SORT(D2:D11,1,-1)

Puntos clave

Al presionar Entrar para confirmar la fórmula, Excel ajustará dinámicamente el tamaño del rango de salida y colocará los resultados en cada celda dentro de ese rango.
Si está escribiendo una fórmula de matriz dinámica para actuar en una lista de datos, puede ser útil colocarla en una tabla de Excel y usar referencias estructuradas para hacer referencia a los datos. Esto se debe a que las referencias estructuradas se ajustan automáticamente a medida que se agregan o quitan filas de la tabla.
Las fórmulas de matriz desbordadas no se admiten en las propias tablas de Excel, por lo que debe colocarlas en la cuadrícula fuera de la tabla. Las tablas son más adecuadas para almacenar filas y columnas de datos independientes.
Una vez que escriba una fórmula de matriz desbordada, al seleccionar cualquier celda dentro del área de desbordamiento, Excel colocará un borde resaltado alrededor del rango. El borde desaparecerá cuando seleccione una celda fuera del área.
Solo se puede editar la primera celda del área de desbordamiento. Si selecciona otra celda en el área de desbordamiento, la fórmula será visible en la barra de fórmulas, pero el texto es "fantasma" y no se puede cambiar. Si necesita actualizar la fórmula, debe seleccionar la celda superior izquierda en el rango de matriz, cambiarla según sea necesario y, entonces, Excel actualizará automáticamente el resto del área de desbordamiento cuando presione Entrar.
Superposición de fórmulas: las fórmulas de matriz no se pueden introducir si hay algo que bloquee el rango de salida. y, si esto ocurre, Excel devolverá un error #DESBORDAMIENTO! que indica que hay un bloqueo. Cuando se elimine el bloqueo, la fórmula se desbordará según lo esperado. En el ejemplo siguiente, el rango de salida de la fórmula se superpone a otro rango con datos y se muestra con un borde de puntos superpuesto a celdas con valores que indican que no se puede desbordar. Quite los datos de bloqueo o cópielos en otro lugar y la fórmula se desbordará según lo previsto.
Las fórmulas de matriz heredadas escritas a través de CTRL+MAYÚS+ENTRAR (CSE) siguen siendo compatibles por motivos de compatibilidad inversa, pero ya no deben usarse. Si quiere, puede convertir fórmulas de matriz heredadas en fórmulas de matriz dinámicas. Para ello, busque la primera celda del rango de matriz, copie el texto de la fórmula, elimine todo el rango de la matriz heredada y vuelva a escribir la fórmula en la celda superior izquierda. Antes de actualizar las fórmulas de matriz heredadas a fórmulas de matriz dinámica, debe tener en cuenta algunas diferencias de cálculo entre las dos.
Excel ofrece soporte limitado para matrices dinámicas entre libros, solo se ofrece soporte para este escenario cuando ambos libros están abiertos. Si cierra el libro de origen, las fórmulas de matriz dinámica vinculadas devolverán un error #REF! al actualizarse.