• Author / Uploaded
• Deivi Ruiz

• Categories
• Macro (informática)
• Microsoft Excel
• Point and Click
• Software
• Informática

Citation preview

EJERCICIO 27 DE EXCEL

EXCEL EJERCICIO 27

FORMULARIOS Y MACROS

Nota: la materia es tan extensa que dividiremos el ejercicio en dos (27 y 28) con el fin de no cansar demasiado. Al igual que el Word, el Excel proporciona herramientas para crear formularios, aunque las posibilidades que ofrecen los formularios de Excel son bastante mayores. Es posible, por ejemplo, vincular el contenido de un control de formulario a una celda y utilizar luego ese contenido en otras fórmulas o funciones. Así, es posible, por ejemplo, que al elegir una opción de un cuadro combinado, dicha opción permita la aparición de múltiples valores en otras celdas (p.ej, a través de funciones de búsqueda). Además, los controles de formulario pueden asociarse a macros de manera que se puedan realizar tareas complejas con un solo clic.

ACTIVIDAD Vamos a ver cada uno de los principales controles de formulario y su utilidad en algún ejemplo. En primer lugar, abre un documento nuevo de Excel y guárdalo en el pendrive con el nombre 27ex Formularios. Al guardarlo, en el apartado Tipo elige la 2ª opción (Libro de Excel habilitado para macros); se crea un archivo con extensión .xlsm. Haz clic en la pestaña Programador de la cinta de opciones (desde la que se crean y manejan los formularios). Este apartado es el que nos interesa.

Antes de empezar, decir que aquí utilizaremos los controles de formulario pero no los controles ActiveX (pensados más para la elaboración de formularios a cumpli1

EJERCICIO 27 DE EXCEL mentar online y ligados normalmente a más o menos complejas secuencias de código en Visual Basic, escritas por el usuario)

BOTÓN Este control sólo tiene sentido asociado a una macro. Por eso, al insertarlo ya se nos ofrece directamente la posibilidad de crear y nombrar una macro asociada al mismo. Veamos un ejemplo sencillo: crearemos un botón que borre el contenido de un rango, concretamente A1:A5

PROCEDIMIENTO 1º- Llama a la hoja 1 Botón de comando. Introduce unos datos cualesquiera en el rango A1:A5 de dicha hoja.

2º- En el apartado Controles de la pestaña Programador haz clic en la flecha del botón Insertar y, luego, dentro de Controles de formulario, en el icono de botón.

3º- Más o menos a la altura de C3 (para que esté próximo al rango a borrar), dibuja el botón como si fuera una autoforma. Al dejar de hacer clic aparecerá el siguiente cuadro:

Haz clic en Grabar…

2

EJERCICIO 27 DE EXCEL

Como nombre de la macro, escribe Borrardatos (sin espacios). Deja las demás opciones como están (si quisieras disponer de esta macro en cualquier documento creado a partir de ahora, en Guardar macro en deberías seleccionar Libro de macros personal)

4º- Selecciona las celdas del rango A1:A5 y borra su contenido con la tecla Supr. 5º- En la pestaña Programador, en el apartado Código, haz clic en el botón Detener grabación.

6º- Haz clic derecho sobre el botón para seleccionarlo. Luego, haz clic izquierdo en el interior del botón; borra el texto del mismo y escribe Borrar. Haz también el botón algo más alto para que el texto quede bien centrado.

7º- Finalmente, haz clic en cualquier punto de la hoja de datos y, luego, clic sobre el botón Borrar. Los datos de A1:A5 quedarán borrados. Vuelve a escribirlos, a fin de facilitar la corrección del ejercicio.

8º- Sólo por curiosidad, le echaremos un vistazo a la macro recién creada, en código VisualBasic. Para ello, haz clic en el botón Visual Basic de la pestaña Programador.

9º- Con ello se abre la ventana del editor de Visual Basic para Excel. El panel izquierdo tiene el siguiente aspecto:

3

EJERCICIO 27 DE EXCEL

En el panel derecho se muestra el código de la macro creada

Significado: “selecciona el rango A1:A5 y borra el contenido de la selección.”

