Manual-de-Excel-Para-Ingenieros-Marcos-Listo.pdf

ALTCA

CURSO DE EXCEL PARA INGENIEROS

Elaborado por: | Ing. Marcos González

Contenido INTRODUCCION ..................................................................... ............................................................................. ...................4 1

CONOCIENDO EXCEL: ......................................................................... ......................................................................7 1.1

NAVEGANDO POR LOS MENU DE EXCEL: ..............................................................................................7

1.2

SUMINISTRAR DATOS: ........................................................................... ..........................................................9

1.3

CONFIGURANDO LOS DATOS DE UNA CELDA: ............................................................................... 10

1.4 1.5

EXPLORANDO EL ESTILO DE REFERENCIA F1C1: ............................................................................. 12 ENTENDIENDO PROCEDENCIA DE LOS OPERADORES: ................................................................ 14

1.6

USANDO EXPONENTES EN FORMULAS: ................................................................................. ..............14

1.7

EXPLORANDO FUNCIONES: ................................................................... .................................................... 15

1.8

FORMATEANDO TU HOJA DE CÁLCULO:............................................................................................ 17

1.9

ASIGNANDO NOMBRES A CELDAS (VARIABLES PROGRAMABLES): .......................................21

1.10 VALIDACION DE DATOS: .............................................................................................................. ..............22 1.11 SUMANDO COMENTARIOS Y ECUACIONES: .................................................................................... 24 1.12 VENTAJAS DEL USO DE MACROS EN EXCEL: ..................................................................................... 26 2

3

GRAFICANDO: ............................................................................ ............................................................................... .. 30 2.1 2.2

CREANDO UN GRAFICO SIMPLE...................................................................... ........................................30 EXPLORANDO ESTILOS DE GRAFICOS: ..................................................................... ............................33

2.3

PERSONALIZAR LOS EJES DE GRAFICAS: .............................................................................................. 34

2.4

ESTABLECIENDO ESCALA SEMILOGARITMICA Y LOGARITMICA: .......................................... 37

2.5

CAMBIANDO LOS TIPOS DE GRÁFCOS: ............................................................................................... 41

2.6

COMBINANDO TIPOS DE GRAFICOS: ................................................................................................... 42

2.7

EDIFICANDO GRAFICOS DE SUPERFICIES: .......................................................................................... 42

FUNCIONES MATEMATICAS: .................................................................................... ...........................................47 3.1

USANDO FUNCIONES DE SUMA:............................................................................................................. 47

3.2

FUNCIONES MATEMATICAS Y TRIGONOMETRICAS: .................................................................... 52

3.3

FUNCIONES FINANCIERAS: ..................................................................... .................................................... 72

3.4

FUNCIONES DE FECHA Y HORA: ...................................................................... ........................................89 Elaborado por: Ing. Marcos González

4

5

3.5

FUNCIONES LÓGICAS: ...................................................................................................... ............................92

3.6

FUNIONES VARIAS Y COMBINACION DE FUNCIONES: ................................................................ 94

CREANDO MACROS EN EXCEL ...................................................... ..................................................................... 95 4.1

TRABAJANDO CON FORMULAS ....................................................................... .....................................111

4.2

GRAFICOS CON MACROS:...................................................... .................................................................. 134

4.3

COMO GRABAR DATOS DE FORMA SECUENCIAL: ...................................................................... 146

4.4 EDIFICANDO SOPORTE DE VECTORES: ............................................................................................. 156 ANALISIS ESTADISTICO CON EXCEL ........................................................................ .....................................162 5.1

PROCEDIMIENTOS PARA VARIABLES CUANTITATIVAS CONTINUAS.................................167

5.2

FUNCIONES ESTADISTICAS: ............................................................................... .....................................173

5.3

EJEMPLOS DE USOS DE ANALISIS DE DATOS: ................................................................................ 174

Elaborado por: Ing. Marcos González

INTRODUCCION Microsoft Excel es un programa de Aplicación que se ejecuta bajo ambiente Windows, manejando así todas las potencialidades gráficas que tiene incorporado el programa para desarrollar sus funciones: Ventanas, Cuadros de Diálogo, Gráficos, etc. Excel esenuna poderosa herramienta para así efectuar cálculos matemáticos dondelos se generan los resultados forma automática, simulando el trabajo en una matriz, donde datos se organizan en filas y columnas. Al igual se puede utilizar para estudios estadísticos y de finanzas, por sus funciones estadísticas y financieras. Excel, para ayudar a desarrollar mejor sus funciones y prestar una gran utilidad en la Gestión Empresarial, a través de la generación de informes y Proyecciones Comerciales, cuenta con las siguientes herramientas. : Una hoja de cálculo es una planilla cuadriculada compuesta de filas y columnas. Tiene 65.536 filas por 256 columnas, lo que da un total de 16.777.216 celdas, una celda es la intersección de una fila con una columna. En una celda se puede introducir caracteres, números, fórmulas y funciones especiales del excel. Las filas se denominan por su número y van desde la 1 a la 65.536, y las columnas se denominan de derecha a izquierda por A, B, C, ..., Y, Z, AA, AB, ..., IV. La potencia de una hoja de cálculo está determinada por el uso de fórmulas y funciones. Las fórmulas realizan cálculos de forma automática a partir de la información contenida en la información contenida en la misma hoja o en otras hojas y permiten realizar supuestos en los proyectos financieros del tipo : ¿Qué pasaría si ...? Con las fórmulas se puede acceder a multitud de funciones que ejecutan operaciones predefinidas: determinar la rentabilidad de una inversión, calcular el tiempo necesario para finalizar una operación de rentas, etc.


Es una herramienta para organizar y administrar la información como si se tratara de una aplicación de base de datos. Ello permite buscar, ordenar, o extraer la información de forma directa y transparente para el usuario. En una base de datos la información está estructurada en forma de tablas, con filas y columnas, a partir de una hoja de cálculo o de sólo una parte de la misma. Corresponden a una Representación Gráfica que tiene por función interpretar los datos que han sido introducidos en la Hoja de Cálculo. Esta representación que proporciona Excel es fácil de realizar y de entender por terceras personas, obteniendo así un Análisis completo de la información. Para desarrollar estas Representaciones Gráficas, Excel proporciona al usuario un Asistente de Gráficos que tiene por característica indicar paso por paso como se crea un gráfico, solicitando la información que requiere para efectuar la operación. Las macros son programas que se pueden grabar en una Hoja de Cálculo, permitiendo ejecutarlos en cualquier instancia y en la hoja que desee. De esta forma se pueden automatizar las diversas operaciones desarrolladas en Excel, ganando tiempo y flexibilidad en los trabajos repetitivos. Si en reiteradas ocasiones se debe modificar la Fuente de Letra a un tipo estándar, es decir, que sea igual para todas las planillas que se desee crear, lo más práctico es crear una Macro que le permita automatizar esta tarea, evitando así tener que efectuar el mismo procedimiento para cada Hoja de Cálculo, que se desee generar. Se pueden ejecutar diversas tareas sobre una macro que corresponden a: Grabar una macro, Ejecutar una macro, Revisar o examinar una macro, Imprimir una macro y Grabar el archivo que contiene la macro. Por medio de la barra de herramientas , Excel ofrece la posibilidad de crear objetos gráficos: líneas rectas y curvas, rectángulos, círculos, flechas, curvas, etc., en las hojas de cálculos, gráficos y macros. Además de estos entornos de trabajo, Excel presenta la capacidad de incorporar (bitsmaps) según el procedimiento estándar (OLE) utilizando el portapapeles de Windows, lo que permite añadir imágenes y fotografías en las hojas de cálculo. Elaborado por: Ing. Marcos González

Excel al operar en entorno Windows, permite trabajar con un número ilimitado de ventanas que pueden desplazarse o redimensionarse a voluntad, conteniendo cada una de ellas un libro de trabajo con un máximo de hasta 255 hojas de cálculo.


1 CONOCIENDO EXCEL: Comencemos realizando un sondeo de las funciones básicas con las que cuenta Excel en su extensa barra de herramientas. Cada menú se encuentra ordenado por secciones las cuales son usadas según sea el requerimiento del usuario

1.1 NAVEGANDO POR LOS MENU DE EXCEL: MENU ARCHIVO:

En este menú podemos encontrar todo lo relacionado con las opciones de formato de los datos ingresados en las celdas, a excepción de la sección modificar donde se encuentra una función matemática usada cotidianamente llamada autosuma, la cual permite sumar varias celdas en fila o columna a la vez. MENU INSERTAR:

Al igual que en el menú anterior, se encuentra dividido por secciones y aquí se encuentra todo lo relacionado con diversos tipos de objetos a insertar en la hoja, tales como: Tablas, Gráficos, Cuadros de Texto, Objetos. MENU DISEÑO DE PÁGINA:

Aquí encontrará todo lo relacionado con el formato de la página al estilo de impresión Elaborado por: Ing. Marcos González

MENÚ FORMULAS:

Aquí encontrará todo lo referente a las diversas funciones de cálculos contenidas en Excel, así como opciones de búsquedas y enlaces entre varias funciones. MENU DATOS:

Este menú es usado cuando se desea usar Excel como base de datos MENU REVISAR:

Cuando se requiere una revisión de la ortográfica de algunas palabras, anexar comentarios informativos a celdas específicas ó proteger una hoja o el libro completo este es el menú a usar. MENÚ VISTA:

En este menú encontraremos las opciones necesarias para darle la apariencia necesaria a la hoja de cálculo. También encontraremos una opción muy importante llamada macros la cual será muy usada en este curso.


1.2 SUMINISTRAR DATOS: Excel admite varios tipo de formatos de datos: texto, numéricos, fecha y hora; con varios tipos de formatos, los cuales pueden ser analizados por medio de funciones y gráficos.

Se tiene la nomina de varios empleados de una industria química donde se establece el monto anual a percibir. Su trabajo es insertar los diez empleados en una hoja de cálculo en Excel. 1 PADILLA

PEDRO

15/11/1978

54765

2 PEREZ

FIDEL

16/04/1983

34563

3 ARIAS

DAVID

17/11/1996

48634

4 GONZALEZ

MARIA

29/10/1989

28543

5 VARGAS

ALIDA

26/12/1978

92745

6 JATAR

ANGELICA

18/05/1985

37686

7 QUINTERO

JULIA

21/11/1967

26597

8 GUANIPA

OSCAR

02/02/1991

82643

9 ALVAREZ

NESTOR

12/01/1969

85646

JOHANNA

04/04/1975

12864

10 NAVARRO

Solución: Los números cuando son consecutivos como el mostrado en la tabla anterior pueden ser escritos de manera automática simplemente escribiendo los dos iniciales, sombreándolos luego y arrastrándolo hacia abajo hasta el número deseado.

1.-

2.-

3.-

El resto de los valores es escrito tal cual como lo indica la tabla anterior. Elaborado por: Ing. Marcos González

1.3 CONFIGURANDO LOS DATOS DE UNA CELDA: Los números pueden admitir diversos tipos de formatos dependiendo de cómo se desean expresar los datos, dicha configuración puede ser establecida bien sea por la barra del menú o por medio de la opción ubicada por la ventana mostrada a hacer click al botón derecho del mouse tal como se muestra a continuación Por medio de la barra:

Por medio del botón derecho del mouse:

Cabe destacar que se deben seleccionar las celdas a modificar. Hacer click en formato de celdas…


Dicha ventana también puede ser mostrada haciendo click en la esquina inferior de la sección ubicada en la barra de herramientas. click

Problema: Añadir a la tabla anterior, dos columnas adicionales con los sueldos anuales de cada empleado y mostrar cada columna en un formato distinto. La primera columna en formato numérico La segunda columna mostrando el tipo de moneda 





La tercera columna en formato científico.

Paso 01: Duplicar Datos


Paso 02: Dar formato a Datos.

1.4 EXPLORANDO EL ESTILO DE REFERENCIA F1C1: Anteriormente se estaba usando el estilo de referencia A1, el cual Excel trae por defecto. Existe otro estilo de formato referencial llamado F1C1 (en versiones de office en ingles es llamado R1C1), muy usado en el área de ingeniería si se deben realizar operaciones matriciales. El estilo F1C1 identifica con números la ubicación de la fila y la columna de la celda en donde nos encontramos ubicados, por ejemplo F1C1 indica que estamos ubicados en la fila 1 columna 1. Para usar este estilo debemos activarlo primeramente tal como lo indican los pasos en las figuras siguientes: Paso 01: Hacer click en el icono principal de Office

Paso 02: Se mostrará una ventana de dialogo donde se encuentran varias opciones posibles a aplicar. Hacer click en opciones de Excel. Elaborado por: Ing. Marcos González

Paso 03: Se mostrará una ventana con varias opciones de modificación de los atributos que Excel trae por defecto, vamos a hacer click en Formulas:

Has click en la opción seleccionada y luego Aceptar. Te darás cuenta cómo cambia la fila inicial que contenía las letras de identificación de cada columna, ahora aparecerán los números en orden progresivo. Elaborado por: Ing. Marcos González

Debes notar que Excel modifica automáticamente las formulas. Por ejemplo, la formula de la celda G14 (ahora F14C7) era =SUMA(G4:G13); ahora es =SUMA(F(-10)C:F(-1)C).

1.5 ENTENDIENDO PROCEDENCIA DE LOS O PERADORES: Excel optimiza las formulas aplicando operadores iniciando la formula con el signo igual. Tú puedes comenzar escribiendo tú formula usando operadores matemáticos según el orden de procedencia: negación, exponente, multiplicación, división, adición y substracción. Problema: Vamos a conocer el orden en que Excel ejecuta operaciones en formulas. Tu puede cambiar la procedencia de los operadores usando paréntesis. Apliquemos el siguiente ejemplo: Si tu suministras la formula =A1+B2/C3 en una celda, Excel realizará la división primeramente y luego la suma. Ahora usemos los paréntesis =(A1+B2)/C3, Excel calculará lo que está encerrado dentro del paréntesis y luego realizará la división.   



   



1.6 USANDO EXPONENTES EN FORMULAS: El exponente es un operador muy usado en ecuaciones de ingeniería para elevar una variable a una potencia x. Problema: Apliquemos un ejemplo: tú necesitas escribir una fórmula para estimar las raíces de algunos números pero tú no sabes cómo escribir la sintaxis de exponente en Excel. Para los siguientes ejercicios usaremos el operador (^). Calcularemos la raíz cubica y cuadrática del numero escrito en la celda A1. Ahora nos ubicaremos en cualquier celda y realizaremos el cálculo de la siguiente manera =A1^(1/3) Elaborado por: Ing. Marcos González

=A1^(1/4) ó A1^0,25 Nota que en la segunda formula se establecen dos formas de realizar los cálculos los cuales arrojarán los mismos resultados. Tú puedes llegar a realizar otras combinaciones de operadores donde el exponencial representa un punto clave en el resultado, por ejemplo: =A1^C5, =A1^(C5+D10), ó =(A1+A2)^(C5/E8)

1.7 EXPLORANDO FUNCIONES: Los operadores matemáticos (+, -, /, *, ^) no satisfacen todos los cálculos que muchas veces llegamos a necesitar, cuando este sea el problema podemos usar la herramienta de funciones de Excel, la cual posee funciones como cálculos de seno, coseno, valor absoluto entre otros. En este manual estaremos aplicando algunas funciones establecidas por Excel para varios cálculos de análisis de datos, conversión de unidades y algunos cálculos de ingeniería. Al inicial el manual dimos un recorrido por la extensa barra que posee Excel 2007 y se pudo observar que se posee una pestaña llamada “Formula”, donde se encuentran desglosado por categoría las funciones preestablecidas. Otra forma de ubicar las formulas la cual llega a tener semejanza con Excel 2003 es haciendo click en fx:

Donde aparecerá una ventana con las mismas funciones establecidas por Excel en su barra de herramienta:


Excel posee una gran variedad de funciones establecidas y organizadas por categorías: Funciones de Base de Datos: Incluye funciones que permiten obtener información de una base de datos creada y realizar algunos análisis estadísticos a dichos datos. Funciones de Fecha y Hora Incluye funciones que permiten trabajar realizando cálculos con fechas y horas, por ejemplo tu puedes calcular el número de días laborables entre dos fechas, en donde se usa la función DIAS.LAB. Otro uso es colocar en la hoja de cálculo a diseñar la fecha y hora actual. Funciones de Ingeniería: Incluye funciones que permiten trabajar con números complejos, convertir entre sistemas numéricos, convertir entre sistemas de medida. También posee otras funciones especializadas. Funciones Financieras: Las funciones financieras incluye muchas funciones que ayudan a analizar tasas de interés, depreciaciones, anualidades, inversiones y otras variables usadas por ejemplo para medir la Elaborado por: Ing. Marcos González

rentabilidad de un proyecto, observar el comportamiento de un financiamiento ó hasta establecer las cuotas a cancelar de un préstamo. Funciones lógicas: Incluye un pequeño stock de funciones lógicas. Por ejemplo, la función SI permite evaluar una expresión lógicamente y retornar un valor si la expresión es verdadera y otro si es falsa. Existen otras funciones como Y, O, NO, que ayudan a la construcción lógica de expresiones. Funciones de información: Son muy usadas para informar la existencia de un dato en tabla entre otras cosas. Funciones matemáticas: Ayudan a realizar cálculos diversos, e incluye operaciones como obtención del valor absoluto, calculo de raíces cuadradas, podemos trabajar con logaritmos y funciones exponenciales así como trigonométricas. Este punto será detallado más adelante del manual. Funciones estadísticas: Las funciones estadísticas son muy usadas para cálculos de promedios, desviaciones estándar, varianza, y muchos otros tipos de cálculos. También encontraremos funciones más complejas como cálculos de probabilidad y distribución, incluyendo binomial, weibull y distribuciones normales. Funciones de texto: Incluye funciones para manipular texto y convertir datos numéricos a texto.

1.8 FORMATEANDO TU HOJA DE CÁLCULO: Puede que te hayas encontrado con has realizado algunos cálculos pero tu hoja no está presentable. En esta sección veremos con establecer un formato para hacer de tu hoja más presentable y mejor organizada. En la sección 1.3 discutimos como darle formato a una celda para especificar el tipo de dato (texto, numérico, contabilidad, etc). En esta sección mostraremos nuevas técnicas de formato de datos. Para ello realizaremos un ejercicio y así tener una mejor comprensión del tema. Elaborado por: Ing. Marcos González

Se desea realizar una hoja de cálculo para calcular los valores de una función de la forma y=xn, donde n es el exponente. También calcularemos el área debajo de la curva de 0 a x. el resultado será un grafico el cual estará ubicado adyacente a l tabla de cálculo (el formato de la grafica será explicado en detalle en una sección posterior). El cálculo del área bajo la curva será realizado aplicando el teorema del trapecio aplicando la siguiente función:  

  

 



La tabla sin formato se muestra a continuación:

Esta no sería la mejor forma de presentar los datos, razón por la cual procederemos a mejorar el formato para su posterior presentación, la cual debe estar como la mostrada a continuación:


Excel posee formatos de tabla, formatos para celdas específicas y formatos condicionales.

Por ahora realizaremos el formato de forma manual para lo cual nos ubicaremos en la celda inicio:

Allí encontraremos las funciones necesarias para darle el formato adecuado a nuestra tabla. Los bordes de la tabla son establecidos por el botón donde encontraremos opciones para seleccionar color, grosor y tipo de línea a dibujar, tal como lo muestra la siguiente figura:


Allí encontraremos una serie opciones las cuales serán explicadas en el curso útiles para dar un formato presentable y digno de presentación. Excel trae bordes predefinido como los mostrados y también trae una opción dibujar bordes, donde podremos dibujar el tipo de borde que deseemos. El fondo de la celda será establecido en el icono podremos escoger el color del relleno de las celdas.

donde

El formato del texto fue explicado anteriormente.

Otros valores de formato a tener en cuenta son: Alineación: Establece la posición, orientación y justificación del texto entre la celda. Fuente: Especifica el tipo de fuente, estilo, y tamaño del texto a mostrarse en el texto.


1.9 ASIGNANDO NOMBRES A CELDAS (VARIABLES PROGRAMABLES): Frecuentemente usamos una celda en particular y nos cansamos de escribir la referencia. Tu puedes establecer una sintaxis mas descriptiva, similar a las usadas por variables o nombres de constantes en un lenguaje de programación tradicional. Vamos a realizar algunas prácticas de asignación de nombres a celdas, selecciona la celda o rango de celdas a cual tú quieres definir un nombre. Luego selecciona en la barra de herramientas la pestaña Formula y seguidamente asignar nombre a un rango tal como se muestra en la figura:

Allí te mostrará una ventana donde te indicara la casilla donde se establecerá el nombre a la celda, algún comentario a realizar de la variable y posteriormente la o las celdas a la cual se le establecerá el nombre.

Para afianzar los conocimientos aplicaremos lo aprendido al ejercicio anterior dándole un nombre al valor del exponente y posteriormente modificando la fórmula del cálculo de Y. Colocaremos en la casilla nombre el texto con el que se conocerá la celda. Elaborado por: Ing. Marcos González

El ámbito establece si dicho nombre será válido para el libro completo o solo para una hoja en específico.

