Explicación de las matrices dinámicas de Excel: ejemplos prácticos en ONLYOFFICE

Si alguna vez has tenido que copiar fórmulas manualmente en cientos de filas o has tenido que lidiar con complejas entradas de matrices mediante Ctrl+Mayús+Intro, las matrices dinámicas de Excel están a punto de cambiar tu flujo de trabajo. Una sola fórmula de matriz dinámica se expande automáticamente para ocupar tantas celdas como requiera el resultado, sin repeticiones ni actualizaciones manuales. En esta guía encontrarás todo lo que necesitas saber al respecto.

¿Qué son las matrices dinámicas en Excel?

Una matriz dinámica es un rango de valores generado por una sola fórmula que «se extiende» automáticamente a varias celdas adyacentes.

Antes de las matrices dinámicas, para obtener resultados en varias celdas era necesario copiar las fórmulas manualmente o utilizar las antiguas fórmulas de matriz CSE (introducidas con Ctrl+Mayús+Intro), que tenían un tamaño de salida fijo y no podían ampliarse ni reducirse al cambiar los datos.

Las fórmulas de matriz dinámica eliminan por completo esa limitación. Solo tienes que escribir la fórmula, pulsar Intro y Excel se encarga del resto. Si los datos de origen aumentan, el rango de extensión se amplía automáticamente. Si se eliminan datos, se contrae.

Ejemplo: =SORT(UNIQUE(A2:A100)) genera una lista ordenada y sin duplicados en una sola celda, sin CSE ni columnas auxiliares.

Cómo funcionan las matrices dinámicas: comportamiento de desbordamiento y funciones principales

Comportamiento de desbordamiento

Cuando una fórmula de matriz dinámica genera varios resultados, Excel los coloca en un bloque contiguo denominado «rango de extensión». Algunas cosas que debes saber:

• Solo la celda superior izquierda contiene la fórmula; el resto muestra los valores extendidos.
• El rango de desbordamiento se actualiza automáticamente a medida que cambian los datos de origen.
• Si alguna celda del área de salida está ocupada, Excel devuelve un error #SPILL!; elimine el bloqueo para solucionarlo.
• Usa el operador # para hacer referencia a todo un rango de desbordamiento: =SUM(A2#) suma todo lo desbordado por la fórmula en A2 y se ajusta automáticamente si ese rango crece.
• Las fórmulas de matriz dinámica no funcionan dentro de tablas; colócalas en la cuadrícula normal.

Funciones básicas de las matrices dinámicas

Echemos un vistazo a las funciones más importantes del Editor de hojas de cálculo ONLYOFFICE

1. FILTER — extrae las filas que cumplen una condición:

=FILTER(A2:D200, B2:B200="East")

2. UNIQUE — devuelve una lista sin duplicados:

=UNIQUE(A1:A8)

3. SORT — ordena un rango mediante una fórmula:

=SORT(A2:A9)

4. SEQUENCE — genera una tabla de números secuenciales:

=SEQUENCE(4; 7; 1; 1)

5. RANDARRAY — rellena un rango con números aleatorios:

=RANDARRAY(5; 3; 1; 100; 1)

Establece el último argumento en 1 para los números enteros. Se vuelve a calcular cada vez que se actualiza la hoja.

6. XLOOKUP — la alternativa moderna a VLOOKUP, capaz de devolver varias columnas a la vez:

=XLOOKUP(G2, A2:A200, B2:D200, "Not found")

El resultado se extiende automáticamente a las columnas adyacentes. Para obtener más información sobre las fórmulas de búsqueda, consulta nuestra guía sobre la función LOOKUP (BUSCAR). Comprender cómo funcionan los argumentos de las funciones resulta especialmente útil a la hora de anidar estas funciones entre sí.

Ventajas y aspectos a tener en cuenta de las matrices dinámicas

Por qué merecen la pena