Haz doble clic sobre el Módulo1 del archivo 27ex Formularios.xlsm

Todas las macros comienzan con Sub nombremacro() En verde aparecen etiquetas descriptivas (esta no lo es mucho) que no ejecutan acciones

End Sub cierra la macro.

10º- Finalmente, cierra la ventana de Visual Basic y vuelve al libro de Excel. Una macro de borrado de datos puede ser muy útil para evitar tareas repetitivas en muchas hojas, adaptando, lógicamente, el rango o rangos a borrar. Al contrario que en Word, en Excel se pueden grabar como parte de una macro tanto clics de ratón tanto como secuencias de teclado. No obstante, cuando se graban secuencias demasiado complejas, es muy frecuente que al intentar ejecutar la macro obtengamos algún mensaje de error.

4

EJERCICIO 27 DE EXCEL CUADRO COMBINADO Al igual que en Word, es un cuadro desplegable que permite elegir una de entre varias opciones. En Excel, sin embargo, las opciones serán las incluidas en algún rango de la misma hoja, una hoja distinta o incluso de otro libro. Esto le da cierta ventaja sobre la regla de validación de datos tipo Lista, que obliga a seleccionar la lista dentro de la misma hoja (aunque, para ser honestos, hay una forma de sortear esta limitación para dicha regla de validación: nombrar el rango y usar el nombre en la regla). Vamos a crear un cuadro combinado que muestre el nombre de nuestros clientes. Al seleccionar uno de ellos, aparecerá abajo su teléfono.

PROCEDIMIENTO 1º- Llama Cuadros a la hoja 2 y Lista cuadros a la hoja 3. 2º- En la pestaña Programador, haz clic en la flecha del botón Insertar y, luego, en el botón de formulario Cuadro combinado. Dibuja un cuadro combinado que ocupe exactamente el área de la celda C2.

3º- Deja la hoja como se muestra a continuación: 4º- En la hoja Lista cuadros introduce lo siguiente:

5

EJERCICIO 27 DE EXCEL

5º- Vuelve a la hoja Cuadros. Haz clic derecho sobre el cuadro combinado y elige la opción Formato de control.

Como origen del cuadro, ve a la hoja Lista cuadros y selecciona los nombres de los clientes.

Vincula el cuadro combinado al contenido de la celda C2 (como está debajo, no se ve; pero, según la opción que elijamos, en C2 aparecerá 1, 2, 3 o 4) Luego, acepta.

6º- En la celda C3, introduce una función BUSCARV para que, en función del contenido de C2 (tendrás que escribir la referencia de celda), aparezca el teléfono del cliente que hemos seleccionado en el cuadro combinado. Complementa la función con otra SI o SI.ERROR para evitar el mensaje de error en caso de que aún no se haya seleccionado nada. 7º- Finalmente, selecciona sucesivamente a los diferentes clientes del cuadro combinado y observa cómo aparece su número de teléfono en C3. En realidad, la principal ventaja de un cuadro combinado respecto a una regla de validación tipo Lista es que, al ser el cuadro combinado un objeto insertado en la hoja, le podemos asignar una macro. Así, por ejemplo, en un impreso de factura podríamos crear una macro que borrara los datos concretos de la operación y asociarla a un cuadro combinado con los nombres de los clientes. De esa forma, al elegir un nuevo cliente en el cuadro combinado, se borrarían automáticamente los datos de la factura anterior (si es que eso realmente nos interesa).

6

EJERCICIO 27 DE EXCEL CUADRO DE LISTA Aunque no sea la siguiente en el grupo de controles, la veremos aquí por su semejanza con el cuadro combinado. De hecho, viene a ser un cuadro combinado cuyo contenido es visible en todo momento. Por lo demás, sus características, configuración y posible utilidad son (aproximadamente) las mismas que las del cuadro combinado.

ACTIVIDAD Añade una columna a la lista de la hoja Lista cuadros tal como se muestra:

En la hoja Cuadros inserta un cuadro de lista que muestre los nombres de los clientes. El cuadro ocupará (aproximadamente) el área del rango B8:B10. Vincúlalo a la celda B8. Añade a la pequeña tabla de B2:C3 una nueva fila para la dirección de la empresa (ajusta el formato). En la celda C4 incluye una función BUSCARV que obtenga la dirección de la lista de la hoja Lista cuadros a partir del cliente elegido en el cuadro de lista.

