Download EJERCITACIÓN EN EXCEL. 2.1.- OPERACIONES CON MATRICES

Computación II - Ingeniería en Agrimensura - 2002 -
UNIDAD Nº 2: EJERCITACIÓN EN EXCEL.
2.1.- OPERACIONES CON MATRICES
Inversa de una Matriz:
La función MINVERSA devuelve la inversa de una matriz almacenada en un rango de celdas.
Sintaxis: MINVERSA(matriz)
Donde matriz es una matriz cuadrada; puede ingresarse seleccionándola o bien indicando la celda
donde está el elemento 11, y la celda donde está el elemento nn separadas por dos puntos. Ej.
MINVERSA(Fk:Ch).
* El argumento matriz puede expresarse como un rango de celdas, por ejemplo A1:C3; como
una constante matricial, por ejemplo, {1;2;3\4;5;6\7;8;9} o como un nombre de cualquiera de
éstas.
* Si hay celdas vacías o celdas que contienen texto, MINVERSA devuelve el valor de error
#¡VALOR!
* MINVERSA también devuelve el valor de error #¡VALOR! si la matriz no es cuadrada.
Observaciones:
* Las fórmulas que devuelvan matrices deben introducirse como fórmulas matriciales.
* Las funciones matriz inversa y determinante se usan para resolver sistemas de ecuaciones lineales
con el mismo número de incógnitas que de ecuaciones. El producto de una matriz de orden n por
su inversa es la matriz identidad de orden n.
* Como ejemplo de como calcular la inversa de una matriz de orden dos, supongamos que el rango
A1:B2 contiene las letras a, b ,c y d, donde a,b,c,d son números. En la siguiente tabla se muestra la
inversa de la matriz A1:B2.
Columna A Columna B
Fila 1 d/(a*d-b*c) -b/(a*d-b*c)
Fila 2 -c/(a*d-b*c) a/(a*d-b*c)
* El cálculo de MINVERSA tiene una exactitud de 16 dígitos aproximadamente, lo cual puede
causar un pequeño error numérico cuando no se completa la cancelación.
* Algunas matrices cuadradas no se pueden invertir y devuelven el valor de error #¡NUM! con
MINVERSA. El determinante de una matriz no invertible es 0.


Ejemplo 1: Escriba en las celda A1:B2 la matriz: 


