• Author / Uploaded
• JasminDextre

Citation preview

EXCEL AVANZADO

MACROS & APLICACIONES VISUAL BASIC MODULO V

MINPE INSTITUCIÓN DE FORMACIÓN

CONTENIDO CONCEPTOS ELEMENTALES ................................................................................................................................ 2 NOCIONES BÁSICAS ........................................................................................................................................ 2 DEFINICIÓN Y UTILIDAD DE UNA MACRO ....................................................................................................... 3 EL LENGUAJE DE PROGRAMACIÓN VISUAL BASIC .............................................................................................. 4 MACROS AUTOMÁTICAS .................................................................................................................................... 6 MACRO AUTOMÁTICA .................................................................................................................................... 6 FICHA DEL PROGRAMADOR / DESARROLLADOR ............................................................................................ 6 PRIMERA MACRO ............................................................................................................................................... 7 AGREGAR BOTONES EN LA HOJA SALIDAS Y EN LA HOJA TOTALES ................................................................ 8 MACRO NEGRITA, CURSIVA Y SUBRAYADO ........................................................................................................ 8 REVISAR EL CODIGO FUENTE .......................................................................................................................... 9 MACRO QUE APLICA FORMATO DE NÚMERO .................................................................................................. 10 MACRO OPERACIONES MATEMÁTICAS ............................................................................................................ 11 BOTONES MULTIPLICACIÓN, OPERACIÓN X & Y ........................................................................................... 12 MACRO GRÁFICOS, CONTROLES Y FUNCIONES ................................................................................................ 13 ASIGNACIÓN DE MACROS A LOS BOTONES .................................................................................................. 14 EJERCICIOS PROPUESTOS ............................................................................................................................. 15 INTRODUCCIÓN AL VISUAL BASIC .................................................................................................................... 16 PROGRAMACIÓN DE FUNCIONES PERSONALIZADAS ................................................................................... 16 FUNCIÓN 1: IGV_MINPE ................................................................................................................................... 16 COMPLEMENTO DE MICROSOFT EXCEL ....................................................................................................... 17 FUNCIÓN 2: VALIDAR_PRESTAMO_MINPE ...................................................................................................... 19 FUNCIÓN 3: IMPORTE_HE_MINPE ................................................................................................................... 20 FUNCIÓN 4: NOMBRE_MES_MINPE ................................................................................................................. 21 FUNCIÓN 5: COMISION_MINPE........................................................................................................................ 22 FUNCIÓN 6: DESTINATARIO_MINPE ................................................................................................................. 23 FUNCIÓN 7: CALIFICACIÓN_MINPE .................................................................................................................. 23 EJERCICIOS PROPUESTO ................................................................................................................................... 24

Página 1 de 24

CONCEPTOS ELEMENTALES Una macro (Abreviatura de macroinstrucción) es un conjunto de instrucciones que se almacenan para que se puedan ejecutar de manera secuencial mediante una sola llamada u orden de ejecución. Dicho de otra manera, una macro es una instrucción que se forma a partir de otras instrucciones más sencillas (Ejemplos de instrucciones sencillas: aplicar negrita, guardar archivo, seleccionar una celda, entre otras). De esta forma se logra la automatización de tareas repetitivas. NOCIONES BÁSICAS

SOFTWARE Las operaciones que debe realizar el hardware son especificadas por una lista de instrucciones, llamadas programas o software .El software se puede dividir en dos grandes grupos.





Software del sistema: Es el conjunto de programas indispensables para que la máquina funcione. Estos programas son básicamente, el sistema operativo (Por ejemplo: Windows, Mac OS, Android) y los lenguajes de programación que sirven para escribir programas que permitan la comunicación usuario / máquina (Visual Basic, HTML, Java). Software de aplicaciones: Son programas desarrollados utilizando un lenguaje de programación. Permiten al usuario realizar diversos tipos de trabajo. Algunos ejemplos de programas de aplicación son los procesadores de textos (Word), hojas de cálculo (Excel), y base de datos (Acces, Oracle). También están incluidos el software de contabilidad, producción, entre otros.

Página 2 de 24

La figura siguiente representa la forma general en que se ejecuta un programa (software). En forma práctica (pero no completa) se puede señalar que el ingreso puede estar representado por el teclado y la salida por el monitor. El computador y programa puede estar representado por el CPU.

