Unidad 4 - Por favor proporciona tanta información adicional como sea posible, como el PDF

Preview

Summary

Excel Avanzado4####### UnidadMacros, Ficha Programador, Gabradorade Macros y SolverINFOTEP 33MACROS, FICHAPROGRAMADOR,GRABADORA DEMACROS Y SOLVER.OBJETIVO DE LA UNIDAD####### Al finalizar el estudio de esta unidad,####### El participante estará en capacidad####### de Crear Macros, Habilitar Ficha Pr...

Description

Unidad

Excel Avanzado

4

Macros, Ficha Programador, Gabradora de Macros y Solver

4.1 ¿Qué es una Macro de Excel? El origen de la palabra Macro se remonta al griego, tal como lo define Wikipedia ( μακρο, makro, que significa ‘grande’). Las Macros en Excel son un conjuntos de instrucciones que se ejecutan de manera secuencial por medio de una orden de ejecución, claro está que una Macro puede invocar a otras, logrando de esta forma obtener operaciones cada vez más complejas. Si utilizas Excel frecuentemente es posible que en alguna ocasión te hayas encontrado ejecutando una misma serie de acciones una y otra vez. Esas acciones que haces repetidas veces se podrían automatizar con una macro. Las macros se utilizan principalmente para eliminar la necesidad de repetir los pasos de aquellas tareas que realizas una y otra vez. Un lenguaje de programación Las macros se escriben en un lenguaje de computadora especial que es conocido como Visual Basic for Applications (VBA). Este lenguaje permite acceder a prácticamente todas las funcionalidades de Excel y con ello también ampliar la funcionalidad del programa. Pero no te preocupes si no eres un programador de computadoras, Excel provee de una herramienta especial que permite crear una macro sin necesidad de conocer los detalles del lenguaje de programación.

INFOTEP

2 *Aplica para: Office 2007, 2010, 2013 y 2016*

2

4.2 Mostrar la Ficha Desarrollador. A fin de poder configurar la Ficha Desarrollador y poder emplearla sin dificultades podemos seguir los siguientes pasos: 1.- Primero hacemos clic en el triángulo invertido en la parte superior izquierda del documento de Excel para personalizar la barra de herramientas de acceso rápido.

2.- Luego damos click en la opción: “Más comandos” de la lista desplegable:

INFOTEP

3

3.- Este nos llevará al cuadro de “Opciones de Excel” y ya en este damos click en la sección “Personalizar cinta de opciones” y en ella activamos la opción “Desarrollador” que esta desactivada por default :

4.- Una vez que hemos activado la opción podremos ver la sección de “DESARROLLADOR” en la cinta de opciones:

Grupos de la ficha Programador El grupo Código tienes los comandos necesarios para iniciar el Editor de Visual Basic donde se puede escribir directamente código VBA. También nos permitirá ver la lista de macros disponibles para poder ejecutarlas o eliminarlas. Y no podríamos olvidar mencionar que en este grupo se encuentra el comando Grabar macro el cual nos permite crear una macro sin necesidad de saber sobre programación en VBA. El grupo Complementos nos permite administrar y habilitar complementos como el Solver. INFOTEP

4 *Aplica para: Office 2007, 2010, 2013 y 2016*

4

El grupo Controles incluye funcionalidad para agregar controles especiales a las hojas de Excel como los controles de formulario que son botones, casillas de verificación, botones de opción entre otros más que serán de gran utilidad para ampliar la funcionalidad de Excel. El grupo XML permite importar datos de un archivo XML así como opciones útiles para codificar archivos XML. Finalmente el grupo Modificar solamente contiene el comando Panel de documentos. 5.- Y con ello podemos acceder a las herramientas de macros:

6.- Por otro lado si ya tenemos un documento en macros y queremos abrir el documento vamos a la opción “Archivo”, en la sección más opciones hacemos click en Centro de confianza y luego en configuración del Centro de confianza:

INFOTEP

5 *Aplica para: Office 2007, 2010, 2013 y 2016*

5

7.- Haga clic en configuración de macros y finalmente activamos la opción Habilitar todas las macros.

8.- Luego de esto podemos iniciar con el desarrollo de nuestros propios macros.

4.3 Crear una macro en Excel utilizando el grabador de macros. El punto fuerte a favor del uso de Macros, es que es sencillo de aprender a crearlas y ejecutarlas, crear Macros es simple, y se puede llegar a ahorrar mucho tiempo remplazando aquellas tareas repetitivas por Macros que usted

