PDF de programación - Matrices - Guía de Excel 2007

Guías Excel 2007

Matrices



Guía 79

MATRICES



Las hojas de cálculo poseen prestaciones interesantes la
gestión de matrices de tipo matemático. Unas consisten en
facilitar los cálculos matriciales y otras están orientadas a
cálculos estadísticos.



CONTENIDO

Matrices ...................................................................................... 1

Contenido ................................................................................ 1

Fórmulas matriciales .............................................................. 2

Operaciones con matrices ..................................................... 4

Suma y resta ......................................................................... 4

Producto ................................................................................ 5

Determinante ........................................................................ 6

Inversa ................................................................................... 6

Transponer ............................................................................ 7

Matrices en Estadística .......................................................... 8

Frecuencia ............................................................................ 8

Suma de productos .............................................................. 9

Tendencia y Estimación .................................................... 10



1

Guías Excel 2007

Matrices



Guía 79

Solver ..................................................................................... 13

Opciones de Solver ............................................................ 17

Sistemas de ecuaciones lineales ..................................... 18



FÓRMULAS MATRICIALES


Las funciones de tipo matricial se distinguen de las demás
por dos aspectos:


Argumento y/o resultado de tipo rango (matriz)

Las funciones de tipo matricial suelen actuar sobre un
rango completo o matriz, y no sobre una sola celda. Así por
ejemplo actúa MDETERM, que calcula el determinante de
una matriz cuadrada. Ese detalle solo no las distingue de
otras como COEF.DE.CORREL, que también actúa sobre
todo un rango de datos. Veremos en los siguientes párrafos
que se necesita algo más.

Otras funciones no sólo actúan sobre una matriz, sino que
el resultado que producen es otra matriz. Así actúa
MMULT, que multiplica dos matrices y el resultado se
presenta como otra matriz.

La primera propiedad, pues, de estas funciones es que
actúan sobre matrices y pueden producir como resultado
otra matriz. También, como veremos más adelante, pueden
devolver un solo resultado en una celda.



2

Guías Excel 2007

Matrices



Guía 79


Gestión de la entrada de la función

La propiedad más característica de estas funciones es que
su fórmula está escrita entre llaves {}, y eso se logra
usando, al terminar de escribirlas, la combinación de teclas
Ctrl+Mayúscula+Intro, en lugar de usar sólo Intro, que es
lo usual.

Haz la prueba:

Escribe una matriz cuadrada. Por ejemplo


23 2
-5 1


En otra celda escribe la fórmula =MDERTM(rango de la
matriz
con
Ctrl+Mayúscula+Intro. Observarás que su fórmula se ha
escrito entre llaves y que el resultado es el determinante de
la matriz. En la imagen lo tienes:

escrito)

termina

que

has

y


La
fórmula {=MDETERM(B2:C3)}
está escrita entre llaves (es de tipo
matricial), actúa sobre el rango
B2:C3, que es la matriz cuadrada, y
produce el resultado de 33, que equivale al determinante
de la matriz, 33=23*1-(-5)*2

Si la fórmula produce un rango, hay que seleccionar ese
rango antes de escribir
la fórmula. Esto es muy
importante.

Lo vemos con un ejemplo. Supongamos que, ya que el
determinante no es nulo, deseamos encontrar la matriz
inversa de la dada. En ese caso debemos seleccionar



3

Guías Excel 2007

Matrices



Guía 79

antes de escribir un rango cuadrado de 2 por 2, por ejemplo
el D2:E3 (en amarillo en la siguiente imagen), después
escribir
con
Ctrl+Mayúscula+Intro


=MINVERSA(B2:C3)

y

terminar



Resumimos los pasos:


 Seleccionar rango de salida (puede ser una sola

celda)

 Escribir la fórmula con funciones matriciales
 Terminar con Ctrl+Mayúscula+Intro



OPERACIONES CON MATRICES



SUMA Y RESTA

Para sumar dos matrices estas deben tener el mismo
número de filas y columnas (una con la otra. Cada una
puede ser rectangular)

Para sumarlas basta con usar el signo de la suma entre sus
dos rangos, como si sumaras números. En lugar de una
fórmula del tipo =C5+D2, debes usar rangos, como en
terminar de
=A6:B10+H6:I10 y
con
escribirla
Ctrl+Mayúscula+Intro. No olvides
seleccionar
un
rango con el mismo número de

previamente



4

Guías Excel 2007

Matrices



Guía 79

filas y columnas que ambos sumandos.

Puedes estudiar este procedimiento en la hoja de la
imagen, en la que verás la fórmula sugerida escrita entre
llaves y cómo se han sumado celda a celda.