SOFTWARE

COMPUTADOR

INGRESO

SALIDA

Ilustración 1 - Excel Avanzado Macros & VBA

DEFINICIÓN Y UTILIDA D DE UNA MACRO

¿Alguna vez has invertido mucho tiempo en realizar una tarea repetitiva en Excel sospechando que habría una forma más sencilla de realizar el trabajo? Quizás deseabas cambiar el formato de varias tablas o reorganizarlos datos. O quizás has querido aplicar los mismos cambios en varias hojas similares .Si estas tareas u otras similares le resultan familiares, debería aprender a utilizar macros. Las macros son muy útiles para ahorrar tiempo. Además, las macros son mucho más sencillas de crear y utilizar de lo que puedas pensar. ¿Qué es una macro? Una macro (abreviatura de macroinstrucción) es un conjunto de instrucciones que se almacenan para que se puedan ejecutar de manera secuencial mediante una sola llamada u orden de ejecución .Dicho de otra manera, una macro es una instrucción que se forma a partir de otras instrucciones más sencillas (Ejemplos de instrucciones sencillas: aplicar negrita, guardar archivo, seleccionar una celda, entre otras). De esta forma se logra la automatización de tareas repetitivas.

Página 3 de 24

¿Son las macros un tipo de programación? Las macros son programación, pero no es necesario ser programador ni tener conocimientos de programación para utilizarlas. La mayoría de las macros que se pueden crear en los programas de office se escriben en un lenguaje llamado Microsoft Visual Basic para Aplicaciones, conocido como VBA. Las macros VBA son las que tratamos en este manual. ¿Cuándo se deben utilizar macros? Las Macros ahorran tiempo y amplían las posibilidades de los programas que utiliza a diario. Sirve para automatizar tareas repetitivas que desea dejar grabadas porque las realizas con frecuencia. Algunos ejemplos: eliminar filas vacías en los informes, aplicar formato numérico a celdas o para crear soluciones como automatizar la creación de documentos que utilizan con regularidad. Los usuarios que estén familiarizados con los lenguajes VBA pueden utilizar macros para crear complementos personalizados que incluyen cuadros de diálogo, o incluso guarden información para utilizarla en distintas ocasiones. Observe el ejemplo utilizado en este artículo para dar formato a varias tablas en un documento de Excel .Supongamos que en un documento hay 50 tablas cuyo formato desea modificar. Incluso si es usted un usuario experimentado y solo necesita cinco minutos para cambiar el formato de cada tabla, necesitará más de cuatro horas para realizar la tarea. Si crea una macro para dar formato a las tablas y después edita dicha macro para repetir los cambios en el documento, puede terminar el trabajo en cuestión de minutos, en vez de horas.

EL LENGUAJE DE PROGRAMACIÓN VISUAL BASIC Visual Basic es un lenguaje de programación que contiene un conjunto completo de herramientas en el que se puede escribir y modificar programas. Resolución de problemas con computadora: La resolución de un problema con una computadora se hace escribiendo un programa (lo que comúnmente se llama programar). Para programar se recomiendan las siguientes actividades: a. b. c. d. e.

Definir o analizar el problema. Diseñar el algoritmo. Transformar el algoritmo en un programa. Ejecutar y validar el programa. Documentar las cuatro actividades anteriormente ( importante actividad que suele olvidarse)

Página 4 de 24

Un herramienta de ayuda para el analista y diseñador (pasos a y b) son los diagramas de flujo. Con ésta herramienta se representa gráficamente un algoritmo.

INICIO

INICIO

ACTIVIDAD

ACTIVIDAD

DECISION

CONECTOR

NO

ACTIVIDAD

DECISION

NO

SI

SI FINAL

DECISION

ACTIVIDAD

Ilustración 2 - Excel Avanzado Macros & VBA

El presente manual no pretende que el lector desarrolle habilidades de analista; diseñador o programador de sistemas. Para ellos existen carreras profesionales. El objetico del manual es brindar una noción elemental de conceptos de programación a través del desarrollo de programas de uso didáctico. Son programas con nivel de complejidad bajo y regular que, sin embargo, podrían ayudar al lector a continuar experimentando.