INFOTEP

6 *Aplica para: Office 2007, 2010, 2013 y 2016*

6

mismo haya creado, uno de los primeros pasos para acercarse al uso de Macros puede ser el comenzar a utilizar el grabador de Macros. El grabador de macros permite crear macros, se limita a las acciones que se puedan hacer con las herramientas visibles. Para empezar a grabar nuestra macro, debemos seguir lo siguiente: Favor entrar a la pestaña programador, seleccionar “Grabar macro”. Aparecerá una ventana donde debemos agregar nombre de la macro, método abreviado, lugar donde se guardará (Libro nuevo, Este libro o Libro de macros personal) y dar clic en Aceptar. En el cuadro de texto Nombre de la macro deberás colocar el nombre que identificará de manera única a la macro que estamos por crear. De manera opcional puedes asignar un método abreviado de teclado el cual permitirá ejecutar la macro con la combinación de teclas especificadas. La lista de opciones Guardar macro en permite seleccionar la ubicación donde se almacenará la macro.  Este libro. Guarda la macro en el libro actual.  Libro nuevo. La macro se guarda en un libro nuevo y que pueden ser ejecutadas en cualquier libro creado durante la sesión actual de Excel.  Libro de macros personal. Esta opción permite utilizar la macro en cualquier momento sin importar el libro de Excel que se esté utilizando. También puedes colocar una Descripción para la macro que vas a crear. Finalmente debes pulsar el botón Aceptar para iniciar con la grabación de la macro. Al terminar de ejecutar las acciones planeadas deberás pulsar el botón Detener grabación para completar la macro.

INFOTEP

7 *Aplica para: Office 2007, 2010, 2013 y 2016*

7

Comienza a gra o que queremos automatizar. Para terminar de grabar dentro del programador seleccionar la opción “Detener macro”.

Ahora para ejecutar la macro que hemos creado, ir a “macros” dentro de la pestaña programador seleccionar el nombre de la macro creada y ejecutar. ¿Cómo editar una Macro? Constantemente nos encontramos en la búsqueda de mejorar los archivos que realizamos, agregar pequeñas características que nos permitan obtener algún beneficio. Ya habiendo aprendido cómo crear una macros, surge la pregunta, si deseo agregar o quitar alguna función o característica de esta ¿Cómo edito una Macro?.

INFOTEP

8 *Aplica para: Office 2007, 2010, 2013 y 2016*

8

El editor de Visual Basic nos permitirá crear nuestros programas, así como mejorarlos hasta obtener el resultado que buscamos. Aprendamos como realizar estas ediciones de forma sencilla. La forma más sencilla de crear una macro es haciendo uso de una grabadora, veamos un ejemplo de cómo editar una macros que fue creada de esta forma Editar una Macro Paso a Paso PASO 1: Crea tu macro, en este caso la siguiente macro realiza la suma del puntaje obtenido por cada participante en un determinado juego:

PASO

PASO 3: A continuación, se presentará la ventana MACRO donde encontrarás todas las macros creadas en tu archivo, debes seleccionar aquella macro que desees editar, en este caso la macro se llama “PUNTAJES”. Una vez seleccionada en la parte derecha de la ventana selecciona MODIFICAR INFOTEP

9 *Aplica para: Office 2007, 2010, 2013 y 2016*

9

Ahora obtendrás una ventana que te permite el acceso de tu macros desde el editor de VISUAL BASIC.

PASO 4: Agrega o quita lo que creas conveniente, para este ejemplo mostraremos como agregar una columna en donde mostrar los porcentajes INFOTEP

10 *Aplica para: Office 2007, 2010, 2013 y 2016*

10

obtenidos por cada participante teniendo en cuenta que 40 puntos representan el 100%. Para el ejemplo agregamos el código Range(“F3”) = (Range(“E3”) / 40) * 100.

PASO 5: Guardamos los cambios, recuerda utilizar el TIPO: Libro de Excel habilitado para macros. PASO 6: Para probar solo debes ir nuevamente a DESARROLLADOR, luego a MACROS y seleccionar el archivo luego pulsa EJECUTAR. y verás los resultados. ¿Cómo se elimina una Macro en Excel? A continuación se muestra un paso a paso de cómo puedes borrar o eliminar cualquier macro que hayas guardado en un libro de Microsoft Excel. 1- Inicia Microsoft Excel y abre una hoja de cálculo de tus archivos, que contenga por lo menos una macro que quieras eliminar. 2- Selecciona la pestaña “Programador” (o “Developer” en inglés) de la parte superior de la pantalla de Excel. Aparecerá la cinta del programador justo debajo de la pestaña.

