tema1_herramientas

VISUAL BASIC APLICADO (VBA) PARA APLICACIONES EN EXCEL

Código 9679

Asignatura de libre opción recomendada para CC. Actuariales y Financieras

Profesores Juan M. Pérez Salamero González

Manuel Ventura Marco CURSO 2005-2006

Departamento de Economía Financiera. Visual Basic para Aplicaciones en Excel

Material elaborado por Julia Suso, Manuel Ventura y Juan M. Pérez-Salamero Pág. 1

PARTE 1: Introducción a Excel. Visual Basic para Aplicaciones en Excel (VBA). La enseñanza de la Matemática de las Operaciones Financieras y Actuariales ha experimentado durante la década de los noventa una transformación profunda como consecuencia de la introducción progresiva de los programas informáticos. Las hojas de cálculo, dada su gran facilidad de uso, su flexibilidad y su utilización generalizada, han sido una de las herramientas disponibles más potentes para el tratamiento de los problemas financieros. No obstante, es preciso tener en cuenta que para aplicaciones de alto nivel, se requieren programas mas especializados y que cada vez se van extendiendo otras herramientas informáticas de cálculo avanzado como los programas de cálculo numérico (p. e. Matlab) o de cálculo simbólico (Mathematica, Matlab, Derive) o el salto a la utilización del lenguaje VBA (Visual Basic Aplicado) a Excel. El curso está dirigido a usuarios de Excel sin experiencia previa en programación con inquietud por aprender sobre las posibilidades que ofrece el VBA así como a usuarios que emplean habitualmente macros y desean potenciarlas con los recursos que ofrece VBA.



No tenemos que convertirnos en programadores de alto nivel para empezar a aprovechar las ventajas que ofrece el VBA. En realidad con unas pocas instrucciones seremos capaces de realizar infinidad de programas. Con este lenguaje de programación (VBA) vamos a poder sistematizar y automatizar tareas, crear funciones personalizadas e incluso aplicaciones completas que permiten la realización de tareas con un mayor grado de complejidad, simplificando a su vez el manejo de las mismas. En definitiva, permite la creación de macros y secuencias de instrucciones que le indican a Excel lo que debe realizar.



TEMA 1.- Introducción: Herramientas Avanzadas de Excel. Macros. 1.1.- Herramientas Avanzadas de Excel.

1.1.1 Funciones de búsqueda 1.1.2 Buscar Objetivo 1.1.3 Escenarios 1.1.4 Datos Tabla 1.1.5 Solver 1.1.6 Tablas dinámicas

1.2 Macros 1.2.1 Creación, ejecución y modificación básica de una macro. 1.2.2 Asignación de una macro a objetos para su ejecución rápida:

1.2.2.1 Botón de barra de herramientas. 1.2.2.2 Objeto gráfico. 1.2.2.3 Menú.

1.2.3 El contenido de una macro: Presentación del Editor de Visual Basic. 1.2.4 Necesidad de depuración de una macro.

1.2.4.1 Referencias absolutas y relativas en una macro. 1.2.4.2 Generación de código innecesario. 1.2.4.3 Otras causas.

1.2.5 Almacenamiento y firma de macros personales.



1.1.1 Funciones de Búsqueda Con estas funciones es posible encontrar valores en una lista o tabla de datos y extraer datos de esa lista o tabla. Las más usuales son BUSCARV, BUSCARH y BUSCAR. BUSCARH(valor_buscado;matriz_buscar_en;indicador_filas; ordenado) Esta función busca un valor (valor_buscado) en la fila superior de una tabla (matriz_buscar_en). La fila superior de la tabla debe contener los valores en orden ascendente. La función se detendrá en aquel valor que sea igual al valor_buscado o en el valor que más se aproxime a valor_buscado y que sea menor que él. A continuación devuelve el valor que se encuentre tantas filas por debajo como indique el indicador_filas. Si ordenado es falso solo encontrará el valor buscado si la coincidencia es exacta. Si ordenado es verdadero o se omite, la función devuelve el mayor valor que sea menor que el argumento valor_buscado. =BUSCARV funciona igual que =BUSCARH, pero en este caso la búsqueda la realiza en la primera columna de la tala y el indicador_columnas indica el número de columnas a desplazarse a la derecha respecto a la primera columna.



