Programación de macros en excel, Apuntes de Informática

¡Descarga Programación de macros en excel y más Apuntes en PDF de Informática solo en Docsity!

Aviso legal: todos los textos y pantallas están extraidos del curso de macros de aulaclic.

Unidad 1. Las herramientas del Excel

Introducción

Antes de ingresar al desarrollo del Lenguaje Visual Basic para Aplicaciones, haremos una breve exposición de algunas herramientas del Excel que se supone son conocidas por nuestro amable lector. Sin embargo, para unificar criterios y con el ánimo de recordar lo conocido, haremos una exposición de la secuencia de pasos que se sigue para ejecutar o usar determinadas herramientas.

Este repaso nos servirá también para mostrar dicha secuencia usado en las versiones Excel 2003 y Excel 2007.

El objetivo de esta sección es, entonces, refrescar al participante de dichos procedimientos a fin de que pueda usarlos sea durante la grabación de macros o cuando tenga que programar ciertas acciones que requieran del uso de tales procedimientos.

En el numeral 1 presentamos la secuencia de pasos para realizar o ejecutar la herramienta con algunas observaciones y comentarios. En el numeral 2 daremos algunos ejemplos de grabación de macros que hagan uso de determinadas herramientas. En el numeral 3 desarrollaremos algunas macros que nos permitan realizar algunas operaciones en Excel de uso frecuente. Esta introducción termina con una breve exposición de la filosofía de objetos en la Programación Orientada a Objetos (POO), técnica empleada en la programación de macros.

Herramientas del Excel

Filtro avanzado

Figura 1

Usamos filtro avanzado para poder extraer, sea en una misma hoja o en otra, ciertos registros de una base de datos que cumplen determinadas condiciones.

Para realizar el filtro avanzado es necesario definir previamente el rango de criterios así como conocer el rango de datos y la celda, a partir de la cual se deben emitir los resultados.

El rango de criterios debe contener en su primera fila, los nombres de los campos (columnas) y en las siguientes filas contener los valores o criterios del filtrado.

La imagen que se presenta en la Figura 01, nos muestran la secuencia de pasos que se debe seguir para realizar el filtro avanzado.

En la ventana de diálogo Filtro avanzado seleccione dónde desea el listado, cuál es el rango de criterios y a partir de qué celda se debe recibir los resultados.

Nota 1:

En el caso de Excel 2003, si desea extraer lo filtrado hacia otra hoja, entonces debe definir el rango de los datos con un nombre de rango. Si desea, el rango de criterios puede estar otra hoja, sólo que, para mayor facilidad se puede usar también nombre de rango. En la versión 2007 no es necesario que el rango tenga nombre, pero sí que el procedimiento se ejecute estando en la hoja hacia donde se desea el resultado.

A la derecha se aprecia una parte de dicho reporte.

Pregunta b)

Inserte una nueva hoja y que se nombre sea Reporte 2. Copiaremos la cabecera de la hoja Pedidos y la pegamos en esta nueva hoja. Supongamos que se desea obtener un reporte de los pedidos atendidos por Buchanan, Steven y por Davolio, Nancy. Esto significa que debemos ingresar estos nombres debajo de la columna Empleado y "Speddy Express" lo ingresamos debajo de Forma de envío pero repetido, como se muestra en la siguiente imagen:

A continuación realice el mismo procedimiento anterior digitando en Rango de la lista: Pedidos. La siguiente imagen muestra parte de este reporte.

Consolidación

Se puede consolidar rangos de datos que están en una misma hoja, rangos de datos que están en diferentes hojas o igualmente rango de datos que corresponden a libros diferentes.

Nota 1:

Se debe tomar en cuenta que la consolidación se lleva a cabo sobre rangos que tiene la misma estructura, aunque el número de filas puede variar entre un rango y otro. Para los temas de consolidación use el archivo Ventas anuales.xlsx y Ventas diarias.xlsx.

Nota 2:

Si los rangos tuvieran columnas que no puedan ser consolidadas, puede dejar de incluirlas en la selección y usar las siguientes o trasladarlas al final a fin de que se pueda elegir un rango adecuado. Siempre consolida tomando en cuenta la primera columna del rango.

Consolidación de rangos de una misma hoja

La Figura 2 muestra la secuencia de pasos para realizar una consolidación de rangos dentro de una misma hoja de un libro.

Observación

En la imagen de la Figura 2, se han consolidado cuatro rangos contenidos en la misma hoja. Haciendo clic en el cuadro se han seleccionado cada rango y luego se hizo clic en . Esto para cada rango a ser consolidado..

A fin de llevar a cabo una consolidación interesante, abra el archivo Proyecciones.xls y realice la consolidación de las proyecciones de todos los meses.

Nota 4

Recuerde que, si desea una consolidación efectuando una suma, promedio, etc. Sobre la misma columna de consolidación, dicha columna debe tener la misma cabecera en todos los componentes (rangos) de la consolidación. En este último caso, en lugar de tener 2001, 2002, etc, se debiera tener un mismo rótulo.

Consolidación de libros

Igualmente, si los rangos a ser consolidados estuvieran en libros diferentes, también se pueden realizar dicha acción. Para ello es necesario que estén abiertos todos los libros a ser consolidados así como un nuevo libro donde se realizará la misma.

Tablas dinámicas

Una de las herramientas de mayor uso en el Excel es la tabla dinámica. Los datos a ser empleados provienen de una tabla o base de datos en general. Tiene la característica de ser dinámica por cuanto los elementos que conforman su estructura pueden ser modificados (añadiendo o quitando campos) en el instante. Una tabla dinámica está ligada a un gráfico dinámico y cualquier cambio de uno afecta al otro.

Una tabla dinámica puede construirse a partir de los datos contenidos en un libro del Excel, con los datos provenientes de una consulta por ejemplo del MS Access o directamente del MS Query.

Del mismo modo, una tabla dinámica puede ser construida desde un cubo OLAP (On Line Analitical Processing), aunque éste ya tiene diseñado las dimensiones (cada una de las cuales generan de por sí una tabla), podemos insertar en la misma tabla más de una dimensión, enriqueciéndola aún más.

Si los datos que se tienen no están en formato del Excel, no son consultas o no tienen un formato que permita acceder automáticamente desde el Excel, podemos realizar el proceso de conversión mediante el uso de la secuencia: - - .

Las siguientes figuras muestran la secuencia de acciones necesarias para construir una tabla dinámica. El archivo a ser usado es Comercial plaza.xlsx.

Figura 4

Nota 1:

Para obtener una tabla dinámica en Excel 2003 siga el siguiente procedimiento:

Hacer clic en cualquier celda dentro del rango de los datos.

- <Informe de Tablas y Gráficos dinámicos> - - - Verificar si el rango que se muestra es el indicado - . En la ventana siguiente seleccionar dónde se desea el resultado y luego hacer clic en <Diseño>. En esta ventana debe seleccionar el o los campos que debe colocar en el área de fila, en el área de columna y en área de datos (a los cuales de preferencia se debe colocar los campos numéricos).

Nota 2:

Como se aprecia en (1), se debe hacer clic en la <Tabla dinámica> de la ficha . El cursor debe estar dentro de los datos a fin de disponer del rango ya seleccionado como se muestra en (3). En esta ventana se debe decidir si la tabla se desea en una nueva hoja o en la hoja donde están los datos. Luego de hacer clic en se obtiene la siguiente ventana:

En Excel 2007:

Al hacer clic en la ficha obtendrá las siguientes opciones

Haga clic en y seleccione la opción correspondiente al formato en el cual se encuentra los datos a ser convertidos al formato del Excel.

En ambos casos y a partir de ello, seguir la secuencia del asistente para importar datos. En ella seleccione la opción que le corresponda y tomando en cuenta el separador de campo. Para ello sería bueno abrir el archivo fuente usando algún programa como un editor de textos como el bloc de notas o algún otro.