Página 5 de 24

MACROS AUTOMÁTICAS La programación automática pretende que sea el propio ordenador el que escriba los programas que necesitan las personas, siguiendo las indicaciones del usuario. Para el caso de las macros de Excel, se graban en el lenguaje de programación de Visual Basic para Aplicaciones. MACRO AUTOMÁTICA

La forma más fácil e intuitiva de crear macros es crearlas mediante el grabador de macros del que dispone Excel. Este grabador de macros te permite grabar las acciones deseadas que posteriormente las traduce a instrucciones en VBA, las cuales podemos modificar si tenemos conocimientos de programación. Antes de grabar una macro hay que tener en cuenta los siguientes puntos:    

Habilite la Ficha del Programador o Desarrollador Planifique los pasos y los comandos que desea que ejecute la macro. Si se comete algún error mientras se graba la macro, el error también se grabará Cada vez que grabe una macro, el programa (conjunto de instrucciones) se almacenará en un nuevo procedimiento adjunto a un libro

FICHA DEL PROGRAMADO R / DESARROLLADOR

Cuando se vaya a trabajar con macros (grabar, escribir programas o crear aplicaciones) se debe activar la ficha del programar. Para activar la Ficha del programador realice lo siguiente: a. En el botón de Archivo de Excel, parte superior izquierda, seleccionar Opciones b. Dentro de la pestaña seleccionar: Personalizar cinta de opciones c. Verifica que la ficha Desarrollador aparezca en el Menú

Página 6 de 24

PRIMERA MACRO 1. 2. 3. 4.

Verificar que la ficha Programador / Desarrollador este activa Abrir el archivo MODULO V_5001_PRIMERA MACRO Seleccionar la celda A1 de la hoja Ingresos Seleccionar Desarrollador, Grabar Macro

Ilustración 3 - Excel Avanzado Macros & VBA

5. 6. 7. 8.

Ingresa el nombre: macro_01_[MINPE] Presiona Aceptar Seleccionar la celda A1 de la hoja Salidas Seleccionar Desarrollador, Detener Grabación

Ilustración 4 - Excel Avanzado Macros & VBA

9. 10. 11. 12.

Seleccionar la celda A1 de la hoja Ingresos Dentro de la ficha de Desarrollador haz clic en Insertar Selecciona el objeto Botón Dibuja un rectángulo del tamaño de dos celdas

Ilustración 5 - Excel Avanzado Macros & VBA

13. En el recuadro Asignar Macro selecciona la macro: macro_01_[MINPE] 14. Presionar Aceptar 15. A continuación renombrar el botón (clic derecho, modificar texto) como Ir a Salidas 16. Seleccionar la celda A1 17. Presionar el nuevo botón creado Ir a Salidas, te debe dirigir a la hoja Salidas Página 7 de 24

18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30.

Seleccionar la celda A1 de la hoja Ingresos Seleccionar: Desarrollador, Grabar Macro Ingresa el nombre: macro_02_[MINPE] Presionar Aceptar Seleccionar la celda A1 de la hoja Totales Seleccionar Desarrollador, Detener Grabación Seleccionar la celda A1 de la hoja Ingresos Insertar un segundo botón debajo del botón Salidas En el recuadro Asignar Macro seleccionar la macro: macro_02_[MINPE] Presionar Aceptar A continuación renombrar el botón como Ir a Totales Seleccionar la celda A1 de la hoja Ingresos Presiona el nuevo botón creado Ir Totales

AGREGAR BOTONES EN L A HOJA SALIDAS Y EN LA HOJA TOTALES

31. Agregar dos botones en la Hoja Salidas  Ir a Ingresos: Que lleve a la celda A1 de la hoja Ingresos  Ir a Totales: Que lleve a la celda A1 de la hoja Totales 32. Agregar dos botones en la Hoja Totales  Ir a Ingresos: Que lleve a la cela A1 de la hoja Ingresos  Ir a Salidas: Que lleve a la celda A1 de la hoja Salidas

MACRO NEGRITA, CURSIVA Y SUBRAYADO 1. 2. 3. 4. 5. 6.

Crear un nuevo documento de Excel A partir de la celda A1 escribe Código En la celda A2 escribe 100 Seleccionar la celda A2 Iniciar la grabación: Desarrollador, Grabar Macro Ingresar los siguientes tres valores:

