¡Bienvenidos al fascinante mundo de las matrices dinámicas en Excel! Si alguna vez has sentido que tus hojas de cálculo podrían hacer más, estás en el lugar correcto. En este artículo, vamos a desentrañar las funciones y fórmulas que transforman Excel en una poderosa herramienta para el análisis de datos. Desde la gestión eficiente de grandes volúmenes de información hasta la creación de informes interactivos que deslumbran a tu audiencia, las matrices dinámicas son la clave para llevar tus habilidades a un nivel superior. Prepárate para descubrir cómo utilizar estas funciones de manera efectiva y revolucionar tu forma de trabajar con datos. ¡Comencemos este viaje hacia la excelencia en Excel!
¿Sabías que Excel es mucho más que una simple herramienta de hojas de cálculo? En este artículo descubrirás cómo las matrices dinámicas, funciones y fórmulas en Excel pueden potenciar tu productividad y simplificar tu trabajo. Ya sea que necesites realizar análisis de datos, crear tablas dinámicas o automatizar tareas repetitivas, Excel tiene todas las herramientas que necesitas. Acompáñanos en este recorrido por las maravillas de las matrices dinámicas, funciones y fórmulas de Excel y descubre cómo puedes utilizarlas para optimizar tu trabajo diario. ¡Prepárate para darle un impulso a tus habilidades y lleva tus hojas de cálculo al siguiente nivel con Excel!
Gracias a la revolucionaria actualización del motor de cálculo de Excel 365, las fórmulas matriciales se vuelven muy sencillas y comprensibles para todos, no solo para los superusuarios. El tutorial explica el concepto de las nuevas matrices dinámicas de Excel y muestra cómo pueden hacer que sus hojas de cálculo sean más eficientes y mucho más fáciles de configurar.
Las fórmulas matriciales de Excel siempre se han considerado una prerrogativa de gurús y expertos en fórmulas. Si alguien dice «Esto se puede hacer con una fórmula matricial», la reacción inmediata de muchos usuarios es «Oh, ¿no hay otra manera?».
La introducción de matrices dinámicas es un cambio muy esperado y muy bienvenido. Debido a su capacidad para trabajar con múltiples valores de una manera sencilla, sin trucos ni peculiaridades, las fórmulas de matriz dinámica son algo que todo usuario de Excel puede entender y disfrutar creando.
matrices dinámicas de excel
Matrices dinámicas son matrices de tamaño variable que se calculan automáticamente y devuelven valores en varias celdas según una fórmula ingresada en una sola celda.
A lo largo de más de 30 años de historia, Microsoft Excel ha sufrido muchos cambios, pero una cosa permanece constante: una fórmula, una celda. Incluso con las fórmulas de matriz tradicionales, era necesario ingresar una fórmula en cada celda donde deseaba que apareciera un resultado. Con las matrices dinámicas, esta regla ya no es cierta. Ahora, cualquier fórmula que devuelva una matriz de valores se extiende automáticamente a las celdas vecinas, sin que tengas que presionar Ctrl + Mayús + Intro o hacer cualquier otro movimiento. En otras palabras, operar matrices dinámicas se vuelve tan fácil como trabajar con una sola celda.
Permítanme ilustrar el concepto con un ejemplo muy básico. Supongamos que necesitas multiplicar dos grupos de números, por ejemplo, para calcular diferentes porcentajes.
En las versiones predinámicas de Excel, la siguiente fórmula funcionaría solo para la primera celda, a menos que la ingrese en varias celdas y presione Ctrl + Mayús + Intro para convertirlo explícitamente en una fórmula matricial:
=A3:A5*B2:D2
>Ahora, mira lo que sucede cuando se usa la misma fórmula en Excel 365. La escribes en una sola celda (B3 en nuestro caso), presionas la tecla Enter… y tienes todo el furor lleno con los resultados de una vez:
>Llenar varias celdas con una sola fórmula se llama derramandoy el rango poblado de celdas se llama rango de derrame.
Una cosa importante a tener en cuenta es que la actualización reciente no es sólo una nueva forma de manejar matrices en Excel. De hecho, se trata de un cambio revolucionario en todo el motor de cálculo. Con matrices dinámicas, un montón de nuevas funciones Se agregaron a la biblioteca de funciones de Excel y las existentes comenzaron a funcionar de manera más rápida y efectiva. Con el tiempo, se supone que las nuevas matrices dinámicas reemplazarán por completo las fórmulas de matriz anticuadas que se ingresan con el Ctrl + Mayús + Intro atajo.
Disponibilidad de matrices dinámicas de Excel
Las matrices dinámicas se presentaron en la Conferencia Microsoft Ignite en 2018 y se lanzaron a los suscriptores de Office 365 en enero de 2020. Actualmente, están disponibles en Suscripciones a Microsoft 365 y Excel 2021.
Las matrices dinámicas son compatibles con estas versiones:
- Excel 365 para Windows
- Excel 365 para Mac
- excel 2021
- Excel 2021 para Mac
- Excel para iPad
- Excel para iPhone
- Excel para tabletas Android
- Excel para teléfonos Android
- Excel para la web
Funciones de matriz dinámica de Excel
Como parte de la nueva funcionalidad, se introdujeron 6 nuevas funciones en Excel 365 que manejan matrices de forma nativa y generan datos en un rango de celdas. La salida es siempre dinámica: cuando se produce algún cambio en los datos de origen, los resultados se actualizan automáticamente. De ahí el nombre del grupo: funciones de matriz dinámica.
Estas nuevas funciones hacen frente fácilmente a una serie de tareas que tradicionalmente se consideran difíciles de resolver. Por ejemplo, pueden eliminar duplicados, extraer y contar valores únicos, filtrar espacios en blanco, generar números enteros y decimales aleatorios, ordenar en orden ascendente o descendente y mucho más.
A continuación encontrará una breve descripción de lo que hace cada función, así como enlaces a tutoriales detallados:
Además, hay dos reemplazos modernos de las populares funciones de Excel, que no están oficialmente en el grupo, pero aprovechan todas las ventajas de las matrices dinámicas:
XLOOKUP: es un sucesor más potente de BUSCARV, BUSCARH y BUSCAR que puede buscar tanto en columnas como en filas y devolver múltiples valores.
XMATCH: es un sucesor más versátil de la función MATCH que puede realizar búsquedas verticales y horizontales y devolver una posición relativa del elemento especificado.
Fórmulas de matriz dinámica de Excel
En las versiones modernas de Excel, el comportamiento de la matriz dinámica está profundamente integrado y se vuelve nativo para todas las funciones, incluso aquellos que no fueron diseñados originalmente para funcionar con matrices. En pocas palabras, para cualquier fórmula que devuelva más de un valor, Excel crea automáticamente un rango de tamaño variable en el que se generan los resultados. ¡Debido a esta habilidad, las funciones existentes ahora pueden realizar magia!
Los siguientes ejemplos muestran nuevas fórmulas de matrices dinámicas en acción, así como el efecto de las matrices dinámicas en las funciones existentes.
Ejemplo 1. Nueva función de matriz dinámica
Este ejemplo demuestra cuán rápida y sencilla se puede lograr una solución con funciones de matriz dinámica de Excel.
Para extraer una lista de valores únicos de una columna, tradicionalmente se utiliza una fórmula CSE compleja como esta. En Excel dinámico, todo lo que necesita es una fórmula ÚNICA en su forma básica:
=UNIQUE(B2:B10)
Ingresas la fórmula en cualquier celda vacía y presionas Enter. Excel extrae inmediatamente todos los valores diferentes de la lista y los genera en un rango de celdas comenzando desde la celda donde ingresó la fórmula (D2 en nuestro caso). Cuando los datos de origen cambian, los resultados se recalculan y actualizan automáticamente.
>Ejemplo 2. Combinar varias funciones de matriz dinámica en una fórmula
Si no hay forma de realizar una tarea con una función, ¡encadene algunas! Por ejemplo, para filtrar datos según la condición y organizar los resultados alfabéticamente, ajuste la función ORDENAR alrededor de FILTRO de esta manera:
=SORT(FILTER(A2:C13, B2:B13=F1, «No results»))
Donde A2:C13 son los datos de origen, B2:B13 son los valores a verificar y F1 es el criterio.
>Ejemplo 3. Uso de nuevas funciones de matriz dinámica junto con las existentes
Como el nuevo motor de cálculo implementado en Excel 365 puede convertir fácilmente fórmulas convencionales en matrices, no hay nada que le impida combinar funciones nuevas y antiguas.
Por ejemplo, para contar cuántos valores únicos hay en un cierto rango, anide la función ÚNICA de matriz dinámica en el viejo CONTAR:
=COUNTA(UNIQUE(B2:B10))
>Ejemplo 4. Las funciones existentes admiten matrices dinámicas
Si proporciona un rango de celdas a la función RECORTAR en una versión anterior como Excel 2016 o Excel 2019, devolverá un único resultado para la primera celda:
=TRIM(A2:A6)
En Excel dinámico, la misma fórmula procesa todas las celdas y devuelve múltiples resultados, como se muestra a continuación:
>Ejemplo 5. Fórmula BUSCARV para devolver múltiples valores
Como todo el mundo sabe, la función BUSCARV está diseñada para devolver un valor único según el índice de columna que especifique. Sin embargo, en Excel 365, puede proporcionar una serie de números de columna para devolver coincidencias de varias columnas:
=VLOOKUP(F1, A2:C6, {1,2,3}, FALSE)
>Ejemplo 6. Fórmula TRANSPONER simplificada
En versiones anteriores de Excel, la sintaxis de la función TRANSPONER no dejaba lugar a errores. Para rotar datos en su hoja de trabajo, necesitaba contar las columnas y filas originales, seleccionar la misma cantidad de celdas vacías pero cambiar la orientación (¡una operación alucinante en hojas de trabajo enormes!), escribir una fórmula TRANSPONER en el rango seleccionado y prensa Ctrl + Mayús + Intro para completarlo correctamente. ¡Uf!
En Excel dinámico, simplemente ingresa la fórmula en la celda más a la izquierda del rango de salida y presiona Enter:
=TRANSPOSE(A1:B6)
¡Hecho!
>Rango de derrame: una fórmula, varias celdas
El rango de derrame es un rango de celdas que contiene los valores devueltos por una fórmula de matriz dinámica.
Cuando se selecciona cualquier celda en el rango de derrame, el borde azul aparece para mostrar que todo lo que contiene se calcula mediante la fórmula de la celda superior izquierda. Si elimina la fórmula en la primera celda, todos los resultados desaparecerán.
>El rango de derrames es algo realmente fantástico que facilita mucho la vida de los usuarios de Excel. Anteriormente, con las fórmulas matriciales CSE, teníamos que adivinar en cuántas celdas copiarlas. Ahora, simplemente ingresa la fórmula en la primera celda y deja que Excel se encargue del resto.
Nota. Si algún otro dato está bloqueando el rango de derrame, un #ErrorDERRAME ocurre. Una vez que se eliminen los datos obstructivos, el error desaparecerá.
Para obtener más información, consulte Rango de derrames de Excel.
Referencia del rango de derrame (símbolo #)
Para hacer referencia a un rango de derrame completo devuelto por una fórmula de matriz dinámica, coloque una etiqueta hash o un símbolo de almohadilla (#) después de la dirección de la celda superior izquierda del rango.
Por ejemplo, para encontrar cuántos números aleatorios genera la fórmula RANDARRAY en A2, proporcione la referencia del rango de derrame a la función CONTARA:
=COUNTA(A2#)
Para sumar los valores en el rango de derrame, use:
=SUM(A2#)
>
Consejos:
- Para hacer referencia rápidamente a un rango de derrame, simplemente seleccione todas las celdas dentro del cuadro azul con el mouse y Excel creará la referencia de derrame por usted.
- A diferencia de una referencia de rango normal, la referencia de rango de derrame es dinámica y reacciona automáticamente al cambio de tamaño del rango.
Para obtener más detalles, consulte Operador de rango de derrames.
Intersección implícita y carácter @
En Excel de matriz dinámica, hay un cambio más significativo en el lenguaje de fórmulas: la introducción del carácter @, conocido como operador de intersección implícito.
En Microsoft Excel, intersección implícita es un comportamiento de fórmula que reduce muchos valores a un solo valor. En el antiguo Excel, una celda solo podía contener un único valor, por lo que ese era el comportamiento predeterminado y no se necesitaba ningún operador especial.
En el nuevo Excel, todas las fórmulas se consideran fórmulas matriciales de forma predeterminada. El operador de intersección implícito se utiliza para evitar el comportamiento de la matriz si no lo desea en una fórmula específica. En otras palabras, si desea que la fórmula devuelva solo un valor, coloque @ antes del nombre de la función y se comportará como una fórmula sin matriz en Excel tradicional.
Para ver cómo funciona en la práctica, mire la captura de pantalla a continuación.
En C2, hay una fórmula de matriz dinámica que distribuye los resultados en muchas celdas:
=UNIQUE(A2:A9)
En E2, la función tiene el prefijo @ que invoca una intersección implícita. Como resultado, sólo se devuelve el primer valor único:
=@UNIQUE(A2:A9)
>Para obtener más información, consulte Intersección implícita en Excel.
Ventajas de las matrices dinámicas de Excel
Sin duda, las matrices dinámicas son una de las mejores mejoras de Excel en años. Como cualquier característica nueva, tienen puntos fuertes y débiles. ¡Afortunadamente para nosotros, los puntos fuertes de las nuevas fórmulas de matrices dinámicas de Excel son abrumadores!
Sencillo y más potente
Los arrays dinámicos permiten crear fórmulas más potentes de una forma mucho más sencilla. Aquí hay un par de ejemplos:
Nativo para todas las fórmulas
En Excel dinámico, no necesita preocuparse por qué funciones admiten matrices y cuáles no. Si una fórmula puede devolver varios valores, lo hará de forma predeterminada. Esto también se aplica a operaciones aritméticas y funciones heredadas como se demuestra en este ejemplo.
Funciones de matriz dinámica anidadas
Para encontrar soluciones para tareas más complejas, puede combinar nuevas funciones de matriz dinámica de Excel o usarlas junto con las antiguas, como se muestra. aquí y aquí.
Las referencias relativas y absolutas son menos importantes.
Gracias al enfoque de «una fórmula, muchos valores», no es necesario bloquear rangos con el signo $ ya que, técnicamente, la fórmula está en una sola celda. Entonces, en su mayor parte, realmente no importa si usar referencias de celda absolutas, relativas o mixtas (lo que siempre ha sido una fuente de confusión para los usuarios inexpertos): ¡una fórmula de matriz dinámica producirá resultados correctos de todos modos!
Limitaciones de las matrices dinámicas
Las nuevas matrices dinámicas son geniales, pero como ocurre con cualquier característica nueva, hay algunas advertencias y consideraciones que debes tener en cuenta.
Los resultados no se pueden ordenar de la forma habitual.
El rango de derrame devuelto por una fórmula de matriz dinámica no se puede ordenar mediante la función Ordenar de Excel. Cualquier intento de este tipo resultará en el «No puedes cambiar parte de una matriz.«error. Para organizar los resultados de menor a mayor o viceversa, ajuste su fórmula actual en la función ORDENAR. Por ejemplo, así es como puede filtrar y ordenar a la vez.
No se puede eliminar ningún valor en el rango de derrame
Ninguno de los valores de un rango de derrame se puede eliminar por el mismo motivo: no se puede cambiar parte de una matriz. Este comportamiento es esperado y lógico. Las fórmulas matrices CSE tradicionales también funcionan de esta manera.
No son compatibles con tablas de Excel.
Esta característica (¿o error?) es bastante inesperada. Las fórmulas de matriz dinámica no funcionan desde tablas de Excel, solo dentro de rangos regulares. Si intenta convertir un rango de derrame en una tabla, Excel lo hará. ¡Pero en lugar de los resultados, solo verás un #DERRAME! error.
No trabajar con Excel Power Query
Los resultados de las fórmulas de matriz dinámica no se pueden cargar en Power Query. Digamos que si intenta combinar dos o más rangos de derrames mediante Power Query, esto no funcionará.
Matrices dinámicas frente a fórmulas de matrices CSE tradicionales
Con la introducción de las matrices dinámicas, podemos hablar de dos tipos de Excel:
No hace falta decir que las matrices dinámicas son superiores a las fórmulas de matrices CSE en todos los aspectos. Aunque las fórmulas matrices tradicionales se conservan por motivos de compatibilidad, a partir de ahora se recomienda utilizar las nuevas.
Estas son las diferencias más esenciales:
- Se ingresa una fórmula de matriz dinámica en una celda y se completa con una pulsación normal de la tecla Intro. Para completar una fórmula matricial antigua, debe presionar Ctrl + Mayús + Intro.
- Las nuevas fórmulas matriciales se extienden automáticamente a muchas celdas. Las fórmulas CSE se deben copiar en un rango de celdas para devolver múltiples resultados.
- La salida de las fórmulas de matriz dinámica cambia de tamaño automáticamente a medida que cambian los datos en el rango de origen. Las fórmulas CSE truncan la salida si el área de retorno es demasiado pequeña y devuelven errores en celdas adicionales si el área de retorno es demasiado grande.
- Una fórmula de matriz dinámica se puede editar fácilmente en una sola celda. Para modificar una fórmula CSE, debe seleccionar y editar todo el rango.
- No es posible eliminar ni insertar filas en un rango de fórmulas CSE; primero debe eliminar todas las fórmulas existentes. Con las matrices dinámicas, la inserción o eliminación de filas no es un problema.
Compatibilidad con versiones anteriores: matrices dinámicas en Excel heredado
Cuando abre un libro que contiene una fórmula matricial dinámica en el antiguo Excel, se convierte automáticamente en una fórmula matricial convencional encerrada entre {llaves}. Cuando abra la hoja de trabajo nuevamente en el nuevo Excel, se eliminarán las llaves.
En Excel heredado, las nuevas funciones de matriz dinámica y las referencias de rango de desbordamiento tienen el prefijo _xlfn para indicar que esta funcionalidad no es compatible. Un signo de referencia de rango de derrame (#) se reemplaza con la función ANCHORARRAY.
Por ejemplo, así es como aparece una fórmula ÚNICA en excel 2013:
>La mayoría de las fórmulas de matriz dinámica (¡pero no todas!) seguirán mostrando sus resultados en el Excel heredado hasta que les realice algún cambio. La edición de una fórmula la rompe inmediatamente y muestra uno o más #¿NOMBRE? valores de error.
Las fórmulas de matriz dinámica de Excel no funcionan
Dependiendo de la función, pueden ocurrir diferentes errores si utiliza una sintaxis incorrecta o argumentos no válidos. A continuación se detallan los 3 errores más comunes que puede encontrar con cualquier fórmula de matriz dinámica.
#¡DERRAMAR! error
Cuando una matriz dinámica devuelve múltiples resultados, pero algo bloquea el rango de derrame, aparece un mensaje #SPILL! se produce un error.
Para corregir el error, sólo necesita borrar o eliminar cualquier celda en el rango de derrame que no esté completamente en blanco. Para detectar rápidamente todas las celdas que se interponen en su camino, haga clic en el indicador de error y luego haga clic en Seleccionar células obstructoras.
>Aparte de un rango de derrame no vacío, este error puede deberse a otras razones. Para obtener más información, consulte:
#¡ÁRBITRO! error
Debido al soporte limitado para referencias externas entre libros, las matrices dinámicas requieren que ambos archivos estén abiertos. Si el libro de origen está cerrado, aparecerá un mensaje #REF! Se muestra el error.
#¿NOMBRE? error
¿UN NOMBRE? Se produce un error si intenta utilizar una función de matriz dinámica en una versión anterior de Excel. Recuerde que las nuevas funciones solo están disponibles en Excel 365 y Excel 2021.
Si este error aparece en las versiones compatibles de Excel, vuelva a verificar el nombre de la función en la celda problemática. Lo más probable es que esté mal escrito 🙂
Así es como se utilizan matrices dinámicas en Excel. ¡Con suerte, te encantará esta nueva y fantástica funcionalidad! De todos modos, te agradezco por leer y espero verte en nuestro blog la próxima semana.
Contioso: ¡Totalmente de acuerdo, Fredy! Yo también he tenido mis batallas con Excel, pero desde que descubrí las matrices dinámicas, todo se me hace pan comido. Una vez, tenía que analizar un montón de datos para una presentación y estas fórmulas me ayudaron a simplificar todo en un abrir y cerrar de ojos. ¡Maravilloso!
Gines francisco: ¡Exacto, chicos! Estoy con ustedes, las matrices dinámicas son una maravilla. A mí me pasaba lo mismo, pasaba horas tratando de organizar datos, pero desde que me aventuré a usar esas funciones, me siento como un crack. Una vez tuve que hacer un análisis de ventas y, en vez de perderme en mil fórmulas, lo hice en un santiamén. Excel realmente tiene su magia, ¿no?
Fredy javier: ¡Qué buen artículo! Me encanta cómo explican las matrices dinámicas, realmente me han salvado la vida en el trabajo. Recuerdo que una vez hice un informe usando fórmulas complejas y gracias a estas funciones todo fue muchísimo más rápido y fácil. ¡Sigan así!