jueves, 10 de diciembre de 2009

Fórmulas matriciales

Este tipo de fórmulas se aplican a matrices (arrays en inglés) que pueden ser datos en celdas de una columna (A1:A9), una fila (B3:Z3) o un grupo de celdas que abarcan más de una fila o columna (A1:D4).

Para ingresarlas se debe pulsar simultáneamente Control-Mayúsc-Intro (Control-Shift-Enter) sobre la fórmula. Si se pulsa sólo Intro entrega el error #¡VALOR! Cuando la fórmula se ha ingresado correctamente en la barra de fórmulas aparece encerrada entre paréntesis de llave {=fórmula} pero estos paréntesis no se deben ingresar manualmente, sino que Excel los pone automáticamente.

¿Para qué sirven? Permiten realizar cálculos complejos que involucran varios cálculos intermedios usando una sola fórmula.

Algunos ejemplos
En ocasiones queremos realizar cálculos condicionados sobre una lista o matriz de datos.  Por ejemplo, nuestra lista contiene ceros y queremos calcular el promedio de los datos excluyendo los ceros. En Excel no existe la función PROMEDIO.SI pero podemos usar las funciones =SUMAR.SI (en inglés =SUMIF) y =CONTAR.SI (COUNTIF) de esta forma para construir una:
=SUMAR.SI(datos;">0")/CONTAR.SI(datos;">0")  donde "datos" corresponde a una matriz o lista de datos.

Lo mismo podemos hacer con una fórmula matricial:
= PROMEDIO(SI((datos)=0;"";datos))
Lo interesante es entender qué está sucediendo, para poder crear la fórmula que uno requiera.  La fórmula realiza dos acciones:
1) primero evalúa si alguno de los valores contenidos en la matriz de datos es igual a cero y si es así reemplaza por una celda vacía (las dobles comillas); si no es igual a cero, mantiene el número. Por ejemplo si la matriz es:
A1: 10
A2: 20
A3:  0
A4:  30

La reemplaza por la nueva matriz virtual dentro de la fórmula (ya que no la escribe en ningún rango):
A1: 10
A2: 20
A3:
A4: 30

2) Luego calcula el promedio sobre la nueva matriz (20). Si se hubiera calculado el promedio sobre la matriz original habría sido igual a 15.

Es interesante notar que el mismo cálculo también se puede escribir con esta fórmula matricial:
=PROMEDIO(SI((A1:A4)>0;A1:A4))
Resultado igual a 20.

Estas fórmulas se pueden adaptar a cualquier función (por ejemplo MIN, MAX, SUMAR, CONTAR) y cualquier condición (por ejemplo valores negativos, valores no erróneos, valores sobre un valor mínimo).

Otro ejemplo. Supongamos que nuestra matriz contiene valores erróneos y queremos saber cuántos son:
=SUMA(SI(ESERROR(datos);1;0))
Esta fórmula evalúa la matriz de datos y genera una nueva matriz con valores uno si hay valores erróneos y valores cero si no son erróneos, luego suma la nueva matriz entregando el número de errores.

Otro ejemplo que muestra la condición lógica Y (AND). Queremos sumar los valores positivos pero menores a 100:
=SUMA((datos>0)*(datos<100)*datos)
Aquí la operación aritmética de multiplicación equivale a una Y lógica.
 
Otro ejemplo que muestra la condición lógica O (OR). Queremos sumar los valores menores a 10 ó los mayores a 100:
=SUMA(SI((datos<10)+(datos>100);datos))
Aquí la operación suma equivale a una O lógica.
 
Por último, veamos una fórmula matricial mucho más compleja para calcular el Coeficiente de Gini, un coeficiente de dispersión estadística usado habitualmente como indicador de desigualdad de ingreso u otras variables.
La fórmula más fácil para calcularlo cuando los datos no están ordenados es a través de la mitad de la "diferencia media relativa", es decir, la media aritmética de las diferencias absolutas entre todos los pares de valores:
 





Además, cuando se trabaja con una muestra está demostrado que se puede obtener un estimador no sesgado (unbiased estimator) multiplicando la fórmula por n/(n-1).

Por lo tanto la fórmula no sesgada se simplifica ligeramente quedando en:


 


Esta es la fórmula que convertimos a Excel usando una fórmula matricial:
=SUMA(ABS((datos)-TRANSPONER(datos)))/(2*SUMA(datos)*(CONTAR(datos)-1))
donde datos es una matriz contenida --necesariamente-- en una fila o columna.

Nuevamente lo interesante es entender qué hace esta fórmula. El denominador es una traducción literal de la fórmula y no tiene mayor interés. El numerador usa la función matricial transponer para generar una matriz de datos transpuesta y luego restarla a la matriz original, lo que efectivamente resta cada par de datos posible, luego calcula el valor absoluto de la resta y lo suma.

No hay comentarios:

Publicar un comentario