Ilustración 6 - Excel Avanzado Macros & VBA

Página 8 de 24

7. Nombre de la macro macro_02_MINPE: es el nombre de la macro. Con este nombre se buscará posteriormente y se podrá acceder al código fuente 8. Método abreviado Ctrl+Mayúscula+K: combinación de teclas para acceder a la macro desde el teclado 9. Descripción Negrita, Cursiva y Subrayado: Texto que indica a descripción general 10. Presionar Aceptar 11. Aplicar lo siguiente: Negrita, Cursiva y Subrayado 12. Termina la grabación: Desarrollador, Detener grabación 13. Escribe en la celda C10: 1200. Luego presionar ENTER 14. Seleccionar la celda C10 15. Ejecutar la macro presionando: CTRL + SHIFT + K 16. Otro forma de ejecutar la macro es: Desarrollador, Macros, Seleccionar el nombre de la macro y Ejecutar 17. A partir de la celda E10 escribe: Mundo, América, Perú y Arequipa 18. Seleccionar las celdas desde la E10 hasta la E13 19. Ejecuta la macro presionando: CTRL + SHIFT + K REVISAR EL CODIGO FUENTE

1. Seleccionar: Desarrollador, Visual Basic (ALT + F11) 2. Aparecerá el software de VISUAL BASIC 3. Revisar el programa. Cada dila de código representa negrita (Bold), cursiva (Italic) y subrayada. 4. Cerrar el Visual Basic

Página 9 de 24

MACRO QUE APLICA FORMATO DE NÚMERO 1. 2. 3. 4. 5. 6.

Crear un nueva hoja de Excel En la celda A2 escribe el número “-450” Seleccionar la celda A1 Nuevamente seleccionar la celda A2 Graba una nueva macro: Desarrollador, Grabar Macro Ingresa los siguientes valores:  Nombre de la macro: macro_03_MINPE  Método Abreviado: CTRL+MAYÚSCULAS+J  Descripción: Formato de número rojo-negativo 7. Aumenta el tamaño de letra a 15 8. Aplica el formato de número personalizado siguiente #,##0.00;[Rojo](-#,##0.00)

Ilustración 7 - Excel Avanzado Macro & VBA

9. Termina la grabación: Desarrollador, Detener Grabación 10. Escribir 4 números y aplicar Macro 11. Revisar el código fuente, luego guardar el documento

Página 10 de 24

MACRO OPERACIONES MATEMÁTICAS 1. Crear una nueva hoja de Excel 2. Insertar el Objeto Cuadro de grupo. Deber ser lo suficientemente grande para contener cuatro botones

Ilustración 8 - Excel Avanzado Macros & VBA

3. Dentro del cuadro de grupo insertado, ingresar cuatro botones como se indica en la siguiente imagen:

Ilustración 9 - Excel Avanzado Macros & VBA

4. 5. 6. 7. 8.

Escribe en la cela H4 el siguiente texto: Ingresa 1er número Escribe en la cela H6 el siguiente texto: Ingresa 2do número Escribe en la cela H8 el siguiente texto: Ingresa 3er número Escribe en la cela H10 el siguiente texto: RESULTADO Sombrear de color amarillo las celdas J4, J6 y J8 Página 11 de 24

9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23.

Sombrear de color azul claro la celda J10 Seleccionar la celda A1 Seleccionar: Desarrollador, Grabar Macro Ingresa los siguientes valores:  Nombre de la macro: macro_suma1_MINPE Seleccionar la celda J10 Escribe la fórmula: =J4+J6+J8 Presionar ENTER Escribe en la celda K10: Suma Seleccionar la celda A1 Termina la grabación: Desarrollador, Detener Grabación Hacer clic derecho en el botón Sumar Seleccionar Asignar Macro Seleccionar macro_suma1_MINPE Presionar Aceptar Prueba el botón Suma

BOTONES MULTIPLICACIÓN, OPER ACIÓN X & Y

24. Repetir los pasos del 10 hasta el 23 para los botones:  Multiplicación  Operación X: =(J4+J6+J8)/3  Operación Y: =POTENCIA(J4;2)+POTENCIA(J6;2)+POTENCIA(J8;2) 25. Insertar un botón adicional y nombrarlo Operación Z 26. Revisa el código Visual Basic: ALT +F11