• Ampliación automática: el resultado se adapta al tamaño de los datos sin necesidad de intervención manual.
• Menos fórmulas: una sola fórmula sustituye a docenas de copias, lo que reduce la complejidad del archivo.
• Mayor legibilidad: es más fácil revisar una única fórmula de origen que buscar entre 500 copias idénticas.
• Menos errores: se acabaron las referencias rotas o las fórmulas ligeramente diferentes por copiarlas manualmente.
• Combinabilidad: las funciones se encadenan de forma natural: =SORT(UNIQUE(FILTER(A2:A100, B2:B100="Q1"))).

Consideraciones prácticas

• Diseño: deja suficiente espacio libre debajo y a la derecha de la fórmula para el rango de extensión.
• Compatibilidad: estas funciones requieren Excel 365, Excel 2021 o ONLYOFFICE Docs. Las versiones anteriores de Excel no las admiten.
• Rangos grandes: aplicar UNIQUE o FILTER a columnas completas puede ralentizar el recálculo; limita el rango de entrada a las filas de datos reales.

Ejemplo de matrices dinámicas y combinaciones

Una vez que te hayas familiarizado con las funciones individuales, el verdadero potencial de las matrices dinámicas reside en encadenarlas entre sí. Este es un caso práctico que va más allá de los conceptos básicos.

Cuadro de puntuación de rendimiento con RANDARRAY y RANK

Situación: quieres simular las puntuaciones de rendimiento de un equipo y asignar automáticamente a cada persona una posición en la clasificación.

Introduce los nombres de los empleados en A2:A6.

En B2, genera puntuaciones aleatorias entre 50 y 100:

=RANDARRAY(5; 1; 50; 100; 1)

Cuando estés satisfecho con las puntuaciones, pégalas como valores (Ctrl+Mayús+V → «Solo valores») para evitar que se recalculen mientras trabajas.

En C2, asigna a cada puntuación una posición en la clasificación:

=RANK(B2; $B$2:$B$6; 0)

Arrastra hacia abajo hasta C6. El símbolo $ bloquea el rango de referencia para que cada puntuación se compare con las cinco; sin él, cada fila se clasificaría solo consigo misma.

El resultado es una tabla de puntuación de tres columnas en la que se muestra cada empleado, su puntuación y su posición en la clasificación del equipo, y que se actualiza automáticamente cada vez que cambian las puntuaciones. Sin necesidad de ordenar manualmente ni de columnas auxiliares.

Casos de uso: ¿Quién se beneficia más?

Los analistas de datos utilizan FILTER, UNIQUE y SORT para crear resúmenes que se actualizan automáticamente sin necesidad de columnas auxiliares ni tablas dinámicas.

Los equipos financieros elaboran informes que se actualizan automáticamente a medida que se añaden nuevas transacciones, y utilizan SEQUENCE para las estructuras de los periodos fiscales.

Los gestores de proyectos crean herramientas de seguimiento de tareas que muestran dinámicamente los proyectos activos, los responsables únicos o las tareas vencidas, sin necesidad de macros.

Cuándo recurrir a las matrices dinámicas:

• Informes y paneles que necesitan datos en tiempo real sin actualizaciones manuales
• Listas de valores únicos para menús desplegables o resúmenes
• Vistas clasificadas o filtradas que no deben alterar los datos de origen
• Búsquedas en varias columnas con XLOOKUP

Obtén ONLYOFFICE y empieza a utilizar matrices dinámicas hoy mismo

Las matrices dinámicas son una de las mejoras más prácticas en el trabajo moderno con hojas de cálculo. Una sola fórmula sustituye a docenas, los resultados se actualizan automáticamente y tus archivos se mantienen ordenados y auditables.

Empieza poco a poco: cambia una columna de VLOOKUP por XLOOKUP, utiliza UNIQUE para tu próximo menú desplegable o crea un resumen con FILTER en lugar de una tabla estática de copiar y pegar. El cambio en tu forma de pensar sobre las hojas de cálculo se produce rápidamente.

Prueba la experiencia completa de las matrices dinámicas creando una cuenta gratuita de ONLYOFFICE DocSpace o sin conexión descargando la aplicación de escritorio gratuita para tu PC o portátil con Windows, Linux o macOS:

EMPEZAR EN LÍNEA   APP DE ESCRITORIO