1.10 VALIDACION DE DATOS: ¿Tú quieres suministrar tu hoja de cálculo pero no suministrar los datos de forma inapropiada? Excel permite especificar condiciones que permitan establecer el tipo de datos a ser suministrados en algunas celdas. Por ejemplo, puedes decirle a una columna que admita solo números enteros, o al contrario puedes decirle que admita solo texto; en cualquiera de los dos casos Excel bloquea para que solo admita lo deseado. Existen casos en donde poseemos columnas extensas de datos y deseamos eliminar datos que se consideren fuera de los rangos, Excel te permite señalar los datos fuera de especificaciones. Todas estas opciones las podemos encontrar en la pestaña datos de la barra de herramientas:

Antes de hacer click en la opción validación de datos debemos seleccionar las celdas a validar. Cabe destacar que las otras dos opciones son usadas cuando queremos observar los datos que no cumplen con las especificaciones o cuando queremos borrar los círculos a los datos desechados. Vamos a hacer un ejercicio asumiendo valores al azar, para ello usaremos la función =ALEATORIO.ENTRE la cual nos permitirá crear valores aleatoriamente entre dos valores límites: inferior 50, superior 70. Dicha función la extrapolaremos 20 celdas más abajo, vamos a asumir que dichos datos son el reporte de un medidor de temperatura a la salida de un intercambiador de calor y deseamos observar cuales valores salen de las especificaciones de la temperatura en operación normal la cual debe estar entre 60 y 65 F.


Para ello realizaremos una validación de los datos donde primeramente seleccionaremos validación de datos, tal como lo muestra la figura

Allí seleccionaremos la opción decima, lo que dará a entender a Excel que las celdas seleccionadas solo admiten números y que estos pueden contener decimales

Luego podremos seleccionar la condición de los datos para la cual dejaremos la opción ENTRE, la cual establece el rango entre los cuales los valores se encuentran dentro las especificaciones deseadas. Una vez suministrado todos los datos podemos hacer click en aceptar. Elaborado por: Ing. Marcos González

Ahora si quisiéramos determinar cuáles valores son los que salen fuera de especificación seleccionamos las celdas que contienen los valores y seguidamente nos ubicamos en validación de datos y allí seleccionamos la opción

Allí podrán observar cómo se marcan los valores que salen fuera de especificaciones.

1.11 SUMANDO COMENTARIOS Y ECUACIONES: ¿Te gustaría agregar comentarios a tu hoja de cálculo? Tú puedes lograr que celdas específicas estén descritas con un texto insertado en un comentario. Puedes mostrar las ecuaciones a usar también. Vamos a comentar el ejercicio anterior y a colocar la ecuación usada para el cálculo del área bajo la curva. Para sumar un comentario, primero selecciona la celda al cual tu quiere comentar, una vez seleccionada selecciona la pestaña revisar de la barra de herramientas y seguida mente NUEVO COMENTARIO. En caso de que ya exista un comentario puedes modificarlo seleccionando la opción MODIFICAR COMENTARIO. PASO 01:


PASO 02:

PASO 03:

El block que contendrá el comentario tendrá como Identificador el nombre con el que se haya registrado Office. Para agregar una ecuación, tú necesitas usar Microsoft Equation Editor; para ello nos iremos a la pestaña INSERTAR y luego seleccionaremos OBJETO

Luego te mostrará una ventana que te mostrara todos los tipos de objetos que puedes insertar, de la lista vamos a buscar Microsoft equation 3.0, y luego presiona ACEPTAR.


La ventana se cerrará y luego aparecerá la hoja de cálculo en modo editor de ecuaciones.

En donde se encuentra el rectángulo es donde se podrá insertar una ecuación usando para ello la barra de herramientas del editor. Escribiremos la siguiente ecuación:  

   

 

1.12 VENTAJAS DEL USO DE MACROS EN EXCEL: es un programa que tiene un gran potencial, pero la mayoría de la gente lo maneja de una forma muy simple, utilizando solo opciones básicas, pero hay algo muy importante que tengo que decirles. cuenta con un lenguaje muy poderoso llamado , este es solo una parte del lenguaje, pero permite hacer o resolver los problemas mas fácilmente, solo debemos aprender a programarlo y para eso es este curso, podría la gente decir que este curso es un nivel muy alto de y quizás si lo sea, pero es fácil de aprender ya que se manejaran Elaborado por: Ing. Marcos González

términos sencillos, a mi me gusta hablar con palabras que todo el mundo entienda y eso lo hace más fácil. La programación que emplea en este curso o las estructuras que aparecen son creadas por su servidor, ya que para manejar la programación de con es necesario tener mucha creatividad, cada persona puede crear estructuras diferentes pero que trabajen igual. Así que manos a la obra. Primeramente debemos de saber que es una Macro y a continuación se explica el término:

Una Macro son una serie de pasos que se almacenan y se pueden activar con alguna tecla de control y una letra. Por ejemplo, todos los días empleo frecuentemente en mis celdas los mismos pasos: Tamaño de Fuente (Tamaño de la letra), Negrita, Fuente (Tipo de letra) y Color de Fuente (Color de Letra), para no estar repitiendo estos pasos los puedo almacenar en una macro y cuando ejecute la macro los pasos antes mencionados se ejecutaran las veces que yo lo desee. Realizaremos un pequeño ejemplo para mostrar uno de los tantos usos de usar macros en Excel, asumamos que tenemos un estilo de formato que debe ser aplicado a todas las celdas que están especificadas como titulo de una columna. La cual deben poseer las siguientes especificaciones: relleno azul, color de la letra Blanco, Negrita, Subrayado, tipo de letra Consolas y tamaño 12. Primeramente hacemos click en la pestaña VISTA de la barra de herramienta y allí encontraremos la opción MACROS

PASO 02: Elegimos la opción GRABAR MACRO:


PASO 03: Luego de hacer click allí aparecerá una ventana donde tendremos que rellenar algunas casillas,

Allí puedes especificarle un nombre a la macro, el cual puede ser “FORMATOCELDA”; luego un

método abreviado el cual simplemente seleccionamos la letra que en conjunto con la opción CTRL ejecutará la función. La opción Guardar macro en establecerá la opción si la macro podrá ser ejecutada en todo el libro o solo en alguna hoja en especifico. La opción descripción establece un sitio en donde podemos anexar un comentario de la macro que se está grabando. PASO 04: Luego de hacer click en Aceptar realizamos todas las modificaciones de formato establecidas. PASO 05: una vez finalizado de realizar todas las modificaciones se procede a detener la Grabación de la macro siguiendo un procedimiento parecido a los pasos 01 y 02, con la diferencia que en el segundo paso ahora seleccionaremos detener macro.


Ahora cada vez que queramos aplicar las modificaciones grabadas, simplemente nos colocamos en la celda a modificar y ejecutamos la macro, bien sea por medio de la tecla abreviada seleccionada o haciendo click en ver macro

Donde seleccionaremos la macro a ejecutar.


2 GRAFICANDO: 2.1 CREANDO UN GRAFICO SIMPLE Problema: debes graficar la data para su análisis. A continuación se muestra la data: Tiempo

Producción 1 37.5131834 2 40.5870482 3 47.2848968 4 57.5667231 5 42.5779321 6 49.0158358 7 49.3924462 8 59.5610631 9 54.5339902 10 54.5664024 11 59.5279574 12 53.4962966 13 54.1583917 14 56.6794541 15 60.3466846 16 47.3722481 17 56.4048891 18 51.0720612 19 46.9764076 20

53.707521

21 52.0576408 22

46.951567

Ir al función insertar posteriormente en el grupo gráficos, Dispersión:


Allí muestra una serie de formatos de diversos tipos de gráficos XY, muy usados en el área de ingeniería. Como lo que se busca es ver el comportamiento de la producción estilo un grafico de control seleccionamos los valores y seguidamente hacemos click en el cuarto formato.

Producción 70 60 50 40 30

Producción

20 10 0 0

5

10

15

20

25

Allí podemos observar el comportamiento de la producción de una empresa X, pero ahora podemos observar que falta darle ciertas características que toda gráfica debe llevar como etiqueta a los ejes, el titulo que corresponde, mejorar los formatos entre otros. Para ello aplicamos formato a las gráficas: Haciendo un click en el gráfico anterior observaremos que se activan tres nuevas pestañas

Allí podremos realizar todas las modificaciones pertinentes al gráfico. Elaborado por: Ing. Marcos González

En diseño podemos modificar el color de la línea, mover el gráfico a una hoja, seleccionar o añadir datos, cambiar entre filas y columnas, entre otros. La pestaña presentación establece todas las opciones requeridas establecer la forma de presentación de los resultados, modificando o moviendo para una mejor apariencia las etiquetas.

Comportamiento de la Producción 70 o 60 ñ a / 50 L B B M 40 n o i 30 c c u 20 d o r P 10

Producción

0 0

5

10

15

20

25

Tiempo

Si quisiéramos mejorar aun más la escala para observar mejor el comportamiento de la producción solo debemos hacer click al botón derecho del mouse y seleccionar


Allí podrán observar cómo se pueden modificar las opciones de eje, donde se podrá establecer los límites mínimos y máximos de la escala.

2.2 EXPLORANDO ESTILOS DE GRAFICOS: La hoja de cálculo proporciona diversos tipos de gráficos que le permitirán representar los datos de diversas maneras y cada uno de éstos, poseeA características hacen de mayor utilidad en determinados conjuntos de datos. continuación separticulares describiránque los le tipos de gráficos que proporciona la hoja de cálculo.

Columna

Permite hacer comparaciones entre elementos.

Barras

Se utilizan para comparar elementos, las categorías se organizan verticalmente y los valores horizontalmente.

Líneas

Muestran las tendencias en los datos a intervalos iguales.

Circulares

Se emplean para mostrar el tamaño de cada elemento como proporción de un total (100%). Sólo grafican una serie de datos, aunque uno de sus subtipos puede mostrar el desglose de un sector en otro gráfico.

Burbujas

Es un gráfico de dispersión que maneja tres variables. Elaborado por: Ing. Marcos González

Cotizaciones

Se utiliza para ilustrar la cotización de acciones o puede usarse para datos científicos.

Cono, pirámide Los gráficos de barras o columnas pueden reemplazarse por pirámides, y cilindro cilindros o pirámides. Anillos

Permiten representar varias series en gráficas circulares.

Área

Presenta la tendencia de los valores a lo largo del tiempo o entre categorías.

XY dispersión

Muestran la relación entre los valores numéricos de varias series de datos. Se recomienda para datos científicos.

Radial

Representa cambios de valores en relación con un punto central.

Superficie

Muestra tendencia de los valores en dos dimensiones a lo largo de una curva continua, generalmente se utiliza para graficar rangos de valores.

Mapas

Grafica datos numéricos asociados a entidades geográficas.

2.3 PERSONALIZAR LOS EJES DE GRAFICAS: De manera predeterminada, Microsoft Office Excel determina los valores de escala mínimo y máximo eje vertical (valores) de unlosgráfico. embargo, personalizar la rango escala muy para adaptarladel a sus necesidades. Cuando valoresSin trazados en puede el gráfico cubren un grande, puede cambiar también el eje vertical (valores) a una escala logarítmica. En un gráfico, haga clic en el eje vertical (valores) que desea cambiar o realice el siguiente procedimiento para seleccionar el eje de una lista de elementos del gráfico: 1. Haga clic en el gráfico. Se mostrarán las Herramientas de gráficos con las fichas Diseño, Presentación y Formato. 2. En la ficha Formato, en el grupo Selección actual, haga clic en la flecha situada junto al cuadro Elementos de gráfico y, a continuación, haga clic en Eje vertical (valores). 2.

En la ficha Formato, en el grupo Selección actual, haga clic en Aplicar formato a la

selección. Elaborado por: Ing. Marcos González

3. Haga clic en Opciones del eje, si no está seleccionada esta opción, y realice alguna de estas operaciones: IMPORTANTE Las opciones de escala siguientes sólo están disponibles cuando se selecciona un eje vertical (valores). Opciones del eje proporciona diferentes opciones para el eje horizontal (categorías).

Para cambiar el número en el comienza o termina el eje vertical (valores), para la opción Mínimo o Máximo, haga clic en Fijo y, a continuación, escriba un número diferente en el cuadro Mínimo o Máximo. Para cambiar el intervalo de las marcas de graduación y de las líneas de división del gráfico, para la opción Unidad mayor o Unidad menor, haga clic en Fijo y, a continuación, escriba un número 



diferente en el cuadro Unidad mayor o Unidad menor. Para invertir el orden de los valores, active la casilla de verificación Valores en orden inverso. 

NOTA Al cambiar el orden de los valores del eje vertical (valores) de abajo a arriba, los rótulos de categoría del eje horizontal (categorías) se voltearán de abajo a arriba en el gráfico. Del mismo modo, al cambiar el orden de las categorías de izquierda a derecha, los rótulos de valor se voltearán de izquierda a derecha en el gráfico. 

Para cambiar el eje de valores a logarítmico, active la casilla de verificación Escala logarítmica.

NOTA No se pueden utilizar escalas logarítmicas para valores negativos o cero.




Para cambiar las unidades de visualización del eje de valores, en la lista Unidades de visualización, seleccione las unidades que desee.

Para mostrar un rótulo que describa las unidades, active la casilla de verificación Mostrar rótulo de unidades. SUGERENCIA Cambiar la unidad de visualización es útil cuando los valores del gráfico son números grandes que desea reducir que resulten más fácilesa de leer en elcomo eje. Por puede mostrar valores del gráfico quepara abarquen desde 1.000.000 50.000.000 de ejemplo, 1 a 50 y mostrar un rótulo que indique que las unidades se expresan en millones. 



Para cambiar la ubicación de las marcas de graduación y rótulos del eje, seleccione la opción que desee en los cuadros Marca de graduación principal, Marca de graduación secundaria y Etiquetas del eje. Para cambiar el punto de intersección entre el eje horizontal (categorías) y el eje vertical (valores), en El eje horizontal cruza, haga clic en Valor del eje y, a continuación, escriba el número que desee en el cuadro de texto, o haga clic en Valor máximo del eje para especificar que el eje horizontal (categorías) se cruza con el eje vertical (valores) en el valor máximo del eje.

NOTA Al hacer clic en Valor máximo del eje, los rótulos de categoría se mueven al lado opuesto del gráfico. SUGERENCIA En los gráficos XY (de dispersión) y de burbujas, se muestran valores en el eje horizontal (categorías) y en el eje vertical (valores), mientras que los gráficos de líneas sólo se muestran en el eje vertical (valores). Esta diferencia es un factor importante a la hora de decidir qué tipo de gráfico utilizar. Puesto que la escala del eje horizontal (categorías) del gráfico de líneas no puede sufrir tantas modificaciones como la escala del eje vertical (valores) que se utiliza en el gráfico xy (de dispersión), podría ser conveniente utilizar un gráfico xy (de dispersión) en lugar de un gráfico de líneas si es necesario cambiar la escala de ese eje o mostrarla como una escala logarítmica.

Ejercicio: al grafico anterior, modificarle los ejes de manera de ajustarlos para observar un mejor comportamiento del grafico. Elaborado por: Ing. Marcos González

2.4 ESTABLECIENDO ESCALA S EMILOGARITMICA Y LOGARITMICA: ¿Qué es una escala logarítmica? Supongamos que en un eje queremos representar los caudales de varios cauces y disponemos de los datos que aparecen a la derecha, ya ordenados de menor a mayor, desde un arroyo con 16 litros/seg hasta un gran río con 154 m3/seg: Si representamos estos datos en una escala aritmética (un papel cuadriculado normal) quedará algo tan poco expresivo como esto:

Los primeros amontonados encima 0, de modocalculamos que no sería si queremos que cuatro aparezcan todosestán los valores. Probamos otradelestrategia: los válido logaritmos de los caudales, y los representamos de nuevo en un papel milimetrado corriente. El resultado será el siguiente:


Ahora los puntos aparecen bien diferenciados, pero, además de la molestia de tener que calcular los logaritmos, el observador no capta los valores: ¿cómo podemos adivinar que el punto situado en 1,50 en realidad se refiere a un caudal de 32 m3/seg? La solución es representar los puntos en una escala logarítmica: no es preciso calcular nada, nosotros situamos en la escala los valores de los caudales, pero lo que determina su posición son los logaritmos de los caudales:

Observamos que, efectivamente, la situación relativa de los puntos en las dos últimas escalas que hemos dibujado es idéntica. Por tanto, representar puntos en una escala logarítmica es equivalente a representar los logaritmos de esos valores en una escala milimetrada normal. Para entenderlo a la inversa: podemos construir nuestra propia escala logarítmica calculando los logaritmos de 1, 2, 3, 4, ...9, 10, 20, 30, 40,...,90, 100, 200, etc. Y representando los logaritmos en un papel milimetrado normal. ¿Por qué dibujamos puntos en una escala logarítmica? Ya hemos visto que la utilidad fundamental de la escala logarítmica consiste en que podemos representar valores de magnitudes muy diferentes. También son convenientes cuando nos permiten convertir el gráfico que relaciona dos variables en una recta. Se utilizan dos tipos de gráficos: Semilogarítmicos: Uno de los dos ejes está en escala logarítmica el otro en escala aritmética. Doble logarítmico o simplemente logarítmico. Los dos ejes están en escala logarítmica. La opción para transformar un gráfico XY en logarítmico está presente en Formato de eje:


Esta opción es aplicable a cualquier de los dos ejes.

Ejemplos: La fórmula que expresa la disminución del caudal de un río (Qt) en función del tiempo (t) es la siguiente:

Donde Qo , e y α son constantes. Si tomamos logaritmos, esta ecuación se transforma en:

Por tanto, si hacemos : y= log Qt , obtenemos la ecuación de una recta:

