hojas de cÁlculopersonales.unican.es/corcuerp/excel/slides/excel_new2.pdf · introducción a excel...
TRANSCRIPT
Índice
• Técnicas generales de resolución de problemas.
• Fundamentos de Ingeniería aplicables.
• Procedimientos de solución matemática.
Introducción a Excel 2
• Procedimientos de solución matemática.
• Papel de las hojas de cálculo.
• Introducción a Excel.
Técnicas generales de resolución de problemas (I)
• El análisis de ingeniería es un proceso sistemático para analizar y comprender los problemas que se encuentran en los diversos campos de la ingeniería.
Introducción a Excel 3
campos de la ingeniería.
• Para llevar a cabo este proceso de manera satisfactoria se debe estar familiarizado con técnicas generales de resolución de problemas.
Técnicas generales de resolución de problemas (II)
• Pensar sobre el problema antes de intentar resolverlo.
• Dibujar un esquema del problema antes de empezar a resolverlo.
• Identificar el propósito general y los puntos
Introducción a Excel 4
• Identificar el propósito general y los puntos clave.
• Identificar la información conocida (datos de entrada) y la información a calcularse (datos de salida).
• Identificar los principios de ingeniería básicos que pueden aplicarse.
Técnicas generales de resolución de problemas (III)
• Pensar en la manera de resolver el problema antes de empezar la solución.
• Desarrollar la solución de manera ordenada y lógica.
Introducción a Excel 5
lógica.
• Después de obtener la solución, pensar en ella. Tiene sentido? →Validación de resultados
• Asegurarse que la solución es clara y completa.
Fundamentos de Ingeniería aplicables
• Equilibrio: La mayoría de problemas de estado estable (problemas en los que las cosas permanecen constantes respecto al tiempo) se basan en algún tipo de equilibrio. Ejm: Equilibrio de fuerzas, Equilibrio de flujo, Equilibrio químico
Introducción a Excel 6
de fuerzas, Equilibrio de flujo, Equilibrio químico
• Leyes de Conservación: Las más comunes son la conservación de masa y energía.
• Fenómenos de cambio: Tienen la forma de un potencial que cambia un flujo. Ejm: ley de Ohm, ley de Fourier.
Procedimientos de solución matemática (I)
• Técnicas de análisis de datos. Técnicas estadísticas que permiten obtener conclusiones útiles sobre la información que está escondida en un conjunto de datos.
• Técnicas de ajuste de curvas. Trata de pasar una
Introducción a Excel 7
• Técnicas de ajuste de curvas. Trata de pasar una curva que aproxime un conjunto de datos.
• Técnicas de interpolación. Permiten obtener valores precisos de una variable dependiente para un conjunto de datos tabulados independientes.
• Técnicas de solución de ecuaciones algebraicassimples.
Procedimientos de solución matemática (II)
• Técnicas de solución de ecuaciones algebraicas lineales simultáneas. Muchos problemas de ingeniería se pueden representar como un conjunto de ecuaciones algebraicas lineales simultáneas.
• Técnicas para evaluar integrales. En ingeniería se
Introducción a Excel 8
• Técnicas para evaluar integrales. En ingeniería se requiere calcular promedios o determinar el efecto acumulativo de algún proceso.
• Técnicas para realizar el análisis económico de ingeniería. Selección económica de alternativas.
• Técnicas de optimización. Seleccionar la mejor alternativa entre varias con un criterio óptimo.
Procedimientos de solución matemática (III)
• Simbólicos: soluciones que se expresan en términos algebraicos.– Mapple
– Mathematica
– Matlab (Symbolic toolbox)
Introducción a Excel 9
– Matlab (Symbolic toolbox)
– MathCad
• Numéricos: soluciones expresadas en términos de números (o grafos).– Hojas de cálculo
– Matlab
– Lenguajes de programación
Papel de las hojas de cálculo• Antes del uso de los ordenadores se utilizaban reglas de cálculo,
tablas tabuladas y calculadoras mecánicas.
Introducción a Excel 10
• Antes del uso de los ordenadores personales (1980s) la solución de los problemas de ingeniería requería el uso de grandes ordenadores programados de manera específica a través de un lenguaje de programación (p.e. Fortran, Pascal, C).
• En la actualidad, las hojas de cálculo se han convertido en la herramienta principal del ingeniero debido a la facilidad para resolver problemas numéricos y su representación gráfica.
Introducción a la hojas de cálculo• En 1979 Dan Bricklin (alumno de la Harvard
Business School) crea una aplicación informática (VisiCalc) que simulaba una hoja de balance.
• Otras hojas: Multiplan, SuperCalc, Lotus 1-2-3, PlanPerfect, Quattro, Calc, Excel, etc.
Introducción a Excel 11
PlanPerfect, Quattro, Calc, Excel, etc.
• Las hojas de cálculo se componen de celdas dispuestas en filas y columnas que pueden contener valores numéricos, alfanuméricos o relaciones entre sí. También permiten graficar los valores de las celdas.
• Excel forma parte del paquete Microsoft Office.
Iniciar Excel• MS XP: Botón Inicio → Todos los programas →
Microsoft Office → Microsoft Office Excel 2007
Introducción a Excel 12
• Haciendo doble click sobre el icono de acceso directo a Microsoft Office Excel 2007
Cerrar Excel• Botón Cerrar que se encuentra en la parte superior
derecha de la venta de Excel
• Botón Office → Cerrar
Introducción a Excel 13
• Pulsando teclas Alt + F4
Componentes de la ventana 1
• Barra de título
– Minimizar
– Restaurar
– Cerrar
Introducción a Excel 16
– Cerrar
• Barra de acceso rápido– Guardar
– Deshacer
– Rehacer
– Personalizar Banda de opciones de acceso rápido
Componentes de la ventana 2
• Cinta de Opciones
Introducción a Excel 17
– Pulsando tecla ALT (modo de acceso por teclado)
Componentes de la ventana 3
• Botón Office
Introducción a Excel 18
• Barra de fórmulas: muestra el contenido de la celda activa
Componentes de la ventana 4
• Hoja de cálculo: contiene celdas identificadas por la columna (letra) y fila (número)
Introducción a Excel 19
Componentes de la ventana 5
• Barra de etiquetas: movernos por las distintas hojas
• Barras de desplazamiento: movernos a lo largo y ancho de la hoja
Introducción a Excel 20
• Barra de estado (configurable)
• Barra de visualización
• Ayuda– Tecla F1
Fundamentos de Excel 1
• Un libro de trabajo es el archivo que creamos con Excel.
• Un libro de trabajo está formado por varias hojas de trabajo (inicialmente 3).
• La hoja de cálculo es uno de los distintos tipos de
Introducción a Excel 21
• La hoja de cálculo es uno de los distintos tipos de hojas que puede contener un libro de trabajo. Es como una gran hoja cuadriculada formada por 16384 columnas y 1.048.576 filas.
• Los elementos individuales (intersección de una columna y una fila) dentro de una hoja de cálculo se llaman celdas.
Fundamentos de Excel 1
• Un rango de celdas es un bloque rectangular de una o más celdas que Excel trata como una unidad.
• Una celda puede contener dos tipos diferentes de datos: – una constante numérica (un número) o
Introducción a Excel 22
– una constante numérica (un número) o
– una constante de texto (etiqueta o cadena de caracteres).
• Cada celda se referencia o llama por su columna (normalmente una letra) y el número de fila. Ejm: B3 referencia la celda en la columna B y fila 3.
Fundamentos de Excel 2
• Un conjunto de celdas forman una hoja de trabajo.
• Si una celda contiene un valor numérico, el número puede haberse escrito directamente o ser el resultado de la evaluación de una fórmula.
• Una fórmula expresa interdependencias entre
Introducción a Excel 23
• Una fórmula expresa interdependencias entre celdas. Ejm: el valor numérico en la celda C7 es generado por la fórmula =(C3+C4+C5).
• Esta importante característica permite análisis del tipo qué pasa si (what-if).
Fundamentos de Excel (I)• Una hoja de cálculo es una tabla que contiene
valores numéricos y/o alfanuméricos.
• Los elementos individuales dentro de una hoja de cálculo se llaman celdas.
• Una celda puede contener dos tipos diferentes de
Introducción a Excel 24
• Una celda puede contener dos tipos diferentes de datos: – una constante numérica (un número) o
– una constante de texto (etiqueta o cadena de caracteres).
• Cada celda se referencia o llama por su columna (normalmente una letra) y el número de fila. Ejm: B3 referencia la celda en la columna B y fila 3.
Fundamentos de Excel (II)• Un conjunto de celdas forman una hoja de trabajo.
• Si una celda contiene un valor numérico, el número puede haberse escrito directamente o ser el resultado de la evaluación de una fórmula.
• Una fórmula expresa interdependencias entre
Introducción a Excel 25
• Una fórmula expresa interdependencias entre celdas. Ejm: el valor numérico en la celda C7 es generado por la fórmula =(C3+C4+C5). Por tanto el valor numérico en la celda C7 se obtiene sumando los valores en las celdas C3, C4 y C5.
• Esta importante característica permite análisis del tipo qué pasa si (what-if).
Movimiento en la hoja 1
• La celda activa en curso se identifica con un rectángulo alrededor de ella.
• El puntero del ratón se indica por un cursor en cruz que indica la posición del ratón.
• Para moverse por la hoja de cálculo se puede usar:
Introducción a Excel 26
• Para moverse por la hoja de cálculo se puede usar:– Ratón– Teclas– Barra de desplazamiento – Barra de fórmulas– Opción: Inicio → Buscar y seleccionar → Ir a…
(F5)
Movimiento en la hoja 2
• Las teclas o combinación de ellas para moverse rápidamente por la hoja de cálculo son:
MOVIMIENTO TECLADO
Celda Abajo / Celda Arriba FLECHA ABAJO / FLECHA ARRIBA
Celda Derecha / Celda Izquierda FLECHADERECHA / FLECHA IZQUIERDA
Introducción a Excel 27
Pantalla Abajo / Pantalla Arriba AVPAG / REPAG
Celda A1 CTRL+INICIO
Primera celda de la columna activa FIN + FLECHA ARRIBA
Última celda de la columna activa FIN + FLECHA ABAJO
Primera celda de la fila activa FIN + FLECHA IZQUIERDA
Última celda de la fila activa FIN + FLECHA DERECHA
Última fila de la hoja CTRL + FLECHA ABAJO
Última columna de la hoja CTRL + FLECHA DERECHA
Movimiento en el libro• En un libro de trabajo existen varias hojas de
cálculo. Por defecto aparecen 3 hojas de cálculo aunque el número podría cambiarse.
• Para seleccionar una hoja activa se usa la barra de etiquetas.
Introducción a Excel 28
• Opcionalmente con teclas
MOVIMIENTO TECLADO
Hoja Siguiente CTRL+ AVPAG
Hoja Anterior CTRL+ REPAG
Trabajando con las hojas 1
• Buenas costumbres o etapas para crear una hoja de cálculo:– Introducir rótulos (texto), datos y fórmulas.– Realizar pruebas con grupos de datos para comprobar
que las fórmulas realizan los cálculos correctamente.– Dar formatos adecuados que faciliten la lectura y
Introducción a Excel 29
– Dar formatos adecuados que faciliten la lectura y comprensión de la hoja de cálculo.
• Lo que vemos en las celdas no siempre coincide con lo que se ha tecleado en ellas. – Si contiene una fórmula lo que vemos es el resultado de
la fórmula.– Otras veces la diferencia se debe al formato de la celda.
Trabajando con las hojas 2
• Para ver el contenido real de una celda seleccionamos la celda y miramos en la Barra de fórmulas.
Introducción a Excel 30
Escritura de etiquetas o rótulos• Para facilitar la comprensión de las hojas de cálculo,
conviene introducir etiquetas o rótulos en una hoja . Generalmente es un texto.
• Para introducir un rótulo o etiqueta en una celda:– Seleccionar la celda.
Introducción a Excel 31
– Seleccionar la celda.
– Escribir el texto del rótulo (máximo 255 caracteres).
– Pulsar Entrar (↵) o seleccionar otra celda haciendo clic sobre ella o pulsando las teclas del cursor o pulsando el botón de aceptación en la barra de fórmulas.
– Si el texto es largo se puede cambiar el ancho de la columna o desde el menú Inicio → Formato → Ancho de Columna.
Eliminar o borrar el contenido• Si se está escribiendo los datos se puede usar la
tecla de Retroceso, o se puede reescribir el dato. El nuevo dato reemplazará al antiguo una vez que se pulsa Entrar.
• Se puede borrar una celda activa pulsando la tecla Supr o desde Edición→Borrar →
Introducción a Excel 32
Supr o desde Edición→Borrar →Todo/Contenido.
• Empleando el icono Cancelar de la barra de edición de celda.
Inserción de formas e imágenes 1
• Para introducir figuras, esquemas, letras artísticas e imágenes se selecciona:– Formas: Insertar → Ilustraciones → Formas.
– Esquemas: Insertar → Ilustraciones → SamrtArt.
– Figuras: Insertar → Ilustraciones → Imágenes
Introducción a Excel 33
– Figuras: Insertar → Ilustraciones → Imágenes prediseñadas.
– Imágenes: Insertar → Ilustraciones → Imagen.
– Letras: Insertar → Texto → WordArt.
– Cuadros de Texto: Insertar → Texto → Cuadro de texto
Datos• Dato es cualquier información que se puede utilizar
en una fórmula.
• Los pasos para introducir datos son los mismos que para introducir rótulos:– Seleccionar la celda.
Introducción a Excel 35
– Seleccionar la celda.– Escribir el dato.– Pulsar Entrar o seleccionar otra celda haciendo clic
sobre ella o pulsando las teclas del cursor.
• También se puede usar los iconos en la barra de fórmula para ingresar/modificar los datos.
Datos numéricos• Son los más frecuentes en las hojas de cálculo. Se
debe tener en cuenta que Excel:– Alinea a la derecha con formato general.
– Dispone de gran variedad de formatos.
– Utiliza la notación científica cuando no cabe en la celda.
Introducción a Excel 36
– Utiliza la notación científica cuando no cabe en la celda.
– Para fracciones se escribe la parte entera, se deja un espacio en blanco y a continuación la fracción.
– Para introducir porcentajes se teclea el número seguido del símbolo %.
– Cuando un número no cabe llena la celda con #######
• Ejercicio: Introducir 12345,6789 ; 7,25% y 2 3/5
Ingresando etiquetas o rótulos• Para facilitar la comprensión de las hojas de cálculo,
conviene etiquetar el contenido de las celdas.
• Para introducir un rótulo o etiqueta en una celda:– Seleccionar la celda.
– Escribir el texto del rótulo.
Introducción a Excel 37
– Escribir el texto del rótulo.
– Pulsar Entrar o seleccionar otra celda haciendo clic sobre ella o pulsando las teclas del cursor.
– Si el texto es largo se puede cambiar el ancho de la columna o desde el menú Formato-Columna-Ancho.
Formato L1C1 para celdas• El formato por defecto de las hojas de cálculo es
A1, es decir, columna-fila.• Hay otro formato para hacer referencia a celdas
que es fila-columa y es llamado L1C1. • Para ello se selecciona Herramientas→Opciones
→ General → Estilo de referencia L1C1.
Introducción a Excel 38
• Para ello se selecciona Herramientas→Opciones→ General → Estilo de referencia L1C1.
• Este estilo usa números para identificar las filas y columnas. Ejemplo: L(1)C(2) hace referencia abasoluta a la fila 1, columna 2.
Fórmulas• El éxito de las hojas de cálculo se debe a la
potencia de sus fórmulas.• Para introducir una fórmula:
– Seleccionar la celda– Escribir el signo igual (=)– Escribir la fórmula: operandos y operadores
Introducción a Excel 39
– Escribir la fórmula: operandos y operadores– Pulsar Entrar
• Las fórmulas empiezan con el signo igual (=).• Lo que muestra en la celda es el valor del resultado
de la fórmula.• Los operandos de fórmulas pueden ser referencias
a celdas que contienen los datos. Las celdas de referencia pueden estar en otras hojas o libros.
Ejemplo de fórmula• Ejercicio: Calcular el equivalente en pesetas de
123,45 €.
• Como calculadora manual.
Introducción a Excel 40
• Como hoja de cálculo.
Operadores (I)• Excel incluye operadores aritméticos, de texto, de
comparación y de referencia.
• Operadores aritméticosOperador aritmético Significado Ejemplo
+ (signo más) Suma 3+3
Introducción a Excel 41
+ (signo más) Suma 3+3
– (signo menos) RestaNegación
3–1 –1
* (asterisco) Multiplicación 3*3
/ (barra oblicua) División 3/3 | =A1+B1-C2*A4/A2
% (signo de porcentaje) Porcentaje 20%
^ (acento circunflejo) Exponenciación 3^2
Operadores (II)• Operadores de comparación: el resultado es un
valor lógico, VERDADERO o FALSO
Operador de comparación Significado Ejemplo
= (signo igual) Igual a A1 = B1
> (signo mayor que) Mayor que A1 > B1
Introducción a Excel 42
> (signo mayor que) Mayor que A1 > B1
< (signo menor que) Menor que A1 < B1
>= (signo mayor o igual que) Mayor o igual que A1 >= B1
<= (signo menor o igual que) Menor o igual que A1 <= B1
<> (signo distinto de) Distinto de A1 <> B1
Operadores (III)• Operador de texto o concatenación (&): une o
concatena una o varias cadenas de texto con el fin de generar un solo elemento de texto.
Operador de texto Significado Ejemplo
& ("y" comercial) Conecta o concatena dos ("Viento"&"norte")
Introducción a Excel 43
& ("y" comercial) Conecta o concatena dos valores para generar un valor de texto continuo
("Viento"&"norte")
Operadores (IV)• Operadores de referencia: combina rangos de
celdas para los cálculos con los siguientes operadores.
Operador de referencia
Significado Ejemplo
Introducción a Excel 44
referencia
: (dos puntos) Operador de rango, que genera una referencia a todas las celdas entre dos referencias, éstas incluidas.
B5:B15
; (punto y coma) Operador de unión, que combina varias referencias en una sola
SUMA(B5:B15;D5:D15)
(espacio) Operador de intersección, que genera una referencia a las celdas comunes a las dos referencias
B7:D7 C6:C8
Operadores (Precedencia)Orden de prioridad Operador Descripción
1 − Negación
2 % Porcentaje
3 ^ Exponente
4 * / Multiplicación y división
5 + − Suma y resta
Introducción a Excel 45
• Si una fórmula contiene operadores con la misma prioridad se evaluará de izquierda a derecha.
• Para cambiar el orden de evaluación usar paréntesis.
5 + − Suma y resta
6 & Concatenación
7 = < > <= >= <> Comparación
Ejemplos• Una hoja de cálculo simple (Ejemplo1.xls)
Introducción a Excel 46
• Asignando nombres a las celdas
Fórmulas con datos en más de una hoja
• Excel permite crear fórmulas que operan con datos almacenados en más de una hoja de un mismo libro.
• Ejercicio: Se desea calcular los totales de ingresos y gastos del primer trimestre.– Barra de etiquetas → Insertar hoja de cálculo– Vista → Nueva Ventana | Organizar todo
Introducción a Excel 48
– Vista → Nueva Ventana | Organizar todo
Fórmulas con datos en más de un libro
• Excel permite crear fórmulas que operan con datos almacenados en más de un libro.
• Ejercicio: El grupo G1 tiene dos empresas A y B. Cada una entrega un libro a G1 para consolidar.
Introducción a Excel 49
Formato de celdas (I)
• El formato no afecta al contenido de la celda, sólo a su presentación.
• Si se cambia el formato de la celda, el mismo valor puede significar cosas diferentes.
• Excel presenta el valor de una celda según el formato asignado.
Introducción a Excel 50
significar cosas diferentes.
• También se puede cambiar la apariencia de una celda: tipo de fuente, tamaño, alineación, color, etc.
• Ejemplo: Formato_celdas.xls• Ventana Formato de celdas: Inicio → Celdas → Formato →
Formato de celdas ó Celda → Botón derecho
Formato de celdas (II)• Formato de números.
– General: El contenido se presenta como se ha introducido.
– Número: Adecuado para representar números. Se especifica el número de decimales, separador de miles y números negativos
Introducción a Excel 51
números negativos
– Moneda: Se usa para cantidades monetarias. Se especifica el número de decimales, la moneda y formato de negativos.
– Contabilidad: Igual que el formato moneda, la diferencia es que alinea los números por la coma decimal y el símbolo de moneda.
Formato de celdas (III)• Ejercicio: Introducir en la celda A2 el valor
12345,6789 y en las celdas D2:D5 las fórmulas adecuadas para que tengan igual valor que la celda A2. Dale a cada celda del rango D2:D5 el formato que se indica.
Introducción a Excel 52
que se indica.
Formato de celdas (IV)• Fecha-Hora
– Fecha: número (parte decimal cero) que indica los días transcurridos desde el 1/01/1900 hasta la fecha indicada.
– Hora: fracción decimal (parte entera cero) que tiene como unidad el día (1 equivale a 24 horas).
Introducción a Excel 53
Formato de celdas (V)• Ejercicio: Rellena los datos de tu nacimiento (fecha, hora
y fecha-hora) en introduce en las celdas C2:C4 las fórmulas adecuadas para que su valor sea igual al de las celdas B2:B4. Después dale al rango C2:C4 el formato número con tres posiciones decimales y separador de miles.
Introducción a Excel 54
Formato de celdas (VI)• Otros Formatos:
– Porcentaje: Multiplica el valor de la celda por 100 y añade el símbolo porcentual (%).
– Fracción: Muestra los números en forma de fracción.– Científica: Parte entera y decimal seguido de la letra E
y de un entero que indica el exponente de 10.
Introducción a Excel 55
y de un entero que indica el exponente de 10.– Texto: Se presenta tal como se introduce el texto.– Especial: Se usa para números que representan
determinados datos (código postal y teléfono).– Personalizada: Se escribe el formato que se ajusta a
nuestra necesidades adaptando los códigos predefinidos. Códigos #, 0, ?
Formato de celdas (VI)• Alineación, permite modificar la alineación del
texto, orientación, control y dirección del texto.
• Fuente, permite modificar aspectos del tipo de fuente, estilo, tamaño, subrayado, color y efectos.
• Bordes, permite aplicar diferentes tipos de bordes a
Introducción a Excel 57
• Bordes, permite aplicar diferentes tipos de bordes a cada lado de los bordes de una celda.
• Relleno, permite dar a las celdas distintos tipos de color de fondo y de sombreado.
• Proteger, permite bloquear y ocultar celdas. Requiere activar la opción Revisar → Cambios → Proteger hoja.
Otras opciones de Formato• Copiar formato, permite copiar el formato de una
celda a otras celdas. Se usa el botón
• Formato condicional, permite modificar el formato de la celda dependiendo de su valor.
• Autoformato, es un formato prediseñado que puede
Introducción a Excel 58
• Autoformato, es un formato prediseñado que puede aplicarse rápidamente a un rango de celdas.
• Para ajustar el ancho de una columna (fila):– Método 1: colocar el cursor en el extremo derecho del
encabezado de la columna (fila). El puntero cambia a una cruz. Después con el ratón se arrastra .
– Método 2: usar comando Formato→Columna/Ancho.
Operaciones en bloque de celdas• Muchas operaciones se realizan sobre un bloque o
conjunto de celdas.• Selección de un bloque de celdas:
– Ratón: clic sobre una celda esquina del bloque y arrastrar o manteniendo pulsada la tecla Shifthasta la esquina opuesta.
Introducción a Excel 59
hasta la esquina opuesta. – Si el bloque no es contiguo pulsar la tecla Ctrl.– Toda la hoja: Pulsar en el botón de la esquina superior izquierda.
• Borrar un bloque de celdas:– Primero se selecciona el bloque de celdas y luego se pulsa la tecla Supr.
Operaciones en bloque de celdas• Selección de filas (columnas) enteras:
– Hacer clic sobre el número (letra) que identifica la fila (columna).
– Para seleccionar filas (columnas) contiguas se selecciona la primera y se arrastra el ratón sobre los identificadores de fila (columna).
Introducción a Excel 60
selecciona la primera y se arrastra el ratón sobre los identificadores de fila (columna).
– También se hace clic sobre el primer identificador y manteneniendo pulsada la tecla Shift se hace clic sobre el último identificador.
– Si los bloques no son contiguos mantener pulsada la tecla Ctrl.
Nombres de celdas (I)• Excel permite asignar nombres (propios) a celdas o
bloques de celdas, así como constantes y fórmulas.
• Los nombres se pueden pueden usar en fórmulas.
• El nombre es válido en todo el libro, por tanto no se puede dar el mismo nombre a dos celdas,
Introducción a Excel 61
se puede dar el mismo nombre a dos celdas, rangos, constantes o fómulas diferentes, pero sí se puede dar nombres diferentes.
• La sintaxis de los nombres es similar a las variables de un lenguaje de programación (p.e. Fortran)
Nombres de celdas (II)• Para asignar un nombre a una celda o bloque:
– Seleccionar la celda o rango.– Clic en el interior del Cuadro de nombres.– Tecleamos el nombre.– Pulsamos la tecla Entrar (Intro). ← No olvidar!
Introducción a Excel 62
– Pulsamos la tecla Entrar (Intro). ← No olvidar!
Nombres de constantes o fórmulas• Para asignar un nombre a una constante o fórmula:
Introducción a Excel 63
Copiar• Método I – Copiar y Pegar
– Seleccionar el bloque de celdas.– Seleccionar Copiar en el menú Portapapeles. – Mover el puntero a la esquina superior izquierda de la
nueva ubicación.– Pulsar Enter o seleccionar Pegar en el menú
Introducción a Excel 64
– Pulsar Enter o seleccionar Pegar en el menú Portapapeles. .
• Método II – con el ratón– Seleccionar el bloque de celdas.– Mover el puntero a cualquier borde de las celdas
seleccionadas. – Pulsar la tecla Ctrl y el botón izquierdo del ratón y arrastrar el bloque a la nueva ubicación.
– Soltar el botón del ratón.
Mover• Método I – Cortar y Pegar
– Seleccionar el bloque de celdas.– Seleccionar Cortar en el menú Portapapeles. – Mover el puntero a la esquina superior izquierda de la
nueva ubicación.– Pulsar Enter o seleccionar Pegar en el menú
Introducción a Excel 65
– Pulsar Enter o seleccionar Pegar en el menú Portapapeles.
• Método II – con el ratón– Seleccionar el bloque de celdas.– Mover el puntero a cualquier borde de las celdas
seleccionadas. – Pulsar el botón izquierdo del ratón y arrastrar el bloque
a la nueva ubicación.– Soltar el botón del ratón.
Insertar• Celdas
– Seleccionar la celda o bloque de celdas donde se desea insertar.
– Ejecutar Inicio → Celdas → Insertar. Se abre la ventana Insertar celdas.
Introducción a Excel 66
la ventana Insertar celdas.– Seleccionar la opción que interesa. Pulsar
Aceptar.
• Filas (columnas)– Seleccionar la fila(s) (columna(s)) donde se desea insertar.
– Ejecutar Insertar → Filas (Columnas).
Eliminar - Deshacer• Usar el comando Inicio → Celdas → Eliminar
– Seleccionar la celda o bloque de celdas que se desea eliminar.
– Ejecutar Edición → Eliminar. Se abre la ventana Eliminar celdas.
Introducción a Excel 67
– Seleccionar la opción que interesa. Pulsar Aceptar. – En el caso de seleccionar filas (columnas) se elimina
directamente la selección.
• Para deshacer los cambios– Seleccionar Deshacer en el menú Edición. – O el icono Deshacer en la barra estándar.
Introducir datos repetidos• Escribir el mismo dato en varias celdas de una hoja:– Seleccionar las celdas.– Introducir el dato.– Pulsar simultáneamente las teclas Ctrl - Entrar.
Introducción a Excel 68
– Pulsar simultáneamente las teclas Ctrl - Entrar.
• Escribir el mismo dato en varias hojas de un libro:– Seleccionar las hojas del libro (clic sobre su etiqueta y mantener pulsada la tecla Ctrl sobre el resto de etiquetas).
– Teclear los datos sobre una de ellas.
Serie de datos o fechas• Series de datos consecutivos mediante el controlador de relleno– Introducir el primer dato de la serie en una celda y situar
el cursor sobre el controlador de relleno de la celda. Cuando adopte la forma + hacer clic con el botón izquierdo del ratón y arrastrar en la dirección vertical u
Introducción a Excel 69
izquierdo del ratón y arrastrar en la dirección vertical u horizontal que interesa.
– Situar el puntero sobre el cuadro y hacer clic para desplegar el cuadro de opciones.
– Hacer clic sobre la opción que interesa.
Listas personalizadas• Se puede configurar con el comando Botón
Microsoft Office → Opciones de Excel →Más frecuentes → Modificar listas personalizadas.
Introducción a Excel 70
Series de datos lineales• Con el controlador de relleno
– Introducir los dos primeros datos de la serie en dos celdas contiguas de una fila (o columna).
– Seleccionar dichas celdas.
– Arrastrar el controlador de relleno horizontalmete
Introducción a Excel 71
– Arrastrar el controlador de relleno horizontalmete (o verticalmente).
• Con el comando Edición→Rellenar→Series…
Referencias de celda (I)• Cuando se crea una fórmula se puede utilizar tres tipos de
referencia a celdas y a rangos de celdas:– Referencias relativas, cuando al copiar la fórmula cambia la fila y
la columna.– Referencias absolutas, cuando al copiar la fórmula no cambia la
fila ni la columna.– Referencias mixtas, cuando al copiar la fórmula cambia la fila
Introducción a Excel 72
– Referencias mixtas, cuando al copiar la fórmula cambia la fila (columna) y permanece fija la columna (fila).
• Referencias relativas– Son las más frecuentes y son las que se usa por defecto.– Cuando se copia una fórmula, Excel actualiza automáticamente la
fila y la columna ajustándolas a las de la celda en la que se ha hecho la copia.
– Si en C1 hay una fórmula = A1+B1 y se copia en la celda C2, en la celda C2 la formula cambia automáticamente a: = A2+B2.
Referencias de celda (II)• Referencias absolutas
– Se requieren cuando se necesita que una referencia a celda o rango no cambie al copiar la fórmula.
– Para indicar referencia absoluta se antepone un signo dólar ($) a la letra que indica la columna y otro signo dólar ($) al número que indica la fila.
– Ejemplo: Si en C1 hay una fórmula = $A$1+$B$1 y se copia en la
Introducción a Excel 73
– Ejemplo: Si en C1 hay una fórmula = $A$1+$B$1 y se copia en la celda C2, en la celda C2 la fórmula no cambia.
• Referencias mixtas– Se requieren cuando al copiar la fórmula no cambie la fila o
columna.– Tienen una parte de referencia absoluta, que no cambia al copiar, y
otra parte de la referencia relativa, que sí cambia al copiar.– Se antepone el signo dólar ($) a la parte absoluta (fija).– Se puede usar la tecla F4 para introducir las referencias absolutas.
Validación de datos (I)• La protección de celdas es útil para impedir que el
usuario modifique fórmulas, rótulos, etc.• Para controlar la entrada de datos es útil la
validación de datos. Así se restringe el tipo de datos, fija límites, establece valores de una lista, utiliza una fórmula, visualiza un mensaje al
Introducción a Excel 74
utiliza una fórmula, visualiza un mensaje al seleccionar una celda y un mensaje de error al introducir un dato erróneo.
• Para establecer un criterio de validación de datos:– Seleccionar una celda o rango.– Ejecutar comando Datos→Validación…
Otras opciones de Edición• Guardar la hoja de cálculo
– Hay varias formas. La más común es pulsar el icono Guardar en la barra de herramientas estándar.
• Recuperar la hoja de cálculo– Seleccionar Abrir en la ventana obtenida obtenida al
pulsar en el Botón de Office.
Introducción a Excel 76
pulsar en el Botón de Office.
• Impresión de la hoja de cálculo– Para imprimir toda la hoja seleccionar Imprimir en la
ventana obtenida obtenida al pulsar en el Botón de Office. Luego seleccionar la opción Hojas activas.
– Para imprimir una parte se debe seleccionar el bloque de celdas previamente y escoger Selección.
Más opciones• Mostrar fórmulas de las celdas
– Seleccionar Herramientas→Opciones y en la pestaña Ver seleccionar Fórmulas.
• Creación y ejecución de Macros– Una macro es una serie de acciones consecutivas de
teclas y/o ratón que se guarda para una ejecución posterior. Permite la automatización de una serie de
Introducción a Excel 77
posterior. Permite la automatización de una serie de instrucciones.
– Cada macro se guarda con un nombre y tiene una combinación de teclas.
– Para grabar una macro seleccionar Herramientas→Macro→Grabar nueva macro. Después Detener.
– Las macros se guardan en programas VBA.
Funciones (I)• Las funciones son fórmulas predefinidas que
ejecutan cálculos utilizando valores específicos (argumentos).
• Características de las funciones:– Estructura: Una función comienza por el nombre de la
función, seguido de un paréntesis de apertura, los
Introducción a Excel 79
función, seguido de un paréntesis de apertura, los argumentos de la función separados por comas y un paréntesis de cierre. Ejm: SUMA(C1,C2,C3)
– Argumentos: Los argumentos pueden obligatorios u opcionales. Pueden ser constantes, fórmulas u otras funciones Los tipos de dato pueden ser números, texto, valores lógicos, valores de error (p.e. #N/A), fechas o referencias de celda.
Introducción de Funciones (II)• Directamente, cuando se conoce la sintaxis de la
función.
• Asistente para funciones, seleccionar Insertar→Función en la barra de herramientas estándar o en la barra de fórmulas.
Introducción a Excel 80
estándar o en la barra de fórmulas.
Funciones (II)• La opción Ayuda sobre esta función ofrece una
explicación, sintaxis, observaciones y ejemplos de cada función.
Introducción a Excel 81
Más sobre Funciones (III)• Se puede introducir funciones anidadas con el
asistente para funciones.
• Las funciones Suma, Promedio, Cuenta, Máx y Mín se usan con mucha frecuencia y por ello están disponibles en el botón Autosuma de la pestaña
Introducción a Excel 82
disponibles en el botón Autosuma de la pestaña Fórmulas.
Funciones Lógicas• SI comprueba si se cumple una condición y
devuelve un valor si se evalúa como VERDADERO y otro valor si se evalúa como FALSO.
Introducción a Excel 84
Permiten crear funciones lógicas:
• Y• O• NO
Funciones Fecha y Hora• Excel representa los días mediante números
enteros secuenciales llamados números de serie de fecha.
• Para representar la hora usa la parte decimal del número de serie. Excel usa las fechas como números en fórmulas y funciones.
Introducción a Excel 85
números en fórmulas y funciones.• HOY, AHORA• DIA, MES, AÑO• DIASEM, FECHA, DIAS360• HORA, MINUTO, SEGUNDO• NSHORA, FECHA.MES
Funciones Búsqueda y Referencia• Las funciones de búsqueda permiten localizar un
valor en una lista y extraer información de una tabla o matriz de datos.
• BUSCARV, BUSCARH , BUSCAR• COINCIDIR, INDICE
Introducción a Excel 86
• COINCIDIR, INDICE• TRANSPONER (operación matricial)
Funciones Financieras• Excel incorpora funciones que permiten resolver la
mayoría de problemas financieros de las empresas y de evaluación de proyectos.
• PAGO, PAGOINT, PAGOPRIN• VA, VF, NPER, TASA
Introducción a Excel 87
• VA, VF, NPER, TASA• VNA, TIR
Funciones Matemáticas y trigonométricas
• Excel ofrece un repertorio de funciones matemáticas y trigonométricas amplio.
• PI, SUMA, PRODUCTO, POTENCIA, RESIDUO, ABS, SIGNO, RAIZ, NUMERO.ROMANO, SUMAR.SI, SUMAPRODUCTO, REDONDEAR, TRUNCAR, ENTERO, COMBINAT, M.C.D., M.C.M.
Introducción a Excel 88
TRUNCAR, ENTERO, COMBINAT, M.C.D., M.C.M.• EXP, LN, LOG, LOG10, ALEATORIO• ACOS, ASENO, ACOSH, ASENOH, ATAN, ATAN2,
ATANH, COS, COSH, GRADOS, RADIANES, SENO, SENOH, TAN, TANH
• MDETERM, MINVERSA, MMULT
Funciones Estadísticas• Excel ofrece un repertorio de funciones estadísticas
amplio.
• CONTAR, CONTAR.BLANCO, CONTARA, CONTAR.SI, FRECUENCIA, MIN, MAX, MEDIANA, MODA, PROMEDIO.
Introducción a Excel 89
MEDIANA, MODA, PROMEDIO.• PENDIENTE, INTERSECCION.EJE,
ESTIMACION.LINEAL, COEF.DE.CORREL• DESVEST, DISTR.BINOM, DISTR.NORMAL,
PRUEBA.F, VAR
Funciones Información y Texto• Las funciones de información permiten comprobar
el tipo de dato de un valor o de una referencia. Útiles para comprobar el tipo de resultado que se ha obtenido de un cálculo. Normalmente se usan combinadas con la función SI.
• Las funciones de texto permite realizar operaciones
Introducción a Excel 90
• Las funciones de texto permite realizar operaciones sobre el texto.
• ESBLANCO, ESERROR, ESNUMERO, ESTEXTO, TIPO.
• CONCATENAR, DECIMAL, MONEDA, TEXTO, MAYUS, MINUSC, NOMPROPIO, VALOR.
Funciones de Ingeniería• Las funciones de ingeniería incluyen funciones
propias de cálculos de ingeniería.
• BESSELI, BESSELJ, BESSELK, BESSELY• BIN.A.DEC, BIN.A.HEX, DEC.A.BIN,
DEC.A.HEX
Introducción a Excel 91
DEC.A.HEX• COMPLEJO, IM.ABS, IM.ANGULO,
IM.CONJUGADA, IM.DIV, IM.EXP, IM.LN, IM.PRODUCT, IM.POT, IM.RAIZ2, IM.SUM, IM.SUSTR, IMAGINARIO
Gráficos de datos• La obtención de gráficos de datos es la tarea más
común realizada con una hoja de cálculo.
• Excel dispone de un asistente para gráficos para crear con facilidad gráficos que muestran la información de modo claro y atractivo.
Introducción a Excel 93
información de modo claro y atractivo.
• Conceptos para crear gráficos:– Cada dato en la hoja se representa en el gráfico
mediante un marcador de dato.
– Cada conjunto de datos constituye una serie de datos.
– Cada componente de una serie de datos constituye una categoría.
Elementos de los gráficos1. El área del gráfico.2. El área de trazado del gráfico.3. Los puntos de datos de la serie
de datos que se trazan en el gráfico.
4. Los ejes horizontal (categorías) y vertical (valores) en los que se
Introducción a Excel 95
y vertical (valores) en los que se trazan los datos del gráfico.
5. La leyenda del gráfico.6. Un título de eje y de gráfico que
puede agregar al gráfico.7. Una etiqueta de datos que puede
usar para identificar los detalles de un punto de datos de una serie de datos.
Hojas de gráfico y Gráfico Incrustado • Hoja de gráfico, es una hoja de cálculo que
contiene únicamente un gráfico.
• Gráfico incrustado, se considera como un objeto gráfico situado en la hoja.
Introducción a Excel 96
Tipos de gráfico (I)• Tipos estándar:
– Columna y Barra, adecuados para comparar categorías.
– Línea, apropiado para mostrar la tendencia de una serie de valores medidos a intervalos regulares de tiempo.
– Circular, usados para representar las distintas partes que
Introducción a Excel 97
– Circular, usados para representar las distintas partes que componen un total.
– Área, iguales a los de líneas, pero rellenan los espacios comprendidos entre las líneas que representan los valores.
– XY (dispersión), adecuado para representar pares de valores.
Tipos de gráfico (II)• Tipos estándar:
– Cotizaciones, gráficos específicos para representra cotizaciones de valores bursátiles.
– Superficie, crea superficies 3D o curvas de nivel en superficies.
Introducción a Excel 98
– Anillos, equivalente al gráfico circular, pero adaptado para representar varias series de datos.
– Burbujas, similar al de dispersión pero con un valor adicional para tamaño del marcador.
– Radial, radial con marcadores en cada valor de datos.
Subtipos de gráfico (I)Columna
Línea
Introducción a Excel 99
Línea
Circular
Barra
Área
XY (dispersión)
Subtipos de gráfico (II)Cotizaciones
Superficie
Anillos
Introducción a Excel 100
Anillos
Burbuja
Radial
Ejemplos de gráficos de datos• Gráfico de barras (Ejemplo1.xls).• Gráfico X-Y (Graficos.xls)
El voltaje en un condensador varía con el tiempo según la fórmula V= 10 e-0.5t
donde: V representa el voltaje y
Introducción a Excel 101
donde: V representa el voltaje y t el tiempo en segundos.
– Mostrar los datos con una precisión de tres decimales.
– Preparar un gráfico para el rango de 0 a 10 segundos.
– Etiquetar el gráfico para que sea legible.
Ejemplos de gráficos de datos• Gráfico Semilog X-Y (Graficos.xls)
– Hacer doble click sobre el eje o pulsar botón derecho del ratón y seleccionar Formato de ejes.
– En la pestaña Escala seleccionar Escala logarítmica.
Introducción a Excel 102
logarítmica.
Ejemplos de gráficos de datos• Gráfico Log-Log X-Y (Graficos.xls)
– Construir una hoja para calcular el área (A=4πr2) y volumen de una esfera (V=4/3 πr3) para r = rango de 1-10 en incrementos de 1.
– Graficar el área y volumen en gráficos tipo X-Y
Introducción a Excel 103
– Graficar el área y volumen en gráficos tipo X-Y y log-log
Gráficos– Ejes múltiples• Problema: Graficar varias series de datos con
diferentes ordenes de magnitud.
• Ejemplo: Ejes_multiples.xls
• Una opción es usar un eje secundario. Para ello seleccionar la serie y con el botón derecho del
Introducción a Excel 104
seleccionar la serie y con el botón derecho del ratón seleccionar Formato de serie de datos. En la pestaña Eje seleccionar Eje secundario.
• Otra opción puede ser pasar los datos a escala similar multiplicando (o dividiendo) por un factor de escala (múltiplo de 10).
Gráficos– Ejes múltiples• Como Excel sólo admite un eje secundario, una opción adicional es crear un eje falso.
• Para crear el eje falso se mantiene una de las coordenadas constante. Es necesario editar manualmente los valores de Y.
Introducción a Excel 105
manualmente los valores de Y.
• Se puede añadir elementos gráficos (flechas) y texto a partir de la barra de dibujo en el gráfico.
Gráficos– tipo radial• Problema: A partir de un gráfico creado cambiarlo
sin partir de cero. • Ejemplo: Ejes_multiples.xls• Seleccionar el gráfico con el botón derecho del
ratón y cambar el tipo de gráfico a Radial.
Introducción a Excel 107
ratón y cambar el tipo de gráfico a Radial.• El tipo Radial exige que los datos de X estén
espaciados uniformemente. El espacio entre las líneas de división siempre es uniforme y habrá tantos como datos.
• Modificar el Formato de líneas de división para representar adecuadamente los valores.
Gráficos– Superficies 3D• Problema: Crear un gráfico de superficie 3D para
mostrar los resultados de un estudio de optimización multidimensional o mostrar datos topográficos.
• Ejemplo: Ejes_multiples.xls• Se utilizará el tipo de gráfico Superficie.
Introducción a Excel 109
• Se utilizará el tipo de gráfico Superficie.• Se requiere que los datos X e Y estén espaciados
uniformemente. Se puede agregar una leyenda con un rango de colores apropiado para el usuario.
• Se puede representar el gráfico como malla de alambres o colores. Excel permite cambiar el punto de vista 3D.
Gráficos– Superficies 3D• Ejemplo: gráfico de la función
para k=1.25 k
yx
yxZ ⋅
+
+−=
22
22 )cos(1
Introducción a Excel 110
Gráficos– Contorno• Problema: Crear un gráfico tipo contorno (curvas
de nivel) para un mapa con la elevación del terreno, un mapa de presiones u otra variable distribuída en una cuadrícula espaciada uniformemente.
Introducción a Excel 111
uniformemente.
• Ejemplo: Ejes_multiples.xls
• Se utilizará el tipo de gráfico Superficie, opción Contorno.
• Se recomienda mostrar la leyenda y usar una escala apropiada para mostrar los datos de interés.
Gráficos– Combinar tipos• Problema: Mostrar distintas series de datos en el mismo gráfico con diferentes estilos.
• Ejemplo: Ejes_multiples.xls
• Se fija el estilo de gráfico de cada serie por
Introducción a Excel 113
• Se fija el estilo de gráfico de cada serie por separado. Para ello se selecciona la serie con el botón derecho del ratón y se selecciona el tipo de gráfico adecuado.
Gráficos– Anotaciones• Problema: Crear anotaciones y adornos en los gráficos para añadir información.
• Ejemplo: Ejes_multiples.xls
• Se utiliza las herramientas de dibujo
Introducción a Excel 115
• Se utiliza las herramientas de dibujo disponibles en Ver→Barras de Herramientas → Dibujo colocando los elementos gráficos sobre el gráfico y se les da el formato adecuado. También se puede usar sobre la hoja de cálculo.
Análisis estadístico de datos• Excel dispone de un amplio conjunto de funciones
estadísticas predeterminadas para ser usadas en las hojas de cálculo.
• El complemento Herramientas para Análisis también cuenta con varias herramientas estadísticas más, que permiten visualizar los datos y hacer que
Introducción a Excel 118
más, que permiten visualizar los datos y hacer que Excel sea adecuado para determinados análisis.
• En la ayuda de Excel hay descripciones y sintaxis para todas las funciones estadísticas.
• Este apartado muestra el uso de Excel para realizar cálculos estadísticos estándar, sin entrar en la teoría del análisis.
Análisis estadístico – Resumen• Problema: Calcular un resumen de estadísticas
(medidas de tendencia general y dispersión) de una serie de datos.
• Ejemplo: Analisis_estadistico_datos.xls
• Una opción es usar las funciones estadísticas de
Introducción a Excel 119
• Una opción es usar las funciones estadísticas de Excel, tales como: PROMEDIO, MEDIANA, MODA, MIN, MAX, VAR (Varianza), DESVEST (Desviación estándard)
• Otra opción es usar los complementos de Análisis de datos →Estadística descriptiva
Análisis estadístico -Ejemplo
• Ejemplo: Se tiene una serie de datos de muestra de un control de calidad, calcular la Mediana, Promedio, Moda, Mínimo, Máximo, Desviación Estándar y Varianza de la serie.
Introducción a Excel 120
Estándar y Varianza de la serie.
• Usar funciones estadísticas y la opción de Estadística descriptiva del Análisis de Datos.
Análisis estadístico – Histograma• Problema: Crear un histograma (gráfico de frecuencia) de un conjunto de datos.
• Ejemplo: Analisis_estadistico_datos.xls
• Una opción es usar la función
Introducción a Excel 123
• Una opción es usar la función FRECUENCIA, para calcular la frecuencia de incidencia de cada punto de referencia y después graficarla.
• Otra opción es usar los complementos de Análisis de datos →Histograma.
Análisis estadístico - Frecuencia• Se debe establecer un rango de valores (bin)
• Para calcular las frecuencias de cada clase se selecciona el rango de salida y se aplica la función matricial (pulsar Control-Mayús-Intro después de introducir la función)
Introducción a Excel 124
Intro después de introducir la función) FRECUENCIA poniendo el rango de los datos y el rango de clases.
• Ejm: ={FRECUENCIA(DATA,RANGE)}
Análisis de datos - Histograma• Otra opción para obtener el histograma de un
conjunto de datos es utilizar Análisis de datos →Histograma
• En el rango de entrada se selecciona el rango de celdas que contienen el DataSet de entrada. En el campo Rango de Clases se introduce o selecciona
Introducción a Excel 127
campo Rango de Clases se introduce o selecciona el rango de celdas que contienen los valores de clases.
• En las opciones de salida, se selecciona la ubicación de salida de los resultados. Se puede seleccionar la opción de Porcentaje acumulado y Crear gráfico.
Análisis estadístico – ANOVA• Problema: Realizar un análisis de varianza
(ANOVA). • Ejemplo: Analisis_estadistico_datos.xls• En las Análisis de datos se ofrecen tres clases de
ANOVA: de un factor, de dos factores con una sola muestra por grupo y de dos factores con varias
Introducción a Excel 129
ANOVA: de un factor, de dos factores con una sola muestra por grupo y de dos factores con varias muestras por grupo.
• Para el ejemplo, se realiza un ANOVA para dos o más grupos por lo que se usa ANOVA de un factor para probar la hipótesis de que no hay diferencia entre las medias de esos grupos.
Análisis estadístico – ANOVA• Se selecciona Análisis de datos → Análisis de
varianza de un factor• En el rango de entrada se selecciona el rango de los
datos (dataset) que deben estar en columnas contiguas. Si se incluye los rótulos de la columna, marcar la opción Rótulos en la primera fila.
Introducción a Excel 130
marcar la opción Rótulos en la primera fila. • Se deja el parámetro Alfa en 0.05, pero se puede
modificar.• Se selecciona la opción de salida y Aceptar.• Para el ejemplo, como P < Alfa proporcionado y F
> F crítico implica que se debería rechazar la hipótesis nula de que no hay diferencias significativas entre las medias de los grupos.
Análisis estadístico – Aleatorios• Problema: Calcular números aleatorios entre
límites superiores e inferiores específicos.• Ejemplo: Analisis_estadistico_datos.xls.• Usar la función ALEATORIO de Excel, que
genera un número aleatorio entre 0 y 1.
Introducción a Excel 133
genera un número aleatorio entre 0 y 1.• Se puede crear una serie de números aleatorios
seleccionando las celdas, poniendo la función ALEATORIO y pulsando Ctrl-Mayús-Enter.
• Si se quiere un número aleatorio que no cambie cada vez que se calcula la hoja, pulsar F9 después de introducir la fórmula.
Análisis estadístico – Aleatorios• Para calcular un número (serie) aleatorio entre dos valores específicos utilizar la fórmula
{=ALEATORIO()*(MaxVal-MinVal)+MaxVal}
Introducción a Excel 134
• Se puede redondear lo anterior.
• Se puede usar también la fórmula ALEATORIO.ENTRE
Ajuste de ecuaciones a datos• Es muy común en ingeniería intentar hallar la
ecuación (curva) que mejor aproxime un conjunto de datos.
• Datos: pares de puntos P1=(x1,y1)… Pn=(xn,yn) o tuplas de variables independientes y dependientes.
• Se trata de pasar una curva a través del conjunto de
Introducción a Excel 137
• Se trata de pasar una curva a través del conjunto de datos. Cuando los resultados se usan para hacer nuevas predicciones de variables dependientes, se conoce como regresión.
• Se usa el método de mínimos cuadrados: se fundamenta en la minimización del error ei = yi – f(xi) obtenido para cada punto.
• Ejemplos: Ajuste_curvas.xls
Ajuste lineal por MMC a datos (1)• Si la función de ajuste es una línea recta y = mx + b entonces el MMC permite deducir los coeficientes a y b a partir de la resolución de las siguientes ecuaciones:
∑∑ =+
nn
∑∑∑nnn
Introducción a Excel 138
• Bondad de ajuste (coef. Correlación):
positiva 1 o negativa -1
∑∑==
=+
n
i
i
n
i
i ybnxm11
∑∑∑===
=+
n
i
ii
n
i
i
n
i
i yxxbxm111
2
SST
SSEr −=12
∑=
−=
n
i
ii xfySSE1
2)]([ ∑=
−=
n
i
i yySST1
2][
Ajuste lineal por MMC a datos (1)Ajuste mediante MMC de una serie
1.0
1.5
2.0
Introducción a Excel 139
-2.0
-1.5
-1.0
-0.5
0.0
0.5
-3.0 -2.0 -1.0 0.0 1.0 2.0 3.0
Y
estimado
Ajuste lineal por MMC a datos(2)• Otro método rápido de obtener un ajuste lineal (y de otro
tipo) a un conjunto tabulado en columnas de datos x (variable independiente) e y (variable dependiente) es:
• Graficar los datos como tipo de gráfico X-Y (dispersión) como puntos.
• Pulsar en uno de los puntos dato para seleccionar como
Introducción a Excel 140
• Pulsar en uno de los puntos dato para seleccionar como objeto activo el conjunto de datos y pulsar el botón derecho del ratón para obtener el menú Gráfico.
• Seleccionar Añadir Línea de Tendencia en el menú Gráfico. Especificar el tipo de curva (Lineal) y llenar las opciones correspondientes. Conviene seleccionar en Opciones Presentar ecuación en el gráfico y el valor R (coeficiente de correlación). Es posible realizar extrapolación.
Ajuste lineal por MMC a datos(2)Fuerza ejercida por un resorte y = 0.5147x + 1.2794
R2 = 0.9577
8
10
12
Fue
rza,
N
Introducción a Excel 142
0
2
4
6
0 2 4 6 8 10 12 14 16 18
Desplazamiento desde la posición de equilibrio, cm
Fue
rza,
N
Ajuste lineal por MMC a datos(2)Par vs. Desviación (grados) y = 0.8087x + 0.0074
R2 = 0.9985
1.0
1.5
2.0
Introducción a Excel 143
-2.0
-1.5
-1.0
-0.5
0.0
0.5
-3.0 -2.0 -1.0 0.0 1.0 2.0 3.0
Desviación (grados)
Lineal (Desviación(grados))
Ajuste lineal de datos(3)• Otro método rápido de obtener un ajuste lineal es
utilizar la función ESTIMACION.LINEAL a un conjunto tabulado en columnas de datos x (variable independiente) e y (variables dependientes).
• La sintaxis de la función corresponde a una fórmula matriz (hay que pulsar Ctrl-Mayus-Entrar)
Introducción a Excel 144
fórmula matriz (hay que pulsar Ctrl-Mayus-Entrar) cuando se introduce la fórmula. Ejemplo:
{=ESTIMACION.LINEAL(C5:C13, A5:A13, VERDADERO, VERDADERO)}
Y X Para que calcule la intersección y las
estadísticas ampliadas
Ajuste multilineal de datos(4)• Para hacer un ajuste lineal múltiple del tipo
y = m1x1 + m2x2 + m3x3 + … mnxn + btambién se puede utilizar la función ESTIMACION.LINEAL a un conjunto tabulado en columnas de datos x (variables independientes) e y (variables dependientes).
Introducción a Excel 146
• Es necesario seleccionar una cuadrícula de celdas de tamaño n+1 columnas, donde n es el número de variables independientes (x) y 5 filas.
• La sintaxis de la función es de tipo matriz (hay que pulsar Ctrl-Mayus-Entrar) cuando se introduce la fórmula. Ejemplo:
{=ESTIMACION.LINEAL(B12:B27,C12:H27,VERDADERO,VERDADERO)}
Ajuste multilineal de datos(4)
64000
66000
68000
70000
72000
y
Introducción a Excel 147
54000
56000
58000
60000
62000
64000
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
y
y-est
Regresión• Un método adicional para obtener un análisis completo
(lista de residuos) de regresión es usando la opción de Regresión en Análisis de datos. Se procede de manera similar que para la obtención de histogramas.
• Ejemplo
Introducción a Excel 148
Otros tipos de ajuste• Exponencial
• Potencial
• Polinómico: es necesario dar el orden del polinomio– Ejemplos
Introducción a Excel 150
Selección de la mejor curva de ajuste a un conjunto de datos
• Método de prueba y error. Primero se grafican los datos como una línea recta.
• Si no se obtiene un buen ajuste, intentar diferentes tipos de curvas, usando evaluación visual ayudado por los resultados de la suma de cuadrados de los errores y el coeficiente de correlación (r2).
• Si no se obtienen resultados satisfactorios, intentar graficar los datos de otra manera (y – 1/x, 1/y-x, etc.)
Introducción a Excel 151
datos de otra manera (y – 1/x, 1/y-x, etc.)• En algunos casos se consiguen mejores ajustes escalando los
datos (datos de x e y del mismo orden de magnitud).• Cambio de escala (se obtiene una recta) para el paso 2:
– Exponencial y = a ebx log y vs. x (semi-log)– Logarítmico y = a ln x + b y vs. log x (semi-log)– Potencial y = a xb log y vs. log x (log – log)
Ajuste exponencial de datos
Tasa de descarga de un condensador y = 10e-0.5x
R2 = 1
8
10
12
Tasa de descarga de un condensador y = 10e-0.5x
R2 = 1
1
10
Introducción a Excel 152
0
2
4
6
8
0 2 4 6 8 10 12
Tiempo, segundos
Vol
tios
0
0
1
0 2 4 6 8 10 12
Tiempo, segundosV
oltio
s
Ajuste logarítmico de datos
Temperatura vs.
profundidad en un reactor
y = 2.9941Ln(x) + 28.036
R2 = 1
45
50
55
Tem
pera
tura
ºC
Temperatura vs.
profundidad en un reactor
y = 2.9941Ln(x) + 28.036
R2 = 1
45
50
55
Tem
pera
tura
ºC
Introducción a Excel 153
20
25
30
35
40
45
0 500 1000 1500 2000 2500
Distancia cm.
Tem
pera
tura
ºC
20
25
30
35
40
45
0.1 1 10 100 1000 10000
Distancia cm.
Tem
pera
tura
ºC
Ajuste potencial de datos
Tasa Reacción vs Concentración y = 0.0018x1.599
R2 = 1
2.5
3.0
3.5
TR
, m
ole
s/s
ec
Tasa Reacción vs Concentración y = 0.0018x1.599
R2 = 1
1.0
10.0
Introducción a Excel 154
0.0
0.5
1.0
1.5
2.0
2.5
0 20 40 60 80 100
C, moles/cu ft
TR
, m
ole
s/s
ec
0.0
0.0
0.1
1.0
1 10 100
C, moles/cu ftT
R, m
ole
s/s
ec
Ajuste polinomial de datosTiempo de aceleración vs Veloc.Max.
y = 1E-08x5 - 5E-06x4 + 0.0008x3 - 0.0525x2 + 1.777x - 20.958
R2 = 0.9998
30
35
40
45
50T
iem
po
, seg
Introducción a Excel 155
0
5
10
15
20
25
30
0 20 40 60 80 100 120 140 160
Velocidad máx. Km/h
Tie
mp
o, seg
Análisis de series de tiempo• El análisis de series de tiempo es un campo de estudio amplio con aplicaciones en ingeniería, economía, ciencias sociales, etc.
• Excel dispone de funciones y herramientas para realizar tales análisis, así como
Introducción a Excel 156
para realizar tales análisis, así como facilidades de visualización de resultados y realizar predicciones.
• Ejemplos: Ajuste_curvas.xls
Análisis de series de tiempo -Visualización
• Problema: Graficar un grupo de datos de series de tiempo para análisis posteriores.
• Se utiliza el asistente para gráficos.
Introducción a Excel 157
Análisis de series de tiempo -Visualización
Temperatura anual (Los Angeles)
67.0
67.5
68.0
68.5
69.0
Tem
pera
tura
ºF
Introducción a Excel 158
64.0
64.5
65.0
65.5
66.0
66.5
67.0
1920 1930 1940 1950 1960 1970 1980 1990 2000 2010
Año
Tem
pera
tura
ºF
Análisis de series de tiempo -tendencia
• Problema: Agregar una línea de tendencia a una serie de tiempo.
• Se puede utilizar la opción de Agregar línea de tendencia cuando se crea un gráfico.
Introducción a Excel 159
Análisis de series de tiempo -tendencia
Temperatura anual (Los Angeles) y = -0.0105x + 87.231
R2 = 0.0498
67.0
67.5
68.0
68.5
69.0
Tem
pera
tura
ºF
Introducción a Excel 160
64.0
64.5
65.0
65.5
66.0
66.5
67.0
1920 1930 1940 1950 1960 1970 1980 1990 2000 2010
Año
Tem
pera
tura
ºF
Análisis de series de tiempo -tendencia
Temperatura anual (Los Angeles) y = 0.0011x2 - 4.2275x + 4231
R2 = 0.219
67.0
67.5
68.0
68.5
69.0
Tem
pera
tura
ºF
Introducción a Excel 161
64.0
64.5
65.0
65.5
66.0
66.5
67.0
1920 1930 1940 1950 1960 1970 1980 1990 2000 2010
Año
Tem
pera
tura
ºF
Análisis de series de tiempo –medias móviles
• Problema: Suavizar una serie de tiempo mediante medias móviles.
• Se puede calcular medias móviles de varias formas: usando la función de gráficos Media móvil de la línea de tendencia o usando la
Introducción a Excel 162
móvil de la línea de tendencia o usando la función Media móvil de las Herramientas→Análisis de datos.
Análisis de series de tiempo –medias móviles línea de tendencia• Para la Línea de tendencia, se selecciona la serie de datos y haciendo clic con el botón derecho del ratón se selecciona Agregar línea de tendencia.
• Se selecciona Media móvil y el período
Introducción a Excel 163
• Se selecciona Media móvil y el período deseado (3). En opciones se puede escribir un nombre para esta nueva línea de tendencia.
• Es sencillo pero no genera datos numéricos.
Análisis de series de tiempo -medias móvilesTemperatura anual (Los Angeles)
67.0
67.5
68.0
68.5
69.0
Tem
pera
tura
ºF
Introducción a Excel 164
64.0
64.5
65.0
65.5
66.0
66.5
67.0
1920 1930 1940 1950 1960 1970 1980 1990 2000 2010
Año
Tem
pera
tura
ºF
Temperatura anual media Media móvil de 3 años
Análisis de series de tiempo –medias móviles Análisis de datos• En Herramientas→Análisis de datos hay una
opción que es Media móvil.
• La ventaja es que genera datos numéricos para la serie de media móvil.
• En la ventana de diálogo se selecciona el rango de
Introducción a Excel 165
• En la ventana de diálogo se selecciona el rango de las celdas que contiene la serie de datos.
• Se introduce el intervalo (3) sobre el que se desea calcular las medias.
• Introducir la celda donde se desea colocar los resultados.
Análisis de series de tiempo -medias móviles Análisis de datos
Media móvil
67.068.069.070.0
Va
lor Real
Introducción a Excel 166
62.063.064.065.066.067.0
1 6 11 16 21 26 31 36 41 46 51 56 61 66
Punto de datos
Va
lor Real
Pronóstico
Análisis de series de tiempo –índices estacionales
• Problema: calcular los índices estacionales de una serie de tiempo que muestra variaciones estacionales.
• Hay varios métodos para calcular el índice estacional de una serie. Aquí se muestra el método promedio-porcentaje.
Introducción a Excel 167
promedio-porcentaje. • Primero se calcula el promedio de la variable cada
año.• Despúes se calcula el porcentaje de cada mes
respecto al promedio anual.• Finalmente se calcula el promedio de los
porcentajes de cada mes para todos los años. Para comprobar el promedio de los índices debe ser 1.
Análisis de series de tiempo -índices estacionales
1996 1997 1998 1999
Ene 49.1 49.2 53.5 54.0Feb 53.0 53.9 53.6 57.9Mar 54.7 63.8 58.1 58.6Abr 64.5 62.2 64.7 70.9May 76.7 72.4 77.0 73.7Jun 79.1 78.7 83.5 79.9Jul 82.2 83.1 85.5 82.2Ago 80.2 81.3 83.8 85.0Sep 75.8 78.4 80.5 75.8Oct 66.9 67.1 70.0 66.7Nov 58.9 55.1 61.5 59.7Dic 54.2 49.2 53.5 51.0
Indices Estacionales
Introducción a Excel 168
Dic 54.2 49.2 53.5 51.0Average: 66.3 66.2 68.8 68.0
Indice Estacional1996 1997 1998 1999 Indice
Ene 0.74 0.74 0.78 0.79 0.76Feb 0.80 0.81 0.78 0.85 0.81Mar 0.83 0.96 0.84 0.86 0.87Abr 0.97 0.94 0.94 1.04 0.97May 1.16 1.09 1.12 1.08 1.11Jun 1.19 1.19 1.21 1.18 1.19Jul 1.24 1.26 1.24 1.21 1.24Ago 1.21 1.23 1.22 1.25 1.23Sep 1.14 1.18 1.17 1.12 1.15Oct 1.01 1.01 1.02 0.98 1.01Nov 0.89 0.83 0.89 0.88 0.87Dic 0.82 0.74 0.78 0.75 0.77
Sum: 12.00
Average: 1.00
Análisis de series de tiempo –Transformada discreta de Fourier• Problema: usar la transformada discreta de Fourier
para analizar un conjunto de datos.• En Herramientas→Análisis de datos hay una
opción que es Análisis de Fourier que permite realizar transformaciones discretas de Fourier
Introducción a Excel 169
realizar transformaciones discretas de Fourier (DFT) y transformaciones inversas.
• El tamaño de la serie debe ser potencia de 2 con un tamaño máximo de 2^12 = 4096.
• Cálculo de la frecuencia en Hz donde ies el número de la muestra, n el número de muestras y s el intervalo de muestra.
)(nsifi =
Análisis de series de tiempo –Transformada discreta de Fourier• Cálculo de la frecuencia en ciclos por
muestra donde i es el número de la muestra, n el número de muestras y s el intervalo de muestra.
• La DFT se obtiene seleccionando el rango de las celdas que contienen la serie.
nifi =
Introducción a Excel 170
celdas que contienen la serie.• Los resultados de la DFT son números complejos
que se escriben como texto. Para manipularlos Excel dispone de funciones para ellos.
• Cálculo de la potencia en cada banda de frecuencia hasta la frecuencia de Nyquist (0.5 ciclos/muestra). IM.ABS(DFT)^2/n^2
Análisis de series de tiempo –Transformada discreta de Fourier• Se puede filtrar los datos en el campo de la
frecuencia para aislar un determinado componente.
• Se construye un filtro adecuado.
EXP(-((ABS(frec cs) – fo)/sig)^2))
Introducción a Excel 171
EXP(-((ABS(frec cs) – fo)/sig)^2))
• Se aplica el filtro a la DFT multiplicándolo (usar funciones de números complejos).
• Se calcula DFT inversa y se obtiene la serie numérica utilizando funciones de números complejos. IM.REAL(InversaDFT)
Análisis de series de tiempo -Transformada discreta de Fourier
0
0.5
1
1.5
0 0.5 1 1.5 2 2.5 3 3.5 4 4.5 5
Introducción a Excel 172
-2
-1.5
-1
-0.50 0.5 1 1.5 2 2.5 3 3.5 4 4.5 5
Y(t) Filtrada
Resolviendo ecuaciones• En ingeniería es frecuente la tarea de resolver ecuaciones algebraicas complicadas o sistemas de ecuaciones no lineales.
• Hay métodos manuales y computarizados para resolver tal problema, como son el
Introducción a Excel 174
para resolver tal problema, como son el método de Newton y la eliminación gaussiana.
• Excel dispone de funciones y herramientas para ayudar a cumplir esa tarea.
• Ejemplos: Resolviendo_ecuaciones.xls
Resolviendo ecuaciones• La raíz de una ecuación algebraica es el valor de la variable
independiente que satisface la ecuación.• Las ecuaciones pueden ser lineales o no lineales.• Las ecuaciones no lineales se pueden resolver de forma
gráfica o numérica y pueden tener múltiples raíces reales o complejas.
Introducción a Excel 175
complejas. • Las ecuaciones polinómicas son un caso especial de
ecuaciones no lineales muy frecuentes en ingeniería con las siguientes características:– Un polinomio de grado n no puede tener más de n raíces reales.– Si el grado de un polinomio es impar, siempre tendrá al menos una
raíz real.– Las raíces complejas siempre existen en pares de conjugadas
complejas.
Resolviendo ecuaciones método gráfico
• El procedimiento es escribir la ecuación en la forma f(x) = 0 y graficas f(x) vs. x.
• El punto donde f(x) cruza el eje x (valor de x que causa que f(x) sea 0) son las raíces reales de la ecuación.
Introducción a Excel 176
de la ecuación.
• La solución se puede leer directamente del gráfico o interpolar entre los valores tabulados para hallar el punto donde f(x) = 0.
Resolviendo ecuaciones método gráfico
x f(x)
0 -5.00
0.1 -5.03
0.2 -5.12
0.3 -5.27
0.4 -5.46
0.5 -5.69
0.6 -5.92
0.7 -6.13
0.8 -6.26
Raíz real de un polinomio
4.00
6.00
8.00
10.00
Introducción a Excel 177
0.8 -6.26
0.9 -6.25
1 -6.00
1.1 -5.41
1.2 -4.34
1.3 -2.64
1.4 -0.12
1.5 3.44
1.6 8.29
1.7 14.73
1.8 23.07
1.9 33.69
2 47.00
-8.00
-6.00
-4.00
-2.00
0.00
2.00
4.00
1.00 1.10 1.20 1.30 1.40 1.50 1.60
X
f(x
)
Resolviendo ecuaciones método gráficoRaíces de un polinomio cúbico
-2
0
2
-1 -0.8 -0.6 -0.4 -0.2 0 0.2 0.4 0.6 0.8 1 1.2 1.4 1.6 1.8 2
Introducción a Excel 178
-12
-10
-8
-6
-4
-2
X
Y
Resolviendo ecuaciones usando Buscar objetivo
• Se puede obtener una solución rápida de ecuaciones algebraicas simples usando la opción Buscar Objetivo en el menú Herramientas.
• Para ello se sigue:– Escribir un valor inicial de x en una celda.– Escribir la fórmula de la ecuación en la forma f(x)=0 en
Introducción a Excel 179
– Escribir la fórmula de la ecuación en la forma f(x)=0 en otra celda. Escribir la variable x como referencia a la celda que contiene el valor inicial.
– Seleccionar Buscar Objetivo en el menú Herramientas.
– En el diálogo escribir la dirección de la celda que contiene la fórmula, el valor 0 en Valor y la dirección de la celda que contiene el valor inicial. Pulsar Aceptar.
Resolviendo ecuaciones usando Buscar objetivo
• Ejemplo: f(x) = 2*x5 – 3* x2 – 5 = 0
Introducción a Excel 180
x= 1.40408295
Resolviendo ecuaciones usando Solver
• Solver se usa para resolver problemas de más complejidad y se puede configurar el método y visualización de solución.
• Instalar Solver desde Complementos en el menú Herramientas.
• Para ello se sigue:– Escribir un valor inicial de la variable x en una celda
Introducción a Excel 181
– Escribir un valor inicial de la variable x en una celda– Escribir la fórmula de la ecuación en la forma f(x)=0 en otra celda.
Escribir la variable x como referencia a la celda que contiene el valor inicial.
– Seleccionar Solver en el menú Herramientas.– En el diálogo escribir la dirección de la celda que contiene la
fórmula, el valor 0 en Valor y la dirección de la celda que contiene el valor inicial.
– Si se desea restringir el rango de x, pulsar en Agregar.– Pulsar Resolver. Se puede configurar con Opciones.
Resolviendo ecuaciones usando Solver
• Ejemplo: f(x) = 2*x5 – 3* x2 – 5 = 0
Introducción a Excel 182
x= 1.40408619
Resolviendo sistemas de ecuaciones
• Las ecuaciones algebraicas simultáneas se presentan habitualmente en problemas de ingeniería.
• Los sistemas de ecuaciones pueden ser lineales o no lineales.
• Las técnicas para resolver sistemas de ecuaciones
Introducción a Excel 183
• Las técnicas para resolver sistemas de ecuaciones lineales son diferentes que las usadas para las no lineales.
• Para sistemas lineales se usa la notación matricial: [A][x] = [b] donde [A] es una matriz nxn y x y b son vectores de n elementos.
Operaciones matriciales en Excel• En Excel las matrices se representan como arrays.
Un array es un bloque de celdas que se referencian colectivamente.
• Cualquier operación que se realiza sobre un array producirá lo mismo para todas las celdas dentro del
Introducción a Excel 184
producirá lo mismo para todas las celdas dentro del array. Un array se puede especificar como un argumento simple de una función.
• Un array se especifica como un bloque de celdas encerradas entre llaves ({}). Las llaves son añadidas automáticamente por Excel y NO deben escribirse.
Operaciones matriciales en Excel• Para especificar una operación con array:
– Seleccionar el bloque que forma el array. Mover el cursor hasta la celda superior izquierda dentro del bloque.
– Escribir una fórmula con el array. Se puede incluir rangos de celdas dentro de la celda.
Introducción a Excel 185
rangos de celdas dentro de la celda.– Pulsar Ctrl-Mayús-Enter para que la fórmula aparezca
entre llaves.
• Operaciones habituales con matrices:– Suma, Resta, Transpuesta (TRANSPONER)– Multiplicación matricial (MMULT) – Determinante, Inversa (MDETER, MINVERSA)
Operaciones matriciales en Excel
2 3 -4[A] = 3 -1 -2
4 -7 -6
Det[A] = 82
[M] = 1 4 35 2 6
7 12[N] = 11 8
9 10
Introducción a Excel 186
-0.09756098 0.56097561 -0.12195122Inverse [A] = 0.12195122 0.04878049 -0.09756098
-0.20731707 0.31707317 -0.13414634
2 3 4Transpose [A] = 3 -1 -7
-4 -2 -6
[M][N] = 78 74111 136
1.90243902 3.56097561 -4.12195122A + Trans[A] = 3.12195122 -0.95121951 -2.09756098
3.79268293 -6.68292683 -6.13414634
0 0 -8A - Trans[A] = 0 0 5
8 -5 0
Operaciones matriciales en Excel• Problema: calcular la inversa de una matriz compleja.
• Sea una matriz compleja X + jY cuya inversa es U + jV. Por definición de la inversa y el producto de una matriz y su
Introducción a Excel 187
inversa y el producto de una matriz y su inversa: XU − YV = I y YU + XV = 0 de donde se obtiene: V = (− Y-1 X− X-1 Y) -1 X-1
U = − Y-1 XV
Operaciones matriciales en ExcelInversa de una Matriz ComplejaX Real Imgy Y
2 0.6 0.4 0.50.6 3 0.15 0.2
X INV Y INV YINV * X X INV * Y0.53191489 -0.10638298 40 -100 20 -276 0.19680851 0.24468085
-0.10638298 0.35460993 -30 80 -12 222 0.0106383 0.0177305
(Y INV * X + X INV * Y) (Y INV * X + X INV * Y) -1 V = -(Y INV * X + X INV * Y) -1 * X INV
Introducción a Excel 188
(Y INV * X + X INV * Y) (Y INV * X + X INV * Y) V = -(Y INV * X + X INV * Y) * X INV
20.1968085 -275.755319 0.18848297 0.23410374 0.07535224 0.06296413-11.9893617 222.01773 0.01017842 0.01714617 0.00358999 0.00499739
U = - YINV * X * V-0.51620653 0.11999762 -0.07535224 -0.062964130.10724825 -0.35385149 -0.00358999 -0.00499739
Comprobación0.51620653 -0.11999762
-0.10724825 0.35385149 X * U Y * V X*U - Y*V-0.96806411 0.02768435 0.03193589 0.02768435 1 -3.6776E-160.01202084 -0.9895559 0.01202084 0.0104441 -5.2389E-16 1
Y * U X * V Y * U + X * V-0.15285849 -0.1289267 0.15285849 0.1289267 0 0-0.05598133 -0.05277066 0.05598133 0.05277066 0 0
Solución de sistema lineal mediante matrices
• Un método para resolver un sistema de ecuaciones lineales simultáneas [A][x] = [b] es mediante métodos matriciales [x] = [A]-1 [b]
• Para resolver en Excel:– Escribir los elementos de la matriz A.– Escribir los elementos del vector b.
Introducción a Excel 189
– Escribir los elementos del vector b.– Seleccionar las celdas para la inversa A-1. Escribir la
fórmula en la celda superior izquierda =MINVERSA() y seleccionar las celdas de A. Pulsar Ctrl-Mayus-Enter simultáneamente.
– Seleccionar las celdas donde se desea aparezca el vector x. Escribir la fórmula en la celda superior =MMULT() y seleccionar las celdas de A-1 yB . Pulsar Ctrl-Mayus-Enter simultáneamente.
Solución de sistema lineal mediante matrices
Solución de Ecuaciones Simultáneas mediante inversión matricial[A][x] = [b]
9.375 3.042 -2.437[A] = 3.042 6.183 1.216
-2.437 1.216 8.443
9.233[b] = 8.205
3.934
Introducción a Excel 190
0.148 -0.084 0.055Inv[A] = -0.084 0.214 -0.055
0.055 -0.055 0.142
0.896[x] = Inv[A] [b] = 0.765
0.614
Comprob. [A][x] = [b]
9.23338.20493.9339
Solución de ec. simultáneas usando Solver (I)
• Solver ofrece un enfoque diferente para resolver sistemas ecuaciones simultáneas lineales o no lineales.
• Suponiendo que se tiene un sistema de n ecuaciones y nincógnitas representados mediante las ecuaciones:
0),,,(
0),,,(
212
211
=
=
n
n
xxxf
xxxf
…
…
Introducción a Excel 191
• Se desea hallar los valores de x1, x2 …, xn que produce que cada ecuación sea cero. Una forma para hacer esto es forzar a que la función (varianza residual):
y =f12 + f22 + …+ fn2 sea cero.
0),,,(
0),,,(
21
212
=
=
nn
n
xxxf
xxxf
…
�
…
Solución de ec. simultáneas usando Solver (II)
• Procedimiento:– Escribir un valor inicial para cada variable independiente x1, x2 …,
xn en celdas diferentes– Escribir las ecuaciones f1 , f2 , …, fn e y en celdas diferentes
expresadas como fórmulas dependientes de las celdas donde están las variables x1, x2 …, xn
– Seleccionar Solver de la Barra de herramientas. Dar la dirección de
Introducción a Excel 192
– Seleccionar Solver de la Barra de herramientas. Dar la dirección de la celda que contiene la fórmula de y para Celda Objetivo. Seleccionar Valores de 0. En Cambiando las celdas dar el rango de las celdas que contienen los valores iniciales de las variables x1, x2…, xn.
– Se puede restringir opcionalmente el rango de los valores de las variables independientes pulsando Agregar.
– Se puede seleccionar la opción de generar Resultados en otra hoja.
Solución de ec. simultáneas usando Solver (III)
• Ejemplo 1 (lineal):3 x1 + 2 x2 – 2 x3 = 42 x1 – x2 + x3 = 3x1 + x2 – 2 x3 = – 3
f = 3 x + 2 x – 2 x – 4 = 0
x1 = 0.999999357
x2 = 2.000000069
x3 = 2.999999083
f(x1,x2,x3) = -8.75401E-07
g(x1,x2,x3) = -2.27238E-06
h(x1,x2,x3) = 1.25909E-06
Introducción a Excel 193
f = 3 x1 + 2 x2 – 2 x3 – 4 = 0g = 2 x1 – x2 + x3 – 3 = 0h = x1 + x2 – 2 x3 + 3 = 0
y = f2 + g2 + h2
y = 7.51534E-12
Solución de ec. simultáneas usando Solver (IV)
• Ejemplo 2 (no lineal):x1
2 + 2 x22 – 5 x1 + 7 x2 = 40
3 x12 – x2
2 + 4 x1 + 2 x2 = 28
f (x1, x2) = x12 + 2 x2
2 – 5 x1 + 7 x2 – 40 = 0
Introducción a Excel 194
g (x1, x2) = 3 x12 – x2
2 + 4 x1 + 2 x2 – 28 = 0
y (x1, x2) = f2 + g2Ecuaciones No Lineales Simultáneas
x1 = 3.206598
x2 = 5.083364
f(x1, x2) = -0.000447
f(x1, x2) = -0.000654
y = 6.28E-07
Solución de ec. simultáneas usando Solver (V)
• Ejemplo 3 (no lineal):
149
122
=+
−=−
xy
ey x
0.2
0.3
0.4
0.5
y=1-e^(-x)
9y^2+4x^2=1
Introducción a Excel 195
-0.5
-0.4
-0.3
-0.2
-0.1
0
0.1
-0.5 -0.4 -0.3 -0.2 -0.1 0 0.1 0.2 0.3 0.4 0.5
x 0.30671241 x -0.25279654y 0.26329549 y -0.28767525
f(x) = 0 -0.00084231 f(x) = 0 -5.3977E-05g(x) = 0 0.00021064 g(x) = 0 0.00043784
7.5386E-07 1.9462E-07
Evaluación de Integrales• Muchos problemas de ingeniería requieren la evaluación de
integrales. Para ello se usan métodos analíticos y numéricos.
• Suponiendo que se da una función continua y=f(x) definida en el intervalo a ≤ x ≤ b, entonces la integral
b b
Introducción a Excel 197
se interpreta como el área bajo la curva.
• La integración numérica trata de aproximar el resultado de una integral aplicando las fórmulas de integración Newton-Cotes, cuadratura gaussiana o Monte Carlo.
• Ejemplo: Integrales1.xls
∫ ∫==
b
a
b
a
dxydxxfI )(
Evaluación de Integrales método trapezoidal
• Regla trapezoidal (datos espaciados uniformes):– Datos: n pares de puntos equiespaciados (x1,y1),
(x2,y2),… (xn,yn) donde x1 = a y x2 = b
– Los puntos definen n-2 intervalos rectangulares con un ancho igual a ∆x
+ yy
Introducción a Excel 198
– La altura de cada intervalo se expresa como:
– Por tanto la integral se aproxima como: 2
1++
=ii
i
yyy
∫ ∑ ∆
+
+==
−
=
b
a
n
i
in xyyy
dxyI1
2
1
2
)(
Evaluación de Integrales método trapezoidal
• Ejemplo:– La presión media de una gas cuando la temperatura del
gas varía en el tiempo se calcula por:
592.3)12300(10571.8100
0
4−+= ∫
− dttxP
Introducción a Excel 199
Para hallar la integral, se obtiene 21 puntos igualmente espaciados y se aplica la trapezoidal, de donde se obtiene que la presión es:
547.73592.3)109)(10571.8( 44=−=
− xxP
Evaluación de Integrales método trapezoidal
Integración numérica usando la regla trapezoidal con datos espaciados uniformemente
tiempo Temperatura
0 3005 360 360
10 420 42015 480 48020 540 54025 600 60030 660 66035 720 720
Introducción a Excel 200
35 720 72040 780 78045 840 84050 900 90055 960 96060 1020 102065 1080 108070 1140 114075 1200 120080 1260 126085 1320 132090 1380 138095 1440 1440
100 1500Suma = 17100 Integral = 90000 Presión = 73.547
Evaluación de Integrales método trapezoidal
• Regla trapezoidal (datos espaciados no uniformes):– Datos: n pares de puntos (x1,y1), (x2,y2),… (xn,yn) donde
x1 = a y x2 = b
– Estos puntos definen n-1 intervalos rectangulares con un ancho para el i-ésimo intervalo ∆xi = xi+1 – xi
+=
yy
Introducción a Excel 201
– La altura de cada intervalo se expresa como:
– Por tanto la integral se aproxima como: 2
1++
=ii
i
yyy
∫ ∑−
=
++−+==
b
a
n
i
iiii xxyydxyI1
111 ))((
2
1
Evaluación de Integrales método trapezoidal
• Ejemplo:– La corriente por una inductancia se puede obtener con la fórmula
donde: i = corriente (amperios), L = inductancia (henrios), v = voltaje (voltios) y t=tiempo (seg).
Se induce una corriente de 2.15 amperios por un periodo de 500
∫=
t
dtvL
i0
1
Introducción a Excel 202
Se induce una corriente de 2.15 amperios por un periodo de 500 milisegundos. La variación del voltaje con el tiempo en este periodo se muestra en la siguiente tabla:
Hallar la inductancia evaluando integral por medio de la regla trapezoidal
t (msec) v (volts) t v t v t v 0 0 40 45 90 45 180 27 5 12 50 49 100 42 230 21 10 19 60 50 120 36 280 16 20 30 70 49 140 33 380 9 30 38 80 47 160 30 500 4
Evaluación de Integrales método trapezoidal
Integración numérica usando la regla trapezoidal con datos espaciados no uniformes
t (mseg) v (volts) deltat (seg) vbar area
0 0 0.005 6 0.035 12 0.005 15.5 0.0775
10 19 0.01 24.5 0.24520 30 0.01 34 0.3430 38 0.01 41.5 0.41540 45 0.01 47 0.4750 49 0.01 49.5 0.495
50
60
Voltaje vs. Tiempo
Introducción a Excel 203
50 49 0.01 49.5 0.49560 50 0.01 49.5 0.49570 49 0.01 48 0.4880 47 0.01 46 0.4690 45 0.01 43.5 0.435
100 42 0.02 39 0.78120 36 0.02 34.5 0.69140 33 0.02 31.5 0.63160 30 0.02 28.5 0.57180 27 0.05 24 1.2230 21 0.05 18.5 0.925280 16 0.1 12.5 1.25380 9 0.12 6.5 0.78500 4
Total = 10.7675
0
10
20
30
40
0 100 200 300 400 500 600
Voltios
Tiempo, mseg
Evaluación de Integrales método Simpson
• Regla de Simpson (número de datos impar –número de subintervalos par):– En lugar de considerar rectángulos entre los puntos, se
pasa un polinomio de segundo orden (parábola) a través de tres puntos adyacentes igualmente espaciados.
Introducción a Excel 204
– Por tanto la integral se aproxima como:
• Ejemplo: Evaluar la integral
en el rango de 0 a 1 con un ∆=0.1 entre puntos.
∫ ∆++++++++==−−
b
a
nnn xyyyyyyyydxyI )422424(3
11254321 �
∫−
=
1
0
2
dxeI x
Evaluación de Integrales método trapezoidal
Integración numérica usando la regla de Simpson
x y
0.0 1.0000 1.00000.1 0.9900 3.96020.2 0.9608 1.92160.3 0.9139 3.65570.4 0.8521 1.7043
Funcion Exp(-x^2)
1.2000
Y
∫−
=
1
0
2
dxeI x
Introducción a Excel 205
0.4 0.8521 1.70430.5 0.7788 3.11520.6 0.6977 1.39540.7 0.6126 2.45050.8 0.5273 1.05460.9 0.4449 1.77941.0 0.3679 0.3679
SUMA = 22.4047
INTEGRAL = 0.7468
0.0000
0.2000
0.4000
0.6000
0.8000
1.0000
0.0 0.2 0.4 0.6 0.8 1.0 1.2X
Cálculo del centro de área mediante Integrales
• Problema: calcular el área y centro del área de una función dada como tabla.
• Se aplica una técnica de integración numérica para el cálculo del área y el primer momento para el centroide.
Introducción a Excel 206
centroide.• El centro de un área se calcula tomando el primer
momento del área calculado con:
• De donde se obtiene el centro:
∫ ∫∫ ∫ ==== xydxxdAMyxdyydAM yx
A
My
A
Mx x
c
y
c ==
Cálculo del segundo momentode un área
• Problema: calcular el segundo momento de un área (momento de inercia).
• Se usa la misma técnica que la sección anterior para el primer momento, pero usando x2 e y en lugar de x e y. Es decir para el eje y:
∫ ∫== ydxxdAxI 22
Introducción a Excel 207
• El momento de inercia para un eje que pasa por el centro de un área se calcula aplicando el teorema del eje paralelo,
donde Ina es el momento de inercia del área sobre el eje paralelo a y que pasa por el centroide, A es el área y d es la distancia al eje y.
∫ ∫== ydxxdAxI y22
2AdII yna −=
Cálculo del centro y segundo momento de un área
Cálculo del Centro y Momento de Inercia de un Area usando integración numérica
x y Coef.Simpson
0.000 0.100 10.200 0.300 40.400 0.600 20.600 0.900 40.800 1.050 21.000 1.000 41.200 0.700 2 0.800
1.000
1.200
Introducción a Excel 208
1.200 0.700 21.400 0.400 41.600 0.200 21.800 0.100 42.000 0.050 1
s = 0.2
Area = 1.070xc = 0.869yc = 0.382
Iy = 0.97013333Iyc = 0.16297404
0.000
0.200
0.400
0.600
0.800
0.000 0.500 1.000 1.500 2.000
Centro del Area
Cálculo de Integrales dobles• Problema: se requiere integrar numéricamente una
integral doble para calcular, por ejemplo, un volumen bajo una superficie.
• La técnica propuesta es dividir una integral múltiple en sucesivas integrales sencillas y aplicar
Introducción a Excel 209
múltiple en sucesivas integrales sencillas y aplicar las técnicas de integración numérica mostradas antes.
• Así para calcular el volumen bajo una superficie se calculan las áreas de las secciones transversales al eje y y después integrarlas para el eje x.
Cálculo del volumen bajo una superficie
Cálculo de Volumen mediante Integrales doblessx = 0.1sy = 0.1
Coeficientes x 0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1
0.5 0 1.000 0.990 0.961 0.914 0.852 0.779 0.698 0.613 0.527 0.445 0.3681 0.1 0.990 0.980 0.951 0.905 0.844 0.771 0.691 0.607 0.522 0.440 0.3641 0.2 0.961 0.951 0.923 0.878 0.819 0.748 0.670 0.589 0.507 0.427 0.3531 0.3 0.914 0.905 0.878 0.835 0.779 0.712 0.638 0.560 0.482 0.407 0.3361 0.4 0.852 0.844 0.819 0.779 0.726 0.664 0.595 0.522 0.449 0.379 0.3131 0.5 0.779 0.771 0.748 0.712 0.664 0.607 0.543 0.477 0.411 0.346 0.2871 0.6 0.698 0.691 0.670 0.638 0.595 0.543 0.487 0.427 0.368 0.310 0.2571 0.7 0.613 0.607 0.589 0.560 0.522 0.477 0.427 0.375 0.323 0.273 0.2251 0.8 0.527 0.522 0.507 0.482 0.449 0.411 0.368 0.323 0.278 0.235 0.1941 0.9 0.445 0.440 0.427 0.407 0.379 0.346 0.310 0.273 0.235 0.198 0.164
0.5 1 0.368 0.364 0.353 0.336 0.313 0.287 0.257 0.225 0.194 0.164 0.135Areas: 0.746 0.739 0.717 0.682 0.636 0.581 0.521 0.457 0.393 0.332 0.275
Coeficientes: 0.5 1 1 1 1 1 1 1 1 1 0.5
Volumen = 0.55683
y
Introducción a Excel 210
0
0.4
0.8
00.
1
0.2
0.3
0.4
0.5
0.6
0.7
0.8
0.9 1
0.000
0.100
0.200
0.300
0.400
0.500
0.600
0.700
0.800
0.900
1.000
z
x
y
0.900-1.000
0.800-0.900
0.700-0.800
0.600-0.700
0.500-0.600
0.400-0.500
0.300-0.400
0.200-0.300
0.100-0.200
0.000-0.100
Curva de Areas
0.000
0.100
0.200
0.300
0.400
0.500
0.600
0.700
0.800
0 0.1 0.2 0.3 0.4 0.5 0.6 0.7 0.8 0.9 1
Y
Are
a
Volumen = 0.55683
Optimización• Los problemas de ingeniería se modelan mediante sistemas
de ecuaciones para su análisis.• Muchos problemas de ingeniería requieren la optimización
de un criterio, como el costo, ganancia, peso, etc, al que se llama función objetivo.
• Adicionalmente hay una serie de condiciones, tales como leyes de conservación, restricciones de capacidad u otra
Introducción a Excel 212
leyes de conservación, restricciones de capacidad u otra restricción técnica, que deben ser satisfechas. Estas condiciones se llaman restricciones.
• El objetivo de una solución óptima es determinar una solución que produce que la función objetivo sea maximizada o minimizada cumpliendo todas las restricciones.
• Los problemas de este tipo se conocen como problemas de optimización.
Optimización - Formalización• Un problema de optimización se puede escribir como:
Determinar los valores de la variables x1, x2, …. , xn que maximice o minimice la función objetivo:
),,,( 21 nxxxfy �=
Introducción a Excel 213
Sujeto a las siguientes restricciones j = 1, 2, …, m:
o
oa
además, es común restringir i = 1, 2, …, n
• Ejemplos: Optimización 1
0),,,( 21 =nj xxxg �
0),,,( 21 ≤nj xxxg �
0),,,( 21 ≥nj xxxg �
0≥ix
Optimización usando Solver• Procedimiento:
– Escribir un valor inicial para cada variable independiente x1, x2 …, xn en celdas diferentes.
– Escribir la función objetivo como fórmula Excel en una celda.– Escribir las ecuaciones de cada restricción como fórmulas Excel.– Seleccionar Herramientas→Solver. Dar la dirección de la celda que
contiene la función objetivo para Celda objetivo. Seleccionar Máximo o Mínimo en Valor de la celda objetivo. Dar el rango de
Introducción a Excel 214
Máximo o Mínimo en Valor de la celda objetivo. Dar el rango de las celdas que contienen los valores iniciales de las variables x1, x2 …, xn en Cambiando las celdas.
– Escribir las celdas que contienen cada restricción, el tipo de restricción y el valor del lado derecho usando Agregar.
– Si la función objetivo y las restricciones son lineales, pulsar en el botón Opciones y seleccionar Adoptar Modelo Lineal.
– Pulsar Aceptar y después Resolver. Se puede seleccionar la opción de generar Resultados en otra hoja.
Programación lineal clásica• Maximizar f(x1, x2) = 29 x1 + 45 x2
Sujeto a las restricciones:
2x1 + 8x2 ≤ 60
4x1 + 4x2 ≤ 50
x , x ≥ 0
Introducción a Excel 215
x1, x2 ≥ 0
Programación lineal clásicaSolución (Solver) de problema de programación lineal clásico
x1 = 6.6667x2 = 5.8333f(x1, x2) = 455.8333 ? Función Objectivo
Valor LímiteRestricción 1 60 60Restricción 2 50 50
Introducción a Excel 216
Programación lineal clásica
8
10
12
14x2
Región de soluciones factibles
Recta de la Función
R2
R1
Introducción a Excel 217
0
2
4
6
0 2 4 6 8 10 12 14 16 18 20
x1
Restricción 1 Restricción 2 Recta FObj
Recta de la Función Objetivo para 455.833
Optimización de producción• Problema de asignación lineal:
Maximizar y = 60 x1 + 44 x2
Sujeto a las restricciones:
x + x ≥ 1000
Introducción a Excel 218
x1 + x2 ≥ 1000
5x1 +3 x2 ≤ 8000
x1, x2 ≥ 0
Optimización de producción
Optimización de la producción
Unidades de A /mes: 0
Unidades de B /mes: 2666.66667
Ganacia (y) = 117333.333
Producción mínima requerida (g1): 2666.66667
Introducción a Excel 219
Producción mínima requerida (g1): 2666.66667
Disponibilidad mano de obra (g2): 8000
Optimización no lineal• Minimizar y = 10 + (x1 – 0.5)2 + (x2 – 0.5)2
(problema no lineal)
Sujeto a las restricciones:
π (x 2 + x 2 ) ≥ 10
Introducción a Excel 220
π (x12 + x2
2 ) ≥ 10
x1 ≤ 1.25 x2
x1, x2 ≥ 0
Nota: Excel No indica si la solución es una solución local o global.
Optimización no lineal
Optimización no lineal
x1 = 1.39316687
x2 = 1.1145335
y = 13.4044655
Introducción a Excel 221
g1 = 9.99999997
g2 = 0
Optimización no lineal• La solución de problemas de optimización no
lineales pueden depender de los valores iniciales supuestos, con lo que se pueden obtener soluciones óptimas locales y no hallar la solución óptima global.
Introducción a Excel 222
global.
• Una opción es graficar la función objetivo para ver su comportamiento en problemas sencillos, sino se puede usar técnicas de Monte Carlo (aleatorio).
• Ej: Minimizar -3 ≤ x1, x2 ≤ 3 25.1
)5.0(
)cos(1
22
22
⋅
++
+−=
yx
yxz
Optimización no lineal
0.5
1
1.5
2Gráfica de la función para x=0
1
1.5
2
Introducción a Excel 223
-3
-2.2
5
-1.5
-0.7
5 0
0.75 1.5
2.25 3
-3
-1
1
3
-1.5
-1
-0.5
0
0.5
-1.5
-1
-0.5
0
0.5
1
-3 -2 -1 0 1 2 3
Evaluación económica de alternativas
• Una parte importante en la evaluación de proyectos es la evaluación económica.
• Se basa en el valor del dinero en el tiempo. La terminología empleada es el principal para indicar la cantidad prestada y el interés que es el pago adicional por el uso del dinero.
Introducción a Excel 225
adicional por el uso del dinero.• Los cálculos de interés se basan en la tasa de
interés i.• Los cálculos económicos se basan en el uso del
interés compuesto. Así para n períodos de interés, la cantidad total de dinero acumulado al final del último período de interés es: F = Fn = P(1 + i)n
• Ejemplos: Comparacion_Economica1.xls
Cálculos financieros básicos• Problema: Calcular el capital acumulado para un depósito a un interés y período dado.
Interés compuesto
P = 2000
i (anual) = 0.05
Cantidad acumulada
Final de año F Interés horario = 5436.550 2000.001 2100.002 2205.003 2315.25
Acumulación del interés compuesto
6000.00
Introducción a Excel 226
i (anual) = 0.05
n = 20
3 2315.254 2431.015 2552.566 2680.197 2814.208 2954.919 3102.66
10 3257.7911 3420.6812 3591.7113 3771.3014 3959.8615 4157.8616 4365.7517 4584.0418 4813.2419 5053.9020 5306.60
0.00
1000.00
2000.00
3000.00
4000.00
5000.00
0 2 4 6 8 10 12 14 16 18 20
Final de Año
To
tal acu
mu
lad
o
Cálculos financieros básicos• Problema: Calcular el capital acumulado para un depósito a un interés y período dado con capitalización menores al año. m = períodos de capitalizacion
n = número total de períodos de capitalizacion = mxnúmero de años
Introducción a Excel 227
n = número total de períodos de capitalizacion = mxnúmero de años
F = Fn = P(1 + i/m)nInterés compuesto: Variación de la frecuencia del interés
Interés anual = 5306.60
Interés cuatrimetral = 5402.97
Interés mensual = 5425.28
Interés diario = 5436.19
Valor presente de un flujo de caja • Una alternativa económica realista incluye normalmente un
período de inversión inicial seguida de una serie de flujos de caja.
0 1 2 3 n-1 n
A
Introducción a Excel 228
• Un flujo de caja típico para un préstamo consiste en una inversión inicial seguido de una serie de n pagos uniformes. En ese caso las cuotas se calculan como:
0 1 2 3 n-1 n
−+
+=
1)/1(
)/1()/(n
n
mi
mimiPA
P
Pago uniforme• Problema: Calcular el pago uniforme (amortización) para devolver una cantidad inicial P. Excel tiene una función Pago
Pago uniforme de una inversión
Introducción a Excel 229
Pago uniforme de una inversión
A = -140,000.00 €
i = 0.08
n = 12
P = 1055050.92
Valor presente• Problema: Calcular el valor actual de un pago uniforme A. Excel tiene una función VA.
Valor presente de una inversión
Introducción a Excel 230
Valor presente de una inversión
A = 140000
i = 0.08
n = 12
P = -1,055,050.92 €
Valor futuro• Problema: Calcular el valor futuro de un pago uniforme A. Excel tiene una función VF.
Valor futuro de un pago
Introducción a Excel 231
Valor futuro de un pago
A = 140000
i = 0.08
n = 12
F = 2,656,797.70 €
Flujos de caja no uniformes• Problema: Calcular el valor presente neto de un flujo de caja no uniforme en periodos uniformes. Excel tiene una función VNA.
Valor Presente de una inversión
i = 0.08
Introducción a Excel 232
Final año Flujo de Caja
0 -100000001 -80000002 03 10000004 20000005 30000006 40000007 50000008 60000009 5000000
10 400000011 300000012 200000013 1000000
VPN = 2,380,570.73
Fujo de Caja
-12000000
-10000000
-8000000
-6000000
-4000000
-2000000
0
2000000
4000000
6000000
8000000
0 1 2 3 4 5 6 7 8 9 10 11 12 13
Final Año
Ca
nti
da
d (
€)
Comparación de Alternativas Flujos de caja no uniformes
• Problema: Comparar varias alternativas de flujos de caja. Se selecciona la de mayor Valor Presente Neto.
Comparación de dos oportunidades de inversión
i = 0.1
Introducción a Excel 233
i = 0.1
Flujo de Caja Flujo de Caja
Final año Alternativa A Alternativa B
0 -3500000 -35000001 1200000 6000002 1200000 9000003 1200000 11000004 1200000 13000005 1200000 15000006 1200000 1800000
VPN = 1,726,312.84 1,451,055.03
Comparación de Alternativas Tasa interna de retorno (TIR)
• El método de la Tasa Interna de Retorno (TIR) es otro criterio muy usado para comparar varias alternativas de inversión. A diferencia del método del Valor Presente no hay necesidad de especificar una tasa de interés.
• Si dibujamos el valor presente de un flujo de caja en función de la tasa de interés, la TIR es el punto de cruce, es decir, el valor de la tasa de interés al cual el valor presente
Introducción a Excel 234
decir, el valor de la tasa de interés al cual el valor presente neto se hace cero.
• Durante la comparación de alternativas mediante la TIR se escogerá aquella alternativa que tenga la mayor tasa interna de retorno.
• Excel tiene la función TIR que calcula la tasa interna de retorno directamente.
VPN - TIRValor Presente en función de la Tasa de Interés
Final año Flujo Caja0 -1000001 150002 200003 250004 300005 35000
Valor Presente Neto vs Tasa de de Interés
50,000
60,000
70,000
Introducción a Excel 235
5 350006 40000
i VPN0 65,000
0.03 46,6390.06 31,0570.09 17,7510.12 6,3220.15 -3,549 0.18 -12,119 0.21 -19,597
-20,000
-10,000
0
10,000
20,000
30,000
40,000
50,000
0 0.05 0.1 0.15 0.2 0.25
Tasa de Interés
VP
N
TIR
Comparación de Alternativas Tasa interna de retorno (TIR)Comparación de dos oportunidades de inversión
i = 0.1
Flujo de Caja Flujo de Caja
Final año Alternativa A Alternativa B
0 -3500000 -3500000
Introducción a Excel 236
0 -3500000 -35000001 1200000 6000002 1200000 9000003 1200000 11000004 1200000 13000005 1200000 15000006 1200000 1800000
VPN = 1,726,312.84 1,451,055.03
TIR = 26% 21%
Conversión de Unidades• En ingeniería es frecuente la necesidad de conversión de unidades entre diferentes sistemas de unidades.
• La forma usual de realizar esta tarea es mediante equivalencias de unidades que se
Introducción a Excel 238
mediante equivalencias de unidades que se obtienen a partir de factores de conversión tabulados.
• Las hojas de cálculo también se pueden usar con este propósito eliminando la necesidad de realizar cálculos manuales.
Conversión de Unidades• Los sistemas de unidades más comunes son:
– Sistema Internacional de Unidades – Sistema Anglosajón de Unidades – Sistema métrico decimal
Introducción a Excel 239
– Sistema métrico decimal – Sistema Cegesimal de Unidades o cgs– Sistema Técnico de Unidades o mks
• Con Excel se pueden realizar conversiones simples y complejas de unidades.
Conversiones simples
• Una conversión simple incluye sólo unidades de la misma dimensión, p.e. pies a metros (longitud).
• Excel ofrece la función de Ingeniería
Introducción a Excel 240
• Excel ofrece la función de Ingeniería CONVERTIR (instalar Herramientas→
Complementos→Herramientas para análisis).
• En las conversiones simples se multiplica la cantidad original por el factor de equivalencia unitario apropiado.
Conversiones simples
• Ejemplo manual: Conversión simple de pies a metros
Convertir 2.5 pies a su número equivalente en metros, usando el factor de conversión
Introducción a Excel 241
en metros, usando el factor de conversión
1 pie = 0.3048 m.
La conversión es: mft
mftL 762.0
1
3048.05.2 =×=
Conversiones simples en Excel
• En Excel se usa la función CONVERTIR. • Sintaxis:
CONVERTIR(número, de_unidad, a_unidad)
número es el número a convertir.
Introducción a Excel 242
número es el número a convertir.
de_unidad abreviación de la unidad original.
a_unidad abreviación de la unidad final.
• Ejemplo:
=CONVERTIR(1.0, "lbm“, "kg")
Convierte 1 libra masa a kilogramos (0,453592).
Abreviaciones de Unidades• Las abreviaciones de unidad se escriben entre
comillas dobles y se distinguen entre mayúsculas y minúsculas (Ver ayuda).
• Ejemplos:
=CONVERTIR(68, "F“, "C")
Introducción a Excel 243
=CONVERTIR(68, "F“, "C")
Convierte 68 grados Fahrenheit a Celsius (20).
=CONVERTIR(2,5, "ft“, "sec")
Los tipos de datos no son iguales, por tanto se devuelve un error (#N/A).
Conversiones complejas• Se refieren a conversiones donde intervienen
varias unidades.
• Ejemplo: Convertir 6.3 lb/sq-inch (psi) a newtons /m2 (Pa) donde 1 lbf/in2 = 6894.8 N/m2.
Introducción a Excel 244
22
22 /43437
)/(1
)/(8.6894)/(3.6 mN
inlb
mNinlbP
f
f =×=
2
2
2/43437
1
37.39
1
44822.43.6mN
m
in
lb
N
in
lbP
f
f=
××=
Ejemplo de Conversiones complejas
• =CONVERTIR(6.3,”lbf”,”N”)*CONVERTIR(1, “m",“in")^2
Convierte 6.3 libras a newtons y se multiplica por la conversión de metros a pulgadas.
Introducción a Excel 245
pulgadas.
Transferencia de datos - Lectura• Algunas aplicaciones requieren que sean leídos o
importados ficheros diferentes de Excel.• Para leer ficheros tipo texto se siguen los
siguientes pasos:– Asegurarse que el fichero es un fichero texto (extensión
típica .txt, .csv, o .prn).
Introducción a Excel 247
típica .txt, .csv, o .prn).– En Excel seleccionar Archivo→Abrir. Cuando aparece
la ventana de diálogo seleccionar Archivos de texto. Seleccionar el archivo.
– Aparece el Asistente. Es necesario seleccionar si el fichero tiene delimitadores entre campos o si son de ancho fijo.
– Si hay delimitadores, seleccionar el tipo de separador.– Finalmente se selecciona el formato.
Importando datos desde páginas Web
• Es posible importar datos desde una página Web.– La forma más fácil es utilizar Datos > Obtener datos externos > Nueva Consulta Web. Aparece un navegador donde se
Introducción a Excel 249
Web. Aparece un navegador donde se puede colocar la URL deseada. (Ejm:http://physics.nist.gov/cgi-
bin/Compositions/stand_alone.pl?ele=&ascii=html&isotype=some )– Se seleccionan los datos y se da en el botón Importar.
Transferencia de datos - Escritura• Para exportar datos a un fichero tipo texto.
– En Excel seleccionar Archivo→Guardar como...
– Si se desea que los datos de cada línea se separen por tabuladores, seleccionar Texto (delimitado por tabulaciones). Se añade
Introducción a Excel 251
(delimitado por tabulaciones). Se añade automáticamente el sufijo .txt al fichero.
– Si se desea que los datos de cada línea se separen por comas, seleccionar CSV (delimitado por comas). Se añade automáticamente el sufijo .csv al fichero.
Organización de datos - Listas• Creación de listas en Excel.
– Introducir los datos (pueden tener encabezado).
– Para introducir un nuevo registro se puede usar la opción Formulario del menú Datos.
– Ejemplo: Provincias_España.xls.
Introducción a Excel 254
– Ejemplo: Provincias_España.xls.
Organización de datos - Ordenación• Ordenación de datos en Excel.
– Una lista puede ser ordenada seleccionando el rango de datos y pulsando sobre el icono de ordenar ascendente o descente en la barra de herramientas.
Introducción a Excel 256
herramientas.
– También se puede seleccionar en el menú Datos la opción Ordenar.
Organización de datos - Filtrado• Filtrado de datos en Excel.
– Excel dispone de operaciones similares a las bases de datos como es la recuperación de la información que satisface ciertas condiciones.
– El filtrado de listas permite seleccionar registros
Introducción a Excel 258
– El filtrado de listas permite seleccionar registros que satisfacen determinados criterios.
– Se usará la sub-opción Autofiltro de la opción Filtro en el menú Datos.
Organización de datos - Filtrado• Ejercicios (Provincias_España.xls):
– Las 10 provincias que tienen mayor densidad de población.
– Qué provincias tienen superficies que exceden 15,000 km2.
Introducción a Excel 259
15,000 km2.
– Qué provincias tienen poblaciones entre 500000 y 1 millón de habitantes.
Organización de datos - Filtrado• Las 10 provincias que tienen mayor densidad de
población:– Seleccionar una celda arbitraria dentro de la lista.
– Seleccionar Filtro/Autofiltro en el menú Datos.
– Aparecen flechas hacia abajo en el encabezado.
Introducción a Excel 260
– Aparecen flechas hacia abajo en el encabezado.
– Pulsar sobre la flecha en el encabezado de densidad de población y se selecciona los 10 mejores. Aparecen una ventana de diálogo en la que se puede modificar la selección.
– Cuando se termina, se pulsa en la flecha y se selecciona Todas.
Organización de datos - Filtrado• Qué provincias tienen areas que exceden 15,000 km2:– Se pulsa sobre la flecha en el encabezado Superficie y se selecciona Personalizar.
– En el cuadro de diálogo que aparece seleccionar
Introducción a Excel 261
– En el cuadro de diálogo que aparece seleccionar es mayor que y colocar 15000 en el siguiente campo. Pulsar el botón Aceptar.
– Aparecen los resultados en el mismo orden que los originales.
– Cuando se termina, se pulsa en la flecha y se selecciona Todas.
Organización de datos - Filtrado• Qué provincias tienen poblaciones entre 500000 y
1 millón:– Se pulsa sobre la flecha en el encabezado Total y se
selecciona Personalizar.
– En el cuadro de diálogo que aparece seleccionar es
Introducción a Excel 262
– En el cuadro de diálogo que aparece seleccionar es mayor o igual que y colocar 500000 en el siguiente campo y seleccionar es menor o igual que y colocar 1000000 en el siguiente campo. Pulsar el botón Aceptar.
– Aparecen los resultados en el mismo orden que los originales.
– Cuando se termina, se pulsa en la flecha y se selecciona Todas.
Búsqueda en tablas• Problema: Realizar búsquedas de valores en una tabla de datos tabulares para utilizarlos en otros cálculos.
• Excel dispone de las funciones BUSCAR , BUSCARV, BUSCARH, COINCIDIR e
Introducción a Excel 264
BUSCARV, BUSCARH, COINCIDIR e INDICE para acceder a datos tabulares o tablas.
• Ejemplo: Busqueda_tablas.xls
Búsqueda en tablas - BUSCAR• La función BUSCAR devuelve un valor
procedente de un rango de una fila o columna o de una matriz.
• Tiene dos formas de sintaxis: vectorial y matricial. • La forma vectorial de BUSCAR busca en un rango
de una fila o de una columna un valor (vector) y
Introducción a Excel 265
• La forma vectorial de BUSCAR busca en un rango de una fila o de una columna un valor (vector) y devuelve un valor desde la misma posición en un segundo rango de una fila o de una columna.
• Sintaxis: BUSCAR(valor_buscado;vector_de_comparación;vector_resultado)
• El vector_de_comparación debe estar ordenado en orden ascendente.
Búsqueda en tablas - BUSCAR
Longitudft m Acero Aluminio
10 3.05 10 420 6.10 15 740 12.19 20 1080 24.38 30 15
Factor de diseño
Introducción a Excel 266
80 24.38 30 15160 48.77 35 17
Longitud dada = 20Unidades = ftMaterial = AceroFactor de diseño = 15
Búsqueda en tablas - BUSCARV• La función BUSCARV busca un valor en la primera columna de un rango de celdas y devuelve el valor en la columna especificada. Opcionalmente se puede indicar el criterio de búsqueda.
Introducción a Excel 267
indicar el criterio de búsqueda.
• Sintaxis: BUSCARV(valor_buscado;matriz_buscaren;ind_columnas;ordenado)
Búsqueda en tablas - BUSCARV
1 2 3 4 5 6 7 8 9
Temperatura (C) Densidad (kg/m3)Energía Interna (kJ/kg) Entalpía (kJ/kg) Entropía (J/g*K) Cv (J/g*K) Cp (J/g*K) Veloc.sonido (m/s) Viscosidad (Pa*s)10 999.7 42.018 42.119 0.15108 4.1906 4.1952 1447.3 0.001305920 998.21 83.906 84.007 0.29646 4.1567 4.1841 1482.3 0.001001630 995.65 125.72 125.82 0.43673 4.1172 4.1798 1509.2 0.0007973540 992.22 167.51 167.62 0.57237 4.0734 4.1794 1528.9 0.0006529850 988.04 209.32 209.42 0.70377 4.0262 4.1813 1542.6 0.0005468560 983.2 251.15 251.25 0.83125 3.9765 4.185 1551 0.000466470 977.76 293.02 293.12 0.95509 3.9251 4.1901 1554.7 0.0004038980 971.79 334.95 335.06 1.0755 3.8728 4.1968 1554.4 0.0003543590 965.31 376.96 377.06 1.1928 3.8203 4.2052 1550.5 0.00031441
Introducción a Excel 268
90 965.31 376.96 377.06 1.1928 3.8203 4.2052 1550.5 0.00031441
Temperatura (C) 40 <-- Introduce TemperaturaDensidad (kg/m3) 992.22Energía Interna (kJ/kg) 167.51Entalpía (kJ/kg) 167.62Entropía (J/g*K) 0.57237Cv (J/g*K) 4.0734Cp (J/g*K) 4.1794Veloc.sonido (m/s) 1528.9Viscosidad (Pa*s) 0.00065298
Búsqueda en tablas - BUSCARH• La función BUSCARH busca un valor en la primera fila de un rango de celdas y devuelve el valor en la fila especificada. Opcionalmente se puede indicar el criterio de búsqueda.
Introducción a Excel 269
de búsqueda.
• Sintaxis: BUSCARH(valor_buscado;matriz_buscaren;ind_filas;ordenado)
Búsqueda en tablas - BUSCARH
1 Temperatura (C) 10 20 30 40 50 60 70 80 902 Densidad (kg/m3) 999.7 998.21 995.65 992.22 988.04 983.2 977.76 971.79 965.313 Energía Interna (kJ/kg) 42.018 83.906 125.72 167.51 209.32 251.15 293.02 334.95 376.964 Entalpía (kJ/kg) 42.119 84.007 125.82 167.62 209.42 251.25 293.12 335.06 377.065 Entropía (J/g*K) 0.15108 0.29646 0.43673 0.57237 0.70377 0.83125 0.95509 1.0755 1.19286 Cv (J/g*K) 4.1906 4.1567 4.1172 4.0734 4.0262 3.9765 3.9251 3.8728 3.82037 Cp (J/g*K) 4.1952 4.1841 4.1798 4.1794 4.1813 4.185 4.1901 4.1968 4.20528 Veloc.sonido (m/s) 1447.3 1482.3 1509.2 1528.9 1542.6 1551 1554.7 1554.4 1550.59 Viscosidad (Pa*s) 0.001306 0.001002 0.000797 0.000653 0.000547 0.000466 0.000404 0.000354 0.000314
Introducción a Excel 270
9 Viscosidad (Pa*s) 0.001306 0.001002 0.000797 0.000653 0.000547 0.000466 0.000404 0.000354 0.000314
Temperatura (C) 30 <-- Introduce TemperaturaDensidad (kg/m3) 995.65Energía Interna (kJ/kg) 125.72Entalpía (kJ/kg) 125.82Entropía (J/g*K) 0.43673Cv (J/g*K) 4.1172Cp (J/g*K) 4.1798Veloc.sonido (m/s) 1509.2Viscosidad (Pa*s) 0.000797
Búsqueda en tablas -COINCIDIR
• La función COINCIDIR devuelve la posición de un elemento en una matriz. Sintaxis: COINCIDIR(valor_buscado;matriz_buscaren;tipo_coincidencia)
1 Temperatura (C) 10 20 30 40 50 60 70 80 90
Introducción a Excel 271
1 Temperatura (C) 10 20 30 40 50 60 70 80 902 Densidad (kg/m3) 999.7 998.21 995.65 992.22 988.04 983.2 977.76 971.79 965.313 Energía Interna (kJ/kg) 42.018 83.906 125.72 167.51 209.32 251.15 293.02 334.95 376.964 Entalpía (kJ/kg) 42.119 84.007 125.82 167.62 209.42 251.25 293.12 335.06 377.065 Entropía (J/g*K) 0.15108 0.29646 0.43673 0.57237 0.70377 0.83125 0.95509 1.0755 1.19286 Cv (J/g*K) 4.1906 4.1567 4.1172 4.0734 4.0262 3.9765 3.9251 3.8728 3.82037 Cp (J/g*K) 4.1952 4.1841 4.1798 4.1794 4.1813 4.185 4.1901 4.1968 4.20528 Veloc.sonido (m/s) 1447.3 1482.3 1509.2 1528.9 1542.6 1551 1554.7 1554.4 1550.59 Viscosidad (Pa*s) 0.001306 0.001002 0.000797 0.000653 0.000547 0.000466 0.000404 0.000354 0.000314
Temperatura (C) 10 <-- Introduce TemperaturaVeloc.sonido (m/s) 1447.3
Búsqueda en tablas - INDICE• La función INDICE resulta útil para extraer un valor de una tabla dada la posición del valor en la tabla.
• Sintaxis: INDICE(matriz; num_fila; num_columna)Datos:
Introducción a Excel 272
Datos:0.02451 0.93331 0.847560.08995 0.03905 0.326200.94215 0.49682 0.622900.67168 0.60431 0.84449
Elemento 2,2 = 0.0390457
Fila 1 = 0.0245112 0.9333076 0.847564
Columna 2 = 0.93330760.03904570.49681780.6043112
Tablas pivot o dinámicas• Las listas normalmente se organizan de manera lineal, es decir, en columnas adyacentes.
• Algunas listas se pueden organizar de manera bidimensional para mostrar una
Introducción a Excel 273
manera bidimensional para mostrar una mejor interrelación entre los datos.
• Las listas bidimensionales reestructuradas se conocen como tablas pivot o dinámicas.
Tablas pivot o dinámicas• Ejemplo: (datos_pobl_usa.xls)
– Primero asegurarse que la lista está formada por bloques de celdas contiguas con un encabezado en cada columna.
– Se selecciona cualquier celda dentro de la lista y
Introducción a Excel 274
– Se selecciona cualquier celda dentro de la lista y seleccionar en el menú Datos la opción Informe de tablas y gráficos dinámicos.
– Aparece el Asistente con una lista seleccionada y la opción tabla dinámica como resultado.
– Pulsar en el botón Siguiente.
Tablas pivot o dinámicas– Aparece de manera automática el rango de datos
incluyendo los encabezados. Se puede modificar si se desea. Pulsar Siguiente.
– Aparece una ventana para seleccionar la ubicación de la tabla dinámica. Seleccionar Hoja de cálculo nueva. Pulsar el botón Finalizar.
Introducción a Excel 275
Pulsar el botón Finalizar.
– Aparece una hoja de trabajo que incluye una ventana de Lista de campos de tabla dinámica, una plantilla de tabla y una barra de tabla dinámica.
– Arrastrar el campo Región de la Lista de campos de tabla dinámica a la parte Coloque campos de fila aquí.
Tablas pivot o dinámicas– Arrastrar el campo Estado de la Lista de campos de
tabla dinámica a la misma que Region.
– Arrastrar el campo Año de la Lista de campos de tabla dinámica a la parte Coloque campos de columna aquí.
– Finalmente, arrastrar el campo Población de la Lista de campos de tabla dinámica a la parte Coloque datos aquí.
Introducción a Excel 276
campos de tabla dinámica a la parte Coloque datos aquí.
– Se puede reordenar arrastrando los encabezados de una ubicación a otra.
– Se puede crear un gráfico dinámico pulsando sobre el icono en la barra de Tabla dinámica.
Tablas pivot o dinámicasSuma de Población Año
Region Estado 1970 1980 1990 2000 Total general
Midwest Missouri 4677623 4916762 5117073 5595211 20306669North Dakota 617792 652717 638800 642200 2551509
Total Midwest 5295415 5569479 5755873 6237411 22858178Northeast New York 18241391 17558165 17990455 18976457 72766468
Penssylvania 11800766 11864720 11881643 12281054 47828183Rhode Island 949723 947154 1003464 1048391 3948732
Total Northeast 30991880 30370039 30875562 32305902 124543383
Introducción a Excel 279
Total Northeast 30991880 30370039 30875562 32305902 124543383Northwest Alaska 302583 401851 550043 626932 1881409
Washington 3413244 4132353 4866692 5894121 18306410Total Northwest 3715827 4534204 5416735 6521053 20187819South Florida 6791418 9746961 12937926 15982378 45458683
North Carolina 5084411 5800415 6628637 8049313 25562776Virginia 4651448 5346797 6187358 7078515 23264118
Total South 16527277 20894173 25753921 31110206 94285577Southwest California 19971069 23667764 29760021 33871648 107270502
Colorado 2209596 2889735 3294394 4301261 12694986Texas 11198655 14225513 16986510 20851820 63262498
Total Southwest 33379320 40783012 50040925 59024729 183227986Total general 89909719 102150907 117843016 135199301 445102943