Descarga el libro de trabajo de ejemplo
Este tutorial le dará una introducción a las fórmulas de matriz dinámica en Excel y Google Sheets.
Introducción
En septiembre de 2022, Microsoft introdujo las fórmulas de matriz dinámica en Excel. Su propósito es facilitar la redacción de fórmulas complejas y con menos posibilidades de error.
Las fórmulas de matriz dinámica están destinadas a reemplazar eventualmente las fórmulas de matriz, es decir, fórmulas avanzadas que requieren el uso de Ctrl + Shift + Enter (CSE).
A continuación, se muestra una comparación rápida entre la fórmula de matriz y la fórmula de matriz dinámica que se utilizan para extraer una lista de departamentos únicos de nuestra lista en el rango. A2: A7.
Fórmula de matriz heredada (CSE):
La siguiente fórmula se ingresa en la celda D2 y se ingresa presionando Ctrl + Shift + Enter y copiándolo desde D2 para D5.
| 1 | {= SIERROR (ÍNDICE ($ A $ 2: $ A $ 7, COINCIDIR (0, CONTAR.SI ($ D $ 1: D1, $ A $ 2: $ A $ 7), 0)), "")} |
Fórmula de matriz dinámica:
La siguiente fórmula solo se ingresa en la celda D2 y entró presionando Enter. De un vistazo rápido, puede ver lo fácil y directo que es escribir una fórmula de matriz dinámica.
| 1 | = ÚNICO (A2: A7) |
Disponibilidad
A partir de agosto de 2022, las fórmulas de matriz dinámica solo están disponibles para los usuarios de Office 365.
Alcance de derrames y derrames
Las fórmulas de matriz dinámica funcionan devolviendo varios resultados a un rango de celdas en función de una única fórmula ingresada en una celda.
Este comportamiento se conoce como "Derramar" y el rango de celdas donde se colocan los resultados se llama "Rango de derrame". Cuando selecciona cualquier celda dentro del rango de derrame, Excel la resalta con un borde azul delgado.
En el siguiente ejemplo, la fórmula de matriz dinámica CLASIFICAR está en la celda D2 y los resultados se han derramado en el rango D2: D7
| 1 | = CLASIFICAR (A2: A7) |
Los resultados de la fórmula son dinámicos, lo que significa que si se produce un cambio en el rango de origen, los resultados también cambian y el rango de derrame cambia de tamaño.
#¡DERRAMAR!
Debe tener en cuenta que si su rango de derrame no está completamente en blanco, se devuelve un error #SPILL.
Cuando selecciona el error #SPILL, el rango de derrame deseado de la fórmula se resalta con un borde azul discontinuo. Mover o eliminar los datos en la celda que no está en blanco elimina este error y permite que la fórmula se derrame.
Notación de referencia de derrame
Para hacer referencia al rango de derrame de una fórmula, colocamos el # símbolo después de la referencia de celda de la primera celda en el derrame.
También puede hacer referencia al derrame seleccionando todas las celdas en el rango del derrame y se creará automáticamente una referencia al derrame.
En el siguiente ejemplo, nos gustaría contar el número de empleados en nuestra empresa usando la fórmula COUNTA después de que se hayan ordenado alfabéticamente utilizando la fórmula de matriz dinámica CLASIFICAR.
Entramos en el CLASIFICAR fórmula en D2 para ordenar los empleados en nuestra lista:
| 1 | = CLASIFICAR (A2: A7) |
Luego entramos en el COUNTA fórmula en G2 para contar el número de empleados:
| 1 | = CONTAR (D2 #) |
Tenga en cuenta el uso de # en D2 # para hacer referencia a los resultados vertidos por SORT en el rango D2: D7.
Nuevas fórmulas
A continuación se muestra la lista completa de las nuevas fórmulas de matriz dinámica:
- ÚNICO - Devuelve una lista de valores únicos de un rango.
- CLASIFICAR - Ordena los valores en un rango
- ORDENAR POR - Ordena los valores en función de un rango correspondiente
- FILTRAR - Filtra un rango según los criterios proporcionados
- RANDARRAY - Devuelve una matriz de números aleatorios entre 0 y 1
- SECUENCIA - Genera una lista de números secuenciales como 1, 2, 3, 4, 5
Fórmula de matriz dinámicas en Hojas de cálculo de Google
Todos los ejemplos anteriores funcionan exactamente igual en Google Sheets que en Excel.