Resta

La diferencia entre dos matrices se organiza de la misma
forma que la suma, pero cambiando el signo + por el signo
–



PRODUCTO

Aquí no nos sirve la misma estructura. Si escribes
A2:B5*C2:D5 no obtienes el producto de matrices en su
sentido algebraico, sino el producto de cada elemento en
una matriz por su homólogo en la otra, que puede ser
interesante, pero no pertenece al cálculo matricial.

Recuerda que para poder multiplicar dos matrices el
número de columnas de la primera ha de ser igual al de
filas en la segunda. Es condición imprescindible. Recuerda
también que deberás reservar un rango que posea el
mismo número de filas que la primera matriz y el de
columnas igual al de la segunda.

Una vez seleccionado ese rango usa la función MMULT
seguida de los dos rangos separados por punto y coma y



5

Guías Excel 2007

Matrices



Guía 79

encerrados entre paréntesis. Como siempre, no olvides
terminar con Ctrl+Mayúscula+Intro.

Observa bien el siguiente ejemplo:



se

ha


La primera matriz posee tres columnas y la segunda tres
filas, luego se pueden multiplicar. Hemos reservado dos
filas (como la primera matriz) y una sola columna (como la
otra). Después
fórmula
{=MMULT(C2:E3;G2:G4)} para obtener el producto.


escrito

como

DETERMINANTE

El determinante de una matriz cuadrada se obtiene con la
función MDETERM. El resultado ocupa sólo una celda, ya
que se trata de un número real. Esto hace que no sea
necesario
fórmula con
Ctrl+Mayúscula+Intro.

Si la matriz no es cuadrada, se nos devolverá un mensaje
de error.

la escritura de una

terminar

INVERSA

Para que una matriz posea inversa ha de ser cuadrada y de
determinante no nulo. Cumplidas estas condiciones se
obtendrá la inversa con la función matricial MINVERSA,
que actúa sobre un rango cuadrado y se construye sobre
otro rango similar. Hay que cuidar bien estas condiciones.



6

Guías Excel 2007

Matrices



Guía 79


Los errores de truncamiento y redondeo pueden producir
que el producto de una matriz por la inversa obtenida con
MINVERSA no equivalga exactamente a la matriz unidad.

Puedes verlo en el cálculo de la imagen:



TRANSPONER

Con la función trasponer cambiamos entre sí las filas y
columnas de una matriz. Para conseguirlo daremos estos
pasos:

Seleccionamos un rango con tantas filas como columnas
tenga
tantas
columnas como filas tenga.
Usamos la función TRANSPONER(rango de la matriz
estudiada)
Terminamos con Ctrl+Mayúscula+Intro


la matriz que deseamos

transponer, y



7

Guías Excel 2007

Matrices



Guía 79

MATRICES EN ESTADÍSTICA


Recopilamos a continuación algunas funciones útiles en
Estadística. Unas son de tipo matricial y otras actúan sobre
matrices, pero su gestión coincide con la de la funciones
normales. Las primeras se escriben
terminando con
Ctrl+Mayúscula+Intro y las segundas solo con Intro



FRECUENCIA

Esta función lee todos los datos de un rango y los clasifica
en frecuencias según los límites marcados en otro rango.
Los límites se interpretan como extremos superiores de
intervalos e incluidos en ellos. Así, si escribimos un 9, se
recogerá la frecuencia de los datos inferiores o iguales a 9.

Su formato es

{=FRECUENCIA(Rango de datos; Rango de límites)}

Con la observación de la imagen se comprende bien su
funcionamiento:



Así, para el límite 6 la frecuencia es 5. Esto significa que
existen 5 datos entre el límite anterior 4 sin incluir y el 6



8

Guías Excel 2007

Matrices



Guía 79

incluido. Por tanto habrá contado las veces que aparecen el
5 y el 6.

SUMA DE PRODUCTOS


Multiplica cada término de una matriz por su homólogo en
la otra y suma posteriormente todos los resultados. Basta
estudiar la siguiente imagen para comprenderlo:



El resultado 18 se ha obtenido mediante la fórmula
=SUMAPRODUCTO(G8:H9;J8:K9)

Esta función es muy útil en Estadística
para calcular el promedio de una
distribución de frecuencias. Basta dividir la
suma de productos de cada cantidad por
su
frecuencia y dividir posteriormente
entre la suma de frecuencias. Intenta
reproducir este ejemplo:

La fórmula usada ha sido
=SUMAPRODUCTO(B4:B9;C4:C9)/SUMA(