Manual de Excel Para Ingenieros Marcos Listo

ALTCA

CURSO DE EXCEL PARA INGENIEROS

Elaborado por: | Ing. Marcos González

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

2

3

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

EXPLORANDO EL ESTILO DE REFERENCIA F1C1: .............................................................................12

1.5

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

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

CREANDO UN GRAFICO SIMPLE..............................................................................................................30

2.2

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 es una poderosa herramienta para efectuar cálculos matemáticos donde se generan los resultados en forma automática, simulando así el trabajo en una matriz, donde los 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.

Hoja de Cálculo: 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.


Base de Datos

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. Gráficos

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. Macros

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.

Objetos Gráficos Por medio de la barra de herramientas Dibujo, 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 mapas de bits (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.


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. Problema:

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 2 3 4 5 6 7 8 9 10

PADILLA PEREZ ARIAS GONZALEZ VARGAS JATAR QUINTERO GUANIPA ALVAREZ NAVARRO

PEDRO FIDEL DAVID MARIA ALIDA ANGELICA JULIA OSCAR NESTOR JOHANNA

15/11/1978 16/04/1983 17/11/1996 29/10/1989 26/12/1978 18/05/1985 21/11/1967 02/02/1991 12/01/1969 04/04/1975

54765 34563 48634 28543 92745 37686 26597 82643 85646 12864

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.


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.


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.


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 OPERADORES: 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)


=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.


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.


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: 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. Excel cuenta con un lenguaje muy poderoso llamado Visual Basic, 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 Excel 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 Visual Basic con Excel 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 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

Producción 37.5131834 40.5870482 47.2848968 57.5667231 42.5779321 49.0158358 49.3924462 59.5610631 54.5339902 54.5664024 59.5279574 53.4962966 54.1583917 56.6794541 60.3466846 47.3722481 56.4048891 51.0720612 46.9764076 53.707521 52.0576408 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 Produccion MBBL/año

70 60 50 40 30 Producción

20 10 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, posee características particulares que le hacen de mayor utilidad en determinados conjuntos de datos. A continuación se describirán los 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.


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,

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

y cilindro

cilindros o pirámides.

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 del eje vertical (valores) de un gráfico. Sin embargo, puede personalizar la escala para

adaptarla a sus necesidades. Cuando los valores trazados en el gráfico cubren un rango muy 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.


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 para que resulten más fáciles de leer en el eje. Por ejemplo, puede mostrar valores del gráfico que abarquen desde 1.000.000 a 50.000.000 como de 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.


2.4 ESTABLECIENDO ESCALA SEMILOGARITMICA 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 cuatro primeros están amontonados encima del 0, de modo que no sería válido si queremos que aparezcan todos los valores. Probamos otra estrategia: calculamos los 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 del tiempo (t), podemos representar el logaritmo del caudal (eje vertical) en función del tiempo (eje horizontal).


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 48 107 48 155 53 208 44 252 40 292 48 340 58 398 43 441 42 483 50 533 51 584 56 640 59 699

800 700 600 500 400

Valores

300

Acumulado

200 100 0 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.


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 X1

X2

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.

 COS ( X 2  Y 2 )   * K Z  1  X 2 Y 2   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).


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 3

-3

2,5

2

1,5

1

0,5

0

-0,5

-1

-1,5

-2

-1

-2,5

-3

-1

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 -3

-2

-1

-1

0

-3 1

2

0-1

-1-0 -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

ESTE

grado de desnivel que presenta dicha superficie. Grafique los resultados:

900543 900438 900333 900228 900123 900018 899913 899808 899703 899598 899493 899388 899283

345697 50 40 37 46 35 36 38 40 44 42 43 36 37

338654 38 37 45 40 46 44 38 43 43 42 38 35 43

331611 48 39 43 36 33 48 44 38 43 41 40 37 35

324568 41 39 33 39 34 35 36 31 37 33 42 36 40

317525 39 43 43 35 49 35 42 34 44 33 43 38 35

310482 46 38 51 43 42 40 42 39 39 41 30 31 39

NORTE 303439 296396 46 41 42 48 46 32 46 51 42 42 42 38 43 38 40 39 39 48 47 44 43 33 44 35 45 38

289353 45 37 45 39 41 42 44 44 39 25 33 37 38

282310 39 44 41 40 40 32 39 45 41 35 42 43 35

275267 38 47 49 39 35 29 38 37 46 44 58 40 35

268224 52 41 42 44 42 41 47 44 38 43 37 38 41

261181 32 53 33 43 44 38 43 39 45 37 41 38 42

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 ESTE 900123 ESTE 900333

40-60 20-40 0-20

345697 338654 331611 324568 317525 310482 303439 296396 289353 282310 275267 268224 261181

ESTE 900543

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

Matriz2

A11

A12

A13

B11

B12

B13

A21

A22

A23

B21

B22

B23

La función sumaproducto, multiplica los componentes de las matrices y los suma como se muestra enseguida: SUMAPRODUCTO(matriz1,matriz2) calcula A 11*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.

SUMAPRODUCTO({2;3;5\1;2;2},{6;4\1;2}) da como resultado #¡VALOR! porque las dos matrices son de diferentes dimensiones.

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 triá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 (A11)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.


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

Matriz2

A11

A12

A13

B11

B12

B13

A21

A22

A23

B21

B22

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 - (B22)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 resultado 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

Matriz_y

A11

A12

A13

B11

B12

B13

A21

A22

A23

B21

B22

B23