INFOTEP

11 *Aplica para: Office 2007, 2010, 2013 y 2016*

11

3- Encuentra el grupo “Código” (o “Code” en inglés) en la cinta del programador y haz clic en el botón “Macros”. Se abrirá la ventana de diálogo de macros en la pantalla de Excel.

4- Elige el nombre del macro que quieres eliminar de la lista que se encuentra en la ventana de diálogo del Macro. El nombre aparecerá resaltado en negro una vez que haya sido seleccionado. Por ejemplo, en mi caso, la macro que deseo eliminar se llama “boton_borrar”.

INFOTEP

12 *Aplica para: Office 2007, 2010, 2013 y 2016*

12

5- Haz clic en el botón “Eliminar_” (o “Delete” en inglés) del lado derecho de la ventana para indicarle a Excel que elimine la macro seleccionado. Aparecerá una ventana de diálogo preguntándote si estás seguro de que deseas eliminarlo.

6- Selecciona el botón “Sí” (Yes) para confirmar que deseas eliminarlo. Desaparecerá el nombre del macro de la lista en la ventana de diálogo y ésta se cerrará.

7- Puedes repetir los pasos anteriores para eliminar otros macros de una hoja de cálculo abierta.

4.4 Excel Solver. INFOTEP

13 *Aplica para: Office 2007, 2010, 2013 y 2016*

13

Solver es un complemento de Excel que nos ayuda a trabajar con modelos de negocio y nos permite resolver problemas lineales y no lineales. En esta ocasión mostraré cómo activar este complemento en Excel 2010. Cómo activar Solver en Excel Solver está incluido dentro de Excel pero se encuentra desactivado de manera predeterminada. Para poder habilitarlo debes ir a la ficha Archivo y elegir Opciones y se mostrará el cuadro de diálogo Opciones de Excel donde deberás seleccionar Complementos.

En el panel derecho encontrarás el complemento llamado Solver. Para activarlo debes hacer clic en el botón Ir de la sección Administrar.

INFOTEP

14 *Aplica para: Office 2007, 2010, 2013 y 2016*

14

Se mostrará el cuadro de diálogo Complementos y deberás marcar la casilla de verificación de Solver y aceptar los cambios.

INFOTEP

15 *Aplica para: Office 2007, 2010, 2013 y 2016*

15

Para utilizar el complemento Solver debes ir a la ficha Datos y Excel habrá creado un nuevo grupo llamado Análisis el cual contendrá el botón Solver.

Al hacer clic sobre ese botón se mostrará el cuadro de diálogo Parámetros de Solver el cual nos permitirá configurar y trabajar con el complemento recién instalado.

INFOTEP

16 *Aplica para: Office 2007, 2010, 2013 y 2016*

16

Utilizando Excel Solver. Si tienes la necesidad de realizar un pronóstico que involucra más de una variable, puedes utilizar Solver en Excel. Este complemento ayudará a analizar escenarios de negocio multivariable y de optimización.

Ejemplo de uso de Solver. El ejemplo es el siguiente. Tengo un establecimiento de venta de pizzas que ofrece dos tipos de pizza tradicionales, Pepperoni ($30) y Vegetariana ($35) además de la pizza especial Suprema ($45). No sabemos cuál es el potencial de ingresos del establecimiento y tampoco el énfasis que se debería de dar a cada tipo de pizza para maximizar las ventas. Antes de realizar el análisis debemos considerar las siguientes condiciones. Dada nuestra capacidad de producción solamente podemos elaborar 150 pizzas al día. Otra condición es que no podemos exceder de 90 pizzas tradicionales (Pepperoni y Vegetariana) y además, al no haber muchos vegetarianos en el área, estimamos vender un máximo de 25 pizzas vegetarianas al día. Otra condición a considerar es que solamente podemos comprar los ingredientes necesarios para producir 60 pizzas Suprema por día. Con esta información elaboraré la siguiente hoja de Excel:

INFOTEP

17 *Aplica para: Office 2007, 2010, 2013 y 2016*

17

