Contenido
¿Qué son las matrices dinámicas?
En septiembre de 2018, Microsoft lanzó una actualización que agrega una herramienta completamente nueva a Microsoft Excel: Dynamic Arrays y 7 nuevas funciones para trabajar con ellos. Estas cosas, sin exagerar, cambian radicalmente toda la técnica habitual de trabajar con fórmulas y funciones y preocupan, literalmente, a todos los usuarios.
Considere un ejemplo simple para explicar la esencia.
Supongamos que tenemos una tabla simple con datos sobre ciudades-meses. ¿Qué sucederá si seleccionamos cualquier celda vacía a la derecha de la hoja e ingresamos en ella una fórmula que se vincule no a una celda, sino inmediatamente a un rango?
En todas las versiones anteriores de Excel, después de hacer clic en Participar obtendríamos el contenido de una sola primera celda B2. ¿De que otra forma?
Bueno, o sería posible envolver este rango en algún tipo de función de agregación como =SUMA(B2:C4) y obtener un total general.
Si necesitáramos operaciones más complejas que una suma primitiva, como extraer valores únicos o Top 3, entonces tendríamos que ingresar nuestra fórmula como una fórmula de matriz usando un atajo de teclado Ctrl+Shift+Participar.
Ahora todo es diferente.
Ahora, después de ingresar dicha fórmula, simplemente podemos hacer clic en Participar – y obtener como resultado inmediatamente todos los valores uXNUMXbuXNUMXb a los que nos referimos:
Esto no es magia, sino los nuevos arreglos dinámicos que ahora tiene Microsoft Excel. Bienvenidos al nuevo mundo 🙂
Características de trabajar con arreglos dinámicos
Técnicamente, toda nuestra matriz dinámica se almacena en la primera celda G4, llenando el número requerido de celdas hacia la derecha y hacia abajo con sus datos. Si selecciona cualquier otra celda en la matriz, el enlace en la barra de fórmulas estará inactivo, mostrando que estamos en una de las celdas "secundarias":
Un intento de eliminar una o más celdas "secundarias" no conducirá a nada: Excel las volverá a calcular y las llenará de inmediato.
Al mismo tiempo, podemos referirnos con seguridad a estas celdas "secundarias" en otras fórmulas:
Si copia la primera celda de una matriz (por ejemplo, de G4 a F8), toda la matriz (sus referencias) se moverá en la misma dirección que en las fórmulas regulares:
Si necesitamos mover la matriz, entonces será suficiente mover (con el mouse o una combinación de Ctrl+X, Ctrl+V), nuevamente, solo la primera celda principal G4; después de eso, se transferirá a un nuevo lugar y toda nuestra matriz se expandirá nuevamente.
Si necesita hacer referencia en otro lugar de la hoja a la matriz dinámica creada, puede usar el carácter especial # ("almohadilla") después de la dirección de su celda principal:
Por ejemplo, ahora puede hacer fácilmente una lista desplegable en una celda que se refiera a la matriz dinámica creada:
Errores de matriz dinámica
Pero, ¿qué sucede si no hay suficiente espacio para expandir la matriz o si hay celdas ya ocupadas por otros datos en su camino? Conozca un tipo de error fundamentalmente nuevo en Excel: #¡TRANSFERIR! (#¡DERRAMAR!):
Como siempre, si hacemos clic en el icono con un rombo amarillo y un signo de exclamación, obtendremos una explicación más detallada del origen del problema y podremos encontrar rápidamente las celdas que interfieren:
Se producirán errores similares si la matriz se sale de la hoja o toca una celda combinada. Si elimina el obstáculo, todo se corregirá inmediatamente sobre la marcha.
Matrices dinámicas y tablas inteligentes
Si la matriz dinámica apunta a una tabla "inteligente" creada por un atajo de teclado Ctrl+T o por Inicio – Formato como tabla (Inicio — Formatear como tabla), entonces también heredará su principal cualidad: el tamaño automático.
Al agregar nuevos datos en la parte inferior o a la derecha, la tabla inteligente y el rango dinámico también se expandirán automáticamente:
Sin embargo, hay una limitación: no podemos usar una referencia de rango dinámico en fórmulas dentro de una tabla inteligente:
Matrices dinámicas y otras características de Excel
Está bien, dices. Todo esto es interesante y divertido. No es necesario, como antes, estirar manualmente la fórmula con una referencia a la primera celda del rango original hacia abajo y hacia la derecha y todo eso. ¿Y eso es todo?
No del todo.
Las matrices dinámicas no son una herramienta más en Excel. Ahora están integrados en el corazón (o cerebro) de Microsoft Excel: su motor de cálculo. Esto significa que otras fórmulas y funciones de Excel que nos son familiares ahora también admiten trabajar con matrices dinámicas. Echemos un vistazo a algunos ejemplos para darle una idea de la profundidad de los cambios que se han producido.
Transponer
Para transponer un rango (intercambiar filas y columnas), Microsoft Excel siempre ha tenido una función incorporada TRANSPORTE (TRANSPONER). Sin embargo, para usarlo, primero debe seleccionar correctamente el rango de los resultados (por ejemplo, si la entrada fue un rango de 5×3, entonces debe haber seleccionado 3×5), luego ingrese la función y presione el botón combinación Ctrl+Shift+Participar, porque solo podría funcionar en el modo de fórmula de matriz.
Ahora solo puede seleccionar una celda, ingresar la misma fórmula y hacer clic en la normal Participar – matriz dinámica hará todo por sí mismo:
Tabla de multiplicación
Este es el ejemplo que solía dar cuando me pedían que visualizara los beneficios de las fórmulas de matriz en Excel. Ahora, para calcular toda la tabla de Pitágoras, es suficiente pararse en la primera celda B2, ingresar allí una fórmula que multiplique dos matrices (conjunto vertical y horizontal de números 1..10) y simplemente hacer clic en Participar:
Encolado y conversión de cajas
Las matrices no solo se pueden multiplicar, sino que también se pueden unir con el operador estándar & (ampersand). Supongamos que necesitamos extraer el nombre y el apellido de dos columnas y corregir el caso de salto en los datos originales. Hacemos esto con una fórmula corta que forma la matriz completa, y luego le aplicamos la función PROPNACIÓN (CORRECTO)para ordenar el registro:
Conclusión Top 3
Supongamos que tenemos un montón de números de los que queremos derivar los tres primeros resultados, organizándolos en orden descendente. Ahora bien, esto se hace mediante una fórmula y, de nuevo, sin ninguna Ctrl+Shift+Participar como antes:
Si desea que los resultados no se coloquen en una columna, sino en una fila, basta con reemplazar los dos puntos (separador de línea) en esta fórmula con un punto y coma (separador de elementos dentro de una línea). En la versión en inglés de Excel, estos separadores son puntos y comas y comas, respectivamente.
BUSCARV extrayendo varias columnas a la vez
Clave VPR (VLOOKUP) ahora puede extraer valores no de una, sino de varias columnas a la vez; solo especifique sus números (en el orden que desee) como una matriz en el tercer argumento de la función:
Función OFFSET que devuelve una matriz dinámica
Una de las funciones más interesantes y útiles (después de BUSCARV) para el análisis de datos es la función ELIMINACIÓN (COMPENSAR), a la que dediqué en un momento un capítulo entero de mi libro y un artículo aquí. La dificultad para comprender y dominar esta función siempre ha sido que devolvía un arreglo (rango) de datos como resultado, pero no podíamos verlo, porque Excel aún no sabía cómo trabajar con arreglos listos para usar.
Ahora este problema está en el pasado. Vea cómo ahora, utilizando una sola fórmula y una matriz dinámica devuelta por OFFSET, puede extraer todas las filas de un producto determinado de cualquier tabla ordenada:
Echemos un vistazo a sus argumentos:
- A1 – celda inicial (punto de referencia)
- ПОИСКПОЗ(F2;A2:A30;0) – cálculo del cambio desde la celda inicial hacia abajo – hasta el primer repollo encontrado.
- 0 – desplazamiento de la “ventana” a la derecha con respecto a la celda inicial
- СЧЁТЕСЛИ(A2:A30;F2) – cálculo de la altura de la “ventana” devuelta – el número de líneas donde hay repollo.
- 4 — tamaño de la “ventana” horizontalmente, es decir, salida de 4 columnas
Nuevas funciones para arreglos dinámicos
Además de admitir el mecanismo de matrices dinámicas en funciones antiguas, se han agregado varias funciones completamente nuevas a Microsoft Excel, perfeccionadas específicamente para trabajar con matrices dinámicas. En particular, estos son:
- GRADO (CLASIFICAR) – ordena el rango de entrada y produce una matriz dinámica en la salida
- SORTPO (ORDENAR POR) – puede ordenar un rango por valores de otro
- FILTRO (FILTRAR) – recupera filas del rango de origen que cumplen las condiciones especificadas
- ÚNICO (ÚNICO) – extrae valores únicos de un rango o elimina duplicados
- SLMASIVO (RANDARRAY) – genera una matriz de números aleatorios de un tamaño dado
- PLACENTA (SECUENCIA) — forma una matriz a partir de una secuencia de números con un paso dado
Más sobre ellos, un poco más tarde. Valen un artículo aparte (y no uno) para un estudio reflexivo 🙂
Conclusiones
Si ha leído todo lo escrito anteriormente, creo que ya se da cuenta de la magnitud de los cambios que han tenido lugar. Muchas cosas en Excel ahora se pueden hacer de manera más fácil, más fácil y más lógica. Debo admitir que estoy un poco sorprendido por la cantidad de artículos que ahora tendrán que ser corregidos aquí, en este sitio y en mis libros, pero estoy listo para hacerlo con un corazón ligero.
Resumiendo los resultados, плюсы matrices dinámicas, puede escribir lo siguiente:
- Puedes olvidarte de la combinación. Ctrl+Shift+Participar. Excel ahora no ve ninguna diferencia entre las "fórmulas regulares" y las "fórmulas de matriz" y las trata de la misma manera.
- Sobre la función SUMPRODUCT (SUMAPRODUCTO), que anteriormente se usaba para ingresar fórmulas de matriz sin Ctrl+Shift+Participar también puedes olvidar - ahora es bastante fácil SUM и Participar.
- Las tablas inteligentes y las funciones familiares (SUM, IF, VLOOKUP, SUMIFS, etc.) ahora también admiten total o parcialmente las matrices dinámicas.
- Hay compatibilidad con versiones anteriores: si abre un libro de trabajo con matrices dinámicas en una versión anterior de Excel, se convertirán en fórmulas de matriz (entre llaves) y seguirán funcionando con el "estilo antiguo".
Encontré algún número menos:
- No puede eliminar filas, columnas o celdas individuales de una matriz dinámica, es decir, vive como una sola entidad.
- No puede ordenar una matriz dinámica de la manera habitual a través de Datos – Clasificación (Datos — Ordenar). Ahora hay una función especial para esto. GRADO (CLASIFICAR).
- Un rango dinámico no se puede convertir en una tabla inteligente (pero puede crear un rango dinámico basado en una tabla inteligente).
Por supuesto, este no es el final y estoy seguro de que Microsoft seguirá mejorando este mecanismo en el futuro.
¿Dónde puedo descargar?
Y finalmente, la pregunta principal 🙂
Microsoft anunció y mostró por primera vez una vista previa de matrices dinámicas en Excel en septiembre de 2018 en una conferencia Encender. En los meses siguientes, se realizaron pruebas exhaustivas y se ejecutaron nuevas funciones, primero en gatos empleados de la propia Microsoft, y luego en evaluadores voluntarios del círculo de Office Insiders. Este año, la actualización que agrega matrices dinámicas comenzó a implementarse gradualmente para los suscriptores habituales de Office 365. Por ejemplo, solo lo recibí en agosto con mi suscripción a Office 365 Pro Plus (con orientación mensual).
Si su Excel aún no tiene matrices dinámicas, pero realmente desea trabajar con ellas, existen las siguientes opciones:
- Si tiene una suscripción a Office 365, simplemente puede esperar hasta que le llegue esta actualización. La rapidez con que esto suceda depende de la frecuencia con la que se envíen las actualizaciones a su Office (una vez al año, una vez cada seis meses, una vez al mes). Si tiene una PC corporativa, puede pedirle a su administrador que configure las actualizaciones para que se descarguen con más frecuencia.
- Puede unirse a las filas de los voluntarios de prueba de Office Insiders; entonces será el primero en recibir todas las características y funciones nuevas (pero existe la posibilidad de que haya más errores en Excel, por supuesto).
- Si no tiene una suscripción, sino una versión independiente en caja de Excel, tendrá que esperar hasta el lanzamiento de la próxima versión de Office y Excel en 2022, al menos. Los usuarios de dichas versiones solo reciben actualizaciones de seguridad y correcciones de errores, y todos los nuevos "beneficios" ahora van solo para los suscriptores de Office 365. Triste pero cierto 🙂
En cualquier caso, cuando aparezcan matrices dinámicas en su Excel, después de este artículo, estará listo para ello 🙂
- ¿Qué son las fórmulas de matriz y cómo usarlas en Excel?
- Suma de ventana (rango) utilizando la función OFFSET
- 3 formas de transponer una tabla en Excel