HERRAMIENTAS EXCEL

HERRAMIENTAS EXCELRESUMEN INTRODUCTORIO A MACROSEl objetivo de esta seccin es, refrescar al participante de dichos procedimientos a fin de que pueda usarlos ya sea durante la grabacin de macros o cuando tenga que programar ciertas acciones que requieran del uso de tales procedimientos.FILTRO AVANZADOPara 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 dilogo Filtro avanzado seleccione dnde desea el listado, cul es el rango de criterios y a partir de qu celda se debe recibir los resultados.

Nota :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, slo que, para mayor facilidad se puede usar tambin nombre de rango. En la versin 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.

EjemploAbra el archivoPedidos.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 envo sea Speddy Express.

Solucin:Haciendo clic en el cuadro de nombres, apreciamos que los datos de la hojaPedidostiene por nombre de rango,Pedidosy 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 1Supongamos que se desea obtener los pedidos del cliente cuyo nmero 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 del grupo de la ficha .Completamos la ventana de dilogo que aparece, segn se muestra en la siguiente imagen:

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 envo pero repetido, como se muestra en la siguiente imagen:

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

CONSOLIDACINSe puede consolidar rangos de datos que estn en una misma hoja, rangos de datos que estn en diferentes hojas o igualmente rango de datos que corresponden a libros diferentes.

Se debe tomar en cuenta que la consolidacin se lleva a cabo sobre rangos que tiene la misma estructura, aunque el nmero de filas puede variar entre un rango y otro. Para los temas de consolidacin use el archivoVentas anuales.xlsxyVentas diarias.xlsx.

Si los rangos tuvieran columnas que no puedan ser consolidadas, puede dejar de incluirlas en la seleccin 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.

Consolidacin de rangos de una misma hoja

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

ObservacinEn 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..

Consolidacin de hojasEn este caso, los rangos de consolidacin se encuentran en hojas diferentes, aunque no necesariamente deben estar todos en hojas diferentes.La secuencia de acciones se muestra en la figura 03.

En esta imagen se ha seleccionado el rango correspondiente a la hojaSemana1y se procede a a la consolidacin laSemana2y todas las otras semanas.Use el archivo ventas diariasNOTA:A fin de llevar a cabo una consolidacin interesante, abra el archivoProyecciones.xlsy realice la consolidacin de las proyecciones de todos los meses.

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

Consolidacin de libros

Igualmente, si los rangos a ser consolidados estuvieran en libros diferentes, tambin se pueden realizar dicha accin. Para ello es necesario que estn abiertos todos los libros a ser consolidados as como un nuevo libro donde se realizar la misma.

Tablas dinmicasUna de las herramientas de mayor uso en el Excel es la tabla dinmica. Los datos a ser empleados provienen de una tabla o base de datos en general. Tiene la caracterstica de ser dinmica por cuanto los elementos que conforman su estructura pueden ser modificados (aadiendo o quitando campos) en el instante. Una tabla dinmica est ligada a un grfico dinmico y cualquier cambio de uno afecta al otro.Una tabla dinmica 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.

Si los datos que se tienen no estn en formato del Excel, no son consultas o no tienen un formato que permita acceder automticamente desde el Excel, podemos realizar el proceso de conversin mediante el uso de la secuencia: - - .

Las siguientes figuras muestran la secuencia de acciones necesarias para construir una tabla dinmica. El archivo a ser usado esComercial plaza.xlsx.

Para obtener una tabla dinmica en Excel siga el siguiente procedimiento:

Hacer clic en cualquier celda dentro del rango de los datos. - - - - Verificar si el rango que se muestra es el indicado - .

En la ventana siguiente seleccionar dnde se desea el resultado y luego hacer clic en . 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 numricos). Elija donde desea colocarla:

Luego de hacer clic en se obtiene la siguiente ventana:

Arrastre los campos de la Lista de campos hacia el rea donde desea se despliegue los valores de dicho campo. Al rea de datos deben ir uno o ms campos capaces de ser sumados o los campos categricos con la idea de contar su repeticin.Anlisis de datosEl Excel dispone de una herramienta llamada anlisis de datos que nos permite resolver problemas estadsticos que implican el uso de un determinado procedimiento a diferencia de las funciones estadsticas que emiten resultados independientes de un contexto. Entre los procedimientos disponibles en esta herramienta estn: Estadstica descriptiva, Muestreo aleatorio simple y sistemtico, anlisis de regresin mltiple, anlisis de covarianzas, anlisis de correlacin mltiple, algunos modelos de prueba de hiptesis, de anlisis de varianza de uno y dos factores, etc.La figura muestra la secuencia de acciones para obtener las estadsticas descriptivas de una serie de datos.

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