La intersección implícita en las matrices dinámicas

El concepto de ‘intersección implícita’ lleva utilizándose en Excel desde hace muchos años pero ni siquiera muchos expertos en hojas de cálculos saben de qué se trata... y mucho menos lo han aplicado en sus plantillas.

A grandes rasgos la arroba (@) u operador de intersección implícita, desactiva el comportamiento predeterminado de Excel 365 ante las matrices (en lugar de devolver la matriz completa, devuelve un solo valor).

¿Cómo se comporta la intersección implícita en Excel 2019 y anteriores?

En el Excel ‘de antes’ la intersección implícita es el comportamiento predeterminado. Siempre se realiza sin que te des cuenta. Así es como funciona:

• Si la fórmula devuelve un solo valor, con intersección implícita se devuelve ese valor (en este caso no actúa).
• En el caso de un rango, la fórmula utiliza el valor de la celda en la misma fila o columna que la fórmula.
• Si se usa una matriz, usa en la fórmula el valor de la celda superior izquierda.

Por ejemplo, si multiplicas dos columnas de números, Excel utiliza solo el número de cada columna en la misma fila donde se introduce la fórmula y, por supuesto devuelve un solo resultado en una sola celda. Observa la imagen:

Esa es la intersección implícita.

Si deseas calcular todos los valores con la misma fórmula debes anular la intersección implícita. ¿Cómo? Introduciéndola de forma matricial (con Ctrl + Mayús + Intro). Esta acción le dice a Excel que realice el cálculo con todo el rango, no solo con un valor.

En el caso de la imagen anterior, debes seleccionar previamente el rango F4:F11 (el destino del resultado) y escribir la misma fórmula de la imagen. Esta vez presiona Ctrl + Mayús + Intro. Verás que la fórmula se encierra entre corchetes indicando que se trata de una fórmula matricial. Y el resultado es el siguiente: los números de cada una de las filas se multiplica y se muestra el resultado de cada cálculo.

¿Y qué ocurre con la intersección implícita en Excel365?

Con la introducción de Excel 365 Microsoft ha mejorado mucho el motor de cálculo otorgándole un uso más ‘amigable’ a las matrices. Esto ha supuesto un cambio de comportamiento. Si hasta ahora la intersección dinámica era la fórma de cálculo predeterminada, ahora ya no. Si en Excel 365 una fórmula es capaz de devolver varios resultados, los devuelve automáticamente en la hoja.

Cuando antes había que informarle a Excel de que se iba a introducir una fórmula matricial, ahora hay que informarle de que únicamente quieres un resultado.

Para decirle a Excel que devuelva un único valor (en caso de que exista la posibilidad de que devuelva varios de ellos) tienes que activar explícitamente la intersección implícita mediante el operador @.

Yo ya me he acostumbrado pero he de reconocer que me costó algo de tiempo ????.

El operador de intersección implícita @

Como he comentado el operador se introdujo por primera vez en Excel 365 para evitar el comportamiento dinámico en esta versión. Si quieres que una fórmula devuelva solo un valor, debes introducir la @ delante del nombre de la función (o antes del rango o matriz dentro de la fórmula) para que se comporte como una fórmula 'normal' y no matricial.

Fíjate en este ejemplo de Excel 365:

La fórmula únicamente devuelve un resultado a pesar de introducir todo el rango.

Intersección implícita de Excel ‘antiguo’ a Excel 365

Es posible que aparezca una arroba (@) si recibes un archivo en tu Excel 365 con fórmulas matriciales creadas en una versión anterior. Esto no cambia el comportamiento de la fórmula, simplemente te permite ver la intersección implícita, que antes no era posible. Existen algunas funciones que podrían devolver rangos con varias celdas como INDICE o DESREF pero también pueden darse intersecciones implícitas en otras funciones que usen rangos como argumentos (como SUMA o PROMEDIO).

Intersección implícita de Excel 365 a Excel ‘antiguo’

Por el contrario, si utilizas Excel 2019 o anterior y recibes un archivo con fórmulas de matriz dinámica que contengan el operador @ es posible que se muestren con este formato:

_xlfn.CONCAT()

El resultado se visualizará en la celda pero no podrás editarlo, pues la función CONCAT no está disponible en versiones anteriores a 365.

Esa arroba ¿tiene algo que ver con las referencias estructuradas de las tablas?

Así es. Fíjate en la siguiente fórmula:

Al introducirla en cualquier versión de Excel obtendrás este mensaje:

Si ya tienes experiencia con las referencias estructuradas de las tablas esta sintaxis te resultará familiar. El operador @ indica que la fórmula solo operará con los valores de los campos Cantidad y Precio que se encuentren en la misma fila que ella.

En caso de no introducir el operador de intersección implícita Excel asumirá que quieres calcular todo el rango y produce un error de desbordamiento porque no puede devolver todos los resultados en las celdas en blanco.

Resumen

El concepto de intersección implícita no es algo nuevo aunque ha permanecido ‘oculto’. Al introducir una arroba (@) delante de un rango o una función que devuelve una matriz, los cálculos se realizan únicamente con los valores de los rangos de la fila en la que se encuentra la fórmula ignorando el resto.

¿Conocías ya la intersección implícita? Déjame un comentario.