Nota 6:

En los casos en los que se desea crear una tabla dinámica con datos de otras fuentes,

En el numeral 3, de la gráfica inicial de tabla dinámica, use la opción de la ficha , si desea acceder a archivos de consulta grabados usando el MS Query o para crear cubos OLAP o acceder a uno de ellos.

Análisis de datos

El Excel dispone de una herramienta llamada análisis de datos que nos permite resolver problemas estadísticos que implican el uso de un determinado procedimiento a diferencia de las funciones estadísticas que emiten resultados independientes de un contexto. Entre los procedimientos disponibles en esta herramienta están: Estadística descriptiva, Muestreo aleatorio simple y sistemático, análisis de regresión múltiple, análisis de covarianzas, análisis de correlación múltiple, algunos modelos de prueba de hipótesis, de análisis de varianza de uno y dos factores, etc.

La figura 5 muestra la secuencia de acciones para obtener las estadísticas descriptivas de una serie de datos.

Figura 5

Nota:

El uso de cada una de las opciones de esta herramienta requiere de conocimientos estadísticos tanto descriptivos como inferenciales.

Aplicaciones de las macros

En una consolidación

Procedimiento para grabar una macro:

En Excel 2003:

Puede usar una de las siguientes opciones:

Activar la barra de herramientas de Visual Basic y hacer clic en el botón . Puede usar la secuencia - - .

En Excel 2007:

Debe tener activada la ficha del Programador.

Hacer clic en Grabar macro del grupo Código

A continuación y en ambas versiones, debe dar nombre a la macro (sin dejar espacio en blanco); ingresar una tecla para el método abreviado y verificar dónde desea grabar la macro: en el libro actual, en un nuevo libro o en el libro de macros personal.

En una tabla dinámica

Ejemplo

Crear una tabla dinámica básica usando los datos del archivo TablaDin01.xls. Suponer que las hojas son datos copiados desde otros libros provenientes de la zona Norte, Oeste y Este.

Solución

Al abrir este archivo veremos que los datos se encuentran en tres hojas, cada una de las cuales contiene las ventas en las zonas Norte, Oeste y Sur.

El objetivo es disponer de una nueva hoja que contenga los datos de todas las zonas y a partir de ella, se pueda crear la tabla dinámica básica. Llamamos tabla dinámica básica pues la macro creará una con los mínimos elementos; a partir de la cual se puede añadir o remover los campos de las áreas respectivas.

La macro se llamará TablaDinamica01. El método abreviado, +.

Secuencia de acciones que debe hacerse al grabar la macro:

Insertar una nueva hoja, darle nombre: Datos Copiar toda la hoja Norte hacia la hoja Datos, a partir de la celda A Copiar sólo los datos de Oeste hacia Datos, a partir de la primera fila vacía Copiar sólo los datos de Sur hacia Datos, a partir de la primera fila vacía Ubicarse al interior de la hoja Datos Usar la secuencia para crear la tabla dinámica diseñando la siguiente estructura.

Zona

Tie nda Forma^ de Pago

Suma de Monto

Detener grabación de la macro

En Filtro avanzado

Otra de las herramientas que podemos automatizar mediante el uso de macros es la de Filtro Avanzado.

La potencia de la macro está en el hecho de modificar los criterios de filtrado no tanto en la ejecución de la macro con los valores iniciales, que de por sí serían bastante rígidos.

Habiendo establecido un valor para el filtrado, podemos modificar dicho valor o agregar otros (en la misma fila, no más columnas). Puesto que la salida será siempre a partir de la celda ingresada durante la grabación de la macro y, estando ocupado dicho rango con una ejecución anterior, podemos grabar nueva macro que permita borrar la cabecera en la salida y volver ejecutar la macro.

Ejemplo 1

Grabar una macro para extraer todos los pedidos atendidos un determinado empleado. Use el archivo Pedidos.xls.

