Fórmulas matriciales i II

Ya había tratado antes el tema de las fórmulas matriciales, pero estaba contento de cómo lo había hecho. Además la sintaxis que expliqué no era la más comprensible visualmente, por lo tanto esta entrada es una aclaración y extensión de la primera.

Repasemos los aspectos clave:

• Las fórmulas matriciales son muy potentes y permiten analizar tablas con condiciones complejas.
• Las fórmulas matriciales consumen muchos recursos del ordenador, se deben usar con moderación y cuando no exista una fórmula nativa de Excel que sirva.
• No se puede evaluar una columna entera tipo "A:B", se debe definir un rango con un límite definido de filas como "A1:B3000".
• Las fórmulas matriciales pueden evaluar matrices verticales (fila a fila) o horizontales (columna a columna).
• Para que una fórmula se evalúe de forma matricial se debe introducir apretando "Ctrl"+"Mayúsculas"+"Enter".

Bien, entremos un poco en detalle de como trabaja Excel con la fórmula. Tendremos una tabla que puede ser tan grande como queramos y le pondremos unas condiciones sobre algunas de las columnas o filas. Cada vez que introducimos una condición el Excel se hace un mapa de la columna tipo (verdadero, falso, verdadero, falso, falso, ...). Si queremos poner condiciones adicionales, Excel irá superponiendo estas matrices de condiciones.

Ejemplo: (verdadero, falso, falso, verdadero, falso) y (falso, verdadero, falso, verdadero, falso) = (falso, falso, falso, verdadero, falso)

Se debe resaltar que el orden de las condiciones es importante, ya que en una condición tipo "y" cada condición actúa como filtro de las siguientes.

Ejemplo: (verdadero, falso, falso, verdadero, falso) o (falso, verdadero, falso, verdadero, falso) = (verdadero, verdadero, falso, verdadero, falso)

Una condición tipo "o" hace que Excel realice una suma de las matrices.

Normalmente pondremos diversas condiciones comparándolas con una referencia:

• > mayor que
• <>
• = igual que
• >= mayor o igual que
• <= menor o igual que
• <> diferente que

Los operadores que podemos usar son:

• * operador "y"
• + operador "o"
• { } como matriz de condiciones "o" (separador ; para filas y \ para columnas)

La sintaxis es la siguiente (recordad que las llaves las pone Excel):

{=FÓRMULA(SI(((condición 1a)+(condición 1b))*(condición 2);(rango valores)))}

{=FÓRMULA(SI((condición={1a; 1b})*(condición 2);(rango valores)))}

Se lee: si se cumple la condición 1a o la condición 1b y también se cumple la condición 2 entonces aplica la fórmula sobre el rango de valores. Las dos fórmulas son iguales si los datos están en columnas y se evalúan fila a fila.

Ejemplo real de fórmula:
{=SUMA(SI((B5:F5="b")*(B6:F6={"azul"\"rojo"});B8:F8))}

La ventaja de las fórmulas matriciales también reside en que no sólo permiten hacer sumas complejas sino que admiten otros tipos de funciones como promedio, todas las medianas, mínimo, máximo, contar, k ésimo mayor o menor, percentil y aún debo dejarme alguna.

Existe un caso particular para calcular sumas matriciales en el que nos podemos ahorrar el condicional:

{=SUMA((condición={1a; 1b})*(condición 2)*(rango de valores))}

{=SUMA((condición={1a; 1b})*(condición 2)*(rango de valores<>0))}

En la primera formula sumamos os valores del rango de valores y en la segunda contamos los valores diferentes de 0.

Fichero con ejemplos: Fórmulas matriciales II
----------------------------------------------