Ejemplo 1.1: A B C D E

1 Año 1970 1980 1990 2000 2 Producción 123 157 258 499 3 Beneficio -102 -40 500 631

=BUSCARH(1987;A1:E3;2) devuelve como resultado 157 =BUSCARH(1999;A1:E3;3) devuelve como resultado 500 =BUSCARH(1999;A1:E3;3;falso) devuelve como resultado #N/A Una vez que ya hemos obtenido unos resultados, operando con las fórmulas o funciones de Excel y con unos determinados datos, Excel nos proporciona diferentes mecanismos que permiten plantearnos la pregunta: ¿Qué pasaría si....? Algunos de estos mecanismos son: -Modificar directamente los datos iniciales -Comando Herramientas Buscar Objetivo -Comando Datos Tabla -Administrador de escenarios



1.1.2 Buscar Objetivo Busca un resultado específico de una celda ajustando el valor de otra celda Pasos a seguir: 1 Situarnos en la celda que contiene la fórmula cuyo valor queremos obtener 2 En el menú Herramientas, haga clic en Buscar objetivo. 3 En el cuadro Definir celda, introduzca la referencia de la celda que contenga la fórmula que desee resolver. 4 En el cuadro Con el valor, introduzca el resultado que desee. 5 En el cuadro Para cambiar la celda, introduzca la referencia de la celda que contenga el valor que desee ajustar. Ejemplo 1.2 ¿Qué valor ha de tomar {el número de artículos, o el precio o el coste} si queremos obtener un beneficio de 9.000.000?



A B C D 1 DATOS: nº artículos precio coste 2 12.000 1.000 400 3 4 NINGRESOS: 12.000.000 5 COSTES: 4.800.000 6 BENEFICIOS: 7.200.000

Resultado:

A B C D 1 DATOS: nº artículos precio coste 2 15.000 1.000 400 3 4 INGRESOS: 15.000.000 5 COSTES: 6.000.000 6 BENEFICIOS: 9.000.000



Ejemplo 1.3 Obtener el precio base si queremos que el PVP tenga un valor determinado.

A B C 1 Precio base IVA PVP 2 3.000 16% 3.480

A B C

1 Precio base IVA PVP 2 3.017 16% 3.500

Si existe solución nos muestra los valores que satisfacen los objetivos buscados y nos da opción a aceptarlos o rechazarlos. Pero puede ocurrir que el proceso iterativo que sigue este comando no sea convergente, es decir que no exista ningún valor que satisfaga el buscado en la fórmula.



1.1.3 Escenarios Es otro mecanismo del tipo ¿Qué pasaría si...? En este caso vamos a poder determinar cómo cambiarían ciertos cálculos en función del escenario que se esté mostrando. Para el Ejemplo 1.2: Pasos: 1 Creamos una hoja con los datos y fórmulas que queremos utilizar. 2 En el menú Herramientas, elegimos Escenarios. Mostrar Ejecuta el escenario y muestra los

valores asociados a ese escenario Agregar Sirve para definir el escenario Modificar Permite modificar cualquier aspecto del

escenario Eliminar Elimina el escenario seleccionado Combinar Podemos traer a la hoja activa otros

escenarios definidos en otras hojas. Resumen Se obtiene un informe resumen en una

hoja nueva. El propósito es poder tomar la mejor decisión para nuestros objetivos



A B C D

1 DATOS: nº artículos precio coste 2 12.000 1.000 400 3 4 INGRESOS: 12.000.0005 COSTES: 4.800.0006 BENEFICIOS: 7.200.000

3 Hacemos clic en Agregar. 4 En el cuadro Nombre de escenario, introducimos un nombre para el escenario. 5 En el cuadro Celdas cambiantes, introducimos las referencias de las celdas que queremos cambiar. 6 En Proteger, seleccionamos las opciones que deseamos. 7 Hacemos clic en Aceptar.



