En la primera parte de esta serie hablamos de los conceptos básicos. Aquí iremos un poco más lejos y avanzaremos en algunos aspectos y comportamientos de las matrices dinámicas sobre los que no se suele hablar tanto, pero que son esenciales.
Resumen de lo visto hasta ahora
En el artículo anterior abordamos y desarrollamos los siguientes puntos:
- Una fórmula matricial es aquella capaz de devolver una serie de resultados en lugar de uno solo.
- Una fórmula de matriz dinámica es aquella que “desborda” los resultados obtenidos sobre todas las celdas adyacentes que necesite, de forma automática.
- Cuando una fórmula de matriz dinámica no puede desbordarse, la celda devuelve el valor de error #¡DESBORDAMIENTO!
- Tres de las funciones de matriz dinámica más usadas son UNICOS, ORDENAR y FILTRAR.
- Las matrices dinámicas surgieron en 2018, en la versión de Microsoft 365. En la primera versión perpetua que aparecieron fue en la 2021.
Aquí te dejo el enlace a dicho artículo: Matrices en Excel [Parte 1]: Qué son las matrices dinámicas, por si no lo has leído.
En esta entrega veremos cómo solucionar algunas sorpresas e interrogantes comunes que suelen presentarnos las matrices dinámicas si no estamos muy preparados a la hora de trabajar con ellas.
Peculiaridades del rango de desbordamiento
No es raro el desconcierto que tienen algunos usuarios de Excel cuando descubren accidentalmente las matrices dinámicas: la mayoría de las celdas no pueden editarse y muestran su contenido atenuado; si sobrescribes alguna, aparece el error #¡DESBORDAMIENTO! en la primera celda y se borran las demás. ¿Qué diablos sucede?
Cuando creamos una fórmula de matriz dinámica, normalmente, se generan dos secciones bien diferenciadas:
- La celda de origen: Es la celda donde se guardó la fórmula y, salvo excepciones, devolverá el primer valor de la matriz resultante.
- El rango de desbordamiento: Es el resto de las celdas ocupadas por la matriz dinámica.
Tanto si seleccionamos la celda de origen como cualquier otra celda del rango de desbordamiento notaremos que Excel envuelve con un borde azul toda el área abarcada por los resultados.
Como la celda de origen es la única que contiene realmente la fórmula, es la única en la que se puede editar.
El rango de desbordamiento solo guarda resultados. Pero para que no interpretemos estos valores como textos comunes, ingresados mediante el teclado, Excel los identifica de manera distintiva. Si te posicionas sobre una celda dentro del rango de desbordamiento y miras la barra de fórmulas, verás que muestra la misma fórmula que la celda de origen, pero de manera atenuada. Aparecerá con un color de texto gris.
Así se indica que esa celda devuelve un valor que depende de la fórmula que presenta, pero que esa fórmula no está allí en realidad. Son celdas “fantasma”, que no pueden ser editadas directamente.
Si intentas borrar el contenido de una de estas celdas “fantasma” no lo lograrás. (¡Inténtalo!😜) Si sobrescribes algo en ellas, la celda de origen mostrará el valor de error #¡DESBORDAMIENTO! y la fórmula dejará de funcionar, como ya se explicó en el artículo anterior.
Si seleccionas una celda que retorna el valor #¡DESBORDAMIENTO!, el contorno del área que debiera ocupar la matriz dinámica se mostrará punteado.
En definitiva, la única forma de editar o borrar una fórmula de matriz dinámica es efectuando dichas acciones en la celda de origen.
Rangos vs matrices
Una matriz es un conjunto de datos ordenados. (Para una explicación más detallada, consulta el artículo anterior.)
Por lo tanto, un rango de celdas de Excel es una matriz. Pero no son exactamente lo mismo. Pues hay matrices que no llegamos a ver nunca en un rango de celdas.
Son matrices virtuales, por decirlo de algún modo, cuyos datos se guardan temporalmente en la memoria del equipo, para luego ser usados como base de otros cálculos.
Pongamos un ejemplo para entender esto mejor.
Imagina que en el rango C2:C9 tenemos una serie de números, algunos de los cuales están repetidos. Queremos hacer una lista de valores únicos. De manera que usamos la función UNICOS para lograr ese fin. En la práctica podría quedar algo así:
En este caso, tanto la matriz de origen de la función, como su matriz devuelta son rangos de celdas.
Pero ahora demos un paso más allá. Supongamos que queremos ordenar la lista de valores únicos obtenida.
La solución obvia sería usar la función ORDENAR, tomando como base el rango generado por la matriz UNICOS. En el caso del ejemplo sería E3:E7.
Pero también podemos usar la matriz generada por la función UNICOS como base de la función ORDENAR, de la siguiente manera:
En este caso, Excel resolverá primero la función UNICOS y, a continuación, los datos devueltos por esa función (los valores únicos del rango C2:C9), serán procesados por ORDENAR.
Como se ve, la función UNICOS no desbordará su resultado en un rango de celdas, sino que la matriz resultante, guardada en ese momento en la memoria del equipo, será tomada directamente como argumento de la función ORDENAR.
Si nos posicionamos en la celda E3 y seleccionamos la parte correspondiente a la función UNICOS de la fórmula, Excel nos revelará la matriz que da como resultado:
Ahí tenemos una matriz que no es un rango de celdas.
Todo esto nos demuestra la importancia de dominar los conceptos y la terminología correcta en Excel. Por ejemplo, la función BUSCARV pide una matriz (o una tabla) como segundo argumento; no se limita a un rango. Pero la función CONTAR.BLANCO sí pide un rango y no una matriz, porque una matriz de Excel no puede tener lugares vacíos.
Las expresiones usadas en la aplicación no son caprichosas y tener conocimiento de este hecho nos vuelve más competentes.
Un rango de datos es siempre una matriz.
Pero una matriz no siempre es un rango de datos.
El operador #
La aparición de las matrices dinámicas trajo aparejada la creación de tres operadores nuevos. En este artículo nos ocuparemos de “#” (llamado numeral, almohadilla y de varias otras formas).
Un operador es un signo que interviene en una fórmula aplicando una acción sobre los datos. El signo “#”, si bien es usado en varios lugares dentro de Excel con diferentes propósitos (valores de error, formato personalizado o referencias estructuradas), nunca había sido empleado como operador.
Más allá de eso, vayamos a lo concreto: ¿para qué sirve?
Creo que la mejor forma de explicarlo es con un ejemplo.
Cambiemos de escenario: Ahora imaginemos que tenemos una tabla con nombres y queremos que estos nombres aparezcan ordenados dentro de una lista desplegable.
Ya hemos visto que podemos ordenar los datos dinámicamente con la función ORDENAR. Y podemos hacer una lista desplegable con la herramienta Validación de datos, que encontramos yendo a la ficha Datos, dentro del grupo Herramientas de datos.
Para crear nuestra lista desplegable en la celda seleccionada, debemos elegir la opción “Lista”, en Permitir, e indicar el rango de datos que alimentará la lista, en Origen.
Uno podría pensar que, para que los datos de la tabla aparezcan ordenados en la lista desplegable, podría escribirse, como origen, la fórmula:
Pero eso no funciona. No podemos poner una función matricial como origen. ☹
Por lo tanto, debemos aplicar la función ORDENAR en la hoja y tomar su resultado como origen de la lista desplegable.
¡Y, entonces, sí funciona!
El problema surgirá si el resultado de la función ORDENAR crece. Porque los nuevos datos quedarán fuera de la lista desplegable. Al utilizar una referencia estándar (estática) esta nunca crecerá ni se achicará por sí sola.
¡Y es aquí cuando hace su aparición triunfal nuestro operador “#”!
Para que la referencia a un rango dinámico devuelva también un rango dinámico debemos escribir la primera celda seguida del operador “#”. En este caso: =E3#
De esta manera, estamos haciendo referencia tanto la celda de origen (E3) como al rango de desbordamiento (#).
A diferencia de E3:E10, que tiene un tamaño fijo, E3# será un reflejo exacto de la matriz dinámica generada en E3 en todo momento.
El operador de desbordamiento:
Si colocas el operador “#” después de la dirección de una celda obtienes todo el resultado de la fórmula de matriz dinámica que comience en esa celda.
FILTRAR con más de una condición
En el anterior capítulo de esta serie, realizamos una primera aproximación a las funciones de matriz dinámica fundamentales: UNICOS, ORDENAR y FILTRAR. Las tres son pilares esenciales en el manejo moderno de datos en Excel.
Pero la función FILTRAR fue la que abordamos de manera más superficial, a falta del marco teórico necesario para sacarle más provecho. Creo que este es un buen momento para retomar el tema. Hagamos un repaso rápido de lo ya visto. Tiene dos argumentos obligatorios:
El primer argumento (datos) es el rango de origen de los datos que queremos filtrar.
El segundo argumento (condición) nos permite indicar el criterio mediante el cual queremos filtrar los datos de origen.
Para ejemplificar esto creé los siguientes datos ficticios de una tienda de artículos deportivos:
Usemos ahora la función FILTRAR para obtener un nuevo rango donde solo se muestren las filas de la Sucursal Norte.
Como datos colocaremos el rango original (B2:E20) y como condición estableceremos: C2:C20="Norte". Con esa condición solicitamos solo las filas que en la columna C (de C2:C20) sean igual a “Norte”.
¡Perfecto!
Hasta ahí todo bien. Pero… ¿Y si necesitamos usar más de una condición? ¿Cómo hacemos? 🤔
Si tienes suficiente experiencia en el uso de las funciones lógicas de Excel tal vez se te ocurra usar las funciones Y y O como segundo argumento, para ampliar la cantidad de condiciones.
Sin embargo, lamentablemente, esas funciones no fueron diseñadas para trabajar con matrices y no nos darán el resultado esperado. Así que nos toca apelar a una solución un poco más refinada.
Multiplicar expresiones lógicas
Partamos desde esta base: toda expresión lógica (o condición) va a dar como resultado uno de dos valores: VERDADERO o FALSO.
Por ejemplo, Sucursal = “Norte”, solo puede ser VERDADERO o FALSO.
Y, para Excel, el valor FALSO es equivalente a cero; mientras que VERDADERO es equivalente a uno. (Haciendo la conversión inversa, un cero será equivalente a FALSO, pero cualquier valor numérico diferente de cero será equivalente a VERDADERO.)
De modo que si multiplicamos VERDADERO*VERDADERO nos dará 1, porque internamente, Excel asignará el valor 1 a VERDADERO para resolver la multiplicación.
Las 4 combinaciones posibles al multiplicar dos valores lógicos serán:
| Operación | Equivalencia | Resultado |
| =VERDADERO * VERDADERO | =1 * 1 | 1 (=VERDADERO) |
| =VERDADERO * FALSO | =1 * 0 | 0 (=FALSO) |
| =FALSO * VERDADERO | =0 * 1 | 0 (=FALSO) |
| =FALSO * FALSO | =0 * 0 | 0 (=FALSO) |
Si te fijas, verás que multiplicar los valores lógicos da un resultado equivalente a la función Y, que devuelve VERDADERO solo cuando todos los valores lógicos evaluados son VERDADERO.
Esto significa que multiplicar dos condiciones equivale a verificar si ambas se cumplen. Y podemos hacer uso de este conocimiento para establecer más de una condición en la función FILTRAR.
Volvamos a nuestro ejemplo de la tienda de deportes. Para filtrar las filas en las que la sucursal sea Norte y el mes sea enero, necesitamos que:
Debemos sustituir la “y” por un signo de multiplicación (*):
Y como las sucursales están en C2:C20 y los meses en B2:B20, la expresión final será:
Debemos envolver cada comparación entre paréntesis para que Excel no se confunda.
Lo mejor es que esta fórmula sirve para cualquier cantidad de condiciones que necesites. Pues, tengas la cantidad de condiciones que tengas, si una sola de ellas deja de cumplirse, puedes tener la seguridad de que la operación dará FALSO. Cualquier multiplicación que contenga cero como uno de sus factores dará cero.
Sumar expresiones lógicas
Una vez entendido lo anterior, esto que sigue resultará bastante obvio.
Para averiguar si al menos una condición se cumple entre varias alcanza con sumarlas.
Sumar dos comparaciones da el mismo resultado que usar la función O.
Las 4 combinaciones posibles al sumar dos valores lógicos serán:
| Operación | Equivalencia | Resultado |
| =VERDADERO + VERDADERO | =1 + 1 | 2 (=VERDADERO) |
| =VERDADERO + FALSO | =1 + 0 | 1 (=VERDADERO) |
| =FALSO + VERDADERO | =0 + 1 | 1 (=VERDADERO) |
| =FALSO + FALSO | =0 + 0 | 0 (=FALSO) |
La suma de valores lógicos solo dará FALSO cuando todos los sumandos sean FALSO. Es decir, al sumar valores lógicos solo obtendrás FALSO cuando ninguna condición se cumpla.
Volvamos a nuestro ejemplo. ¿Cuándo podría darse la necesidad de sumar dos condiciones? Pues, podría darse, por ejemplo, si necesitáramos alistar las ventas que pertenezcan tanto a enero como a febrero, es decir, las ventas de enero o febrero. La expresión final sería así:
Aquí te dejo una imagen con la fórmula final:
Conversión automática
Cuando Excel espera un valor lógico intenta convertir el valor que le damos, sea cual fuere, en un valor lógico. Cuando Excel espera un número también intenta convertir el valor dado en un número. A veces lo logra, a veces no, dependiendo de varios factores. En los casos que estamos analizando en este artículo, no suele haber ningún inconveniente.
Conclusiones
En este capítulo hemos visto los siguientes puntos:
- Al usar matrices dinámicas, la celda donde está la fórmula se llama celda de origen. Al área ocupada con los resultados se le llama rango de desbordamiento.
- Solo se puede editar o borrar una fórmula de matriz dinámica en la celda de origen.
- Podemos usar una matriz dinámica dentro de una fórmula mayor, para procesar sus datos, en vez de desbordarlos directamente en la hoja.
- Para hacer referencia a un rango de matriz dinámica conviene usar el signo # después de la celda de origen (por ej.: A1#). El operador # representa el rango de desbordamiento.
- Multiplicar dos o más valores lógicos equivale a usar la función Y. Sumar valores lógicos equivale a la función O.
- Para poder evaluar más de una condición en la función FILTRAR podemos multiplicar o sumar dichas condiciones (y a veces hacer ambas cosas), según lo requiera el caso.
No te pierdas lo que sigue
Muchas gracias por acompañarme en este viaje. Espero que te haya resultado interesante y útil. Asimilar y aplicar lo visto hasta ahora te permitirá entrar en el reducido grupo de usuarios de Excel que manejan las matrices con fluidez.
Pero todavía nos falta llegar al nivel más avanzado, para que tengas el panorama completo. Así que te espero en la tercera entrega de esta serie.
Si quieres que te avise de su publicación, solo tienes que suscribirte a nuestro boletín gratuito.
La imagen superior toma elementos de un diseño de uso libre de Magnific (antes Freepik).
Más contenido que puede interesarte:
Cómo habilitar la ficha Programador/Desarrollador en Excel
Cómo convertir un número a su equivalente en letras en Excel (con VBA)
Cómo rellenar las celdas vacías de una tabla, o rango, con el valor inmediato anterior
Comparte este artículo...
