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