8 En el cuadro de diálogo Valores del escenario, introducimos los valores que deseamos para las celdas cambiantes.

9 Para crear el escenario, hacemos clic en Aceptar. Para crear escenarios adicionales, hacemos clic en Agregar y, a continuación, repetimos los pasos del 3 al 9. 10 Al terminar de crear los escenarios, elegimos Aceptar y hacemos clic en Cerrar en el cuadro de diálogo Administrador de escenarios.



Ejemplo 1.4 de resumen obtenido con el administrador de escenarios.

Resumen de escenario Valores actuales: Máximo Mínimo ActualCeldas cambiantes: $B$2 12.000 15.000 15.000 12.000 $C$2 1.000 2.000 500 1.000 $D$2 400 400 400 400Celdas de resultado: $C$6 7.200.000 24.000.000 1.500.000 7.200.000

Notas: La columna de valores actuales representa los valores de las celdas cambiantes en el momento en que se creó el Informe resumen de escenario. Las celdas cambiantes de cada escenario se muestran en gris.



1.1.4 Solver Solver es una macro automática que nos permite buscar soluciones de forma similar a Buscar Objetivo, pero de manera que no solo podemos indicar el valor que debe tener una celda, sino que además es posible maximizar o minimizar dicho valor. También tenemos la opción de establecer restricciones y condiciones en el modo de buscar ese valor. Para poder utilizar el Solver, es necesario especificarlo en el proceso de instalación del Office, ya que por defecto no se instala. Se puede añadir posteriori con la opción Agregar o quitar programas del Panel de Control de Windows. Si a pesar de haberlo instalado, no aparece en el menú herramientas debemos añadirlo desde la ventana de Excel Herramientas/Complementos y activar la casilla de verificación del Solver. Los pasos a seguir para resolver un problema con el Solver son: - Definimos el problema

- Asignamos una celda a cada variable - Asignamos una celda a la función y la escribimos en ella. - Asignamos una celda a cada restricción y las escribimos. - Seleccionamos Herramientas/Solver

La solución puede no existir, no ser única, o ser infinita (problema no acotado). Hay que tener cuidado con estos aspectos.



Ejemplo 1.5 Para los mismos datos del Ejemplo 1.2, ¿qué valor ha de tomar {el número de artículos y el precio} si queremos obtener un beneficio de 9.000.000?

* Escribimos:

- Celda de la función - Celdas de la variable - Valor deseado - Introducimos las restricciones

* Hacemos clic en resolver



Ejemplo 1.6: Resuelva el problema de Programación Lineal:

Max F(x) = 4 x + 5 y s.a : 2 x + y ≤ 8

y ≤ 5 x, y ≥ 0

A B C D E F G 1 x y F(x,y) R1 R2 R3 R4 2 1 1 =4*A2+

5*B2 =2*A2

+B2 =B2 =A2 =B2

Resultado 9 3 1 1 1



Solución:

A B C D E F G 1 x y F(x,y) R1 R2 R3 R4 2 =4*A2+

5*B2 =2*A2

+B2 =B2 =A2 =B2

2 1,5 5 31 8 5 1,5 5



Normalmente lo más difícil a la hora de utilizar Solver no es el uso de la herramienta en sí, sino el planteamiento del problema en la hoja de cálculo. Podemos ver varios ejemplos de resolución de sistemas con el Solver en el archivo que incorpora Excel llamado SOLVSAMP.XLS Para resolver este tipo de problemas hay otros paquetes más adecuados p. e. LINDO, LINGO, GAMS, etc.



1.1.5. Datos Tabla Es otro mecanismo del tipo ¿Qué pasaría si...? Ejemplo 1.7:

A B 1 Capital: 80.000,00 € 2 Tipo: 12,50% 3 Años: 10 4 Cuota anual: 14.449,74 €