SUMAXMENOSY2(matriz_x,matriz_y) se calcula (A 11-B11)2 + (A12-B12)2 + (A13- B13)2 + (A21-B21)2 + (A22-B22)2 + (A23-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”. Inmediatamente 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.


8

8

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.


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 RANGO


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


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:


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

Clásico

0

Clásico

1

Más conciso

2

Más conciso

3

Más conciso

4

Simplificado

VERDADERO

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.


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. Figura 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:


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 redondeo respectivas.

de

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

Función

1

PROMEDIO

2

CONTAR

3

CONTARA

4

MAX

5

MIN

6

PRODUCTO

7

DESVEST

8

DESVESTP

9

SUMA Elaborado por: Ing. Marcos González

10

VAR

11

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: Interés Efectivo

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 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_año) tiene valores exactos; por ejemplo: mensual (12), trimestral (4), semestral (2) o anual (1).

El resultado proporcionado por esta función lo obtenemos también con la siguiente fórmula:

43

i = 1

Ejemplo 1:

j m

m

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:

43

i = 1+

4

0.68 4

-1= 0.8739

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

Ejemplo 2:

núm_per_año 4

INT.EFECTIVO 0.8739

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 0.68

núm_per_año 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. Tasa nominal

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; por ejemplo: mensual (12), trimestral (4), semestral (2) o anual (1).

Si alguno de los argumentos es menor o igual a cero o si el argumento núm_per 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 nominal del pago de intereses vencidos. Para el interés anticipado debe calcularse la tasa nominal aplicando la fórmula (B):

B

ia =

ia 1+ iv

Funciones para el manejo de series uniformes

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 original.

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.


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%. Valor Final

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:

27

1+ i 

n

VF = C

-1

i

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

1+0.015 

36

VF = 350

0.015

-1

= UM 16,546.59

Sintaxis VF(tasa;nper;pago;va;tipo) TASA 0.015

NPER 36

PAGO -350

VA

TIPO

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 Excel, utilizadas en la obra, desde luego, cada con su propia persiana de argumentos de función.

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


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:

24

1+ i  - 1 n i  1+ i   n

VA = C

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  350

1.01536  1 0.015  1.01536

 UM 9,681.24

Sintaxis VA(tasa;nper;pago;vf;tipo) Tasa 0.015

Nper 36

Pago -350

VF

Tipo

VA 9,681.24

PAGO

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 encontrar la cantidad total pagada durante el período del préstamo, multiplique el valor devuelto por PAGO por el argumento nper.

El resultado proporcionado por esta función lo obtenemos también con la siguiente fórmula: Elaborado por: Ing. Marcos González

25

C = VA



i 1+ i 

n

1+ i 

n



-1

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

C = 10, 000



0.03 1+ 0.03 

24

1+ 0.03

24



-1

 UM 590.47

Sintaxis PAGO(tasa;nper;va;vf;tipo) TASA 0.03

NPER 24

VA -10,000

VF

TIPO

PAGO 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) TASA 0.0287

NPER 24

VA -50,000

VF 12%

TIPO

PAGO 3,088.32

TASA

Calcula la tasa del período


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) Nper 5

Pago -1,250.00

VA 5,000

VF

Tipo

Tasa 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.

Numero de períodos (NPER)

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:


23

VF VA n= , log 1+ i 

28

  VA   log 1-   i   1  C    n=  1  log    1  i  

log

26

  VA   log 1-  i   C  n= ,  1  log    1  i  

Por ejemplo:

i = 0.06; C = 14,000; VA = 93,345.50; n =? Sintaxis NPER(tasa; pago; va; vf; tipo) Tasa 0.06

Pago 14000

VA -93,345.50

VF

Tipo

n 8.7682

Funciones de Evaluación de proyectos

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


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 =

FC2 FC3 FC1 FC4 FCn + + + + -I0 (1+i) (1+i)2 (1+i)3 (1+i)4 (1+i)n

Por ejemplo:

Tenemos los siguientes flujos netos de un proyecto FLUJO DE CAJA 0 1 2 3 4 5 -50,000 16,000 14,000 17,000 15,000 18,000

AÑOS Flujos Netos

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 FLUJO

Tasa 0.15

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

VAN 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 ] -I0 +

FC3 FC1 FC2 FC4 FCn + + + + =0 2 3 4 (1+i) (1+i) (1+i) (1+i) (1+i)n

Por ejemplo:

Tenemos el siguiente flujo de caja de un proyecto: 0 -60,000

1 8,000

2 15,000

3 15,000

4 15,000

5 20,000

6 28,000


Aplicando la función calculamos la TIR del proyecto: Sintaxis TIR(valores;e stimar) 0 -60,000

1 2 3 4 5 6 TIR 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 -42,000

1 120,000

2 -80,000

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

1 120,000

2 -80,000

TIR 0.0597

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

1 120,000

2 -80,000

TIR 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.


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.

Tablas de amortización

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


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

INTERÉS

Saldo final del período anterior

interés

AM ORTIZACIÓN

PAGO

SALDO FINAL

Saldo inicial P ago menos

Definida a

Saldo inicial

por tasa de

voluntad

menos

interés

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:


Celda E4

10 [Ingresamos a la celda sin poner el signo (=)]

Celda E5

=E4

Celda E7

=E6

Celda E6

=E5*2 (de acuerdo a la condición del problema).

Celda E8

=E7*2

Celda E9

=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 MES

1 2 3 4 5 6 7 8

B

C

D

SALDO INTERÉS AMORTZ INICIAL

E

F

PAGO

SALDO FINAL 10,000.00

0 1

10,000.00

450.00

413.28

863.28

9,586.72

2

9,586.72

431.40

431.87

863.28

9,154.85

3 4

9,154.85 7,840.26

411.97 352.81

1,314.59 1,373.74

1,726.55 1,726.55

7,840.26 6,466.52

5

6,466.52

290.99

3,162.11

3,453.11

3,304.41

