Excel PE - Modulo I

grupoconocimiento para crecer

EXCEL PARA EXPERTOS

GRFICOS Y TABLAS DINMICAS EN EXCEL Expositor: Giovanni Cabrera

www.bsgrupo.com

Material de Estudio

REGLAMENTO DE BS GRUPO

NORMAS DE EJECUCIN DE PROGRAMAS PARA ALUMNOS I.LOS PROGRAMAS EDUCATIVOS: 1.1. Los Programas Educativos, tienen como objetivo fundamental el consolidar la formacin acadmica y/o la experiencia de los participantes a travs de la adquisicin de conocimientos actuales en los diferentes temas que comprende cada PROGRAMA. II.DIRECCION: 1.2. Los Programas Educativos, dependen de la Direccin Acadmica en gestin de BS GRUPO. Cada Programa cuenta con un Coordinador(a) Acadmico. III.DERECHOS Y DEBERES 3.1 Son derechos de los participantes: 3.1.1 Recibir una formacin completa de acuerdo a la estructura curricular y perfil profesional ofrecidos para cada programa. 3.1.2 Ser informado a cabalidad de las disposiciones que le competen como participante. 3.1.3 Recibir la certificacin correspondiente de acuerdo a los estudios y requisitos exigidos para cada Programa. 3.2 Son deberes de los participantes: 3.2.1. Portar la acreditacin correspondiente a cada Programa. 3.2.2. Cumplir con las normas y dems disposiciones de los Programas. 3.2.2. Cumplir con la asistencia y la exigencia acadmica de nuestros Programas. 3.2.3. Cumplir puntualmente con los pagos correspondientes y otros derechos. IV.NORMASACADEMICAS 4.1 Cada Programa consta de un conjunto de cursos distribuidos segn un cronograma, con una frecuencia especfica. 4.2 Los horarios en los que se dictan las asignaturas son establecidos en cada Programa, los cuales podrn ser modificados temporalmente de comn acuerdo y segn la disponibilidad de horario del docente y de contar con los ambientes necesarios.

1


4.3 De los participantes El cupo mximo en el programa es de 20 participantes. Existiendo dos tipos de participantes: aquellos que participan en la totalidad de programa, y otros que participan en algn(os) cursos especficos del programa. 4.4 Asistencia y Puntualidad 4.4.1 Para aprobar una asignatura es requisito indispensable asistir a no menos del 70% de las horas acadmicas de la misma. 4.4.2 Los participantes debern ingresar al aula antes de la hora sealada para el inicio de clases. Luego de tomada la asistencia no se permitir el ingreso al aula hasta el descanso intermedio. Los participantes tendrn una tolerancia de 10 minutos pasada la hora de inicio, luego de los cuales se cerrar la puerta y se dar inicio a la sesin de clases. No se fuma ni se permiten celulares encendidos dentro del saln de clase. 4.5 Metodologa de Enseanza 4.5.1 Al inicio de cada asignatura el participante recibir el silabo respectivo en el que se especificar los alcances del curso y el sistema de evaluacin. 4.5.2 Recibir tambin un CD con las exposiciones completas y material de lectura para el ptimo desarrollo del curso. 4.5.3 Los trabajos solicitados se entregarn en la fecha sealada por el profesor y debern ser presentados segn los formatos y requerimientos del mismo. 4.5.4 Los Programas tienen como base de su desarrollo la participacin y discusin en clase. Por tanto los participantes debern realizar sus lecturas previas y estar preparados para hacer preguntas y comentarios. 4.6 Evaluaciones 4.6.1 El sistema de evaluacin de Los Programas incluye la medicin cualitativa y cuantitativa del proceso de aprendizaje. 4.6.2. La escala de evaluacin es de 01 (uno) a 20 (veinte), siendo la nota 13 (trece) el mnimo aprobatorio. 4.6.3..Toda asignatura ser evaluada de acuerdo al sistema de evaluacin especificado por cada profesor en el perfil del curso. Debiendo tener una evaluacin semanal. 4.6.4 La evaluacin final deber recoger todos los objetivos del curso, y se realizar al finalizar este. 4.6.5 Las evaluaciones se tomarn en fecha nica; no existiendo recuperacin, evaluacin de aplazados ni subsanacin. 4.6.6 Los participantes que no se presenten a una evaluacin obtendrn el calificativo de 0 (cero). Si el motivo fue un problema de salud podr 2