Como esto es conveniente para nuestros cálculos, en lugar de representar el caudal (Qt) en función delhorizontal). tiempo (t), podemos representar el logaritmo del caudal (eje vertical) en función del tiempo (eje Elaborado por: Ing. Marcos González

En general, una función del tipo: y = 2,7x, al tomar logaritmos quedará como: log y = x . log 2,7 Por tanto, si representamos el logaritmo de y en función de x, obtendremos una recta de pendiente log 2,7. En otros casos, es el eje horizontal el que conviene representar en escala logarítmica, como los descensos observados en un pozo que bombea a lo largo del tiempo. En los primeros minutos el nivel desciende rápidamente, luego lo hace cada vez con mayor lentitud. La representación gráfica será una curva (izquierda). Pero puede obtenerse una recta si se representa el descenso en función del logaritmo del tiempo (derecha):

Por otra parte, una función del tipo : y = x2,7, al tomar logaritmos quedará como: log y = 2,7 . log x Por tanto, si representamos el logaritmo de y en función del logaritmo de x, obtendremos una recta de pendiente 2,7.


2.5 CAMBIANDO LOS TIPOS DE GRÁFCOS: Muchas veces luego de crear gráficos se decide usar un tipo diferente que pudiera ser más efectivo, por lo cual tú deseas modificar el existente sin necesidad de crear otro. Para solucionar tu problema solo debes seleccionar el gráfico a modificar y en la barra de herramientas se activará la opción herramientas de gráficos, solo debes hacer click en la pestaña diseño y allí encontrarás la opción cambiar de grafico.

Un simple ejemplo es reconsiderar los datos usados en el ejemplo anterior y forzarlo a un grafico radial. 1 2200,00 505,00 15 2100,00 500,00

14

2 3

2000,00 495,00 1900,00 490,00 1800,00

13

4 CALOR HX-1

1700,00 485,00

12

5

11

CALOR HX-2

6 10

7 9

8


2.6 COMBINANDO TIPOS DE GRAFICOS: Vamos a asumir que tu quieres diversos datos en el mismo grafico pero usando diferentes estilos. Para resolver este problema solo debes graficar todos los datos a estar incluidos en el gráfico, posteriormente se realiza la operación del apartado anterior, donde solo se procede a cambiar de grafico. Un ejemplo típico usado para este tipo de opción es en el análisis estadístico cuando se requiere usar un diagrama de pareto.

Valores

Acumulado

59

59

800

48

107

700

48

155

53

208

600

44

252

500

40

292

48

340

400

Valores Acumulado

58

398

300

43

441

200

42 50

483 533

100

51

584

0

56

640

59

699

1 2 3 4 5 6 7 8 9 10 11 12 13 14

2.7 EDIFICANDO GRAFICOS DE SUPERFICIES: En muchos casos crear superficies 3D en Excel es poco estudiado por el usuario, siendo esta una herramienta muy útil en el campo de la ingeniería, donde podemos presentar resultados de un estudio de optimización multidimensional donde podemos ver el comportamiento de hasta dos variables con respecto a otra, también es muy usado en el área civil cuando se desean representar datos topográficos de la superficie de un terreno. Elaborado por: Ing. Marcos González

El tipo de eje usado para este tipo de gráfico es el X, Y, Z, donde su equivalente son las coordenadas geográficas, Norte, Sur, Este y Oeste. Los datos se representan en la hoja de Excel de la siguiente manera

Y1

Z11

Z21

Y2

Z12

Z22

Los ejes X e Y es la ubicación en un plano 2D, l eje Z es el que designa la altura del punto a graficar. EJEMPLO: Graficar las siguientes ecuaciones para observar su comportamiento Tridimensional y observar el efecto de una variable con respecto a la otra. Z

 COS ( X 2  Y 2 )   * K  1  2 2 X Y  

Z

 COS (Y )  SENO( X )

Los limites de las graficas es: para X e Y desde -3 hasta 3 (grafique como mínimo 13 puntos). Elaborado por: Ing. Marcos González

SOLUCION: El procedimiento es sencillo, ubicar en una fila los valores correspondientes al eje X y en la primera columna los valores del eje Y tal y como lo muestra la figura anterior. En la parte interna de la tabla que se está creando deben ir los cálculos correspondientes al eje Z los cuales se realizarán de acuerdo a la ecuación a evaluar.

Una vez realizado los cálculos se seleccionan los valores y en la pestaña insertar se selecciona el grafico 3D que mejor represente lo que se desea mostrar, bien sea en solido o estilo malla.

2 3

1 1 0

-1 3 -

-1

5 , 2 -

2 -

5 , 1 -

1 -

5 , 0 -

0

5 , 0

1

5 , 1

-3 2

5 , 2

3

1-2 0-1 -1-0 -2--1

-2

-3--2

-3

-4--3

-4


Los resultados de la segunda ecuación son los mostrados a continuación: 2 1-2

1 2 -0,5

0 -1

-3

-2

-1

0

1

2

0-1 -1-0

-3

-2--1

3

-2

Existen otro tipo de gráficos de superficies muy usado cuando se quiere observar los relieves de una superficie pero visto desde un ángulo aéreo. Estos gráficos reciben el nombre de Gráficos de contornos. Ejemplo: Se tienen los datos topográficos de una superficie la cual se desea observar para establecer el grado de desnivel que presenta dicha superficie. Grafique los resultados: NORTE 345697

E T S E

338654

331611

324568

317525

310482

303439

296396

289353

282310

275267

900543

50

38

48

41

39

46

46

41

45

39

38

52

32

900438

40

37

39

39

43

38

42

48

37

44

47

41

53

900333

37

45

43

33

43

51

46

32

45

41

49

42

33

900228

46

40

36

39

35

43

46

51

39

40

39

44

43

900123

35

46

33

34

49

42

42

42

41

40

35

42

44

900018

36

44

48

35

35

40

42

38

42

32

29

41

38

899913

38

38

44

36

42

42

43

38

44

39

38

47

43

899808

40

43

38

31

34

39

40

39

44

45

37

44

39

899703

44

43

43

37

44

39

39

48

39

41

46

38

45

899598

42

42

41

33

33

41

47

44

25

35

44

43

37

899493

43

38

40

42

43

30

43

33

33

42

58

37

41

899388

36

35

37

36

38

31

44

35

37

43

40

38

38

899283

37

43

35

40

35

39

45

38

38

35

35

41

42

268224

261181

Igual que en el ejercicio anterior ir a la pestaña insertar y seleccionar insertar gráfico de superficie.


Y se obtendrá la siguiente gráfica: ESTE 899283 ESTE 899493 ESTE 899703 ESTE 899913 40-60 ESTE 900123 ESTE 900333

20-40 0-20

ESTE 900543 9 5 6 6 5 8 3 3 3

1 1 6 1 3 3

8 6 5

5 2 5

2 3

1 3

2 8 0 1 3

9 3 4 3 0 3

6 9 3 6 9 2

3 5 3 9 8 2

0 1 3 2 8 2

7 6 2 5 7 2

2 2 8 6 2

1 8 1 1 6 2

NORTE


3 FUNCIONES MATEMATICAS: 3.1 USANDO FUNCIONES DE SUMA: SUMA(número1,número2,...): El resultado devuelto por esta función es la suma de los argumentos número1, número2, etc.. Los valores no numéricos no son tenidos en cuenta para el cálculo. Los argumentos número1, número2, etc., pueden ser una constante, la dirección de una celda, un rango de celdas. Ejemplo: SUMA(3,3,5,8) da como resultado 19 SUMA(2.5,3,6,8) da como resultado 19.5 SUMA(18,26,12,22,6) da como resultado 84. SUMA.CUADRADOS(número1,número2,...): El resultado de la función es la suma de los cuadrados de los argumentos número1, número2, etc. Es decir, eleva al cuadrado cada valor y va calculando la suma total. Ejemplo: SUMA.CUADRADOS(2,2,4,1) da como resultado 25 SUMA.CUADRADOS(2.5,7,2) da como resultado 59.25 SUMA.CUADRADOS({2;3;4\1;2;3}) da como resultado 43. SUMAPRODUCTO(matriz1,matriz2,matriz3,...): Los argumentos de esta función son mínimo dos matrices y máximo 30. Todas las matrices deben tener el número de filas, del mismo modo, todas las matrices deben tener el mismo número y de columnas. A continuación se muestran los subíndices para matriz1 y matriz2. Matriz1 A11 A12 A21 A22

A13 A23

Matriz2 B11 B12 B21 B22

B13 B23

La función sumaproducto, multiplica los componentes de las matrices y los suma como se muestra enseguida: SUMAPRODUCTO(matriz1,matriz2) calcula A11*B11 + A12*B12 + A13*B13 + A21*B21 + A22*B22 + A23*B23. En este caso se trata de dos matrices de 2 filas y tres columnas, el cálculo es similar para las matrices que tengan cualquier dimensión. Ejemplos: Elaborado por: Ing. Marcos González

SUMAPRODUCTO({2;1\3;2},{6;4\1;2}) da como resultado 23.

En la figura No. 24 puede apreciar que en la celda A7 calculó el valor de la función SUMAPRODUCTO. Tomando como argumentos tres matrices formadas por los rangos A2:B4, D2:E4 y G2:H4. Figura 24

SUMAR.SI(rango,criterio,rango_suma): Esta función se utiliza para sumar un conjunto de valores, dependiendo si cumplen con una condición determinada. El argumento rango, es en donde se va a evaluar la condición. El argumento criterio, es la condición que debe cumplirse para llevar a cabo la suma. El argumento rango_suma establece los valores que se van a sumar si se cumple con la condición. Si se omite el argumento rango_suma, se suman los valores del argumento rango. Por ejemplo, en la figura No. 25 puede ver que la hoja de cálculo contiene una lista. En la columna A aparece tipo de triángulo, en la columna B el área en metros cuadrados, por último, en la columna C la cantidad de triángulos de ese tipo y con el área especificada. En la celda E11 se incluyó la fórmula para sumar la cantidad de triángulos equilateros presentes en la lista. En el rango A2:A9 se va a establecer si se cumple con la condición “Equilatero”. Aquellos registros que

cumplan con la condición sumarán los valores correspondientes. El rango de suma va a ser C2:C9. En este ejemplo se suman los valores de las celdas C3 y C5 que cumplen con la condición “Equilatero” en las celdas A3 y A5 respectivamente. El resultado a esta función es el valor 10.

Se hubiera podido incluir la función del siguiente modo:


SUMAR.SI(B2:B9,”>=28”,C2:C9). En este caso, está determinando cuáles t riángulos tienen un área

mayor o igual a 28, estableciendo la cantidad total de triángulos que cumplen con esta condición.

SUMAX2MASY2(matriz_x,matriz_y): Calcula la sumatoria de los cuadrados de los elementos que contienen las matrices. A continuación aparecen la matriz1 y la matriz2 y se indicará cómo se llevan a cabo los cálculos: SUMAX2MASY2(matriz1,matriz2) se calcula (A 11)2 + (B11)2 + (A12)2 + (B12)2 + (A13)2 + (B13)2 + (A21)2 + (B21)2 + (A22)2 + (B22)2 + (A23)2 + (B23)2. Se puede generalizar el procedimiento. Matriz1 Matriz2 A11 A12 A13 B11 B12 B13 A21 A22 A23 B21 B22 B23 Los dos argumentos son de tipo matriz y las matrices deben ser del mismo tamaño, de no ser así, la función devuelve el código de error #N/A. Ejemplo: SUMAX2MASY2({2;3;5\1;2;3},{1;2;2\3;2;2}) da como resultado 78 SUMAX2MASY2({3;5\2;3},{2;3\4;4}) da como resultado 92. Observe la figura No. 26, en la celda C5 se incluyó la función SUMAX2MASY2, tomando como argumento las matrices en los rangos A2:C3 y E2:G3. El resultado es 124. Elaborado por: Ing. Marcos González

SUMAX2MENOSY2(matriz_x,matriz_y): Esta función devuelve la sumatoria de la diferencia de los cuadrados de los componentes de las matrices, como se aclara enseguida: Matriz1 A11 A12 A21 A22

A13 A23

Matriz2 B11 B12 B21 B22

B13 B23

SUMAX2MENOSY2(matriz1,matriz2) se calcula (A 11)2 - (B11)2 + (A12)2 - (B12)2 + (A13)2 - (B13)2 + (A21)2 - (B21)2 + (A22)2 - (B 22)2 + (A23)2 - (B23)2. Para los casos en que las matrices tienen diferentes dimensiones, la función realiza los cálculos de manera similar. Matriz_x debe tener el mismo número de filas que matriz_y, además, matriz_x debe tener el mismo número de columnas que matriz_y. Por ejemplo, SUMAX2MENOSY2({2;3\1;4},{2;6\3;7}) da como resultado –68 SUMAX2MENOSY2({4;“a”},{2;3}) da como re sultado 12. Como el segundo elemento de matriz1 no es numérico, no hace ningún cálculo tampoco con el segundo elemento de matriz2. En la figura No. 27 puede apreciar que en la celda D6 incluyó el cálculo de la función SUMAX2MENOSY2, tomando como argumentos las matrices ubicadas en los rangos A2:C3 y E2:G3.


SUMAXMENOSY2(matriz_x,matriz_y): Dadas las matrices que aparecen enseguida, esta función lleva a cabo el siguiente cálculo, el resultado de esta función es un único valor numérico: Matriz_x A11 A12 A21 A22

A13 A23

Matriz_y B11 B12 B21 B22

B13 B23

SUMAXMENOSY2(matriz_x,matriz_y) se calcula (A 11-B11)2 + (A 12-B12)2 + (A13- B 13)2 + (A21-B21)2 + (A 22-B22)2 + (A 23-B23)2. En este caso se trata de dos matrices que tienen 2 filas y 3 columnas, sin embargo, el lector puede comprender el procedimiento para matrices con cualquier número de filas y de columnas. Debe cumplirse con la condición de que el número de filas de matriz_x es igual al número de filas de matriz_y y el número de columnas de matriz_x es igual al número de columnas de matriz_y. Por ejemplo: SUMAXMENOSY2({3;5\1;2},{4;7\6;5}) da como resultado 39 SUMAXMENOSY2({1;2;3\4;2;5},{2;3;6\1;2;7}) da como resultado 24 SUMAXMENOSY2({2;3\3;1\4;2},{2;2\1;5}) da como resultado #N/A, indicando que hay error debido a que las matrices son de diferentes dimensiones. En la figura No. 28 puede observar que en la celda C8 se calculó la función SUMAXMENOSY2 tomando como argumentos las matrices comprendidas en los rangos B2:C5 y E2:G5. Las dos matrices tienes 4 filas y 3 columnas.


3.2 FUNCIONES MATEMATICAS Y TRIGONOMETRICAS: A continuación se explican las funciones matemáticas y trigonométricas que incluye Excel. Para acceder a las funciones haga clic en el icono “Insertar función”. I nmediatamente aparece el cuadro de diálogo mostrado en la figura En la lista desplegable denominada “O seleccionar una categoría:” escoja “Matemáticas y trigonométricas”. Posteriormente se muestra en la sección “Seleccionar una función:” la lista de todas las funciones que corresponden a la categoría seleccionada y que son las que se van a explicar de aquí en adelante. ABS(número): Devuelve el valor absoluto del argumento número. Ejemplos: La función ABS(-5) da como resultado 5 La función ABS(10) da como resultado 10. La función ABS(-2) da como resultado 2. ACOS(número): Esta función devuelve el arco coseno del argumento número. Número debe ser mayor o igual que –1 y menor o igual que 1. Los valores devueltos por la función están comprendidos en el intervalo 0 a pi. En el ejemplo de la figura No.2 puede apreciar que en cada una de las celdas de la columna B se calcula el arco coseno para el argumento presente en cada celda correspondiente de la columna A. Al lado derecho se muestra la gráfica generada por Excel con los valores calculados.


ACOSH(número): Esta función calcula el coseno hiperbólico inverso del argumento número. En la figura No. 3 puede observar que en cada una de las celdas de la columna B se ha calculado el coseno hiperbólico inverso, tomando como argumento cada uno de los valores de la correspondiente celda de la columna A. A la derecha se muestra la gráfica generada por Excel utilizando los datos calculados. El argumento dado a esta función debe ser un valor mayor o igual a uno.


ALEATORIO(): Esta función devuelve un valor al azar comprendido entre 0 y 1. Esta función no tiene argumentos. Cada vez que se genere un valor aleatorio será diferente al anteriormente calculado. ASENO(número): Devuelve el arcoseno del argumento número. El valor devuelto por esta función está expresado en radianes. El argumento número debe ser mayor o igual que –1 y menor o igual que 1. El valor devuelto está comprendido en el intervalo comprendido entre -pi/2 a pi/2. En la figura No. 4 se presenta el ejemplo correspondiente. En cada una de las celdas de la columna B se ha calculado el arcoseno tomando como argumento el valor de cada celda correspondiente de la columna A. Figura 4

ASENOH(número): Esta función calcula el seno hiperbólico inverso del argumento número. En el ejemplo de la figura No. 5 puede apreciar que en cada una de las celdas de la columna B se calcula el seno hiperbólico inverso, tomando como argumento el valor de cada celda correspondiente de la columna A. Observe que a la derecha se muestra la gráfica generada por los valores calculados. El argumento de esta función puede ser cualquier valor numérico positivo o negativo.


ATAN(número): Esta función calcula el arco tangente del argumento número. En la figura No. 6 puede apreciar, en cada una de las celdas de la columna B se calcula el arco tangente del argumento representado por celda correspondiente en la columna A.

ATAN2(coord_x,coord_y): Esta función devuelve la tangente inversa de las coordenadas especificadas por los argumentos coord_x y coord_y. El valor devuelto por la función es un ángulo. El resultado viene dado en radianes. Ejemplo: ATAN2(2,1) da como resultado 0.46 radianes. ATAN2(1,1) da como resultado 0.79 radianes. Elaborado por: Ing. Marcos González

ATANH(número): Esta función devuelve la tangente hiperbólica inversa del argumento número. En la figura No. 7 se observa que en cada una de las celdas de la columna B se ha calculado la tangente hiperbólica inversa, tomando como argumento el valor correspondiente de cada celda de la columna A. El argumento debe ser un valor mayor que –1 y menor que 1.

COMBINAT(número,tamaño): El resultado de la función es el número de combinaciones para un determinado número de elementos. Número representa el número de elementos. El argumento tamaño indica el número de elementos en cada combinación. Ejemplo: COMBINAT(25,2) da como resultado 300 COMBINAT(12,12) da como resultado 1 COMBINAT(12,3) da como resultado 220 COS(número): Esta función calcula el coseno del argumento número. El ángulo viene expresado en radianes. Por ejemplo, como puede apreciar en la figura No. 8, en cada una de las celdas de la columna B se calcula el coseno para la celda correspondiente en la columna A. Al lado derecho de la figura puede apreciar la gráfica generada por Excel para los datos introducidos.


COSH(número): Calcula el coseno hiperbolico del argumento número. En el ejemplo de la figura No. 9, en cada celda de la columna B se calcula el coseno hiperbólico para el valor dado como argumento que es cada celda correspondiente de la columna A. A la derecha aparece la gráfica generada por Excel tomando los datos del coseno hiperbólico.

ENTERO(número): Devuelve la parte entera del número, sin importar la magnitud de la parte decimal. Es decir, devuelve el número eliminando la parte decimal. Por ejemplo: ENTERO(3.1) da como resultado 3 ENTERO(3.9) da como resultado 3 ENTERO(2.95) da como resultado 2 ENTERO(5) da como resultado 5 ENTERO(-10) da como resultado –10. Elaborado por: Ing. Marcos González

EXP(número): El resultado de esta función es el número e elevado a la potencia indicada en el argumento. Como puede observar en el ejemplo de la figura No. 10, en cada una de las celdas de la columna B se calculó la función exp, tomando como argumento el contenido de cada celda correspondiente en la columna A. Como puede observar, a la derecha, Excel generó la gráfica correspondiente tomando como base los valores calculados por la función exp.

FACT(número): Esta función devuelve el factorial del valor especificado como argumento. Por ejemplo, FACT(3) da como resultado 6 FACT(5) da como resultado 120 GRADOS: Convierte el argumento expresado en radianes a grados. Por ejemplo, GRADOS(PI()) da como resultado 180 grados GRADOS(PI()/2) da como resultado 90 grados. LN(número): Calcula el logaritmo natural del valor dado como argumento. El logaritmo natural está definido únicamente para valores mayores que cero. Observe el ejemplo de la figura No. 11, en cada celda de la columna B se calcula el logaritmo natural para cada valor correspondiente de la celda de la izquierda, en la columna A. Observe a la derecha, la gráfica generada por Excel para el logaritmo natural.


LOG(número,base): Calcula el logaritmo del argumento número, la base está dada por el segundo argumento. Si no se le da valor al argumento base, lo calcula con la base 10. El logaritmo está definido para valores positivos. En la figura No. 11 puede apreciar que en cada celda de la columna C se ha calculado el logaritmo con base 5, siendo el argumento número su correspondiente valor de la celda de la columna A. Como puede observar, a la derecha aparece la gráfica generada por Excel para logaritmo con base 5. LOG10(número): Calcula el logaritmo con la base 10 del argumento número. El logaritmo está definido para valores positivos. En la figura No. 11 puede apreciar que en la columna D se ha calculado el logaritmo con base 10, tomando como argumento su correspondiente valor en cada celda de la columna A. Observe que al lado derecho aparece la gráfica para el logaritmo con base 10.

MDETERM(matriz): Devuelve el determinante de una matriz. El argumento matriz puede ser un rango de celdas o una constante. Esta función devuelve un único valor. Se genera el código de error #¡VALOR! si al menos una celda de la matriz contiene un valor no numérico o si la celda está vacía. La matriz debe tener el mismo número de filas y de columnas; Si no se cumple con esta restricción, la función devuelve el código de error #¡VALOR! Ejemplo: Observe la figura No. 12, en la celda D6 se ingresó la fórmula para calcular el determinante de la matriz comprendida en el rango A2:C4. También pudo ingresarse el argumento de la siguiente forma, como constante: =MDETERM({2;1;5\6;1;2\3;2;1}), dando como resultado 39. Elaborado por: Ing. Marcos González

MINVERSA(matriz): El resultado generado por esta función es la matriz inversa del argumento que es de tipo matriz. En el siguiente ejemplo, se explica cómo calcular la matriz inversa. Cuando una función devuelve una matriz, como es este caso, el procedimiento varía levemente, tal como se explicará en el siguiente ejemplo: Se va a calcular la inversa de la matriz presente en el rango A2:C4 que aparece en la figura No. 13. Debe seleccionar las celdas en las cuales va a quedar la matriz resultante, en este caso, el rango A8:C10. Haga clic en el ícono Pegar función de la barra de herramientas estándar. Se muestra el cuadro de diálogo “Pegar función”, como puede apreciar en la figura No. 14. En “Nombre de la función:” seleccione MINVERSA y presione el botón Aceptar


Se Muestra el cuadro de diálogo indicado en la figura No. 15, en el cual puede seleccionar la matriz que es el argumento de la función. Haga clic en el rectángulo que aparece al frente del nombre del argumento. Se cierra temporalmente este cuadro de diálogo para que seleccione el rango del argumento, tal como se indica en la figura No. 16. Después de seleccionado el rango, presione la tecla INTRO y de nuevo se encontrará en la figura No. 15, ya con el argumento establecido.

SELECCIONAR


Finalmente, presione las siguientes teclas, sostenidas: ++ y en el rango A8:C10 ha quedado el resultado de la función, tal como puede apreciar en la figura No. 17. Figura 17

MMULT(matriz1,matriz2): El resultado de la función es el producto matricial de matriz1 y matriz2. El número de columnas de matriz1 debe ser el mismo número de filas que matriz2. La matriz resultado tiene el mismo número de filas que matriz1 y el mismo número de columnas que matriz2. Recuerde que como se trata de una función que devuelve una matriz, el procedimiento es similar al explicado para la función MINVERSA. Ejemplo: En la figura No.18 Se calculó el producto matricial de dos matrices, matriz1 se encuentra en el rango A3:C6. Matriz2 tiene el rango A9:E11. El rango que se le dio a la matriz resultado fue Elaborado por: Ing. Marcos González

H3:L6. Matriz1 tiene 3 columnas y matriz2 tiene 3 filas, cumpliéndose la condición de que el número de columnas de matriz1 debe ser igual al número de filas de matriz2. La matriz resultado tiene 4 filas y 5 columnas, es decir, tiene el mismo número de filas de matriz1 y el mismo número de columnas que matriz2.

Figura 18

Si al menos una celda de matriz1 o matriz2 contiene un valor no numérico o está vacía, se genera el código de error #¡VALOR!, en todas las celdas de la matriz resultante. También se genera el código de error #¡VALOR! si la matriz1 no tiene el mismo número de columnas que el número de filas de matriz2. En la figura No. 19 puede observar, por ejemplo, que en el rango A6:E11 se incluyó la función para el cálculo del producto matricial de las matrices en los rangos A2:B3 y D2:E3. El producto matricial da como resultado una matriz de dos filas y dos columnas. Las celdas sobrantes devuelven el código de error #N/A. En este caso, debió definirse como matriz resultante la comprendida en el rango A6:B7.


Figura 19

MULTIPLO.INFERIOR(número, cifra_significativa): El múltiplo de un número es aquel que contiene a otro un número exacto de veces, es decir que el resultado de la división es un valor entero. Ejemplo, 15 es múltiplo de 3 y 5 porque 15/3=5 y 15/5=3. En cambio, 15 no es múltiplo de 4 porque 15/4 da como resultado 3.75 que es un valor con parte decimal. La función MULTIPLO.INFERIOR devuelve el múltiplo del argumento cifra_significativa que es menor y más próximo al argumento número. Para el caso de valores negativos, busca el múltiplo del argumento cifra_significativa que es mayor y más próximo al argumento número. Ejemplo: MULTIPLO.INFERIOR(25,3) da como resultado 24. Porque 24 es el múltiplo de 3, que es menor y más próximo al número 25. MULTIPLO.INFERIOR(18,5) da como resultado 15 porque 15 es el múltiplo de 5 más próximo a 18. NUMERO.INFERIOR(-18,-5) da como resultado –15 porque –15 es el múltiplo de –5 más próximo a –18. Observe que para el caso de valores negativos, busca el número mayor que es múltiplo, no menor para el caso de valores positivos. NUMERO.INFERIOR(-25,-3) da como resultado –24. NUMERO.INFERIOR(-30,2) da como resultado el error #NUM! Porque los dos argumentos tienen diferentes signos, el primero negativo y el segundo positivo. MULTIPLO.SUPERIOR(Número,Cifra_significativa): Devuelve el valor que es múltiplo de cifra_significativa, siendo mayor y más próximo al argumento Número. Para valores negativos busca el valor que es múltiplo de cifra_significativa, siendo menor y más próximo al argumento número. Ejemplo: Elaborado por: Ing. Marcos González

MULTIPLO.SUPERIOR(25,3) da como resultado 27. Porque 27 es el múltiplo de 3, siendo mayor que y al mismo tiempo más próximo al número 25. MULTIPLO.SUPERIOR(-25-3) da como resultado –27. Porque –27 es el múltiplo de –3, siendo menor y al mismo tiempo más próximo a –25. MULTIPLO.SUPERIOR(255,6) da como resultado 258. Porque 258 es el múltiplo de 6 que es mayor y al mismo tiempo más próximo a 255. MULTIPLO.SUPERIOR(-25, 4) da como resultado #NUM! Indicando que los dos argumentos son de diferente signo, por lo tanto no es posible hallar el valor. NUMERO.ROMANO(número,forma): Esta función toma el argumento número y lo convierte a su equivalente en romano. El argumento forma, es el tipo de número romano deseado, entre las opciones presentadas enseguida: Forma

Tipo de romano

omitido 0 1 2 3 4 VERDADERO

Clásico Clásico Más conciso Más conciso Más conciso Simplificado Clásico

FALSO

Simplificado

A continuación se muestran algunos ejemplos que indican la utilización de la función número.romano: NUMERO.ROMANO(399,0) da como resultado “CCCXCIX” NUMERO.ROMANO(399,1) da como resultado “CCCVCIV” NUMERO.ROMANO(399,2) da como resultado “CCCVCIV” NUMERO.ROMANO(256,FALSO) da como resultado “CCLVI”

PI(): Da como resultado el valor de la constante matemática pi. POTENCIA(número,potencia): Toma el argumento número y lo eleva a la potencia indicada por el argumento potencia. Ejemplo, POTENCIA(2, 5) da como resultado 32 POTENCIA( 10, 3) da como resultado 1000 POTENCIA(2,4) da como resultado 16 POTENCIA(3,3) da como resultado 27. Elaborado por: Ing. Marcos González

PRODUCTO(número1,número2,...): Multiplica todos los valores dados como argumentos y devuelve su resultado. Por ejemplo, en la figura No. 20 se ha incluido en la celda B13 la función para calcular el producto de los rangos A2:A11 y C2:C4. También hubiera podido incluir como argumentos, constantes o valores numéricos directamente ingresados, también se pueden incluir celdas individuales. Todos los argumentos van separados por comas. Fi ura 20

RADIANES(ángulo): Esta función toma el argumento ángulo, que está expresado en grados y devuelve su equivalente expresado en radianes. Por ejemplo: RADIANES(90) da como resultado 1.571, es decir PI/2 RADIANES(180) da como resultado 3.142, es decir PI RAIZ(número): Devuelve la raíz cuadrada del argumento número. Por ejemplo, RAIZ(25) da como resultado 5 RAIZ(81) da como resultado 9 RAIZ(12) da como resultado 3.46. REDONDEA.IMPAR(número): Toma el argumento número y lo aproxima al siguiente entero impar, que es mayor que él. Cuando el argumento es un valor negativo, lo aproxima al siguiente entero impar que es menor que él. Ejemplo: REDONDEA.IMPAR(1.3) da como resultado 3. REDONDEA.IMPAR(-1.3) da como resultado –3 REDONDEA.IMPAR(-4.6) da como resultado -5 REDONDEA.IMPAR(4.6) da como resultado 5 REDONDEA.IMPAR(7) da como resultado 7. Se puede apreciar que aplicar esta función a un número entero impar da como resultado el mismo número. REDONDEA.PAR(número): Toma el argumento número y lo aproxima al siguiente entero par que es mayor que él. En el caso de que el argumento sea un valor negativo, lo aproxima al siguiente entero par que es menor que él. Ejemplos: Elaborado por: Ing. Marcos González

REDONDEA.PAR(4.3) da como resultado 6 REDONDEA.PAR(-4.3) da como resultado -6 REDONDEA.PAR(7) da como resultado 8. REDONDEA.PAR(8) da como resultado 8. Al aplicar esta función a un número entero par da como resultado el mismo número. REDONDEAR(número, núm_decimales): Devuelve el argumento número, con la cantidad de decimales especificados en el argumento núm_decimales, realizando las aproximaciones de redondeo respectivas. Por ejemplo, REDONDEAR(1.4545, 2) da como resultado 1.45 REDONDEAR(2.94388,3) da como resultado 2.944 REDONDEAR(125.6898) da como resultado 125.69 REDONDEAR(35.458,2) da como resultado 35.46 REDONDEAR(-18.97,1) da como resultado –19.0 REDONDEAR(18.97,1) da como resultado 19. REDONDEAR.MAS(número,número_decimales): Tal como con la función REDONDEAR, devuelve el argumento número con la cantidad de decimales especificados en el argumento número_decinales En este caso, las aproximaciones de los decimales siempre los hace a los valores superiores. En el caso en que el argumento número es un valor negativo, las aproximaciones siempre van a ser al valor menor. Por ejemplo, REDONDEAR.MAS(3.2222,2) da como resultado 3.23 REDONDEAR.MAS(4.52,1) da como resultado 4.6 REDONDEAR.MAS(-3.25,1) da como resultado –3.3 REDONDEAR.MAS(3.25,1) da como resultado 3.3 REDONDEAR.MENOS(número,núm_decimales): Devuelve el argumento número con la cantidad de decimales especificada en el argumento núm_decimales. Las aproximaciones de los decimales siempre las hace al valor inferior más próximo. Cuando el argumento número es negativo, las aproximaciones las hace al valor mayor. Ejemplo: REDONDEAR.MENOS(3.99992,2) da como resultado 3.99 REDONDEAR.MENOS(4.52,1) da como resultado 4.5 REDONDEAR.MENOS(-3.28,1) da como resultado –3.2 REDONDEAR.MENOS(3.28,1) da como resultado 3.2


RESIDUO(número,núm_divisor): La función divide el argumento número entre núm_divisor y devuelve el residuo o resto de esta división. Si la división es exacta, el residuo da como resultado cero. Ejemplo, RESIDUO(20,5) da como resultado 0 RESIDUO(9,4) da como resultado 1 RESIDUO(12,8) da como resultado 4. SENO(número): Esta función devuelve el seno del ángulo especificado en el argumento número. El ángulo va expresado en radianes. Por ejemplo, en la figura No. 21 se puede observar que en cada una de las celdas de la columna B, se calcula el seno para el valor correspondiente de cada una de las celdas de la columna A. A la derecha se ha incluido la gráfica de la función seno.

Figura 21

SENOH(número): Devuelve el seno hiperbólico del valor dado como argumento. Se puede dar como argumento cualquier número real. En la figura No. 22 puede apreciar que en cada una de las celdas de la columna B se calculó el seno hiperbólico, tomando como argumento el valor correspondiente de cada una de las celdas en la columna A. Al lado derecho aparece la gráfica generada por Excel, tomando como base los valores de seno hiperbólico.


Figura 22

SIGNO(número): Esta función devuelve –1 si el argumento número es negativo; devuelve cero si el argumento número es cero; devuelve 1 si el argumento número es positivo. Ejemplo: SIGNO(12) da como resultado 1 SIGNO(-10) da como resultado –1 SIGNO(0) da como resultado 0 SUBTOTALES(núm_función;ref1,..): Devuelve el subtotal del rango o rangos dados como argumentos. El argumento núm_función es un número entre 1 y 11 que indica el tipo de cálculo que debe realizarse con los valores dados como argumento, por ejemplo, si es el promedio, producto, etc. Los argumentos ref1, ref2, etc., son referencia a una celda o un rango de celdas. Essta función no acepta como argumento una constante. Todas las opciones de núm_función puede verlas en la tabla que se muestra a continuación: Núm_función 1 2 3 4 5 6 7

Función PROMEDIO CONTAR CONTARA MAX MIN PRODUCTO DESVEST

8 9

DESVESTP SUMA Elaborado por: Ing. Marcos González

10 11

VAR VARP

Por ejemplo, en la figura No. 23 puede apreciar que en la celda B9 se calculó el promedio de los valores comprendidos en el rango B1:B7 utilizando la función SUBTOTALES. En la celda B10 se cuenta la cantidad de celdas que contiene números en el rango B1:B7, dando como resultado 7. En la celda B11 se determinó el valor mayor presente en el rango B1:B7, dando como resultado 7. En la celda B12 la función devolvió el menor valor encontrado en el rango B1:B7, siendo su resultado 2.

Figura 23

TAN(número): Esta función calcula la tangente del ángulo dado en el argumento. El ángulo viene expresado en radianes. En la figura No. 29 puede apreciar que en cada celda de la columna B se calcula la tangente para cada celda correspondiente de la columna A. A la derecha puede apreciar la gráfica generada por Excel. Para que la gráfica tenga sentido, debe recordarse que esta es una función discontinua, por lo tanto debe graficarse en un intervalo. En el ejemplo de la figura No. 29 se graficó para valores mayores que PI/2 y para valores menores que 3PI/2.


Figura 29

TANH(número): Esta función devuelve la tangente hiperbólica del valor dado como argumento. El argumento puede ser cualquier valor real. Observe la figura No. 30, en cada una de las celdas de la columna B se calcula la tangente hiperbólica, tomando como argumento el valor correspondiente de cada una de las celdas de la columna A. Como puede darse cuenta, al lado derecho se muestra la grafica generada por Excel para la tangente hiperbólica. Figura 30

TRUNCAR(número,núm_decimales): Esta función toma el argumento número, conservando la cantidad de dígitos decimales especificados en núm_decimales. Los demás dígitos decimales más a la derecha los eliminan. En este caso, no realiza redondeo, sencillamente quita los decimales sobrantes. Ejemplo: TRUNCAR(4.6545789, 3) da como resultado 4.654. Elaborado por: Ing. Marcos González

TRUNCAR(35.999,1) da como resultado 35.9 TRUNCAR(18.25,1) da como resultado 18.2

3.3 FUNCIONES FINANCIERAS:

Devuelve la tasa efectiva del interés anual si conocemos la tasa de interés anual nominal y el número de períodos de interés compuesto por año. De aplicación cuando los períodos de pago son exactos. Sintaxis INT. EFECTIVO (int_nominal;núm_per_año) Si alguno de los argumentos es menor o igual a cero o si el argumento núm_per_año es menor a uno, la función devuelve el valor de error #¡NUM! La respuesta obtenida viene enunciada en términos decimales y debe expresarse en formato de porcentaje. Nunca divida ni multiplique por cien el resultado de estas funciones. Esta función proporciona la tasa efectiva de interés del pago de intereses vencidos. Para intereses anticipados debe calcularse la tasa efectiva aplicando la fórmula. El argumento núm_per_año trunca a entero cuando los períodos son irregulares, hay que tener especial sólo produce confiables cuando lamensual cantidad de períodos cuidado de pago con en elestaañofunción, (núm_per_año) tiene resultados valores exactos; por ejemplo: (12), trimestral (4), semestral (2) o anual (1). El resultado proporcionado por esta función lo obtenemos también con la siguiente fórmula: m

43 i=1 

j 1 m

Ejemplo 1: Cuando los períodos de pago son exactos y el resultado es confiable: FECHA INICIAL : 15-03-2004 FECHA FINAL : 15-06-2004 TASA NOMINAL : 68% anual, compuesto trimestralmente Solución: n = (15/03/2004 - 15/06/2004) = 90/30 = 3, m = (12/3) = 4


Aplicando ambos métodos: 4

 43

i = 1+

0.68 -1= 0.8739 4

Sintaxis INT.EFECTIVO( int_nominal;núm_per_año) int_nominal 0.68

núm_per_año 4

INT. EFECTIVO 0.8739

Ejemplo 2: Cuando los períodos de pago son inexactos y por lo tanto el resultado es irreal. FECHA INICIAL : 15-03-2004 FECHA FINAL : 15-06-2004 TASA NOMINAL : 68% anual, compuesto cada 2.20 meses Solución: n = (15/03/2004 - 21/05/2004) = 66/30 = 2.2, m = (12/2.2) = 5.2174 Aplicando ambos métodos:  43 i= 1+

0.68 5.2174

5.2174

-1= 0.8739

Sintaxis INT.EFECTIVO( int_nominal;núm_per_año) int_nominal

núm_per_año

0.68

5.2174

INT. EFECTIVO 0.8919

Observando ambos resultados, constatamos que son diferentes. En estos casos es recomendable el uso de las fórmulas, sus resultados son más reales.

Devuelve la tasa de interés nominal anual si conocemos la tasa efectiva y el número de períodos de interés compuesto por año. Sintaxis TASA.NOMINAL(tasa_efectiva; núm_per) El argumento núm_per se trunca a entero, hay que tener especial cuidado con esta función, sólo produce resultados confiables cuando la cantidad de períodos de pago en el año (núm_per) tiene valores exactos; ejemplo: es mensual trimestral (2) onúm_per anual (1).es menor a uno, Si alguno de los por argumentos menor(12), o igual a cero o(4), si elsemestral argumento la función devuelve el valor de error #¡NUM! Elaborado por: Ing. Marcos González

La respuesta obtenida viene enunciada en términos decimales y debe expresarse en formato de porcentaje. Nunca divida ni multiplique por cien el resultado de estas funciones. Esta función proporciona la tasa nominal del pago de intereses vencidos. Para el interés anticipado debe calcularse la tasa nominal aplicando la fórmula (B): Bi

ia a= 1+ iv

Presenta las funciones que sirven para resolver problemas en los cuales entre el valor inicial y el valor final de un negocio existen pagos de cuotas o valores recibidos. En todas las funciones de series uniformes suponemos que los valores recibidos o pagados durante el tiempo del negocio son reinvertidos razón por la cual debe restase del plazo total, en las mismas condiciones existentes para la inversión srcinal. Un problema es de series uniformes cuando reúne las siguientes condiciones en su totalidad: a) El monto de los pagos efectuados dentro del tiempo de la inversión es constante b) La periodicidad de los pagos efectuados dentro del tiempo de la inversión es constante c) La tasa de interés de liquidación de pagos dentro del tiempo de la inversión es constante. Los argumentos utilizados por las funciones financieras de series uniformes son los siguientes: VA: Es el valor actual de la serie de pagos futuros iguales. Si este argumento es omitido, significa que es 0. Pago (C): Es el pago efectuado periódicamente y no cambia durante la vida de la anualidad. El Pago incluye el capital y el interés pero no incluye ningún otro cargo o impuesto. Este argumento debe tener signo contrario al de VA, para conservar las condiciones del flujo de caja: expresamos los ingresos con signo positivo y los egresos con signo negativo. Nper: Es la cantidad total de períodos en una anualidad; es decir, el plazo total del negocio. Tasa (i): Es la tasa de interés por período. Tener en cuenta que no es la tasa anual, si no la tasa nominal del período de pago expresada en términos decimales. Es importante mantener la uniformidad en el uso de las unidades con las que especificamos Tasa y Nper. VF: Es el valor futuro o el saldo en efectivo que desea lograrse después de efectuar el último pago. Si el argumento VF es omitido, asumimos que el valor es 0. Tipo: Es el número 0 ó 1 e indica la forma de pago de la cuota entre vencida y anticipada. Defina tipo Es cero (0) o omitido, cuando el pago de la cuota es vencida. Ponemos 1, cuando el pago de la cuota es anticipada. Período Especifica el número ordinal de la cuota y debe encontrarse en el intervalo comprendido entre 1 y Nper. Elaborado por: Ing. Marcos González