Página 12 de 24

MACRO GRÁFICOS, CONTROLES Y FUNCIONES 1. Crear una nueva hoja de Excel 2. Insertar cinco objetos. Nombrarlos y ubicarlos según las siguientes indicaciones:

Ilustración 10 - Excel Avanzado Macros & VBA

3. A partir de la celda M9 ingresar la siguiente información:

PERU BRASIL ALEMANIA COREA

2016 34 35 60 48

2017 47 60 43 57

2018 34 31 41 61

2019 46 31 53 31

Tabla 1 - Excel Avanzado Macro & VBA

4. Seleccionar desde la celda M10 hasta la celda M13 5. Crear un grupo llamado PAISES 6. Crear cuatro grupos adicionales:  Grupo Perú: desde la celda M10 hasta Q10  Grupo Brasil: desde la celda M11 hasta Q11  Grupo Alemania: desde la celda M12 hasta Q12  Grupo Corea: desde la celda M13 hasta Q13 7. Haz clic derecho en el cuadro combinado y selecciona Formato de Control:

Página 13 de 24

8. Llenar los campos según lo siguiente:

Ilustración 11 - Excel Avanzado Macros & VBA

9. Presionar Aceptar 10. Comprobar que cada vez que seleccionemos un país en cuadro combinado la celda M2 cambie a un nuevo valor 11. A partir de la celda M17 ingresar la siguiente información: =INDICE(P AISES;M2)

2016

2017

2018

2019

=INDICE(INDIREC TO($M$18);1)

=INDICE(INDIREC TO($M$18);2)

=INDICE(INDIREC TO($M$18);3)

=INDICE(INDIREC TO($M$18);4)

12. Seleccionar desde las celdas M17 hasta la celda Q18 13. Ingresar un gráfico de barras, ubicarlo debajo del cuadro de grupo ASIGNACIÓN DE MACROS A LOS BOTONES

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18.

Seleccionar la celda A1 Seleccionar Grabar Macro Nombra la nueva macro como: macro_grafico1_MINPE Seleccionar el gráfico Cambiar el tipo de gráfico a Línea Seleccionar la celda A1 Detener la grabación de macro Hacer clic derecho en el primero botón Seleccionar Asignar Macro Seleccionar la macro: macro_grafico1_MINPE Presionar Aceptar Seleccionar la celda A1 Seleccionar Grabar Macro Nombra la nueva macro como: macro_grafico2_MINPE Seleccionar gráfico Cambiar el tipo de gráfico a Circular Seleccionar la celda A1 Detener la grabación de la macro Página 14 de 24

19. 20. 21. 22. 23.

Hacer clic derecho en el segundo botón Seleccionar Asignar Macro Seleccionar la macro: macro_grafico2_MINPE Presionar Aceptar Probar el archivo creado:  Seleccionar otro país  Presionar el botón 1 y 2 24. Crear un nueva macro que cambie el tipo gráfico a columna 3D 25. Asignar la nueva macro al tercer botón EJERCICIOS PROPUESTO S

1. En un nuevo documento de Excel, construir una macro que sombree una celda de color amarillo, centre el contenido de la celda y coloque el tamaño de letra a 8. Luego revisar el código en Visual Basic 2. En un nuevo documento de Excel, nombra tres hojas como: Resumen, Ventas e Inventario. Colocar dos botones en cada una de la hojas de tal manera que, a través de los botones, te puedas mover entre las hojas 3. Construir una macro que, después de seleccionar una conjunto de números, aplique formato condicional Tipo Tres Banderas 4. Construir una aplicación que cambie el tipo de gráfico utilizando dos botones:

LAPTOP DESKTOP TABLET MOVIL

HP 86 59 135 99

APPLE 89 79 119 137

TOSHIBA 115 115 61 152

SONY 135 143 63 71

Tabla 2 - Excel Avanzado Macros & VBA

Página 15 de 24

INTRODUCCIÓN AL VISUAL BASIC Visual Basic es un lenguaje de programación que contiene un conjunto completo de herramientas en el que se puede escribir y modificar programas. PROGRAMACIÓN DE FUNCIONES PERSONALIZADAS