Para calcular su inversa, siga los siguientes pasos:
1. Seleccione donde desea que se ubique la inversa por supuesto con un rango de 2x2, como por
ejemplo D1:E2.
æ 1
3
2
4
2. Ingrese la función Minversa desde la barra de herramientas o bien desde el menú Insertar
Funciones, aparecerá el siguiente cuadro de diálogo
Mg. Elda Pick, Ing. José Luis Bustos y Lic. Ester de la Torre
Pág.:52
Computación II - Ingeniería en Agrimensura - 2002 -
3. En este cuadro ingrese el rango de la matriz a la cual desea calcular su inversa, seleccionando
desde A1:B2.
4. Oprima la combinación de Ctrl+Mayúscula+Entrar y obtendrá la inversa de la matriz
seleccionada.
Producto de Matrices:
La función MMULT devuelve la matriz producto de dos matrices. El resultado es una matriz con el mismo número de filas que matriz1 y el mismo número de columnas que matriz2.
Sintaxis: MMULT(matriz1;matriz2)
Matriz1 y matriz2 son las matrices que desea multiplicar (en ese orden).
* El número de columnas en matriz1 debe ser el mismo que el número de filas en matriz2 y
los elementos de ambas matrices sólo pueden ser números.
* Los argumentos matriz1 y matriz2 pueden expresarse como rangos de celdas, constantes
matriciales o referencias.
* MMULT devuelve el valor de error #¡VALOR! si hay celdas vacías o con texto, o si el
número de columnas de matriz1 es diferente al número de filas de matriz2.
Observaciones:
* El elemento genérico de la matriz producto “A” de dos matrices ”B” y “C” (en ese orden)
es:
n
aij = ∑ bik ⋅ ckj
k =1
donde i es el número de filas y j es el número de columnas.
* Las fórmulas que devuelven matrices deben introducirse como fórmulas matriciales.
Mg. Elda Pick, Ing. José Luis Bustos y Lic. Ester de la Torre
Pág.:53
Computación II - Ingeniería en Agrimensura - 2002 -
Ejemplos:
1) MMULT({1;3\7;2}, {2;0\0;2}) es igual a {2;6\14;4}
Para resolver el ejercicio anterior siga los siguientes pasos:
a) Seleccione un rango de celdas vacías de orden 2x2
b) Inserte la función, MMULT
c) En el cuadro de diálogo ingrese las dos matrices de la siguiente forma: {1;3\7;2} y
{2;0\0;2}
d) Oprima la combinación: Ctrl+Mayúscula+Entrar
2) MMULT({3;0\2;0}, {2;0\0;2}) es igual a {6;0\4;0}
3) MMULT({1;3;0\7;2;0\1;0;0}, {2;0\0;2}) es igual a #¡VALOR! porque la primera matriz tiene
tres filas y la segunda matriz sólo tiene dos filas.
Transposición de matrices:
La función TRANSPONER devuelve un rango vertical de celdas como un rango horizontal y
viceversa.
La función TRANSPONER debe introducirse como una fórmula matricial en un rango cuyo
número de filas y de columnas sea igual al número de filas y de columnas del argumento matriz.
Utilice TRANSPONER para cambiar la orientación vertical y horizontal de una matriz en una hoja
de cálculo.
Sintaxis:
TRANSPONER(matriz)
Matriz es una matriz de un rango de celdas en una hoja de cálculo que desea transponer. Matriz
puede ser también un rango de celdas. La transposición de una matriz se crea utilizando la primera
fila de la matriz como primera columna de la nueva matriz, la segunda fila de la matriz como
segunda columna de la nueva matriz y así sucesivamente.
Ejemplo:
Supongamos que el rango A1:C1 contiene los valores 1; 2 y 3 respectivamente.
Cuando se introduce la siguiente formula como una matriz en las celdas A3:A5:
1) Seleccione el rango de celdas A3:A5
2) Inserte la función TRASPONER
3) Seleccione en el cuadro de diálogo el rango de matrices A1:C1
4) Oprima la combinación Ctrl+Mayúscula+Entrar y se obtendrá:
Mg. Elda Pick, Ing. José Luis Bustos y Lic. Ester de la Torre
Pág.:54
Computación II - Ingeniería en Agrimensura - 2002 -
Cálculo de determinantes:
La función MDETERM devuelve la determinante de una matriz (cuadrada).
Sintaxis: MDETERM(matriz)
Matriz es una matriz cuadrada.
* Matriz se puede dar como un rango de celdas, por ejemplo A1:C3; como una constante
matricial, por ejemplo {1,2,3;4,5,6;7,8,9} o como un nombre que se refiera a cualquiera de
ellas.
* Si una de las celdas en la matriz contiene celdas vacías o con texto, MDETERM devuelve
el valor de error #¡VALOR!
* MDETERM también devolverá #¡VALOR! si el argumento matriz no tiene un número
igual de filas y de columnas.
Observaciones:
* La función determinante de una matriz es un número que se obtiene a partir de los
valores de los elementos de la matriz. La función determinante de una matriz de
orden tres, A1:C3, es:
MDETERM(A1:C3) =
A1*(B2*C3-B3*C2) + A2*(B3*C1-B1*C3) + A3*(B1*C2-B2*C1)
* MDETERM tiene una exactitud de cálculo de 16 dígitos aproximadamente, lo que puede
causar pequeños errores numéricos cuando el cálculo no está completo. Por ejemplo, el
determinante de una matriz podría diferir de cero en 1E-16 (=10-16).
Ejemplos:
1) MDETERM({1;3;8;5\1;3;6;1\1;1;1;0\7;3;10;2}) = 88.
2) MDETERM({3;6;1\1;1;0\3;10;2}) = 1.
3) MDETERM({3;6\1;1}) =-3.
4) MDETERM({1;3;8;5\1;3;6;1}) es igual al valor de error #¡VALOR! porque la matriz no es
cuadrada.
Mg. Elda Pick, Ing. José Luis Bustos y Lic. Ester de la Torre
Pág.:55
Computación II - Ingeniería en Agrimensura - 2002 -
EJERCICIOS PROPUESTOS:
1) Obtenga, cuando sea posible, la inversa de las matrices siguientes:




A=
2 −1 
− 2 2 7


 − 2 7 1