Per_inicial y Per_final Especifica el número ordinal de la primera y la última cuota del período en el cual analizaremos las cuotas pagadas. Estimar Es la tasa de interés estimada para que Excel empiece las iteraciones en el cálculo de la tasa de interés de series uniformes. Si el argumento Estimar es omitido, suponemos que es 10%.

Permite calcular VF a partir de C o de VA. También sirve para calcular el valor de VF indicando si es cuota anticipada (tipo=1) o vencida (tipo=0). Si lo que queremos calcular es VF a partir de VA omitimos el valor de C; si la cuota es vencida, omitimos el valor tipo. Devuelve el valor futuro de la inversión, equivalente a los pagos periódicos uniformes a una tasa de interés constante. Sintaxis: VF(tasa;nper;pago;va;tipo) El resultado proporcionado por esta función lo obtenemos también con la siguiente fórmula: n

1+ i  - 1 F =C i

27V

Por ejemplo: Si ahorramos UM 350 mensuales durante 3 años en un banco que paga el 18% nominal anual y deseamos saber cuánto dinero tendremos ahorrado al final de los 3 años: Solución: C = 350; n = (3*12) = 36; i = 0.015 (0.18/12); VF = ? Aplicando ambos métodos, tenemos: 27

VF = 350

36 1+0.015  -1

0.015

Sintaxis VF( tasa;nper;pago;va;tipo) TA S A NPE R P A G O 0.015

36

-350

= UM16,546.59

VA

TI P O

VF 16,546.59

Ingresamos los datos en los argumentos de función en el orden indicado en el cuadro de la sintaxis:


En la solución de los ejemplos y ejercicios en el presente libro, utilizaremos el FORMATO SIMPLIFICADO indicado en el cuadro de la Sintaxis, cuando operemos con la herramienta Funciones Financieras de Excel. Esta metodología de ingresar los datos es aplicable a todas las funciones de función.de Excel, utilizadas en la obra, desde luego, cada con su propia persiana de argumentos Hay tres aspectos a considerar en este ejemplo: El interés incluido en el argumento Tasa debe estar en la misma unidad de tiempo utilizada para el argumento Nper. En este caso, como son cuotas mensuales, la tasa de interés debe ser mensual, es necesario dividir por doce la tasa anual nominal. VA puede omitirse como apreciamos en el asistente para funciones y en la barra de fórmulas automáticamente deja el espacio en la función, asumiéndolo como cero. Si deseamos que las cifras en la hoja de cálculo sean positivas, introducimos el argumento Pago con signo negativo, como apreciamos en el asistente para funciones (-350, en C2). Valor Actual (VA) Permite calcular VA a partir de C o de VF. También sirve para calcular el valor de VF indicando si es cuota anticipada (tipo=1) o vencida (tipo=0). Para calcular VA a partir de VF, omitir el valor de C; y cuando operemos con cuotas vencidas, omitir el valor tipo. Devuelve el valor actual de la Elaborado por: Ing. Marcos González

inversión. El valor actual es la suma de una serie de pagos a futuro. Por ejemplo, cuando pedimos dinero prestado, la cantidad del préstamo es el valor actual para el prestamista. La versión XP de Excel, recomienda el empleo de fx insertar función de la barra de fórmulas. Al oprimir fx aparece el menú de funciones y escogemos la función buscada. Esta función conserva las mismas observaciones efectuadas para VF. Sintaxis: VA(tasa;nper;pago;vf;tipo) El resultado proporcionado por esta función lo obtenemos también con la siguiente fórmula: n

V 24

1+ i  - 1 A=C i 1 + i 



n



Por ejemplo: Si ahorramos UM 350 mensuales durante 3 años en un banco que paga el 18% nominal anual y deseamos saber cuánto representan estas mensualidades al día de hoy.