presentar un Certificado Mdico en un plazo no mayor de una semana en el que segn el Certificado Mdico terminaba el periodo de incapacidad, trmite que ser evaluado por el Comit Acadmico presidido por el Director de Capacitacin. La evaluacin en fecha especial ser realizada previo pago de los derechos correspondientes de ser necesario. 4.6.7 Para efectuar cualquier reclamo, el participante deber sustentarlo por escrito a travs del Coordinador del Programa. 4.6.8 Los participantes podrn efectuar cualquier reclamo inmediatamente despus de la entrega de la nota; en caso contrario se dar como aceptada la nota obtenida y no cabe reclamo alguno. 4.7 Excepciones y casos extraordinarios 4.7.1 Las excepciones y/o casos extraordinarios no contemplados en el presente reglamento sern evaluados y dictaminados por un Comit Acadmico presidido por el Director de Capacitacin respecto de las razones expuestas por escrito por el(los) participante(s) del Programa involucrados segn el caso. V. CERTIFICACIN 5.1 BS GRUPO expide a los estudiantes que lo soliciten, la constancia de estudios de los modulos cursados. 5.2 Para obtener el certificado del Programa correspondiente es necesario: a) Haber aprobado satisfactoriamente el 100% de los mdulos del Programa de Capacitacin b) No tener ninguna deuda (econmica o libros) con BS GRUPO. VI. RETIROS 6.1 Los retiros acadmicos no eximen a los participantes de las obligaciones econmicas contradas con BS GRUPO y/o con la institucin que los inscribe. Estos debern ser asumidos en su totalidad. VII. DISPOSICIONES GENERALES Todos los alumnos deben tener durante el desarrollo del Programa un comportamiento tico intachable. Cualquier conducta contraria, ser causal de retiro inmediato, sin posibilidad alguna de reincorporacin posterior. A cada alumno, se le analizar su ranking acadmico y asistencia, una vez finalizado cada mdulo. Se generar un informe, el cual ser comunicado al alumno para evaluar su rendimiento.

3

Grficos y Tablas Dinmicas en EXCEL

Excel para Expertos

ExpositorGiovanni Cabrera Mlaga (Per)

Programa Profesional de Ingeniera de Sistemas, Universidad Catlica de Santa Mara, Arequipa (1997 2001). Profesional Tcnico en Computacin y Administracin de Sistemas Computacin e Informtica, I.S.T.P. baco, Arequipa (1993 1996). Ha desempeado sus labores en el MINISTERIO DE TRANSPORTES (MTC), MINAS OCOA S.A., I.S.T.P. IBC TECHNOLOGY INSTITUTE, I.E.S. ESSAM. Actualmente desempea sus labores como Docente en la Carrera Profesional de Computacin e Informtica en el I.S.T. CESCA y Docente en la rama de Tecnologas de Informacin en la empresa BS Grupo S.A.C. Docente de Informtica en el Grupo IDAT.



Excel para Expertos

PERFIL DEL PROGRAMA DE EXCEL PARA EXPERTOS AREQUIPA - 2011 SILABOI. Curso Cdigo del curso Programa Profesor Email Duracin INFORMACIN GENERAL : : : : : : Grficos y Tablas Dinmicas en Excel TI EXCEL PE 2011 IV AQP Excel para Expertos Arequipa 2011 Giovanni R. Cabrera Mlaga [email protected] 16 Horas

II.

PRESENTACIN