Observa que en los datos están representadas todas las reglas de negocio del establecimiento. Para cada tipo de pizza he colocado el total de pizzas a vender (por ahora en cero), el subtotal de cada una, así como el total de ventas que esta formado por la suma de los subtotales. Además bajo el título Restricciones he colocado las condiciones previamente mencionadas. Algo muy importante es establecer las equivalencias para las restricciones. Por ejemplo, una restricción es que el total de pizzas no puede exceder de 150, pero Excel no necesariamente sabe lo que significa “Total de pizzas”, así que he destinado una celda para especificar que el total de pizzas es la suma de las celdas B2+B6+B10. Lo mismo sucede para explicar lo que significa Pizzas Tradicionales. Los datos ya están listos para utilizar Solver, así que debes ir a la ficha Datos y hacer clic en el botón Solver donde se mostrará el cuadro de diálogo Parámetros de Solver.

INFOTEP

18 *Aplica para: Office 2007, 2010, 2013 y 2016*

18

En nuestro ejemplo lo que queremos maximizar son las ventas totales por lo que en el cuadro de texto Establecer objetivo está especificada la celda $E$1 y por supuesto seleccioné la opción Máx. El otro parámetro importante son las celdas de variables que en nuestro ejemplo son las pizzas a vender para cada uno de los diferentes tipos. Finalmente observa cómo en el cuadro de restricciones están reflejadas las condiciones de venta del establecimiento. Pon especial atención a la manera en que se han utilizado las equivalencias que son las celdas $E$10 y $E$11. Todo está listo para continuar. Solamente debes hacer clic en el botón Resolver y Excel comenzará a calcular diferentes valores para las celdas

INFOTEP

19 *Aplica para: Office 2007, 2010, 2013 y 2016*

19

variables hasta encontrar el valor máximo para las ventas totales. Al término del cálculo se mostrará el cuadro de diálogo Resultados de Solver.

Solamente haz clic en Aceptar para ver los resultados en la hoja de Excel.

INFOTEP

20 *Aplica para: Office 2007, 2010, 2013 y 2016*

20

Excel ha hecho los cálculos para saber que, con las restricciones establecidas, tendremos un valor máximo de venta total de $5,525. Ahora fácilmente podrías cambiar los valores de las restricciones y volver a efectuar el cálculo con Solver para observar el comportamiento en las ventas.

INFOTEP

21 *Aplica para: Office 2007, 2010, 2013 y 2016*

1

ACTIVIDAD _______________

ACTIVIDAD No. 4 Ejercicio #6  Descargue y abra el archivo Ejercicio_6_Excel_Avanzado.  Realiza una sumatoria Sencilla de las tareas 1, 2 y 3 utilizando     

Macros (Ver hoja Macro Grabados). Realiza una sumatoria Sencilla del Total Tareas y Examen Final utilizando Macros (Ver hoja Macro Grabados). Colocar los nombres de las damas en color rosado y en los caballeros en azul utilizando Macros (Ver hoja Macro Grabados). Realice un Capture de la pantalla donde se muestre el código para edición del Macro que acabas de realizar, colócalo en la hoja Edición de Macro. Realice un Capture de la pantalla donde se muestre el lugar desde donde se puede eliminar el Macro que acabas de realizar, colócalo en la hoja Eliminar Macro. Guardar el libro con el nombre Ejercicio 6.

Ejercicio #7  Descargue y abra el archivo Ejercicio_7_Excel_Avanzado.  Utilizando la función Solver realizar un análisis Max como se   

indica en la siguiente hoja (Solver para Max). Utilizando la función Solver realizar un análisis Min como indica la siguiente hoja (Solver para Min). Utilizando la función Solver realizar un análisis Valor para Valor de 10,000,000 como indica la siguiente hoja (Valor para Valor de 10,000,000). Guardar el libro con el nombre Ejercicio 7.

INFOTEP

22 *Aplica para: Office 2007, 2010, 2013 y 2016*

22

RESUMEN ________________ RESUMEN En esta unidad usted aprendió a:              

¿Qué es una Macro de Excel?. Lenguaje de programación. Función de los macros en Excel. Automatización de tareas. Mostrar la Ficha Desarrollador. Grupos de la ficha Programador. Crear una macro en Excel utilizando el grabador de macros. ¿Cómo editar una Macro?. Editar una Macro Paso a Paso. ¿Cómo se elimina una Macro en Excel?. Excel Solver. Cómo activar Solver en Excel. Utilizando Excel Solver. Ejemplo de uso de Solver.

INFOTEP

23 *Aplica para: Office 2007, 2010, 2013 y 2016*

23...