¿Qué pasaría si .... el capital fuese.... los intereses fuesen.... el nº de meses fuera....? Con el comando Datos Tabla podemos hacer este tipo de análisis de forma rápida. Las tablas se pueden construir de 1 o de 2 variables:



* Crear una tabla de datos de una variable 1 Introduzca la lista de valores que desee sustituir, formando una fila o una columna de celdas con dichos valores. 2 Si las variables aparecen en una columna, introduzca la fórmula en la fila situada encima del primer valor y una celda a la derecha de los valores de columna. Introduzca las fórmulas adicionales a la derecha de la primera fórmula. Si las variables aparecen en una fila, introduzca la fórmula en la columna situada a la izquierda del primer valor y una celda por debajo de los valores de fila. Introduzca las fórmulas adicionales debajo de la primera fórmula. 3 Seleccione el rango de celdas que contenga las fórmulas y los valores que desee sustituir.



4 En el menú Datos, haga clic en Tabla. 5 Si la tabla de datos está orientada a columna, introduzca la referencia de celda con la variable que queremos sustituir en el cuadro Celda de entrada columna. Si la tabla de datos está orientada a fila, introduzca la referencia de celda con la variable que queremos sustituir en el cuadro Celda de entrada fila. Pueden agregarse fórmulas adicionales a una tabla de datos de una variable.

A B 1 Capital: 80.000,00 € 2 Tipo: 12,50% 3 Años: 10 4 Cuota anual: 14.449,74 € 5 6 =B4 7 11% 8 12% 9 13%



*Crear una tabla de datos de dos variables Las tablas de datos de dos variables solamente utilizan una fórmula con dos listas de variables. La fórmula deberá hacer referencia a dos celdas variables diferentes. 1 En una celda de la hoja de cálculo, introduzca la fórmula que haga referencia a las dos celdas variables. 2 Introduzca una lista de variables en la misma columna, debajo de la fórmula. Introduzca la segunda lista en la misma fila, a la derecha de la fórmula. 3 Seleccione el rango de celdas que contenga la fórmula y los valores de fila y de columna. 4 En el menú Datos, haga clic en Tabla. 5 En el cuadro Celda de entrada fila, introduzca la referencia de la celda variable para las variables de la fila. 6 En el cuadro Celda de entrada columna, introduzca la referencia de la celda variable para las variables de la columna.



A B C D 1 Capital: 80.000,00 € 2 Tipo: 12,50% 3 Años: 10 4 Cuota anual: 14.449,74 € 5 6 =B4 50.000 60.000 70.000 7 11% 8 12% 9 13%



1.1.6 Tablas Dinámicas Las tablas dinámicas de Excel nos van a permitir resumir los datos de nuestras listas de datos o de nuestras bases de datos en forma de tablas de referencia cruzadas o de gráficos. Para poder crear este tipo de tablas, Excel incluye un asistente que nos guía en el proceso. Este asistente se activa mediante el menú Datos –Asistente para tablas dinámicas. Vamos a ver este proceso mediante un ejemplo: Ejemplo 1.8: Supongamos que tenemos una lista con los siguientes campos: Código Fecha Conductor Origen Destino Kilometra Mercancia Bultos Precio Porte

Y queremos saber el número de bultos totales que ha transportado cada conductor por cada tipo de mercancía. Esta información sería muy cómoda de consultar si se encontrase en una tabla similar a :



Pagina: Si ponemos un campo sobre esta sección, tendremos una tabla distinta para cada campo. Columna: Si colocamos un campo en esta sección veremos en la parte superior de las columnas los valores que tenga dicho campo. Fila: En la parte izquierda de la filas veremos los distintos valores que contenga el campo que coloquemos. Datos: Con el campo que coloquemos aquí, se realizará el cálculo que seleccionemos, agrupando previamente por los encabezados de fila y columna que hayamos escogido.



Cuando se genera la tabla dinámica, Excel muestra una barra de herramientas para poder realizar diferentes operaciones con los datos obtenidos de la tabla:

tema1_herramientas

Documents