6

3,304.41

148.70

3,304.41

3,453.11

0.00

INTERES

= SALDO INICIAL x 0.045

AMORTIZACION

= PAGO - INTERES

PAGO

= BUSCAR OBJETIVO

(=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

INTERÉS

AM ORTIZACIÓN

Saldo final

Saldo inicial Definida a

Amortización Saldo inicial

del período

por tasa de

más interés

anterior

interés

voluntad

PAGO

SALDO FINAL

menos amortización

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

1 2 3 4 5 6 7 8

B

C

D


E

F

PAGO


0 1

10,000.00

450.00

1,666.67

2,116.67

8,333.33

2

8,333.33

375.00

1,666.67

2,041.67

6,666.67

3 4

6,666.67 5,000.00

300.00 225.00

1,666.67 1,666.67

1,966.67 1,891.67

5,000.00 3,333.33

5

3,333.33

150.00

1,666.67

1,816.67

1,666.67

6

1,666.67

75.00

1,666.67

1,741.67

0.00

INTERES

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

AMORTIZACION

= 10,000/6 = 1,666.67

PAGO

= 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

 UM 1,938.78

Sintaxis PAGO(tasa;nper;va;vf;tipo) Tasa 0.045

Nper 6

VA -10,000

VF

Tipo

PAGO 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 MES

B C D SALDO INTERÉS AMORTZ INICIAL

E PAGO

F SALDO FINAL 10,000.00

1 2

0

3

1

10,000.00

450.00

1,488.78

1,938.78

8,511.22

4

2

8,511.22

383.00

1,555.78

1,938.78

6,955.44

5 6

3 4

6,955.44 5,329.65

312.99 239.83

1,625.79 1,698.95

1,938.78 1,938.78

5,329.65 3,630.70

7

5

3,630.70

163.38

1,775.40

1,938.78

1,855.30

8

6

1,855.30

83.49

1,855.30

1,938.78

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 =?


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

10

Celda E4

Celda E6

=E3+30

Celda E5

Celda E7

=E4+30

En buscar Objetivo: Definir la celda con el valor

=E6+30

Celda E8

=E5+30

=E7+30

: Con el mouse hacemos clic en la celda F8 :0

para cambiar la celda : Con el mouse hacemos clic en la celda E3 Aplicando este procedimiento obtenemos la siguiente tabla: A MES

1 2 3 4 5 6 7 8

B

C

D

E

F


PAGO


1

5,000.00

190.00

685.87

875.87

4,314.13

2

4,314.13

163.94

741.93

905.87

3,572.20

3 4

3,572.20 2,772.07

135.74 105.34

800.13 860.53

935.87 965.87

2,772.07 1,911.54

5

1,911.54

72.64

923.23

995.87

988.31

6

988.31

37.56

988.31

1,025.87

0.00

0

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


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. Sintaxis

DIAS.LAB(fecha_inicial;fecha_final;festivos)

Importante. 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. Observaciones

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 inicial Fecha final 2003-05-15

2003-07-28

Festivos

DIAS 53

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

Calcular el número de meses entre dos fechas

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). Sintaxis

MES(núm_de_serie)

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. Observaciones

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.

Calcular el número de años entre dos fechas 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. Sintaxis

AÑO(núm_de_serie)

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.


Observaciones

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 muy pocas funciones, la más usada es la función SI, las demás pueden ser empleadas como parte de esta función.


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 El nivel mínimo de PPM en el ambiente debe ser de 200. 212 310 251 Solución: 368 Hacer click en insertar funciones y seleccionar la función si. 225 174 130 13 373 377


Luego establecer los parámetros:

PPM H2S 347 377 41 212 310 251 368 225 174 130 13 373 377

EFECTO SI CONTAMINACION SI CONTAMINACION NO CONTAMINACION SI CONTAMINACION SI CONTAMINACION SI CONTAMINACION SI CONTAMINACION SI CONTAMINACION NO CONTAMINACION NO CONTAMINACION NO CONTAMINACION SI CONTAMINACION 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 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. Excel cuenta con un lenguaje muy poderoso llamado Visual Basic, este es solo una parte del lenguaje, pero permite hacer o resolver los problemas más


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 Excel 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 Visual Basic con Excel es necesario tener mucha creatividad, cada persona puede crear estructuras diferentes pero que trabajen igual. Así que manos a la obra. Fase 1 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 A1 y escribe tu Nombre. Por ejemplo, Ramón y presiona Enter

2. Regrésate a la celda A1, porque cuando diste Enter bajo de celda o cambio el rumbo.

3. Da clic en el Menú Ver, seguido por la Opción Barra de Herramientas y elija Visual Basic. Se activara la barra de herramientas Visual Basic.


4. Da clic en el botón Guardar Macro, el que tiene la ruedita Azul. Windows activa el cuadro de dialogo Grabar Macro, 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 Nombre de la macro ya aparece el nombre que llevara la macro en

este caso Macro1. si desea cambiar el nombre escriba uno nuevo, pero yo le recomiendo que así lo deje.

6. En la opción Método Abreviado aparece que se activara con la tecla Control(CTRL) + la letra que usted indica, de clic en el cuadrito y ponga una letra, por ejemplo ponga la letra

a(en

minúsculas). La macro se activara cuando este

lista con la tecla Control + a

7. De clic en el Botón Aceptar. Windows empezara a grabar todos los pasos en la Macro1.y el botón de la ruedita azul cambiara de forma ahora será un cuadrito

Azul, se llamara Detener grabación. Lo utilizaremos cuando terminemos de indicarle los pasos para detener la grabación.

8. Cambie el Tipo de Letra en el Botón Fuente de la barra de herramientas Formato