El presente programa es un efectivo curso educativo que permite a estudiantes y profesionales que ya conocen Excel bsico e intermedio aprender a aplicarlo en situaciones de la vida real y perfeccionarse en su utilizacin.

III.

OBJETIVOS GENERALES Formar al alumno de una manera eficaz y prctica para la resolucin de problemas reales y cotidianos en el mbito empresarial con la hoja de clculo ms utilizada del mercado: Excel 2007. Formar al alumno una lgica de programacin en base a estructuras de control eficientes.



Excel para Expertos

IV.

OBJETIVOS ESPECFICOS Formar al alumno como desarrollador de aplicaciones de software tiles para sus necesidades particulares. Permitir al alumno conocer el funcionamiento de las macros de Excel, tanto las macros de grabacin como las macros de Excel Programadas, tambin se aprende a desarrollar multitud de ejemplos prcticos de macros programadas con Visual Basic de Aplicaciones para Excel en Mdulos o Userforms.

V.

PRE REQUISITOS Conocimientos de Excel Bsico e Intermedio

VI.

EVALUACIN Asistencia mnima : 70% al curso Promedio mnima aprobatoria: de 4.5 o 13 en escala vigesimal

La composicin de la evaluacin para obtener el promedio final es:Mecanismo de Evaluacin Asistencia Evaluacin de Fin de Sesin Evaluacin Grupal Ponderacin 10% 10% 35% Descripcin Control en Formato de Asistencia Evaluacin programada de fin de temas indicados Aplicado a la resolucin de ejercicios en cada sesin Consolida el aprendizaje en la implementacin de un caso, desarrollo o inclusive evaluacin escrita Fecha Aplicacin Permanente

Permanente

Evaluacin Final

45%

Fecha de entrega de promedio final del curso: Fecha de devolucin de evaluaciones: La Evaluacin de Fin de Sesin se realizar durante la primera hora de cada sesin de clase en la que se le programe, considerando un nmero de temas abordados de acuerdo al programa curricular, por lo que requiere una revisin y preparacin del estudiante con los materiales entregados previamente. Las fechas de evaluacin son definitivas e impostergables, cualquier entrega posterior no ser considerada dentro de la evaluacin del curso.



Excel para Expertos

Es justificable el 30% de inasistencias siempre y cuando sean comunicadas con anterioridad a la fecha de inasistencia pero no en la entrega de trabajos o exmenes. Es responsabilidad exclusiva del alumno el no comunicar la inasistencia as como de no presentarse a la entrega de trabajos y evaluaciones.

VII.

CONTENIDO DETALLADO DEL CURSO

Tema 1: Listas y Filtros I Tema 2: Listas y Filtros II Tema 3: Esquemas y Vistas Tema 4: Consolidacin de Datos Tema 5: Consolidar datos con Subtotales Tema 6: Auditora de Formulas Tema 7: Trabajo con Bases de Datos Tema 8: Obtener Datos Externos (Query Analyzer) Tema 9: Informes de Tablas Dinmicas I Tema 10: Informes de Tablas Dinmicas II Tema 11: Creacin de Informes de Grficos Dinmicos Tema 12: Administrador de Escenarios Tema 13: Solver Anexos: Excel Estadstico I Excel Estadstico II

VIII.

PROGRAMACIN DAS DOMINGO HORARIOS 9:00 a 13:00 hrs.



Excel para Expertos

IX.

MATERIAL DEL CURSO

Se entregar: Material de Estudio CD

X.

PAUTAS COMPLEMENTARIAS

El profesor es el responsable de la elaboracin del material y de las actividades; as como quien absolver las consultas que le realicen a travs del correo electrnico. Adicionalmente la Coordinadora Acadmica ser la encargada de supervisar el cumplimiento de las tareas, Ud. podr comunicarse con ella travs de su correo electrnico.

XI.