B = 1 / 2 − 1 0 
5 − 3 1 


0 2 0 


D =  4 3 − 8
1 0 7 


2
0

E = 4 − 3
1
0

1
3
5
4





 −1 0 3 / 2 


C = 0 2 1 
 −1 5 0 


2 6 
 1


F = 0
1 3 
 − 2 − 1 − 3


2) Calcule:
a)
b)
c)
d)
e)
f)
g)
h)
A.A-1.
A-1. A.
A.C.
C-1.D.
C-1.D.
B.A.
CT .B.
ET .D.
3) Calcule:
a) det(A)
b) det(B)
c) det(C)
d) det(D)
e) det(A.B)
f) det(E)
g) det(F)
4) Resuelva los sistemas lineales siguientes por el método de inversión matricial, cuando sea
posible.
a)
5 x + 12 y + 43 z = 19

10 x − 8 y + 27 z = 14
5 x − 20 y + 30 z = −3

2 x + 4 y + 6 z = 18

b) 4 x + 8 y + 6 z = 14
10 x + 20 y + 12 z = 36

Mg. Elda Pick, Ing. José Luis Bustos y Lic. Ester de la Torre
Pág.:56
Computación II - Ingeniería en Agrimensura - 2002 -
2.2.- Ángulos y funciones trigonométricas en Excel.
Es muy común, en la práctica profesional, la utilización de la planilla de cálculo en la
solución de problemas orientados a la topografía, por ejemplo, nivelaciones, levantamientos
planimétricos, etc. En la mayoría de los casos los datos pueden ser leídos por filas y columnas y la
introducción de las fórmulas resulta sencilla. Pero en todos los casos es necesario la introducción
de ángulos, que según se convenga, pueden ser introducidos de dos maneras. Recordemos que
usaremos el sistema sexagesimal de ángulos en los cuales 360º corresponden a una vuelta completa
y un grado lo forma 60 minutos y un minuto lo forman 60 segundos.
La primer forma de introducir los ángulos, y que nosotros aplicaremos en la resolución de
ejemplos, conviene en introducir los ángulos medidos en campaña, separando la parte de los grados
minutos y segundos en tres celdas distintas. Otra forma de hacerlo, que también es utilizada en la
práctica es colocar el ángulo como forma entera y en la parte decimal los minutos y segundos. Esta
última utiliza una sola celda para cada dato angular. Veamos algunos ejemplos:
El primer ángulo corresponde a 31º 27´48”, mientras que el segundo a 45º 58´25”.
En esta última figura se muestra la segunda forma de introducir los mismos ángulos del
ejemplo anterior.
Las funciones trigonométricas en Excel, se introducen en las celdas con la siguiente sintaxis:
Ej. SENO ( número)
Devuelve el seno de un ángulo determinado.
Número es el ángulo en radianes cuyo seno desea obtener. Si el argumento está en grados,
multiplíquelo por PI()/180 para convertirlo en radianes. Obviamente el ángulo se introduce
referenciado a una celda como por ejemplo: seno(A2), para ello el ángulo debe ocupar una sola
celda y debe estar expresado en radianes. Por lo tanto veremos cómo se soluciona para los dos
formas de introducir los mismos.
En el primer caso se junta el ángulo en una sola celda, pasando la parte de segundos y
minutos a grados sexagesimales y sumando todo de la siguiente forma:
Mg. Elda Pick, Ing. José Luis Bustos y Lic. Ester de la Torre
Pág.:57
Computación II - Ingeniería en Agrimensura - 2002 -
Luego se lo puede pasar a radianes en una operación posterior, (ó en la misma)
multiplicando lo por PI()/180:
Para la segunda forma de introducir los ángulos se sigue el siguiente procedimiento: primero
se resta la parte entera, la parte decimal que queda lo multiplicamos por 100 para obtener los
minutos. Para esta operación utilizaremos la función “ENTERO( )”, que redondea un número hasta
el entero inferior más próximo.
Para obtener los segundos se sigue el mismo procedimiento:
Finalmente el ángulo se obtiene así: se le suma a la parte entera del ángulo la parte
de los minutos divididas en 60 y la parte de los segundos expresados en grados, es decir dividido en
602 =3600
Si
se
quiere
usar este ángulo
para
aplicarle
las
funciones trigonométricas, se lo pasa a radianes como ya hemos visto. Los 3 pasos sucesivos
marcados en el ejemplo anterior se podría haber hecho en un solo paso.
Con estos conceptos, se obtendrán las coordenadas de un levantamiento planimétrico
resuelto en la Cátedra de Topografía I.
Mg. Elda Pick, Ing. José Luis Bustos y Lic. Ester de la Torre
Pág.:58
Computación II - Ingeniería en Agrimensura - 2002 -
Básicamente el ejercicio consiste en las lecturas realizadas sobre una mira (regla), con un
instrumento, para la obtención de las distancias entre distintos puntos visectados y las estaciones
donde se ubica el instrumento. Las fórmulas a aplicar se darán oportunamente en clase, una vez que
se haya avanzado en el cursado de la materia Topografía I.
Las planillas que se muestran en la siguiente tabla son las obtenidas en campaña:
ESTACIÓN
(COORDENADAS)
A (20.000;10.000)
B (11,180;43.840)
C (18.370;64.198)
PUNTO
VDO.
1
2
3
4
5
6
7
8
9
10
B
11
12
13
14
15
16
17
18
19
20
21
22
23
24
C
25
26
27
28
29
30
31
32
33
LECTURAS DE MIRA
L. SUP.
L. MEDIA
L. INF.
1.385
1.424
1.418
1.476
1.490
1.671
1.779
1.791
1.800
1.696
0.875
1.158
1.068
0.907
1.015
1.164
1.326
1.322
1.419
1.432
1.432
1.438
1.437
1.455
1.466
1.508
1.485
1.438
1.457
1.459
1.520
1.505
1.510
1.562
1.523
1.345
1.349
1.326
1.402
1.400
1.500
1.600
1.600
1.600
1.500
0.700
1.100
1.000
0.800
0.900
1.100
1.300
1.300
1.400
1.400
1.400
1.400
1.400
1.400
1.400
1.400
1.400
1.400
1.400
1.400
1.400
1.400
1.400
1.400
1.400
1.305
1.274
1.234
1.328
1.310
1.329
1.421
1.409
1.400
1.304
0.525
1.042
0.932
0.693
0.785
1.036
1.274
1.278
1.381
1.368
1.368
1.362
1.363
1.345
1.334
1.292
1.315
1.362
1.343
1.341
1.280
1.295
1.290
1.238
1.277
Mg. Elda Pick, Ing. José Luis Bustos y Lic. Ester de la Torre
Pág.:59
Computación II - Ingeniería en Agrimensura - 2002 -
Además de las lecturas mostradas anteriormente, se cuenta también con las lecturas de
ángulos horizontales y verticales para el cálculo de las coordenadas.
ESTACIÓN
(COORDENADAS)
A (20.000;10.000)
B (11,180;43.840)
C (18.370;64.198)
DIRECCIÓN
HORIZONTAL
GRDS.
MIN.
4
4
7
16
17
67
66
67
67
73
104
14
20
19
22
44
35
60
181
150
144
138
120
120
87
70
197
335
21
94
97
113
131
309
300
1
37
11
45
11
52
24
10
32
3
36
3
28
52
59
20
44
58
16
13
4
47
10
51
10
32
45
47
46
27
29
56
34
26
52
SEG.
6
57
12
9
36
24
21
45
51
39
30
36
18
30
12
24
57
33
12
9
39
33
12
3
27
54
44
9
30
42
12
30
6
6
15
DIRECCIÓN
HORIZONTAL
GRDS.
MIN.
90
90
90
89
89
89
89
90
90
90
91
91
91
91
91
91
91
91
91
91
91
91
91
90
90
91
90
89
90
90
90
90
90
89
90
0
0
0
57
52
57
56
0
3
10
39
38
48
45
27
37
32
54
19
5
12
1
9
32
39
0
12
19
32
7
35
23
17
47
35
SEG.
0
0
0
48
48
33
39
57
36
45
39
33
48
54
18
0
12
15
6
57
3
15
51
48
21
18
42
18
34
18
24
4
6
51
6
Una vez obtenidas las coordenadas de cada punto, se hará la representación gráfica de la
planimetría, usando AutoCAD.
Mg. Elda Pick, Ing. José Luis Bustos y Lic. Ester de la Torre
Pág.:60