Manual Macros

Unidad 1. Las herramientas del Excel (I)

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.

Ejemplo

Abra el archivo Pedidos.xlsx. En la hoja Pedidos se tiene una lista de pedidos de 830 clientes, atendidos por un grupo de empleados y enviados a distintos destinos. En la hoja Detalles de pedidos, se tiene la lista de productos pedidos por cada cliente. Se desea obtener

a) Un reporte de los pedidos realizados por un cliente en particular.

b)Igualmente se desea obtener un reporte de todos los pedidos atendidos por dos empleados en particular y cuya forma de envío sea Speddy Express.

Solución

Haciendo clic en el cuadro de nombres, apreciamos que los datos de la hoja Pedidos tiene por nombre de rango, Pedidos y que la otra hoja tiene por nombre, Detalle.

Pregunta a)

Insertemos una nueva hoja; que su nombre sea Reporte 1.

Copiemos toda la cabecera (nombres de campo) de la hoja Detalles de pedidos y la pegamos en la primera fila de la hoja Reporte 1

Supongamos que se desea obtener los pedidos del cliente cuyo número de pedido es 11077. Para ello digitamos debajo de Id Pedido, 11077 (en la hoja Reporte)

Ahora realizaremos el procedimiento del Filtro Avanzado:

Estando en la hoja Reporte, hacemos clic en <Avanzadas> del grupo <Ordenar y filtrar> de la ficha <Datos>.

Completamos la ventana de diálogo que aparece, según se muestra en la siguiente imagen:

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 <Referencia> se han seleccionado cada rango y luego se hizo clic en <Agregar>. Esto para cada rango a ser consolidado..

Figura 2

Consolidación de hojas

En este caso, los rangos de consolidación se encuentran en hojas diferentes, aunque no necesariamente deben estar todos en hojas diferentes.

La secuencia de acciones se muestra en la figura 03.

Figura 3

En esta imagen se ha seleccionado el rango correspondiente a la hoja Semana1 y se procede a <Agregar> a la consolidación la Semana2 y todas las otras semanas.

Nota 3

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: <Datos> - <Obtener datos externos> - <Importar datos>.

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.

<Datos> - <Informe de Tablas y Gráficos dinámicos> - <Lista o base de datos de Microsoft Excel> - <Siguiente> - Verificar si el rango que se muestra es el indicado - <Siguiente> . 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 <Insertar>. El cursor debe estar dentro de los datos a fin de disponer del rango ya seleccionado como se muestra en (3). En esta

...