Las funciones son fórmulas ya escritas que toman un valor, realizan una operación y devuelven un resultado. Utilizamos funciones para simplificar y acortar fórmulas en una hoja de cálculo especialmente aquellas que llevan a cabo cálculos prolongados o complejos. Por ejemplo una función usual predefinida en Excel es la función SUMA. Esta función contiene líneas de programa que ordena a Excel a realizar la suma de un conjunto determinado de números.

FUNCIÓN 1: IGV_MINPE En este ejercicio se construirá una función personalizada que recibirá un valor y calculará el valor sin IGV 1. 2. 3. 4.

Crear un nuevo documento Excel Presionar ALT + F11 para ingresar al Edito Visual Basic Hacer clic en el proyecto Libro1 Insertar Módulo, clic derecho en VBAProject (Libro1) – Insertar – Módulo

Ilustración 12 - Excel Avanzado Macros & VBA

Página 16 de 24

5. En el editor de Visual Basic (cuadro de texto), escribir lo siguiente programa:

Ilustración 13 - Excel Avanzado Macros & VBA

6. Graba el archivo con el nombre macro_igv_MINPE con el siguiente tipo de archivo: Libro de Excel habilitado para macros 7. Prueba la función creada escribiendo lo siguiente a partir de la celda C3 MONTO 5600 MONTO SIN IGV Tabla 3 - Excel Avanzado Macros & VBA

8. Luego ingresar en la celda D4: =IGV_MINPE(D3) 9. De esta forma se ha creado su propia función 10. También puede encontrar la función buscándola en: Insertar función, Definidas por el usuario COMPLEMENTO DE MICROSOFT EXCEL

Una vez creada la función; es posible crear un complemento de Excel (archivo .XLAM) que permita usar dichas funciones en cualquier libro en que se necesite la función. Por ejemplo, si crea dos funciones desde su oficina en Perú y luego viaja a USA, podría guardar las dos funciones creadas en un archivo complemento y enviarlas a su correo electrónico, guardarlas en Excel y seguir utilizando en USA las dos funciones creadas. Los complementos son programas suplementarios que agregan funciones o comandos personalizados a Microsoft Excel. Los complementos de Excel están disponibles cuando se instala Excel o puede crear complementos personalizados.

Página 17 de 24

GUARDAR LIBRO EXCEL COMO COMPLEMENTO 1. 2. 3. 4.

Debe tener abierto su archivo macro_igv trabajado en la sección anterior En la hoja presionar Archivo, Guardar como En nombre de archivo escribir: Funciones_Propias_MINPE_01 En tipo seleccionar: Complementos de Excel

ACTIVAR EL COMPLEMENTO 1. Una vez creado el archivo complemento, este debe ser activado para usar las funciones creadas 2. Presionar el Archivo, Opciones 3. Seleccionar la categoría Complementos 4. Presionar Ir 5. En la ventana Complementos activar la casilla Funciones_Propias_MINPE_01 y presionar Aceptar

Ilustración 14 - Excel Avanzado Macros & VBA

6. Si se guardó el complemento en una ubicación distinta de la especificada, usar el botón Examinar e indicar la ruta 7. Ahora la función o funciones guardadas en un archivo complemento podrán ser usadas en cualquier archivo de Excel que abra en la computadora donde ha activado el complemento

Página 18 de 24

FUNCIÓN 2: VALIDAR_PRESTAMO_MINPE En este ejercicio crearás una función personalizada que permitirá aprobar o no aprobar el préstamo a un cliente. Si el monto del préstamo solicitado por el cliente es menor o igual a cuatro veces su sueldo, entonces el préstamo estará “Aprobado”. 1. Crear un nuevo documento Excel 2. A partir de la celda A1 ingrese la siguiente data:

Préstamos Personales Expediente 2012-001 2012-002 2012-003

Sueldo Monto Solicitado Evaluación S/3,500.00 S/10,000.00 S/4,200.00 S/20,000.00 S/900.00 S/3,000.00

Tabla 4 - Excel Avanzado Macros & VBA

3. Presionar ALT + F11 para ingresar al Editor de Visual Basic 4. Insertar un módulo en el libro de trabajo activo: Clic derecho en VBAProject (Libro), Insertar, Módulo 5. En el editor de Visual Basic escribir el siguiente programa:

Ilustración 15 - Excel Avanzado Macros & VBA

6. Grabar el archivo con el nombre macro_validar_prestamo_MINPE como Libro de Excel habilitado para macros 7. En la celda D4 escribir: =validar_prestamo_MINPE(D4) 8. Copiar el resultado al resto de la columna D

Página 19 de 24

FUNCIÓN 3: IMPORTE_HE_MINPE En este ejercicio se creará una función personalizada que permitirá calcular el monto a pagar por horas extras. Considerar que el sueldo básico se calcula sobre 160 horas al mes y que la hora extra se paga en un 50% adicional a la hora normal. 1. Crear un nuevo documento Excel 2. A partir de la celda A1 ingrese la siguiente tabla:

Código Sueldo Básico Horas trabajadas Importe Horas extras T01 S/1,600.00 S/170.00 T02 S/2,800.00 S/180.00 T03 S/900.00 S/200.00 Tabla 5 - Excel Avanzado Macros & VBA

3. Presionar ALT + F11 para ingresar al Editor de Visual Basic 4. Insertar un módulo en el libro de trabajo activo: Clic derecho en VBAProject (Libro), Insertar, Módulo 5. En el editor de Visual Basic escribir el siguiente programa:

Ilustración 16 - Excel Avanzado Macros & VBA

6. En la celda D2 escribir: =Importe_HE_MINPE(B2;C2) 7. Copiar el resultado al resto de la columna D

Página 20 de 24

FUNCIÓN 4: NOMBRE_MES_MINPE 1. En un archivo Excel nuevo escribir el siguiente código:

Ilustración 17 - Excel Avanzado Macros & VBA

2. Probar el programa 3. Guardar como Libro de Excel habilitado para macros

Página 21 de 24

FUNCIÓN 5: COMISION_MINPE 1. En un archivo Excel nuevo escribir el siguiente código:

Ilustración 18 - Excel Avanzado Macros & VBA

2. Grabar y luego probar el programa 3. Agregar la siguiente condición al programa: Si la comisión calculada es menor a 800, entonces, asignar a la comisión el valor de 800. Es decir, ningún vendedor puede comisionar menos de 800 4. Probar el programa, luego guardar

Página 22 de 24

FUNCIÓN 6: DESTINATARIO_MINPE 1. En un archivo Excel nuevo escribir el siguiente código:

2. Grabar y luego probar el programa con tres registros: Nombre

Apellido

Sexo

Destinatario

Ilustración 19 - Excel Avanzado Macros & VBA

3. La función UCASE transformar en mayúscula el texto

FUNCIÓN 7: CALIFICACIÓN_MINPE 1. Abrir el archivo MODULO V_5001_CALIFICACION 2. Criterios de Evaluación  Si el promedio se encuentra entre 0.0 y 10.4; resultado “DEFICIENTE”  Si el promedio se encuentra entre 10.5 y 13.9; resultado “REGULAR”  Si el promedio se encuentra entre 14.0 y 16.9; resultado “BUENO”  Si el promedio se encuentra entre 17.0 y 20.0; resultado “SOBRESALIENTE” 3. Crear una función definida por el usuario llamada calificacion_MINPE 4. Aplicar la función creada

Página 23 de 24

EJERCICIOS PROPUESTO 1. Crear función TIPO_CAMBIO_MINPE. La función debe recibir un monto en dólares y arrojar el monto correspondiente en nuevos soles (T.C. =S/3.40). Considerar también:  Si el monto en soles es mayor a 10,000 considerar un T.C.=3.35  Si el monto en soles es mayor a 50,000 considerar un T.C.= 3.30 2. Crear una función RESULTADO_MINPE que acepte cinco valores (VAR1, VAR2, VAR3, VAR4 y VAR5) y dé como resultado lo siguiente:  Si VAR1 > VAR2. Resultado: (VAR1 x VAR3) + (VAR4 x VAR5)  Si no. Resultado: (VAR2 + VAR3) + (VAR4 + VAR5) 3. Crear un función que calcule el área de un triángulo: (B x H)/2 La función debe recibir dos variables:  B, representa el largo de la base del triángulo  H, representa la altura de la base del triángulo

Página 24 de 24