BIBLIOGRAFA Conociendo Excel 2007, Editorial MACRO Excel Avanzado 2007, Editorial Megabyte Introduccin a la Computacin, Editora DELTA Aplicaciones en Excel, Editora DELTA Texto de Consulta Informtica Bsica, Universidad Catlica de Santa Mara - Arequipa URLs de Internet



Excel para Expertos

Giovanni Cabrera Mlaga

TEMA No. 1 MS EXCEL 2007 Listas y Filtros I Cuando filtramos una tabla, Microsoft Excel oculta de la vista todos aquellos registros (filas) de la lista que no cumplen con la condicin establecida. De esta forma, slo quedan visibles aquellos registros que hayamos elegido. Contamos con la siguiente lista (Libros de Computacin e Informtica) y deseamos filtrar la lista de manera tal que slo muestre aquellos datos del campo Tipo de Salida:

Realizar lo siguiente: Haga clic en una celda de la lista. En la ficha Datos, seleccione el botn Filtro. Observe que al lado derecho de los nombres de cada campo aparece un botn de lista desplegable llamado Flecha Autofiltro:

Haga clic sobre el botn de flecha del campo requerido (para nuestro ejemplo, seleccione Tipo de Salida):

MS Excel 2007

Pgina: 1



Excel para Expertos


Si escogemos la opcin Venta el resultado ser:

Nota: Para quitar el filtro de la columna Tipo de Salida haga clic en la flecha de filtro y elija Seleccionar todo. Los filtros se pueden combinar, por ejemplo si queremos mostrar las ventas realizadas el da 5 de Julio, entonces: Seleccione el campo Tipo de Salida y elija Venta, luego seleccione en el campo Fecha el da 5 de Julio:

Filtros Personalizados: Por ejemplo, deseamos conocer y mostrar los precios de libros mayores a 35, para ello procedemos de la siguiente forma:

MS Excel 2007

Pgina: 2



Excel para Expertos

Giovanni Cabrera Mlaga Haga clic en una celda de la lista. En la ficha Datos, seleccione el botn Filtro. Observe que al lado derecho de los nombres de cada campo aparece un botn de lista desplegable llamado Flecha Autofiltro. Haga clic en el botn de flecha del campo Precio y seleccione Filtro de nmero y luego Filtro Personalizado:

El resultado es el siguiente:

Nota: Para salir del modo Filtrar, seleccione de Datos la opcin Borrar: Filtros Mltiples: Existen tres formas bsicas en las que podemos realizar Filtros Mltiples: 1. Condicin Y: Por ejemplo, deseamos conocer las salidas realizadas entre los das 03 y 05 de Julio:

El resultado ser:

MS Excel 2007

Pgina: 3



Excel para Expertos


2. Condicin O: Por ejemplo, deseamos conocer los libros que se han dado como Muestra o Donacin:

El resultado ser:

3. Caracteres Comodn (*) (?): Por ejemplo, deseamos filtrar la lista de modo que slo muestre los datos de los Clientes cuyo nombre empiece con la letra R.

MS Excel 2007

Pgina: 4



Excel para Expertos

Giovanni Cabrera MlagaEl resultado ser:

Filtros Avanzados: A diferencia del comando Autofiltro, el comando Filtro Avanzado requiere que los criterios de filtrado sean especificados dentro de un rango separado de la lista. Un rango de criterios debe constar al menos de dos filas. En la primera fila se introduce uno o ms encabezamientos de columnas y en las filas restantes los criterios de filtrado. Por ejemplo, deseamos conocer todos los libros que han sido dados como Donacin, para ello: Copie los encabezados en la celda A24. Coloque en la celda F25 Tipo de Salida Donacin. Copie los encabezados en la celda A28.

Hasta este momento la hoja debe verse as:

Seleccione cualquier celda de la tabla. En la ficha Datos seleccione el botn Avanzadas, en el siguiente cuadro de dialogo realice lo siguiente:

MS Excel 2007

Pgina: 5



Excel para Expertos