Procedimiento:

Abrir el archivo Pedidos.xls Insertar una hoja, colocarla al final y darle nombre Filtrado. Supongamos que se desea extraer los pedidos atendidos por el empleado Davolio, Nancy con forma de envío, Speedy Express. Se ingresa esos datos. Iniciamos la grabación de la macro. Noimbre: MacFiltro01; método abreviado: + Estando en grabación: En Excel 2003: - - En Excel 2007: Ficha Datos – Clic en del grupo . En ambas versiones: - - <Rango de criterios: A1:L2 - - Detener grabación

Grabemos otra macro llamada MacBorrar01; método abreviado: + que seleccione el rango A10:L10 y lo borre; luego detener grabación.

Ahora ejecute la macro MacFiltro01 usando el método abreviado.

Cambie el nombre del empleado por otro de la hoja Pedidos y antes de volver a ejecutar la macro, borre la cabecera de la salida, ejecutando le macro MacBorrar01.

Si se desea el detalle de otro cliente, será suficiente copiar o digitar el Id de pedido del cliente deseado de la salida de la MacFiltro01 y ejecutar la macro MacFiltro borrando previamente la salida con MacBorrar02.

Botones de control de formularios para ejecutar marcos

Como habrá podido comprobar, el uso del método abreviado para ejecutar una macro es limitado sea por que no podemos usar cualquier letra ya que inhabilitaría las definidas por omisión, sea por que en un determinado libro podemos tener muchas macros y no sabríamos distinguir una de otra, o por alguna otra razón.

Para evitar esto, el Excel dispone de un conjunto de elementos (botones) de control o de formulario a los cuales podemos asignarle una determinada macro. De esta manera, no tenemos limitación alguna pues podemos tener tantos botones como macros tengamos en un libro.

Estos botones de control son de dos tipos:

Los llamados " Controles Activex " y que conforman la barra de herramientas del Cuadro de controles, y Los llamados " Controles de Formulario " y que conforman la barra de herramientas de Formulario.

Los primeros requieren de una programación (módulo) a la cual se les asigna y el uso de los botones de Formulario son más simples. Estos pueden ser directamente asignados a una macro o pueden ser usado para manipular listas, textos, barra de desplazamiento, casillas de verificación o selección, etc. Entre ellas disponemos del botón llamado Botón Botón o Button.

La imagen anterior corresponde a la barra de herramientas de Formulario, en el caso del Excel 2003. Sin embargo en el caso de la versión 2007, se accede a estos botones haciendo clic en el comando Insertar del grupo Controles de la ficha del Programador , como se muestra en la siguiente imagen:

Procedimiento para asignar un botón a una macro:

En el caso del Excel 2003:

Paso 1: Active la barra de herramientas de Formulario usando la siguiente secuencia:

- -

En el caso del Excel 2007

Paso 1: - Desplegar la lista de del grupo Controles.

Paso 2: Haga clic en el botón "Botón" y trace un rectángulo (dibuje) en una parte de la hoja donde desee colocar el botón

Paso 3: Si al soltar el botón del mouse, no se abre una ventana, haga clic con el botón derecho del mismo y seleccione la opción . Seleccione el nombre de la macro al que desea asignar el botón y luego haga clic en .

Para ejecutar la macro es suficiente hacer clic en dicho botón.

Ejemplo 1

Abra el archivo Pedidos.xls. Vaya a la hoja Filtrado. Vamos a obtener un listado de todos los pedidos atendidos por Davolio, Nancy. Para ello digite dicho nombre en C2.

Ahora, usando el procedimiento, descrito líneas arriba, inserte un botón en G7 y cuando tenga que ingresar el nombre de la macro, seleccione MacFiltro01. Luego haga clic en Aceptar.

Vamos a cambiar de nombre al botón. Haga clic con el botón derecho del mouse. Como verá, se despliega un menú contextual que no nos interesa. Presione la tecla y digite Filtrar Pedidos.