Solución: C = 350; n = (3*12) = 36; i = 0.015 (0.18/12); VA = ? Aplicando ambos métodos, tenemos: [24] VA 3 50

1.01536 1 0.015 1.015 36

Sintaxis VA(tasa;nper;pago;vf;tipo) Ta s a Np e r P a go 0.015

36

VF

-350

 UM 9,681.24

Ti p o

VA 9,681.24

Calcula el pago de un préstamo basándose en pagos constantes y con la tasa de interés constante. Sintaxis: PAGO(tasa;nper;va;vf;tipo)

Sugerencia: Para por encontrar la cantidad total pagada el valor devuelto PAGO por el argumento nper. durante el período del préstamo, multiplique El resultado proporcionado por esta función lo obtenemos también con la siguiente fórmula: Elaborado por: Ing. Marcos González



n



i 1 + i  = VA 1+ i  - 1

25C

n

Por ejemplo: Obtenemos un crédito de UM 10,000 para su pago en 24 cuotas trimestrales iguales, a la tasa nominal anual de 36% por trimestre vencido:

Solución: VA = 10,000; n = 24; i = (0.36/12) = 0.03; C = ? Aplicando ambos métodos, tenemos:

25

C1 = 0 000 ,

24



24

.  11+0 03

Sintaxis PAGO( tasa;nper;va;vf;tipo) TA S A NP E R VA 0.03

24



.  0.03 1 +0 03

VF

 UM 590.47

TI P O

-10,000

PA G O 590.47

En algunos casos puede darse la necesidad de requerir tanto el VA como el VF; como en el caso del leasing, en el cual, además del valor inicial de un equipo tenemos cuotas mensuales iguales y al final del pago existe la opción de compra para que el usuario adquiera el bien. Por ejemplo: En un leasing de UM 50,000 a 24 meses con la tasa de interés del 2.87% mensual y la opción de compra del 12%, la función PAGO para calcular la cuota mensual a pagar operaría de la siguiente forma: Solución: VA = 50,000; i = 0.0287; n = 24; VF = 12%; C = ? Sintaxis PAGO(tasa;nper;va;vf;tipo) TA S A NPE R VA 0.0287

24

-50,000

VF 1 2%

TI P O

PA G O 3,088.32

Calcula la tasa del período Elaborado por: Ing. Marcos González

Devuelve la tasa de interés por período de la anualidad. La TASA es calculada por iteración y puede tener cero o más soluciones. Si los resultados sucesivos de TASA no convergen dentro de 0,0000001 después de 20 iteraciones, TASA devuelve el valor de error #¡NUM!. Con esta función es posible calcular la tasa de interés, combinando no sólo VA y VF, sino también VA y C, C y VF y VA, C y VF. Por ser la tasa del período tiene la característica de ser simultáneamente nominal y efectiva, para convertir ésta tasa en tasa anual debe tenerse cuidado con la fórmula utilizada, dependiendo de qué tasa queremos calcular: la tasa nominal o la tasa efectiva anual (TEA). Sintaxis TASA(nper;pago;va;vf;tipo;estimar) Por ejmeplo: VA = 5,000; n = 5; C = 1,250; i =? Sintaxis TASA(nper;pago;va;vf;tipo;estimar) Np er P ag o VA VF 5

-1,250.00

5,000

Ti p o

Ta s a 0.07931

Función utilizada para calcular la tasa periódica de las anualidades. No existen fórmulas para obtener la tasa de las anualidades.

Devuelve la cantidad de períodos que debe tener la inversión para que sea equivalente a la serie de pagos periódicos iguales. Sintaxis NPER(tasa, pago, va, vf, tipo) La unidad de tiempo consignada en la función Nper debe ser la misma que la utilizada en la tasa de interés. El resultado proporcionado por esta función lo obtenemos también con las siguientes fórmulas, según los casos:


  VA   VF l og 1-   i  , C   VA , 26  =  n 23 n= log 1+ i   1  log   1  i       VA    i   1 log 1-     C  28 = n  1  log    1  i   log

Por ejemplo: i = 0.06; C = 14,000; VA = 93,345.50; n =? Sintaxis NPER( tasa; pago; va; vf; tipo) Ta s a P a g o VA 0.06

14000

-93,345.50

VF

Ti p o

n

8.7682

La evaluación financiera de proyectos consiste en la aplicación de algunos indicadores de conveniencia económica al flujo de caja estimado de un negocio. En esta parte presentaremos solamente las funciones financieras del Excel utilizadas en el presente libro como indicadores de conveniencia económica (VAN y TIR). En Excel existen otras funciones financieras para este propósito. En un proyecto real el flujo de efectivo resultante no obedece a las series conocidas (anualidades, gradientes, etc.), puesto que depende de cantidad de variables, por lo tanto no existe una fórmula para calcular el valor presente neto o la tasa de retorno (las fórmulas del VAN y la TIR insertos en el presente libro son solamente ilustrativas). Es necesario trabajar cada componente del flujo como elemento independiente. Es aquí donde el Excel presenta un gran aporte para la evaluación financiera de proyectos. Marcando la opción aceptar, obtenemos el VA del flujo. Para el cálculo del VAN sumamos la celda donde está la inversión con signo negativo. Los argumentos que utilizan las funciones de evaluación de proyectos VAN o VNA y TIR, son los siguientes: Tasa : Es la tasa de descuento utilizada para calcular el valor presente. Debe expresarse en el mismo período que empleamos para la serie de datos. Valor1, valor2: Son los rangos que contienen los valores (ingresos y egresos) a los cuales calcularemos el valor presente. La función acepta hasta 29 rangos. Valores: Rango que contiene los valores (flujo de caja) a los cuales deseamos calcular la tasa interna de retorno. El argumento valores debe contener al menos un valor positivo y uno negativo Elaborado por: Ing. Marcos González

para calcular la tasa interna de retorno. Estos flujos de caja no tienen por que ser constantes, como es el caso en una anualidad; sin embargo, los flujos de caja deben ocurrir en intervalos regulares. Estimar: Es el número estimado por el usuario que considera aproximará al resultado de TIR. VNA o VAN Calcula el valor actual neto de la inversión a partir de la tasa de descuento y pagos futuros (valores negativos) e ingresos (valores positivos). Sintaxis VNA(tasa;valor1;valor2; ...) Los valores incluidos en el flujo de caja no tienen que ser constantes. Esta es la principal diferencia frente a la función VA, conserva la condición de que tanto la tasa de interés como la periodicidad son constantes; es decir, todo el flujo de caja descuenta a la misma tasa y los valores incluidos en él ocurren a intervalos iguales. Dentro del rango del flujo de caja excluimos el valor presente ubicado en el período cero (0), dicho valor está en UM de hoy. La inversión inicial de la celda con período 0 no ingresa en el argumento valores, posteriormente restamos del resultado que arroje la función. La fórmula relacionada con ésta función es: [41] VAN=

FC1 FC 2 FC 3 FC 4FC + + + + -I 4n (1+i) (1+i) 2 3(1+i) (1+i) (1+i)

n 0

Por ejemplo: Tenemos los siguientes flujos netos de un proyecto FLUJO DE CAJA AÑOS Flujos Net os

0 1 2 3 4 5 -50,000 16,000 14,000 17,000 15,000 18,000

Aplicando la función VNA y con un costo de oportunidad del capital de 15% calculamos el VAN del flujo precedente: Sintaxis VNA (tasa ;valor1 ;valor2; ...) A Ñ O T as a 0 1 2 3 4 5 VAN FLUJO 0.15 -50,000 16,000 14,000 17,000 15,000 18,000 3,202.31

El valor actual neto es un indicador sobre la conveniencia económica de la inversión, involucra la subjetividad del inversionista, que debe seleccionar la tasa de interés para descontar el flujo de Elaborado por: Ing. Marcos González

caja. Al calcular con dos tasas diferentes obtenemos dos resultados, para evaluar estos casos debe tenerse en cuenta que la respuesta esta expresada en UM del período cero y su significado puede interpretarse de la siguiente manera: VNA > 0, un resultado positivo indica que el negocio estudiado arroja rentabilidad superior a la exigida por el inversionista, deducida la inversión, luego es conveniente llevar a cabo el negocio. VNA = 0, en caso de presentarse, un resultado igual a cero indica que el negocio arroja rentabilidad igual a la exigida por el inversionista, la ejecución del proyecto es opcional. VNA < 0, valor presente neto negativo no significa que el negocio estudiado arroje pérdidas, únicamente la rentabilidad es inferior a la exigida por el inversionista y para él, particularmente, no es conveniente el negocio. De lo anterior concluimos cuando anunciemos el VNA de un proyecto debe aclararse cuál fue la tasa de descuento utilizada para calcularlo, es decir, cuál fue el valor ingresado en el argumento Tasa. TIR Devuelve la tasa interna de retorno (tasa de rentabilidad) de los flujos de caja representados por los números del argumento valores. Estos flujos de caja no son constantes, como en las anualidades. Sin embargo, los flujos de caja deben ocurrir en intervalos regulares, como meses o años. La tasa interna de retorno equivale a la tasa de interés producida por un proyecto de inversión con pagos (valores negativos) e ingresos (valores positivos) que ocurren en períodos regulares. Sintaxis TIR(valores;estimar) Para el cálculo de la función TIR incluimos en el rango de valores todo el flujo de caja y es necesario que existan valores positivos y negativos. El argumento Estimar es opcional. En caso de omitirse, el Excel asume la tasa inicial del 10%. La fórmula relacionada con ésta función es: [ TIR] -I+

0

FC FC3 FC 4FC FC n + 1 + 2 2+3 + n =0 4 (1+i) (1+i) (1+i) (1+i) (1+i)

Por ejemplo: Tenemos el siguiente flujo de caja de un proyecto: 0

1234

-60,000

8,000

56 15,000

15,000

15,000

20,000

28,000


Aplicando la función calculamos la TIR del proyecto: Sintaxis TIR(valores ;estimar) 0

1

-60,000

2

3

4

5

6

TI R

8,000 15,000 15,000 15,000 20,000 28,000 0.1436

La TIR sólo involucra las condiciones particulares de un proyecto y no está afecta por la subjetividad del inversionista. Sin embargo, dificultades de orden matemático llevan a desconfiar de los resultados que arroja. Para ilustrar el caso presentamos el siguiente flujo. 0

1

2

-42,000

120,000

-80,000

Aplicando la función calculamos la TIR del proyecto: Con el argumento estimar = 6% Sintaxis TIR(valores ;estimar) 0 -42,000

1

2

120,000

TI R -80,000

0.0597