Giovanni Cabrera Mlagao o o o Seleccione Copiar a otro lugar. Rango de la lista: $A$1:$H$21 Rango de criterios: $A$24:$H$25 Copiar a: $A$28:$H$28


Condiciones O: Se coloca el nuevo criterio debajo del anterior, por ejemplo:

MS Excel 2007

Pgina: 6



Excel para Expertos

Giovanni Cabrera Mlaga El resultado es el siguiente:

Condiciones Y: Se debe agregar columnas. Por ejemplo al filtro anterior le agregaremos la condicin que sea en una fecha determinada, en este caso el 3 de Julio:


MS Excel 2007

Pgina: 7



Excel para Expertos


TEMA No. 2 MS EXCEL 2007 Listas y Filtros II

1. Ingrese los datos como se muestra en la figura siguiente: Importante: Respete la posicin de las celdas, observe y cree la lnea de divisin(Ficha Inicio/Fuente/Borde inferior).

2. Seleccione las columnas B y C y ocltelas. El resultado debe ser el siguiente:

3. Realice la siguiente ordenacin teniendo como criterio varios campos, para ello utilice de la ficha Datos la opcin Ordenar:

MS Excel 2007

Pgina: 8



Excel para Expertos


4. Muestre las columnas B y C que anteriormente fueron ocultadas, para ello seleccione una columna en cada lado de las columnas ocultas (seleccione A y D). 5. Observe los resultados del ordenamiento. Importante: Cuando ordene, siempre el primer criterio ser el que Excel elegir primero,despus, el segundo criterio y, por ltimo, el tercero. Los criterios pueden establecerse eligiendo cualquiera de los campos de la lista, y el orden puede ser ascendente o descendente.

6. Agregue ahora 3 registros al final de la Lista:APELLIDO PATERNO Crdenas Gmez Llerena APELLIDO MATERNO Arce Sandoval Castro Gustavo Alex Ana Karina Amancia P. Viejo 312 Dpto. 2 Manuel Balln 205 Villa Amrica Dpto. 8 29857413 28546089 29638112 Arequipa Lima Cajamarca NOMBRES DIRECCION DNI PROVINCIA

7. Ahora vamos a crear Autofiltros, para ello seleccione la ficha Datos/Filtro, luego elija de la columna PROVINCIA la opcin Arequipa y observe los resultados:

8. Para crear Filtros Avanzados siga estos pasos: Copie el rango de cabecera o ttulo por debajo de la propia lista o base. En las celdas de la siguiente fila a la copiada de ttulos se le irn indicando las condiciones de bsqueda o criterios.

MS Excel 2007

Pgina: 9



Excel para Expertos

Giovanni Cabrera Mlaga Se copiar de nuevo el rango de cabecera o ttulos por debajo de la anterior copiada y ser bajo esta cabecera donde aparecern los datos filtrados.

9. Seleccione de la ficha Datos la opcin Avanzadas y seleccione los rangos que aparecen en la imagen:

10. Observe en el resultado que aparecen todos los registros cuya PROVINCIA sea diferente a Lima. 11. Seleccione la celda F24 y ordene la columna en forma Ascendente utilizando el botn de la ficha Datos. 12. Seleccione de la ficha Datos la opcin Subtotal y realice lo siguiente:

MS Excel 2007

Pgina: 10



Excel para Expertos


13. El resultado ser el siguiente:

14. Seleccione el rango E4:E16 (DNI), vaya a la ficha Datos y elija la opcin Validacin de datos. En la pestaa Configuracin coloque lo siguiente:

MS Excel 2007

Pgina: 11



Excel para Expertos

Giovanni Cabrera MlagaEn la pestaa Mensaje de entrada coloque lo siguiente:

En la pestaa Mensaje de error coloque lo siguiente:

15. Observe los resultados de sus validaciones equivocndose a propsito:

16. Guarde su hoja de clculo dentro de su carpeta de trabajo. 17. En el mismo Libro de trabajo, en la Hoja2 ingrese los datos como en la figura:

MS Excel 2007

Pgina: 12



Excel para Expertos


Nota: Para la columna de Apellidos y Nombres haga lo siguiente: En la Hoja1 en la celda H4 coloque la siguiente funcin y luego arrastre el resultado hasta la celda H16: =CONCATENAR(A4," ",B4,","," ",C4) Copie luego el rango H4:H16 Vaya a la Hoja2 y pegue el rango en la celda B4. decimales. 19. A partir de las columnas APELLIDOS Y NOMBRES y N.FINAL realice el siguiente grafico en una hoja nueva, pngale de nombre NOTAS_FINALES:

18. Luego calcule el Promedio con 2 decimales y la Nota Final redondeada a 0

MS Excel 2007

Pgina: 13



Excel para Expertos

Giovanni Cabrera Mlaga20. Se va a mostrar en una nueva tabla y nuevo grfico, la informacin correspondiente a alumnos Repitentes, sin distincin de Sexo ni de Distrito ni de otra clase. Para especificar este criterio de filtrado realice lo siguiente: Ubique el cursor en la celda J4 y escriba en ella SITUACION (rtulo de la columna que contiene los datos de alumnos nuevos y Repitentes). En la celda J5 escriba Repitente (situacin que interesa filtrar).

21. Haga clic en cualquier celda no vaca de la columna SITUACION. Vaya a la ficha Datos y seleccione la opcin Avanzadas...y seleccione los rangos como se muestra en la figura:

Haga clic en Aceptar y observe el resultado: se muestran solamente los datos de los alumnos Repitentes, en una nueva tabla con su grfico. Copie esta nueva tabla en la Hoja3 y su grfico en la Hoja4 del Libro de Excel abierto y ponga a esta hoja el nombre REPITENTES. Regresar a la Hoja2.

MS Excel 2007

Pgina: 14



Excel para Expertos

Giovanni Cabrera MlagaPara volver a la visualizacin completa de los datos originales seleccione la opcin Borrar de la ficha Datos. 22. Para concluir realice los siguientes ejercicios: Vuelva a la lista completa original. Aplique un filtro avanzado que permita mostrar nicamente los datos de Alumnas, sin mover la lista a otro lugar. No olvide especificar el criterio del filtro en una parte despejada de la hoja de datos. Copie la nueva tabla en la Hoja5 y el grfico en la Hoja6 del libro de Excel, y llmela ALUMNAS. Vuelva a la lista completa original. Aplique un filtro avanzado que muestre la informacin de todos los alumnos(as) que viven en el Distrito de J.L.B.y R., sin mover la lista a otro lugar. Copie la nueva tabla en la Hoja7 y el grfico en la Hoja8 del libro de Excel, al que llamar BUSTAMANTINOS. Vuelva a la lista completa original y aplique 2 criterios de filtro simultneamente: Repitentes del Distrito de Cerro Colorado. Importante: Hacer que las especificaciones de estos 2 criterios de filtro queden en columnas adyacentes de una parte despejada de la tabla. Al indicar el Rango de criterios anotar las referencias absolutas a las celdas del vrtice izquierdo superior y derecho inferior de dicho rango. Guarde la nueva tabla en la Hoja9 y el grfico en la Hoja10 del libro de Excel, con nombre REP-CER. Vuelva a la lista completa original. Filtrar la informacin de los alumnos(as) que vivan en el Distrito del Cercado o Cayma. Importante: Para ello se debe especificar el criterio anotando Cercado y Cayma en celdas contiguas de la columna DISTRITO (una bajo la otra), para que el filtro opere con la conectiva O. Vuelva a la lista completa original. Filtre la informacin de Promedios entre 10 y 13. Importante: Para eso es necesario especificar el criterio poniendo los datos en dos columnas contiguas, usando los smbolos > (mayor que) y < (menor que): PROM. >10 PROM.

Documents

Excel PE - Modulo I