El aspecto final de la hoja Cuadros será similar a esto (el cliente seleccionado puede ser distinto).

Ten en cuenta que, en nuestro ejemplo, de momento, es posible que en el cuadro combinado hayamos seleccionado un cliente y en la lista, otro distinto. Para evitar esto, cambia la celda vinculada del cuadro combinado a B8 (la misma que el cuadro de lista). El procedimiento es básicamente el mismo ya visto para el cuadro combinado.

7

EJERCICIO 27 DE EXCEL CASILLA DE VERIFICACIÓN Al igual que en el Word, permite indicar si se cumple un determinado requisito o condición (si se ha pagado la mercancía, si se ha recibido el pedido, si se es mayor de edad, etc.).

ACTIVIDAD En el libro 27ex Formularios, en una nueva hoja que llamarás Casillas de verificación, crea el siguiente cuadro:

Un profesor ha de calificar un examen a 3 alumnos. A aquellos que hayan presentado un trabajo extra, les sumará un punto a la nota de examen. En las celdas de la columna Trabajo presentado inserta casillas de verificación que funcionen así: -

-

Si la casilla está desactivada (no se ha presentado el trabajo), en la columna TOTAL se calcula simplemente la nota de examen, sumando la puntuación de las preguntas 1, 2 y 3 Si la casilla está activada (se ha presentado el trabajo), además se suma un punto a la nota de examen (TOTAL), siempre que el resultado no sea superior a 10 (en cuyo caso, la nota total será 10)

Nota: el mismo resultado se podría conseguir sin necesidad de casillas de verificación (escribiendo SÍ o NO en las celdas de la columna Trabajo presentado); con las casillas, sin embargo, damos a la hoja un aspecto más profesional. Además, al igual que con los demás controles, podemos asignar a la casilla (según la activemos o no) una macro cualquiera.

PROCEDIMIENTO 1º- Crea la nueva hoja y, en ella, el cuadro mostrado arriba. Selecciona las celdas de la columna Trabajo presentado y elige color blanco para el texto de las celdas.

2º- En la pestaña Programador, haz clic en la flecha de Insertar y, luego, en el icono Casilla de verificación (del grupo Controles de formulario)

8

EJERCICIO 27 DE EXCEL 3º- Haz clic en la primera celda de la columna Trabajo presentado. Se inserta una casilla con una etiqueta; para borrar el texto, sencillamente selecciónalo y bórralo con el botón Supr. Luego, reduce el ancho del control tanto como se pueda y muévelo (con Ctrl + flechas de dirección) para centrarlo en la celda.

4º- Haz clic derecho en la casilla y selecciona Formato de control; configura el cuadro de diálogo como se muestra (luego, acepta):

Esta celda ha de ser la misma en que has insertado la casilla (sea C4 u otra)

Con esto, vinculamos el estado de la casilla (activado o desactivado) con el contenido de la celda en que está la casilla (podría ser también cualquier otra celda); como antes hemos aplicado color blanco al texto de la celda, el resultado (VERDADERO para “activada” y FALSO para “desactivada”) no se verá.

5º- Haz lo mismo para las otras dos casillas, vinculando cada una a su celda respectiva.

6º- En las celdas de la columna TOTAL introduce una función SI que sume las calificaciones de las 3 preguntas si la casilla está desactivada y que añada a lo anterior un punto extra, si está activada. Salvo que la suma de las preguntas de examen más el punto extra sumen más de 10; en ese caso, la nota total será 10. En la prueba lógica (tanto en la función SI como en la Y), para indicar que en la celda correspondiente de la columna Trabajo presentado pone VERDADERO, escribe únicamente la referencia (relativa) de esa celda (p.ej, C4), sin ningún operador ni término de comparación (dado que aquí VERDADERO es un valor lógico y no un texto). Prueba la hoja, introduciendo datos de ejemplo en las celdas correspondientes a las preguntas del examen y activando alguna de las casillas de verificación insertadas.

9