Con el argumento estimar = 35% Sintaxis ;estimar) TIR(valores 0 1 -42,000

120,000

2

TI R -80,000

0.7974

Como apreciamos, ante el mismo flujo de caja la función TIR arroja dos resultados diferentes, dependiendo del valor utilizado en el argumento Estimar. Es recomendable tener cuidado al utilizar esta función, puede llevarnos a conclusiones erróneas. Por otra parte, la TIR no toma en cuenta los costos de financiación ni la reinversión de utilidades generadas al realizar la inversión. Es decir sólo está mostrando la rentabilidad por mantener en un negocio el saldo no recuperado de la inversión inicial. Para resolver esta dificultad utilizamos otra forma de calcular la TIR llamada la Tasa Verdadera de Rentabilidad (TVR) o la Tasa Interna de Rendimiento Modificada (TIRM). La TIRM: Devuelve la tasa interna de retorno modificada para una serie de flujos de caja periódicos. TIRM toma en cuenta el costo de la inversión y el interés obtenido por la reinversión del dinero. Elaborado por: Ing. Marcos González

Sintaxis TIRM(valores;tasa_financiamiento;tasa_reinversión) Valores es una matriz o una referencia a celdas que contienen números. Estos números representan el flujo de caja, expresado en una serie de pagos (valores negativos) e ingresos (valores positivos) efectuados en períodos regulares. El argumento valores debe contener por lo menos un valor positivo y uno negativo para poder calcular la tasa interna de retorno modificada. De lo contrario, TIRM devuelve el valor de error #¡DIV/0! Si el argumento matricial o de referencia contiene texto, valores lógicos o celdas vacías, estos valores se pasan por alto; sin embargo, se incluirán las celdas con el valor cero. Tasa_financiamiento es la tasa de interés que se paga por el dinero utilizado en los flujos de caja. Tasa_reinversión es la tasa de interés obtenida por los flujos de caja a medida que se reinvierten. Esta función en el presente libro es referencial, todos los casos son resueltpos aplicando la función TIR.

La tabla de amortización indica cómo el pago de una deuda está dividida entre interés y abono o amortización de la deuda. Con la tabla de amortización podemos también establecer el saldo pendiente al final de cada período. Igualmente podemos operar con la tabla de capitalización; la diferencia radica en que en lugar de amortizar (disminuir la deuda), los ahorros y los intereses que ellos producen capitalizan luego, es posible calcular también el saldo acumulado del capital ahorrado con sus intereses. Con la ayuda de Excel, las tablas de amortización pueden elaborarse con variados esquemas de pago, el límite lo impone la imaginación y capacidad del usuario. Algunos ejemplos son las cuotas escalonadas del pago de deudas. La clave para manipular estos esquemas es hacer depender todas las cuotas futuras de la primera cuota y construir el «modelo» en función de esa primera cuota; hecho esto, hay que encontrar el valor de la primera cuota que haga cero el saldo final. Esto es posible lograrlo con la opción de Excel que está en Herramientas del menú, llamada Buscar objetivo. Ajustar el valor de una celda para obtener un resultado específico para otra celda. En el menú Herramientas, haga clic en Buscar objetivo. En el cuadro Definir celda, escriba la referencia de la celda que contenga la fórmula (fórmula: secuencia de valores, referencias de celda, nombres, funciones u operadores de la celda que Elaborado por: Ing. Marcos González

producen juntos un valor nuevo. Una fórmula comienza siempre con el signo (=).) que desee resolver. En el cuadro Con el valor, introduzca el resultado que desee. En el cuadro Para cambiar la celda introduzca la referencia de la celda que contenga el valor que desee ajustar. A esta celda debe hacer referencia la fórmula en la celda especificada del cuadro Definir celda. Haga clic en Aceptar. Lo más conveniente al construir la tabla de amortización es su estructura básica, así: 1º

Caso cuando fijamos la cuota o pago

SALDO INICIAL

INT ERÉS

AMO RTIZACIÓN PA GO

Saldo fin al del período anterior

Saldo inicial Pago menos por tasa de interés interés

Definida a voluntad

SALD O FINA L Saldo inicial menos amortización

Por ejemplo: Un préstamo de UM 10,000 al 4.5% mensual, cuyos 6 pagos, se duplican cada dos meses. Solución: VA=10,000;

i = 0.045;

n = 6;

C1...6 = ?

La primera cuota puede ser cualquier valor; lo importante es que las demás cuotas (de la segunda en adelante) dependan de la primera; de modo que cuando cambie la primera, las demás cuotas y el resto de la tabla también cambien. Habrá que cambiar el valor de la primera cuota hasta cuando el saldo final sea cero. Es posible hacer esto a mano, pero el computador lo hace más rápido con la opción Buscar objetivo ya mencionada. Definimos la celda donde está el saldo final del último período con el valor cero y pedimos que cambie la celda donde está la primera cuota. Operando con Buscar Objetivo de Excel. Elaboramos la tabla de amortización, como ilustramos en el extracto de la hoja de Excel. En la columna E4 (Pago), ingresamos 10 un valor arbitrario, de la siguiente forma: Elaborado por: Ing. Marcos González

Celda E4 Celda E5 Celda E6 Celda E7 Celda E8 Celda E9

10 [Ingresamos a la celda sin poner el signo (=)] =E4 =E5*2 (de acuerdo a la condición del problema). =E6 =E7*2 =E8

Cuando la tabla es de muchos períodos (filas) y no exista la condición doble o UM X más cada 2, 3, etc. cuotas; la forma más rápida de operar, es ingresar a la primera celda (PAGO) cualquier número, luego ingresamos a la segunda celda (PAGO) el signo (=) y hacemos clic con el mouse en la primera celda PAGO. Finalmente, colocamos el puntero en la 2º celda PAGO y del ángulo inferior arrastramos el puntero en forma de cruz hasta la celda PAGO final de la tabla.

Aplicando la opción buscar objetivo obtenemos el valor de cada cuota: A

B

ME S 1

0

2 3 4 5 6 7 8

1 2 3 4 5 6

C

D

S A LD O IN TER ÉS AM OR TZ INICIAL

10,000.00 9,586.72 9,154.85 7,840.26 6,466.52 3,304.41

INTERES PAGO AMORTIZACION

E

PA GO

F

SA LD O FINAL

10,000.00 450.00 413.28 863.28 9,586.72 431.40 431.87 863.28 9,154.85 411.97 1,314.59 1,726.55 7,840.26 352.81 1,373.74 1,726.55 6,466.52 290.99 3,162.11 3,453.11 3,304.41 148.70 3,304.41 3,453.11 0.00

= SALDO INICIAL x 0.045 = BUSCAR OBJETIVO = PAGO - INTERES (=E3 - C3) ... (=E8 - C8)


2º Caso cuando fijamos el abono o amortización Caso que confirma que la suma de las amortizaciones es igual a la deuda. SALDO INICIAL

INT ERÉ S

AMOR TIZACIÓN PA GO

Saldo fi nal

Saldo ini cial Defini da a

Amortización Saldo inicial

del período anterior

por tasa de interés

más interés

voluntad

SALD O FINA L

menos amortización

Considerando el ejemplo anterior con amortización constante: ELABORAMOS LA TABLA DE AMORTIZACIÓN A

ME S 1

0

B

2 3 4 5 6 7 8

1 2 3 4 5 6

INTERES

C

D

S A LD O IN TER ÉS AM OR TZ INICIAL 10,000.00 8,333.33 6,666.67 5,000.00 3,333.33 1,666.67

E

F

PA GO

10,000.00 450.00 1,666.67 2,116.67 375.00 1,666.67 2,041.67 300.00 1,666.67 1,966.67 225.00 1,666.67 1,891.67 150.00 1,666.67 1,816.67 75.00 1,666.67 1,741.67

SA LD O FINAL

8,333.33 6,666.67 5,000.00 3,333.33 1,666.67 0.00

= SALDO INICIAL x 0.045 Elaborado por: Ing. Marcos González

AMORTIZACION PAGO

= 10,000/6 = 1,666.67 = AMORTIZACIÓN + INTERÉS (=C3 + D3) ... (=C8 + D8)

El ejemplo anterior con pagos en cuotas uniformes: Solución: VA = 10,000; i = 0.045; n = 6; C = ? El pago C también es calculado aplicando la fórmula [25], la función financiera PAGO o Buscar Objetivo de Excel: [25] C  10,000

0.045(1  0.045) 6 (1  0.045) 6  1

Sintaxis PAGO( tasa;nper;va;vf;tipo) Ta s a Np er VA 0.045

6

 UM 1,938.78

VF

Ti p o

-10,000

PA G O 1,938.78

Elaboramos la TABLA DE AMORTIZACIÓN, como ilustramos en el extracto de la hoja de Excel. Aplicamos el proceso ya conocido y obtenemos la siguiente tabla: A

ME S 1

0

B

2 3 4 5 6 7 8

1 2 3 4 5 6

C

D

S A LD O IN TER ÉS AM OR TZ INICIAL 10,000.00 8,511.22 6,955.44 5,329.65 3,630.70 1,855.30

E

F

PA GO

10,000.00 450.00 1,488.78 1,938.78 383.00 1,555.78 1,938.78 312.99 1,625.79 1,938.78 239.83 1,698.95 1,938.78 163.38 1,775.40 1,938.78 83.49 1,855.30 1,938.78

SA LD O FINAL

8,511.22 6,955.44 5,329.65 3,630.70 1,855.30 0.00

Ejemplo de cuota o pagos escalonados es la liquidación de un préstamo de UM 5,000 a la tasa del 3.8% mensual con cuotas que crecen UM 30 cada mes. El primer esquema sería: Solución: VA = 5,000; i = 0.038; n = 5; C =? Elaborado por: Ing. Marcos González

En la celda E3 (Pago), ingresamos un valor arbitrario, de la siguiente forma: Celda E3 Celda E4 Celda E5

10 =E3+30 =E4+30

Celda E6 =E5+30 Celda E7 =E6+30 Celda E8 =E7+30

En buscar Objetivo: Definir la celda : Con el mouse hacemos clic en la celda F8 con el valor :0 para cambiar la celda : Con el mouse hacemos clic en la celda E3 Aplicando este procedimiento obtenemos la siguiente tabla: A

ME S 1

0

B

C

2 3 4 5 6

1 2 3 4 5

7 8

6

D

S A LD O IN TER ÉS AM OR TZ INICIAL 5,000.00 4,314.13 3,572.20 2,772.07 1,911.54 988.31

190.00 163.94 135.74 105.34 72.64 37.56

E

F

PA GO

5,000.00 685.87 875.87 741.93 905.87 800.13 935.87 860.53 965.87 923.23 995.87 988.31 1,025.87

SA LD O FINAL

4,314.13 3,572.20 2,772.07 1,911.54 988.31 0.00

Con estos ejemplos demostramos que es posible construir tablas de amortización con cualquier esquema de pagos y siempre podremos encontrar el saldo final igual a cero. El esquema de pagos puede ser tal que la cuota sea menor que los intereses que deben pagarse; en este caso el saldo final aumentará en lugar de disminuir.

3.4 FUNCIONES DE FECHA Y HORA: Calcular el número de días entre dos fechas Utilice el operador de sustracción (-) o la función DIAS.LAB para realizar esta tarea. FUNCION DIAS.LAB Devuelve el número de días laborables entre fecha_inicial y fecha_final. Los días laborables no incluyen los fines de semana ni otras fechas que se identifiquen en el argumento festivos. Utilice Elaborado por: Ing. Marcos González

DIAS.LAB para calcular el incremento de los beneficios acumulados de los empleados basándose en el número de días trabajados durante un período específico. Si esta función no está disponible y devuelve el error #¿NOMBRE?, instale y cargue el programa de complementos Herramientas para análisis.

(

; ;festivos) . Las fechas deben introducirse mediante la función FECHA o como resultado de otras fórmulas o funciones. Por ejemplo, utilice FECHA(2008;5;23) para el día 23 de mayo de 2008. Pueden producirse problemas si las fechas se introducen como texto. Fecha_inicial es una fecha que representa la fecha inicial. Fecha_final es una fecha que representa la fecha final. Festivos es un rango opcional de una o varias fechas que deben excluirse del calendario laboral, como los días festivos nacionales y locales. La lista puede ser un rango de celdas que contengan las fechas o una constante matricial de los números de serie que representen las fechas.

Microsoft Excel almacena las fechas como números de serie secuenciales para que puedan utilizarse en los cálculos. De forma predeterminada, el 1 de enero de 1900 es el número de serie 1 y el 1 de enero de 2008 es el número de serie 39448 porque viene 39.448 días después del 1 de enero de 1900. Microsoft Excel para Macintosh utiliza un sistema de fechas predeterminado diferente. Si uno de los argumentos no es una fecha válida DIAS.LAB devuelve el valor de error #¡VALOR!. Ejemplo del ejercicio 78. Sintaxis DIAS.LAB(fecha_inicial;fecha_final;festivos ) Fecha inicia l Fe cha final Festivos DIAS 2003-05-15

2003-07-28

53

Nota: Para que el resultado sea en números (no en fechas), la celda días debe estar configurado como número.

Utilice las funciones MES y AÑO para realizar esta tarea. FUNCION MES Elaborado por: Ing. Marcos González

Devuelve el mes de una fecha representada por un número de serie. El mes se expresa como número entero comprendido entre 1 (enero) y 12 (diciembre).

( ) Núm_de_serie es la fecha del mes que intenta buscar. Las fechas deben introducirse mediante la función FECHA o como resultados de otras fórmulas o funciones. Por ejemplo, utilice FECHA(2008;5;23) para el día 23 de mayo de 2008. Pueden producirse problemas si las fechas se introducen como texto.

Microsoft Excel almacena las fechas como números de serie secuenciales para que puedan utilizarse en los cálculos. De forma predeterminada, el 1 de enero de 1900 es el número de serie 1 y el 1 de enero de 2008 es el número de serie 39448 porque viene 39.448 días después del 1 de enero de 1900. Microsoft Excel para Macintosh utiliza un sistema de fechas predeterminado diferente. Los valores devueltos por las funciones AÑO, MES Y DIA serán valores gregorianos independientemente del formato de visualización del valor de fecha suministrado. Por ejemplo, si el formato de visualización de la fecha suministrada es Hijri, los valores devueltos para las funciones AÑO, MES Y DIA serán valores asociados con la fecha gregoriana equivalente.

Utilice la función AÑO para esta tarea. FUNCION AÑO Devuelve el año correspondiente a una fecha. El año se devuelve como número entero comprendido entre 1900 y 9999.

( ) Núm_de_serie es la fecha del año que desee buscar. Las fechas deben introducirse mediante la función FECHA o como resultados de otras fórmulas o funciones. Por ejemplo, utilice FECHA(2008;5;23) para el día 23 de mayo de 2008. Pueden producirse problemas si las fechas se introducen como texto. Elaborado por: Ing. Marcos González

Microsoft Excel almacena las fechas como números de serie secuenciales para que puedan utilizarse en los cálculos. De forma predeterminada, el 1 de enero de 1900 es el número de serie 1 y el 1 de enero de 2008 es el número de serie 39448 porque viene 39.448 días después del 1 de enero de 1900. Microsoft Excel para Macintosh utiliza un sistema de fechas predeterminado diferente. Los valores que devuelven las funciones AÑO, MES Y DIA serán valores gregorianos independientemente del formato de visualización del valor de fecha suministrado. Por ejemplo, si el formato de visualización de la fecha suministrada es Hijri, los valores devueltos para las funciones AÑO, MES Y DIA serán valores asociados con la fecha gregoriana equivalente. Si no están disponibles estas funciones, instale y cargue el programa de complementos Herramientas para análisis.

3.5 FUNCIONES LÓGICAS: Permite realizar una comparación entre dos o más opciones por medio de criterios establecidos, estableciendo bien sean una respuesta verdadera o falsa dependiendo de si se cumple o no con dicho criterio. Se encuentran empleadas comomuy partepocas de estafunciones, función. la más usada es la función SI, las demás pueden ser


Prueba lógica: No es más que el criterio que se debe cumplir. Por ejemplo que la celda A5 debe ser mayor que 5 (A5>5). Valor_si_verdadero: Es el resultado que será mostrado en caso de cumplirse el criterio. Valor_si_falso: Es el resultado que será mostrado en caso de no cumplirse el criterio.

Ejemplo: Se desea realizar una hoja de calculo para analizar los resultados un análisis al sistema de mechurrio de tal manera que sea capaz de decir en letras si paso o no la prueba de contenido de H2S. PPM H2S 347 377 41 212 310 251 368

El nivel mínimo de PPM en el ambiente debe ser de 200. Solución: Hacer click en insertar funciones y seleccionar la función si.

225 174 130 13 373 377


Luego establecer los parámetros:

PPM H2S

EFECTO

347 SI CONTAMINACION 377 SI CONTAMINACION 41 NO CONTAMINACION 212 SI CONTAMINACION 310 SI CONTAMINACION 251 SI CONTAMINACION 368 SI CONTAMINACION 225 SI CONTAMINACION 174 NO CONTAMINACION 130 NO CONTAMINACION 13 NO CONTAMINACION 373 SI CONTAMINACION 377 SI CONTAMINACION

3.6 FUNIONES VARIAS Y COMBINACION DE FUNCIONES: Función Buscarv: Vamos a seguir con una de las funciones más útiles que existen de cara al control de una lista de argumentos como podrían ser, por ejemplo, productos de una empresa. Observa la sintaxis de la función =BUSCARV( ) =BUSCARV(Celda;Rango;Columna) Elaborado por: Ing. Marcos González

Es decir, buscará el valor de una celda en un rango de celdas y retornará el contenido de n columnas a su derecha. Suponte que es un lista súper larga de artículos en almacén. Observa que en la parte superior hemos preparado tres casillas de colorines. Estas celdas servirán para nuestro propósito. En la celda C2 colocaremos la fórmula: =BUSCARV(C1;A7:C15;2) ¿Para qué servirá esta hoja? Lo que haremos será escribir un código de artículo en la celda C1 (amarilla) y Excel hará que aparezca automáticamente la descripción y la cantidad disponible en las dos celdas inferiores.

Este tipo de hojas va perfecto para hacer una consulta a un listado. La fórmula mirará lo que hay en la celda C1, y lo buscará en el rango A7:C15. Una vez que lo encuentre, (lo encontrará en la 1ª columna), mostrará lo que hay 2 columnas a su derecha (contándose ella), es decir, la descripción del producto.

4 CREANDO MACROS EN EXCEL es un programa que tiene un gran potencial, pero la mayoría de la gente lo maneja de una forma muy simple, utilizando solo opciones básicas, pero hay algo muy importante que tengo que decirles. cuenta con un lenguaje muy poderoso llamado , este es solo una parte del lenguaje, pero permite hacer o resolver los problemas más Elaborado por: Ing. Marcos González

fácilmente, solo debemos aprender a programarlo y para eso es este curso, podría la gente decir que este curso es un nivel muy alto de y quizás si lo sea, pero es fácil de aprender ya que se manejaran términos sencillos, a mi me gusta hablar con palabras que todo el mundo entienda y eso lo hace más fácil. La programación que emplea en este curso o las estructuras que aparecen son creadas por su servidor, ya que para manejar la programación de con es necesario tener mucha creatividad, cada persona puede crear estructuras diferentes pero que trabajen igual. Así que manos a la obra.

Primeramente debemos de saber que es una Macro y a continuación se explica el termino: Una Macro son una serie de pasos que se almacenan y se pueden activar con alguna tecla de control y una letra. Por ejemplo, todos los días empleo frecuentemente en mis celdas los mismos pasos: Tamaño de Fuente (Tamaño de la letra), Negrita, Fuente (Tipo de letra) y Color de Fuente(Color de Letra), para no estar repitiendo estos pasos los puedo almacenar en una macro y cuando ejecute la macro los pasos antes mencionados se ejecutaran las veces que yo lo desee. A continuación te muestro como grabar una macro y ejecutarla: 1. Trasládate a la celda y escribe tu Nombre. Por ejemplo, y presiona Enter 2. Regrésate a la celda , porque cuando diste Enter bajo de celda o cambio el rumbo. 3. Da clic en el , seguido por la Opción y elija . Se activara la barra de herramientas Visual Basic.


4. Da clic en el botón , el que tiene la ruedita Azul. Windows activa el cuadro de dialogo , el cual permitirá darle el nombre a la macro y cual será el método abreviado para ejecutarla. El método Abreviado se refiere con que letra se va activar la macro, obviamente se activara con la tecla Control y la letra que usted quiera, de preferencia en minúscula, porque si activa las mayúsculas la macro se activara presionando la tecla Shift + Control + la letra que usted indico.

5. Donde dice ya aparece el nombre que llevara la macro en este caso . si desea cambiar el nombre escriba uno nuevo, pero yo le recomiendo que así lo deje. 6. En la opción aparece que se activara con la tecla + la letra que usted indica, de clic en el cuadrito y ponga una letra, por ejemplo ponga la letra (en minúsculas). La macro se activara cuando este lista con la tecla 7. De clic en el . Windows empezara a grabar todos los pasos en la .y el botón de la ruedita azul cambiara de forma ahora será un cuadrito Azul, se llamara . Lo utilizaremos cuando terminemos de indicarle los pasos para detener la grabación. 8. Cambie el Tipo de Letra en el

de la barra de herramientas Formato Elaborado por: Ing. Marcos González

9. Cambie el tamaño de la letra en el de la barra de herramientas Formato 10. Presione el de la barra de herramientas Formato 11. Cambie el color de la letra en el de la barra de herramientas Formato. Recuerde que todos estos pasos están siendo almacenados en la macro que estamos grabando y también recuerde que estos pasos se están efectuando en la celda . 12. Presione el Botón de la barra de Herramientas de El que tiene el cuadrito azul presionado.

Listo Excel guardo los pasos en la Macro1 que se activara presionado la tecla 13. Escribe otro nombre en la celda y presiona , después regresa a la celda . 14. Presiona la tecla . Windows efectuara todos los pasos grabados sobre la celda , esto quiere decir que el nombre que esta en tendrá las características del que esta en la macro.

. Tipo de letra, tamaño, negrita y el color que indicaste al grabar

. Cada vez que presiones Excel ejecutara la macro y efectuara los pasos en la celda que te encuentres. Puedes grabar todas las macros que desees. Ahora te recomiendo que domines estos pasos antes de pasar a la siguiente fase. Trata de crear macros que almacenen pasos como estos, recuerda los pasos los vas a indicar tu, que no se te olvide detener la grabación después de que indicaste los pasos, repite este ejercicio las veces que sea necesario para aprendértelo bien. Practica I Genera las siguientes Macros: Elaborado por: Ing. Marcos González

Graba una Graba una

que se active con que se active con

y que esta macro permita abrir un archivo y que esta macro permita insertar un WordArt

Muchos pensaran que esto no es nada pero ya veremos mas adelante cuando mezclemos los códigos que genera

con los de

. Esto será pura

.

Bien, ahora después de practicar la con diferentes ejemplos o pasaremos a la siguiente que nos permitirá observar los códigos que hemos generados con nuestra macros. Te recomiendo que salgas de y vuelvas a entrar, para que trabajes limpio sin ninguna macro y empezando de la macro1 de nuevo.

Crearemos una macro y veremos sus códigos: . Para observar los códigos de una macro debemos de seguir los siguientes pasos: 1. Primeramente trasládese a la celda antes de empezar la grabación de la Macro 2. Presione el Botón de la barra de Herramientas muestra el cuadro de Dialogo Grabar Macro 3. en la opción escriba la letra , por lo tanto la macro se llamara con 4. Presione el botón inicia la grabación del la 5. Trasládese a la celda y escriba , después presione para aceptar el valor en la celda 6. Pare la grabación de la macro presionando el botón de la barra de herramientas Excel a grabado los pasos y a generado un código, Observémoslos: Elaborado por: Ing. Marcos González

7. Presione la tecla Alt + la tecla de función F11( ). Excel nos traslada al Editor de Visual Basic. Si este editor no se activa es que Excel no esta bien instalado o se a borrado. También puede acceder desde el . 8. Active los siguientes cuadros o ventanas: 



De clic en el

y elija la opción

De clic en el

y elija la opción

Estas dos opciones deben de estar siempre activadas ya que de ahí depende todo lo que vallamos a hacer.

9. Del cuadro

de doble clic en

que aparece en la opción

o simplemente presione el signo de . Se activara debajo de

la Opción

10. De doble clic en . Se mostrara en el Editor de Visual Basic el código de la macro que grabamos de la siguiente forma:


Sub Macro1() ' ' Macro1 Macro ' Macro grabada el 08/04/2001 por RAMON MENDOZA OCHOA ' ' Acceso directo: CTRL+r ' Range("A1").Select ActiveCell.FormulaR1C1 = "Ramón" Range("A2").Select End Sub Que es lo que significa esto nos preguntaremos asombrados, a continuación se da una explicación de lo que ha hecho : 









y indican el inicio y el final del procedimiento de la Todo lo que aparece con un apostrofe indica que no se tomara en cuenta que es solo texto o comentarios y ese texto debe de aparecer en un color, ya sea el color verde. Indica que lo primero que hicimos al grabar la macro fue trasladarnos a la celda . La orden nos permite trasladarnos a una celda Esto indica que se escribirá en la celda en que se encuentra el valor de texto . Todo lo que aparece entre comillas siempre será un valor de texto. La orden nos permite escribir un valor en la celda activa. Otra vez indicamos que se traslade a la celda A2. Esto se debe a que cuando escribimos el nombre de en presionamos y al dar bajo a la celda .

Para comprender mejor alteraremos el código dentro del editor de Visual Basic. Que crees que pasara aquí con nuestra Macro: Elaborado por: Ing. Marcos González

Sub Macro1() ' ' Macro1 Macro ' Macro grabada el 08/04/2001 por RAMON MENDOZA OCHOA ' ' Acceso directo: CTRL+r ' Range("A1").Select ActiveCell.FormulaR1C1 = "Ramón" Range("B1").Select ActiveCell.FormulaR1C1 = "Calle 21 de Marzo #280" Range("C1").Select ActiveCell.FormulaR1C1 = "31-2-47-13" Range("D1").Select ActiveCell.FormulaR1C1 = "Nogales Sonora" Range("E1").Select ActiveCell.FormulaR1C1 = "CONALEP NOGALES" End Sub Así es acabo de alterar el código y cuando regrese a hará lo siguiente:

y ejecute la macro con

Así que salgamos del editor dando clic en el y eligiendo la opción . Si no desea salir por completo de clic en que se encuentra activado en la barra de tareas y cuando deseé volver al editor de clic en el que se encuentra en la barra de Tareas. Elaborado por: Ing. Marcos González

Ahora ya que salimos de y estamos en de Nuevo ejecutemos la macro presionando y veamos los resultados de nuestra modificación. Que te parece es sencillo o No?, Claro necesitamos practicar bastante para dominar esto, así que repasa la cuantas veces sea necesario, otra cosa no trates de generar códigos muy complejos en tus macros porque te vas a enredar, poco a poco se va lejos. Practica II Genera una que escriba un nombre en una celda y lo ponga negrita y observa el . Genera una que escriba un nombre en una celda y lo Centre y observa el . Genera una que escriba un nombre en una celda y cambie el tamaño de la letra a 20 puntos y observa el .

Códigos Más comunes:

Range("A1").Select Activecell.FormulaR1C1="Ramon" Selection.Font.Bold = True Selection.Font.Italic = True Selection.Font.Underline = xlUnderlineStyleSingle With Selection .HorizontalAlignment = xlCenter End With With Selection .HorizontalAlignment = xlLeft End With Elaborado por: Ing. Marcos González

With Selection .HorizontalAlignment = xlRight End With With Selection.Font .Name = "AGaramond" End With With Selection.Font .Size = 15 End With Selection.Copy ActiveSheet.Paste Selection.Cut Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Cells.Find(What:=" ", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate Selection.EntireRow.Insert Selection.EntireRow.Delete Elaborado por: Ing. Marcos González

Selection.EntireColumn.Insert Selection.EntireColumn.Delete Workbooks.Open Filename:="C:\Mis documentos\

"

ActiveWorkbook.SaveAs Filename:="C:\Mis documentos\

", FileFormat _

:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _ False, CreateBackup:=False Estos serian algunos códigos muy comunes en , pero si usted desea puede generar mas códigos de otras opciones, es cuestión de que los ocupe. Antes de pasar a la es necesario que domines generar y Observar sus códigos que se encuentran en la Recuerda esto es de mucha práctica y para eso es necesario aprender bien las fases Anteriores. .

Antes de Empezar esta nueva fase te recomiendo que salgas de y vuelvas a entrar, esto es por si estuviste practicando los códigos, para que no quede una secuencia de Macros. Ahora te enseñare a dominar lo máximo de Excel que es crear formularios y programarlos, bueno un formulario es una ventana que se programa por medio de controles y estos controles responden a sucesos que nosotros programamos. Todo esto se encuentra dentro de Visual Basic. A continuación Muestro como crear un formulario y como programarlo: Elaborado por: Ing. Marcos González

1. Presione La Teclas para entrar al editor de . 2. Activa las siguientes opciones: De clic en el y elija la opción De clic en el y elija la opción 3. Del elija la Opción Esto inserta el Formulario que programaremos con controles. En el se observara que se inserto el . 



También cuando de clic en el Formulario , si no se activa de clic en el .

se debe de activar y elija la opción

4. Elija del el Control el que tiene la y Arrastre dibujando en el Formulario la etiqueta. Quedara el nombre Label1, después de un clic en la etiqueta dibujada y podrá modificar el nombre de adentro y pondremos ahí Si por error da doble clic en la etiqueta y lo manda a la pantalla de programación de la etiqueta, solo de doble clic en que se encuentra en el 5. Elija del el control el que tiene y arrastre dibujando en el formulario el cuadro de texto a un lado de la etiqueta que dice . El cuadro de texto debe de estar vacío y su nombre será , el nombre solo aparecerá en el control. Elaborado por: Ing. Marcos González

6. Haga los dos pasos anteriores igualmente poniendo en la y en la y también dibújeles su Textbox. Esto quedara así después de haberlo hecho

al dibujar las etiquetas o los cuadros de texto, solo cámbiele el nombre a la etiqueta o el cuadro de texto en la la opción se llama El Error que marque puede ser pero si le cambias el Nombre al control se quitara el error. Puedes ponerle cualquier nombre en lugar de Label1.


Los controles como las Etiquetas y Cuadros de Textos pueden modificárseles algunas opciones en la Ventana Propiedades Para hacer esto es necesario tener conocimiento sobre las propiedades de los controles. No altere las propiedades si no las conoce.

7. Elija del dibujando en el Formulario

el control y Arrastre el Botón, después de un clic en el

nombre del Botón dibujado y podrá modificar el nombre y pondremos ahí Si por error da doble clic en la Botón y lo manda a la pantalla de programación de la etiqueta, solo de doble clic en que se encuentra en el

Así quedara el Formulario formado por los controles:


8. Ahora de doble clic sobre el control el siguiente código:

para programarlo y después inserte

Private Sub TextBox1_Change()

End Sub Esto indica que se valla a

y escriba lo que hay en el

.-Lo que esta en azul lo genera Excel automáticamente, usted solo escribirá lo que esta en Negrita. Para volver al y programar el siguiente Textbox de doble clic en que se encuentra en el o simplemente de clic en en el mismo . 9. Ahora de doble clic sobre el control para programarlo y después inserte el siguiente código: Private Sub TextBox2_Change()



Para volver al y programar el siguiente Textbox de doble clic en que se encuentra en el o simplemente de clic en en el mismo . 10. Ahora de doble clic sobre el control

para programarlo y después inserte

el siguiente código: Elaborado por: Ing. Marcos González

Private Sub TextBox3_Change()



Para volver al y programar el que se encuentra en el en el mismo

11. Ahora de doble clic sobre el control después inserte el siguiente código:

de doble clic en o simplemente de clic en .

para programarlo y

Private Sub CommandButton1_Click()

End Sub El comando es empleado para poner comentarios dentro de la programación, el comando es empleado para vaciar los Textbox. 12. Ahora presione el botón que se encuentra en la barra de herramientas o simplemente la tecla de función


Se activara el y todo lo que escriba en los Textbox se escribirá en Excel y cuando presione el botón Insertar, se insertara un renglón y se vaciaran los Textbox y después se mostrara el cursor en el . En este archivo que usted bajo se encuentra una hoja de Excel Libre de Virus o sea que esta limpio, ábralo sin ningún problema, ya que ahí viene un ejemplo de la Macro ya realizada y solo la ejecutara y vera como trabajan las Macros. Espero y estés pendiente porque vienen mas partes sobre este interesante curso de Macros.

4.1 TRABAJANDO CON FORMULAS Es de suma importancia saber aplicar en , ya que la mayoría de las hojas de cálculos las involucran, por ejemplo los Inventarios, las Nominas o cualquier otro tipo de hoja las llevan, es por eso que en la siguiente se muestra como manejar en .




Ahora crearas un formulario con el siguiente aspecto:


el formulario tendrá: 





Tres etiquetas Tres Textbox Un Botón de Comando

Los datos que se preguntaran serán Nombre y Edad, los Días Vividos se generaran automáticamente cuando insertes la edad. A continuación se muestra como se deben de programar estos Controles: Programación de los Controles:

Private Sub CommandButton1_Click() Selection.EntireRow.Insert TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty TextBox1.SetFocus End Sub Private Sub TextBox1_Change() Range("A9").Select ActiveCell.FormulaR1C1 = TextBox1 End Sub


Private Sub TextBox2_Change() Range("B9").Select ActiveCell.FormulaR1C1 = TextBox2 Rem aquí se crea la Formula TextBox3 = Val(TextBox2) * 365 Rem El Textbox3 guardara el total de la multiplicación del Textbox2 por 365 Rem El Comando Val permite convertir un valor de Texto a un Valor Numérico Rem Esto se debe a que los Textbox no son Numéricos y debemos de Convertirlos End Sub Private Sub TextBox3_Change() Range("C9").Select ActiveCell.FormulaR1C1 = TextBox3 End Sub Esto va permitir que cuando se ejecute el formulario y se de la edad el resultado de los días vividos aparecerá en el y se escribirá también en . El comando es un comando de que te permite convertir un valor de texto a un valor numérico. Recuerden el Comando Rem se utiliza para poner Comentarios únicamente y no afecta a la programación. Este Archivo de esta

se llama

y viene incluido aquí.

Generaremos otro ejemplo, Crea el Siguiente Formulario con los siguientes datos: 





5 Etiquetas 5 Textbox 1 Botón de Comando

Los datos que se preguntaran serán Nombre, Días Trabajados, Pago por Día, Bonos y Sueldo Neto.


Genera el siguiente código: Private Sub CommandButton1_Click() Selection.EntireRow.Insert TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty TextBox1.SetFocus End Sub Private Sub TextBox1_Change() Range("A9").Select ActiveCell.FormulaR1C1 = TextBox1 End Sub Private Sub TextBox2_Change() Range("B9").Select ActiveCell.FormulaR1C1 = TextBox2 End Sub Private Sub TextBox3_Change() Range("C9").Select ActiveCell.FormulaR1C1 = TextBox3 End Sub


Private Sub TextBox4_Change() Range("D9").Select ActiveCell.FormulaR1C1 = TextBox4 Rem aquí se crea la formula TextBox5 = Val(TextBox2) * Val(TextBox3) + Val(TextBox4) Rem El TextBox5 guardara el total End Sub Private Sub TextBox5_Change() Range("E9").Select ActiveCell.FormulaR1C1 = TextBox5 End Sub Cuando se introduzca el Bonos automáticamente se generara el Sueldo Neto. Este ejemplo viene en el Archivo

Se puede buscar información con un Textbox programándolo de la siguiente forma:

Dibuje una

, un

y un

y agregue el siguiente Código:

Private Sub TextBox1_Change() Range("a9").Select ActiveCell.FormulaR1C1 = TextBox1 End Sub Elaborado por: Ing. Marcos González

Private Sub CommandButton1_Click() Cells.Find(What:= , After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate End Sub Si te fijas incluí en la programación del Textbox1 a la hora de Presionarse.

que buscara lo que en el

Este proceso viene en el Archivo Completo

Todo debe de tener su propia , es por eso que en este nuevo capitulo nos concentramos en ello, primeramente en poder que ya se escribió en la , obviamente desde una combinada con , observemos el siguiente ejemplo:




Ahora crearas un formulario con el siguiente aspecto:


el formulario tendrá: 





Tres etiquetas Tres Textbox Tres Botones de Comando

Los datos que se preguntaran serán . Los tres botones nos servirán para lo siguiente: consultara la información que hayamos insertado desde el botón insertar. podrá eliminar algún dato que se consulto y no lo queremos. tendrá la función de insertar los registros que vayamos dando de alta, es como los ejercicios anteriores. A continuación se muestra como se deben de programar estos Controles: Programación de los Controles:

Private Sub CommandButton1_Click() Cells.Find(What:= , After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate ActiveCell.Offset(0, 1).Select TextBox2 = ActiveCell Elaborado por: Ing. Marcos González

Rem la línea que contiene el permite moverse una columna a la derecha, por lo tanto después de la búsqueda de las primeras líneas con si encuentra el Nombre de la persona se mueve a la siguiente columna y la línea Permite capturar el valor de la celda al y así mostrar el dato de la celda en el ActiveCell.Offset(0, 1).Select TextBox3 = ActiveCell Rem Cada vez que se escriba la línea que moverse una columna a la derecha.

significa que se tiene

Rem Si el nombre que tratas de consultar no se encuentra podría generar un error porque fallaría el Cell.Find esto puede ocurrir en el Word 97, yo trabajo con el Word 2000 o XP y no tengo ese problema. Pero esto se solucionaría con una trampa de error. End Sub

Private Sub CommandButton2_Click() Selection.EntireRow.Delete Range("A9").Select TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty TextBox1.SetFocus End Sub


Private Sub CommandButton3_Click() Range("A9").Select Selection.EntireRow.Insert TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty TextBox1.SetFocus End Sub

Private Sub TextBox1_Change() Range("A9").FormulaR1C1 = TextBox1 Rem esta primer línea reemplaza a estas dos…… que te parece todavía mas corta

Range("A9").Select ActiveCell.FormulaR1C1 = TextBox1 End Sub

Private Sub TextBox2_Change() Range("B9").FormulaR1C1 = TextBox2 End Sub Private Sub TextBox3_Change() Range("C9").FormulaR1C1 = TextBox3 End Sub Elaborado por: Ing. Marcos González

Si con el tienes un error cuando no encuentra a la persona, entonces tendrás que agregar esto a tu código del


Rem esta línea genera una trampa de error si Excel encuentra un error se le dice que se vaya a la etiqueta que esta definida mas adelante en el código. No use la trampa de error si no tiene problemas a la hora de que no encuentra a la persona. Recuerde si usted comete cualquier error Excel se dirigirá a la etiqueta .y esquivara cualquier error, hasta uno que usted cometa en la programación. Cells.Find(What:= , After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate

Rem También se puede utilizar este código para leer la información de las celdas lo que esta en azul. La diferencia es que se asignan los valores a variables y después se descargan a los TextBoxs.


Rem Aquí se esquiva el error End Sub

Que te parece es increíble como una Macro combinada con Visual Basic puede hacer hasta lo imposible

Bueno ya tenemos elaborado un ejercicio de consultas de datos, ahora accesaremos al formulario desde sin necesidad de entrar al . Para realizar este ejercicio debemos permanecer dentro del para poder introducir el código en un , por lo tanto deberás seguir los siguientes pasos:  

De clic en el Escriba dentro del

y elija la opción el nombre del modulo en este caso

Cuando usted escriba Sub Entrada aparecerá de la siguiente manera: Sub Entrada() Load UserForm1 UserForm1.Show End Sub Elaborado por: Ing. Marcos González

Usted deberá escribir las dos líneas que están en medio que son:

La primer línea significa que cargue a la memoria el formulario que se llama la segunda línea significa que lo muestre, esto quiere decir que en el modulo estamos

,

escribiendo el código de una macro que permitirá cargar el formulario desde Excel sin necesidad de entrar al . Si te fija en el explorador de proyecto aparece el

si queremos volver al formulario solo da doble clic en Bueno ya esta listo ahora salgamos del  

De clic en el Elija la opción

Ya que estamos en

que creamos.

y volvamos a

del

, podemos insertar una imagen o un botón o cualquier grafico,

por ejemplo: Elaborado por: Ing. Marcos González

       

De clic en el Elija la opción , seguido por inserte cualquier imagen y dele el tamaño que usted desea. De clic derecho sobre la Imagen Elija la opción De clic en la que se llama , es obvio la única que hicimos De Clic en De clic fuera de la imagen en cualquier celda y listo si presionas la imagen cargara el formulario.

Que te parece es increíble el mundo de las Este ejemplo viene en el archivo

o No. junto con esta guía.

Bueno empezaremos con como agregar información a un y un , primeramente deberás crear el siguiente formulario dentro de , recuerda desde Excel se utiliza la tecla para entrar a , seguido del y después , bueno creo que ya lo sabes. Inserta Un y un

. Ahora que ya creaste la Interfaz vamos a programar el botón, veremos como se le puede agregar información por medio de código a estos dos controles. Da doble clic en el siguientes líneas procedimiento.

y escribe las dentro del



End Sub Bueno vamos a analizar el significado de estas líneas:

La opción significa que vas a agregar un dato de texto, por lo tanto se entiende como vas a agregar a Juan José al , por lo tanto yo puedo agregar los datos que quiera a un o un con la opción AddItem, entonces al presionar el botón aparecerán los datos que se encuentra escritos y podrás seleccionar cualquiera de ellos, recuerda que la información la vas a agregar según tus necesidades. Ahora si deseas agregar números a un Combobox o ListBox escribe el siguiente código en un botón: Private Sub CommandButton1_Click()

End Sub La Instrucción

es un ciclo contador que te permite contar desde un numero

hasta otro. Por ejemplo le digo que cuente desde el 1 hasta el 50 y lo que se encuentre dentro del ciclo se ejecutara el número de veces, la X es una variable numérica Elaborado por: Ing. Marcos González

que guarda el valor, cada vez que el ciclo da una vuelta aumenta un numero, por lo tanto X va a valer desde 1 hasta 50, y la instrucción es para convertir el valor numérico de la X en valor de Texto, ya que la opción guarda solo texto, claro esta que también puede funcionar sin esta instrucción en algunos casos. Por lo tanto el Listbox1 va a guardar los número del 1 al 50, sin necesidad de irlos poniendo de uno por uno, imagínatelo.

Ya te quiero ver en el código para que llegues al 50. Bueno esto es para introducirle datos a un , pero como puedo usar estos datos para enviarlos para una celda, en el siguiente ejemplo te lo explico: Da doble clic en el

y escribe el siguiente código:

Private Sub ListBox1_Click() End Sub Así de de fácil cada vez que escojas un dato que se encuentre en un lo enviara a la celda , escribiéndolo ahí. Si lo deseas hacer lo puedes hacer en un Combobox, solo cambia por y se acabo. Ahora si deseas agregar los datos al escribe el siguiente código:

o

sin ningún botón que presionar

Private Sub UserForm_Activate()


End Sub La Clave esta en el procedimiento esto quiere decir que cuando se active el formulario cargara lo que tu le indiques, en este caso va a introducir los datos al y automáticamente, que te parece. Ahora si deseas tomar información de una celda y enviarla a un escribe el siguiente código en un Botón:

o


End Sub


Fíjate bien, primeramente muevo el rango a la celda porque ahí esta el inicio de mi información, después la línea significa Hazlo mientras la celda no se encuentre vacía, la siguiente línea que es significa Baja un Renglón, la siguiente línea agrega la información de la celda al y la línea es parte del ciclo siempre cierra el ciclo, como el Por lo tanto todos los nombres que estén delante de serán enviados al y cuando tope con la celda que se encuentra vacía la condición del parara la ejecución de su código. Esto funciona caminando renglones hacia abajo, pero si deseas moverte hacia la derecha por columnas solo cambia la línea por quiere decir que se mueva por columna, no por renglón. Si cambias el 1 por otro numero se moverá el numero de veces que tu le indiques, por ejemplo si quiero bajar 10 renglones de un golpe:

Si quiero moverme 20 columnas a la derecha

Así funciona esto. Ahora veremos como se ejecuta una macro a la hora de abrir un libro Primeramente inserta un siguiente código:

del

dentro de

y escribe el

Sub Auto_open()

End Sub


La magia esta en el procedimiento que permite ejecutar automáticamente lo que se encuentre dentro de el cuando abras un libro que contenga este código, en este ejemplo cuando se abre el libro se activa el formulario 1 que programe. Así que todo lo que agregues dentro de este procedimiento se ejecutara automáticamente cuando abras un libro, que te parece.

A continuación veremos cómo ordenar una información por orden alfabética ascendente, es un código muy completo y bueno que te permite localizar los datos y ordenarlos, sin pasarse un renglón en blanco.

Observemos el siguiente ejemplo y aprendamos de el: Si se fijan en la siguiente pantalla tengo datos en una hoja que empinan en el renglón y terminan en , el siguiente código detectara donde debe detenerse para poder ordenar los datos. Es necesario crear el código para ordenar datos, pero aquí yo te lo muestro:


Programa esto en el botón1 Private Sub


End Sub


Así es como funciona este código de Macros de Excel ordenando exactamente desde A10 hasta donde están los datos finales.

Bueno ahora para convertir la información a Minúscula o Mayúscula es muy parecido el código, solo obsérvalo: Private Sub Range("a10").Select Do While ActiveCell <> Empty ActiveCell.FormulaR1C1 = ActiveCell.Offset(1, 0).Select

(ActiveCell)

Loop End Sub Así es la magia esta en que convierte a Minúsculas y a Mayúsculas, empieza en A10 y hasta que no encuentra datos deja de convertir a Minúsculas. El siguiente Formulario y código muestra la fuerza de cómo se puede consultar y modificar el dato que se encontró. escríbele el numero 9 dentro.


Crea la siguiente Interfaz, 4 Etiquetas, 3 Textbox y 3 Botones Copia el siguiente código: Private Sub CommandButton1_Click()

If TextBox1 = Empty Then Range("A9").FormulaR1C1 = "No Tiene" If TextBox2 = Empty Then Range("B9").FormulaR1C1 = "No Tiene" If TextBox3 = Empty Then Range("C9").FormulaR1C1 = "No Tiene" Range("A9").Select Selection.EntireRow.Insert TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty TextBox1.SetFocus End Sub Private Sub CommandButton2_Click() On Error GoTo noencontro

Cells.Find(What:=TextBox1, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate ActiveCell.Offset(0, 1).Select Elaborado por: Ing. Marcos González

TextBox2 = ActiveCell ActiveCell.Offset(0, 1).Select TextBox3 = ActiveCell

Label4 = ActiveCell.Row noencontro:

End Sub Private Sub CommandButton3_Click()

Label4 = "9" Range("a9").Select TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty TextBox1.SetFocus End Sub Private Sub TextBox1_Change()

Range("A" + Label4).FormulaR1C1 = TextBox1 End Sub Private Sub TextBox2_Change() Elaborado por: Ing. Marcos González

Range("B" + Label4).FormulaR1C1 = TextBox2 End Sub Private Sub TextBox3_Change() Range("C" + Label4).FormulaR1C1 = TextBox3 End Sub

4.2 GRAFICOS CON MACROS: trabajaremos con gráficos en Excel, veremos como se puede generar una grafica desde un código generado y alterado por nosotros mismos.


Si observamos los datos que vamos a graficar nos damos cuenta que en la columna encuentran los

y en la columna los

se

, estos datos son

necesario para efectuar una grafica que podría quedar así

Esta grafica muestra las edades de 5 personas, los nombres son y la edad , ahora veremos como se puede detectar estos datos por medio de una Macro Al graficar estos datos se genero el siguiente código:


Sub

End Sub 1. 2. 3. 4. 5.

La primer línea indica el rango donde están los datos, , La segunda línea indica que se agrega una grafica La tercera línea indica el tipo de grafica que se desea La cuarta línea indica como se acomodan los datos en la grafica La quinta línea indica donde se muestra la grafica, si en la misma hoja o en una sola hoja. Los números de 1 al 5 no van en el código, solo los puse para poder explicar las

líneas A continuación se muestran algunos de los diferentes tipos de graficas

:





Si tu agregas al final del código principal alguna línea del tipo de grafico que te gusto, ese se activara, por ejemplo: Sub

End Sub Este código se puede programar en un botón o cualquier otro control de

.

A continuación se muestra como se acomodan los datos


En esta línea se muestra la grafica por

En esta línea se muestra la grafica por Esta es la forma en que se muestran los datos de lo que habla la La habla de que si la grafica queda en la misma hoja o simplemente toma una hoja para ella, por ejemplo:


Esta línea indica que la grafica tenga su propia hoja y que su nombre sea En este ejemplo ejecuto un código con cada una de las características explicadas en las


Elabora el siguiente formulario con el siguiente código, para observar los diferentes tipos de gráficos y la forma en que se acomodan los datos:


Range("A5:B10").Select Charts.Add ActiveChart.ChartType = xlColumnClustered ActiveChart.SetSourceData Source:=Sheets("Hoja1").Range("A5:B10"), PlotBy:= _ xlColumns ActiveChart.Location Where:=xlLocationAsObject, Name:="Hoja1"

ListBox1.AddItem "xlColumnClustered" Elaborado por: Ing. Marcos González

ListBox1.AddItem "xlBarClustered" ListBox1.AddItem "xlLineMarkers" ListBox1.AddItem "xlPie" ListBox1.AddItem "xlXYScatter" ListBox1.AddItem "xlAreaStacked" ListBox1.AddItem "xlDoughnut" ListBox1.AddItem "xlRadarMarkers" ListBox1.AddItem "xlCylinderColClustered" ListBox1.AddItem "xlConeColClustered" ListBox1.AddItem "xlPyramidColClustered"

ListBox2.AddItem "Renglon" ListBox2.AddItem "Columna"

End Sub Private Sub ListBox1_Click()

If ListBox1 = "xlColumnClustered" Then ActiveChart.ChartType = xlColumnClustered If ListBox1 = "xlBarClustered" Then ActiveChart.ChartType = xlBarClustered If ListBox1 = "xlLineMarkers" Then ActiveChart.ChartType = xlLineMarkers If ListBox1 = "xlPie" Then ActiveChart.ChartType = xlPie If ListBox1 = "xlXYScatter" Then ActiveChart.ChartType = xlXYScatter If ListBox1 = "xlAreaStacked" Then ActiveChart.ChartType = xlAreaStacked If ListBox1 = "xlDoughnut" Then ActiveChart.ChartType = xlDoughnut If ListBox1 = "xlRadarMarkers" Then ActiveChart.ChartType = xlRadarMarkers Elaborado por: Ing. Marcos González

If ListBox1 = "xlCylinderColClustered" Then ActiveChart.ChartType = xlCylinderColClustered If ListBox1 = "xlConeColClustered" Then ActiveChart.ChartType = xlConeColClustered If ListBox1 = "xlPyramidColClustered" Then ActiveChart.ChartType = xlPyramidColClustered End Sub Private Sub ListBox2_Click() If ListBox2 = "Renglon" Then ActiveChart.SetSourceData Source:=Sheets("Hoja1").Range("A5:B10"), PlotBy:= _ xlRows End If If ListBox2 = "Columna" Then ActiveChart.SetSourceData Source:=Sheets("Hoja1").Range("A5:B10"), PlotBy:= _ xlColumns End If End Sub


Antes de ejecutar esta

llenas los datos anteriores en la hoja1 de

En este archivo indexo un ejemplo de un libro de cómo se puede generar una que realice 25 graficas, esto por medio un modulo, este ejemplo es apoyado por su servidor hacia una persona que deseaba realizar este trabajo. Espero le sea de utilidad el código.

4.3 COMO GRABAR DATOS DE FORMA SECUENCIAL: veremos como se pueden archivar los datos de una hoja en un archivo aparte. Aprenderemos a trabajar con

en

. Los

son aquellos que al registrar sus datos llevan una secuencia, por ejemplo si registro 5 nombres llevaran un orden del 1 al 5, en cambio existen también los Elaborado por: Ing. Marcos González

, pero ellos no respetan la secuencia, por ejemplo los 5 nombres podrían quedar en cualquier posición del 100 en adelante, del 300 en adelante, del 10 en adelante, de donde quieras ponerlos, tu indicas en donde quieres que queden los 5 nombres, pueden quedar hasta separados y no respetar una secuencia.

El problema de los

, es que si introduces algunos símbolos en la

captura pueden alterar el archivo y no funcionar correctamente, por eso se recomienda filtrar los datos con algún código o simplemente no capturar símbolos.

En esta hoja podemos observar 5 nombres, la intención será archivarlos aparte y hacerlos desparecer de la hoja, para después volverlos aparecer en la hoja. A esto se le llamara Registro de datos y Consulta de datos. Elaborado por: Ing. Marcos González

Iremos a

con

y Insertaremos un

dos botones, uno con el nombre de

en el cual dibujaremos

y Otro con el Nombre de

.

Ahora a programar el botón Registro, para poder archivar los nombres.


Rem se translada a la celda a8 Range("a8").Select Rem si no hay ningún dato en a8 que no archive de nuevo If ActiveCell = Empty Then GoTo salte Rem abre un archivo en la unidad c con el nombre de datos.txt Rem en forma de añadir Temporal (Output) en el área de almacenamiento #1 Open "c:\datos.txt" For Output As 1 Rem activa una etiqueta para poder regresar regresa: Rem escribe el dato de la celda activa en el archivo Write #1, ActiveCell Rem borra el dato de la celda Elaborado por: Ing. Marcos González

ActiveCell = Empty Rem baja un renglón para el siguiente nombre ActiveCell.Offset(1, 0).Select Rem si la celda esta vacía que no regrese ya If ActiveCell = Empty Then GoTo salte Rem regresa a escribir el siguiente nombre en el archivo GoTo regresa:

salte: Rem se acabo Rem cierra el archivo Close #1 End Sub

Los datos quedaran archivados en la unidad y serán devueltos cuando presiones el botón consulta. Que a continuación se muestra:

Ahora a programar el botón consulta

Private Sub CommandButton2_Click() Rem se translada a la celda a8 Range("a8").Select Elaborado por: Ing. Marcos González

Rem abre un archivo en la unidad c con el nombre de datos.txt Rem en forma de Leer (input) en el área de almacenamiento #1 Open "c:\datos.txt" For Input As 1 Rem esto significa hazlo mientras no sea fin del archivo Rem esto quiere decir que no deje de leer los datos Rem hasta que no se llegue al ultimo de ellos Do While Not EOF(1) Rem lee un dato Input #1, nombre Rem lo escribe en la celda ActiveCell.FormulaR1C1 = nombre Rem baja un renglón para el siguiente nombre ActiveCell.Offset(1, 0).Select Rem activa el ciclo Do While-que regrese hasta Rem que se cumpla la condición Loop Rem cierra el archivo Close #1 End Sub


Qué te parece archivar los datos aparte sin que nadie pueda observarlos, esta es la magia de los archivos secuénciales. Este ejemplo viene indexado en un archivo con el nombre de .

El siguiente código archiva el nombre, la dirección y el teléfono en el archivo, crea un formulario igual con dos botones.


Rem se traslada a la celda a8 Range("a8").Select Rem si no hay ningún dato en a8 que no archive de nuevo If ActiveCell = Empty Then GoTo salte Rem abre un archivo en la unidad c con el nombre de datos.txt Rem en forma de añadir Temporal(output) en el área de almacenamiento #1 Open "c:\datos.txt" For Output As 1 Rem activa una etiqueta para poder regresar regresa: Rem captura el nombre en una variable nombre = ActiveCell Rem borra el dato de la celda ActiveCell = Empty Elaborado por: Ing. Marcos González

Rem se mueve una columna a la derecha ActiveCell.Offset(0, 1).Select Rem captura la direccion en una variable direccion = ActiveCell Rem borra el dato de la celda ActiveCell = Empty Rem se mueve una columna a la derecha ActiveCell.Offset(0, 1).Select Rem captura el telefono en una variable telefono = ActiveCell Rem borra el dato de la celda ActiveCell = Empty Rem escribe los datos nombre, direccion y telefono en el archivo Write #1, nombre, direccion, telefono Rem baja un renglón para el siguiente nombre ActiveCell.Offset(1, 0).Select Rem retrocede dos columnas ActiveCell.Offset(0, -2).Select Rem si la celda esta vacía que no regrese ya If ActiveCell = Empty Then GoTo salte Rem regresa a escribir el siguiente nombre en el archivo GoTo regresa: Elaborado por: Ing. Marcos González

salte: Rem se acabo Rem cierra el archivo Close #1

End Sub

Private Sub CommandButton2_Click() Rem se translada a la celda a8 Range("a8").Select Rem abre un archivo en la unidad c con el nombre de datos.txt Rem en forma de Leer (input) en el área de almacenamiento #1 Open "c:\datos.txt" For Input As 1 Rem esto significa hazlo mientras no sea fin del archivo Rem esto quiere decir que no deje de leer los datos Rem hasta que no se llegue al ultimo de ellos Do While Not EOF(1) Rem lee los datos Input #1, nombre, direccion, telefono Rem escribe en la celda el nombre ActiveCell.FormulaR1C1 = nombre Rem se mueve una columna a la derecha Elaborado por: Ing. Marcos González

ActiveCell.Offset(0, 1).Select Rem escribe en la celda la direccion ActiveCell.FormulaR1C1 = direccion Rem se mueve una columna a la derecha ActiveCell.Offset(0, 1).Select Rem escribe en la celda el telefono ActiveCell.FormulaR1C1 = telefono Rem baja un renglón para el siguiente nombre ActiveCell.Offset(1, 0).Select Rem retrocede dos columnas ActiveCell.Offset(0, -2).Select Rem activa el ciclo Do While-que regrese hasta Rem que se cumpla la condición Loop Rem cierra el archivo Close #1 End Sub

Este ejemplo viene en el archivo También se puede consultar sin necesidad de leer los datos en la hoja, esto quiere decir leyendo directo del archivo y trayendo los datos al formulario, en el siguiente ejemplo, se programa el botón consulta en formulario. Elaborado por: Ing. Marcos González

Dibuja el siguiente formulario, los dos primeros botones es el mismo código anterior, pero el tercer botón incluye el siguiente código:


Open "c:\datos.txt" For Input As 1 Do While Not EOF(1) Input #1, nombre, direccion, telefono If nombre = TextBox1 Then TextBox2 = direccion TextBox3 = telefono End If Loop Close #1 End Sub Elaborado por: Ing. Marcos González

Este ejemplo viene en el archivo Solo corra el formulario y escriba el nombre que desea consultar y presione el tercer botón. Usted podrá consultar cualquiera de los nombres que se encuentren dentro del archivo, sin necesidad de que existan en la hoja, claro está que primero es necesario presionar el botón registro para archivarlos, pero después se pueden manipular.

4.4 EDIFICANDO SOPORTE DE VECTORES: Tú quieres realizar cálculos basado en vectores, pero Excel no posee ninguna función basada en vectores. Nosotros podemos incluir una función sin mucho esfuerzo. Primeramente debemos activar una opción en Excel que no está activa por defecto, usada solamente por programadores. Inicialmente debemos hacer click en Opciones de Excel.


Luego se debe activar la opción mostrar ficha programador en la cinta de opciones.

Luego se le da aceptar una nueva pestaña aparecerá en la cinta de opciones.

Allí se hace click donde dice visual basic y se abrirá una nueva ventana y luego le das insertar modulo:


Allí hemos entrado a las funciones de programación orientada a objetos de visual basic. Una vez allí podemos establecer procedimientos de cálculos programados. Accedemos al menú insertar y luego a procedimiento:

Allí nos aparecerá una ventana donde estableceremos el tipo de procedimiento a realizar:


Allí estableceremos que es una función que deseamos programar: Luego escribimos las siguientes sintaxis de programación:

Luego grabamos el modulo donde creamos la función y cerramos la ventana de dialogo de visual basic. Elaborado por: Ing. Marcos González

Listo ahora podemos escribir la sintaxis de la función creada anteriormente la cual realizará el cálculo.

EJERCICIO 02: Ahora obtendremos en producto cruzado de dos vectores, para lo cual necesitaremos realizar la siguiente función: Public Function v_CrossProduct(u As Range, v As Range) ' Declare local variables: Dim ux As Double Dim Dim Dim Dim

uy uz vx vy

As As As As

Double Double Double Double


Dim vz As Double Dim ReturnArray(3) Dim DoTranspose As Boolean ' Determine whether or not the selected ' output range is a row or a column array: If Application.Caller.Rows.Count > 1 Then DoTranspose = True Else DoTranspose = False End If ' Get the vector components: ux = u.Cells(1).Value uy = u.Cells(2).Value uz = u.Cells(3).Value vx = v.Cells(1).Value vy = v.Cells(2).Value vz = v.Cells(3).Value ' Compute the cross product: ReturnArray(0) = (uy * vz - uz * vy) ReturnArray(1) = (uz * vx - ux * vz) ReturnArray(2) = (ux * vy - uy * vx) ' If the selected output range is a column of cells then transpose the result: If DoTranspose Then v_CrossProduct = Application.WorksheetFunction.Transpose(ReturnArray) Else v_CrossProduct = ReturnArray End If End Function


5 ANALISIS ESTADISTICO CON EXCEL El poder obtener conclusiones de un grupo de datos brutos o no procesados puede ser muy importante al momento de tomar decisiones. Esto no solamente es válido si quieres aprobar el tema de estadística en la escuela o Universidad, sino que también en tu vida personal, entendiendo como procesar; el poder sacar alguna conclusión importante de los mismos, algunos de los más conocidos son la media, la varianza, el rango, etc. Teniendo esto en mente, he preparado un sencillo tutorial que permite obtener esta información fácilmente con las herramientas de datos que Excel proporciona. Para el mismo, solo necesitan tener instalado el office 2007 que creo es el más extendido, aunque en el 2003 las opciones son muy parecidas. Antes que nada, y debido a que estas herramientas de análisis estadístico no se instalan por defecto, debemos asegurarnos que las mismas estén disponibles. Esto es fácil si dentro del menuDatos, se encuentra el Submenú Análisis de Datos. Si aun no está, entonces debemos aprender a instalarlo, veamos como: Debemos identificar la barra de herramientas de acceso rápido, para eso ayudémonos de la siguiente imagen:

Al posicionarnos sobre la pestaña de la derecha de la imagen, obtenemos un cuadro desplegable como el mostrado:


Escojamos la opción Mas Comandos y luego, en la nueva ventana de diálogo a nuestra izquierda, la opción Complementos y podremos ver una vez hecho esto, ver la opción Herramientas de Análisis:


Una vez que hagamos clic sobre el botón Ir... podremos ver un nuevo cuadro de diálogo en donde deberemos habilitar las dos opciones relacionadas con el Análisis de Datos:

Con eso tendremos habilitada ya, la opción Análisis de Datos dentro del Menú Datos. Ahora que ya tenemos las herramientas habilitadas, necesitamos algunos datos cualquiera para poder procesarlos, pueden conseguir cualquiera de cualquier lugar o, podemos pedirle a Excel que nos regale unos cuantos números a azar para evitar la fatiga:

Al hacer clic en la opción Análisis de Datos disponible ya dentro de Datos/Análisis de Datos buscamos la opción Generación de números aleatorios:


Y después hacemos clic en Aceptar. Acá hay varias opciones. Para los que conocen algo de estadística las Opciones de distribución de Poisson, Normal, Discreta, etc. tienen sentido..pero por ahora, limitémonos a generar nuestros números aleatorios usando la distribución Uniforme:

Note que para el ejemplo, he escogido generar 60 números aleatorios con valores entre 12,120 y 16,300. Esto claro puede variar de acuerdo a sus gustos. El Rango de salida es la celda en donde se comenzarán a escribir estos valores aleatorios de manera vertical y, es completamente arbitraria también. Al hacer clic en el botón Aceptar, tendremos nuestros números aleatorios generados...podemos incluso imaginar que representan las ventas de un grupo de vendedores. Notara general con valoresescribimos decimales.la Podemos arreglar esto Sifácil en la columnaque de laExcel derecha de losvalores datos generados, formula REDONDEAR. por siejemplo la celda en donde se comenzaron a generar los números aleatorios es la B3 entonces en la celda B4 Elaborado por: Ing. Marcos González

bastara con escribir =REDONDEAR(B3;0) que significa redondear el numero de la celda B3 con 0 valores decimales. Hacer todo esto nos podría generar un cuadro similar al mostrado (ojo, son números aleatorios y en consecuencia no esperen tener los mismos valores)

Finalmente, podemos procesar los datos. Para eso de nuevo en el menú desplegable una vez pulsada la opción Análisis de Datos, buscamos la opción Estadística Descriptiva. Ahí podemos definir el rango de entrada, el rango de salida. No olvidemos también, habilitar la opción Resumen de Estadísticas. Una vez hecho esto, tendremos el resumen de datos. Algunos serán más conocidos que otros dependiendo de nuestra formación o experiencia. Entre los más comunes como mencione, están el total de datos, su suma, la media, mediana, moda y la desviación estándar. Acá los datos están procesados en columnas. Si dejas todo en una sola aparecerá un solo resumen en lugar de 6 como es el caso del ejemplo.


Si la variable de interés es de naturaleza cuantitativa contínua, como Salario anual “SalarioA” en la planilla Empleados, se puede tener interés en construir una tabla de frecuencias agrupada en clases. Por esta razón NO SE PUEDE UTILIZAR la tabla dinámica de Excel: como los valores de una variable contínua se repiten poco (o no se repiten), se tendría una tabla inmensa (probablemente con centenares de líneas en el caso de la planilla Empleados). A continuación se explicará cómo construir la tabla utilizando algunas funciones existentes en Excel, como MÁXIMO, MINIMO, CONTAR.SI, entre otras.

Tabla de frecuencias agrupada en clases: Pasos a seguir: Determinar el rango o amplitud del conjunto de datos Para obtener el rango, se tienen que identificar los extremos del conjunto de datos, o sea, sus valores máximo y mínimo. Se iniciará por el mínimo. Seleccione una celda donde desea que el resultado sea colocado: por ejemplo la celda L2. Seleccione esta celda con el cursor. Observe que en la barra de herramientas de Excel hay un botón llamado FUNCION.


Luego de seleccionar “Estadística”, basta buscar la función MIN: y observe la descripción en la

parte inferior. También se puede pedir ayuda a Excel sobre la descripción detallada de las funciones. Buscando detenidamente, se encontrarán otras funciones estadísticas muy útiles en el análisis de una variable cuantitativa continua, tal es el caso de: PROMEDIO (media aritmética), MEDIANA (mediana) VAR (varianza) y DESVEST (desviación estándar), PERCENTIL, etc. Una vez seleccionada la función, basta presionar ACEPTAR y para el caso de MIN se tendrá:

El mismo resultado podría ser obtenido simplemente digitando la fórmula directamente en la celda: =MIN(E2:E475) . Pueden ser utilizadas mayúsculas o minúsculas. Para encontrar el valor máximo se puede realizar un proceso análogo utilizando la función MAX, pero colocando el resultado en otra celda, L3 por ejemplo. Para calcular el intervalo se puede colar Elaborado por: Ing. Marcos González

una fórmula en la celda L4, haciendo la sustracción entre máximo y mínimo. Los resultados pueden observarse a continuación: El menor salario anual es Q 15,750.00 y el mayor de Q 135,000.00, resultando en una amplitud de Q 119,250. Esta amplitud es la se necesita para la construcción de la distribución en clases del conjunto de datos.

Dividir el rango en un número conveniente de clases Usualmente se define el número de clases (NC), utilizando la ecuación de Sturges, NC = 1+3.33 * log10 (n), como en nuestro caso n = 474 personas, tenemos que el número de clases es aproximadamente igual a 10. De acuerdo a esto, la amplitud (o ancho de clase) sería igual a 11,925. Establecer los límites de las clases Podemos definir valores diferentes para la amplitud de las clases y el valor inicial, para este último, debe considerarse que sea menor de 15,750 (mínimo) y la amplitud garantice que el valor máximo sea incluido en el conjunto de datos. Seleccionando una amplitud de 12,000 y un valor inicial de 15,000, teniendo en mente que la tabla tendrá 10 clases, el resultado será (los límites también pueden ser calculados a través de Excel):

Observe que los valores del conjunto (del mínimo al máximo) formarán parte de las clases, la ya que la última clase el límite superior también fue incluido. Podemos colocar los límites de cada clase en celdas de Excel, para posteriormente construir un gráfico.


Determinar las frecuencias de cada clase. Este es el paso más difícil, pero podemos resolverlo utilizando la función CONTAR.SI. Esta función cuenta cuántos valores en un determinado intervalo de datos atienden a un criterio establecido. El establecimiento del criterio no permite sin embargo, que sean incluidos dos límites, por ejemplo, contar todos los valores que son mayores que 15,000 y menores que 27,000. Además de eso, no es posible utilizar otras celdas de la planilla al definir el criterio. Para el caso en mención, necesitaremos insertar la función en un celda al lado de aquella donde se encuentran los límites de la primera clase.

Es necesario definir el rango de datos: los datos de interés están en las celdas E2 a E475 en la planilla Empleados. Y el criterio será <27000, o sea, el límite superior de la primera clase.

Si presiona ACEPTAR, Excel mostrará que hay 193 salarios inferiores a 27,000. Por otra parte, si quisiera arrastrar esta celda para abajo, para aplicarla a las otras celdas, tendría problemas.


Como el rango E2:E475 no es una referencia absoluta, al arrastrar la fórmula hacia abajo, el rango de los datos también se irá a mover: E3:ÑE476, E4:E477, y así sucesivamente, lo que los puede llevar a valores incorrectos. Para evitar eso, basta con marcar el rango en la Figura 24, y presionar la tecla F4 en el teclado del computar que estuviere usando: el intervalo será considerado como referencia absoluta, y al arrastrar las fórmulas, sus celdas permanecerán las mismas.

Este procedimiento (utilización de la tecla F4) puede ser realizado en cualquier situación en que sea necesario establecer una referencia absoluta. Presionando ACEPTAR, y arrastrando la fórmula hacia abajo:

Observe que el mismo valor fue repetido para todas las clases. Eso ocurrió porque el criterio (“<27000)

permaneció constante para todas. Se necesita cambiar manualmente los criterios para cada clase. Por ejemplo, en la celda al lado de la clase 27000 – 39000, el 27000 (que fue arrastrado de la celda anterior) debe ser sustituido por 39000. En la celda siguiente, debe ser sustituido por 51000, y así sucesivamente. Para hacer eso, basta colocar el cursor sobre la celda deseada y cambiar el valor del criterio, que está entre comillas. En la celda referente a la última clase, es necesario adicionar el símbolo = después el < del criterio (además de cambiar el valor para 135000), para garantizar la incorporación del valor máximo.


La tabla mostrada al lado registró las frecuencias ACUMULADAS hasta las respectivas clases. Esa información puede ser importante, pero estamos interesados en obtener las frecuencias individuales de cada clase. Nuevamente necesitamos modificar las fórmulas, sustrayendo de cada una, a partir de la segunda clase, las frecuencias de todas las clases anteriores. Por ejemplo para la clase 27000 | --39000, es necesario sustraer de su frecuencia (<366), las frecuencia anterior (193). Realizando las modificaciones, el resultado será:

Ahora si, las frecuencias están correctas. Podemos observar, como era esperado, una mayor frecuencia de salarios anuales más bajos, hasta Q 39,000. Esta tabla puede ser usada para construir un histograma. Seleccionando la tabla, escogiendo el gráfico de columnas, y reduciendo el espacio entre barras a cero, entre otros ajustes, se obtendrá un histograma.


Funciones de Excel para medidas de tendencia central Entre las funciones que se encuentran disponibles para obtener medidas de tendencia central están:

Funciones de Excel para medidas de dispersión Entre las funciones que se encuentran disponibles para obtener medidas de dispersión están:


En un levantamiento forestal fue medido el diámetro (expresado en centímetros) de algunos árboles de dos florestas, obteniéndose los siguientes valores:


Con esta información se le solicita: a) Calcular las medidas de tendencia, dispersión, asimetría y curtosis para cada conjunto de datos. b) Construir un histograma y un polígono de frecuencias para cada conjunto de datos. c) ¿Existen observaciones extremas en esos conjuntos de datos? (construya un box plot para los datos de cada floresta) d) Describa la forma de la distribución en las dos florestas. Solución: Es necesario recordar, que los datos referentes a cada variable (en este caso, a cada floresta) deben estar ubicados en una sola columna o una sola fila. Inicialmente se explicará cómo obtener las estadísticas descriptivas para la Floresta A, siguiendo los siguientes pasos:

a) En el menú DATOS, seleccione la opción ANÁLISIS DE DATOS

b) Luego se desplegará el siguiente la herramienta para ANÁLISIS DE DATOS, donde se deberá seleccionar la opción ESTADÍSTICA DESCRIPTIVA:


c) Luego de seleccionar la opción ESTADÍSTICA DESCRIPTIVA y presionar ACEPTAR se desplegará la siguiente pantalla:

En esta pantalla se debe marcar el rango de entrada de los datos e indicar si están colocados en una columna o en una fila, si decidió colocar alguna identificación y la incluyó dentro del rango de entrada, debe marcar el cuadro de Rótulos en la primera fila. En la parte referente a opciones de salida, debe indicar donde desea que Excel coloque los resultados del análisis de datos: Rango de Salida (si es dentro de la misma planilla donde tiene los datos), en una hoja nueva (otra planilla dentro del mismo archivo) o en un libro nuevo (archivo nuevo). Luego se seleccionará el cuadro RESUMEN DE ESTADÍSTICAS, y llegaremos a los siguientes resultados para la Floresta A:


d) Aplicando el anterior procedimiento para la floresta B, obtendremos los siguientes resultados:



Manual-de-Excel-Para-Ingenieros-Marcos-Listo.pdf

Recommend Documents