9. Cambie el tamaño de la letra en el Botón Tamaño de Fuente de la barra de herramientas Formato

10. Presione el Botón Negrita de la barra de herramientas Formato

11. Cambie el color de la letra en el Botón Color de Fuente 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 A1.

12. Presione el Botón Detener Grabación de la barra de Herramientas de Visual Basic. El que tiene el cuadrito azul presionado.

Listo Excel guardo los pasos en la Macro1 que se activara presionado la tecla Control + a 13. Escribe otro nombre en la celda C1 y presiona Enter, después regresa a la celda C1.

14. Presiona la tecla Control + a. Windows efectuara todos los pasos grabados sobre la celda C1, esto quiere decir que el nombre que esta en C1 tendrá las características del que esta en A1. Tipo de letra, tamaño, negrita y el color que indicaste al grabar la macro.

Nota. Cada vez que presiones Control + a 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 Macro que se active con Control + b y que esta macro permita abrir un archivo

Graba una Macro que se active con Control + c 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 Excel con los de Visual Basic. Esto será pura Dinamita.

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

OBSERVANDO LOS CODIGOS DE UNA MACRO DE EXCEL 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 A5 antes de empezar la grabación de la Macro

2. Presione el Botón Grabar Macro de la barra de Herramientas Visual Basic. Excel muestra el cuadro de Dialogo Grabar Macro

3. en la opción Método Abreviado escriba la letra r, por lo tanto la macro se llamara con Control + r

4. Presione el botón Aceptar. Excel inicia la grabación del la Macro1

5. Trasládese a la celda A1 y escriba Ramón, después presione Enter para aceptar el valor en la celda

6. Pare la grabación de la macro presionando el botón Detener Grabación de la barra de herramientas Visual Basic. Excel a grabado los pasos y a generado un código, Observémoslos:


7. Presione la tecla Alt + la tecla de función F11(Alt + 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 Menú Herramientas, Macro y Editor de Visual Basic.

8. Active los siguientes cuadros o ventanas: 

De clic en el Menú Ver y elija la opción Explorador de Proyectos



De clic en el Menú ver y elija la opción Ventana Propiedades

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

9. Del cuadro Proyecto de doble clic en Módulos o simplemente presione el signo de

+ que aparece en la opción

Módulos. Se activara debajo de Módulos la Opción

Modulo1

10. De doble clic en Modulo1. 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 Excel:  

Sub y End Sub indican el inicio y el final del procedimiento de la Macro1

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.



Range("A1").Select Indica que lo primero que hicimos al grabar la macro fue trasladarnos a la celda A1. La orden Range nos permite trasladarnos a una celda



ActiveCell.FormulaR1C1 = "Ramón" Esto indica que se escribirá en la celda en que

se encuentra el valor de texto Ramón. Todo lo que aparece entre comillas siempre será un valor de texto. La orden ActiveCell.FormulaR1C1 nos permite escribir un valor en la celda activa. 

Range("A2").Select Otra vez indicamos que se traslade a la celda A2. Esto se debe a que cuando escribimos el nombre de Ramón en A1 presionamos Enter y al dar Enter bajo a la celda A2.

Para comprender mejor alteraremos el código dentro del editor de Visual Basic. Que crees que pasara aquí con nuestra Macro:


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 Excel y ejecute la macro con Control + r hará lo siguiente:

En A1 escribirá Ramón

En B1 escribirá Calle 21 de Marzo #280 En C1 escribirá 31-2-47-13

En D1 escribirá Nogales Sonora

En E1 escribirá CONALEP NOGALES Así que salgamos del editor dando clic en el Menú Archivo y eligiendo la opción Cerrar y volver a Microsoft Excel. Si no desea salir por completo de clic en el botón Microsoft Excel que se encuentra activado en la barra de tareas y cuando deseé volver al editor de clic en el botón Microsoft Visual Basic que se encuentra en la barra de Tareas. Elaborado por: Ing. Marcos González

Ahora ya que salimos de Visual Basic y estamos en Excel de Nuevo ejecutemos la macro presionando Control + r 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 Fase 2 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 Macro que escriba un nombre en una celda y lo ponga negrita y observa el Código. Genera una Macro que escriba un nombre en una celda y lo Centre y observa el Código.

Genera una Macro que escriba un nombre en una celda y cambie el tamaño de la letra a 20 puntos y observa el Código .

Códigos Más comunes: Trasladarse a una Celda Range("A1").Select

Escribir en una Celda

Activecell.FormulaR1C1="Ramon" Letra Negrita

Selection.Font.Bold = True Letra Cursiva

Selection.Font.Italic = True Letra Subrayada

Selection.Font.Underline = xlUnderlineStyleSingle Centrar Texto

With Selection

.HorizontalAlignment = xlCenter

End With

Alinear a la izquierda With Selection

.HorizontalAlignment = xlLeft

End With Elaborado por: Ing. Marcos González

Alinear a la Derecha With Selection

.HorizontalAlignment = xlRight

End With

Tipo de Letra(Fuente) With Selection.Font

.Name = "AGaramond"

End With

Tamaño de Letra(Tamaño de Fuente) With Selection.Font .Size = 15

End With

Copiar

Selection.Copy Pegar

ActiveSheet.Paste Cortar

Selection.Cut

Ordenar Ascendente

Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Orden Descendente

Selection.Sort Key1:=Range("A1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Buscar

Cells.Find(What:="Ramon", After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate Insertar Fila

Selection.EntireRow.Insert Eliminar Fila

Selection.EntireRow.Delete Insertar Columna Elaborado por: Ing. Marcos González

Selection.EntireColumn.Insert Eliminar Columna

Selection.EntireColumn.Delete Abrir un Libro

Workbooks.Open Filename:="C:\Mis documentos\video safe 3.xls" Grabar un Libro

ActiveWorkbook.SaveAs Filename:="C:\Mis documentos\piscis.xls", FileFormat _ :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _ False, CreateBackup:=False

Estos serian algunos códigos muy comunes en Excel, pero si usted desea puede generar mas códigos de otras opciones, es cuestión de que los ocupe.

Antes de pasar a la Fase 3 es necesario que domines generar Macros y Observar sus códigos que se encuentran en la Fase 2. Recuerda esto es de mucha práctica y para eso es necesario aprender bien las fases Anteriores. .

Fase 3 CREANDO FORMULARIOS Y PROGRAMÁNDOLOS

Antes de Empezar esta nueva fase te recomiendo que salgas de Excel 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:


1. Presione La Teclas Alt + F11, para entrar al editor de Visual Basic. 2. Activa las siguientes opciones:  

De clic en el Menú Ver y elija la opción Explorador de Proyectos De clic en el Menú ver y elija la opción Ventana Propiedades

3. Del Menú Insertar elija la Opción UserForm. Esto inserta el Formulario que programaremos con controles. En el Explorador de Proyecto se observara que se inserto el UserForm.

También cuando de clic en el Formulario USERFORM1 se debe de activar el Cuadro de Herramientas, si no se activa de clic en el Menú Ver y elija la opción Cuadro de Herramientas.

4. Elija del Cuadro de Herramientas el Control Etiqueta el que tiene la

A

y Arrastre

dibujando en el Formulario USERFORM1 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í Nombre. 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 UserForm1 que se encuentra en el Explorador de Proyecto.

5. Elija del Cuadro de Herramientas el control Cuadro de Texto el que tiene

ab y

arrastre dibujando en el formulario USERFORM1 el cuadro de texto a un lado de la etiqueta que dice Nombre. El cuadro de texto debe de estar vacío y su nombre será Textbox1, el nombre solo aparecerá en el control.


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

Si tiene algún problema al dibujar las etiquetas o los cuadros de texto, solo cámbiele el

nombre a la etiqueta o el cuadro de texto en la Ventana Propiedades la opción se llama (Name). El Error que marque puede ser Nombre Ambiguo, pero si le cambias el Nombre al control se quitara el error. Puedes ponerle cualquier nombre en lugar de Label1.

Solo altera esto si te marca error, si NO déjalo así.


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 Cuadro de Herramientas el control Botón de Comando y Arrastre dibujando en el Formulario USERFORM1 el Botón, después de un clic en el

nombre del Botón dibujado y podrá modificar el nombre y pondremos ahí Insertar. 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 UserForm1 que se encuentra en el Explorador de Proyecto.

Así quedara el Formulario formado por los controles:


8. Ahora de doble clic sobre el control Textbox1 para programarlo y después inserte el siguiente código: Private Sub TextBox1_Change() Range("A9").Select

ActiveCell.FormulaR1C1 = TextBox1 End Sub

Esto indica que se valla a A9 y escriba lo que hay en el Textbox1

Nota.-Lo que esta en azul lo genera Excel automáticamente, usted solo escribirá lo que esta en Negrita.

Para volver al Formulario y programar el siguiente Textbox de doble clic en UserForm1 que se encuentra en el Explorador de Proyecto, o simplemente de clic en Ver Objeto en el mismo Explorador de Proyecto.

9. Ahora de doble clic sobre el control Textbox2 para programarlo y después inserte el siguiente código: Private Sub TextBox2_Change() Range("B9").Select


Esto indica que se valla a B9 y escriba lo que hay en el Textbox2 Para volver al Formulario y programar el siguiente Textbox de doble clic en UserForm1 que se encuentra en el Explorador de Proyecto, o simplemente de clic en Ver Objeto en el mismo Explorador de Proyecto.

10. Ahora de doble clic sobre el control Textbox3 para programarlo y después inserte el siguiente código:


Private Sub TextBox3_Change() Range("C9").Select


Esto indica que se valla a C9 y escriba lo que hay en el Textbox3 Para volver al Formulario y programar el Botón de Comando Insertar de doble clic en

UserForm1 que se encuentra en el Explorador de Proyecto, o simplemente de clic en Ver Objeto en el mismo Explorador de Proyecto.

11. Ahora de doble clic sobre el control Botón de Comando para programarlo y después inserte el siguiente código: Private Sub CommandButton1_Click() Rem inserta un renglón

Selection.EntireRow.Insert

Rem Empty Limpia Los Textbox TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty

Rem Textbox1.SetFocus Envía el cursor al Textbox1 para volver a capturar los datos TextBox1.SetFocus End Sub

Nota.-El

comando Rem es empleado para poner comentarios dentro de la

programación, el comando Empty es empleado para vaciar los Textbox. 12. Ahora presione el botón Ejecutar User/Form que se encuentra en la barra de herramientas o simplemente la tecla de función F5


Se activara el Userform1 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 Textbox1.

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 Formulas en Macros de Excel, 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 Fase se muestra como manejar Formulas en Macros de Excel. Fase I 4. Presione La Teclas Alt + F11, para entrar al editor de Visual Basic. 5. Activa las siguientes opciones: 

De clic en el Menú Ver y elija la opción Explorador de Proyectos



De clic en el Menú ver y elija la opción Ventana Propiedades


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



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 Textbox3 y se escribirá también en Excel. El comando Val es un comando de Visual Basic 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 Macro se llama Macros de Edad 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


Private Sub TextBox2_Change() Range("B9").Select

ActiveCell.FormulaR1C1 = TextBox2 End Sub Private Sub TextBox3_Change() Range("C9").Select



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


Cuando se introduzca el Bonos automáticamente se generara el Sueldo Neto. Este ejemplo viene en el Archivo Macros de Sueldo Neto

BUSANDO INFORMACIÓN CON UN TEXTBOX Se puede buscar información con un Textbox programándolo de la siguiente forma:

Dibuje una Etiqueta, un Textbox y un Botón de Comando y agregue el siguiente Código:

Private Sub TextBox1_Change() Range("a9").Select



Private Sub CommandButton1_Click() Cells.Find(What:=TextBox1, After:=ActiveCell, LookIn:=xlFormulas, LookAt _

:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate

End Sub

Si te fijas incluí en la programación del Botón Buscar Ahora que buscara lo que en el Textbox1 a la hora de Presionarse.

Este proceso viene en el Archivo Completo Macro2 ELABORANDO UNA CONSULTA Todo Registro de información debe de tener su propia Consulta, Baja y Modificación, es por eso que en este nuevo capitulo nos concentramos en ello, primeramente en poder

consultar la información que ya se escribió en la Hoja de Excel, obviamente desde una Macro combinada con Visual Basic, observemos el siguiente ejemplo: Fase I 7. Presione La Teclas Alt + F11, para entrar al editor de Visual Basic. 8. Activa las siguientes opciones:  

De clic en el Menú Ver y elija la opción Explorador de Proyectos De clic en el Menú ver y elija la opción Ventana Propiedades


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 Nombre, Dirección y Teléfono. Los tres botones nos servirán para lo siguiente: Consultar consultara la información que hayamos insertado desde el botón insertar. Baja podrá eliminar algún dato que se consulto y no lo queremos. Insertar 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: BOTON DE CONSULTA Private Sub CommandButton1_Click() Cells.Find(What:=TextBox1, After:=ActiveCell, LookIn:=xlFormulas, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False).Activate

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


Rem la línea que contiene el ActiveCell.Offset(0, 1).Select permite moverse una columna a la derecha, por lo tanto después de la búsqueda de las primeras líneas con Cell.Find si

encuentra el Nombre de la persona se mueve a la siguiente columna y la línea TextBox2 = ActiveCell Permite capturar el valor de la celda al Textbox2 y así mostrar el dato de la celda en el TextBox2. ActiveCell.Offset(0, 1).Select TextBox3 = ActiveCell Rem Cada vez que se escriba la línea ActiveCell.Offset(0, 1).Select significa que se tiene que moverse una columna a la derecha. 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()

BOTON BAJA

Selection.EntireRow.Delete Range("A9").Select

TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty



BOTON INSERTAR Private Sub CommandButton3_Click() Range("A9").Select

Selection.EntireRow.Insert TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty


CUADROS DE TEXTO 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


Private Sub TextBox2_Change() Range("B9").FormulaR1C1 = TextBox2 End Sub

Private Sub TextBox3_Change()

Range("C9").FormulaR1C1 = TextBox3 End Sub


Si con el Botón Consulta tienes un error cuando no encuentra a la persona, entonces tendrás que agregar esto a tu código del Botón Consultar BOTON DE CONSULTA Private Sub CommandButton1_Click() On Error Goto noencontro Rem esta línea genera una trampa de error si Excel encuentra un error se le dice que se vaya a la etiqueta noencontro 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 noencontro.y esquivara cualquier error, hasta uno que usted cometa en la programación. Cells.Find(What:=TextBox1, 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. ActiveCell.Offset(0, 1).Select Direccion = Activecell ActiveCell.Offset(0, 1).Select Telefono = Activecell Elaborado por: Ing. Marcos González

TextBox2 = Direccion TextBox3 = Telefono

noencontro:

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 Excel sin necesidad de entrar al Editor de Visual Basic. Para realizar este ejercicio debemos permanecer dentro del Editor de Visual Basic para poder introducir el código en un Modulo, por lo tanto deberás seguir los siguientes pasos:  De clic en el Menú Insertar y elija la opción Modulo  Escriba dentro del Modulo el nombre del modulo en este caso Sub Entrada 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: Load UserForm1

UserForm1.Show La primer línea significa que cargue a la memoria el formulario que se llama UserForm1, 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 Editor de Visual Basic.

Si te fija en el explorador de proyecto aparece el Modulo que creamos.

si queremos volver al formulario solo da doble clic en UserForm1

Bueno ya esta listo ahora salgamos del Editor de Visual Basic y volvamos a Excel.  De clic en el Menú Archivo del Editor de Visual Basic  Elija la opción Cerrar y volver a Microsoft Excel

Ya que estamos en Excel, podemos insertar una imagen o un botón o cualquier grafico, por ejemplo: Elaborado por: Ing. Marcos González

 De clic en el Menú Insertar  Elija la opción Imagen, seguido por Imagen Prediseñada

 inserte cualquier imagen y dele el tamaño que usted desea.  De clic derecho sobre la Imagen  Elija la opción Asignar Macro

 De clic en la Macro que se llama Entrada, es obvio la única que hicimos  De Clic en Aceptar

 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 Macros o No. Este ejemplo viene en el archivo Macros3 junto con esta guía.

Bueno empezaremos con como agregar información a un Combobox y un Listbox,

primeramente deberás crear el siguiente formulario dentro de Visual Basic, recuerda desde Excel se utiliza la tecla ALT + F11 para entrar a Visual Basic, seguido del Menú Insertar y después Userform, bueno creo que ya lo sabes. Inserta Un Combobox y un Listbox y un Botón.

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 Botón y escribe las siguientes

líneas

procedimiento.

dentro

del


Private Sub CommandButton1_Click() ComboBox1.AddItem "Juan Jose"

ComboBox1.AddItem "Pedro de la Fuente" ComboBox1.AddItem "Salvador de la Luz" ListBox1.AddItem "Juan José"

ListBox1.AddItem "Pedro de la Fuente" ListBox1.AddItem "Salvador de la Luz" End Sub

Bueno vamos a analizar el significado de estas líneas: ComboBox1.AddItem "Juan José " La opción AddItem significa que vas a agregar un dato de texto, por lo tanto se entiende como vas a agregar a Juan José al Combobox1, por lo tanto yo puedo agregar los datos

que quiera a un Combobox o un Listbox 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() For X=1 to 50

Listbox1.AddItem str(x)

Next End Sub La Instrucción For-Next 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 For-Next 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 Str es para convertir el valor numérico de

la X en valor de Texto, ya que la opción AddItem 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. Listbox1.AddItem “1”

Listbox1.AddItem “2” Listbox1.AddItem “3” Ya te quiero ver en el código para que llegues al 50. Bueno esto es para introducirle datos a un Listbox y Combobox, pero como puedo usar estos datos para enviarlos para una celda, en el siguiente ejemplo te lo explico: Da doble clic en el Listbox y escribe el siguiente código: Private Sub ListBox1_Click() Range("a9").Select

ActiveCell.FormulaR1C1 = ListBox End Sub

Así de de fácil cada vez que escojas un dato que se encuentre en un Listbox1 lo enviara a la celda a9, escribiéndolo ahí. Si lo deseas hacer lo puedes hacer en un Combobox, solo cambia Listbox1 por Combobox1 y se acabo.

Ahora si deseas agregar los datos al Listbox o Combobox sin ningún botón que presionar escribe el siguiente código:

Private Sub UserForm_Activate() ComboBox1.AddItem "Juan Jose" Elaborado por: Ing. Marcos González

ComboBox1.AddItem "Pedro de la Fuente" ComboBox1.AddItem "Salvador de la Luz" ListBox1.AddItem "Juan José" ListBox1.AddItem "Pedro de la Fuente" ListBox1.AddItem "Salvador de la Luz End Sub

La Clave esta en el procedimiento UserForm_Activate() esto quiere decir que cuando se active el formulario cargara lo que tu le indiques, en este caso va a introducir los datos al Listbox1 y Combobox1 automáticamente, que te parece.

Ahora si deseas tomar información de una celda y enviarla a un Combobox o Listbox escribe el siguiente código en un Botón: Private Sub CommandButton1_Click() Range("a9").Select Do While ActiveCell <> Empty ActiveCell.Offset(1, 0).Select

ListBox1.AddItem ActiveCell

Loop

End Sub


Fíjate bien, primeramente muevo el rango a la celda a9 porque ahí esta el inicio de mi información, después la línea Do While Activecell<> Empty significa Hazlo mientras la

celda no se encuentre vacía, la siguiente línea que es ActiveCell.Offset(1, 0).Select,

significa Baja un Renglón, la siguiente línea ListBox1.AddItem ActiveCell, agrega la información de la celda al Listbox1 y la línea Loop es parte del ciclo Do While, siempre cierra el ciclo, como el For-Next. Por lo tanto todos los nombres que estén delante de a9 serán enviados al Listbox1 y cuando tope con la celda a15 que se encuentra vacía la condición del Do While 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 ActiveCell.Offset(1, 0).Select por ActiveCell.Offset(0, 1).Select, quiere decir que se mueva por columna, no por renglón. ActiveCell.Offset(Renglón, Columna).Select

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: ActiveCell.Offset(10, 0).Select Si quiero moverme 20 columnas a la derecha ActiveCell.Offset(0, 20).Select Así funciona esto. Ahora veremos como se ejecuta una macro a la hora de abrir un libro Primeramente inserta un Modulo del Menú Insertar dentro de Visual Basic y escribe el siguiente código:

Sub Auto_open() Load UserForm1

UserForm1.Show End Sub


La magia esta en el procedimiento Auto_open() 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 A10 y terminan en C16, 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 CommandButton1_Click() Rem este código localiza el ultimo registro por medio del renglón Range("a10").Select Do While ActiveCell <> Empty

ActiveCell.Offset(1, 0).Select

Loop


Rem llega hasta el a17 donde no hay información y se regresa un renglón para ser exacto con la siguiente línea. ActiveCell.Offset(-1, 0).Select Rem este código localiza la última columna del último dato Do While ActiveCell <> Empty

ActiveCell.Offset(0, 1).Select

Loop

ActiveCell.Offset(0, -1).Select Rem esta línea guarda en la variable celdaactiva la celda exacta donde esta el ultimo dato de la ultima columna de información, en este caso C16. celdaactiva = ActiveCell.Address Rem este código toma el rango desde A10 donde empieza la información, hasta donde encontró el ultimo dato C16, que lo guarda la variable celdaactiva. Selecciona de A10 hasta C16.

Range("A10:" + celdaactiva).Select

Rem este código ordena los datos en orden ascendente, el código fue generado en Excel, así que si no sabes generarlo solo copialo de aquí.

Selection.Sort Key1:=Range("A10"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

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 CommandButton1_Click() Range("a10").Select Do While ActiveCell <> Empty ActiveCell.FormulaR1C1 = LCase(ActiveCell) ActiveCell.Offset(1, 0).Select Loop End Sub Así es la magia esta en Lcase que convierte a Minúsculas y Ucase 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ó.

Etiqueta 4, 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() Rem si no se escribe nada en los Textboxs a la hora de insertar escribe No Tiene 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

Rem Código para buscar, ya lo conocemos 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

Rem la etiqueta 4 toma el valor del renglón activo y permite modificar la información que encontró, ya que modifiques la información presionas el botón actualizar. Label4 = ActiveCell.Row noencontro:

End Sub Private Sub CommandButton3_Click() Rem Vuelve a indicar el renglón 9 para escribir en los Textboxs Label4 = "9"

Range("a9").Select TextBox1 = Empty TextBox2 = Empty TextBox3 = Empty TextBox1.SetFocus End Sub

Private Sub TextBox1_Change()

Rem si nos damos cuenta la etiqueta 4 sirve para llevar el renglón donde introducimos los datos o los modificamos, asi que cada textbox que programemos debe llevar estas lineas. 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 A se encuentran los valores ejes (X) y en la columna B los valores series (Y), estos datos son necesario para efectuar una grafica que podría quedar así

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


Sub Macro1() 1. Range("A5:B10").Select 2. Charts.Add

3. ActiveChart.ChartType = xlColumnClustered

4. ActiveChart.SetSourceData Source:=Sheets("Hoja1").Range("A5:B10"), PlotBy:= _xlColumns 5. ActiveChart.Location Where:=xlLocationAsObject, Name:="Hoja1"

End Sub 1. La primer línea indica el rango donde están los datos, valores ejes y valores series, 2. La segunda línea indica que se agrega una grafica

3. La tercera línea indica el tipo de grafica que se desea

4. La cuarta línea indica como se acomodan los datos en la grafica 5. La quinta línea indica donde se muestra la grafica, si en la misma hoja o en una sola hoja.

Nota. 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 Línea 3:

ActiveChart.ChartType = xlColumnClustered


ActiveChart.ChartType = xlBarClustered

ActiveChart.ChartType = xlLineMarkers

ActiveChart.ChartType = xlPie


ActiveChart.ChartType = xlXYScatter

ActiveChart.ChartType = xlAreaStacked

ActiveChart.ChartType = xlDoughnut


ActiveChart.ChartType = xlRadarMarkers

ActiveChart.ChartType = xlCylinderColClustered

ActiveChart.ChartType = xlConeColClustered


ActiveChart.ChartType = xlPyramidColClustered

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 Macro1() Range("A5:B10").Select Charts.Add


ActiveChart.SetSourceData Source:=Sheets("Hoja1").Range("A5:B10"), PlotBy:= _xlColumns ActiveChart.Location Where:=xlLocationAsObject, Name:="Hoja1"

ActiveChart.ChartType = xlPyramidColClustered. End Sub Este código se puede programar en un botón o cualquier otro control de Visual Basic.

A continuación se muestra como se acomodan los datos Línea 4:


ActiveChart.SetSourceData Source:=Sheets("Hoja1").Range("A5:B10"), PlotBy:= _ xlRows

En esta línea se muestra la grafica por Renglón

ActiveChart.SetSourceData Source:=Sheets("Hoja1").Range("A5:B10"), PlotBy:= _ xlColumns En esta línea se muestra la grafica por Columna Esta es la forma en que se muestran los datos de lo que habla la línea 4. La línea 5 habla de que si la grafica queda en la misma hoja o simplemente toma una hoja para ella, por ejemplo:


ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Grafico 1" Esta línea indica que la grafica tenga su propia hoja y que su nombre sea Grafico 1. En este ejemplo ejecuto un código con cada una de las características explicadas en las 5 líneas.

Range("A5:B10").Select Charts.Add


ActiveChart.SetSourceData Source:=Sheets("Hoja1").Range("A5:B10"), PlotBy:= _xlColumns ActiveChart.Location Where:=xlLocationAsObject, Name:="Hoja1"

ActiveChart.ChartType = xlPyramidColClustered

ActiveChart.SetSourceData Source:=Sheets("Hoja1").Range("A5:B10"), PlotBy:= xlColumns

ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Grafico 1" 3. Tipo de Grafico 4. Como se acomodan los datos

5. Como se muestra la grafica, en este caso en una sola hoja


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:

Dibuja dos Listbox y un Botón y pega el código dentro del formulario.

Private Sub CommandButton1_Click() Rem este código genera la Grafica en la hoja1 Range("A5:B10").Select Charts.Add


ActiveChart.SetSourceData Source:=Sheets("Hoja1").Range("A5:B10"), PlotBy:= _ xlColumns

ActiveChart.Location Where:=xlLocationAsObject, Name:="Hoja1"

Rem agrega los diferentes tipos de grafica al Listbox1 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" Rem agrega las diferentes formas de acomodar los datos al Listbox2 ListBox2.AddItem "Renglon"

ListBox2.AddItem "Columna"

End Sub Private Sub ListBox1_Click() Rem este código da el tipo de grafica al dar clic en el Listbox1 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


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 Macro llenas los datos anteriores en la hoja1 de Excel

En este archivo indexo un ejemplo de un libro de cómo se puede generar una Macro 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 archivos secuénciales en Visual Basic. Los archivos secuénciales 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 archivos Elaborado por: Ing. Marcos González

aleatorios, 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 archivos secuénciales, 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 Visual Basic con Alt+F11 y Insertaremos un UserForm, en el cual dibujaremos dos botones, uno con el nombre de Registro y Otro con el Nombre de Consulta.

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 Macros VI.

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 Macros VI-2. 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


Este ejemplo viene en el archivo Macros VI-3 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.


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 uy As Double Dim uz As Double Dim vx As Double Dim vy As 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 que Excel general valores con valores decimales. Podemos arreglar esto fácil si en la columna de la derecha de los datos generados, escribimos la formula REDONDEAR. Si por ejemplo

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.


5.1

PROCEDIMIENTOS PARA VARIABLES CUANTITATIVAS CONTINUAS

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


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.


5.2

FUNCIONES ESTADISTICAS:

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:


5.3

EJEMPLOS DE USOS DE ANALISIS DE DATOS:

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

Recommend Documents